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
# 1a Reading in the data
balance = pd.read_excel("data/Balance_Sheet.xlsx", index_col=0)
balance.head()
# 1b Reading in the data
income = pd.read_excel("data/Income_Statement.xlsx", index_col=0)
income.head()
balance.columns = [col.replace(" ", "_").lower() for col in balance.columns]
for col in balance.columns:
    globals()[col]=col
    
income.columns = [col.replace(" ", "_").lower() for col in income.columns]
for col in income.columns:
    globals()[col]=col
# 2 Merging DataFrames
df = pd.merge(balance, income, on=[year, comp_type, company])
df.head()
# # Compute gross margin ratio
# df_ratios["profitability_ratio"] = (df_ratios["Total Revenue"] - df_ratios["Cost Of Goods Sold"])/df_ratios["Total Revenue"]

# # Compute operating margin ratio, but commenting it out
# # df_ratios["profitability_ratio"] = (df_ratios["Total Revenue"] - df_ratios["Total Operating Expenses"])/df_ratios["Total Revenue"]

# # You only need to compute one leverage ratio, but we are providing the code to compute both the debt-to-equity ratio and the equity multiplier ratio 

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

# # Compute equity multiplier ratio, but commenting it out
# # df_ratios["leverage_ratio"] = df_ratios["Total Assets"]/df_ratios["Total Stockholder Equity"]
# 3 Computing Ratios
leverage_ratio, profitability_ratio = "leverage_ratio", "profitability_ratio"
# debt-to-equity ratio
df[leverage_ratio] = df[total_liab]/df[total_stockholder_equity]
# equity multiplier ratio (alternate)
# df[leverage_ratio] = df[total_assets]/df[total_stockholder_equity]

# operating margin ratio
df[profitability_ratio] = (df[total_revenue]-df[total_operating_expenses])/df[total_revenue]
# gross margin ratio (alternate)
# df[profitability_ratio] = (df[total_revenue]-df[cost_of_goods_sold])/df[total_revenue]

df_ratios = df[[year, comp_type, company, leverage_ratio, profitability_ratio]]
df_ratios.head()
import timeit
pivot_times = []
group_times = []
pr_pivot = df.pivot_table(index=comp_type, values=profitability_ratio)
lowest_profitability = pr_pivot[profitability_ratio].idxmin()
lowest_profitability
stmt = """
pr_pivot = df.pivot_table(index=comp_type, values=profitability_ratio)
lowest_profitability = pr_pivot[profitability_ratio].idxmin()
"""

# Setup string to import necessary variables from the global scope
setup = "from __main__ import df, comp_type, profitability_ratio"

# Time the execution
pivot_time = timeit.timeit(stmt=stmt, setup=setup, number=1)
pivot_times.append(pivot_time)
pivot_time
pr_group = df.groupby(comp_type)[profitability_ratio].mean().to_frame()
lowest_profitability = pr_group[profitability_ratio].idxmin()
lowest_profitability
import timeit

stmt = """
pr_group = df.groupby(comp_type)[profitability_ratio].mean().to_frame()
lowest_profitability = pr_group[profitability_ratio].idxmin()
"""

# Setup string to import necessary variables from the global scope
setup = "from __main__ import df, comp_type, profitability_ratio"

# Time the execution
group_time = timeit.timeit(stmt=stmt, setup=setup, number=1)
group_times.append(group_time)
group_time
lr_pivot = df.pivot_table(index=comp_type, values=leverage_ratio)
highest_leverage = lr_pivot[leverage_ratio].idxmax()
highest_leverage
stmt = """
lr_pivot = df.pivot_table(index=comp_type, values=leverage_ratio)
highest_leverage = lr_pivot[leverage_ratio].idxmax()
"""

# Setup string to import necessary variables from the global scope
setup = "from __main__ import df, comp_type, leverage_ratio"

# Time the execution
pivot_time = timeit.timeit(stmt=stmt, setup=setup, number=1)
pivot_times.append(pivot_time)
pivot_time
‌
‌
‌