<jupyter_start><jupyter_text>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](https://github.com/adilmoujahid/coronavirus-covid19-stocks-analysis).  1. The Case StudyIn this tutorial, we will focus on the S&P 500 companies. We will start by collecting the following data :* Stock prices in 3 different dates (January 1st, March 23rd and April 9th) * Number of outstanding shares for each company* Industry/Sector where the companies operate (following the GICS classification)After collecting and structuring the data, we will use ```Python``` and ```Pandas``` library to analyse the data.  2. Technical SetupWe will be using the following services and libraries to collect and analyze the data:* Wikipedia: We will use [this wikipedia page](https://en.wikipedia.org/wiki/List_of_S%26P_500_companies) to get the list of S&P 500 companies.* Google Finance: Google Finance is a website focusing on business news and financial information hosted by Google [1]. Google Finance doesn't have an API that we can use directly in Python, but it can be accessed from Google Sheets using a formula called GOOGLEFINANCE. We will use Python to write down the GOOGLEFINANCE formulas.* Goole Sheets: We will use Google Sheets as a backend to store stocks data. In order to interact with Google Sheets directly from Python, we need 3 libaries: [Google Auth](https://github.com/googleapis/google-auth-library-python), [gspread](https://gspread.readthedocs.io/en/latest/) and [gspread-pandas](https://gspread-pandas.readthedocs.io/en/latest/). We also need to configure Google Sheets to be able to access the spreadsheets using Python.* Python and Jupyter notebooks and Pandas: We will be using Python, Jupyter notebooks and Pandas to collect, store and analyze the data. ![Alt Text](./images/data_flow.png)  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](https://cloud.google.com/), log in using your google account and click on ```Console```. ![Alt Text](./images/gcp_config_1.png) * Step 2: Click on ```Select a project``` > ```NEW PROJECT```, enter the project name and click on ```CREATE``` ![Alt Text](./images/gcp_config_2.png) * Step 3: Click on ```APIs & Services``` > ```Dashboard``` > ```ENABLE APIS AND SEVICES```. ![Alt Text](./images/gcp_config_3.png) * Step 4: Search for both Google Drive API and Google Sheets API and click on ```ENABLE``` ![Alt Text](./images/gcp_config_4.png) * Step 5: Inside the Google Sheets API page, click on ```MANAGE```, ```CREATE CREDENTIALS``` and select ```Google Sheets API```. ![Alt Text](./images/gcp_config_5.png) * Step 6: Choose ```Web sever```, ```Application data``` and ```JSON``` type for the API key. Click ```Continue``` to download your private key in JSON format. ![Alt Text](./images/gcp_config_6.png) * Step 7: Once you download the JSON file, save in the same folder as your Jupyter notebook and copy the ```client_email``` information.  ![Alt Text](./images/gcp_config_7.png)  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```. ![Alt Text](./images/gs_config.png)  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.<jupyter_code>%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')<jupyter_output><empty_output><jupyter_text>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](https://en.wikipedia.org/wiki/List_of_S%26P_500_companies) ![Alt Text](./images/snp_500.png) Fortunatey, we can use ```Pandas``` to read the page, extract the table with S&P 500 companies and store them into a ```Pandas``` Dataframe.<jupyter_code>url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'

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

stocks_df.head()<jupyter_output><empty_output><jupyter_text>The most important data that we need is:* Symbol: Stock Symbol * Security: Name of the company* GICS Sector: Sector where the company operates following the Global Industry Classification Standard (GICS).* GICS Sub Industry: Sub industry where the company operates following the Global Industry Classification Standard (GICS).We can start looking at some statistics. For example the number of companies in the list.<jupyter_code>#Number of companis
len(stocks_df)<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>stocks_df[stocks_df['Security'].str.contains("Class")]<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>stocks_df['GICS Sector'].value_counts()
stocks_df['GICS Sub Industry'].value_counts()<jupyter_output><empty_output><jupyter_text>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 We start by adding stock prices in 3 different dates: January 1st, March 23rd and April 9th.   * January 1st, 2020 is the first date of the year. We want to have this price in order to calculate the price drop since the beggining of 2020.* March 23rd, 2020 is the date when the S&P 500 reached the bottom in 2020.* April 9th, 2020 is the last date when the stock market was open (at the time of writing this blog post)<jupyter_code>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)')<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>stocks_df["Shares"] = stocks_df["Symbol"].apply(lambda x: '=GOOGLEFINANCE("' + x + '","shares")')<jupyter_output><empty_output><jupyter_text>Storing the DataFrame to Google Sheet We start by creating a variable that contains our credentials that we got from Google Cloud Plaltform.<jupyter_code>scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = Credentials.from_service_account_file('./gsheet-stocks.json', scopes=scope)<jupyter_output><empty_output><jupyter_text>Next, we read the empty google sheet in a variable that we call ```spread```.<jupyter_code>client = Client(scope=scope, creds=credentials)
spread = Spread("stocks_analysis", client=client)<jupyter_output><empty_output><jupyter_text>We define the list of variables that we want to keep.<jupyter_code>cols_to_keep = ["Symbol", "Security", "GICS Sector", "GICS Sub Industry", 
                "Price_1_1", "Price_3_23", "Price_4_9", "Shares"]<jupyter_output><empty_output><jupyter_text>The last step is to save the DataFrame to Google Sheets.<jupyter_code>spread.df_to_sheet(stocks_df[cols_to_keep])<jupyter_output><empty_output><jupyter_text>If we go to Google Sheets, we can see that the data is correctly stored. ![Alt Text](./images/gs_data.png)  4. Analyzing the Data  4.1. Reading the data We start by reading the data from Google Sheets into a new DataFrame.<jupyter_code>stocks_df = spread.sheet_to_df()
