<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/downloading-sp-500-stock-data-from-googlequandl-with-r-command-line-script/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://ntguardian.wordpress.com/2017/07/10/downloading-sp-500-stock-data-google-quandl-r-command-line-script/"> R – Curtis Miller's Personal Website</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><strong>DISCLAIMER: Any losses incurred based on the content of this post are the responsibility of the trader, not me. I, the author, neither take responsibility for the conduct of others nor offer any guarantees. None of this should be considered as financial advice; the content of this article is only for educational/entertainment purposes.</strong></p>
<p>While most Americans have heard of the <a rel="nofollow" href="https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average" target="_blank">Dow Jones Industrial Average (DJIA)</a>, most people active in finance consider the <a rel="nofollow" href="http://www.investopedia.com/terms/s/sp500.asp" target="_blank">S&amp;P 500</a> stock index to be the better barometer of the overall American stock market. The 500 stocks included in the index are large-cap stocks seen as a leading indicator for the performance of stocks overall. Thus the S&amp;P 500 and its component stocks are sometimes treated as “the market.”</p>
<p><span></span></p>
<p>I wanted to download the historical price data (specifically closing price) for all the stocks included in the S&amp;P 500, which you can <a rel="nofollow" href="https://en.wikipedia.org/wiki/List_of_S%26P_500_companies" target="_blank">read on a Wikipedia list</a>. Services such as <a rel="nofollow" href="https://finance.google.com/" target="_blank">Google Finance</a> or <a rel="nofollow" href="https://www.quandl.com/" target="_blank">Quandl</a> provide data for individual companies and the index as a whole (or at least for <a rel="nofollow" href="https://www.google.com/finance?q=NYSEARCA:SPY" target="_blank">SPY</a>, the ticker symbol of the SPDR S&amp;P 500 ETF Trust, which is an index ETF that tracks the S&amp;P 500), but they don’t provide a utility to download closing prices for all symbols included in an index, nor will they provide the list. That said, once I have the list I can easily fetch the data for the symbols individually, then merge them together into one dataset.</p>
<p>I wrote an R script for doing this. The script executes the following steps:</p>
<ol>
<li>Scrape a list of symbols included in the S&amp;P 500 index from <a rel="nofollow" href="https://en.wikipedia.org/wiki/List_of_S%26P_500_companies" target="_blank">Wikipedia</a>, storing the list as a character vector.</li>
<li>Using either <strong>quantmod</strong> or the R package <strong>Quandl</strong> (both available from <a rel="nofollow" href="https://cran.r-project.org/" target="_blank">CRAN</a>), attempt to fetch (daily) price data for each symbol in the list created in step one in a loop for a certain date range. If no data for a symbol is available, ignore it.</li>
<li>Merge closing price data for each symbol into a single dataset.</li>
</ol>
<p>In addition to these steps, I made my script executable from the command line on Unix/Linux systems using the <strong>argparse</strong> package, further streamlining the process and hiding the R implementation details (so if you’re a Pythonista who doesn’t want to touch R beyond installing it and perhaps some dependencies, this script is still useful to you). I make no guarantees for Windows users; you may need to run the script by hand. By default, the script downloads data from Google, but in the command line you can instruct the script to get data from Quandl; you will need a Quandl API key if you do so. Quandl data can also be adjusted for stock splits and dividends if you so choose (Google data is adjusted automatically for stock splits only).</p>
<p>You can either apply an inner join data, or an outer join. Inner join will lead to no missing information in your CSV, but the resulting file will likely contain much less data than you wanted, since some symbols won’t have data until a few years ago, thus truncating the entire dataset. The default, outer join, will return a CSV with as much data as possible for each symbol, including <code>NA</code> when some symbols don’t have data extending as far back as specified.</p>
<p>Here is the script. Copy this into a <code>.R</code> file (I named my file <code>get-sp500-data.R</code>), make it executable (for example, <code>chmod +x get-sp500-data.R</code> on Ubuntu Linux), execute (for example, <code>./get-sp500-data.R</code> in Bash), and get that sweet, juicy data.</p>
<pre>
#!/usr/bin/Rscript
# get-sp500-data.R
# Author: Curtis Miller

suppressPackageStartupMessages(library(argparse))
suppressPackageStartupMessages(library(rvest))
suppressPackageStartupMessages(library(magrittr))
suppressPackageStartupMessages(library(tibble))
suppressPackageStartupMessages(library(quantmod))

# Argument parser for command line use
parser &lt;- ArgumentParser()
parser$add_argument(&quot;-v&quot;, &quot;--verbose&quot;, action = &quot;store_true&quot;, default = TRUE,
                    help = &quot;Print extra output [default]&quot;)
parser$add_argument(&quot;--quietly&quot;, action = &quot;store_false&quot;,
                    dest = &quot;verbose&quot;, help = &quot;Print little output&quot;)
parser$add_argument(&quot;-f&quot;, &quot;--file&quot;, type = &quot;character&quot;, dest = &quot;csv_name&quot;,
                    default = &quot;sp-500.csv&quot;,
                    help = &quot;CSV file to save data to [default: sp-500.csv]&quot;)
