R xts to.period for custom daily aggregation periods

Question

I was wondering if its possible to aggregate custom periods.

I tried to use to.period(x,"day",3,OHLC=FALSE) to aggregate but it didn't work as it just returned the lastest period.

For example let x be a 2 day xts object with OHLC data.

               Open     High      Low    Close   Volume
1999-11-18 30.65656 33.68852 26.95082 28.80369 66392936
1999-11-19 28.93002 28.97213 26.82449 27.45615 16173015

Can any xts tools allow me to aggregate to single row?

so the results I want would look like:

               Open     High      Low    Close   Volume
1999-11-19 30.65656 33.68852 26.82449 27.45615 82565951

where the open, is the first days open, high is the max of the two days, low is the low of the two days, and close is the last days close. Volume is just the sum of the volume for the two days. I would want this solution to be generalized and scalable as I want to do this for an entire N by M xts object. I will specify k. In the above case, k=2....so every 2 days, I aggregate in to one.

FXQuantTrader · Answer 1 · 2016-12-01T02:07:20.497

Using OHLC=TRUE may get you part of the way.

AAPL1 <- to.period(AAPL,"days",k = 2,OHLC=TRUE)

head(AAPL1)
# AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
# 2007-01-04     86.29     86.58    81.90      85.66   521395000      11.14619
# 2007-01-05     85.77     86.20    84.40      85.05   208685400      11.06681
# 2007-01-08     85.96     86.53    85.28      85.47   199276700      11.12147
# 2007-01-10     86.45     97.80    85.15      97.00  1575544600      12.62176
# 2007-01-12     95.94     96.78    93.23      94.62   688235800      12.31207
# 2007-01-16     95.68     97.25    95.45      97.10   311019100      12.63477

But the way to.period is calculating every 2 days may not be desirable to you (some aggregation periods still return 1 bar of data because of issues like weekends etc). If speed isn't a big concern for you, you could create the bars from scratch in R (to.period is faster using C code under the hood). This approach could give you much more flexibility in how you aggregate data.

getSymbols("AAPL")

head(AAPL, 12)
#AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
#2007-01-03     86.29     86.58    81.90      83.80   309579900      10.90416
#2007-01-04     84.05     85.95    83.82      85.66   211815100      11.14619
#2007-01-05     85.77     86.20    84.40      85.05   208685400      11.06681
#2007-01-08     85.96     86.53    85.28      85.47   199276700      11.12147
#2007-01-09     86.45     92.98    85.15      92.57   837324600      12.04533
#2007-01-10     94.75     97.80    93.45      97.00   738220000      12.62176
#2007-01-11     95.94     96.78    95.10      95.80   360063200      12.46562
#2007-01-12     94.59     95.06    93.23      94.62   328172600      12.31207
#2007-01-16     95.68     97.25    95.45      97.10   311019100      12.63477
#2007-01-17     97.56     97.60    94.82      94.95   411565000      12.35501
#2007-01-18     92.10     92.11    89.05      89.07   591151400      11.58990
#2007-01-19     88.63     89.65    88.12      88.50   341118400      11.51573
# You want to aggregate every 2 full trading days of data, so have the endpoints of each aggregation period simply increment by 2.
ep2d <- seq(0, NROW(ep), by = 2)

m2 <- period.apply(AAPL,INDEX=ep2d,FUN=
                       function(x) {

                           xts(x = matrix(c(coredata(Op(x))[1], max(coredata(Hi(x))), min(coredata(Lo(x))), coredata(Cl(x))[NROW(x)],
                                            sum(coredata(Vo(x)))), nrow =1), order.by= index(x)[NROW(x)],
                           dimnames = list(NULL, c("Open", "High", "Low", "Close", "Volume")))
                       })

head(m2)
# Open  High   Low Close     Volume
# 2007-01-04 86.29 86.58 81.90 85.66  521395000
# 2007-01-08 85.77 86.53 84.40 85.47  407962100
# 2007-01-10 86.45 97.80 85.15 97.00 1575544600
# 2007-01-12 95.94 96.78 93.23 94.62  688235800
# 2007-01-17 95.68 97.60 94.82 94.95  722584100
# 2007-01-19 92.10 92.11 88.12 88.50  932269800

R xts to.period for custom daily aggregation periods

1 Answers1