CODEWITHSUNDEEP
rxts
PDM
PDM

Reputation: 27

R xts package - to.period by minute over a day

I have a minute timeseries in xts. I am wondering if there is an elegant way to get OHLC from the start of the day until each minute of my time series. Eg for the minute 2017-07-14 12:29 I will have the open of 2017-07-14 08:00, the close of 2017-07-14 12:29 and the min/max during the period 2017-07-14 08:00 until 2017-07-14 12:29. My time serie is fully filled from 08:00 to 22:00 (841 minute points). I am thinking about doing a loop but I guess there is a more elegant way.

Thanks

Pierre

Upvotes: 1

Views: 285

Answers (1)

Joshua Ulrich
Joshua Ulrich

Reputation: 176738

You can calculate cumulative max/min using cummax() and cummin(), respectively. You just need to apply those functions by day. You can do that by split()ing your data into daily groups, applying the function below to each group, then rbind()ing the data back together.

Here's a reproducible example using daily data in the xts package.

library(xts)
set.seed(21)
tm <- timeBasedSeq('2017-07-14/2017-07-15/M')
x <- xts(20*cumprod(1+rnorm(length(tm), 0, 0.0025)), tm)
colnames(x) <- "price"

aggfun <- function(x) {
  stopifnot(ncol(x) > 0)
  # remove potential column name
  colnames(x) <- NULL
  r <- xts(cbind(Open = rep(x[1], nrow(x)),
                 High = cummax(x),
                 Low = cummin(x),
                 Close = x), index(x))
  r
}
y <- do.call(rbind, lapply(split(x, "day"), aggfun))

The output from one day to the next looks like:

y[1439:1442]
#                         Open     High      Low    Close
# 2017-07-14 23:58:00 20.03965 25.02193 19.60128 23.73810
# 2017-07-14 23:59:00 20.03965 25.02193 19.60128 23.71598
# 2017-07-15 00:00:00 23.73816 23.73816 23.73816 23.73816
# 2017-07-15 00:01:00 23.73816 23.73816 23.71164 23.71164

Upvotes: 1

Related Questions