<jupyter_start><jupyter_text>Name: <NAME>  Set up<jupyter_code># Mount Google Drive
from google.colab import drive
drive.mount('/content/gdrive')
import os

# the base Google Drive directory
root_dir = "/content/gdrive/MyDrive/Colab Notebooks/BA870/"

# choose where you want your project files to be saved
project_folder = "Individual Project/"

def create_and_set_working_directory(project_folder):
  # check if your project folder exists. if not, it will be created.
  if os.path.isdir(root_dir + project_folder) == False:
    os.mkdir(root_dir + project_folder)
    print(root_dir + project_folder + ' did not exist but was created.')

  # change the OS to use your project folder as the working directory
  os.chdir(root_dir + project_folder)

  # create a test file to make sure it shows up in the right place
  !touch 'new_file_in_working_directory.txt'
  print('\nYour working directory was changed to ' + root_dir + project_folder + \
        "\n\nAn empty text file was created there. You can also run !pwd to confirm the current working directory." )

create_and_set_working_directory(project_folder)
!pwd<jupyter_output>/content/gdrive/MyDrive/Colab Notebooks/BA870/Individual Project<jupyter_text>Check if the function worked by listing the files in the project folder. During the very first run it should contain only `new_file_in_working_directory.txt`. If you upload other files to your `project_folder`, other files will be listed too.<jupyter_code>!ls<jupyter_output>'Individual Project- Tzu-Hua Huang.ipynb'   tempGICS.csv
 new_file_in_working_directory.txt	    tempNAICS.csv
 Return2020.csv				    tempSIC.csv
 Return2020_w_Industry_Codes.csv	    WRDS_Variables.csv<jupyter_text>Please upload the necessary files (`stock_des.csv`, `ta.csv`, `rev.csv`) in your `project_folder` folder. You can upload the files in your Google Drive `project_folder` via drive.google.com or via below `files.upload()` button.<jupyter_code># imports
