Bind Stock Returns with Differing Column Names-CodePudding

I am using the quantmod package in R to retrieve stock returns. Running getSymbols for a ticker stores the returns for that stock as an xts object in my R environment.

library(quantmod)
getSymbols(AAPL, TSLA, MSFT, HD)

I am looking for a way to run the above command, and then bind the stock returns together into one dataframe. I have converted all the xts objects to dataframes.

The issue is, each object contains its ticker in part of each column name, so the differing column names cannot be binded together. I could pretty easily just change the column names of each of these 4 data frames, but in the case that there are hundreds of stocks this approach doesn't work well.

Ex:

AAPL.Open  AAPL.High  AAPL.Low  AAPL.Close  AAPL.Volume  AAPL.Adjusted
TSLA.Open  TSLA.High  TSLA.Low  TSLA.Close  TSLA.Volume  TSLA.Adjusted
MSFT.Open  MSFT.High  MSFT.Low  MSFT.Close  MSFT.Volume  MSFT.Adjusted
HD.Open    HD.High    HD.Low    HD.Close    HD.Volume    HD.Adjusted

Ideally, there would also be a programmatic way to get the ticker from each xts object, and store in a column.

Desired output:

Index       Ticker Open    High   Low    Close   Volume     Adjusted
2022-11-04  AAPL   142.09  142.67 134.38 138.38  140716700 138.38
2022-11-04  TSLA   222.6   223.8  203.08 207.47  98453100  215.31
2022-11-04  MSFT   217.55  221.59 213.43 221.39  36767800  221.39
2022-11-04  HD     285.89  288.84 277.50 284.03  4401600   284.03

CodePudding user response：

You can use package yfR, it uses quantmod's retrieval functions, but it caches the downloads so if you rerun the code on the same day it will not download all the data again. The return is a data.frame with the first column containing the ticker. Read the package getting started vignette and other vignettes to see what else the package can do.

library(yfR)

tickers <- c("AAPL", "TSLA", "MSFT", "HD")

df_ticker <- yf_get(tickers)
# A tibble: 80 × 11
   ticker ref_date   price_open price_high price_low price_close    volume price_adjusted ret_adjus…¹ ret_cl…² cumre…³
 * <chr>  <date>          <dbl>      <dbl>     <dbl>       <dbl>     <dbl>          <dbl>       <dbl>    <dbl>   <dbl>
 1 AAPL   2022-10-10       140.       142.      139.        140.  74899000           140.   NA        NA         1    
 2 AAPL   2022-10-11       140.       141.      138.        139.  77033700           139.   -0.0103   -1.03e-2   0.990
 3 AAPL   2022-10-12       139.       140.      138.        138.  70433700           138.   -0.00461  -4.60e-3   0.985
 4 AAPL   2022-10-13       135.       144.      134.        143. 113224000           143.    0.0336    3.36e-2   1.02 
 5 AAPL   2022-10-14       144.       145.      138.        138.  88512300           138.   -0.0322   -3.22e-2   0.985
 6 AAPL   2022-10-17       141.       143.      140.        142.  85250900           142.    0.0291    2.91e-2   1.01 
 7 AAPL   2022-10-18       145.       147.      141.        144.  99136600           144.    0.00941   9.41e-3   1.02 
 8 AAPL   2022-10-19       142.       145.      142.        144.  61758300           144.    0.000765  7.65e-4   1.02 
 9 AAPL   2022-10-20       143.       146.      143.        143.  64522000           143.   -0.00327  -3.27e-3   1.02 
10 AAPL   2022-10-21       143.       148.      143.        147.  86464700           147.    0.0271    2.71e-2   1.05 
# … with 70 more rows, and abbreviated variable names ¹ret_adjusted_prices, ²ret_closing_prices,
#   ³cumret_adjusted_prices
# ℹ Use `print(n = ...)` to see more rows