Skip to content

Help your hedge fund manager!

You have two datasets at your disposal: Balance_Sheet.xlsx and Income_Statement.xlsx. Both these datasets have three columns in common:

  • "Company": The company's ticker name.
  • "comp_type" The type of industry the company in question belongs to. It is either "tech" for companies in the technology industry, "fmcg" for companies in the fast-moving consumer goods industry, and "real_est" for companies in the real estate industry.
  • "Year": The year the company's information is from.

The rest of the columns in the datasets contain information from the financial statement of the "Company" in question. Note that the columns in Balance_Sheet.xlsx only contain financial information from the balance sheet. Similarly, the columns in Income_Statement.xlsx only contain financial information from the income statement. The columns are named accordingly. For instance, the column "Total Liab" from Balance_Sheet.xlsx is the total liability.

import numpy as np
import pandas as pd
import seaborn as sns

#loading the file and checking out the dataset
dfb = pd.read_excel("data/Balance_Sheet.xlsx")
dfb.describe()
dfb.info()
dfb.head()
#loading the other file
dfi = pd.read_excel("data/Income_Statement.xlsx")
dfi.head()
dfi.info()
# The data I require is located in both tables so I will merge them
df = pd.merge(dfb, dfi, on=["Year", "company", "comp_type"])

#Additional assessment of the dataset 
print(df["comp_type"].unique())

print(df["company"].unique())
1 hidden cell
# Computing the gross margin ratio
df_ratios["profitability_ratio"] = (df_ratios["Total Revenue"] - df_ratios["Cost Of Goods Sold"])/df_ratios["Total Revenue"]


# Computing the debt-to-equity ratio
df_ratios["leverage_ratio"] = df_ratios["Total Liab"]/df_ratios["Total Stockholder Equity"]


# Using a  pivot table to see the "comp_type" with the lowest average profitability ratio
print(df_ratios.pivot_table(index="comp_type", values="profitability_ratio"))
lowest_profitability = "fmcg"
# Using a pivot table to see the "comp_type" with the highest average leverage ratio
print(df_ratios.pivot_table(index="comp_type", values="leverage_ratio"))
highest_leverage = "real_est"
# Plotting the leverage ratio and profitability to see if real estate companies with higher leverage ratio have  higher profitability
df_real_est = df_ratios.loc[df_ratios["comp_type"]=="real_est"]
plot = sns.regplot(data=df_real_est, x="leverage_ratio", y="profitability_ratio")
relationship = "positive"