Overview๐
A Simple, stream-lined web app to use the financial statements of different companies during the year 2021-2022 to find out if the company's earnings have been manipulated.
Objectives๐ฏ
This Web App is based on the Beneish model, which is a math model that uses eight variables and financial ratios to figure out if a company has manipulated its earnings. An M-Score is made based on a company's financial statements to show how much earnings have been changed.
Use Case๐ฅ
Students at Cornell University were able to spot manipulation by the big Enron Corporation years before Wall Street could figure out how bad it was. This was a common use of the model. People lost their jobs, money, and reputations in what is thought to be one of the biggest frauds in history. One of the biggest auditing and accounting firms in the world, Arthur Andersen LLP, also lost its reputation.
About the Beneish Model๐
Creator - Professor Messod Beneish
Purpose - to uncover financial manipulation by identifying the red flag areas
Beneish M-score Equation
M-score = โ4.84 + 0.92 ร DSRI + 0.528 ร GMI + 0.404 ร AQI + 0.892 ร SGI + 0.115 ร DEPI โ0.172 ร SGAI + 4.679 ร TATA โ 0.327 ร LVGI
| M - Score | Inference |
| < - 2.22 | This company doesn't manipulate. |
| > -2.22 | This company manipulates. |
Below, you can find out more about the Beneish Model ๐
Limitationsโ
TOOLS USED๐ฒ
๐ Programming Language - Python๐
๐๐ฎ๐๐ฎ ๐ฆ๐ผ๐๐ฟ๐ฐ๐ฒ - yfinance (API), yahooquery ๐
๐๐ฎ๐๐ฎ ๐ฎ๐ป๐ฎ๐น๐๐๐ถ๐ - Pandas ๐ผ
๐๐๐๐ผ๐บ๐ฎ๐๐ถ๐ผ๐ป - openpyxl (Python ->Excel) ๐
๐ช๐ฒ๐ฏ ๐๐ฝ๐ฝ - Streamlit (Framework) ๐ฏ
ย ย ย ๐๐ฐ๐๐๐๐ผ๐บ ๐ฐ๐ผ๐บ๐ฝ๐ผ๐ป๐ฒ๐ป๐๐ - hydralit_components โจ
๐๐ฎ๐๐ฎ ๐ช๐ฎ๐ฟ๐ฒ๐ต๐ผ๐๐๐ถ๐ป๐ด - Snowflake โ๏ธ
๐ฉ๐ถ๐๐๐ฎ๐น๐ถ๐๐ฎ๐๐ถ๐ผ๐ป - Plotly ๐
๐๐ป๐ถ๐บ๐ฎ๐๐ถ๐ผ๐ป๐ - Lottie Files ๐
Data Fetching๐
from yahooquery import Ticker
# Fetching the Tickers Module
symbols = ['FB', 'AAPL', 'BRK.B', 'TSLA', 'MCD', 'VZ', 'BA', 'NKE', '^GSPC', 'NQ=F', 'ALB', 'AOS', 'APPS', 'AQB', 'ASPN', 'ATHM', 'AZRE', 'BCYC', 'BGNE', 'CAT', 'CC', 'CLAR', 'CLCT', 'CMBM', 'CMT', 'CRDF', 'CYD', 'DE', 'DKNG', 'EMN', 'FBIO', 'FBRX', 'FCX', 'FLXS', 'FMC', 'FMCI', 'GME','GRVY', 'HAIN', 'HBM', 'HIBB', 'IEX', 'IOR', 'GOOGL', 'MAXR', 'MPX', 'MRTX', 'NSTG', 'NVCR', 'NVO', 'OESX', 'PENN', 'PLL', 'PRTK', 'RDY', 'REGI', 'REKR', 'SBE', 'SQM', 'TCON', 'TWTR', 'TGB', 'TRIL', 'UEC', 'VCEL', 'VOXX', 'WIT', 'WKHS', 'XNCR']
# Create Ticker instance, passing symbols as first argument
# Optional asynchronous argument allows for asynchronous requests
tickers = Ticker(symbols, asynchronous=True)
dat = tickers.get_modules("summaryProfile quoteType")
symb = pd.DataFrame.from_dict(dat).T
# flatten dicts within each column, creating new dataframes
dataframes = [pd.json_normalize([x for x in symb[module] if isinstance(
x, dict)]) for module in ['summaryProfile', 'quoteType']]
# concat dataframes from previous step
symb = pd.concat(dataframes, axis=1)
symb = symb[['shortName', 'symbol']].dropna()
symb = symb.sort_values('symbol')
symb.set_index('shortName', inplace=True, drop=True)
symb = symb.reset_index() # reset index
symb.index = symb.index + 1 # add 1 to each index
symb.columns = ['Companies', 'Symbol']
data = symb.copy()
symb['Companies'] = symb['Companies'].str.replace("'", "''")
- The symbols for each company are put in a list, and then the company names are retrieved with yahooquery's Ticker module and put in a pandas dataframe.
- The single quotes in the company names make it hard to store them in the database, so the single quotes are replaced with double quotes.
Data Analysis ๐
import yfinance as yf
comp = yf.Ticker(symb.at[ch, 'Symbol'])
incomeStatement = comp.financials
balanceSheet = comp.balancesheet
cashFlow = comp.cashflow
The Financial Statements are taken from Yahoo Finance based on the company's symbols (i.e., Tickers)
# Cleaning the data
# Income Statement
incomeStatement = incomeStatement[incomeStatement.columns[0:2]]
incomeStatement.columns = ['2022', '2021']
incomeStatement = incomeStatement.fillna(0).astype(float)
# Balance Sheet
balanceSheet = balanceSheet[balanceSheet.columns[0:2]]
balanceSheet.columns = ['2022', '2021']
balanceSheet = balanceSheet.fillna(0).astype(float)
# Cash Flow
cashFlow = cashFlow[cashFlow.columns[0:2]]
cashFlow.columns = ['2022', '2021']
cashFlow.dropna()
Details for each statement for 2021 and 2022 are obtained, and null values are removed. Also, relevant details must be retrieved and kept in a distinct dataframe to calculate the m-score. I'm not showing the code because it makes the blog longer; you may refer to the source code linked at the end.
Data Evaluation ๐งฎ
functions .py
def DSRI(df):
return (df.at["Accounts Receivables", "2022"] / df.at["Revenue", "2022"]) / (df.at["Accounts Receivables", "2021"] / df.at["Revenue", "2021"])
def GMI(df):
return ((df.at["Revenue", "2021"] - df.at["Cost of Goods Sold", "2021"])/df.at["Revenue", "2021"]) / ((df.at["Revenue", "2022"] - df.at["Cost of Goods Sold", "2022"])/df.at["Revenue", "2022"])
def AQI(df):
AQI_t1 = (1 - (df.at["Current Assets", "2022"] +
df.at["Property, Plant & Equipment", "2022"]+df.at["Securities", "2022"])) / df.at["Total Assets", "2022"]
AQI_t2 = (1 - (df.at["Current Assets", "2021"] +
df.at["Property, Plant & Equipment", "2021"]+df.at["Securities", "2021"])) / df.at["Total Assets", "2021"]
return AQI_t1 / AQI_t2
def SGI(df):
return (df.at["Revenue", "2022"] / df.at["Revenue", "2021"])
def DEPI(df):
DEPI_t1 = (df.at["Depreciation", "2021"] / (df.at["Depreciation",
"2021"] + df.at["Property, Plant & Equipment", "2021"]))
DEPI_t2 = (df.at["Depreciation", "2022"] / (df.at["Depreciation",
"2022"] + df.at["Property, Plant & Equipment", "2022"]))
return DEPI_t1 / DEPI_t2
def SGAI(df):
return (df.at["Selling, General & Admin.Expense", "2022"] / df.at["Revenue", "2022"]) / (df.at["Selling, General & Admin.Expense", "2021"] / df.at["Revenue", "2021"])
def LVGI(df):
return ((df.at["Current Liabilities", "2022"] + df.at["Total Long-term Debt", "2022"]) / df.at["Total Assets", "2022"]) / ((df.at["Current Liabilities", "2021"] + df.at["Total Long-term Debt", "2021"]) / df.at["Total Assets", "2021"])
def TATA(df):
return (df.at["Net Income from Continuing Operations", "2022"] - df.at["Cash Flow from Operations", "2022"]) / df.at["Total Assets", "2022"]
def BeneishMScore(dsri, gmi, aqi, sgi, depi, sgai, lvgi, tata):
return -4.84+0.92*dsri+0.528*gmi+0.404*aqi+0.892*sgi+0.115*depi-0.172*sgai+4.679*tata-0.327*lvgi
# Beneish M Score
m_score = BeneishMScore(DSRI(data),
GMI(data),
AQI(data),
SGI(data),
DEPI(data),
SGAI(data),
LVGI(data),
TATA(data))
if(m_score < -2.22):
res = '##### Company is not likely to manipulate their earnings'
st.write(f"##### M- Score = {round(m_score,2)}")
st.write(f"{res}")
# print(res)
else:
res = " ##### Company is not likely to manipulate their earnings"
st.write(f"##### M- Score = {round(m_score,2)}")
st.write(f"{res}"
The above functions are used to calculate the m-score and are interpreted.
Visualization ๐ด
import plotly.express as px
# The Line Chart using Plotly
fig = px.line(
temp_ratios, # Data Frame
x="Index", # Columns from the data frame
y="Ratios",
title="Financial Ratio Indexes",
)
fig.update_traces(line_color="blue")
with st.container():
st.plotly_chart(fig)
Calling st.plotly chart() is all you need to do to get plotly charts in streamlit, and you can make different kinds of charts, like line charts and bar charts.
Data Warehousing ๐ฝ
Requirement - Snowflake Account Connect Streamlit to Snowflake - Streamlit Docs
Why use a database?
My ultimate goal is to remember the information so I can compare its results to those of the future. That is, the calculated m-score value for each company can be stored in a database table and compared with the future m-score values of the same companies for the coming years.
import snowflake.connector as sf
# SnowFlake Initialize connection.
def init_connection():
return sf.connect(**st.secrets["snowflake"])
conn = init_connection()
cur = conn.cursor()
Initialize connection using this block of code
try:
cur.execute(
f"INSERT INTO FAR.PUBLIC.HISTORY(COMPANY,M_SCORE) VALUES('{symb.at[ch, 'Companies']}',{round(m_score,2)})")
cur.execute('''DELETE FROM FAR.PUBLIC.HISTORY WHERE (COMPANY) in
(SELECT COMPANY FROM FAR.PUBLIC.HISTORY GROUP BY COMPANY HAVING COUNT(COMPANY)> 1)
''')
cur.execute(
'SELECT * FROM FAR.PUBLIC.HISTORY')
finally:
cur.close()
conn.close()
- Once the user makes an entry for the first time, the Company name and its m-score are saved.
- When a user makes an entry for the same company, duplicate entries are deleted and updated in the database again. This is done to get rid of redundant data and make the best use of space.
โ Challenges
The first significant problem for me is to find a data source that gives me the company names for their symbols (e.g., AAPL - Apple Inc.), because it's tough for users to search for specific firms using symbols.
โ So I used a yahooquery module.
The next I ran into was writing manual formulas to figure out the details that some companies leave out, like COGS and long-term debts.
- The last challenge is to add information to the database without duplicating it. I also had trouble adding the company name to the database table.
For eg., Dr. Reddy's Laboratory Ltd. can't be stored directly since it has single quotes. Since it's a string, Snowflake will display it as "Dr. Reddy's Laboratory Ltd." Change Snowflake's single quote mark to a double quote "Dr. Reddy' 's Laboratory Ltd." so that the data can be added to the database
That's pretty much it for the implementation, and my favorite part is now giving my code a GUI look with Streamlit and automation using openpyxl (works only on local system). Let's start with Automation. Using Python's openpyxl module, you can easily manipulate the excel sheets.
Automation๐ค
from openpyxl import Workbook, load_workbook
# Excel Automation
wb = load_workbook('Beneish-M-Score.xlsx')
ws = wb.active
while True: # repeat until the try statement succeeds
try:
# or "a+", whatever you need
myfile = open("Beneish-M-Score.xlsx", "r+")
break
# exit the loop
except IOError:
print("Failed to open!!")
ws['C3'].value = symb.at[ch, 'Symbol']
ws['C4'].value = symb.at[ch, 'Companies']
excel_data = data.reset_index(drop=True)
excel_data.index = excel_data.index + 1
for row in range(8, 21):
for col in range(3, 5):
char = chr(65 + col)
if char == 'D':
ws[char + str(row)] = excel_data.at[row-7, "2022"]
if char == 'E':
ws[char + str(row)] = excel_data.at[row-7, "2021"]
wb.save('Beneish-M-Score.xlsx')
- On Beforehand, a template is made for calculating the beneish workbook, and the workbook is loaded.
- The process is so easy that all the user has to do is make an entry for a certain company. Using automation, the excel sheet is used to update the data in the appropriate cells.
Streamlitโก
The fun and exciting portion of Streamlit is now here. Whence did I get that idea?
I purposely put my discussion of Streamlit until last because there is still so much to say about it. You may be wondering, "What is Streamlit?" before we get into the details. So, there you have it.
Streamlit is a Python-based open source application framework. Our data science and machine learning web apps are much faster to develop thanks to it. Scikit-Learn, Keras, PyTorch, SymPy(latex), NumPy, pandas, and Matplotlib are just a few of the popular Python libraries it works with.
It's now up to Streamlit to shape my code with its components into a WEB APP ๐ธ๏ธ.
import streamlit as st
from streamlit_lottie import st_lottie
import hydralit_components as hc
# Streamlit Page Configuration
st.set_page_config(
page_title="FA",
page_icon="chart_with_upwards_trend",
layout="wide",
initial_sidebar_state="expanded"
)
hide_st_style = """
<style>
#MainMenu {visibility: hidden;}
header{visibility: hidden;}
footer {visibility: hidden;}
</style>
"""
st.markdown(hide_st_style, unsafe_allow_html=True)
# ---Side-Bar----
with st.sidebar:
st_lottie(lottie_hello, loop=True, key=None, height=320, width=320)
st.write(''' It's a DEMO!! ''')
With this code, I can set up my web app's basic settings and wireframe.
def load_lottiefile(filepath: str):
with open(filepath, "r") as f:
return json.load(f)
lottie_analysis = load_lottiefile("lottiefiles/analysis.json")
lottie_hello = load_lottiefile("lottiefiles/hello.json")
Streamlit's custom component can also load Lottie animations, which can be used to show off some amazing illustrations
# -----Home Page-----
st.title("Analyzing the Quality of Financial Statements using Beneish Model")
with st.container():
left_col, right_col = st.columns((2, 1))
with left_col:
st.dataframe(data)
with right_col:
st_lottie(lottie_analysis, height="300",
width="500", quality="high", key=None)
# -- Input----
ch = st.number_input('Enter a number: ')
Inside the st.container() , two separate columns are made One side shows information about the company, while the other shows animations. A user input can also be made with st.number input()
with hc.HyLoader('Now doing loading', hc.Loaders.standard_loaders, index=[3, 0, 5]):
Hydralit components can be used to show loading animations to hide the time it takes to compile the code at run time.
# The Line Chart using Plotly
fig = px.line(
temp_ratios, # Data Frame
x="Index", # Columns from the data frame
y="Ratios",
title="Financial Ratio Indexes",
)
fig.update_traces(line_color="blue")
with st.container():
st.plotly_chart(fig)
# Streamlit Buttons
history = cur.fetch_pandas_all()
if st.button("View History"):
st.snow()
st.dataframe(history)
As was already said, charts can be made in streamlit, status elements can be made with st.snow() for eye-catching visuals, and buttons can also be made with st.button().
Sigh! ๐ฎโ๐จ
What a drag ๐ฆ! I thought blogs were supposed to be short! The end of our blog is near, and we'll wrap things up with this post. Thank you for taking the time to read and benefit from my writing. More interesting projects are in the works, and I'll keep you all posted. For now, keep growing ๐ and improving by 1% each day.
Check out the Beneish Pass website.
Source Code
https://github.com/VidhyaVarshanyJS/FAStreamlit
Peace :)๐๏ธ
Follow me for more on:๐ LinkedIn Twitter Github