Python Stock Analysis – Income Statement Waterfall chart

In this new post on Python Stock Analysis , I would like to show you how to display an income statement in the form of a Waterfall chart using Python, Pandas and Plotly.

Waterfall chart is a way to represent data in order to visualize cumulative effects of different items. In our case, we will be able to visualize the effect of each Income Statement line from Revenue to Net Income

Photo by Pixabay from Pexels

I will structure the article in two parts. In the first part we will retrieve, from a free financial API Financialmodelingprep, income statement data from any company that we are interested in. Then, we will use Pandas to clean up the data and perform some basic operations.

In the second part of the post, we will use Plotly to transform the income statement into a nice Waterfall chart representation.

If you follow this article and code along with me, you will be able to build below Python Waterfall chart for your favourite companies.

Income Statement Waterfall Chart Python

Building a Waterfall Chart with Python

Ok, let’s start coding the first part. We need to import json and requests libraries in order to make a GET request to the API end point. We also import Pandas to transform the requested data into a Pandas DataFrame. Finally, we will import Ploty which is the library we will use to create the chart.

For all of you who are not familiar with Plotly, it is a free graphing library for Python which offers the possibility to create very attractive and interactive charts.

import plotly.express as px
import plotly.graph_objects as go
import pandas as pd
import requests
import json

Having imported the required packages, we start creating our function selectquote where we will add the logic to transform our data into a Pandas DataFrame. We make an http request and transform the data into Json so that we can easily handle it with Python and Pandas.

Then, from the information retrieved through the API, we store the values of the key financials into the stock variable. Financials contains a list of dictionaries where each dictionary represent the income statement from one quarter.

Next, we create a Pandas Dataframe from the stock dictionary and transpose it in order to have the income statement items as rows and the dates as headers.

def selectquote(quote):
     r= 
requests.get(f"https://financialmodelingprep.com/api/v3/financials/income-statement/{quote}?period=quarter")
     r = r.json()
     stock = r['financials']
     stock = pd.DataFrame.from_dict(stock)
     stock = stock.T
     stock.columns = stock.iloc[0]
     stock.reset_index(inplace=True)

    return stock

selectquote('AAPL')

So far, if we print out our stock Pandas DataFrame, we would get something similar to below for Apple since we have passed ‘AAPL‘ to our selectquote function. Please note that we need to pass as an argument the ticker of the stock and not the full name.

There is a few more things to clean up from our stock Pandas DataFrame before we can create our chart.

First, we slice our Pandas DataFrame to only keep the income statement from the last available quarter. Then, we rename our column names to have the name of the stock instead of the date. Finally, we transform our Income Statement information (i.e. column) into a number.

stock = stock.iloc[:,0:2]
stock.rename(columns={ stock.columns[1]: quote }, inplace = True)
cols = stock.columns.drop('index')
stock[cols] = stock[cols].apply(pd.to_numeric, errors='coerce')
stock = stock.iloc[1:,]

After re-running our code, we can see that now we have the latest quarter Income Statement for Apple:

incomeStatement = selectquote('AAPL')

print(incomeStatement)

Python for Finance – Income Statement

Having the Income Statement ready, we need to extract the value of Revenue, Cost of Goods Sold, etc. from our stock Pandas DataFrame in order to use it in the Waterfall chart. It can easily be done with iloc. Iloc will let us extract each value from the income statement DataFrame. We multiply expenses by -1 to have them with negative sign convention:

Revenue = incomeStatement[incomeStatement['index'] == 'Revenue'].iloc[0][1]

COGS = incomeStatement[incomeStatement['index'] == 'Cost of Revenue'].iloc[0][1]*-1

grossProfit = incomeStatement[incomeStatement['index'] == 'Gross Profit'].iloc[0][1]

RD = incomeStatement[incomeStatement['index'] == 'R&D Expenses'].iloc[0][1]*-1

GA = incomeStatement[incomeStatement['index'] == 'SG&A Expense'].iloc[0][1]*-1

operatingExpenses = incomeStatement[incomeStatement['index'] == 'Operating Expenses'].iloc[0][1]*-1

interest = incomeStatement[incomeStatement['index'] == 'Interest Expense'].iloc[0][1]*-1

EBT = incomeStatement[incomeStatement['index'] == 'Earnings before Tax'].iloc[0][1]

incTax = incomeStatement[incomeStatement['index'] == 'Income Tax Expense'].iloc[0][1]*-1

netIncome = incomeStatement[incomeStatement['index'] == 'Net Income'].iloc[0][1]

Transforming our data into a Waterfall chart

Great, we have completed the first part of our code. Now, let’s move to the fun part where we will create a Waterfall chart. First of all, we will create a fig object which will contain the required data points to build the chart.

