Projecting quarterly dates using datetime variables

Hi there,

I'm trying to project quarter-end dates but the calquarter (or calmonth) function doesn't seem to work correctly if the start date is a month with 30 days.

Here's an example where it works correctly (start date is month with 31 days). The result in this case is adjusted for months with 30 and 31 days:

>> t = datetime(2013,05,31):calquarters(1):datetime(2014,05,31)

t = 

   31-May-2013   31-Aug-2013   30-Nov-2013   28-Feb-2014   31-May-2014

Here is an example where it doesn't work correctly (start date is month with 30 days). The result in this case has the 30th of each month as quarter-end, which is not correct:

>> t = datetime(2013,06,30):calquarters(1):datetime(2014,05,31)

t = 

   30-Jun-2013   30-Sep-2013   30-Dec-2013   30-Mar-2014

I appreciate any help with this. Many thanks!!!

Alex

[dpb]

Looks like a bug although I expect TMW will claim it is WAD as the definition is

Q = fix(calmonths(t)/3)

The colon expansion doesn't look at the dynamic nature of the interval as your tests show during the building process...

Hmmm....what is a convenient workaround????

Well, nothing really jumps out at me other than some sorta' nasty gyrations with eomday to try to pass a vector of day increments based on the start/end dates wanted.

This is a bug report/enhancement request...I've noted elsewhere that datetime is a work in progress with much yet to be done to make it truly functional; this appears yet another wart.

Peter P has seen and commented on a number of the time-related threads and sometimes has clever ways to use the functions that aren't apparent to the neophyte or shows how they were intended to be used for real examples--so many of the examples in the doc are just too trivial to be of any value for real situation.

[Stephen23]

"Looks like a bug..."

I disagree: it looks like a badly posed definition. Consider the first of the month:

datetime(2013,05,1):calquarters(1):datetime(2014,05,1)

What would the expected output be? What about the second of the month?:

datetime(2013,05,2):calquarters(1):datetime(2014,05,2)

And so on up until 30. This is ambiguous because what humans see as "intuitive" is really an unrelated operation. I agree with Peter Perkins' comment below: dateshift is the correct way to achieve this task.

[dpb]

I agree I think it a definition issue; see follow-up...I pose an alternate syntax altho I think the "intuitive" solution is the more likely one users would expect in a high fraction of instances so should rate higher on the scale of how to implement/make the definition. The point of Matlab is the RAD arena; things that take extra effort to fix up detract from that objective....and, of course, that's all in the realm of $0.02, imo, ymmv, etc., etc., etc., ... caveats. :)

Answer 1

[dpb]

OH! Wait! That comment re: Peter reminds me of a trick he did show earlier that may fixup the problem-- dateshift. Let's see with your bum example:

>>  tq = datetime(2013,06,30):calquarters(1):datetime(2014,05,31)
tq = 
 30-Jun-2013   30-Sep-2013   30-Dec-2013   30-Mar-2014
>> dateshift(tq,'end','month')
ans = 
 30-Jun-2013   30-Sep-2013   31-Dec-2013   31-Mar-2014
>>

VOILA! I agree it should work "out of the box", but there at least there is a reasonable way to clean up the mess...

You could even wrap the original call in the dateshift function and do it in a single step.

[Peter Perkins]

Yes, dateshift is the way to go here.

Calendar arithmetic is complicated and ambiguous. Consider calmonths. If you are adding calendar months starting at the 15th of January, the sequence will hit the 15th of each subsequent month, because adding calendar months is about preserving the date and incrementing the month. So by the same reasoning, if you start at the 31st of January, the second value in the sequence "ought" to be Feb 31st.

But obviously.

So should it be Mar 3rd, because Jan has 31 days (and should you then continue to add 31 days repeatedly)? Should it be Feb 28th because it's the end of the month? Should it be Feb 28th because Feb has 28 days (which also happens to hit Feb 28th but for a different reason)? Or something else?

The amswer is that adding calendar months is about preserving the date and incrementing the month. So adding calmonths to Jan 31st does "the best it can" to preserve the date and the month, and the fact that it creates a sequence of end of month days is kind of a coincidence. Adding calmonths to Jan 30th also does "the best it can", and doesn't create an end of month sequence.

dateshift is the way to go, these kinds of "formulaic" sequences are 1/2 of what it is designed for (the other half being shifting a vector of dates elementwise).

All that carries over the calquarters and so on.

[dpb]

I grant the month; I also acknowledge potential ambiquity but... (there's always a "but", isn't there? :)

Does it not make sense that a calendar quarter would end on end of month even though a month sequence may logically be on the same day of month? Agreed, it may not be implementable w/o too much grief owing to the complications shown here that colon would have to have variable steps.

Suggestion:

1. Add some words to help to point to the solution, and/or use some of these issues as base for the examples instead of the too-simple of just passing an array of numbers to get the expected result, or
2. Perhaps an optional flag to calquarter and friends indicate one wants that behavior similar to dateshift.

[dpb]

Peter, on a related ? to time in Matlab, what is seen as the time series class' role? Another post in the last day or so got me to investigate it as being potentially helpful in selecting events but it appears that while it can do it the syntax is quite a lot more verbose than simply doing lookup with isbetween with datetime values and that, for instance, setting those event times is still limited to datenum or date strings. In addition, it didn't seem to have any real help for applying processing to the subsets other than the limited statistics and/or the specialized plotting...

Is it, like the Statistics Toolbox dataset a dead-end experiment that didn't pan out or is it anticipated to really develop it into a more powerful tool...

[Alexandre C Borges]

Great job! The dateshift function does exactly what we needed. Thanks for that!

[dpb]

Peter, I think your above explanation should go almost verbatim in the 'Algorithms' section of the documentation; it is the kind of information that does reduce confusion and add clarification to users (particularly newer ones or old ones to whom it is a new beast).

I'll reiterate the comments that the examples for datetime are far too simple in general to illustrate the usage of the forms; the cases shown never deal with these issues. This thread would be an ideal example to use to both illustrate the calculation as implemented and the intent of the functions in use to solve the problems ("warts") left over.

[dpb]

Alexandre -- yes, dateshift is handy, indeed; not sure one would ever find it from the documentation without hours and hours of looking, though, as first of all you have to go to the subsection on 'Arithmetic and Plotting' to even find it mentioned and (at least for me) on first blush it really was neither of those subjects that I even thought I was looking for to fix up similar issues when I first encountered datetime and had such problems.

In remembering it here, it does show that my "stream of consciousness" responses often do bear fruit in it was being reminded of Peter's helpful comments in those other threads that made me think of dateshift here; it isn't yet ingrained enough that it was my first aha! response to your question.