Can't index xts class with Date type

Question

I'm loading some data manually (vs. via quantmod) and trying to create an xts class (which all seems to work ok), but I can't seem to use Date type indexes.

I am trying to find the intersection with another time series (NDX, via quantmod), which should be easy if I could use date indexes.

I'm assuming I'm missing something as a create the xts object but at a loss to know what.## Heading ##

VXN.tmp <- read.csv('VXNDailyPrices.csv', na.strings=c('n/a'), stringsAsFactors=F, header=F)
VXN <- xts(as.matrix(VXN.tmp[,-1]), as.Date(VXN.tmp[,1], tz="", format='%m/%d/%y'), tzone="")
colnames(VXN) <- c('Open', 'High', 'Low', 'Close')
VXN <- VXN[!is.na(Cl(VXN))]

> head(VXN)
          Open High Low Close
2001-02-02   NA   NA  NA 54.89
2001-02-05   NA   NA  NA 55.85
2001-02-06   NA   NA  NA 53.68
2001-02-07   NA   NA  NA 54.41
2001-02-08   NA   NA  NA 54.66
2001-02-09   NA   NA  NA 55.85
> VXN['2001-02-02']
           Open High Low Close
2001-02-02   NA   NA  NA 54.89
> VXN[as.Date('2001-02-02')]
     Open High Low Close
>

Some other info

> str(VXN)
An ‘xts’ object from 2001-02-02 to 2013-03-01 containing:
  Data: num [1:3040, 1:4] NA NA NA NA NA NA NA NA NA NA ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:4] "Open" "High" "Low" "Close"
  Indexed by objects of class: [Date] TZ: 
  xts Attributes:  
List of 2
 $ tclass: chr "Date"
 $ tzone : chr ""
> 
> vxnidx <- head(index(VXN), 20)
> dput(vxnidx)
structure(c(11355, 11358, 11359, 11360, 11361, 11362, 11365, 
11366, 11367, 11368, 11369, 11373, 11374, 11375, 11376, 11379, 
11380, 11381, 11382, 11383), class = "Date")
> 
> vxnidx[1]
[1] "2001-02-02"
> VXN[vxnidx[1]]
     Open High Low Close
> VXN["2001-02-02"]
           Open High Low Close
2001-02-02   NA   NA  NA 54.89
>

The data is from the CBOE here: http://www.cboe.com/micro/vxn/#historical

2/2/2001,n/a,n/a,n/a,54.89
2/5/2001,n/a,n/a,n/a,55.85
2/6/2001,n/a,n/a,n/a,53.68
2/7/2001,n/a,n/a,n/a,54.41
2/8/2001,n/a,n/a,n/a,54.66
2/9/2001,n/a,n/a,n/a,55.85
2/12/2001,n/a,n/a,n/a,57.05
2/13/2001,n/a,n/a,n/a,58.42
2/14/2001,n/a,n/a,n/a,57.49
2/15/2001,n/a,n/a,n/a,55.19
2/16/2001,n/a,n/a,n/a,55.34
2/20/2001,n/a,n/a,n/a,57.93
2/21/2001,n/a,n/a,n/a,59.57
2/22/2001,n/a,n/a,n/a,60.99
2/23/2001,n/a,n/a,n/a,62.03
2/26/2001,n/a,n/a,n/a,61.77
2/27/2001,n/a,n/a,n/a,63.27
2/28/2001,n/a,n/a,n/a,63.65
3/1/2001,n/a,n/a,n/a,64.32

edit: session info

> sessionInfo()
R version 2.15.2 (2012-10-26)
Platform: i386-apple-darwin9.8.0/i386 (32-bit)

locale:
[1] en_AU.UTF-8/en_AU.UTF-8/en_AU.UTF-8/C/en_AU.UTF-8/en_AU.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] PerformanceAnalytics_1.0.4.4 quantmod_0.3-17              TTR_0.21-1                  
[4] xts_0.8-6                    zoo_1.7-7                    Defaults_1.1-1              

loaded via a namespace (and not attached):
[1] grid_2.15.2     lattice_0.20-10
>

What version of R, xts, zoo, etc (i.e. the output from sessionInfo? — Joshua Ulrich
added in the session info: R 2.15.2, quantmod 0.3-17, zoo 1.7-7, xts 0.8-6 — dizzy

Joshua Ulrich Joshua Ulrich · Accepted Answer · 2013-03-02T14:23:26

This will throw a warning with newer versions of xts. Your packages are somewhat outdated, so you might try upgrading.

library(quantmod)
URL <- paste("http://www.cboe.com/publish/ScheduledTask",
  "/MktData/datahouse/vxncurrent.csv", sep="")
VXN.tmp <- read.csv(URL, na.strings='n/a', skip=2)
VXN.tmp[,1] <- as.Date(VXN.tmp[,1], tz="", format="%m/%d/%Y")
VXN <- xts(as.matrix(VXN.tmp[,-1]), VXN.tmp[,1], tzone="")
# Warning message:
# In xts(as.matrix(VXN.tmp[, -1]), VXN.tmp[, 1], tzone = "") :
#   ‘tzone’ setting ignored for Date indexes
VXN <- VXN[!is.na(Cl(VXN))]
VXN['2001-02-02']
#            Open High Low Close
# 2001-02-02   NA   NA  NA 54.89
VXN[as.Date('2001-02-02')]
#            Open High Low Close
# 2001-02-02   NA   NA  NA 54.89

Here's my sessionInfo:

R> sessionInfo()
R version 2.15.2 (2012-10-26)
Platform: x86_64-pc-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=C                 LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] quantmod_0.4-0 TTR_0.21-1     xts_0.9-3.2    zoo_1.7-10     Defaults_1.1-1

loaded via a namespace (and not attached):
[1] grid_2.15.2     lattice_0.20-13

The reason for the issue is that all xts index values are stored as POSIXct, so even though you convert the first column to a Date via as.Date(VXN.tmp[,1], ...) xts converts it to POSIXct. Further, since you don't specify a timezone, that will be determined by your OS (and can be particularly problematic).

You can try setting tzone="UTC" (because that's essentially what newer versions of xts do), but I can't tell you if that will work or not. It would be best to upgrade.

Can't index xts class with Date type

1 Answers