Computing financial ratios using pandas

Analyzing Financial Statements in Python

Rohan Chatterjee

Risk Modeler

Structure of balance sheet data

Balance sheet data loaded in pandas DataFrame called balance_sheet.

print(balance_sheet.head()) A picture showing the top of the DataFrame called balance_sheet.

Computing current ratio

balance_sheet["current_ratio"] = balance_sheet["Total Current Assets"] / 
                                 balance_sheet["Total Current Liabilities"]
print(balance_sheet.head())

A picture showing the DataFrame balance_sheet with the column current_ratio appended to it.

Using .groupby() to get results by group

To get the average current ratio by industry:

balance_sheet.groupby("comp_type")["current_ratio"].mean()

A picture showing the average current ratio of different industries.

Using .groupby() to get results by group

balance_sheet.groupby(["Year","comp_type"])["current_ratio"].mean()

A picture showing the average current ratio per industry over different years.

Using groupby().transform()

.transform() can be used after .groupby() to append the groupby result to rows according to the group each row belongs to.

balance_sheet["industry_curr_ratio"] = 
          balance_sheet.groupby([
          "Year","comp_type"])["current_ratio"].transform("mean")
print(balance_sheet.head())

A picture showing the average current ratio per industry over different years, now appended to the DataFrame balance_sheet.

Using .groupby().transform()

balance_sheet["relative_diff"] = 
                            (balance_sheet["current_ratio"] / 
                             balance_sheet["industry_curr_ratio"]) - 1

A DataFrame showing the relative difference between a company's current ratio and it's industry's current ratio.

Using .isin()

.isin() used to subset data for analysis.
Example: subset a DataFrame to show fmcg and tech companies in the year 2019 and 2020:

fmcg_2019 = balance_sheet.loc[
            (balance_sheet["Year"].isin([2019,2020])) &
            (balance_sheet["comp_type"].isin(["tech","fmcg"]))
                            ]

Let's practice!

Analyzing Financial Statements in Python