cbind function creates misaligned xts object

Question

I am trying to run the code in a tutorial by rbresearch titled 'Low Volatility with R', however when trying to run the cbind function the time series seems to get totally misaligned.

Here's the data-preparation section that works perfectly:

require(quantmod)

symbols = c("XLY", "XLP", "XLE", "XLF", "XLV", "XLI", "XLK", "XLB", "XLU")
getSymbols(symbols, index.class=c("POSIXt","POSIXct"), from='2000-01-01')

for(symbol in symbols) {
  x<-get(symbol)
  x<-to.monthly(x,indexAt='lastof',drop.time=TRUE)
  indexFormat(x)<-'%Y-%m-%d'
  colnames(x)<-gsub("x",symbol,colnames(x))
  assign(symbol,x)
}

for(symbol in symbols) {
  x <- get(symbol)
  x1 <- ROC(Ad(x), n=1, type="continuous", na.pad=TRUE)
  colnames(x1) <- "ROC"
  colnames(x1) <- paste("x",colnames(x1), sep =".")
  #x2 is the 12 period standard deviation of the 1 month return
  x2 <- runSD(x1, n=12)
  colnames(x2) <- "RANK"
  colnames(x2) <- paste("x",colnames(x2), sep =".")
  x <- cbind(x,x2)
  colnames(x)<-gsub("x",symbol,colnames(x))
  assign(symbol,x)
}

rank.factors <- cbind(XLB$XLB.RANK, XLE$XLE.RANK, XLF$XLF.RANK, XLI$XLI.RANK,
  XLK$XLK.RANK, XLP$XLP.RANK, XLU$XLU.RANK, XLV$XLV.RANK, XLY$XLY.RANK)

r <- as.xts(t(apply(rank.factors, 1, rank)))

for (symbol in symbols){
  x <- get(symbol)
  x <- x[,1:6]
  assign(symbol,x)
}

To illustrate that the XLE ETF data data is aligned with the XLE Ranked data:

> head(XLE)
            XLE.Open XLE.High XLE.Low XLE.Close XLE.Vodalume XLE.Adjusted
 2000-01-31    27.31    29.47   25.87     27.31    5903600        22.46
 2000-02-29    27.31    27.61   24.62     26.16    4213000        21.51
 2000-03-31    26.02    30.22   25.94     29.31    8607600        24.10
 2000-04-30    29.50    30.16   27.52     28.87    5818900        23.74
 2000-05-31    29.19    32.31   29.00     32.27    5148800        26.54
 2000-06-30    32.16    32.50   30.09     30.34    4563100        25.07
> nrow(XLE)
[1] 163
> head(r$XLE.RANK)
            XLE.RANK
 2000-01-31        2
 2000-02-29        2
 2000-03-31        2
 2000-04-30        2
 2000-05-31        2
 2000-06-30        2
nrow(r$XLE.RANK)
[1] 163

However after running the following cbind function, the xts object becomes totally misaligned:

> XLE <- cbind(XLE, r$XLE.RANK)
> head(XLE)
           XLE.Open XLE.High XLE.Low XLE.Close XLE.Volume XLE.Adjusted XLE.RANK
2000-01-31    27.31    29.47   25.87     27.31    5903600        22.46       NA
2000-01-31       NA       NA      NA        NA         NA           NA        2
2000-02-29    27.31    27.61   24.62     26.16    4213000        21.51       NA
2000-02-29       NA       NA      NA        NA         NA           NA        2
2000-03-31    26.02    30.22   25.94     29.31    8607600        24.10       NA
2000-03-31       NA       NA      NA        NA         NA           NA        2
> nrow(XLE)
[1] 326

Since running pre-existing code seldom works for me, I suspect there's something wrong with my R console, so here's my session information:

