This is an INDIVIDUAL workshop. In this workshop we review the logistic regression model applied to fundamental analysis in Finance. In addition, we practice data management programming skills using a big panel-dataset of historical financial statement variables.
0.1 General Directions for each workshop
You have to work on Google Colab for all your workshops. In Google Colab, you MUST LOGIN with your @tec.mx account and then create a google colab document for each workshop.
You must share each Colab document (workshop) with the following account:
cdorante@tec.mx
You must give Edit privileges to these accounts.
In Google Colab you can work with Python or R notebooks. The default is Python notebooks.
Your Notebook will have a default name like “Untitled2.ipynb”. Click on this name and change it to “W1-Programming-YourFirstName-YourLastname”.
In your Workshop Notebook you have to:
You have to do all the challenges to get full credit for the workshop. The accuracy of the challenge will not significantly affect your grade; completion will have more weight for your workshop grade.
It is STRONGLY RECOMMENDED that you write your OWN NOTES as if this were your personal notebook to study for the FINAL EXAM. Your own workshop/notebook will be very helpful for your further study.
Once you finish your workshop, make sure that you RUN ALL CHUNKS. You can run each code chunk by clicking on the “Run” button located in the top-left section of each chunk. You can also run all the chunks in one-shot with Ctrl-F9. You have to submit to Canvas the web link of your Google Colab workshop.
1 Introduction
In this workshop we practice data management with financial historical data, and review the logit regression model and its application to fundamental analysis.
We will work with a real dataset of all historical financial variables of ALL US public firms that belong to the NYSE and the NASDAQ exchanges.
We will use a logit model to examine whether some financial ratios are related to the probability that future stock return (1 year later) is higher than the median return of the corresponding industry (1 year later).
We will learn basic programming skills for the required data management and data modeling.
You have to work with 2 datasets:
firmsus2024.csv: List of all US public firms with general information of each firm
dataus2024.csv : Panel data with historical financial quarterly data for all US public firms.
You have to download these 2 files from a web page and save in the directory where you have your workshop.
The first dataset (dataus2024) contains the historical financial data of the firms, while the second dataset (firmsus2024) is a catalog of all firms along with the corresponding industry type and status (active or cancelled).
The dataus2024 dataset has a panel-data (also called long format) structure. Each row has financial information for one US firm and 1 period (a quarter). All $ amounts are in thousands (’1000s). Here is a data dictionary of the columns:
Data dictionary of historical quarterly financial data.
Variable
Description
firm
Unique code of the company (also called ticker)
q
Quarter date
fiscalmonth
Month of the year when the firm closes a fiscal year
revenue
Total sales of the firm from the first fiscal quarter to the current quarter
cogs
Cost of good sold - variable costs of the products sold - from the first fiscal quarter to the current quarter
sgae
Sales and general administrative expenses - from the first fiscal quarter to the current quarter
otherincome
Other operational income/expenses that are not directly from the core operations of the firm - from the first fiscal quarter to the current quarter
extraordinaryitems
Extra income/expenses not related to regular operations - from the first fiscal quarter to the current quarter
finexp
Financial expenses - interest expenses paid (generated from loans) - from the first fiscal quarter to the current quarter
incometax
Income tax from the first fiscal quarter to the current quarter
totalassets
Total assets of the firm at the end of the quarter
currentassets
Current assets of the firm at the end of the quarter
totalliabilities
Total liabilities of the firm at the end of the quarter
currentliabilities
Current liabilities of the firm at the end of the quarter
longdebt
Balance of long-term financial debt (loans to pay longer than 1 year)
adjprice
Stock adjusted price at the end of the quarter; adjusted for stock splits and dividend payments; used to calculate stock returns
originalprice
Historical stock price (not adjusted); used to calculate historical market value
sharesoutstanding
Historical number of shares available in the market
fixedassets
Fixed assets value at the end of the quarter
cashinbanks
Cash balance at the end of the quarter
yearf
Fiscal year - this depends on when the firm ends its fiscal year; if fiscalmonth=12 in the quarter 3, then the fiscal year will start in Q4 of a year and ends in the Q3 of the following year
Each row of this dataset has quarterly financial data of one firm in one quarter. All firms have quarters from Q1 2000 to Q2 2024. Not all firms have existed since 2000, so if the first quarters are empty that means that the firm did not exist in the US financial market in those quarters. Then, it is possible to know when each firm went public to issue shares in the financial market: the first quarter with some non-empty data.
Each firm has defined the month of the year used to close a fiscal year. For example, Apple closes the fiscal year at the end of Quarter 3 (end of September) of any year. Then, for Apple, in the Q3 of 2022, there will be a 12 for the fiscalmonth variable. In this case, Apple starts its fiscal year in the Q4 of each year and ends in the Q3 of the following year. Most of the firms (about 80%) close fiscal year in December, so these firms will have a 12 in the Q4 of each year.
The variables related to sales and expenses are cumulative for each fiscal year. For example, Apple sold about $117 billion in the last calendar quarter (Q4) of 2022, but this is the first fiscal quarter for Apple. For Q1 (calendar) 2023 (which is the 2nd fiscal quarter), Apple has about $212 billion in the revenue variable, meaning that considering fiscal quarter 1 and 2, Apple has sold $212 billion. For Q2 2023 Apple has about $293 billion, meaning that the cumulative revenue of fiscal Q1, Q2 and Q3 is about $293 billion. Then, if you select rows with fiscalmonth=12, then you will be selecting those quarters with annual financial information for each firm!
Earnings before interest and Taxes (ebit) and Net Income (netincome) must be calculated as:
The firmsus2023.csv is a catalog of all active and cancelled US firms:
Variable
Description
firm
Unique code of the company (also called ticker)
name
Name of the firm
status
Status of the firm: active or cancelled
partind
Percent participation in the S&P500 market index
naics1
North American Industry Classification Code - Level 1
naics2
North American Industry Classification Code - Level 2
SectorEconomatica
Economatica Industry classification
2 Review of logit regression and its applications in Finance
The logit model is one type of non-linear regression model where the dependent variable is binary. The logistic or logit model is used to examine the relationship between one or more quantitative variables and the probability of an event happening (1=the event happens; 0 otherwise). For example, a bank that gives loans to businesses might be very interested in knowing which are the factors/variables/characteristics of firms that are more related to loan defaults. If the bank understands these factors, then it can improve its decisions about which firms deserve a loan, and minimize the losses due to loan defaults.
In this workshop, we define the event to be whether a firm in a specific quarter has higher stock return compared to median returns of the firms within its industry. If the stock return is higher than the median return, then we codify the binary variable equal to 1; 0 otherwise.
Then, in this case, the dependent variable of the regression is the binary variable with 1 if the stock outperforms the median of its industry and 0 otherwise. The independent or explanatory variables can be any financial indicator/ratio/variable that we believe is related to the likelihood of a stock to beat the market in the near future.
We can define this logistic model using the following mathematical function. Imagine that Y is the binary dependent variable, then the probability that the event happens (Event=1) can be defined as:
Prob(Event=1)=f(X_{1},X_{2},...,X_{n})
The binary variable Event can be either 1 or 0, but the probability of Event=1 is a continuous value from 0 to 1. The function is a non-linear function defined as follows:
As we can see, the argument of the exponential function is actually a traditional regression equation. We can re-express this equation as follows:
Y=b_{0}+b_{1}X_{1}+b_{2}X_{2}+...+b_{n}X_{n}
Now we use Y in the original non-linear function:
Prob(Event=1)=\frac{1}{1+e^{-Y}}
This is a non-linear function since the value of the function does not move in a linear way with a change in the value of one independent variable X.
Let’s work with an example of a model with only 1 independent variable X_1. Imagine that X_1 is the variable earnings per share (eps) of a firm, and the event is that the firm beats the market. Then, let’s do a simple example with specific values for b_0, b_1, and a range of values for eps from -1 to 1 jumping by 0.1:
import numpy as np# Create a numeric vector from -1 to 1 by 0.1eps = np.arange(-1.0, 1.0+1e-9, 0.1) # inclusive endb0 =-4b1 =10.0# Regression-like linear index and logistic transformY = b0 + b1 * epsprob =1.0/ (1.0+ np.exp(-Y))# Looking at the valueos of eps and probabilitiesprint(eps)
Plotting the probability values as Y and eps as X:
import matplotlib.pyplot as plt# Plot the logistic curveplt.plot(eps, prob)plt.title("A logistic function: expected probability of an event given values of eps")plt.xlabel("eps")plt.ylabel("prob")plt.grid(True)plt.show()
Here we can see that function is not linear with changes in eps. There is a specific range of values for eps close to 0 when the probability that the firm beats the market increases very fast up to a value of about 0.3 where the probability grows very slow with any more increase in eps.
The interpretations of the magnitude of the coefficients b_0 and b_1 in logistic regression is not quite the same as the case of multiple regression. However, the interpretations of the sign of the coefficient (positive or negative) and the level of significance (pvalue) are the same as in the case of multiple regression model. What we can say up to know is that if b_1 is positive and significant (its p-value<0.05), then it means that the variable, in this case, eps is significantly and positively related to the probability that a firm return outperform its industry median return.
Before going to the interpretation of the magnitude of a coefficient, here is a quick explanation of how the logistic regression works and how it is estimated in any specialized software (such as R).
Let’s continue with the same event, which is that the firm return beats its industry median (in other words, that the firm return is higher than the median return of its industry). Then:
p = probability that the firm beats its industry (Event=1); or that the event happens.
(1-p) = probability that the firm DOES NOT beat its industry (Event=0); or that the event does not happen.
To have a dependent variable that can get any numeric value from a negative value to a positive value, we can do the following mathematical transformation with these probabilities:
Y=log\left(\frac{p}{1-p}\right)
The \left(\frac{p}{1-p}\right) is called the odds ratio:
ODDSRATIO=\left(\frac{p}{1-p}\right)
The odds ratio is the ratio of the probability of the event happening to the probability of the event NOT happening. Since p can have a value from 0 to 1, then the possible values of ODDSRATIO can be from 0 (when p=0) to infinity (when p=1). Since we want a variable that can have values from any negative to any positive value, then we can apply the logarithmic function, and then the range of this log will be from any negative value to any positive value.
Now that we have a transformed variable Y (the log of ODDSRATIO) that uses the probability p, then we can use this variable as the dependent variable for our regression model:
Y=log\left(\frac{p}{1-p}\right)=b_{0}+b_{1}X_{1}
This mathematical trick help us to use a linear model to model a non-linear relationship!
Then, with this transformation we can estimate a linear regression model (don’t worry, Python estimate it), so the coefficients b_0 and b_1 values define the logarithm of the odd ratio!, not the actual probability p of the event happening!
How can we interpret the magnitude of the beta coefficients of this regression? Let’s do a mathematical trick from the previous equation. We can apply the exponential function to both sides of the equation:
Then, if X_1 increases in one unit, then the ODDSRATIO will be equal to ODDSRATIO times e^{b_1}. Then, e^{b_1} will be the factor that indicates how many times the ODDSRATIO changes with a +1-unit change in X_1.
Then:
If e^{b_1} = 1, then the ODDSRATIO does not change, meaning that there is no relationship between the variable X_1 and the probability of the event.
If e^{b_1} > 1, then the ODDSRATIO will grow by this factor, meaning that there is a positive relationship between X_1 and the probability of the event.
If e^{b_1} < 1, then the ODDSRATIO will decrease by this factor, meaning that there is a negative relationship between X_1 and the probability of the event.
Then, when we see the output of a logistic regression, we need to apply the exponential function to the coefficients to provide a meaningful interpretation of the magnitude of the coefficients in the model.
If we want to estimate the probability p for any value of X_1, we just need to do some algebraic manipulations to the previous equation:
Y=log\left(\frac{p}{1-p}\right)=b_{0}+b_{1}X_{1}
To get p from this equation, we can apply the exponential function to both sides:
e^{Y}=\left(\frac{p}{1-p}\right)
Leaving p alone, we multiply both sides times (1-p):
e^{Y}\left(1-p\right)=p
We continue playing with the terms to leave p alone:
e^{Y}-pe^{Y}=p
e^{Y}=p+pe^{Y}
e^{Y}=p(1+e^{Y})
p=\frac{e^{Y}}{\left(1+e^{Y}\right)}
Dividing the numerator and the denominator by e^Y ;
This is the same mathematical equation we had used above to illustrate the non-linear relationship between the explanatory variables and the probability of an event happening!
Fortunately, Python performs all these calculations automatically when you run the predict function! So, you do not have to memorize these steps; I just tried to be curious to understand how the logistic model estimates predicted probabilities.
3 Data collection
The dataset we will use for this workshop and for the final project is located in a public we site.
This dataset has real historical information of all US public firms listed in NASDAQ and NYSE. It has data from Q1 2000 to Q2 2024. You can download the dataset as follows.
3.1 Importing data
import requestsheaders = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'}# Download the first fileurl1 ='https://www.apradie.com/datos/firmsus2024.csv'response1 = requests.get(url1, headers=headers)withopen('usfirms.csv', 'wb') as f: f.write(response1.content)
747426
# Download the second fileurl2 ='https://www.apradie.com/datos/usdataq.csv'response2 = requests.get(url2, headers=headers)withopen('uspanel.csv', 'wb') as f: f.write(response2.content)
55336905
Importing the csv files:
import pandas as pd# Import as data framesusfirms = pd.read_csv('usfirms.csv')uspanel = pd.read_csv('uspanel.csv')# Display the first few rows of each DataFrame to confirmusfirms.head()
empresa ... SectorEconomatica
0 A ... Electroelectronic
1 AA ... Siderur & Metalur
2 AABA_old ... Software y Datos
3 AAC_old ... Otros
4 AAIC_old ... Fondos
[5 rows x 7 columns]
uspanel.head()
firm q fiscalmonth ... sharesoutstanding fixedassets yearf
0 A 2000q1 NaN ... 452000.000 NaN NaN
1 A 2000q2 6.0 ... 452271.967 1453000.0 2000.0
2 A 2000q3 9.0 ... 453014.579 1581000.0 2000.0
3 A 2000q4 12.0 ... 456366.381 1741000.0 2000.0
4 A 2001q1 3.0 ... 456769.737 1821000.0 2001.0
[5 rows x 27 columns]
4 CHALLENGE 1 - CALCULATE QUARTERLY REVENUE AND EBIT FROM YTD AMOUNTS
All income-statement variables in the uspanel dataset are Year-to-Date (YTD) amounts starting in the first fiscal quarter.
For example, if you look at revenue of a firm in the fiscal Q3 (fiscalmonth==9), that amount will be the total revenue of the firm starting in the first fiscal Q1 until the fiscal Q3.
Remember that in the US public firms can end a fiscal year in any of the 4 calendar quarters.
Using the uspanel dataset calculate EBIT as: revenue - cogs - sgae - depreciation
Remember that the YTD amount is a cumulative amount from the first fiscal quarter up to any fiscal quarter. Then, what might be the algorithm/code to create the quarter-amount for revenue and EBIT ?
CHALLENGE 1 - WRITE YOUR PSEUDO-ALGORITHM WITH YOUR OWN WORDS, AND WRITE THE PYTHON CODE TO CREATE THE QUARTERLY REVENUE AND EBIT AMOUNT IN THE USPANEL DATASET
5 CHALLENGE 2 - GROWTH OF THE US FINANCIAL MARKET
Using the panel dataset, you have to understand how the US financial market has been growing over the years starting from the oldest year to the last complete fiscal year (2023).
You have to examine how much the US has grown in terms of:
You have to create a table of these 3 totals by year (years as rows)
Which graph(s) would you design to better understand the US financial growth in terms of these variables?
6 CHALLENGE 3 - RUN A LOGIT MODEL TO PREDICT PROBABILITY OF BEATING THE INDUSTRY MEDIAN RETURN
You have to run a logit model to examine whether Operational Earnings per share divided by price (EBITQ/sharesoutstanding) / originalprice explains the probability that a firm has a higher annual return than its corresponding median industry return.
Use the naics1 column as the industry. naics stands for North American Industry Classification System.
You have to create the following variables for the model:
oepsp = Operational Earnings per Share divided by price = (EBITQ/sharesoutstanding) / originalprice
oepspw = Winsorized version of oepsp (use the winsorize function from the statar package to do this)
r = Annual stock return (log return); remember that you have quarterly data
Hint: use this r variable to calculate the median return by industry.
Remember that EBITQ is the Quarterly EBIT you calculated in Challenge 1.
CHALLENGE 4 - INTERPRET WITH YOUR WORDS THE LOGIT MODEL
7 Datacamp online courses and other materials
You will receive an email invitation to register in datacamp.com. Datacamp is one of the best online learning sites for Data Science applied to Business and Finance.
You will receive free access for the whole semester! Accept the invitation to be registered in Datacamp.
You can get official certificates from Datacamp and post them in your Linkedin profile!
You must take the following chapter:
Course: “Credit Risk Modeling in Python”, Chapter 2: “Logistic Regression for Defaults”
8 W1 submission
The grade of this Workshop will be the following:
Complete (100%): If you submit an ORIGINAL and COMPLETE HTML file with all the activities, with your notes, and with your OWN RESPONSES to questions
Incomplete (75%): If you submit an ORIGINAL HTML file with ALL the activities but you did NOT RESPOND to the questions and/or you did not do all activities and respond to some of the questions.
Very Incomplete (10%-70%): If you complete from 10% to 75% of the workshop or you completed more but parts of your work is a copy-paste from other workshops.
Not submitted (0%)
Remember that you have to submit your Colab link file through Canvas BEFORE THE FIRST CLASS OF NEXT WEEK.
