4 Financial Metrics Used to Evaluate Stocks using Python & Pandas

Published in

DataDrivenInvestor

10 min readSep 26, 2022

In this article, we will use Python to calculate 4 most commonly used financial measures. These metrics are used to evaluate particular company when trading stocks based on their Financial & Income Statements.

Please note that these are not the only ones used in the real world. This article serves as an introductory tutorial that doesn’t serve as a financial advice in any way.

Feel free to skip around depending on your interest or go straight to GitHub for copy/paste of the source code.

Table of Content

Environment & Workspace Set Up
Financial Metric (FM) 1: Earnings-Per-Share (EPS)
FM 2: Return on Equity (ROE)
FM 3: Debt to Equity (D/E)
FM 4: Quick Ratio
Conclusion
References

First section will focus on setting up the environment within which we can run Python functions. Then, we will discuss each metric separately as its own section. Each metric section will follow the same format: 1) present the formula, 2) intuition, 3) example 4) Python implementation

Environment & Workspace Set Up

1. Environment Set up

We will be using Jupyter Notebook in this tutorial. Please refer to this article that has installation instructions along with some helpful Jupyter Notebook extensions.

Specifically, we will need following libraries installed in our environment:

pip install xlrd
pip install pandas

Here is another tutorial on how to set up your Python environment from scratch that I wrote.

In this article, I assume that you have working Jupyter Notebook. Alternatively, you can use Google Collab tool that provides free resource.

2. Get Data

I used data from Morningstar website for particular stock. In our case, we will use Microsoft data. Navigate to “Financials” tab and click on “Expand Details View” below Financial Summary as shown in the image below.

Once you expand the view, you will see “Export” button on the right-hand side of the page.

This will give you .xls (Excel) formatted document. As next step you could use online services that provide live API data and fetch it. You can check out an article about what is an API and how to use it here. But for now, we will work with static Excel file.

Here is link to downloaded and unprocessed data.

3. Data Pre-Processing

Now that we have Excel document, we need to adjust it so it is ready for all the manipulation we want to do.

Specifically, we will need to do the following: (1) read the data, (2) clean up all the empty cells, (3) convert all the cells into number instead of strings, (4) clean up column names, and (5) transpose the dataset.

I highly encourage checking out Jupyter Notebook for step-by-step guidance in this case to learn the details (and also for copy/paste of commands).

Data pre-processing is usually the most tedious part and always varies depending on your dataset. So above steps are not all encompassing nor generic enough for the dataset pre-processing.

Picture below outlines steps (1) — (3) where we read data indicated by [9] within the picture (left-hand side). We replace all the NaN values with zero in [10], and remove all the numbers that have comma in [11]. Lastly, we iterate column by column to convert data into numbers from strings in [13].

The screenshot below shows how we rename column as Name for ease of reference on line [16]. On line [18], we remove trailing and leading empty space from each column name.

More importantly, I transposed the dataset on line [17]. You don’t have to do it, but I found it easier to work with columns rather than rows, which you will see when we will calculate ratios in next steps.

Now, our dataset is ready for consumption

Financial Metric 1: Earnings-Per-Share (EPS)

Formula: EPS = Company’s Net Income / Total Number of Shares Outstanding

Intuition: As in investor you care about profit… or usually that is your first thought. EPS attempts to capture company’s effort to generate that profit. In other words, how much of company’s generated profit can be allocated to one share of the stock [1].

Interpretation: Higher EPS indicates higher profit generated relative to outstanding stocks*

Example:

Stock A generated $150 million in fiscal year. After tax and operating expenses, it has left $70 million as net income. During the year, Stock A has a total of 100 million shares outstanding.
Stock B generated $120 million in fiscal year. The net income is $100 million. There are only $50 million shares outstanding
For Stock A, the EPS score is 0.7 and Stock B, the EPS score is 2. In this case Stock B (2) > Stock A (0.7), which means that B generated more money relative to total number of shares outstanding.

Implementation:

First, create empty data frame, which we will use to store our results:

df_results = pd.DataFrame()

Second, we will get all the columns that contain word EPS

df_eps_results = df_income_T.filter(regex='EPS')

The above command will give us the following:

As you can see financial statements already contain EPS information. But for purposes of exercise, we will calculate our own EPS value. To do so, run the following two division operations

df_results["Basic_EPS_Manual"] = df_income_T["Net Income Available to Common Stockholders"] / df_income_T["Basic Weighted Average Shares Outstanding"]df_results["Diluted_EPS_Manual"] = df_income_T["Net Income Available to Common Stockholders"] / df_income_T["Diluted Weighted Average Shares Outstanding"]

Lastly, let’s compare our manual calculations of EPS values with existing values to verify our results. Join two data frames by running this command:

df_eps_results = df_income_T.filter(regex='EPS')

As you can see from figure above our manual column and pre-existing values are close, which confirms our calculations

Financial Metric 2: Return on Equity (ROE)

Formula: ROE = Net Income / Total Shareholder Equity

Intuition: Time is money and you would rather invest it or spend it that brings you the most value at this time. This metric estimates company’s profitability. In other words, using shareholder capital (money), how good is company at making more money by using shareholder capital [2].

Interpretation: Higher ROE is better indicator of company profitability *

Example:

Stock A net income is $120 million, and shareholder equity is $40 million, which means ROE is $3 million.
Stock B net income is $500 million, and shareholder equity is $25 million, which means ROE is $20 million.
Since Stock A < Stock B, ROE is higher for Stock B, which means B is better at making you more money (i.e. more profitable).

