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
>
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.