CODEWITHSUNDEEP
rquantmod
adam.888
adam.888

Reputation: 7846

R merging Quarterly data with first date of quarter as the index

I can generate quarterly OHLC date from a daily time series:

library(quantmod)
getSymbols("SPY", from="2000-01-01", to=Sys.Date())
tail(SPY)
dfQ <- to.quarterly(SPY[,6])
tail(dfQ)

I can also generate the quarterly mean:

dfmean1 <- apply.quarterly(xts(SPY[,6]), FUN = mean)
tail(dfmean1)

However I am having problems merging the two, with an index showing the first date of the quarter (rather than the last date of the quarter).

Thank you for your help

Upvotes: 0

Views: 777

Answers (1)

Darren Cook
Darren Cook

Reputation: 28913

I think you have two questions here. The first is how to have a mean column in OHLC quarterly data. The second is how to have datestamps for the start of each quarter, instead of "last" datestamps. The xts/quantmod packages assume you want "last" datestamps, so go with the flow, and just replace the datestamps at the end.

To have mean with OHLC I've found it best just to do the OHLC calculation myself. So instead of passing mean to apply.quarterly(), do this:

bars = apply.quarterly(xts(SPY[,6]), FUN = function(x){
  d=coredata(x);
  c(first(d),max(d),min(d),last(d),mean(d))
  } )
colnames(bars)=c("open","high","low","close","mean")

This gives:

...
2013-09-30 159.71 171.28 159.56 167.10 165.9822
2013-12-31 168.43 184.69 164.59 184.69 176.1416
2014-01-08 182.92 183.52 182.36 183.52 183.0340

Then to fix the datestamps:

index(bars) = as.Date(as.yearqtr(index(bars)))

To understand that, start by looking at index(bars), then look at as.yearqtr(index(bars)), which gives:

[1] "2000 Q1" "2000 Q2" "2000 Q3" ...
   ... "2013 Q3" "2013 Q4" "2014 Q1"

Then, as luck would have it, as.Date() gives you the datestamp of the start of each quarter.

The final bit is to assign the new index back to the bars object with index(bars) = ... (or index(bars) <- ... if you prefer).

By the way, there is also a indexAt="lastof" or indexAt="firstof" parameter you could give to to.quarterly(). Experiment with this, but in my tests it was not quite useful enough.

Upvotes: 1

Related Questions