stocks_df.head()<jupyter_output><empty_output><jupyter_text>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```.<jupyter_code>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)<jupyter_output><empty_output><jupyter_text>4.2. Adding Market Cap data and percentage change of stock prices  Adding Market Cap DataNext, we will add the market cap in the 3 different dates.<jupyter_code>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"]<jupyter_output><empty_output><jupyter_text>Adding percentage change of stock prices<jupyter_code># 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<jupyter_output><empty_output><jupyter_text>4.3. Analyzing the data  Change in the total market cap of the S&P 500<jupyter_code>sum(stocks_df["Marketcap_3_23"] - stocks_df["Marketcap_1_1"]) / 10**9
sum(stocks_df["Marketcap_4_9"] - stocks_df["Marketcap_3_23"]) / 10**9
sum(stocks_df["Marketcap_4_9"] - stocks_df["Marketcap_1_1"]) / 10**9<jupyter_output><empty_output><jupyter_text>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<jupyter_code>(stocks_df.groupby("GICS Sector").sum()["Marketcap_3_23"] - stocks_df.groupby("GICS Sector").sum()["Marketcap_1_1"]).sort_values() / 10**9<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>(stocks_df.groupby("GICS Sector").sum()["Marketcap_4_9"] - stocks_df.groupby("GICS Sector").sum()["Marketcap_1_1"]).sort_values() / 10**9<jupyter_output><empty_output><jupyter_text>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<jupyter_code>stocks_df.sort_values(by=["PercentageChange_4_9_1_1"])[["Security", "PercentageChange_4_9_1_1"]].head(5)<jupyter_output><empty_output><jupyter_text>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%.<jupyter_code>print(sum(stocks_df["PercentageChange_4_9_1_1"] < 0))
print(sum(stocks_df["PercentageChange_4_9_1_1"] > 0))<jupyter_output>449
56<jupyter_text>Only 56 stocks form the 505 saw positive growth from January 1st to April 9th.  Percentage Change of stock priced by sector<jupyter_code>stocks_df.groupby("GICS Sector").mean()['PercentageChange_4_9_1_1'].sort_values()<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>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)
stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_1_1'].sort_values()
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))<jupyter_output>118
10<jupyter_text>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.<jupyter_code>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()<jupyter_output><empty_output><jupyter_text>Percentage Change of stock priced by sector (From March 23rd to April 9th)<jupyter_code>stocks_df.groupby("GICS Sector").mean()['PercentageChange_4_9_3_23'].sort_values()<jupyter_output><empty_output><jupyter_text>Percentage Change of stock priced by Sub Industry (From March 23rf to April 9th)<jupyter_code>stocks_df.groupby("GICS Sub Industry").mean()['PercentageChange_4_9_3_23'].sort_values()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10256070054398302, 'fraction_numerical': 0.029255672018044316, 'mean_word_length': 6.144549763033176, 'pattern_counts': {'":': 0, '<': 101, '<?xml version=': 0, '>': 104, 'https://': 12, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '494350', 'license': 'MIT', 'n_tokens_mistral': 4938, 'n_tokens_neox': 4551, 'n_words': 1684, 'path': 'coronavirus-covid19-stocks-analysis.ipynb', 'repo_name': 'adilmoujahid/adilmoujahid', 'size': '15074'}