Equity analysis was a take-home case I was given as part of an interview process for a fin-tech company I interviewed for.
Excel file is a time-series data for public companies with a few relevant attributes. Also, some not relevant attributes were added to test a candidate’s ability to recognize essential and functional data for the task from irrelevant data.
Detailed commentary and steps are in iPython notebook comments, please see file below.
For download:
Raw Excel file
iPython notebook on GitHub
Tableau visualization workbook
Data exploration using iPython notebook
In [1]:
import pandas as pd
In [2]:
df = pd.read_csv('equity_data.csv')
In [3]:
df.head()
Out[3]:
In [4]:
df.describe()
Out[4]:
In [5]:
# Interestingly enough, only Returns and Capitalization came back. Let's check types of other columns
In [6]:
df.dtypes
Out[6]:
In [7]:
# As suspected, columns like Mkt Cap, Free Cash Flow etc are objects not numbers, let'a change that.
In [8]:
# First, let's rename column names with SPACE "_"
df.rename(columns = {'Company Name' : 'Company_Name',
'Mkt Cap' : 'Mkt_Cap',
'FCF Yield' : 'FCF_Yield',
'Sales Growth 1Y': 'Sales_Growth_1Y',
'Sales Growth 5Y': 'Sales_Growth_5Y',
'EPS Revision' : 'EPS_Revision'},
inplace = True)
In [9]:
# Quality check
df.dtypes
Out[9]:
In [10]:
# We can't convert NaN to number, so let's change NaN to zero because that will not affect out analysis.
df.isna().sum()
Out[10]:
In [11]:
# change NaN to zero values
df.loc[df['Returns'].isnull(), 'Returns'] = '0'
df.loc[df['Mkt_Cap'].isnull(), 'Mkt_Cap'] = '0'
df.loc[df['FCF_Yield'].isnull(), 'FCF_Yield'] = '0'
df.loc[df['Momentum'].isnull(), 'Momentum'] = '0'
df.loc[df['Sales_Growth_1Y'].isnull(), 'Sales_Growth_1Y'] = '0'
df.loc[df['Sales_Growth_5Y'].isnull(), 'Sales_Growth_5Y'] = '0'
df.loc[df['EPS_Revision'].isnull(), 'EPS_Revision'] = '0'
In [12]:
# Quality check
df.isna().sum()
Out[12]:
In [13]:
df.dtypes
Out[13]:
In [14]:
# Converting object to number/float
df['Returns'] = df.Returns.astype(float)
In [15]:
# Returns column is now float64
df.dtypes
Out[15]:
In [16]:
# Next let's do Mkt_Cap
df['Mkt_Cap'] = df.Mkt_Cap.astype(float)
In [17]:
# There is a string error. We can't convert A to a number so, let's see what the problem is
In [18]:
# After inspecting '1,130.74' it seems that the comma is problematic, so let's strip it out
In [19]:
# Converting to float but also stipping the comma so we get a string number that we can convert
df['Mkt_Cap'] = df.Mkt_Cap.str.replace(',', '').astype(float)
In [20]:
# No error is a good sign
In [21]:
# Quality check proves we have sucessfully converted Mkt_Cap object/tring to float/number
df.dtypes
Out[21]:
In [22]:
df['FCF_Yield'] = df.FCF_Yield.str.replace(',', '').astype(float)
In [23]:
df['Momentum'] = df.Momentum.str.replace(',', '').astype(float)
In [24]:
df['Sales_Growth_1Y'] = df.Sales_Growth_1Y.str.replace(',', '').astype(float)
In [25]:
df['Sales_Growth_5Y'] = df.Sales_Growth_5Y.str.replace(',', '').astype(float)
In [26]:
df['EPS_Revision'] = df.EPS_Revision.str.replace(',', '').astype(float)
In [27]:
# Quality check = great
df.dtypes
Out[27]:
In [28]:
# Now we can perform some statistical checks, let's try it with describe function
df.describe()
Out[28]:
In [36]:
# We can see that we're finally operating with numbers, so math and stats operations are now possible
In [37]:
# Convert string to DateTime
df['Date'] = pd.to_datetime(df.Date)
In [38]:
# Counting number of records per year
pd.to_datetime(df['Date']).dt.year.value_counts()
Out[38]:
In [39]:
# We can clearly see that year 2006 is incomplete with records
# Also 2017 looks the same, probably the data was gathered mid-2017
# 2006 should be excluded from analysis because it will give false information/trends/
# 2017 could be used as a trend and compared to previous years but for simplicity I will exclude it from analysis
In [40]:
# For curiosity I will check if all companies have the same # of records/data
df.groupby(df.Ticker.str.strip("'"))['Date'].nunique()
Out[40]:
In [41]:
# An interesting finding, there are differences in a number of records per company, could be for many reasons
# we can use this knowledge for refinement in deeper analysis, for the time being let's just be aware of this
In [42]:
# Quick check on correlation across the data
df.corr(method ='pearson')
Out[42]:
In [43]:
# Capitalization has a value 1, so we can disregard it, but I will not drop it for file-originality reasons.
In [44]:
# I am happy how everything looks now and let's save this work to csv file.
df.to_csv('Cleaned_equity_data.csv')
In [45]:
# I can visualize in Matplotlib or Seaborn, but our file is fairly small so I will use Tableau
# END.