Implementation:

Clear our variable where we will store our results:

df_results = pd.DataFrame()

Using regex, get Net Income available in the financial statement, which is shown below. I used regex as an example to fetch all the columns that contain particular string you specify. If you know name of the column, you can also write df[“Column Name”].

Similarly to Net Income, add Total Assets and Liabilities to the df_results variable to get one data frame that contains all the relevant information we need in one data structure:

Find Total Shareholder equity based on formula [5]:

df_results["Total Shareholder Equity"] = df_results["Total Assets"] - df_results["Total Liabilities"]

Last step is to find ROE in %

df_results["ROE %"] = (df_results["Net Income Available to Common Stockholders"] / df_results["Total Shareholder Equity"]) * 100df_results["Diluted ROE %"] = (df_results["Diluted Net Income Available to Common Stockholders"] / df_results["Total Shareholder Equity"]) * 100

As we display df_results data frame, we should see both ROE and Diluted ROE variables. Just as sanity check I compared values outlined here [6] and it seems to be relatively close.

Financial Metric 3: Debt to Equity (D/E)

Formula: D/E = Total Liabilities / Total Shareholder Equity

Intuition: Unlike previous ratios above, this one focuses on risk, instead of opportunity. This metric evaluates how much of borrowed money company uses to operate its day-to-day activities. In other words, the ratio gives a measure of how much debt there is for every $1 of equity.

Interpretation: The higher D/E ratio indicates, the more indebted the company is. On its own the value might not give sufficient information, but comparison to other similar companies might give glimpse while using the metric*

Example:

Stock A owes $40 million, and shareholder equity is $120 million, which means D/E is 0.3
Stock B owes $50 million, and shareholder equity is $120 million, which means D/E is 0.41
Since Stock A < Stock B, D/E is higher for Stock B and as a result Stock B is riskier investment compared to Stock A

Implementation:

As we did above, set the result data frame where we will store our result:

df_results = pd.DataFrame()

Here we are calculating (again) Total Shareholder Equity as we did for ROE

df_results['Total Assets'] = df_balance_T.filter(regex='Total Assets')df_results['Total Liabilities'] = df_balance_T.filter(regex='Total Liabilities')df_results["Total Shareholder Equity"] = df_results["Total Assets"] - df_results["Total Liabilities"]

And to calculate D/E, we need to write formula in Python syntax:

df_results["Debt_to_Equity"] = df_results['Total Liabilities'] / df_results["Total Shareholder Equity"]

The resultant data frame is outlined below. Note that on it’s own 2.1 might seem high (i.e. for every $1 of equity MSFT has, the debt is $2.1). Metric is more useful while comparing against other similar stocks.

Side Note: Since D/E relies on Liability and Total Shareholder Equity, we can use metrics calculated for ROE above. By and by this is an example of how automation and use of software engineering (and more specifically data science) give an edge compared to manual or Excel calculations.

Financial Metric 4: Quick Ratio

Formula: QR = Current Assets** / Liabilities**

Intuition: The Quick Ratio (also called acid test) measures company’s liquidity [1]. This is another measure that focuses on risk mitigation rather than opportunity as opposed to first three ratios above. In particular, Quick Ratio measures if company can meets it debts in short-term using assets that can be converted into cash quickly [2].

Interpretation: The Quick Ratio value indicates how much money company has to cover for its every $1 debt. If company has Quick Ratio < 1, it may not be able to meet its short term obligations. On the other hand > 1 indicates that company will be able to pay of its debt if necessary in short term [8] *

Example:

Stock A has Current Assets of $120 million and liabilities of $150 million. Quick Ratio is 0.8
Stock B has Current Assets of $40 million, and liabilities of $10 million. Quick Ratio is 4
Stock A has Quick Ratio < 1, which means it may not be able to pay its obligations, which is not the case for Stock B. Note, however, that Stock A has 3 times higher current assets than Stock B, but looking at assets alone doesn’t give entire picture.

** In the 10-K statement, we will use Total Assets and Total Liabilities, which is provided in the stock of our implementation choice from [7]

Implementation:

Filter out total assets and liabilities from 10-K

df_results['Total Assets'] = df_balance_T.filter(regex='Total Assets') df_results['Total Liabilities'] = df_balance_T.filter(regex='Total Liabilities')

Compute Quick Ratio by dividing total current assets by

df_results['Quick Ratio'] = df_results['Total Assets'] / df_results['Total Liabilities']

Display df_results variable and note the Quick Ratio as shown below:

Conclusion

These are only some metrics used while evaluating stock performance. Furthermore, we simplified (omitted) many details to focus on demonstrating how to use Python and Pandas to automate process of calculating these metrics. As next step, you can fetch live stock data using API and/or create dashboard to visualize your metrics.

It is important to note, even in passing, that no one metric is sufficient to paint the whole picture. For instance, rise in EPS doesn’t necessarily mean that company is generating more money. For example, company could be repurchasing their own stocks, which will have an impact on EPS even if earnings decreased [1]. This is just an instance of one metric, and remember that companies can use many accounting tricks to adjust the inputs used in the ratio calculations, which in turn, affects the ratios used.

Remember seeing * next to each Interpretation ? It was warning to note that those aren’t strictly true for reasons stated in the paragraph above.

Ultimately, it comes down to pieces of the puzzle (i.e. various financial factors) put together to present bigger picture from which you must draw your own interpretations. And remember… correlation doesn’t mean causation!