I have been trying to bind together stock data I gather from the quantmod package in R, but the package gives back the data in a XTS format, which is kind of hard to turn into a data frame and bind them all together into a single large data frame.
So far I have done this. I understand the logic, I understand what I have to do, I need to create an empty data frame with all the desired columns and then put individual stocks into the data frame, but it's hard to work with the XTS format.
I am also planning on binding together all S&P 500 stocks, which is why I need to do it in a loop and not some other way manually.
library(quantmod)
start <- as.Date("2000-01-01")
end <- as.Date("2020-04-15")
symbolBasket <- c('MMM', 'AXP', 'AAPL', 'BA', 'CAT', 'MSFT', 'IBM')
empty_df <- data.frame(Open= numeric(),
High = numeric(),
Low = numeric(),
Close = numeric(),
Volume = numeric(),
Adjusted = numeric(),
Ticker = character())
for (i in symbolBasket) {
xts <- as.data.frame(getSymbols(i, src = "yahoo", from = start, to = end))
df <- as.data.frame(xts)
ticker <- i
df_with_ticker <- cbind(df,ticker)
df_final <- rbind(empty_df,df)
}
One approach would be using the built-in function getSymbols with the env = argument.
We can use a simple for loop to extract the data as a data.frame from the environment and get the dates out of the rownames. rbindlist from data.table makes it easy to bind the final results together.
library(quantmod)
library(data.table)
stockEnv <- new.env()
getSymbols(symbolBasket, src='yahoo', env=stockEnv, from = start, to = end)
datalist <- list()
for(stock in ls(stockEnv)){
table <- as.data.frame(stockEnv[[stock]])
date = rownames(table)
rownames(table) <- NULL
colnames(table) <- c("Open","High","Low","Close","Volume","Adjusted")
bound.table <- data.frame(Ticker = stock, date ,table)
datalist[[stock]] <- bound.table
}
Result <- rbindlist(datalist,fill=TRUE)
Result
# Ticker date Open High Low Close Volume Adjusted
# 1: AAPL 2000-01-03 3.745536 4.017857 3.631696 3.997768 133949200 3.470226
# 2: AAPL 2000-01-04 3.866071 3.950893 3.613839 3.660714 128094400 3.177650
# 3: AAPL 2000-01-05 3.705357 3.948661 3.678571 3.714286 194580400 3.224152
# 4: AAPL 2000-01-06 3.790179 3.821429 3.392857 3.392857 191993200 2.945139
# 5: AAPL 2000-01-07 3.446429 3.607143 3.410714 3.553571 115183600 3.084645
# ---
#35710: MSFT 2020-04-07 169.589996 170.000000 163.259995 163.490005 62769000 163.490005
#35711: MSFT 2020-04-08 165.669998 166.669998 163.500000 165.130005 48318200 165.130005
#35712: MSFT 2020-04-09 166.360001 167.369995 163.330002 165.139999 51431800 165.139999
#35713: MSFT 2020-04-13 164.350006 165.570007 162.300003 165.509995 41905300 165.509995
#35714: MSFT 2020-04-14 169.000000 173.750000 168.000000 173.699997 52874300 173.699997