1

I have been trying to convert my data so I can get a xts data frame "data" with a time index and two colums Price and Volume. But so far I have had no luck with the code.

The data example can be found here. ftp://ftp.cmegroup.com/datamine_sample_data/ts/2012-11-05-e-mini-s-p-futures.csv

I only got to this stage so far: 

require(data.table); require(xts)
data=fread("2012-11-05-e-mini-s-p-futures.csv");      
data=data[,c(2,8,10),with=FALSE]
setnames(data,colnames(data),c('Time','Volume','Price'));

Then I have tried to work with the xts and POSIXct, but without any luck. Anyone got the magic fings to get it to work?

tonytonov
  • 25,060
  • 16
  • 82
  • 98
Wolfgang
  • 13
  • 2
  • This is my own Attempt: data=as.xts(data[,2:3,with=FALSE],unique=FALSE, order.by=fastPOSIXct(data[,Time],tz='GMT')) – Wolfgang Sep 15 '15 at 08:34

1 Answers1

0

I'm not using data.table, just base R for reading csv. Then I combine total datetime and parse it.

data2 <- read.csv("~/Downloads/2012-11-05-e-mini-s-p-futures.csv", head=TRUE)
data2$index <- paste(data2$T.Date, data2$T.Time)
datax <- xts(data2[, c("Volume", "T.Price")], 
             strptime(data2$index, "%Y%m%d %H:%M:%S"))
head(datax)
                    Volume T.Price
2012-11-05 00:00:01      1  1408.5
2012-11-05 00:00:01      7  1408.5
2012-11-05 00:00:01      1  1408.5
2012-11-05 00:00:01      1  1408.5
2012-11-05 00:00:01      8  1408.5
2012-11-05 00:00:01      6  1408.5
tonytonov
  • 25,060
  • 16
  • 82
  • 98
  • Thank you for your replay :) And thank you for the help. As a side question: How do I get it to start from 16:02 that day instead of midnight? – Wolfgang Oct 06 '15 at 08:35
  • You're welcome. Well, `xts` always arranges points according to time order. There's likely an error in your data: it has `20121105,23:59:50` followed by `20121105,00:00:01`, probably should be `20121106` instead. – tonytonov Oct 06 '15 at 10:07