Convert xts to data.frame

0
votes

I work on a code to collect data from the S&P500. I do get the data I need, but now I have for each stock a xts object that I want to convert to one big data.frame, so I can run an event study or create a chart.

url <- "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
tables <- getURL(url)

tables <- readHTMLTable(tables, stringsAsFactors = F)
sp500symbols <- tables[[1]][,1:2]
head(sp500symbols)
rm(list="tables", "url")
tix <- c(sp500symbols$`Ticker symbol`)

quantmod_list = tix
for(company in quantmod_list) {  
  try(getSymbols(company, from="2009-12-29", to="2009-12-30"))
  print(company)
}
2
rdataframexts
Welcome to Stack Overflow! To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a minimal reproducible example. – Mat

2 Answers

2
votes

quantmod works nicely with xts.

Maybe use xts instead of a data.frame to store your market data.

You can use merge.xts to join multiple stocks in one big xts object.

Have a look at it via ?merge.xts or here: https://www.rdocumentation.org/packages/xts/versions/0.10-1/topics/merge.xts

also - I found these similar questions & answers - they might help you:

the last one gives you a list with a data.frame of the market data in it:

library(BatchGetSymbols)

first.date <- Sys.Date()-365
last.date <- Sys.Date()

df.SP500 <- GetSP500Stocks()
tickers <- df.SP500$tickers

l.out <- BatchGetSymbols(tickers = tickers,
                     first.date = first.date,
                     last.date = last.date)

print(l.out$df.control)
print(l.out$df.tickers)
0
votes
    # generates a XTS-File wich will download the adjusted stockprices on a Daily base. 
url <- "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
tables <- getURL(url)

tables <- readHTMLTable(tables, stringsAsFactors = F)
sp500symbols <- tables[[1]][,1:2]
head(sp500symbols)
rm(list="tables", "url" )
tix <-c(sp500symbols$`Ticker symbol`)

dataEnv <- new.env()
quantmod_list = tix
for(company in quantmod_list){
  try(getSymbols(company, auto.assign = TRUE, src="yahoo", from="2018-01-01", to="2018-01-23",return.class = 'zoo', env=dataEnv))
  print(company)
}
plist <- eapply(dataEnv, Ad)
ad.Stocks <- do.call(merge, plist)  
str(ad.Stocks)

# generates the dataset "Stockreturns" 
Stockreturns <- data.frame(c(diff(log(ad.Stocks))))                       #calc. the stockreturns
write.csv2(Stockr, file = "Stockreturns.csv", row.names=FALSE, na = "")   #export the stockreturns into a csv file