I am back-testing a trading strategy on the members of a stock index. I have historical stock price date for every years. I would like to merge these time-series objects. My problem is that the index constituents are not the same , therefore I could not simple rbind them. Merging is not an option as well, because it creates separate columns for the same stocks from different years. Could someone suggest me a solution?
Here is an example of my problem: xts1:
AAPL AMZ AA AXP
11/01/2020 100 85 90 70
12/01/2020 105 70 80 90
xts2:
AAPL AM AXP BA
01/01/2021 108 75 80 50
02/01/2021 110 60 70 60
final xts:
AAPL AMZ AA AXP BA
11/01/2020 100 85 90 70 NA
12/01/2020 105 70 80 90 NA
01/01/2021 108 75 NA 80 50
02/01/2021 110 60 NA 70 60
The order.by i.e. index of xts should be Date/Datetime class. According to `?xts
order.by - a corresponding vector of unique times/dates - must be of a known time-based class.
If the initial objects are xts and the dates are Date class, an option is to rbind after converting to data.frame with fortify.zoo
library(xts)
library(dplyr)
bind_rows(fortify.zoo(xt1), fortify.zoo(xt2))
Index AAPL AMZ AA AXP BA
1 2020-11-01 100 85 90 70 NA
2 2020-12-01 105 70 80 90 NA
3 2021-01-01 108 75 NA 80 50
4 2021-02-01 110 60 NA 70 60
If we want to reconvert to xts
out <- bind_rows(fortify.zoo(xt1), fortify.zoo(xt2))
xts(out[-1], order.by = out$Index)
AAPL AMZ AA AXP BA
2020-11-01 100 85 90 70 NA
2020-12-01 105 70 80 90 NA
2021-01-01 108 75 NA 80 50
2021-02-01 110 60 NA 70 60
xt1 <- structure(c(100L, 105L, 85L, 70L, 90L, 80L, 70L, 90L), .Dim = c(2L,
4L), .Dimnames = list(NULL, c("AAPL", "AMZ", "AA", "AXP")), index = structure(c(1604188800,
1606780800), tzone = "UTC", tclass = "Date"), class = c("xts",
"zoo"))
xt2 <- structure(c(108L, 110L, 75L, 60L, 80L, 70L, 50L, 60L), .Dim = c(2L,
4L), .Dimnames = list(NULL, c("AAPL", "AMZ", "AXP", "BA")), index = structure(c(1609459200,
1612137600), tzone = "UTC", tclass = "Date"), class = c("xts",
"zoo"))