Python Stock Analysis — Balance Sheet Trend Analysis

I used to have plenty of Excel templates pre-build in Excel in order to analyse stocks of any company of my interest. My old process was simple but inefficient, I would select a few companies, go to the annual reports or in the best scenario to Yahoo Finance to get all required input for my Excel template. In the end, it was a very manual process and time consuming.

Manually collect financial data required a lot of effort that kept me from spending more time in the analytical part. That was one of the reasons for learning Python and Pandas and automate as much as possible all data collection process.

source: Pixabay, via Pexels

It took me a few months to get to a good level of Python and Pandas before I could fully automate all my stock analysis. However, it was totally worth it. Now, I can just write down the name of any companies that I want to analyse and in a few seconds, the results are shown just in front of my eyes.

Now, I would like to share with you all those analysis and hope that you can benefit from them. In the current post, I would like to show you how to perform a Balance Sheet Trend Analysis with Python.

Balance Sheet Trend Analysis will display each balance sheet item as a percentage of the total assets. It is a very useful tool to compare balance sheet item changes and analyse trends over time.

In addition to the Medium stories, I also have a video tutorial serie in Youtube (Python for Finance) where I explain line by line how to develop the code for few of the analysis. In case something is not clear with the code shared below, feel free to visit the video tutorial in Youtube:

Let’s move now to the code. Below script is very simple and would take us only a couple of minutes to write it. We will be using Pandas, Requests and Json libraries. And we will extract the data from an API called Financial Modelling Prep. It is a free API with great financial data.

We start by importing Pandas, request and JSON and making a get call to the Financial Modelling Prep API end point that will return balance sheet information of any company we passed with the get request. In this example, we will get data from Apple by passing the ticker of the company. Then, we will convert the data into a Json format:

import pandas as pd
import requests
import json
bs = requests.get(f'https://financialmodelingprep.com/api/v3/financials/balance-sheet-statement/AAPL?period=quarter')
bs = bs.json()

Next, we need to parse the dictionary that we have stored in the variable bs. The required data is contained in a dictionary with a key named financials. Then, we use Pandas DataFrame.from_dict method to convert our dictionary into a Pandas Dataframe. Since we want to have the Pandas DataFrame showing the data balance sheet items as row items, we will transpose it.

bs = bs[‘financials’]
bs = pd.DataFrame.from_dict(bs)
bs = bs.T
bs.columns = bs.iloc[0]

Python for Finance — Retrieving a Balance Sheet

Now, our Pandas Dataframe looks exactly as in the picture above. We still need to write a few more lines of code to clean it up and show each of the balance sheet items as a percentage of the Total assets.

Let’s do that with below code. First, I store all my columns in a variable called cols. Then, I remove the duplicated first row and keep only 4 columns and 12 rows (i.e. first four columns are the 4 most recent financial quarters and will get rid of the liabilities and equities which are after the 12 row).

Finally, we convert our columns from objects to floats in order to be able to perform mathematical operations. You can always refer to above Youtube video in case something is not.

cols = bs.columns
bs = bs.iloc[1:,]
bs = bs.iloc[:12,:4]
cols = bs.columns
bs[cols] = bs[cols].apply(pd.to_numeric, errors=’coerce’)

Our bs Pandas DataFrame is starting to look much better. We have all assets from Apple for the last 4 quarters:

To complete our script, we only need to divide each of balance sheet items in a quarter by the Total assets of that quarter. Let’s quickly do that.

First, we extract the Total assets number from each of the quarters using iloc[row,col]. Then, we create a list called allassets. Finally we divide each of the balance sheet items by the Total assets and multiply by 100 in order to show it as a percentage:

assetsQ1 = bs.iloc[11,0]
assetsQ2 = bs.iloc[11,1]
assetsQ3 = bs.iloc[11,2]
assetsQ4 = bs.iloc[11,3]
allassets = [assetsQ1,assetsQ2,assetsQ3,assetsQ4]
bs[cols] = (bs[cols] / allassets)*100
pd.options.display.float_format = ‘{:.2f}%’.format

And finally, we have our analysis fully automated. Now, we can quickly see, by looking into the Balance Sheet trend analysis, that Apple had been building cash for the last year. Receivables had almost doubled from June to September and Long Term Investments had decreased. You can extract your own conclusions based on this trends.

Balance Sheet Trend Analysis with Python

Note that we can reuse the analysis for any other company by simply replacing the url ticker from AAPL to any other company in the url passed to the get request at the beginning of our code.