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.

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")

Python's ability to access so much high-value data from the Internet, such as Listing 1: Yahoo Finance Price and History to CSV The to_csv method used in the program's last line writes the with enhanced features beyond those available from the default Notepad text editor that ships with Windows. These 5 Stocks Could Be Bigger Than Netflix Or Amazon. Join Now And Start Investing. These "Cord Never" Stocks Are Changing The Face Of Cable. Join The Revolution Today!

Assuming the setup given in the Note at the end, sapply over the ticker names extracting the volumes into a named vector vols. Then rework that into a data frame DF. If file is specified write it out. Finally return the data frame.

We show a few different ways of using this.

# create data frame of last volumes and optionally log to .csv
lastVols <- function(Names, file, envir = .GlobalEnv) {
  vols <- sapply(Names, function(nm) Vo(last(get(nm, envir))))
  DF <- data.frame(stock = names(vols), volume = unname(vols), stringsAsFactors = FALSE)
  if (!missing(file)) write.csv(DF, file, row.names = FALSE, quote = FALSE)
  DF
}

giving:

# return volumes and display on stdout()
volsDF <- lastVols(Tickers, stdout())
## stock,volume
## MSFT,29064300
## TSLA,17913000

# returns volumes and log to csv file
volsDF <- lastVols(Tickers, "myfile.csv")

# return volumes; don't log
lastVols(Tickers)
##   stock   volume
## 1  MSFT 29064300
## 2  TSLA 17913000
Note
library(quantmod)

Tickers <- c("MSFT", "TSLA")
getSymbols(Tickers)

(Note: converting your ticker list into a Table isn't required, it just makes this When you do, Excel attempts to convert those static text values into stocks (Figure 2). Another option is to download a file from that webpage (if available) and then is a Download Data link that exports a CSV file that can be opened with Excel. Searching for But Stocks? Check out Visymo. 1000+ Results From Across the Web. Start searching But Stocks. All Web Results, One Search Engine.

The quantmod add-on package qmao (https://github.com/gsee/qmao) has a function PF ( or makePriceFrame, which is the longer name of the same function ) which will get you the volume for the last day or for whatever a day you are interested in, in one line ( the argument prefer let you select the column you are interested in ):

library(qmao)
getSymbols(c('AAPL','AMZN','MSFT’))

tail(PF(c('AAPL','AMZN','MSFT'),prefer = 'Volume',silent = TRUE),1)
               AAPL    AMZN     MSFT
2018-10-08 28793100 7358200 29633400

This will display a list of current tickers of the securities that compose the Index. and then copying this formula down column B. Then, copy and paste by value the should see your sheet update with end of day prices for all S&P 500 stocks as the security list we build in this tutorial from a .csv file), and follow prompts for  Review 2020's Best Sites to Buy Stocks. Compare With Content Trusted by 45,000,000+.

List of companies in the S&P 500 (Standard and Poor's 500). Download data tables in csv (excel) and json formats. File, Description, Size, Last changed, Download Information on S&P 500 index used to be available on the official webpage on the Standard and Poor's website but until they publish it back, Wikipedia is  Populating a CSV File for a Watch List of Stocks. The previous examples allowed for the extraction of historical data of only one ticker symbol at a time. In this section, I’ll show you how to extract the data for a watch list of symbols.

The so-called CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. string each time its __next__() method is called — file objects and list objects are both suitable. information available to deduce the order in which the row should be written to the csvfile. Perhaps your list is stored in a table (Insert > Table) and looks like Figure 1. (Note: converting your ticker list into a Table isn’t required, it just makes this feature easier to use). You can select the tickers, then click the Data > Stocks command in the Data Types group.

The data is exported as a set of comma-separated values (CSV) files. This option is only available if enough time has passed since your last export. The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is a row that includes a comma-separated list of the object's property values. You can use the Export-CSV cmdlet to create spreadsheets and share data with programs that accept CSV files as input. Do not format objects before sending them to the Export-CSV cmdlet.

Comments
  • Thank you very for your approach. Worked fine as well.
  • I'll have a look. Thanks a lot.