Create xts index with both date & time

Question

I want to run some 1 minute data I get from a trading app through an R program I've written. So far all of my xts objects have just been daily bars from Yahoo, etc. How do I create the time part of the xts index? Note that Date & Time are in the first 2 columns. It is possible that there will be missing dates, it's pretty much guaranteed there will be missing minutes. There should not be any duplicates. (I'll check for that myself)

Thanks

library(timeDate)

TestData = structure(list(X = 1:6, Date = structure(c(1L, 1L, 1L, 1L, 1L, 
                          1L), .Label = "07/01/1998", class = "factor"), Time = structure(1:6, .Label = c("06:31", 
                          "06:34", "06:35", "06:36", "06:38", "06:39"), class = "factor"), 
                          Open = c(114.06, 114.11, 114.06, 114.09, 114.09, 114.06), 
                          High = c(114.06, 114.13, 114.13, 114.09, 114.09, 114.13), 
                          Low = c(114, 114.06, 114.06, 114.03, 114.06, 114.06), Close = c(114, 
                          114.06, 114.13, 114.03, 114.06, 114.13), Volume = c(257600L, 
                          24400L, 2500L, 900L, 3000L, 16700L)), .Names = c("X", "Date", 
                          "Time", "Open", "High", "Low", "Close", "Volume"), class = "data.frame", row.names = c(NA, 
                          -6L))


MyDates = as.Date(TestData$Date,format = "%m/%d/%Y")
MyTimes = as.vector(TestData$Time)

MyIndexes = timeDate(paste(MyDates, MyTimes), format = "%Y-%m-%d %H:%M", zone="UTC")

NewData = xts(TestData[,3:7], order.by = MyIndexes)

is.xts(NewData)

Thanks Dirk. I have it working now with the timeDate package and the edited code above. Cheers! — LGTrader, Jan 31 '14 at 18:48
Are you aware of the fact that the package in question, xts, has extensive documentation? — Dirk Eddelbuettel, Jan 31 '14 at 18:49
Absolutely. However I did not find an example of how to do this so I went elsewhere. This is my first time working with data with minutes. — LGTrader, Feb 01 '14 at 00:44

score 1 · Accepted Answer · answered Feb 01 '14 at 02:45

Here you go.

First, your data. Note the superfluous first columns first throws off the indices used in your code. Easily corrected though.

R> TestData
  X       Date  Time   Open   High    Low  Close Volume
1 1 07/01/1998 06:31 114.06 114.06 114.00 114.00 257600
2 2 07/01/1998 06:34 114.11 114.13 114.06 114.06  24400
3 3 07/01/1998 06:35 114.06 114.13 114.06 114.13   2500
4 4 07/01/1998 06:36 114.09 114.09 114.03 114.03    900
5 5 07/01/1998 06:38 114.09 114.09 114.06 114.06   3000
6 6 07/01/1998 06:39 114.06 114.13 114.06 114.13  16700
R>

Much worse is that you forced date and time to a factor type. Not a good idea:

R> sapply(TestData, class)
        X      Date      Time      Open      High       Low     Close    Volume 
"integer"  "factor"  "factor" "numeric" "numeric" "numeric" "numeric" "integer" 
R>

So when we work with, we first convert to character, then paste, then parse and lastly convert to as.POSIXct which is a type that xts is rather happy with.

R> x <- xts(TestData[,4:8],
+           order.by=as.POSIXct(strptime(paste(as.character(TestData[,2]),
+                                              as.character(TestData[,3])), 
+                                              "%m/%d/%Y %H:%M")))
R>

Et voila:

R> myxts
                      Open   High    Low  Close Volume
1998-07-01 06:31:00 114.06 114.06 114.00 114.00 257600
1998-07-01 06:34:00 114.11 114.13 114.06 114.06  24400
1998-07-01 06:35:00 114.06 114.13 114.06 114.13   2500
1998-07-01 06:36:00 114.09 114.09 114.03 114.03    900
1998-07-01 06:38:00 114.09 114.09 114.06 114.06   3000
1998-07-01 06:39:00 114.06 114.13 114.06 114.13  16700
R>

But you really should spend some time with the extensive documentation for xts and zoo.

Create xts index with both date & time

1 Answers1