Group Project - Filtering Badly Performing Companies
by Jonathan Ricketts, Kontee Siravongtanawadi, Russ Tselentis
import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import sys
print(sys.version)
3.7.12 (default, Oct 12 2021, 03:36:26)
[GCC 8.3.0]
!pip install xlrd
import xlrd
Collecting xlrd
Downloading xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
|████████████████████████████████| 96 kB 863 kB/s
Installing collected packages: xlrd
Successfully installed xlrd-2.0.1
WARNING: You are using pip version 20.1.1; however, version 21.3.1 is available.
You should consider upgrading via the '/root/venv/bin/python -m pip install --upgrade pip' command.
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()
<class 'pandas.core.frame.DataFrame'>
Index: 5294 entries, NasdaqGS:FLWS to NasdaqGS:ZNGA
Data columns (total 38 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 name 5294 non-null object
1 comp_type 5294 non-null object
2 exchange 4904 non-null object
3 industry 5294 non-null object
4 sector 5294 non-null object
5 marketcap 5282 non-null float64
6 total_debt 4998 non-null float64
7 shrout 5282 non-null float64
8 revenue 5156 non-null float64
9 gross_profit 5160 non-null float64
10 sga 5160 non-null float64
11 advertising 5160 non-null float64
12 marketing 5160 non-null float64
13 sales_marketing 5160 non-null float64
14 gen_admin 5160 non-null float64
15 rd_expense 5160 non-null float64
16 int_expense 5160 non-null float64
17 op_lease_int 5160 non-null float64
18 ebitda 5156 non-null float64
19 ebit 5160 non-null float64
20 net_income 5160 non-null float64
21 cash 4920 non-null float64
22 capex 4917 non-null float64
23 acquisitions 4913 non-null float64
24 changeNWC 5046 non-null float64
25 beta1 4351 non-null float64
26 beta2 4351 non-null float64
27 beta5 4101 non-null float64
28 etr 3424 non-null float64
29 tev 4641 non-null float64
30 gRev1yr 4280 non-null float64
31 gRev3yr 3967 non-null float64
32 ebit_margin 3529 non-null float64
33 ebitda_margin 3510 non-null float64
34 gross_margin 3851 non-null float64
35 roe 4481 non-null float64
36 roc 4161 non-null float64
37 altman 3509 non-null float64
dtypes: float64(33), object(5)
memory usage: 1.6+ MB
None
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()
<class 'pandas.core.frame.DataFrame'>
Index: 5282 entries, NasdaqGS:FLWS to NasdaqGS:ZNGA
Data columns (total 38 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 name 5282 non-null object
1 comp_type 5282 non-null object
2 exchange 5282 non-null object
3 industry 5282 non-null object
4 sector 5282 non-null object
5 marketcap 5282 non-null float64
6 total_debt 5282 non-null object
7 shrout 5282 non-null float64
8 revenue 5282 non-null float64
9 gross_profit 5152 non-null float64
10 sga 5152 non-null float64
11 advertising 5152 non-null float64
12 marketing 5152 non-null float64
13 sales_marketing 5152 non-null float64
14 gen_admin 5152 non-null float64
15 rd_expense 5152 non-null float64
16 int_expense 5152 non-null float64
17 op_lease_int 5152 non-null float64
18 ebitda 5148 non-null float64
19 ebit 5152 non-null float64
20 net_income 5152 non-null float64
21 cash 4919 non-null float64
22 capex 4910 non-null float64
23 acquisitions 4906 non-null float64
24 changeNWC 5039 non-null float64
25 beta1 4351 non-null float64
26 beta2 4351 non-null float64
27 beta5 4101 non-null float64
28 etr 3416 non-null float64
29 tev 5282 non-null float64
30 gRev1yr 4279 non-null float64
31 gRev3yr 3966 non-null float64
32 ebit_margin 3528 non-null float64
33 ebitda_margin 3509 non-null float64
34 gross_margin 3850 non-null float64
35 roe 4478 non-null float64
36 roc 4158 non-null float64
37 altman 3508 non-null float64
dtypes: float64(32), object(6)
memory usage: 1.6+ MB
#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