I have an xts object with intraday (1 minute OHLC) financial timeseries data.
How do I add columns representing current day session values such as sessionHigh, sessionLow etc up to this point in time?
To specify further: for any row I'd like to know what so far the highest and lowest price of this day was. At the beginning of the next day this should reset to the high and low price of the first datapoint of the new day.
How do I go about this in R? How do I calculate new columns based on (in this case daily) subsets of the data?
You can do this with a pure xts approach:
This approach is general. You can change the split to be across weeks, months, hours, via the f argument in split.xts
library(lubridate)
# generate some toy data:
set.seed(4)
time <- as.POSIXct(as.character(Sys.Date() + days(0:5)))
time <- rep(time, 5) + sample(x= 1:84000, replace = FALSE, size = 5)
time <- time[order(time)]
x <- xts(runif(length(time)), time)
# Solve your problem:
x.byday <- lapply(split(x, f = "days"), function(x) setNames(merge(x, cummax(x), cummin(x)), c("Close", "Close.runmax", "Close.runmin")))
z <- do.call(rbind, x.byday)
> head(z, 15)
# Close Close.runmax Close.runmin
# 2017-11-23 00:12:32 0.26042777 0.2604278 0.26042777
# 2017-11-23 06:28:19 0.72440589 0.7244059 0.26042777
# 2017-11-23 06:51:14 0.90609215 0.9060922 0.26042777
# 2017-11-23 13:40:08 0.94904022 0.9490402 0.26042777
# 2017-11-23 18:58:57 0.07314447 0.9490402 0.07314447
# 2017-11-24 00:12:32 0.75467503 0.7546750 0.75467503
# 2017-11-24 06:28:19 0.28600062 0.7546750 0.28600062
# 2017-11-24 06:51:14 0.10005352 0.7546750 0.10005352
# 2017-11-24 13:40:08 0.95406878 0.9540688 0.10005352
# 2017-11-24 18:58:57 0.41560712 0.9540688 0.10005352
# 2017-11-25 00:12:32 0.45510242 0.4551024 0.45510242
# 2017-11-25 06:28:19 0.97105566 0.9710557 0.45510242
# 2017-11-25 06:51:14 0.58398798 0.9710557 0.45510242
# 2017-11-25 13:40:08 0.96220462 0.9710557 0.45510242
# 2017-11-25 18:58:57 0.76170240 0.9710557 0.45510242
Please provide some reproducible data next time.