<jupyter_start><jupyter_text><NAME> MSBA '21 Instructions: BA870 – Assignment 2 (June 2021)This is an individual assignment – You must complete this assignment on your own and it must be your own work (not copied from another BA870 student)Due: Submit to Assignments Tab (Assignment 2) by Thursday, June 17 at 8:00pm (Boston Time)Main Deliverables:- Complete Steps listed below.- Attach your Notebook (Use the name "Assignment 2 - YOUR NAME.ipynb" and your CSV file with "assign1.csv") in the Attachments part of this assignment submission.- Attach a PDF file (Use the name ""Assignment 2 - YOUR NAME.pdf") that include ALL of the text boxes and executed python code output from your Colab notebook in the Attachments part of this assignment submission. Goals:Learn about financial credit ratings. You should read this Wikipedia note (Only the section on "Credit Ratings"):https://en.wikipedia.org/wiki/S%26P_Global_RatingsUse WRDS Compustat financial variables to explain variation in companies' Credit Ratings.Apply an in-sample linear regression model (OLS) to determine which financial variables are statistically significant determinants of a company's Credit Rating.Further improve your skills to access and download companies’ financial accounting data from the S&P Compustat database using the WRDS platform (https://wrds.wharton.upenn.edu)Provide a report of your completed assignments and analysis using “Markdown” text boxes in Colab NotebookShare a Colab Notebook using shareable weblink.Print a completed and executed Colab Notebook and submit in PDF format. Steps:(1) Attached to this assignment are 2 files: File 1"assignment2.csv" contains a comma-delimited data set for 354 manufacturing companies for the fiscal year 2006. The variables in the file are:- TICKER- CREDIT RATING (From 2 (Highest Credit Rating) through 23 (Lowest Credit Rating))- CURRENT ASSETS at end of 2006 ($ millions)- CURRENT LIABILITIES at end of 2006 ($ millions)- TOTAL ASSETS at end of 2006 ($ millions)- RETAINED EARNINGS at end of 2006 ($ millions)- EARNINGS BEFORE INTEREST AND TAXES for 2006 ($ millions)- TOTAL LIABILITIES at end of 2006 ($ millions)- TOTAL SALES for 2006 ($ millions) File 2"assign2_tickers.txt" is a text file with the 354 tickers of manufacturing companies from the year 2006. You may use this file to download additional financial variables for the 524 companies from 2006 (For Fiscal Period January 2006 to December 2006) from WRDS Compustat (OPTIONAL) (2) Using the financial data in "assignment2.csv", create a list of financial ratio variables that you believe would help explain the cross-sectional variation in "CREDIT RATING".Note:- Make sure you check for outliers and "fix them" (such as winsorizing the variables at 1% and 99% values). (3) Estimate a linear regression model to explain "CREDIT RATING" using the financial ratio variables you created in step (2). NOTES:- You must report the key statistical output from the linear regression including:* R2 and AdjR2* Estimates of the coefficients for each of the explanatory variables in the regression.* The statistical significance (t0stat and p-value) for each of the explanatory variables in the regression. (4) Using textboxes in your python Notebook, explain and interpret your findings. Final Submission:- Go to the Assignments Tab on QuestromTools- Click on Assignment 2- Read this document- Make sure you put your name in a text box (Markdown) at the top of your  Notebook "Assignment 2 - YOUR NAME.ipynb"- Attach your Notebook (Use the name "Assignment 2 - YOUR NAME.ipynb" and your CSV file with "assign1.csv").- Attach a PDF file (Use the name ""Assignment 2 - YOUR NAME.pdf") that include ALL of the text boxes and executed python code output from your Colab notebook.- Insert the shared link to your completed Colab Notebook on your Google Drive in the text box below.<jupyter_code>import os

# the base Google Drive directory
root_dir = "/content/drive/MyDrive/M/MSBA - 5th Year/BA 870 - Financial and Accounting Analytics"

