Divide two xts time series in for loop maintaining xts time

Question

I have multiple xts time series where the index() is the same for all. I would like to divide two series and insert them in an empty matrix and then apply this to other pairs and maintain the time series.

I have tried various ways in the loop: such as simple dividing of the columns close.price.ratio[,i] <- close.price[,cor.ticker[i,1]]/close.price[,cor.ticker[i,2]] and mapply('/',close.price['APC.Close'],close.price['CVX.Close'],SIMPLFY=F). However the close.price.ratio matrix always loses the indexing and is not a xts series.

Just dividing two columns results in keeping the xts indexing. Grateful for any help.

> head(close.price)
           KO.Close PEP.Close EOG.Close NBL.Close CVX.Close XOM.Close COG.Close APC.Close APA.Close
2014-01-02    40.66     82.10    165.02     66.59    124.14     99.75     38.17     78.56     85.48
2014-01-03    40.46     82.24    164.56     66.14    124.35     99.51     37.95     78.31     85.54
2014-01-06    40.27     82.28    164.02     65.75    124.02     99.66     38.20     78.34     86.31
2014-01-07    40.39     83.48    166.85     65.85    125.07    101.07     38.87     79.84     87.90
2014-01-08    39.94     83.24    166.78     65.71    123.29    100.74     38.75     79.04     86.65
2014-01-09    39.73     82.85    167.00     65.35    123.29     99.76     37.44     79.08     86.09

str(close.price)
An 'xts' object on 2014-01-02/2014-10-03 containing:
  Data: num [1:191, 1:9] 40.7 40.5 40.3 40.4 39.9 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:9] "KO.Close" "PEP.Close" "EOG.Close" "NBL.Close" ...
  Indexed by objects of class: [Date] TZ: UTC
  xts Attributes:  
List of 2
 $ src    : chr "yahoo"
 $ updated: POSIXct[1:1], format: "2014-10-06 18:16:04"
>

My code trying to divide the pairs according to the following vector

head(cor.ticker)
     [,1]        [,2]           
[1,] "PEP.Close" "KO.Close" 
[2,] "CVX.Close" "KO.Close"  
[3,] "APC.Close" "KO.Close"   
[4,] "APA.Close" "KO.Close"   
[5,] "EOG.Close" "PEP.Close" 
[6,] "CVX.Close" "PEP.Close"

close.price.ratio <- matrix(,nrow=nrow(close.price),ncol=nrow(cor.ticker))
for (i in 1:nrow(cor)){
  close.price.ratio[,i] <- close.price[,cor.ticker[i,1]]/close.price[,cor.ticker[i,2]] 
  colnames(close.price.ratio) <- c(cor.ticker[,3])
}
> head(close.price.ratio)
       PEP.KO   CVX.KO   APC.KO   APA.KO  EOG.PEP  CVX.PEP  
[1,] 2.019183 3.053123 1.932120 2.102312 2.009988 1.512058 
[2,] 2.032625 3.073406 1.935492 2.114187 2.000973 1.512038 
[3,] 2.043208 3.079712 1.945369 2.143283 1.993437 1.507292 
[4,] 2.066848 3.096559 1.976727 2.176281 1.998682 1.498203 
[5,] 2.084126 3.086880 1.978968 2.169504 2.003604 1.481139 
[6,] 2.085326 3.103197 1.990435 2.166876 2.015691 1.488111

resulting in a not xts matrix. Trying to define rownames did not work either. Many thanks!

You correctly pre-allocate close.price.ratio, but you do so as a matrix, not an xts object. close.price.ratio[,i] <- is not going to coerce close.price.ratio to an xts object just because the RHS of the expression happens to be one. — Joshua Ulrich

jlhoward jlhoward · Accepted Answer · 2014-10-07T03:56:26

This seems to work.

get.column <- function(i) close.price[,corr.ticker[i,1]]/close.price[,corr.ticker[i,2]]
result     <- do.call(cbind,lapply(1:nrow(corr.ticker),get.column))
names(result) <- corr.ticker[,1]
result
#            PEP.Close CVX.Close APC.Close APA.Close EOG.Close CVX.Close
# 2014-01-02  2.019183  3.053123  1.932120  2.102312  2.009988  1.512058
# 2014-01-03  2.032625  3.073406  1.935492  2.114187  2.000973  1.512038
# 2014-01-06  2.043208  3.079712  1.945369  2.143283  1.993437  1.507292
# 2014-01-07  2.066848  3.096559  1.976727  2.176281  1.998682  1.498203
# 2014-01-08  2.084126  3.086880  1.978968  2.169504  2.003604  1.481139
# 2014-01-09  2.085326  3.103197  1.990435  2.166876  2.015691  1.488111

This also works and might be a little faster.

do.call(cbind,mapply(function(x,y) close.price[,x]/close.price[,y],
                     corr.ticker[,1],corr.ticker[,2],SIMPLIFY=FALSE))

I think your problem is that, in the statement

close.price.ratio[,i] <- close.price[,cor.ticker[i,1]]/close.price[,cor.ticker[i,2]]

the RHS is evaluated as an xts object but coerced to a matrix because of the way you have written the LHS. Using do.call(cbind,lapply(...)) avoids that by creating the columns separately and binding them together at the end. Note that cbind.xts(...) is the same as merge.xts(...) - it matches based on index(...).

Divide two xts time series in for loop maintaining xts time

1 Answers