# start by loading in our typical set of packages
import numpy as np # NumPy
import pandas as pd # pandas
import seaborn as sns
import matplotlib.pyplot as plt
import sys
print(sys.version)
# use pip to install the package 'xlrd'
# need it to read the excel file below
!pip install xlrd
fnamein = 'Company Screening Report 0yr Lag.xls'
# use pandas to read the excel file. Open it if you like and check out why the parameters are set the way they are.
df_mult0 = pd.read_excel(fnamein,sheet_name='Screening',header=None,na_values=['-','NM'],skiprows=8)
# here we are making our own column names instead of the longer ones in the Excel file.
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']
# set column names
df_mult0.columns = i
# We are going to make the ticker our index - i.e. the unique identifier. So we can't have any NA values, so .dropna()
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()
# fill in the two variables mentioned above to see their summary stats
df_mult0.loc[:,['revenue','tev']].describe()
# let's fix that. What should we do? Pick some (not all) of the following
# set values that are nonsensical to NaN, keep the rest the same?
df_mult0.loc[:,'revenue'].where(df_mult0['revenue'] >= 0, other=np.nan, inplace = True)
df_mult0.loc[:,'tev'].where(df_mult0['tev'] >= 0, other = np.nan, inplace = True)
# fill the NaN values with something?
# drop NaN values?
df_mult0.dropna(axis=0, how='any', subset=['revenue','tev'], inplace=True)
# see if you solved the problem
df_mult0.loc[:,['revenue','tev']].describe()
# let's look at the dataframe, but only where the 'exchange' variable is NaN
df_mult0[df_mult0.exchange.isna()]
# this looks a lot like they are just international firms. We should keep these.
# Let's fill them with a value that may help us sort later.
# Also, later functions won't like NaN in our data.
df_mult0['exchange'].fillna(value='NonUS', inplace=True)
#all this does is replace the NaN above with the text NonUS for Non-US Exchanges.
#check how many NaN are left in the data frame
#what function did we use above to see the non-null counts?
df_mult0[df_mult0.exchange.isna()]
# working from the top, the next one with missing values is Market Cap. (depending on what you did exactly - it may be ok)
# if the firm is missing MarketCap, then that means we don't have stock price or shares outstanding.
# Either is a problem, so we have to drop these firms. How do we do that?
# drop NaN values
df_mult0.dropna(axis=0, how='any', subset=['marketcap'], inplace=True)
# next we see total_debt.
# what does it mean if total_debt is missing? It likely means that the firm just has no debt.
# filter the Data Frame by only looking at firms where total_debt is NaN.
df_mult0.loc['total_debt'] = np.nan
# Maybe we need to look by sector. Where are these firms with missing total_debt?
# Let's look at how many NaN we have in total_debt by sector.
# the first part is the same filter as above, then use the groupby() function on the variable 'sector'
df_mult0.loc[df_mult0.total_debt.isna()].groupby(['sector']).count()
# here is a nifty trick - combine two conditional statements: which firms have NaN total_debt and are NOT in the 'Financials' sector
# (note the parentheses around each conditional statement - this is important)
df_mult0.loc[(df_mult0.total_debt.isna()) & (df_mult0.sector != 'Financials')]
# since we feel comfortable that these are normal companies but that they just have 0 total_debt, let's fill the NaN with 0
df_mult0['total_debt'].fillna(value = 0, inplace = True) # make sure you set the correct second parameter to update the DataFrame.
df_mult0.info()
# here is another trick, though you may not need it.
# what if a firm has EBIT but not EBITDA? We want to keep the firm, so we fill the EBITDA field with the EBIT, assuming that DA are 0 instead of NaN
df_mult0['ebitda'].fillna(value=df_mult0['ebit'], inplace=True)
You are now a lot of the way there. The goal is to do this kind of analysis all the way down for all of your variables, finding why there is any odd data - negatives, NaN, zeros, etc. You can use filters, look at the data, summary stats, etc.
# lets put all of our conditions into a single index object, then use that to filter our dataframe
# do an internet search for "pandas dataframe slice by multiple conditions" if you want more info on what I'm doing here
# each of these create a boolean series using specific criteria.
idx_margin = (df_mult0.ebit_margin > 10) & (df_mult0.ebit_margin < 20)
idx_roc = (df_mult0.roc > 10) & (df_mult0.roc < 20)
idx_growth = (df_mult0.gRev1yr > 0) & (df_mult0.gRev1yr < 20)
idx_beta = (df_mult0.beta2 > 0.8) & (df_mult0.beta2 < 1.2)
# these are all boolean conditions, so we just use AND to combine them
idx = idx_margin & idx_roc & idx_growth & idx_beta
# now we pass idx for the row portion, and the list of columns we want to see
df_mult0.loc[idx,['ebit_margin','roc','gRev1yr','beta2']].describe()
# now, compute the EV/lEBITDA multiple. Or EV/EBIT. Or perhaps Price/Sales (MarketCap/Revenue).
# note: you may want to use np.where() if your denominator is zero. You'll see inf or NaN for your multiple in that case.
df_mult0['ev_ebitda'] = df_mult0['tev']/df_mult0['ebitda']
# check out your multiple
df_mult0[idx]['ev_ebitda'].describe()
df_mult0[idx].sort_values(by='ev_ebitda',ascending = True)
# want to do a histogram?
df_mult0[idx]['ev_ebitda'].hist();
#Very poorly performing companies
idx_margin = (df_mult0.ebit_margin > 5) & (df_mult0.ebit_margin < 12.5)
idx_roc = (df_mult0.roc > 8) & (df_mult0.roc < 15)
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
df_mult0[idx].sort_values(by='ev_ebitda',ascending = False)
#df_mult0.loc[idx,['ebit_margin','roc','gRev1yr','beta2']].describe()
#distribution of multiples for poor performing companies
df_mult0[idx]['ev_ebitda'].hist();
We filtered the companies based on certain criteria that we felt encompassed poor performance standards. We selected the metrics EBIT margin, return on capital, revenue, and beta and set specific ranges. We sorted the list of poor performing companies by each firm's EV/EBITDA multiple in descending order where we were able to identify three companies in particular that met our criteria. Those three companies were Nike (NKE), Tetra Tech (TEK), and CorVel Corporation (CRVL). Based on the metrics we selected, these companies appeared overvalued and were assigned a sell rating by our group.
#High performing companies
idx_margin = (df_mult0.ebit_margin > 20)
idx_roc = (df_mult0.roc > 15)
idx_growth = (df_mult0.gRev1yr > 25)
idx_beta = (df_mult0.beta2 > 0.7) & (df_mult0.beta2 < 1.5)
idx = idx_margin & idx_roc & idx_growth & idx_beta
df_mult0[idx].sort_values(by='ev_ebitda',ascending = True)
#df_mult0.loc[idx,['ebit_margin','roc','gRev1yr','beta2']].describe()
#distribution of multiples for high performing companies
df_mult0[idx]['ev_ebitda'].hist();
We repeated the same process for the high performing companies. We selected ranges with the same metrics and adjusted the ranges accordingly. We sorted the high performing firms by the EV/EBITDA multiple in ascending order where we were able to identify three companies in particular that met our criteria. These companies: Great Panther Mining (GPR), Sage Therapeutics (SAGE), B2Gold (BTO). Based on the metrics we selected, these companies appeared undervalued and were assigned a buy rating by our group.