3

I am trying to aggregate daily share price data (close only) to weekly share price data using the "to.weekly" function in quantmod. The xts object foo holds daily share price data for a stock starting from Monday 3 January 2011 and ending on Monday 20 September 2011. To aggregate this daily data I used:

tmp <- to.weekly(foo)

The above approach succeeds in that tmp now holds a series of weekly OHLC data points, as per the quantmod docs. The problem is that the series begins on Monday 3 January 2011 and each subsequent week also begins on Monday e.g. Monday 10 January, Monday 17 January and so on. I had expected the week to default to ending on Friday so that the weekly series started on Friday 7 January and ended on Friday 16 September.

I have experimented with adjusting the start and end of the data and using 'endof' or 'startof' together with the indexAt parameter but I cannot get it to return a week ending in Friday.

I am grateful for any insights received. (Sorry, I could not find any way to attach dput file so data appears below)

foo:

2011-01-03 2802
2011-01-04 2841
2011-01-05 2883
2011-01-06 2948
2011-01-07 2993
2011-01-10 2993
2011-01-11 3000
2011-01-12 3000
2011-01-13 3025
2011-01-14 2970
2011-01-17 2954
2011-01-18 2976
2011-01-19 2992
2011-01-20 2966
2011-01-21 2940
2011-01-24 2969
2011-01-25 2996
2011-01-26 2982
2011-01-27 3035
2011-01-28 3075
2011-01-31 3020

tmp:

           foo.Open foo.High foo.Low foo.Close
2011-01-03     2802     2802    2802      2802
2011-01-10     2841     2993    2841      2993
2011-01-17     3000     3025    2954      2954
2011-01-24     2976     2992    2940      2969
2011-01-31     2996     3075    2982      3020
SlowLearner
  • 7,907
  • 11
  • 49
  • 80

2 Answers2

1

I've come up with something yielding only Close values, perhaps it can be hacked further to return OHLC series.

Assuming that foo is an xts object, first we create the vector of indeces of Fridays:

fridays = as.POSIXlt(time(foo))$wday == 5

Then we prepend it with 0:

indx <- c(0, which(fridays))

And use period.apply:

period.apply(foo, INDEX=indx, FUN=last)

Result:

          [,1]
2011-01-07 2993
2011-01-14 2970
2011-01-21 2940
2011-01-28 3075
Anatoliy
  • 1,350
  • 9
  • 9
  • Thanks Anatoliy. I have adapted the code into a wrapper code and tested it with a few xts objects and it seems to work well. – SlowLearner Sep 21 '11 at 00:21
0

For Fridays (with occasional Thursdays due to market closures), use: 

tmp = to.weekly(foo, indexAt = "endof")

For Mondays (with occasional Tuesdays due to market closures), use:

tmp = to.weekly(foo, indexAt = "startof")`

Or you can create a custom vector of Dates that contains the date to be associated with each week. For instance, to force every week to be associated with Friday regardless of market closures:

customIdx = seq(from = as.Date("2011-01-07"), by = 7, length.out = nrow(tmp)) index(tmp) = customIdx

Geoffrey Poole
  • 1,102
  • 8
  • 10