import pandas as pd
import numpy as np
import statsmodels.api as sm
import matplotlib.pyplot as plt
import seaborn as sns<jupyter_output><empty_output><jupyter_text>1.2019 Financial Accounting Characteristics that Explain Varying Exposures to Covid-19 Market Shock (for Early and Late 2020  Step 1. Load and merge dataLoad the data from `temp1.csv` and `temp2.csv` into Pandas dataframes. Merge the datasets based on each stock's ticker symbol (TIC).<jupyter_code># load data including RetEarly2020 and RetLate2020
temp1 = pd.read_csv('Return2020.csv')
temp1.head()
# check data
temp1.info()
temp1[temp1.isnull().any(axis=1)]
# temp1['RetEarly2020'] = pd.to_numeric(temp1['RetEarly2020']) # -- ValueError: Unable to parse string "#VALUE!"<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2716 entries, 0 to 2715
Data columns (total 3 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   TICKER        2716 non-null   object 
 1   RetEarly2020  2716 non-null   object 
 2   RetLate2020   2716 non-null   float64
dtypes: float64(1), object(2)
memory usage: 63.8+ KB<jupyter_text>`ValueError` occurs when converting the datatype of `RetEarly2020`. Thus, we drop the rows with errors accroding to the warning message.<jupyter_code># drop the rows with errors
temp1.drop(temp1.loc[temp1['RetEarly2020'] == '#VALUE!'].index, inplace=True)

# convert datatype of RetEarly2020 from object to float
temp1['RetEarly2020'] = pd.to_numeric(temp1['RetEarly2020'])
temp1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 2711 entries, 0 to 2715
Data columns (total 3 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   TICKER        2711 non-null   object 
 1   RetEarly2020  2711 non-null   float64
 2   RetLate2020   2711 non-null   float64
dtypes: float64(2), object(1)
memory usage: 84.7+ KB<jupyter_text>Download required variables for creating financial ratios from WRDS Compustat. Load data set `WRDS_Variables.csv` and save as `temp2`.<jupyter_code># load data #2
temp2 = pd.read_csv('WRDS_Variables.csv')

# display all rows of dataframe
# pd.set_option('display.max_rows', None)
temp2.sample(20)
# check data
temp2.info()
# data cleaning - remove unnecessary or incomplete fields
temp2.drop(columns=['gvkey', 'datadate', 'fyear', 'indfmt', 'consol',	'popsrc',	'datafmt', 
                    'curcd', 'ogm', 'revt', 'xintd', 'costat', 'ipodate'], inplace=True)
temp2.head()
# merge data set temp1 anf temp2
df = temp1.merge(temp2, left_on='TICKER', right_on='tic')
df
# clean and rename columns
df.drop(columns=['tic'], inplace=True)
df.rename(columns={'act':'Current Assets', 'at':'Total Assets', 'che':'Cash', 'ebit':'EBIT', 
                   'dltt':'Long-term Debt', 'emp':'#Employees', 'intan':'Intangible Assets', 
                   'invt':'Inventories', 'lct':'Current Liabil', 'lt':'Total Liabil', 'ni':'NI', 
                   'ppent':'PP&E', 're':'Retained Earnings', 'sale':'Sales', 'xint':'Interest Expenses',
                   'city':'City', 'ggroup':'GGROUP', 'loc':'Location', 'state':'State'}, inplace=True)
df.head()<jupyter_output><empty_output><jupyter_text>- According [WRDS Compustat North America User’s Guide](http://sites.bu.edu/qm222projectcourse/files/2014/08/compustat_users_guide-2003.pdf), the unit of `Employees` is 'Thousands'. Adjust the column values by multiplying the column values by 1000.- Add new column `Total Equity` for later use.<jupyter_code># fix column values to desirable unit
df['#Employees'] = df['#Employees'] * 1000

# add new column - Total Equity
df['Total Equity'] = df['Total Assets'] - df['Total Liabil']
df.info()
df.describe().T<jupyter_output><empty_output><jupyter_text>Step 2. Create new dataframe for financial ratiosCreate a list of financial ratio variables that would help explain the cross-sectional variation in returns.- Cash/Total Assets (Do firms with more cash at end of 2019 havehigher stock returns in 2020 after Covid shock?)- Cash/Current_Liabilities (Do firms with more cash to cover current liabilities at end of 2019 have higher stock returns in 2020after Covid shock?)- Cash/Interest Expense (Do firms with more cash to coverimmediate interest expenses have higher stock returns in 2020after Covid shock?)- Quick Ratio (Do firms with more liquid assets to cover currentliabilities at end of 2019 have higher stock returns in 2020 afterCovid shock?)- Long_Term_Debt/Total Assets (Do firms with lower Long TermDebt at end of 2019 have higher stock returns in 2020 after Covidshock?)- EBIT/Interest Expense (Do firms with high 2019 earnings to coverinterest expenses have higher stock returns in 2020 after Covidshock?)- Fixed Assets/Total_Assets (Do firms with higher fixed assetsperform better or worse in 2020 after Covid shock?)- Net Sales/ Employees = Sales per Employee (Do firms that have higher versus lower reliance on labor perform better or worse in 2020 after Covid shock?)- Geographic Location (Do firms in certain States, Cities or ZipCodes have higher versus lower stock returns in 2020 after Covid-19 shock? Is there an economic story to support these patterns?)<jupyter_code>df1 = pd.DataFrame()
df1['TICKER'] = df['TICKER']
df1['RetEarly2020'] = df['RetEarly2020']
df1['RetLate2020'] = df['RetLate2020']

# Cash/Total Assets
df1['Cash/TA'] = df['Cash']/df['Total Assets']

# Cash/Current_Liabilities
df1['Cash/CL'] = df['Cash']/df['Current Liabil']

# Cash/Interest Expense
df1['Cash/Interest Expense'] = df['Cash']/df['Interest Expenses']

# Quick ratio = (CA - Inventories) / CL
df1['Quick Ratio'] = (df['Current Assets'] - df['Inventories'])/df['Current Liabil']

# Long_Term_Debt/Total Assets
df1['Long-term Debt/TA'] = df['Long-term Debt']/df['Total Assets']

# EBIT/Interest Expense
df1['EBIT/Interest Expense'] = df['EBIT']/df['Interest Expenses']

# Fixed Assets/Total_Assets
df1['Fixed Assets/TA'] = df['PP&E']/df['Total Assets']

# Net Sales/# Employees = Sales per Employee
df1['Sales per Employee'] = df['Sales']/df['#Employees']

df1.sample(5)<jupyter_output><empty_output><jupyter_text>Step 3. Check data   3.a Check for extreme/inf values<jupyter_code>df1.describe().T<jupyter_output><empty_output><jupyter_text>- From the previous descriptive statistics, we find several fields have minimum of 0. For those columns, if we use them as denominator for calculating financial ratios, we will receive infinity values as the above descriptive statistics table shows. Thus, we will invert the relevant ratios using these variables as denominator.- For `Cash/Interest Expense`, both of the variables have 0, if we consider the number of problematic values compared to the size of data set, we may not to consider this ratio in the model.- For `Sales per Employee`, similarly, both of the variables have 0. Compared to the size of data set, the number of extreme and missing values are acceptable. Rather than invert the ratio, we can delete the rows with extreme and missing values.<jupyter_code># df1.drop(columns=['Cash/Interest Expense'], inplace=True)

# invert ratios with infinity values
# drop the columns first
df1.drop(columns=['EBIT/Interest Expense'], inplace=True)

# invert EBIT/Interest Expense to Interest Expense/EBIT
df1['Interest Expense/EBIT'] = df['Interest Expenses']/df['EBIT']
df1.describe().T<jupyter_output><empty_output><jupyter_text>3.b Check for missing values<jupyter_code># check columns with na values
df1.isna().sum()<jupyter_output><empty_output><jupyter_text>- All of the ratios have NaN values, compared to the size of the whole data set, rather than delete them directly, we replace the missing values with the sample mean.<jupyter_code>df1.describe().T
df1['Cash/TA'].fillna((df1['Cash/TA'].mean()), inplace=True)
df1['Cash/CL'].fillna((df1['Cash/CL'].mean()), inplace=True)
df1['Cash/Interest Expense'].fillna((df1['Cash/Interest Expense'].mean()), inplace=True)
df1['Quick Ratio'].fillna((df1['Quick Ratio'].mean()), inplace=True)
df1['Long-term Debt/TA'].fillna((df1['Long-term Debt/TA'].mean()), inplace=True)
df1['Fixed Assets/TA'].fillna((df1['Fixed Assets/TA'].mean()), inplace=True)
df1['Sales per Employee'].fillna((df1['Sales per Employee'].mean()), inplace=True)
df1['Interest Expense/EBIT'].fillna((df1['Interest Expense/EBIT'].mean()), inplace=True)
df1.describe().T<jupyter_output><empty_output><jupyter_text>Now, we can drop the observations with infinity values.<jupyter_code>df1 = df1.replace([np.inf, -np.inf], np.nan).dropna(axis=0)
df1.describe().T<jupyter_output><empty_output><jupyter_text>We have 2022 observations left.   3.c Check for outliers - winsozrizationWinsorize each financial ratios by replacing 1 to 5% of the lowest/highest values.<jupyter_code>df1.columns<jupyter_output><empty_output><jupyter_text>Cash/TA<jupyter_code>import matplotlib.pyplot as plt
import seaborn as sns

# check outliers with boxplot
sns.boxplot(df1['Cash/TA'])
plt.show()
# deal with outliers by winzoring
print(df1['Cash/TA'].quantile(0.00))
print(df1['Cash/TA'].quantile(0.90))
# remove the effect of outliers
# according to the boxplot, replace 1% of the lowest and 2% the highest values by winsorizing
df1['Cash/TA'].clip(lower=df1['Cash/TA'].quantile(0.00),
                    upper=df1['Cash/TA'].quantile(0.90),
                    axis=0, inplace=True)
df1['Cash/TA'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Cash/TA'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Cash/CL<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Cash/CL'])
plt.show()
# deal with outliers by winzoring
print(df1['Cash/CL'].quantile(0.00))
print(df1['Cash/CL'].quantile(0.90))
# remove the effect of outliers
# according to the boxplot, replace 5% of the lowest and 2% the highest values by winsorizing
df1['Cash/CL'].clip(lower=df1['Cash/CL'].quantile(0.00),
                    upper=df1['Cash/CL'].quantile(0.90),
                    axis=0, inplace=True)
df1['Cash/CL'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Cash/CL'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Cash/Interest Expense<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Cash/Interest Expense'])
plt.show()
# deal with outliers by winzoring
print(df1['Cash/Interest Expense'].quantile(0.00))
print(df1['Cash/Interest Expense'].quantile(0.85))
# remove the effect of outliers
# according to the boxplot, replace 5% of the lowest and 3% of the highest values by winsorizing
df1['Cash/Interest Expense'].clip(lower=df1['Cash/Interest Expense'].quantile(0.00),
                                  upper=df1['Cash/Interest Expense'].quantile(0.85),
                                  axis=0, inplace=True)
df1['Cash/Interest Expense'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Cash/Interest Expense'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Quick Ratio<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Quick Ratio'])
plt.show()
# deal with outliers by winzoring
print(df1['Quick Ratio'].quantile(0.00))
print(df1['Quick Ratio'].quantile(0.95))
# remove the effect of outliers
# according to the boxplot, replace 5% of the highest values by winsorizing
df1['Quick Ratio'].clip(lower=df1['Quick Ratio'].quantile(0.00),
                        upper=df1['Quick Ratio'].quantile(0.95),
                        axis=0, inplace=True)
df1['Quick Ratio'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Quick Ratio'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Long-term Debt/TA<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Long-term Debt/TA'])
plt.show()
# deal with outliers by winzoring
print(df1['Long-term Debt/TA'].quantile(0.00))
print(df1['Long-term Debt/TA'].quantile(0.97))
# remove the effect of outliers
# according to the boxplot, replace 5% of the highest values by winsorizing
df1['Long-term Debt/TA'].clip(lower=df1['Long-term Debt/TA'].quantile(0.00),
                              upper=df1['Long-term Debt/TA'].quantile(0.97),
                              axis=0, inplace=True)
