Analyzing the Impact of Coronavirus on the Stock Market using Python, Google Sheets and Goo...

Since its emergence in Asia late 2019, the coronavirus COVID-19 pandemic has been devastating. The virus spread to most countries causing severe respiratory infections and many human casualties. The virus also put half of the world population in lockdown which resulted in a slowdown of the world economy and a fall in stock prices.

The goal of this tutorial is to introduce the steps for collecting and analyzing stock data in the context of the coronavirus pandemic. To do this, we will use Python, Google Sheets and Google Finance.

In section 2 of the the tutorial, we will see how to configure Google Sheets in order to be able to interact with them using Python. In section 3, we will see how to collect stocks data using Google Finance and how to store this data in Google Sheets using Python. In section 4, we will see how to read the data from Google Sheets and analyze it using Python and Pandas.

The source code for this tutorial can be found in this github repository .

2.1. Google Sheets Configuration in GCP

In order to access Google Sheets from Python, we need a private key from Google Cloud Platfrom (GCP) that we can obtain using the following steps.

• Step 1: Go to Google Cloud platform , log in using your google account and click on Console .

• Step 2: Click on Select a project > NEW PROJECT , enter the project name and click on CREATE

• Step 3: Click on APIs & Services > Dashboard > ENABLE APIS AND SEVICES .

• Step 4: Search for both Google Drive API and Google Sheets API and click on ENABLE

• Step 5: Inside the Google Sheets API page, click on MANAGE , CREATE CREDENTIALS and select Google Sheets API .

• Step 6: Choose Web sever , Application data and JSON type for the API key. Click Continue to download your private key in JSON format.

• Step 7: Once you download the JSON file, save in the same folder as your Jupyter notebook and copy the client_email information.

2.2. Google Sheets Configuration

As a last step, we need to create a new Google sheet and share it with the client_email that we created in the previous step. Open Google Drive, create a new Google Sheet, change its name to "stocks-data". Click on Share button, enter the client_email and click Send .

3. Collecting and Storing Stocks Data

Now that we have our google Sheets configured, we can start using Python and Jupyter Notebook to collect the data. We start by importing the different libraries that we need.

%matplotlib inline

import pandas as pd
import gspread
from google.oauth2.service_account import Credentials
from gspread_pandas import Spread, Client

import seaborn as sns 
import matplotlib.pyplot as plt

plt.style.use('fivethirtyeight')

Getting the list of S&P 500 companies from Wikipedia

As a first step in the data collection effort, we need to get the list of S&P 500 companies. To do this, we will use the following Wikipedia Page: https://en.wikipedia.org/wiki/List_of_S%26P_500_companies

Fortunatey, we can use Pandas to read the page, extract the table with S&P 500 companies and store them into a Pandas Dataframe.

url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'

stocks_df = pd.read_html(url, header=0)[0]

stocks_df.head()

#Number of companis
len(stocks_df)

We got 505 companies in our list, and not 500... This is because some companies have a dual-class stock structure and are listed more than once in the list. We can get the list of these companies by searching for the word "Class" in their security name.

stocks_df[stocks_df['Security'].str.contains("Class")]

We got 10 companies with dual-class structure in the list. If we take that into consideration, we can see that we have 500 unique companies in the list.

We can also check the number of companies by sector and sub industry.

stocks_df['GICS Sector'].value_counts()

Industrials               71
Information Technology    71
Financials                66
Consumer Discretionary    64
Health Care               60
Consumer Staples          33
Real Estate               31
Utilities                 28
Materials                 28
Energy                    27
Communication Services    26
Name: GICS Sector, dtype: int64

stocks_df['GICS Sub Industry'].value_counts()

Health Care Equipment       19
Electric Utilities          13
Semiconductors              13
Industrial Machinery        13
Packaged Foods & Meats      12
                            ..
Real Estate Services         1
Motorcycle Manufacturers     1
Multi-Sector Holdings        1
Hotel & Resort REITs         1
Drug Retail                  1
Name: GICS Sub Industry, Length: 128, dtype: int64

Adding stocks data from Google Finance

Now that we have the list of S&P 500 companies, we can add to our DataFrame Google Sheets formulas that will fetch from Google Finance stock prices and the number of outstanding shares for each company. Note that, these formulas will be executed once we save the Pandas DataFrame in our Google Sheet.

You can find the documentation of GOOGLEFINANCE formulas here: https://support.google.com/docs/answer/3093281

stocks_df["Price_1_1"] = stocks_df["Symbol"].apply(lambda x: '=INDEX(GOOGLEFINANCE("' + x + '","price", "1/1/2020"),2,2)')
stocks_df["Price_3_23"] = stocks_df["Symbol"].apply(lambda x: '=INDEX(GOOGLEFINANCE("' + x + '","price", "3/23/2020"),2,2)')
stocks_df["Price_4_9"] = stocks_df["Symbol"].apply(lambda x: '=INDEX(GOOGLEFINANCE("' + x + '","price", "4/9/2020"),2,2)')

