5. Downloading the full S&P 500: web scraping and data cleaning

Level 3: Market Data and APIs

So far you have worked with a single ticker. But comparing strategies, building a screener or simply researching the market as a whole requires working with hundreds of assets at once — and nobody is going to type out the list of the 500 S&P 500 tickers by hand.

The up-to-date list of S&P 500 constituents is published on Wikipedia, and it can be extracted automatically with web scraping: downloading the page's HTML and looking inside it for the table that holds the tickers, using the BeautifulSoup library.

import requests
import bs4 as bs

response = requests.get("http://en.wikipedia.org/wiki/List_of_S%26P_500_companies")
soup = bs.BeautifulSoup(response.text, "lxml")
table = soup.find("table", {"class": "wikitable sortable"})

tickers = []
for row in table.findAll("tr")[1:]:
    ticker = row.findAll("td")[0].text.replace("\n", "")
    tickers.append(ticker)

The [1:] in findAll('tr')[1:] discards the first row of the table, which is the header with the column names, not a real ticker. With the full list of tickers in hand, downloading all the prices at once is as simple as passing the whole list to yfinance instead of a single string:

import yfinance as yf
import datetime as dt

start = dt.datetime(2018, 11, 1)
end = dt.datetime(2020, 1, 31)

data = yf.download(tickers, start=start, end=end)["Adj Close"]

The result is a single DataFrame with one column per company. And here comes the almost inevitable problem of working with hundreds of real assets: missing data. Companies that went public after your start date, that were removed from the index before your end date, or plain errors from the data source, leave gaps (NaN) in the table.

pandas offers dropna() to clean up those gaps, but with important nuances depending on the arguments you pass:

# drops columns (companies) that have NO data at all over the whole range
clean_data = data.dropna(axis="columns", how="all")

# drops rows (days) that have NO data at all for any company
clean_data = clean_data.dropna(axis="index", how="all")

# drops columns that have AT LEAST ONE missing value, on any row
clean_data = clean_data.dropna(axis="columns", how="any")

The order matters, and so does the how criterion. how='all' is conservative: it only deletes if ALL the values in that row or column are missing. how='any' is aggressive: it deletes as soon as a SINGLE value is missing. Applying how='any' as the first step across 500 companies would normally leave you with very few columns, because a company only needs one day without data to be dropped entirely — which is why it is applied at the end, after everything clearly empty has already been discarded.

Once clean, you can select specific subsets of the table with iloc, which indexes by numeric position rather than by name:

data.iloc[0]           # the first row (the first day)
data.iloc[:, 0]        # the first column (the first company)
data.iloc[0:5, 0:2]    # the first 5 rows and the first 2 columns

With the table cleaned, computing the log returns of all 500 companies at once is, once again, a single-line vectorized operation thanks to NumPy and pandas working together:

import numpy as np

returns = np.log(clean_data).diff().dropna()