I have an xts object with intraday OHLC price data over several years. I'd like to be able to write a function that calculates the min and max value between 04:00:00 and 05:00:00 every day and include that as a column in the xts object. Im not really familiar with manipulating xts objects. Can anyone point me in the right direction? Here's a head of the xts object.
Open High Low Close Volume
2017-01-01 00:00:00 968.29 968.76 966.74 966.97 106562
2017-01-01 00:05:00 966.97 967.00 966.89 966.89 13731
2017-01-01 00:10:00 966.89 966.89 964.86 964.86 124137
2017-01-01 00:15:00 964.86 964.99 964.80 964.80 3001
2017-01-01 00:20:00 964.80 964.80 964.80 964.80 0
2017-01-01 00:25:00 964.80 965.09 964.54 964.91 48000
2017-01-01 00:30:00 964.91 965.01 964.91 965.01 2501
2017-01-01 00:35:00 965.01 967.82 965.57 967.82 71501
2017-01-01 00:40:00 967.82 967.82 967.08 967.08 50
2017-01-01 00:45:00 967.08 967.40 967.40 967.40 50
2017-01-01 00:50:00 967.40 968.08 967.40 968.08 14000
2017-01-01 00:55:00 968.08 968.08 966.89 968.00 1008
2017-01-01 01:00:00 968.00 968.10 968.00 968.10 1002
2017-01-01 01:05:00 968.10 968.10 967.62 967.62 5200
2017-01-01 01:10:00 967.62 967.70 966.29 966.29 35476
2017-01-01 01:15:00 966.29 966.29 966.28 966.28 3068
2017-01-01 01:20:00 966.28 966.66 965.00 965.00 30471
2017-01-01 01:25:00 965.00 965.01 964.00 964.00 77884
2017-01-01 01:30:00 964.00 964.76 964.76 964.76 500
2017-01-01 01:35:00 964.76 967.48 964.69 965.00 134129
2017-01-01 01:40:00 965.00 965.00 963.67 963.67 59676
2017-01-01 01:45:00 963.67 963.67 963.67 963.67 0
2017-01-01 01:50:00 963.67 964.56 963.66 964.55 5531
2017-01-01 01:55:00 964.55 963.43 963.40 963.40 3000
2017-01-01 02:00:00 963.40 964.60 963.40 964.60 1301
2017-01-01 02:05:00 964.60 964.60 964.60 964.60 0
2017-01-01 02:10:00 964.60 964.60 964.00 964.11 49954
2017-01-01 02:15:00 964.11 964.60 964.59 964.60 5000
2017-01-01 02:20:00 964.60 964.60 964.60 964.60 0
2017-01-01 02:25:00 964.60 964.60 964.51 964.51 2000
2017-01-01 02:30:00 964.51 964.51 964.51 964.51 0
2017-01-01 02:35:00 964.51 964.51 963.23 963.99 16667
2017-01-01 02:40:00 963.99 963.99 963.65 963.66 10000
2017-01-01 02:45:00 963.66 964.26 963.16 964.26 75500
2017-01-01 02:50:00 964.26 964.26 964.26 964.26 0
2017-01-01 02:55:00 964.26 964.26 964.26 964.26 0
2017-01-01 03:00:00 964.26 964.61 963.98 964.61 13000
2017-01-01 03:05:00 964.61 964.61 964.61 964.61 0
2017-01-01 03:10:00 964.61 964.61 964.61 964.61 0
2017-01-01 03:15:00 964.61 964.61 964.61 964.61 0
2017-01-01 03:20:00 964.61 964.82 964.48 964.82 16666
2017-01-01 03:25:00 964.82 965.00 963.99 964.97 50500
2017-01-01 03:30:00 964.97 964.97 964.02 964.02 56000
2017-01-01 03:35:00 964.02 964.29 964.29 964.29 500
2017-01-01 03:40:00 964.29 963.53 963.52 963.52 24000
2017-01-01 03:45:00 963.52 963.52 963.43 963.43 16500
2017-01-01 03:50:00 963.43 963.67 963.42 963.42 25002
2017-01-01 03:55:00 963.42 963.42 961.69 961.69 84507
2017-01-01 04:00:00 961.69 961.69 960.90 960.93 57909
2017-01-01 04:05:00 960.93 960.93 960.93 960.93 0
2017-01-01 04:10:00 960.93 961.19 961.19 961.19 400
2017-01-01 04:15:00 961.19 962.09 961.19 962.09 7001
2017-01-01 04:20:00 962.09 962.09 962.09 962.09 0
2017-01-01 04:25:00 962.09 962.10 961.14 961.14 32000
2017-01-01 04:30:00 961.14 961.14 960.93 960.93 41900
2017-01-01 04:35:00 960.93 961.94 960.93 961.64 640
2017-01-01 04:40:00 961.64 961.71 961.64 961.71 1
2017-01-01 04:45:00 961.71 962.00 961.90 961.99 5499
2017-01-01 04:50:00 961.99 961.99 961.99 961.99 0
2017-01-01 04:55:00 961.99 961.99 961.99 961.99 1
2017-01-01 05:00:00 961.99 961.99 961.99 961.99 0
2017-01-01 05:05:00 961.99 961.99 961.99 961.99 40
2017-01-01 05:10:00 961.99 961.99 961.99 961.99 0
2017-01-01 05:15:00 961.99 961.99 961.99 961.99 0
2017-01-01 05:20:00 961.99 961.99 961.99 961.99 0
2017-01-01 05:25:00 961.99 961.99 961.99 961.99 0
2017-01-01 05:30:00 961.99 962.10 961.99 962.10 1382
2017-01-01 05:35:00 962.10 968.84 962.10 968.84 122909
2017-01-01 05:40:00 968.84 968.86 963.78 965.53 161263
2017-01-01 05:45:00 965.53 964.81 963.11 963.81 18021
2017-01-01 05:50:00 963.81 964.39 963.85 964.39 40006
2017-01-01 05:55:00 964.39 964.47 964.00 964.47 39966
2017-01-01 06:00:00 964.47 964.47 964.47 964.47 0
You can do this by filtering on the hours of the index and then using period.max and period.min functions. The values will be put in last record of the chosen hour. See example below with intraday data of MSFT, max and min values for between 15:00 and 16:00.
library(xts)
# max of high values between 15 and 16. (excluding 16:00)
msft$max <- period.max(msft$high[.indexhour(msft) == 15], endpoints(msft$high[.indexhour(msft) == 15], on = "hour"))
# min of low values between 15 and 16. (excluding 16:00)
msft$min <- period.min(msft$low[.indexhour(msft) == 15], endpoints(msft$low[.indexhour(msft) == 15], on = "hour"))
head(msft[8:24], 16)
open high low close volume max min
2020-01-23 14:50:00 166.180 166.2300 166.090 166.1050 87934 NA NA
2020-01-23 14:55:00 166.105 166.2200 166.103 166.1700 92280 NA NA
2020-01-23 15:00:00 166.160 166.3500 166.160 166.3400 114359 NA NA
2020-01-23 15:05:00 166.335 166.3400 166.285 166.2850 102633 NA NA
2020-01-23 15:10:00 166.290 166.3050 166.170 166.2550 125558 NA NA
2020-01-23 15:15:00 166.250 166.2750 166.210 166.2400 103938 NA NA
2020-01-23 15:20:00 166.230 166.2500 166.180 166.2350 99649 NA NA
2020-01-23 15:25:00 166.240 166.3000 166.225 166.2850 93846 NA NA
2020-01-23 15:30:00 166.270 166.4164 166.175 166.3600 183154 NA NA
2020-01-23 15:35:00 166.360 166.5000 166.320 166.4600 177178 NA NA
2020-01-23 15:40:00 166.450 166.4650 166.380 166.3800 112174 NA NA
2020-01-23 15:45:00 166.385 166.4050 166.290 166.3875 152806 NA NA
2020-01-23 15:50:00 166.382 166.5200 166.362 166.4500 205667 NA NA
2020-01-23 15:55:00 166.450 166.6900 166.305 166.6700 508469 166.69 166.16
2020-01-23 16:00:00 166.660 166.7200 166.589 166.7200 934090 NA NA
2020-01-24 09:35:00 167.510 167.5300 166.890 166.8918 1152646 NA NA
data:
msft <- structure(c(166.224, 166.29, 166.29, 166.2456, 166.165, 166.1446,
166.1601, 166.18, 166.105, 166.16, 166.335, 166.29, 166.25, 166.23,
166.24, 166.27, 166.36, 166.45, 166.385, 166.382, 166.45, 166.66,
167.51, 167.03, 167.265, 167.325, 167.37, 167.16, 167.405, 167.35,
167.31, 167.39, 167.17, 167.1, 166.845, 167.03, 167.1223, 167.125,
167.21, 167.34, 167.235, 167.3, 167.37, 167.1977, 166.9814, 166.8499,
166.99, 166.93, 166.83, 166.64, 166.775, 166.85, 166.71, 166.6838,
166.46, 166.35, 165.765, 166.2269, 166.01, 166.19, 166.13, 166.31,
166.36, 166.42, 166.3682, 165.99, 166.1328, 165.85, 165.74, 165.8439,
165.655, 165.5434, 165.47, 165.3227, 165.0627, 165.03, 165.2546,
165.14, 165.1, 164.91, 164.75, 164.65, 164.53, 164.81, 164.8979,
164.6, 164.89, 164.94, 165.03, 165.12, 165.17, 165.24, 165.4,
165.335, 165.2734, 164.985, 164.9, 164.61, 164.93, 165.18, 166.315,
166.29, 166.3, 166.265, 166.22, 166.2201, 166.2, 166.23, 166.22,
166.35, 166.34, 166.305, 166.275, 166.25, 166.3, 166.4164, 166.5,
166.465, 166.405, 166.52, 166.69, 166.72, 167.53, 167.34, 167.39,
167.495, 167.47, 167.48, 167.4251, 167.3699, 167.42, 167.41,
167.2, 167.1, 167.03, 167.21, 167.23, 167.255, 167.35, 167.35,
167.33, 167.405, 167.38, 167.25, 167.01, 167, 167.02, 167.02,
166.8384, 166.9056, 166.86, 166.94, 166.75, 166.6844, 166.47,
166.42, 166.22, 166.4049, 166.221, 166.2003, 166.3749, 166.3999,
166.43, 166.43, 166.375, 166.175, 166.16, 165.96, 165.93, 165.86,
165.671, 165.64, 165.49, 165.4, 165.08, 165.27, 165.26, 165.34,
165.12, 165, 164.825, 164.765, 164.82, 164.89, 165, 164.89, 164.99,
165.041, 165.293, 165.23, 165.27, 165.44, 165.6046, 165.37, 165.295,
165.18, 164.93, 164.945, 165.185, 165.24, 166.22, 166.225, 166.25,
166.15, 166.145, 166.13, 166.1015, 166.09, 166.103, 166.16, 166.285,
166.17, 166.21, 166.18, 166.225, 166.175, 166.32, 166.38, 166.29,
166.362, 166.305, 166.589, 166.89, 167.03, 167.22, 167.32, 167.225,
167.16, 167.2, 167.23, 167.2801, 167.145, 167.05, 166.84, 166.77,
167, 167.1, 167.02, 167.18, 167.23, 167.223, 167.28, 167.1843,
166.862, 166.85, 166.821, 166.8121, 166.85, 166.55, 166.6303,
166.69, 166.7, 166.54, 166.4, 166.31, 165.76, 165.74, 165.8966,
165.91, 166.07, 166.09, 166.171, 166.32, 166.22, 165.96, 165.97,
165.82, 165.73, 165.72, 165.64, 165.49, 165.45, 165.32, 165.045,
164.89, 164.91, 165.09, 165.1, 164.91, 164.74, 164.53, 164.529,
164.53, 164.735, 164.59, 164.54, 164.88, 164.938, 165.01, 165.0792,
165.12, 165.22, 165.335, 165.263, 164.88, 164.89, 164.58, 164.58,
164.87, 164.87, 166.29, 166.275, 166.26, 166.16, 166.145, 166.155,
166.19, 166.105, 166.17, 166.34, 166.285, 166.255, 166.24, 166.235,
166.285, 166.36, 166.46, 166.38, 166.3875, 166.45, 166.67, 166.72,
166.8918, 167.27, 167.325, 167.371, 167.2251, 167.4, 167.34,
167.29, 167.3988, 167.2, 167.1047, 166.86, 167.025, 167.11, 167.12,
167.2, 167.345, 167.23, 167.29, 167.37, 167.1916, 167.0027, 166.85,
167, 166.94, 166.85, 166.64, 166.7738, 166.85, 166.72, 166.68,
166.4672, 166.3512, 165.79, 166.21, 165.9969, 166.18, 166.14,
166.2968, 166.36, 166.43, 166.36, 165.99, 166.13, 165.83, 165.73,
165.8405, 165.65, 165.545, 165.48, 165.33, 165.05, 165.0227,
165.26, 165.1425, 165.101, 164.91, 164.74, 164.6581, 164.5292,
164.805, 164.89, 164.59, 164.8801, 164.9498, 165.04, 165.12,
165.16, 165.2302, 165.4, 165.34, 165.28, 164.987, 164.89, 164.605,
164.94, 165.185, 165.04, 158120, 165333, 101115, 78491, 123999,
76037, 82733, 87934, 92280, 114359, 102633, 125558, 103938, 99649,
93846, 183154, 177178, 112174, 152806, 205667, 508469, 934090,
1152646, 558627, 277325, 321651, 255494, 333848, 272126, 395463,
194593, 211910, 193131, 242112, 210240, 193265, 139617, 204182,
179146, 159259, 237888, 410982, 213787, 233082, 188071, 193742,
132377, 118994, 264247, 182490, 109514, 138164, 221052, 194127,
169059, 458214, 247712, 169523, 115531, 161259, 263230, 155536,
82474, 87549, 109057, 101772, 130642, 171988, 117235, 134507,
236662, 219303, 217698, 219808, 420288, 208087, 149358, 197435,
218090, 267667, 320279, 422434, 340478, 273866, 258938, 212451,
268017, 323657, 267686, 214060, 222314, 293731, 288867, 219687,
304733, 251063, 425450, 455311, 741208, 1429645),
.Dim = c(100L, 5L),
.Dimnames = list(NULL, c("open", "high", "low", "close", "volume")),
index = structure(c(1579785300, 1579785600, 1579785900, 1579786200, 1579786500,
1579786800, 1579787100, 1579787400, 1579787700, 1579788000,
1579788300, 1579788600, 1579788900, 1579789200, 1579789500,
1579789800, 1579790100, 1579790400, 1579790700, 1579791000,
1579791300, 1579791600, 1579854900, 1579855200, 1579855500,
1579855800, 1579856100, 1579856400, 1579856700, 1579857000,
1579857300, 1579857600, 1579857900, 1579858200, 1579858500,
1579858800, 1579859100, 1579859400, 1579859700, 1579860000,
1579860300, 1579860600, 1579860900, 1579861200, 1579861500,
1579861800, 1579862100, 1579862400, 1579862700, 1579863000,
1579863300, 1579863600, 1579863900, 1579864200, 1579864500,
1579864800, 1579865100, 1579865400, 1579865700, 1579866000,
1579866300, 1579866600, 1579866900, 1579867200, 1579867500,
1579867800, 1579868100, 1579868400, 1579868700, 1579869000,
1579869300, 1579869600, 1579869900, 1579870200, 1579870500,
1579870800, 1579871100, 1579871400, 1579871700, 1579872000,
1579872300, 1579872600, 1579872900, 1579873200, 1579873500,
1579873800, 1579874100, 1579874400, 1579874700, 1579875000,
1579875300, 1579875600, 1579875900, 1579876200, 1579876500,
1579876800, 1579877100, 1579877400, 1579877700, 1579878000),
tzone = "",
tclass = c("POSIXct", "POSIXt")),
class = c("xts", "zoo"))