parser$add_argument(&quot;-s&quot;, &quot;--sleep&quot;, type = &quot;integer&quot;, dest = &quot;sleeptime&quot;,
                    default = 2,
                    help = paste(&quot;Time (seconds) between fetching symbols&quot;,
                                 &quot;[default: 2] (don't flood websites with&quot;,
                                 &quot;requests!)&quot;))
parser$add_argument(&quot;--inner&quot;, action = &quot;store_true&quot;, default = FALSE,
                    dest = &quot;inner&quot;,
                    help = paste(&quot;Inner join; only dates where all symbols&quot;,
                                 &quot;have data will be included&quot;))
parser$add_argument(&quot;--start&quot;, type = &quot;character&quot;, dest = &quot;start&quot;,
                    default = &quot;1997-01-01&quot;,
                    help = paste(&quot;Earliest date (YYYY-MM-DD) to include&quot;,
                                 &quot;[default: 1997-01-01]&quot;))
parser$add_argument(&quot;--end&quot;, type = &quot;character&quot;, dest = &quot;end&quot;,
                    default = &quot;today&quot;,
                    help = paste('Last date (YYYY-MM-DD or &quot;today&quot;) to',
                                 'include [default: &quot;today&quot;]'))
parser$add_argument(&quot;-k&quot;, &quot;--key&quot;, type = &quot;character&quot;, dest = &quot;api_key&quot;,
                    default = NULL,
                    help = &quot;Quandl API key, needed if getting Quandl data&quot;)
parser$add_argument(&quot;-q&quot;, &quot;--quandl&quot;, action = &quot;store_true&quot;, default = FALSE,
                    dest = &quot;use_quandl&quot;, help = &quot;Get data from Quandl&quot;)
parser$add_argument(&quot;-a&quot;, &quot;--adjust&quot;, action = &quot;store_true&quot;, default = FALSE,
                    dest = &quot;adjust&quot;, help = &quot;Adjust prices (Quandl only)&quot;)
parser$add_argument(&quot;--about&quot;, action = &quot;store_true&quot;, default = FALSE,
                    dest = &quot;about&quot;,
                    help = paste(&quot;Print information about the script and its&quot;,
                                 &quot;usage, then quit&quot;))

args &lt;- parser$parse_args()

join &lt;- &quot;outer&quot;
if (args$inner) {
  join &lt;- &quot;inner&quot;
}
verbose &lt;- args$verbose
start &lt;- args$start
if (args$end == &quot;today&quot;) {
  end &lt;- Sys.Date()
} else {
  end &lt;- args$end
}
sleeptime &lt;- args$sleeptime  # In seconds
csv_name &lt;- args$csv_name
api_key &lt;- args$api_key
use_quandl &lt;- args$use_quandl
adjust &lt;- args$adjust
about &lt;- args$about

