Search code examples
rfunctionquantmod

Function to export last available value of list of stocks to .csv

I'm trying to export to a .csv some data coming from Quantmod.

What I'd like to export into a .csv is the name of the Ticker and the last data available in my list regarding the volume (Vol)

Let me explain my approach:

Load a list of tickers from a .csv into my environment

library(quantmod)
Tickers <- read.csv("nasdaq_tickers_list.csv", stringsAsFactors = FALSE)

getSymbols(Tickers$Tickers,from="2018-08-01", src="yahoo" )
stock_data = sapply(.GlobalEnv, is.xts)
all_stocks <- do.call(list, mget(names(stock_data)[stock_data]))

Let's have a look into for example the "Tail" of MSFT

            MSFT.Open   MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
2018-09-28    114.19    114.57   113.68     114.37    21647800        114.37
2018-10-01    114.75    115.68   114.73     115.61    18883100        115.61
2018-10-02    115.30    115.84   114.44     115.15    20787200        115.15
2018-10-03    115.42    116.18   114.93     115.17    16648000        115.17
2018-10-04    114.61    114.76   111.63     112.79    34821700        112.79
2018-10-05    112.63    113.17   110.64     112.13    29064300        112.13

If we use "last" we get the latest volume available data in my list

last(MSFT$MSFT.Volume)

             MSFT.Volume
2018-10-05    29064300

Now, I'd like to create a function to get the last available "Volume" data of all tickers in my list (all_stocks) exported into a .csv file.

The desired output is a .csv file with the Ticker name and the last Volume data.

MSFT,29064300
stock1,volume1
stock2,volume2

Any help on how to approach?

Thank you very much.

Solution

  • Created a function to get the last volume per stock. Then you can use lapply to get the data, but I wrapped it in Reduce with rbind to get everything nicely in a data.frame ready for writing to a csv.

    stock_last_volume <- function(x) {
  stock_name <- stringi::stri_extract(names(x)[1], regex = "^[A-Z]+")
  volume <- last(quantmod::Vo(x))
  colnames(volume) <- "volume"
  my_df <- data.frame(stock = stock_name, volume = volume, stringsAsFactors = FALSE, row.names = NULL)
  return(my_df)
}


all_volumes <- Reduce(rbind, lapply(all_stocks, stock_last_volume))

all_volumes[1:2, ]
  stock   volume
1  MSFT 29064300
2  GOOG  1184300

# write.csv to working directory
write.csv(all_volumes, file = "all_volumes.csv")