Search code examples
rfor-loopmergextsquantmod

merge.xts on specific columns over a for loop

I have downloaded a lot of stock data and need to compare it to a relevant index. To do this I need to merge the time series data (adjusted closing price column only) before I start calculating my log returns.

I have tried doing it in the following way with a for loop: please note that I have downloaded all the data into separate environments because it is a lot of data with many stock tickers.

require(quantmod)
e<-new.env()
tickers<-c("GE","BMW.DE","NOVO-B.CO","1COV.DE")
getSymbols("^GSPC",from="2018-01-01")
getSymbols(tickers,from="2019-01-01",env=e)

Now I want to take the 6th column (adjusted closing price) of each element in environment e and apply merge.xts(join="inner") to the column to combine it with the adjusted closing price of GSPC. I choose join="inner" to make sure that I only have observations with matching xts dates.

for(ticker in tickers){
x<-get(ticker,envir=e)[,6]
merged_prices<-merge.xts(x,GSPC[,6])
}

I get an error when I try to do this, I have also tried to create a data frame before running the for loop but I can't get it to work.

Thanks in advance.

Solution

  • You're on the right track by using an environment. Good job!

    You want to extract the adjusted close column from each symbol and merge them into one xts object. You're on the right track with your for loop, but there's an easier way. You can loop over the elements in an environment using lapply(), and use the Ad() function to extract the adjusted close column. Then use do.call(merge, ...) to call the merge function on the output of lapply().

    merged_prices <- do.call(merge, lapply(e, Ad))

    You can merge the adjusted close for GSPC at this point (merge(Ad(GSPC), merged_prices)), or you could include it in your list of tickers.