# choose where you want your project files to be saved
project_folder = "/Assignment 2"

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)
# !ls
import pandas as pd
import statsmodels.api as sm
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
from scipy.stats.mstats import winsorize
from sklearn.preprocessing import StandardScaler<jupyter_output>/usr/local/lib/python3.7/dist-packages/statsmodels/tools/_testing.py:19: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
  import pandas.util.testing as tm<jupyter_text>Data LoadingAdded Extra Variables book_value_per_share, shares_issued, shares_outstanding, inventory, acct_receivable, total_equity, working_cap, mkt_value, cogs, net_income, and interest_exp to caluclate more ratios<jupyter_code># load the csv files
df = pd.read_csv("assignment2.csv")
# df.sample(5)<jupyter_output><empty_output><jupyter_text>Data Engineering<jupyter_code>df["book_value"] = df.book_value_per_share * df.shares_outstanding # Book Value
df["acid_ratio"] = (df.current_assets - df.inventory)/df.current_liabilities # Acid Ratio
df["current_ratio"] = df.current_assets / df.current_liabilities # Current Ratio

df["coverage_ratio"] = df.ebit / df.interest_exp # Interest Coverage Ratio
df['bep'] = df.ebit / df.total_assets # Basic Earnings Power Ratio

df["inventory_turnover"] = df.cogs / (df.inventory/365) # Inventory Turnover
df["dso"] = df.acct_receivable / (df.total_sales/365) # Days Sales Outstanding

df["roa"] = df.net_income / df.total_assets # Return on Assets
df["roe"] = df.net_income / df.total_equity # Return on Equity

df['debit_asset_ratio'] = df.total_liabilities / df.total_assets # Debit to Asset Ratio
df["debit_equity_ratio"] = df.total_liabilities / df.total_equity # Debit to Equity Ratio
df.columns<jupyter_output><empty_output><jupyter_text>Data Summary<jupyter_code>df.info()
df.describe().T<jupyter_output><empty_output><jupyter_text>EDAVisual null values and Histogram of numeric variables<jupyter_code>plt.figure(figsize=(10, 8))
plt.title("Null values")
sns.heatmap(df.isnull(),yticklabels=False,cbar=False)
plt.show()
# infinity and nonnumeric values not able to be plotted 
numeric_col = list(df.drop(["coverage_ratio","inventory_turnover"],axis =1).describe(include=[np.number]).columns)  
df_numeric = df[numeric_col]

fig, axes = plt.subplots(len(numeric_col)//4, 4, figsize=(10, 20))

i = 0
for triaxis in axes:
    for axis in triaxis:
        df_numeric.hist(column = numeric_col[i], ax=axis)
        i = i+1<jupyter_output><empty_output><jupyter_text>Data CleaningAttempted to windsorized data<jupyter_code># def fences(df, variable_name):    
#     q1 = df[variable_name].quantile(0.25)
#     q3 = df[variable_name].quantile(0.75)
#     iqr = q3-q1
#     outer_fence = 3*iqr
#     outer_fence_le = q1-outer_fence
#     outer_fence_ue = q3+outer_fence
#     return outer_fence_le, outer_fence_ue

# # Printed Variable Outer Quartiles
# for col in df_numeric.columns:
#     outer_fence_le, outer_fence_ue = fences(df, col)
#     print(f'{col}\nLower end outer fence:\t{outer_fence_le}\nUpper end outer fence:\t{outer_fence_ue}\n')


def df_winsorize(df,lower,upper):
    
    #Create copy of df
    df_win = df.copy(deep=True)

    # Winsorize for loop
    for col in df.describe(include=[np.number]).columns:
        df_win[col]= winsorize(df[col], limits=(lower,upper))
    
    return df_win

df_win = df_winsorize(df,0.01,0.99)

df_win.describe().T<jupyter_output><empty_output><jupyter_text>Variable Explainations- 'ticker'- 'current_assets'- 'total_assets'- 'ebit',- 'current_liabilities'- 'total_liabilities'- 'retained_earnings'- 'total_sales'- 'credit_rating'- 'book_value_per_share'- 'shares_issued'- 'shares_outstanding'- 'inventory'- 'acct_receivable'- 'total_equity'- 'working_cap'- 'mkt_value'- 'cogs'- 'net_income'- 'interest_exp'- 'book_value' Ratios- **'acid_ratio' -** liquidity ratio, which measures the ability of a company to use its near cash or quick assets to extinguish or retire its current liabilities immediately- **'current_ratio' -** liquidity ratio that measures whether a firm has enough resources to meet its short-term obligations- **'coverage_ratio' -** measure of a company's ability to honor its debt payments- **'bep' -** profitability ratio which determines how effectively a firm uses its assets to generate income.- **'inventroy_turnover' -** measure of the number of times inventory is sold or used in a time period such as a year- **'dso' -** calculation used by a company to estimate the size of their outstanding accounts receivable- **'roa' -** measure of how profitable a company's assets are in generating revenue- **'roe' -**  measure of the profitability of a business in relation to the equity- **'debit_asset_ratio' -** leverage ratio that defines the total amount of debt relative to assets owned by a company- **'debit_equity_ratio' -**  leverage ratio that indicates how much debt a company is using to finance its assets relative to the value of shareholders' equity  Model 1Dropped Coverage ratio adn inventroy turnover ratio because of infinite vaules.<jupyter_code>df_model_1 = df[['acid_ratio', 'current_ratio', 'bep','dso', 'roa', 'roe', 
                 'debit_asset_ratio','debit_equity_ratio','credit_rating']].dropna()