Next, we add the formula to calculate the number of outstanding shares for each company. We can use this data with the stock prices to calculate the market cap of companies at the 3 different dates.

stocks_df["Shares"] = stocks_df["Symbol"].apply(lambda x: '=GOOGLEFINANCE("' + x + '","shares")')

Storing the DataFrame to Google Sheet

We start by creating a variable that contains our credentials that we got from Google Cloud Plaltform.

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = Credentials.from_service_account_file('./gsheet-stocks.json', scopes=scope)

Next, we read the empty google sheet in a variable that we call spread .

client = Client(scope=scope, creds=credentials)
spread = Spread("stocks_analysis", client=client)

We define the list of variables that we want to keep.

cols_to_keep = ["Symbol", "Security", "GICS Sector", "GICS Sub Industry", 
                "Price_1_1", "Price_3_23", "Price_4_9", "Shares"]

The last step is to save the DataFrame to Google Sheets.

spread.df_to_sheet(stocks_df[cols_to_keep])

If we go to Google Sheets, we can see that the data is correctly stored.

4. Analyzing the Data

4.1. Reading the data

We start by reading the data from Google Sheets into a new DataFrame.

stocks_df = spread.sheet_to_df()

stocks_df.head()

we can see that the DataFrame contains real values for stocks prices and number of shares (and not Google Sheets formulas).

We need to change the data type of stock prices and number of outstanding shares from string to numeric .

stocks_df[["Price_1_1", "Price_3_23", "Price_4_9", "Shares"]] = \
stocks_df[["Price_1_1", "Price_3_23", "Price_4_9", "Shares"]].apply(pd.to_numeric)

4.2. Adding Market Cap data and percentage change of stock prices

stocks_df["Marketcap_1_1"] = stocks_df["Price_1_1"] * stocks_df["Shares"]
stocks_df["Marketcap_3_23"] = stocks_df["Price_3_23"] * stocks_df["Shares"]
stocks_df["Marketcap_4_9"] = stocks_df["Price_4_9"] * stocks_df["Shares"]

Adding percentage change of stock prices

# Percentage Change from January 1st to March 23rd 
stocks_df["PercentageChange_3_23_1_1"] = (stocks_df["Price_3_23"] - stocks_df["Price_1_1"]) / stocks_df["Price_1_1"]*100
# Percentage Change from March 23rd to April 9th
stocks_df["PercentageChange_4_9_3_23"] = (stocks_df["Price_4_9"] - stocks_df["Price_3_23"]) / stocks_df["Price_3_23"]*100
# # Percentage Change from January 1st to April 9th
stocks_df["PercentageChange_4_9_1_1"] = (stocks_df["Price_4_9"] - stocks_df["Price_1_1"]) / stocks_df["Price_1_1"]*100

4.3. Analyzing the data

Change in the total market cap of the S&P 500

sum(stocks_df["Marketcap_3_23"] - stocks_df["Marketcap_1_1"]) / 10**9

-8713.69057696208

sum(stocks_df["Marketcap_4_9"] - stocks_df["Marketcap_3_23"]) / 10**9

4668.79088967648

sum(stocks_df["Marketcap_4_9"] - stocks_df["Marketcap_1_1"]) / 10**9

-4044.8996872855996

The S&P 500 lost 8.7 trillion USD from January 1st to March 23rd, but it got back 4.6 trillion from March 23rd to April 9th.

Change in the total market cap by sector

(stocks_df.groupby("GICS Sector").sum()["Marketcap_3_23"] - stocks_df.groupby("GICS Sector").sum()["Marketcap_1_1"]).sort_values() / 10**9

GICS Sector
Information Technology   -1592.206753
Financials               -1518.712229
Industrials              -1087.991664
Health Care              -1031.715099
Consumer Discretionary    -793.938427
Communication Services    -734.977458
Energy                    -710.786571
Consumer Staples          -450.846959
Real Estate               -265.205780
Materials                 -264.696109
Utilities                 -262.613530
dtype: float64

At the 2020 bottom of the S&P 500 (March 23rd), the Information Technology and Financials sectors had the largest drop in total market cap (compared to January 1st) with 1.592 trillion and 1.518 trillion respectively.

(stocks_df.groupby("GICS Sector").sum()["Marketcap_4_9"] - stocks_df.groupby("GICS Sector").sum()["Marketcap_1_1"]).sort_values() / 10**9

GICS Sector
Financials               -870.187706
Industrials              -691.882829
Information Technology   -559.803630
Energy                   -510.373229
Communication Services   -401.445690
Consumer Discretionary   -397.755029
Health Care              -269.510749
Consumer Staples         -138.054799
Materials                -110.571043
Real Estate               -55.584791
Utilities                 -39.730193
dtype: float64

