Group Project - Filtering Badly Performing Companies

import numpy as np import pandas as pd import seaborn as sns import matplotlib.pyplot as plt import sys print(sys.version)

!pip install xlrd import xlrd

fnamein = 'Company Screening Report 0yr Lag.xls' df_mult0 = pd.read_excel(fnamein,sheet_name='Screening',header=None,na_values=['-','NM'],skiprows=8) i = ['name','ex:tick','comp_type','exchange','industry','sector','marketcap','total_debt','shrout','revenue','gross_profit','sga','advertising','marketing','sales_marketing','gen_admin','rd_expense','int_expense','op_lease_int','ebitda','ebit','net_income','cash','capex','acquisitions','changeNWC','beta1','beta2','beta5','etr','tev','gRev1yr','gRev3yr','ebit_margin','ebitda_margin','gross_margin','roe','roc','altman'] df_mult0.columns = i df_mult0.dropna(axis=0, how='any', subset=['ex:tick'],inplace=True) df_mult0.set_index('ex:tick',inplace=True, verify_integrity=True) print(df_mult0.info()) df_mult0.describe()

df_mult0.loc[:,'revenue'] = np.where(df_mult0['revenue'] <= 0, np.nan, df_mult0['revenue']) df_mult0.loc[:,'tev'] = np.where(df_mult0['tev'] <= 0,np.nan, df_mult0['tev']) df_mult0['revenue'].fillna(value=0,inplace=True) df_mult0['tev'].fillna(value=0,inplace=True) df_mult0.dropna(axis=0, how='any', subset=['revenue','tev'], inplace=True) df_mult0.loc[:,['revenue','tev']].describe()

df_mult0[df_mult0.exchange.isna()]

df_mult0['exchange'].fillna(value='Non_US', inplace=True) df_mult0.isnull()

df_mult0.dropna(axis=0, how='any', subset=['marketcap'], inplace=True)

df_mult0.loc[df_mult0.total_debt.isna()]

df_mult0['total_debt'].fillna(value = 'nan',inplace=True) df_mult0.info()

#We set the following criteria to filter out the worst performing companies in the ~5000 company dataset. idx_margin = (df_mult0.ebit_margin < 5) idx_roc = (df_mult0.roc < 5) idx_tev = (df_mult0.tev < 100) idx_cash = (df_mult0.cash < 50) idx_growth = (df_mult0.gRev1yr < -5) idx_beta = (df_mult0.beta2 > 0.8) & (df_mult0.beta2 < 1.2) idx = idx_margin & idx_roc & idx_growth & idx_beta & idx_cash & idx_tev df_mult0.loc[idx,['ebit_margin','roc','gRev1yr','beta2','tev','cash']].describe()

#calculate EPS and share price df_mult0['eps'] = df_mult0['net_income']/df_mult0['shrout'] df_mult0['price_per_share'] = df_mult0['marketcap']/df_mult0['shrout'] #get P/E ratio df_mult0['price_earnings'] = df_mult0['price_per_share']/df_mult0['eps'] # check out your multiple df_mult0[idx]['price_earnings'].describe()

df_mult0[idx].sort_values(by='price_earnings')

df_mult0[idx]['price_earnings'].hist();

#Based on the Price to Earnings ratio, we believe that Communications Systems, Inc. (NasdaqGM:JCS) is an immediate SELL #It is clearly an underperformer, even when compared with other poorly performing companies in this universe #In the histogram above, you can see how the P/E ratio is so low (-325), that it is a standout in this distribution

#EV to Sales ratio df_mult0['ev_sales'] = df_mult0['tev']/df_mult0['revenue'] df_mult0[idx]['ev_sales'].describe()

df_mult0[idx].sort_values(by='ev_sales')

df_mult0[idx]['ev_sales'].hist();

#Based on EV/ Sales, we believe that SigmaTron International, Inc. (NasdaqCM:SGMA) is an immediate SELL #It had the lowest EV/Sales ratio of any company in this universe with a non-null Enterprise Value