if (about) {
  # Display a message, then quit
  comm_name &lt;- substring(commandArgs(trailingOnly = FALSE)[4], 8)
  cat(comm_name, &quot;\n(c) 2017 Curtis Miller\n&quot;,
      &quot;Licensed under GNU GPL v. 3.0 available at &quot;,
      &quot;https://www.gnu.org/licenses/gpl-3.0.en.html \n&quot;,
      &quot;E-mail: cgmil@msn.com\n\n&quot;,
      &quot;This script fetches closing price data for ticker symbols included &quot;,
      &quot;in the S&amp;P 500 stock index. A list of symbols included in the index &quot;,
      &quot;is fetched from this webpage:&quot;,
      &quot;https://en.wikipedia.org/wiki/List_of_S%26P_500_companies  The list &quot;,
      &quot;is parsed and the symbols included in the list are fetched from &quot;,
      &quot;either Google Finance (the default) or Quandl (which requires a &quot;,
      &quot;Quandl API key). If Quandl is the data source, adjusted data can be &quot;,
      &quot;fetched instead. The resulting data set is then saved to a CSV&quot;,
      &quot;file in the current working directory.\n\n&quot;,
      &quot;This package requires the following R packages be installed in order &quot;,
      &quot;to work (all of which are available from CRAN and can be downloaded &quot;,
      &quot;and installed automatically from R via the command &quot;,
      &quot;'install.packages(\&quot;package_name\&quot;)'):\n\n&quot;,
      &quot;* rvest\n&quot;,
      &quot;* magrittr\n&quot;,
      &quot;* quantmod\n&quot;,
      &quot;* Quandl\n&quot;,
      &quot;* tibble\n&quot;,
      &quot;* argparse (used only for the command line interface)\n\n&quot;,
      &quot;This script was written by Curtis Miller and was made available on &quot;,
      &quot;his website: https://ntguardian.wordpress.com\n\n&quot;,
      &quot;You can read more about this script in the following article: &quot;,
      &quot;https://ntguardian.wordpress.com/blog\n\n&quot;, sep = &quot;&quot;)
  quit()
}

start %&lt;>% as.Date
end %&lt;>% as.Date

options(&quot;getSymbols.warning4.0&quot;=FALSE)

sp500_wiki &lt;- read_html(
  &quot;https://en.wikipedia.org/wiki/List_of_S%26P_500_companies&quot;)

symbols_table &lt;- sp500_wiki %>%
  html_nodes(xpath='//*[@id=&quot;mw-content-text&quot;]/div/table[1]') %>%
  html_table()

symbols_table &lt;- symbols_table[[1]]
symbols &lt;- as.character(symbols_table$`Ticker symbol`)

if (use_quandl) {
  suppressPackageStartupMessages(library(Quandl))  # This will be needed
  Quandl.api_key(api_key)
}

sp500 &lt;- NULL
for (s in symbols) {
  Sys.sleep(sleeptime)
  if (verbose) {
    cat(&quot;Processing:&quot;, s, &quot;...&quot;)
  }
  tryCatch({
    if (use_quandl) {
      s_data &lt;- Quandl.datatable(&quot;WIKI/PRICES&quot;, ticker = c(s),
                                 date.gte = start, date.lte = end)
      rownames(s_data) &lt;- as.Date(s_data$date)
      if (adjust) {
        s_data &lt;- s_data[, &quot;adj_close&quot;, drop = FALSE]
      } else {
        s_data &lt;- s_data[, &quot;close&quot;, drop = FALSE]
      }
    } else {
      s_data &lt;- Cl(getSymbols(s, src=&quot;google&quot;, from = start, to = end,
                              env = NULL))
    }
    names(s_data) &lt;- s
    s_data %&lt;>% as.xts
    if (length(unique(s_data)) > 1) {    # Don't allow what is effectively
                                         # empty data
      if (is.null(sp500)) {
        sp500 &lt;- as.xts(s_data)
      } else {
        sp500 %&lt;>% merge(s_data, join = join)
      }
      if (verbose) {
        cat(&quot; Got it! From&quot;, start(s_data) %>% as.character, &quot;to&quot;,
            end(s_data) %>% as.character, &quot;\n&quot;)
      }
    } else if (verbose) {
      cat(&quot;Sorry, but not this one!\n&quot;)
    }
  }, error = function(e) {
    if (verbose) {
      cat(&quot;Sorry, but not this one!\n&quot;)
    }
  })
}

badsymbols &lt;- setdiff(symbols, names(sp500))
if (verbose &amp; (length(badsymbols) > 0)) {
  cat(&quot;There were&quot;, length(badsymbols),
      &quot;symbols for which data could not be obtained.\nThey are:&quot;, badsymbols,
      &quot;\n&quot;)
}

write.csv(rownames_to_column(as.data.frame(sp500), &quot;Date&quot;), file=csv_name)
</pre>
<p>Clearly this can be modified to work for other indices, which requires getting a different list to scrape. I’m sure Wikipedia has more lists that can easily substitute for the one I scraped. You could even construct a list of symbols by hand (though this could be a painful process). I’ll leave that up to you. If you have alternative lists of symbols for different indices, I would love to read your substitute for lines 107-112.</p>
<hr>
<p>I have created a video course that <a rel="nofollow" href="http://packtpub.com/" target="_blank">Packt Publishing</a> will be publishing later this month, entitled <a rel="nofollow" href="https://ntguardian.wordpress.com/video-courses/unpacking-numpy-and-pandas/" target="_blank"><em>Unpacking NumPy and Pandas</em></a>, the first volume in a four-volume set of video courses entitled, <em>Taming Data with Python; Excelling as a Data Analyst</em>. This course covers the basics of setting up a Python environment for data analysis with <a rel="nofollow" href="http://www.continuum.io/" target="_blank">Anaconda</a>, using Jupyter notebooks, and using <strong>NumPy</strong> and <strong>pandas</strong>. If you are starting out using Python for data analysis or know someone who is, please consider buying my course or at least spreading the word about it. You can <a rel="nofollow" href="https://www.packtpub.com/big-data-and-business-intelligence/unpacking-numpy-and-pandas-video" target="_blank">buy the course directly</a> or <a rel="nofollow" href="https://www.packtpub.com/mapt/" target="_blank">purchase a subscription to Mapt</a> and watch it there (when it becomes available).</p>
<p>If you like my blog and would like to support it, spread the word (if not get a copy yourself)! Also, stay tuned for future courses I publish with Packt at the <a rel="nofollow" href="https://ntguardian.wordpress.com/video-courses/" target="_blank">Video Courses</a> section of my site.</p>
<p> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/ntguardian.wordpress.com/3102/" target="_blank"><img border="0" alt="" src="http://feeds.wordpress.com/1.0/comments/ntguardian.wordpress.com/3102/"></a> <img border="0" alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" data-recalc-dims="1"></p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://ntguardian.wordpress.com/2017/07/10/downloading-sp-500-stock-data-google-quandl-r-command-line-script/"> R – Curtis Miller's Personal Website</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/8P5u58hMlE4" height="1"></a></div>