# Dependent Variables
X = df_model_1[['acid_ratio', 'current_ratio',  'bep', 'dso', 'roa', 'roe', 
                'debit_asset_ratio', 'debit_equity_ratio']]

X = sm.add_constant(X)

#Independent Variables
Y = df_model_1[['credit_rating']]

# Fit Model
model = sm.OLS(Y, X).fit()

# # Predict Model
# predictions = model.predict(X) 

# Summary Statisitcs
print(model.summary(title="Model # 1"))<jupyter_output>Model # 1                                   
==============================================================================
Dep. Variable:          credit_rating   R-squared:                       0.425
Model:                            OLS   Adj. R-squared:                  0.405
Method:                 Least Squares   F-statistic:                     21.86
Date:                Thu, 24 Jun 2021   Prob (F-statistic):           8.30e-25
Time:                        20:28:16   Log-Likelihood:                -593.42
No. Observations:                 246   AIC:                             1205.
Df Residuals:                     237   BIC:                             1236.
Df Model:                           8                                         
Covariance Type:            nonrobust                                         
======================================================================================
                         coef    std err    [...]<jupyter_text>Despite not having a very important metric such as the interest coverge ratio and a third of our data points were we able to get a high R squared.Large coeficents of Acid Ratio, Current Ratio, Basic Earning Power, Debit to Asset Ratio seems to show high statisical predictive power to credit rating.  Model 2 Dropped Equity ratios to use more data points<jupyter_code>df_model_2 = df[['acid_ratio', 'current_ratio', 'bep','dso', 'roa', 'debit_asset_ratio','credit_rating']].dropna()

# Dependent Variables
X = df_model_2[['acid_ratio', 'current_ratio',  'bep', 'dso', 'debit_asset_ratio']]

X = sm.add_constant(X)

#Independent Variables
Y = df_model_2[['credit_rating']]

# Fit Model
model2 = sm.OLS(Y, X).fit()

# # Predict Model
# predictions = model.predict(X) 