For the Waterfall chart, we need a list of measures to indicate if each of the variables is a relative measure or a total measure. This will impact how the chart is build. For example, Gross Profit will be a total measure and it will start from the bottom of the chart.

Then, we will have our x axis which will contain the name of the income statement items. Similarly, our y will contain the values associated to each of the x. In addition, we will have a list call text. This list will include the label to show in the face of the graph for each of our items.

We divide our text by 100,000 to represent the numbers in millions.

fig = go.Figure(go.Waterfall(
    name = "20", orientation = "v",

    measure = ["relative", "relative", "total", "relative", "relative", "total","relative","total","relative","total"],

    x = ["Revenue", "COGS", "Gross Profit", "RD", "G&A", "Operating Expenses","Interest Expense", "Earn Before Tax","Income Tax","Net Income"],

    textposition = "outside",

    text = [Revenue/100000, COGS/100000, grossProfit/100000, RD/100000, GA/1000000, operatingExpenses/1000000,interest/100000, EBT/100000,incTax/100000, netIncome/100000],

    y = [Revenue, COGS, grossProfit, RD, GA, operatingExpenses, interest,EBT, incTax, netIncome],

    connector = {"line":{"color":"rgb(63, 63, 63)"}},
))

fig.update_layout(
        title = "Profit and loss statement",
        showlegend = True
)

fig.show()

Now, if we run the code, we can see the Income Statement from Apple as a Waterfall chart:

Python Stock Analysis – Income Statement Waterfall Chart

Try by your own with different companies. Simply pass the ticker of the desired stock as the argument in the selectquote function.

See below the full code for your reference. Please note that you need to have all libraries used in the tutorial installed. Otherwise, the code will not work.

import plotly.express as px
import plotly.graph_objects as go
import pandas as pd
import requests
import json

def selectquote(quote):
     r= requests.get(f"https://financialmodelingprep.com/api/v3/financials/income-statement/{quote}?period=quarter")
     r = r.json()
    stock = r['financials']
    stock = pd.DataFrame.from_dict(stock)
    stock = stock.T
    stock.columns = stock.iloc[0]
    stock.reset_index(inplace=True)
    stock = stock.iloc[:,0:2]
    stock.rename(columns={ stock.columns[1]: quote }, inplace = True)
    cols = stock.columns.drop('index')
    stock[cols] = stock[cols].apply(pd.to_numeric, errors='coerce')
    stock = stock.iloc[1:,]
    return stock

incomeStatement = selectquote('AAPL')
Revenue = incomeStatement[incomeStatement['index'] == 'Revenue'].iloc[0][1]
COGS = incomeStatement[incomeStatement['index'] == 'Cost of Revenue'].iloc[0][1]*-1
grossProfit = incomeStatement[incomeStatement['index'] == 'Gross Profit'].iloc[0][1] 
RD = incomeStatement[incomeStatement['index'] == 'R&D Expenses'].iloc[0][1]-1 
GA = incomeStatement[incomeStatement['index'] == 'SG&A Expense'].iloc[0][1]-1
operatingExpenses = incomeStatement[incomeStatement['index'] == 'Operating Expenses'].iloc[0][1]-1 
interest = incomeStatement[incomeStatement['index'] == 'Interest Expense'].iloc[0][1]-1
EBT = incomeStatement[incomeStatement['index'] == 'Earnings before Tax'].iloc[0][1]
incTax = incomeStatement[incomeStatement['index'] == 'Income Tax Expense'].iloc[0][1]*-1
netIncome = incomeStatement[incomeStatement['index'] == 'Net Income'].iloc[0][1] 

fig = go.Figure(go.Waterfall(
    name = "20", orientation = "v",
    measure = ["relative", "relative", "total", "relative", "relative", "total","relative","total","relative","total"],

    x = ["Revenue", "COGS", "Gross Profit", "RD", "G&A", "Operating Expenses","Interest Expense", "Earn Before Tax","Income Tax","Net Income"],
    textposition = "outside",

    text = [Revenue/100000, COGS/100000, grossProfit/100000, RD/100000, GA/1000000, operatingExpenses/1000000,interest/100000, EBT/100000,incTax/100000, netIncome/100000],

     y = [Revenue, COGS, grossProfit, RD, GA, operatingExpenses, interest,EBT,incTax,netIncome],

    connector = {"line":{"color":"rgb(63, 63, 63)"}},
     
fig.update_layout(
         title = "Profit and loss statement",
         showlegend = True
 )))

fig.show()

I have created below Youtube video where I build the Waterfall chart step by step. It is worth to check in case you have troubles understanding the code in this article.

Follow my other entries on Python for Finance in my website https://codingandfun.com/.