As of April 9th, we can see that the Financials and Industrials sector had the largest drop in total market cap compared to January 1st.

Ranking of companies by percentage change of stock prices

stocks_df.sort_values(by=["PercentageChange_4_9_1_1"])[["Security", "PercentageChange_4_9_1_1"]].head(5)

We can see from the table above that the companies that are hardest hit are the 3 major cruises companies: Norwegian Cruise Line Holdings, Carnival Corp., Royal Caribbean Cruises Ltd. These companies saw drops in their stock price of over 70%.

print(sum(stocks_df["PercentageChange_4_9_1_1"] < 0))
print(sum(stocks_df["PercentageChange_4_9_1_1"] > 0))

Only 56 stocks form the 505 saw positive growth from January 1st to April 9th.

Percentage Change of stock priced by sector

stocks_df.groupby("GICS Sector").mean()['PercentageChange_4_9_1_1'].sort_values()

GICS Sector
Energy                   -49.838483
Consumer Discretionary   -32.364198
Financials               -25.090248
Industrials              -23.928685
Communication Services   -20.003368
Materials                -17.806975
Information Technology   -15.412405
Real Estate              -14.374746
Health Care               -8.395149
Consumer Staples          -8.243594
Utilities                 -6.477197
Name: PercentageChange_4_9_1_1, dtype: float64

We can see that the energy sector was the hardest hit with a 49.8% average drop in stock prices.

Below we can visualize a boxplot of the 11 sectors' percentage change in stock prices from January 1st to April 9th.

plt.figure(figsize=(18, 6))
plt.tick_params('both', labelsize='8')
plt.xticks(rotation=45)
sns.boxplot(x="GICS Sector", y="PercentageChange_4_9_1_1", data=stocks_df)

<matplotlib axes="" _subplots="" axessubplot="" at="" 0x12427a6a0=""></matplotlib>

stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_1_1'].sort_values()

GICS Sub Industry
Hotels, Resorts & Cruise Lines        -61.709685
Oil & Gas Equipment & Services        -58.043026
Oil & Gas Drilling                    -57.631986
Airlines                              -53.542246
Department Stores                     -53.265702
                                         ...    
Food Retail                             8.374040
Wireless Telecommunication Services     9.301438
Metal & Glass Containers                9.486537
Water Utilities                         9.608985
Gold                                   32.600648
Name: PercentageChange_4_9_1_1, Length: 128, dtype: float64

print(sum(stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_1_1'] < 0))
print(sum(stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_1_1'] > 0))

if we look at the average percentage change in stock prices by Sub Industry; we can see that the travel related industries, Oil & Gas and Department Stores were the hardest hit. 118 of the 138 Sub Industries had their average stock price declined from January 1st to April 9th.

plt.figure(figsize=(18, 6))
plt.tick_params('both', labelsize='8')
stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_1_1'].sort_values().plot.bar()

<matplotlib axes="" _subplots="" axessubplot="" at="" 0x1279d2b70=""></matplotlib>

Percentage Change of stock priced by sector (From March 23rd to April 9th)

stocks_df.groupby("GICS Sector").mean()['PercentageChange_4_9_3_23'].sort_values()

GICS Sector
Communication Services    17.908378
Consumer Staples          21.212689
Information Technology    22.503612
Health Care               28.291532
Industrials               28.485595
Consumer Discretionary    33.247166
Utilities                 35.572216
Financials                35.613067
Real Estate               36.756680
Materials                 36.822111
Energy                    44.655048
Name: PercentageChange_4_9_3_23, dtype: float64

Percentage Change of stock priced by Sub Industry (From March 23rf to April 9th)

stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_3_23'].sort_values()

GICS Sub Industry
Food Retail                       -0.064350
Drug Retail                        0.848429
Hypermarkets & Super Centers       5.825800
Interactive Home Entertainment     8.869739
Hotel & Resort REITs              11.516854
                                    ...    
Copper                            51.948052
Real Estate Services              52.899765
Diversified Chemicals             55.535296
Distributors                      62.086331
Household Appliances              63.111180
Name: PercentageChange_4_9_3_23, Length: 128, dtype: float64

All sectors and sub industries (except Food Retail) saw their average stock price go up from March 23rd to April 9th.

In this tutorial, we learned how to use Python, Google Sheets and Google Finance to collect and analyze stock data in the context of coronavirus pandemic. We're still in the early stages of the pandemic, and we don't know yet what would be the mid to long term effect of the pandemic on both the society and the economy. It would be interesting to review this analysis in the coming weeks to see if the stock market recovers from the 2020 losses.

The source code for this tutorial can be found in this github repository .

[1] https://en.wikipedia.org/wiki/Google_Finance