I have the following data:
dput(head(trade.wide,10))
structure(c(54.7, 54.5, 54.5, 54.6, 54.65, 54.6, 54.65, 54.65,
54.65, 54.7), .indexCLASS = c("POSIXct", "POSIXt"), .indexTZ = "", tclass = c("POSIXct",
"POSIXt"), tzone = "", class = c("xts", "zoo"), index = structure(c(1459482300,
1459482302, 1459482305, 1459482306, 1459482307, 1459482308, 1459482312,
1459482314, 1459482315, 1459482317), tzone = "", tclass = c("POSIXct",
"POSIXt")), .Dim = c(10L, 1L), .Dimnames = list(NULL, "PRICE"))
dput(tail(trade.wide,10))
structure(c(84.15, 84.1, 84.1, 84.05, 84.1, 84.05, 84, 84.1,
84.1, 84.2), .indexCLASS = c("POSIXct", "POSIXt"), .indexTZ = "", tclass = c("POSIXct",
"POSIXt"), tzone = "", class = c("xts", "zoo"), index = structure(c(1472637583,
1472637584, 1472637585, 1472637586, 1472637588, 1472637595, 1472637596,
1472637597, 1472637598, 1472637600), tzone = "", tclass = c("POSIXct",
"POSIXt")), .Dim = c(10L, 1L), .Dimnames = list(NULL, "PRICE"))
I am trying to estimate high and low over 30 minute intervals from start (9:15:00).
df.OHLC<-to.period(trade.wide,period = "minutes", k=30, indexAt="startof")
This is what I get:
head(df.OHLC,10)
trade.wide.Open trade.wide.High trade.wide.Low trade.wide.Close
2016-04-01 09:15:00 54.70 54.85 54.05 54.65
2016-04-01 09:30:07 54.65 56.50 54.65 56.05
2016-04-01 10:00:02 56.15 56.15 55.75 55.85
2016-04-01 10:30:03 55.80 56.20 55.70 56.10
2016-04-01 11:00:12 56.10 56.35 55.75 55.75
2016-04-01 11:30:12 55.75 55.80 55.40 55.50
2016-04-01 12:00:20 55.50 55.70 55.45 55.60
2016-04-01 12:30:24 55.55 55.75 55.25 55.50
2016-04-01 13:00:10 55.50 56.40 55.35 55.90
2016-04-01 13:30:17 55.85 57.35 55.75 57.20
However, it has timestamps 09:15:00, 09:30:07, 10:00:02,... What I need is 09:15:00, 09:45:00, 10:15:00,...
I have also tried period.max() function but it also has similar issues.
df.OHLC1<- do.call(rbind, lapply(split(trade.wide, "days"),function(x) period.max(x,endpoints(x,on= "minutes",k=30))))
head(df.OHLC1,10)
[,1]
2016-04-01 09:29:59 54.85
2016-04-01 09:59:56 56.50
2016-04-01 10:29:53 56.15
2016-04-01 10:59:59 56.20
2016-04-01 11:29:54 56.35
2016-04-01 11:59:52 55.80
2016-04-01 12:29:59 55.70
2016-04-01 12:59:54 55.75
2016-04-01 13:29:45 56.40
2016-04-01 13:59:59 57.35
tail(df.OHLC1)
[,1]
2016-08-31 13:29:59 86.55
2016-08-31 13:59:56 86.30
2016-08-31 14:29:59 85.85
2016-08-31 14:59:59 85.15
2016-08-31 15:29:58 84.90
2016-08-31 15:30:00 84.20
I wonder why these functions divide time disproportionately? Kindly help me solve this problem. Thanks
Using you sample data, I get at first:
trade.wide <- readRDS("sample")
df.OHLC <- to.period(trade.wide, period = "minutes", k = 30, indexAt = "startof")
head(df.OHLC, n = 4)
## trade.wide.Open trade.wide.High trade.wide.Low trade.wide.Close
## 2016-04-01 05:45:00 54.70 54.85 54.05 54.65
## 2016-04-01 06:00:07 54.65 56.50 54.65 56.05
## 2016-04-01 06:30:02 56.15 56.15 55.75 55.85
## 2016-04-01 07:00:03 55.80 56.20 55.70 56.10
The time stamps given are actually the first ones in the interval that are present in trade.wide. You can align these to the actual boundaries of the intervals using align.time():
aligned <- align.time(df.OHLC, n = 30*60)
head(aligned, n = 4)
## trade.wide.Open trade.wide.High trade.wide.Low trade.wide.Close
## 2016-04-01 06:00:00 54.70 54.85 54.05 54.65
## 2016-04-01 06:30:00 54.65 56.50 54.65 56.05
## 2016-04-01 07:00:00 56.15 56.15 55.75 55.85
## 2016-04-01 07:30:00 55.80 56.20 55.70 56.1
In aligned, the rows are labeled by the time at the end of the interval, because align.time rounds up to the next 30 multiple of 30 minutes. If you want to label them with the time at the beginning of the interval, you need to subtract 30 minutes from the time stamps as follows:
index(aligned) <- index(aligned) - 30*60
head(aligned, n = 4)
## trade.wide.Open trade.wide.High trade.wide.Low trade.wide.Close
## 2016-04-01 05:30:00 54.70 54.85 54.05 54.65
## 2016-04-01 06:00:00 54.65 56.50 54.65 56.05
## 2016-04-01 06:30:00 56.15 56.15 55.75 55.85
## 2016-04-01 07:00:00 55.80 56.20 55.70 56.10
(see How can I alter a time series (XTS or ZOO) in R?)
The intervals are chosen as 00:00, 00:30, 01:00, etc. If you want intervals like 00:15, 00:45, 01:15, etc., you could start by subtracting 15 minutes from the time stamps and then follow a procedure similar to the above:
index(trade.wide) <- index(trade.wide) - 15*60
df.OHLC <- to.period(trade.wide, period = "minutes", k = 30, indexAt = "startof")
aligned <- align.time(df.OHLC, n = 30*60)
index(aligned) <- index(aligned) - 15*60
head(aligned, n = 4)
## trade.wide.Open trade.wide.High trade.wide.Low trade.wide.Close
## 2016-04-01 05:45:00 54.70 55.65 54.05 55.65
## 2016-04-01 06:15:00 55.60 56.50 55.30 55.90
## 2016-04-01 06:45:00 55.85 56.20 55.70 55.85
## 2016-04-01 07:15:00 55.85 56.35 55.70 55.90