df1['Long-term Debt/TA'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Long-term Debt/TA'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Fixed Assets/TA<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Fixed Assets/TA'])
plt.show()
# deal with outliers by winzoring
print(df1['Fixed Assets/TA'].quantile(0.00))
print(df1['Fixed Assets/TA'].quantile(0.95))
# remove the effect of outliers
# according to the boxplot, replace 5% of the highest values by winsorizing
df1['Fixed Assets/TA'].clip(lower=df1['Fixed Assets/TA'].quantile(0.00),
                            upper=df1['Fixed Assets/TA'].quantile(0.95),
                            axis=0, inplace=True)
df1['Fixed Assets/TA'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Fixed Assets/TA'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Sales per Employee<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Sales per Employee'])
plt.show()
# deal with outliers by winzoring
print(df1['Sales per Employee'].quantile(0.00))
print(df1['Sales per Employee'].quantile(0.90))
# remove the effect of outliers
# according to the boxplot, replace 5% of the highest values by winsorizing
df1['Sales per Employee'].clip(lower=df1['Sales per Employee'].quantile(0.00),
                               upper=df1['Sales per Employee'].quantile(0.90),
                               axis=0, inplace=True)
df1['Sales per Employee'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Sales per Employee'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Interest Expense/EBIT<jupyter_code># check outliers with boxplot
sns.boxplot(df1['Interest Expense/EBIT'])
plt.show()
# deal with outliers by winzoring
print(df1['Interest Expense/EBIT'].quantile(0.10))
print(df1['Interest Expense/EBIT'].quantile(0.94))
# remove the effect of outliers
# according to the boxplot, replace 5% of the highest values by winsorizing
df1['Interest Expense/EBIT'].clip(lower=df1['Interest Expense/EBIT'].quantile(0.10),
                                  upper=df1['Interest Expense/EBIT'].quantile(0.94),
                                  axis=0, inplace=True)
df1['Interest Expense/EBIT'].describe()
# check if outliers exist after winzoring
sns.boxplot(df1['Interest Expense/EBIT'])
plt.show()<jupyter_output>/usr/local/lib/python3.7/dist-packages/seaborn/_decorators.py:43: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.
  FutureWarning<jupyter_text>Step 4. Examine the correlation(e) Examine the correlation between *Price/Book, P/E, P/EBIT, P/SALES, RetEarly2020,* and *RetLate2020*. In a text box, explain the correlations you observe.CORRECTION: According to the above process, we will use the ratios as follows: `Book/Price`, `E/P`, `EBIT/P`, `SALES/P`.<jupyter_code># create correlation matric to check the correlation between the variables
corr_matrix = df1.corr()
sns.heatmap(corr_matrix, annot=True)
plt.show()<jupyter_output><empty_output><jupyter_text>Correlation Matrix Interpretation`Cash/CL`: Cash/Current Liabilities`Long-term Debt/TA`: Long-term Debt/Total Assets1. From the correlation matrix, in general, the correlation among the market ratios, are higher than the correlation between any of the market ratios and `RetEarly2020` and `RetLate2020`. 2. The correlation between `Quick Ratio` and `Cash/CL` is the highest among the X variables. Both of them use `Current Liabilities` as denominator, since liquid assets in `Quick Ratio` includes cash on hand or assets other than cash that can be quickly converted into cash, the high correlation makes sense.3. The correlation between `Long-term Debt/TA` and `Cash/Interest Expense` have the highest negative correlation. We may infer that the firms with lower long-term debt have more cash to coverimmediate interest expenses. 4. Compared to `RetLate2020`, the correlations between each of market ratios and `RetEarly2020` are stronger. Since the ratios are derived from 2019, the connection might be stronger when it compares to the data closer to 2019, which is `RetEarly2020` in this case.5. The correaltions between market ratios and `RetEarly2020` and `RetLate2020` are generally negative, which indicates that they’re negatively correlated. Lower market ratios are related to higher `RetEarly2020` and `RetLate2020`.  Step 5. Estimate linear regression models  5.a RetEarly2020<jupyter_code># define the independent variables (X)
x1 = df1[['Cash/TA', 'Cash/CL', 'Cash/Interest Expense', 'Quick Ratio', 'Long-term Debt/TA',
          'Fixed Assets/TA', 'Sales per Employee', 'Interest Expense/EBIT']]

# define the dependent variable (Y)
y1 = df1['RetEarly2020']
# run multiple regression with constant
# use statsmodels
# add a constant
x1 = sm.add_constant(x1) 
model1 = sm.OLS(y1, x1).fit()

# print out regression summary statistics
print_model1 = model1.summary()
print(print_model1)<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:           RetEarly2020   R-squared:                       0.063
Model:                            OLS   Adj. R-squared:                  0.059
Method:                 Least Squares   F-statistic:                     16.97
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           1.33e-24
Time:                        02:58:31   Log-Likelihood:                -550.26
No. Observations:                2022   AIC:                             1119.
Df Residuals:                    2013   BIC:                             1169.
Df Model:                           8                                         
Covariance Type:            nonrobust                                         
=========================================================================================
                            coef    std e[...]<jupyter_text>**Interpretation**`R-squared: 0.063`Around 6% of the differences or the variation in observed early 2020 return for companies is explained by the financial ratios, indicating 6% of the variation which varies across all these companies is explained by their financial ratios.`Adj. R-squared: 0.059`The adjusted R-squared considers and tests different independent variables against the model. Compared to the model with additional input variables, decreased adjusted R-squared indicates that the additional input variables are not adding value to the model.<jupyter_code>df1_table = pd.read_html(print_model1.tables[1].as_html(),header=0,index_col=0)[0]
df1_table<jupyter_output><empty_output><jupyter_text>**t-stats**According to the coefficients in the above summary table:- **Cash/TA**- **Cash/CL**- **Quick Ratio**- **Fixed Assets/TA**- **Sales per Employee**- **Interest Expense/EBIT**  - The lager the magnitude of the t value, the more the evidence against the null hypothesis, indicating that the more likely there is a significant difference.   - The p-values for the above variables are less than 0.05. We can reject the null hypothesis, suggesting that the variable has some effect with a 95% probability of being correct, assuming the model is specified correctly.   - We can conclude that the data provide enough evidence to reject the null hypothesis, suggesting that each variable is statistically significant.- **Cash/Interest Expense**- **Long-term Debt/TA**  - The t-values are close to 0. The greater the t-values, the more likely there isn't a significant difference. Their p-values are greater than 0.05. We fail to reject the null hypothesis.  5.b RetLate2020<jupyter_code># define the independent variables (X)
x2 = df1[['Cash/TA', 'Cash/CL', 'Cash/Interest Expense', 'Quick Ratio', 'Long-term Debt/TA',
          'Fixed Assets/TA', 'Sales per Employee', 'Interest Expense/EBIT']]

# define the dependent variable (Y)
y2 = df1['RetLate2020']
# run multiple regression with constant
# use statsmodels
# add a constant
x2 = sm.add_constant(x2) 
model2 = sm.OLS(y2, x2).fit()

# print out regression summary statistics
print_model2 = mode2.summary()
print(print_model)<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:            RetLate2020   R-squared:                       0.040
Model:                            OLS   Adj. R-squared:                  0.036
Method:                 Least Squares   F-statistic:                     10.45
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           1.88e-14
Time:                        02:01:53   Log-Likelihood:                -3008.4
No. Observations:                2022   AIC:                             6035.
Df Residuals:                    2013   BIC:                             6085.
Df Model:                           8                                         
Covariance Type:            nonrobust                                         
=========================================================================================
                            coef    std e[...]<jupyter_text>**Interpretation**`R-squared: 0.040`Around 4% of the differences or the variation in observed late 2020 return for companies is explained by the financial ratios, indicating 4% of the variation which varies across all these companies is explained by their financial ratios.`Adj. R-squared: 0.036`The adjusted R-squared considers and tests different independent variables against the model. Compared to the model with additional input variables, decreased adjusted R-squared indicates that the additional input variables are not adding value to the model.<jupyter_code>df2_table = pd.read_html(print_model2.tables[1].as_html(),header=0,index_col=0)[0]
df2_table<jupyter_output><empty_output><jupyter_text>**t-stats**According to the coefficients in the above summary table:- **Cash/TA**- **Quick Ratio**- **Long-term Debt/TA**  - The lager the magnitude of the t value, the more the evidence against the null hypothesis, indicating that the more likely there is a significant difference.   - The p-values for the above variables are less than 0.05. We can reject the null hypothesis, suggesting that the variable has some effect with a 95% probability of being correct, assuming the model is specified correctly.   - We can conclude that the data provide enough evidence to reject the null hypothesis, suggesting that each variable is statistically significant.- **Cash/CL**- **Cash/Interest Expense**- **Fixed Assets/TA**- **Sales per Employee**- **Interest Expense/EBIT**  - The t-values are close to 0. The greater the t-values, the more likely there isn't a significant difference. Their p-values are greater than 0.05. We fail to reject the null hypothesis. According to the coefficients in the above summary table:- **Cash/TA**: The firms with more cash at the end of 2019 havehigher stock returns in 2020 after Covid shock.- **Quick Ratio**: In contrast, firms with more liquid assets to cover currentliabilities at end of 2019 have lower stock returns in 2020 afterCovid shock.- **Long-term Debt/TA**: The firms higher long-termdebt at end of 2019 have higher stock returns in 2020 after Covidshock.However, if we consider the R-Squared/Adj. R-Squared, only around 4% of the variation in observed late 2020 return for companies can be explained by the financial ratios. The proportion of the data fit the regression model is quite low. We may consider other indicators as explanatory variables and look for higher explanatory power. **RetEarly2020 v.s. RetLate2020**If we compare the Adj. R-squared of `RetEarly2020` versus `RetLate2020`, the Adj. R-squared of `RetEarly2020` is higher than `RetLate2020`. For the model of `RetEarly2020`, the higher Adj. R-squared means more variation in observed 2020 return can be explained by the financials, indicating a better fit for the model. We may infer that during 2020, the pandemic caused volatility and instability to the market. Compared to `RetLate2020`, the model for `RetLate2020` covering longer unstable period, thus has lower explanatory power.  2.Does Industry or Sector Explain Differences in Stock Returns for Early (Jan-Mar) and Late (Apr-Dec) 2020?   Step 1. Load and merge data Download and load the data from `temp1.csv`, `tempGICS.csv`, `tempSIC.csv` into Pandas dataframes. Merge the datasets based on each stock's ticker symbol (TIC).<jupyter_code># # load data
# temp1 = pd.read_csv('temp1.csv')

# # check data
# temp1[temp1.isnull().any(axis=1)]

# # drop the rows with errors
# temp1.drop(temp1.loc[temp1['RetEarly2020'] == '#VALUE!'].index, inplace=True)

# # convert datatype of RetEarly2020 from object to float
# temp1['RetEarly2020'] = pd.to_numeric(temp1['RetEarly2020'])
temp1.info()
# load data
tempGICS = pd.read_csv('tempGICS.csv')
tempGICS.head(3)
# load data
tempNAICS = pd.read_csv('tempNAICS.csv')
tempNAICS.head(3)
# merge data
temp = temp1.merge(tempGICS, left_on='TICKER', right_on='tic')
df = temp.merge(tempNAICS, left_on='TICKER', right_on='tic')
df.head()
df.columns
# extract required columns
df = df.loc[:, ['TICKER', 'RetEarly2020', 'RetLate2020', 'ggroup', 'naics']]
df.head()
# save data to csv file
df.to_csv('Return2020_w_Industry_Codes.csv', index=False)
!cp Return2020_w_Industry_Codes.csv "/content/gdrive/My Drive/Colab Notebooks/BA870/Individual Project"
!ls<jupyter_output>cp: 'Return2020_w_Industry_Codes.csv' and '/content/gdrive/My Drive/Colab Notebooks/BA870/Individual Project/Return2020_w_Industry_Codes.csv' are the same file
'Individual Project- Tzu-Hua Huang.ipynb'   tempGICS.csv
 new_file_in_working_directory.txt	    tempNAICS.csv
 Return2020.csv				    tempSIC.csv
 Return2020_w_Industry_Codes.csv	    WRDS_Variables.csv<jupyter_text>Step 2. Create industry indicators  2.a GICS GGROUPCreate Industry Indicator (category) variables for the 24 Industry categories based on GICS GGROUP.<jupyter_code># extract necessary columns
df_gics = pd.read_csv('Return2020_w_Industry_Codes.csv')
df_gics = df_gics.loc[:, ['TICKER', 'RetEarly2020', 'RetLate2020', 'ggroup']]
df_gics.head()
df_gics.info()
# create industry indicators
df_gics = pd.get_dummies(df_gics, columns=['ggroup'], dtype=int)
df_gics.head()
df_gics.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2665 entries, 0 to 2664
Data columns (total 27 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   TICKER        2665 non-null   object 
 1   RetEarly2020  2665 non-null   float64
 2   RetLate2020   2665 non-null   float64
 3   ggroup_1010   2665 non-null   int64  
 4   ggroup_1510   2665 non-null   int64  
 5   ggroup_2010   2665 non-null   int64  
 6   ggroup_2020   2665 non-null   int64  
 7   ggroup_2030   2665 non-null   int64  
 8   ggroup_2510   2665 non-null   int64  
 9   ggroup_2520   2665 non-null   int64  
 10  ggroup_2530   2665 non-null   int64  
 11  ggroup_2550   2665 non-null   int64  
 12  ggroup_3010   2665 non-null   int64  
 13  ggroup_3020   2665 non-null   int64  
 14  ggroup_3030   2665 non-null   int64  
 15  ggroup_3510   2665 non-null   int64  
 16  ggroup_3520   2665 non-null   int64  
 17  ggroup_4010   2665 non-null   int64  
 18  ggroup_4020   2665 non-null [...]<jupyter_text>2.b NAICSCreate Industry Indicator (category) variables for the industry categories based on NAICS.<jupyter_code># extract necessary columns
df_naics = pd.read_csv('Return2020_w_Industry_Codes.csv')
df_naics = df_naics.loc[:, ['TICKER', 'RetEarly2020', 'RetLate2020', 'naics']]
df_naics.head()
df_naics.info()
df_naics[['naics']] = df_naics[['naics']].astype(str)
df_naics['Sector_naics'] = df_naics['naics'].str[:2]
df_naics.info()
# create industry indicators
df_naics = pd.get_dummies(df_naics, columns=['Sector_naics'], dtype=int)
df_naics.head()
df_naics.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2665 entries, 0 to 2664
Data columns (total 27 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   TICKER           2665 non-null   object 
 1   RetEarly2020     2665 non-null   float64
 2   RetLate2020      2665 non-null   float64
 3   naics            2665 non-null   object 
 4   Sector_naics_11  2665 non-null   int64  
 5   Sector_naics_21  2665 non-null   int64  
 6   Sector_naics_22  2665 non-null   int64  
 7   Sector_naics_23  2665 non-null   int64  
 8   Sector_naics_31  2665 non-null   int64  
 9   Sector_naics_32  2665 non-null   int64  
 10  Sector_naics_33  2665 non-null   int64  
 11  Sector_naics_42  2665 non-null   int64  
 12  Sector_naics_44  2665 non-null   int64  
 13  Sector_naics_45  2665 non-null   int64  
 14  Sector_naics_48  2665 non-null   int64  
 15  Sector_naics_49  2665 non-null   int64  
 16  Sector_naics_51  2665 non-null   int64  
 17  Sector_naics_5[...]<jupyter_text>Step 3. Calculation and estimationFor both *ReturnEarly2020* and *ReturnLate2020*, calculate and estimate the following:  3.a GICS GGROUP  i. Average stock returnsWhat are the average stock returns for each industry classification (24 industry groups)?1. Do the highest and lowest return industries make economic sense for early and late 2020? Explain in detail.<jupyter_code>df_gics_AvgRet = df_gics.drop(columns=['TICKER'])
df_gics_AvgRet = df_gics_AvgRet.melt(id_vars=['RetEarly2020', 'RetLate2020']).groupby(['variable', 'value']).mean()
df_gics_AvgRet.query('value == 1')
# barplot
df_gics_AvgRet.query('value == 1').plot(kind='bar')<jupyter_output><empty_output><jupyter_text>**RetEarly2020**- Among all the industries, the telecommunication services sector (`ggroup_5010`) has the highest return rate in 2020, indicating the magnitude telecommunication sector impacted by the pandemic is the least. However, the negative change of return suggests the telecommunication services sector faced a loss in early 2020. We can infer that during the pandemic, people still rely on communication and require telecommunication services.- Compared to the other industries, the energy sector (`ggroup_1010`) has the lowest returns in 2020. During the pandemic, people were confined to their homes and restricted to travel, resulting in a declined oil demand and a decrease in returns, which made the energy sector the sector impacted by the pandemic the worst.**RetLate2020**- For late 2020, the consumer discretionary sector (`ggroup_2510`) has the highest return compared to the other industries. The pandemic caused a consumption shock due to the lockdown and forced shutdown across several industries. However, during recovering from the pandemic in 2020, people may have been accumulating more savings while forced to cut back on spending from lockdowns and travel restrictions. Thus, in late 2020, the consumer discretionary sector may have the highest bounce in return.- In contrast, the utilities sector (`ggroup_5510`) has the lowest return growth among all the industries. If we compare to early 2020, the magnitude utilities industry affected by the pandemic was less than the other industries. We may infer that the pandemic comparatively less affected the sector. After the COVID-19 virus has been controlled, the extent it recovered from the pandemic would also be less than the other sectors. On the other hand, the utilities were affected by the stopped commercial activities and restrictions on public transport; power demand might take time to recover from the pandemic.  ii. Fixed effect regressionsRun fixed effect regressions (using industry fixed effects) to explain both *ReturnEarly2020* and *ReturnLate2020:*1. You may wish to review the lecture notes from Lecture 3 and use the Colab Notebook: Fixed_Effect_Regression_for_BA870_Lecture_3.ipynb that is posted on QTools in Lecture 3 folder.2. What is the explanatory power of these regressions using different types of industry variables. Is this high or low?Explain.Combine the industry fixed effects with other explanatory variables (see Assignment 3 and/or the financial ratios described in OPTION (3) below).   - Run combined regressions with both industry and other “X” variables. **RetEarly2020**<jupyter_code># define x as a subset of original dataframe
x1_gics = df_gics.drop(columns=['TICKER', 'RetEarly2020', 'RetLate2020'])
# define y as a series (RetEarly2020)
y1_gics = df_gics['RetEarly2020']

# run fixed effect regression
# pass x as a dataframe, while pass y as a series
model1_gics = sm.OLS(y1_gics, x1_gics).fit()

# print out regression summary statistics
print(model1_gics.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:           RetEarly2020   R-squared:                       0.131
Model:                            OLS   Adj. R-squared:                  0.123
Method:                 Least Squares   F-statistic:                     17.25
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           1.57e-64
Time:                        02:58:33   Log-Likelihood:                -501.12
No. Observations:                2665   AIC:                             1050.
Df Residuals:                    2641   BIC:                             1192.
Df Model:                          23                                         
Covariance Type:            nonrobust                                         
===============================================================================
                  coef    std err          t      P[...]<jupyter_text>**R-squared & Adj. R-squared**- `R-squared`: 0.131Around 13% of the variation in `RetEarly2020` is explained by the industries that the companies belong to. We can infer that returns in early 2020 are described by its business model of the type of business that operates. So 13% of the variation which varies across all these companies is explained by the sector itself.- `Adjusted R-squared`: 0.123However, the Adjusted R-squared is less than the R-squared, which is 0.123. Since Adjusted R-squared considers different independent variables against the model, a decrease inA djusted R-squared indicates the additional variables doen't improve the model more than would be expected by chance.**t-stats**Among all the variables, except `ggroup_5010`, all the sector variables have large t-values, indicating the most significant difference compared to the other models. Their p-values are also far less than 0.05. We can reject the null hypothesis and conclude that the data provide enough evidence to reject the null hypothesis, suggesting that each variable is statistically significant. Data favor the hypothesis that there is a non-zero correlation between almost all the variables and `RetEarly2020`. **RetLate2020**<jupyter_code># define x as a subset of original dataframe
x2_gics = df_gics.drop(columns=['TICKER', 'RetEarly2020', 'RetLate2020'])
# define y as a series (RetLate2020)
y2_gics = df_gics['RetLate2020']

# run fixed effect regression
# pass x as a dataframe, while pass y as a series
model2_gics = sm.OLS(y2_gics, x2_gics).fit()

# print out regression summary statistics
print(model2_gics.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:            RetLate2020   R-squared:                       0.110
Model:                            OLS   Adj. R-squared:                  0.102
Method:                 Least Squares   F-statistic:                     14.17
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           8.28e-52
Time:                        02:58:33   Log-Likelihood:                -3816.5
No. Observations:                2665   AIC:                             7681.
Df Residuals:                    2641   BIC:                             7822.
Df Model:                          23                                         
Covariance Type:            nonrobust                                         
===============================================================================
                  coef    std err          t      P[...]<jupyter_text>**R-squared & Adj. R-squared**- `R-squared`: 0.110  Around 11% of the variation in `RetLate2020` is explained by the industries that the companies belong to. We can infer that returns in late 2020 are described by its business model of the type of business that operates. So 11% of the variation which varies across all these companies is explained by the sector itself.- `Adjusted R-squared`: 0.102  However, the Adjusted R-squared decreases compared to the R-squared, which is 0.102, indicating that the increase in the variables in the model doesn't enhance the explanatory power of the model. **t-stats**Compared to the regression model for `RetEarly2020`, there are more variables have large p-values. In general, most of the variables have large t-values, and their p-values are also far less than 0.05. We can reject the null hypothesis and conclude that the data provide enough evidence to reject the null hypothesis, suggesting that each variable is statistically significant. Data favor the hypothesis that there is a non-zero correlation between almost all the variables and `RetLate2020`. On the other hand, sector variables `ggroup_3010` and `ggroup_5510` have p-values greater than 0.05. We fail to reject the null, suggesting that the variables are not statistically significant.  3.b NAICS  i. Average stock returnsWhat are the average stock returns for each industry classification (23 industry groups)?1. Do the highest and lowest return industries make economic sense for early and late 2020? Explain in detail.<jupyter_code>df_naics_AvgRet = df_naics.drop(columns=['TICKER'])
df_naics_AvgRet = df_naics_AvgRet.melt(id_vars=['RetEarly2020', 'RetLate2020']).groupby(['variable', 'value']).mean()
df_naics_AvgRet.query('value == 1')
# barplot
df_naics_AvgRet.query('value == 1').plot(kind='bar')<jupyter_output><empty_output><jupyter_text>**RetEarly2020**- Among all the industries, the Utilities sector (`Sector_naics_45`) has the highest return rate in 2020, indicating the magnitude telecommunication sector impacted by the pandemic is the least. However, the negative change of return suggests the Utilities sector faced a loss in early 2020. - Compared to the other industries, the Mining, Quarrying, and Oil and Gas Extraction sector (`Sector_naics_21`) has the lowest returns in 2020. During the pandemic, people were confined to their homes and restricted to travel, resulting in a declined oil demand and a decrease in returns, which made the energy sector the sector impacted by the pandemic the worst.**RetLate2020**- For late 2020, the Retail Trade sector (`Sector_naics_45`) has the highest return compared to the other industries. The pandemic caused a consumption shock due to the lockdown and forced shutdown across several industries. However, during recovering from the pandemic in 2020, people may have been accumulating more savings while forced to cut back on spending from lockdowns and travel restrictions. Thus, in late 2020, the Retail Trade sector may have the highest bounce in return.- In contrast, the Agriculture, Forestry, Fishing and Hunting sector (`Sector_naics_21`) has the lowest return growth among all the industries.  ii. Fixed effect regressionsRun fixed effect regressions (using industry fixed effects) to explain both *ReturnEarly2020* and *ReturnLate2020:*1. You may wish to review the lecture notes from Lecture 3 and use the Colab Notebook: Fixed_Effect_Regression_for_BA870_Lecture_3.ipynb that is posted on QTools in Lecture 3 folder.2. What is the explanatory power of these regressions using different types of industry variables. Is this high or low?Explain.Combine the industry fixed effects with other explanatory variables (see Assignment 3 and/or the financial ratios described in OPTION (3) below).   - Run combined regressions with both industry and other “X” variables. **RetEarly2020**<jupyter_code># define x as a subset of original dataframe
x1_naics = df_naics.drop(columns=['TICKER', 'RetEarly2020', 'RetLate2020', 'naics'])
# define y as a series (RetEarly2020)
y1_naics = df_naics['RetEarly2020']

# run fixed effect regression
# pass x as a dataframe, while pass y as a series
model1_naics = sm.OLS(y1_naics, x1_naics).fit()

# print out regression summary statistics
print(model1_naics.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:           RetEarly2020   R-squared:                       0.083
Model:                            OLS   Adj. R-squared:                  0.075
Method:                 Least Squares   F-statistic:                     10.88
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           1.42e-36
Time:                        02:58:33   Log-Likelihood:                -572.06
No. Observations:                2665   AIC:                             1190.
Df Residuals:                    2642   BIC:                             1326.
Df Model:                          22                                         
Covariance Type:            nonrobust                                         
===================================================================================
                      coef    std err          [...]<jupyter_text>**R-squared & Adj. R-squared**- `R-squared`: 0.083Around 8% of the variation in `RetEarly2020` is explained by the industries that the companies belong to. We can infer that returns in early 2020 are described by its business model of the type of business that operates. So 8% of the variation which varies across all these companies is explained by the sector itself.- `Adjusted R-squared`: 0.075However, the Adjusted R-squared is less than the R-squared, which is 0.075. Since Adjusted R-squared considers different independent variables against the model, a decrease inA djusted R-squared indicates the additional variables doen't improve the model more than would be expected by chance.**t-stats**Among all the variables, except `Sector_naics_49` and `Sector_naics_99`, all the sector variables have large t-values, indicating the most significant difference compared to the other models. Their p-values are also far less than 0.05. We can reject the null hypothesis and conclude that the data provide enough evidence to reject the null hypothesis, suggesting that each variable is statistically significant. Data favor the hypothesis that there is a non-zero correlation between almost all the variables and `RetEarly2020`. **RetLate2020**<jupyter_code># define x as a subset of original dataframe
x2_naics = df_naics.drop(columns=['TICKER', 'RetEarly2020', 'RetLate2020', 'naics'])
# define y as a series (RetLate2020)
y2_naics = df_naics['RetLate2020']

# run fixed effect regression
# pass x as a dataframe, while pass y as a series
model2_naics = sm.OLS(y2_naics, x2_naics).fit()

# print out regression summary statistics
print(model2_naics.summary())<jupyter_output>OLS Regression Results                            
==============================================================================
Dep. Variable:            RetLate2020   R-squared:                       0.080
Model:                            OLS   Adj. R-squared:                  0.073
Method:                 Least Squares   F-statistic:                     10.50
Date:                Thu, 08 Jul 2021   Prob (F-statistic):           4.51e-35
Time:                        02:58:33   Log-Likelihood:                -3859.9
No. Observations:                2665   AIC:                             7766.
Df Residuals:                    2642   BIC:                             7901.
Df Model:                          22                                         
Covariance Type:            nonrobust                                         
===================================================================================
                      coef    std err          [...]
{'fraction_non_alphanumeric': 0.10898255454283004, 'fraction_numerical': 0.04179898544149792, 'mean_word_length': 3.625619992369325, 'pattern_counts': {'":': 0, '<': 129, '<?xml version=': 0, '>': 129, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '551373', 'license': 'MIT', 'n_tokens_mistral': 15568, 'n_tokens_neox': 13525, 'n_words': 5021, 'path': 'Financial Analytics of Stock Returns during Covid-19 Pandemic in 2020.ipynb', 'repo_name': 'huahuang95/Financial-Analytics', 'size': '48501'}