[R] quarter end dates between two date strings

[Marc Schwartz]

On Apr 18, 2012, at 1:49 PM, David Winsemius wrote:

> 
> On Apr 18, 2012, at 2:05 PM, Marc Schwartz wrote:
> 
>> On Apr 18, 2012, at 11:58 AM, Ben quant wrote:
>> 
>>> Hello,
>>> 
>>> I have two date strings, say "1972-06-30" and "2012-01-31", and I'd like to
>>> get every quarter period end date between those dates? Does anyone know how
>>> to do this? Speed is important...
>>> 
>>> Here is a small sample:
>>> 
>>> Two dates:
>>> "2007-01-31"
>>> 
>>> "2012-01-31"
>>> 
>>> And I'd like to get this:
>>> 
>>> [1] "2007-03-31" "2007-06-30" "2007-09-30" "2007-12-31" "2008-03-31"
>>> "2008-06-30" "2008-09-30" "2008-12-31"
>>> [9] "2009-03-31" "2009-06-30" "2009-09-30" "2009-12-31" "2010-03-31"
>>> "2010-06-30" "2010-09-30" "2010-12-31"
>>> [17] "2011-03-31" "2011-06-30" "2011-09-30" "2011-12-31"
>>> 
>>> 
>>> Thanks!
>>> 
>>> ben
>> 
>> 
> 
> Same strategy as Marc used, but I'm thinking his might be returning a factor classed variable and mine it returning a Date class result.
> 
> seq(as.Date("2007-06-30", "%Y-%m-%d")+1, to=as.Date("2012-01-31", "%Y-%m-%d")+1,by="3 month" )-1
> [1] "2007-06-30" "2007-09-30" "2007-12-31" "2008-03-31" "2008-06-30" "2008-09-30" "2008-12-31" "2009-03-31"
> [9] "2009-06-30" "2009-09-30" "2009-12-31" "2010-03-31" "2010-06-30" "2010-09-30" "2010-12-31" "2011-03-31"
> [17] "2011-06-30" "2011-09-30" "2011-12-31"
> 
> -- 
> David.


Hi David,

No, they are dates. In the function body I coerced the factor levels returned from cut.Date() back to Date class prior to subtracting one day to get the last day of the quarter:

> str(Qtrs(as.Date("2007-01-31"), as.Date("2012-01-31")))
 Date[1:20], format: "2007-03-31" "2007-06-30" "2007-09-30" "2007-12-31" ...

> class(Qtrs(as.Date("2007-01-31"), as.Date("2012-01-31")))
[1] "Date"


However, note that in your example, with differing start/end dates:

> seq(as.Date("2007-05-30", "%Y-%m-%d")+1, to=as.Date("2012-02-28", "%Y-%m-%d")+1,by="3 month" )-1
 [1] "2007-05-30" "2007-08-30" "2007-11-30" "2008-03-01" "2008-05-30"
 [6] "2008-08-30" "2008-11-30" "2009-03-02" "2009-05-30" "2009-08-30"
[11] "2009-11-30" "2010-03-02" "2010-05-30" "2010-08-30" "2010-11-30"
[16] "2011-03-02" "2011-05-30" "2011-08-30" "2011-11-30"


If the start dates are not on a quarter boundary, the returned values are not calendar quarter end dates.

Whereas:

> Qtrs(as.Date("2007-05-30"), as.Date("2012-02-28"))
 [1] "2007-06-30" "2007-09-30" "2007-12-31" "2008-03-31" "2008-06-30"
 [6] "2008-09-30" "2008-12-31" "2009-03-31" "2009-06-30" "2009-09-30"
[11] "2009-12-31" "2010-03-31" "2010-06-30" "2010-09-30" "2010-12-31"
[16] "2011-03-31" "2011-06-30" "2011-09-30" "2011-12-31"


I initially thought the same as you did, but realized that it required the assumption that the start date had to be the first date of a calendar quarter. BTW, I should mention that another assumption is that the end date is the end of the possible date interval. Therefore, it is not returning the end date of the quarter in which the end date falls. That is, for end date 2012-02-28, it returns 2011-12-31, which is the last end of quarter date prior to the end date. It does not return 2012-03-31.

Regards,

Marc

> 
>> First thing that comes to mind is to use cut.Date() with breaks = "quarters" and subtract a day, since it will give you the first day of each quarter by default. See ?cut.Date. It returns a grouped sequence based upon the 'breaks' interval, much like ?cut for a continuous variable, with the factor levels being the grouped values. In this case, the first day of each quarter, which I coerce back to Dates. I remove the first value from the result vector and subtract a day.
>> 
>> Thus, encapsulating it in a function:
>> 
>> Qtrs <- function(Start, End)
>> {
>> Vec <- as.Date(levels(cut(seq.Date(Start, End, by = "month"),
>>                           breaks = "quarter")))
>> Vec[-1] - 1
>> }
>> 
>> 
>>> Qtrs(as.Date("2007-01-31"), as.Date("2012-01-31"))
>> [1] "2007-03-31" "2007-06-30" "2007-09-30" "2007-12-31" "2008-03-31"
>> [6] "2008-06-30" "2008-09-30" "2008-12-31" "2009-03-31" "2009-06-30"
>> [11] "2009-09-30" "2009-12-31" "2010-03-31" "2010-06-30" "2010-09-30"
>> [16] "2010-12-31" "2011-03-31" "2011-06-30" "2011-09-30" "2011-12-31"
>> 
>> 
>> Not fully tested, but seems to work, at least with your example dates.
>> 
>> Regards,
>> 
>> Marc Schwartz