> sessionInfo()
R version 3.0.1 (2013-05-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_Canada.1252  LC_CTYPE=English_Canada.1252    LC_MONETARY=English_Canada.1252 LC_NUMERIC=C                   
[5] LC_TIME=English_Canada.1252    

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

other attached packages:
 [1] timeSeries_3010.97         timeDate_3010.98           quantstrat_0.7.8           foreach_1.4.1             
 [5] blotter_0.8.14             PerformanceAnalytics_1.1.0 FinancialInstrument_1.1.9  quantmod_0.4-0            
 [9] Defaults_1.1-1             TTR_0.22-0                 xts_0.9-5                  zoo_1.7-10                

loaded via a namespace (and not attached):
[1] codetools_0.2-8 grid_3.0.1      iterators_1.0.6 lattice_0.20-15 tools_3.0.1

I'm completely unsure how to properly align the xts object without the NA and would greatly appreciate any help.

Works fine for me (although _holy cow_ was installing `quantstrat` an involved process). All I can think of is that somehow you have a strange `cbind` method floating around that's being called by mistake. — joran, Jul 07 '13 at 02:48
Thanks for confirming that! A buddy of mine ran it and it worked perfectly for him as well. I decided to uninstall and re-install R, install the quantstrat package only to face the same exact error. Almost none of the code that is uploaded online works for me. I am convinced something is wrong with either the R software or my new laptop. — , Jul 07 '13 at 03:45
That didn't work. I also tried using an older version of R, 2.15 but that didn't help either. — , Jul 07 '13 at 06:48
There has been some discussion [here](http://stackoverflow.com/questions/16706335/upgraded-quantstrat-0-7-8-from-0-7-7-then-old-code-does-not-work) and [here](http://stackoverflow.com/questions/17120496/guy-yollins-quantstrat-i-lecture-issue) about problems with older quanstrat examples, due to code being updated pretty rapidly. May want to see if either apply to your situation. — Thomas, Jul 07 '13 at 06:58
This doesn't have anything to do with quantstrat because the question doesn't run any quantstrat code except for `stock` and `currency`, which aren't necessary. I don't understand how the `cbind` call could be working for anyone though... — Joshua Ulrich, Jul 07 '13 at 12:55

score 1 · Accepted Answer · answered Jul 07 '13 at 13:19

I don't see how anyone was not able to replicate your issue. The problem is this line:

r <- as.xts(t(apply(rank.factors, 1, rank)))

The first for loop converts the data to monthly and drops the time component of the index, which converts the index to a Date. This means that rank.factors has a Date index. But as.xts creates a POSIXct index by default, so r will have a POSIXct index.

cbind(XLE, r$XLE.RANK) is merging an xts object with a Date index with an xts object with a POSIXct index. The conversion from POSIXct to Date can be problematic if you're not very careful with timezone settings.

If you don't need the time component, it's best to avoid POSIXct and just use Date. Therefore, everything should work if you set dateFormat="Date" in your as.xts call.

R> r <- as.xts(t(apply(rank.factors, 1, rank)), dateFormat="Date")
R> XLE <- cbind(XLE, r$XLE.RANK)
R> head(XLE)
           XLE.Open XLE.High XLE.Low XLE.Close XLE.Volume XLE.Adjusted XLE.RANK
2000-01-31    27.31    29.47   25.87     27.31    5903600        22.46        2
2000-02-29    27.31    27.61   24.62     26.16    4213000        21.51        2
2000-03-31    26.02    30.22   25.94     29.31    8607600        24.10        2
2000-04-30    29.50    30.16   27.52     28.87    5818900        23.74        2
2000-05-31    29.19    32.31   29.00     32.27    5148800        26.54        2
2000-06-30    32.16    32.50   30.09     30.34    4563100        25.07        2

Thanks so much Josh, that resolved the issue. I must admit, I was just about to reformat my laptop and in the likely event that it did not resolve the issue, I would have lost all hope in using R. — , Jul 07 '13 at 20:23

cbind function creates misaligned xts object

1 Answers1