R xts financial intraday data - calculate session values?

Question

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?

Answer 1

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.

Answer 2

while XTS have good things, I have moved towards tidyverse and tibbles. There is also new tibbletime package. Here is how I would do it.

library(lubridate)
library(tidyverse)

tbl <- tibble(time = ymd_hms(time), value)

tbl %>% 
  mutate(day = date(time)) %>% 
  group_by(day) %>% 
  mutate(max = cummax(value),
         min = cummin(value))

# A tibble: 12 x 5
# Groups:   day [2]
                  time     value        day      max       min
                <dttm>     <dbl>     <date>    <dbl>     <dbl>
 1 2012-03-19 11:31:59 10.554327 2012-03-19 10.55433 10.554327
 2 2012-03-19 12:32:59  9.719728 2012-03-19 10.55433  9.719728
 3 2012-03-19 14:34:59 11.775163 2012-03-19 11.77516  9.719728
 4 2012-03-19 15:36:59 10.187320 2012-03-19 11.77516  9.719728
 5 2012-03-19 16:37:59 11.142526 2012-03-19 11.77516  9.719728
 6 2012-03-19 17:38:59 10.415526 2012-03-19 11.77516  9.719728
 7 2012-03-20 11:36:59 11.229507 2012-03-20 11.22951 11.229507
 8 2012-03-20 12:37:59 10.236680 2012-03-20 11.22951 10.236680
 9 2012-03-20 13:38:59  9.634617 2012-03-20 11.22951  9.634617
10 2012-03-20 14:36:59 11.105144 2012-03-20 11.22951  9.634617
11 2012-03-20 15:37:59  8.906406 2012-03-20 11.22951  8.906406
12 2012-03-20 16:38:59 10.461871 2012-03-20 11.22951  8.906406

Data:

time = c("2012-03-19 11:31:59", "2012-03-19 12:32:59", "2012-03-19 14:34:59", 
         "2012-03-19 15:36:59","2012-03-19 16:37:59", "2012-03-19 17:38:59",
         "2012-03-20 11:36:59","2012-03-20 12:37:59", "2012-03-20 13:38:59",
         "2012-03-20 14:36:59","2012-03-20 15:37:59", "2012-03-20 16:38:59")

set.seed(13)
value <- rnorm(12, mean = 10, sd = 1)

Bonus tibbletime: Everything works exactly like in normal tibble but tibbletime is also time aware. Lots of nice functions like in xts.

library(tibbletime)
tbl_time <- tbl_time(tbl, index=time)

tbl_time %>% 
  group_by(date(time)) %>%
  mutate(max = cummax(value),
         min = cummin(value))

Answer 3

Here I'm using daily data, but it will be the same with intraday data:

library(xts)
data(sample_matrix)
sample_close <- as.xts(sample_matrix[,4,drop=FALSE])
sample_close$session_high <- cummax(sample_close)

head(sample_close)
              Close session_high
2007-01-02 50.11778     50.11778
2007-01-03 50.39767     50.39767
2007-01-04 50.33236     50.39767
2007-01-05 50.33459     50.39767
2007-01-06 50.18112     50.39767
2007-01-07 49.99185     50.39767