# Summary Statisitcs
print(model2.summary(title="Model # 2"))<jupyter_output>Model # 2                                   
==============================================================================
Dep. Variable:          credit_rating   R-squared:                       0.426
Model:                            OLS   Adj. R-squared:                  0.418
Method:                 Least Squares   F-statistic:                     51.19
Date:                Thu, 24 Jun 2021   Prob (F-statistic):           1.29e-39
Time:                        20:28:16   Log-Likelihood:                -849.38
No. Observations:                 351   AIC:                             1711.
Df Residuals:                     345   BIC:                             1734.
Df Model:                           5                                         
Covariance Type:            nonrobust                                         
=====================================================================================
                        coef    std err      [...]<jupyter_text>Decided to look at model that drop variables with high null values or low statistical significance such ROA, ROE,and Debit-to-Equity.Significant variables such as Acid Ratio, Current Ratio, Basic Earning Power, Debit to Asset Ratio seemed to stay the same.  Model 3 Not really sure what the problem was but when I windsorized my data the R^2 was negative infinity.<jupyter_code>df_model_3 = df_win[['acid_ratio', 'current_ratio', 'coverage_ratio', 'bep',
       'inventory_turnover', 'dso', 'roa', 'roe', 'debit_asset_ratio',
       'debit_equity_ratio','credit_rating']]
# Dependent Variables
X = df_model_3[['acid_ratio', 'current_ratio', 'coverage_ratio', 'bep',
       'inventory_turnover', 'dso', 'roa', 'roe', 'debit_asset_ratio',
       'debit_equity_ratio']]

X = sm.add_constant(X)

#Independent Variables
Y = df_model_3[['credit_rating']]

# Fit Model
model3 = sm.OLS(Y, X).fit()

# # Predict Model
# predictions = model.predict(X) 

# Summary Statisitcs
print(model3.summary(title="Model # 3"))<jupyter_output>Model # 3                                   
==============================================================================
Dep. Variable:          credit_rating   R-squared:                        -inf
Model:                            OLS   Adj. R-squared:                   -inf
Method:                 Least Squares   F-statistic:                      -inf
Date:                Thu, 24 Jun 2021   Prob (F-statistic):                nan
Time:                        20:28:16   Log-Likelihood:                 11623.
No. Observations:                 354   AIC:                        -2.324e+04
Df Residuals:                     353   BIC:                        -2.324e+04
Df Model:                           0                                         
Covariance Type:            nonrobust                                         
======================================================================================
                         coef    std err    [...]<jupyter_text>Model 4Last model I will try dropping infinite values<jupyter_code>df_model_3 = df[['acid_ratio', 'current_ratio', 'coverage_ratio', 'bep',
       'inventory_turnover', 'dso', 'roa', 'debit_asset_ratio','credit_rating']].replace([np.inf, -np.inf], np.nan).dropna(axis=0)


# Dependent Variables
X = df_model_3[['acid_ratio', 'current_ratio', 'coverage_ratio', 'bep',
       'inventory_turnover', 'dso', 'roa','debit_asset_ratio']]

X = sm.add_constant(X)

#Independent Variables
Y = df_model_3[['credit_rating']]

# Fit Model
model3 = sm.OLS(Y, X).fit()

# # Predict Model
# predictions = model.predict(X) 

# Summary Statisitcs
print(model3.summary(title="Model # 3"))<jupyter_output>Model # 3                                   
==============================================================================
Dep. Variable:          credit_rating   R-squared:                       0.447
Model:                            OLS   Adj. R-squared:                  0.434
Method:                 Least Squares   F-statistic:                     34.11
Date:                Thu, 24 Jun 2021   Prob (F-statistic):           2.93e-39
Time:                        20:28:16   Log-Likelihood:                -831.22
No. Observations:                 346   AIC:                             1680.
Df Residuals:                     337   BIC:                             1715.
Df Model:                           8                                         
Covariance Type:            nonrobust                                         
======================================================================================
                         coef    std err    [...]
{'fraction_non_alphanumeric': 0.12451407704087643, 'fraction_numerical': 0.021734008717163387, 'mean_word_length': 2.9884895466290815, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '242314', 'license': 'MIT', 'n_tokens_mistral': 5216, 'n_tokens_neox': 4690, 'n_words': 1605, 'path': 'Assignment 2/Assignment #2 - Chiebuka Onwuzurike.ipynb', 'repo_name': 'conwuzurike/BA-870', 'size': '16991'}