Workshop 1, Financial Modeling and Programming

Quarterly financial statements, stock screening and the logistic regression model

Author

Alberto Dorantes, Ph.D.

Published

November 1, 2026

Abstract

This is an INDIVIDUAL workshop. We move from annual to quarterly financial statements: we convert Year-to-Date amounts into quarterly amounts, calculate trailing-twelve-month (TTM) figures and financial ratios, and program a stock screen that respects the information that investors really had at the decision date. Then we study in depth the logistic regression model: why we cannot use a linear regression for a binary event, the ladder probability → odds → log-odds, why we must exponentiate the coefficients to interpret their magnitude, and how to translate the results into probabilities. We apply the model to predict whether a stock will be among the top 20% of returns in the next quarter.

1 General directions

Work in Google Colab. Log in with your @tec.mx account and create a new notebook named:

W1-FinProg-YourFirstName-YourLastName

When you finish, download it as a Jupyter Notebook (File → Download → Download .ipynb) and submit the .ipynb file through Canvas.

1.1 How to work in this workshop

As in the Algorithms course, follow the cycle Predict → Run → Investigate → Modify → Make: before running a chunk, write in a text cell what you expect; then run it, and investigate any difference. Re-type the code instead of copy-pasting it, and write your own notes as if this notebook were your study guide for the Evidence and for the theory exam.

1.2 Using an LLM (Gemini, ChatGPT, Claude, etc.)

You may use an LLM to write code, with the same rules as in the Algorithms course:

Write your own algorithm (pseudocode) first in a text cell, and use it as your prompt. Paste your prompt in quotes.
Verify every result with an independent check (an assert, a number you know, a hand calculation).
Be ready to explain any line of your code: the Canvas quiz and the exams will ask you to do so.

1.3 Grading of this workshop

Part	Weight	What is evaluated
Notebook (.ipynb submitted in Canvas)	60%	Completeness: all code replicated, all challenges attempted, notes with your own explanations
Canvas Quiz W1	40%	Automatically graded: key results of your notebook and short concept / code-reading questions

Notebook scale: Code replication and personal notes 20%, and 80% for the completeness and accuracy of the challenges (each challenge has the same weight).

The Quiz W1 is due the same day as the workshop; you have 2 attempts (Canvas keeps the highest). Before submitting, run all cells from top to bottom (Runtime → Run all).

2 What is new in this course

In the Algorithms course you worked with annual financial statements, financial ratios, the golden rule of panel data (groupby + shift), and a first logistic regression. In this course we build a complete investment process, one workshop at a time:

Workshop	Step of the investment process
W1	Fundamental screen: select stocks with financial ratios from quarterly statements; model the probability of an event with a logistic regression
W2	Market model at scale: estimate alpha and beta for many stocks and filter them
W3	Portfolio optimization and backtesting: build the portfolio and test it with data the model never saw
W4	Integration: risk measures and a complete dry run of the Evidence

All the code in this course follows one principle: at the decision date, use only the information that an investor really had at that date. Breaking this principle is called look-ahead bias, and it is the most common reason why investment strategies look great in a notebook and lose money in real life.

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import statsmodels.formula.api as smf
pd.set_option("display.width", 120)

3 The data

We use a panel of quarterly financial statements of all US public firms (NYSE and NASDAQ), active and cancelled, from Q1 2000 to Q2 2024, and a catalog of the firms.

The following function downloads a file only if it is not already in your Colab session (to avoid downloading 55 MB every time you run the notebook):

import os, io, requests

def import_csv(file, base_url="https://www.apradie.com/datos/"):
    """Imports a csv file from base_url; downloads it only once and keeps a local copy."""
    if not os.path.exists(file):
        headers = {"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"}
        response = requests.get(base_url + file, headers=headers)
        response.raise_for_status()               # error if the response is 4xx or 5xx
        with open(file, "wb") as f:
            f.write(response.content)
        print(f"{file} downloaded")
    return pd.read_csv(file)

uspanel = import_csv("usdataq.csv")
usfirms = import_csv("firmsus2024.csv")
print(uspanel.shape, usfirms.shape)

(557718, 27) (5689, 7)

3.1 Data dictionaries

uspanel has a panel-data (long) structure: one row per firm-quarter. All $ amounts are in thousands of US dollars; sharesoutstanding is in thousands of shares.

Variable	Description
firm	Firm ticker
q	Calendar quarter (e.g., 2023q4)
fiscalmonth	Month of the fiscal year in which the quarter ends: 3, 6, 9 or 12
yearf	Fiscal year (see the warning below)
revenue, cogs, sgae, depreciation, otherincome, extraordinaryitems, finexp, incometax	Income-statement items, Year-to-Date (YTD): cumulative from the first fiscal quarter to the current quarter
cashflowoper	Operating cash flow (YTD)
totalassets, currentassets, cashinbanks, accountsreceivables, inventory, fixedassets	Balance-sheet items at the end of the quarter
totalliabilities, currentliabilities, accountspayable, shortdebt, longdebt	Balance-sheet items at the end of the quarter
adjprice	Stock price adjusted for splits and dividends: use it to calculate returns
originalprice	Historical (not adjusted) stock price: use it with `sharesoutstanding` to calculate market value
sharesoutstanding	Historical number of shares

usfirms is a catalog with one row per firm:

Variable	Description
empresa	Firm ticker (same codes as `firm` in uspanel)
Nombre	Name of the firm
status	`activo` (active) or `cancelado` (cancelled: merged, acquired or bankrupt)
partind	Weight (%) of the firm in the S&P 500 index; missing if the firm is not in the index
naics1, naics2	Industry (North American Industry Classification System), levels 1 and 2

As in the Algorithms course, we merge both datasets with a left join and we check the merge:

df = pd.merge(uspanel, usfirms[["empresa", "Nombre", "status", "partind", "naics1"]],
              left_on="firm", right_on="empresa", how="left").drop(columns="empresa")
assert len(df) == len(uspanel), "The merge duplicated rows!"

# The golden rule of panel data: sort by firm and time before any shift/diff
df = df.sort_values(["firm", "q"]).reset_index(drop=True)
df["year"] = df["q"].str[:4].astype(int)          # calendar year
print(df["firm"].nunique(), "firms;", df.groupby("firm").size().unique(), "quarters per firm")

5691 firms; [98] quarters per firm

Every firm has exactly 98 rows (2000q1 to 2024q2). The quarters before a firm went public, or after it was cancelled, are empty. Because the panel is balanced, the previous row of a firm is always the previous calendar quarter.

4 From Year-to-Date to quarterly amounts

4.1 Fiscal years and the fiscalmonth column

A firm can close its fiscal year in any quarter. About 80% of US firms close in December, but Apple closes at the end of September:

cols = ["firm", "q", "fiscalmonth", "yearf", "revenue"]
df.loc[df["firm"] == "AAPL", cols].tail(8)

	firm	q	fiscalmonth	yearf	revenue
1168	AAPL	2022q3	12.0	2022.0	394328000.0
1169	AAPL	2022q4	3.0	2023.0	117154000.0
1170	AAPL	2023q1	6.0	2023.0	211990000.0
1171	AAPL	2023q2	9.0	2023.0	293787000.0
1172	AAPL	2023q3	12.0	2023.0	383285000.0
1173	AAPL	2023q4	3.0	2023.0	119575000.0
1174	AAPL	2024q1	6.0	2024.0	210328000.0
1175	AAPL	2024q2	9.0	2024.0	296105000.0

For Apple, 2022q4 is the first fiscal quarter of fiscal year 2023 (fiscalmonth = 3). Its revenue of 117 billion is the revenue of that quarter only. In 2023q1 (fiscalmonth = 6) the revenue column shows 212 billion: the cumulative revenue of the first two fiscal quarters. Then:

Revenue^{quarter}_t = \begin{cases} Revenue^{YTD}_t & \text{if } t \text{ is the first fiscal quarter (fiscalmonth = 3)}\\ Revenue^{YTD}_t - Revenue^{YTD}_{t-1} & \text{otherwise} \end{cases}

And the rows with fiscalmonth = 12 contain annual amounts.

Do not trust yearf to identify the fiscal year

Look again at Apple’s 2023q4: it is the first quarter of fiscal year 2024 (fiscalmonth = 3), but yearf says 2023. In this dataset, 559 first-fiscal-quarter rows have the same yearf as the previous quarter. If you calculate quarterly amounts with groupby(["firm", "yearf"]).diff(), those rows subtract the annual revenue of the previous fiscal year and become negative. The fiscalmonth column is reliable: use it.

4.2 The algorithm

The fiscal quarter number is fiscalmonth / 3 (1, 2, 3 or 4). A few rows have unusual values (4, 5 or 8); we treat them as missing.

INPUT: panel sorted by firm and q; a YTD column X
FOR each firm-quarter row:
    fq      = fiscal quarter number of the row
    fq_prev = fiscal quarter number of the PREVIOUS row of the SAME firm
    IF fq == 1:                 Xq = X                      (first fiscal quarter)
    ELSE IF fq_prev == fq - 1:  Xq = X - X of previous row  (consecutive fiscal quarters)
    ELSE:                       Xq = missing                (we cannot know the quarter amount)

The last case matters: if the previous quarter is missing, subtracting nothing would give us a YTD amount disguised as a quarterly amount. Using fillna() with the YTD value makes exactly that mistake.

4.3 How `np.select` works

In pandas we do not write a loop over 557,000 rows. Instead, we vectorize the IF / ELSE IF / ELSE with the NumPy function np.select:

result = np.select(conditions, values, default=...)

conditions: a list of boolean columns (True/False for each row), one per IF / ELSE IF of the algorithm.
values: a list with the same number of elements, with the value to use when the corresponding condition is True. Each value can be a single number or a whole column.
default: the value for the rows where no condition is True (the ELSE).

For each row, np.select checks the conditions in order and takes the value of the first condition that is True. It is exactly the logic of an IF / ELSE IF / ELSE, applied to all rows at once. (np.where is the special case with only one condition: np.where(condition, value_if_true, value_if_false).)

Two details that matter:

All the values are calculated for all rows before choosing; np.select only decides which one to keep in each row.
A comparison with a missing value (NaN == 1, NaN == 3 - 1) is False. Then, rows with missing information fall into the default, which is what we want.

4.3.1 A small example with the structure of our panel

The following data frame has the same structure as uspanel: two firms, sorted by firm and quarter, with YTD revenue. Firm AAA closes its fiscal year in December; firm BBB closes it in September (like Apple), and one of its quarters is missing:

toy = pd.DataFrame({
    "firm":        ["AAA"] * 5 + ["BBB"] * 5,
    "q":           ["2022q3", "2022q4", "2023q1", "2023q2", "2023q3"] * 2,
    "fiscalmonth": [9, 12, 3, 6, 9,      12, 3, np.nan, 9, 12],
    "revenue":     [75, 100, 30, 55, 85,  400, 110, np.nan, 300, 390]})

toy["fq"] = toy["fiscalmonth"] / 3                              # fiscal quarter number
toy["prev_fq"] = toy.groupby("firm")["fq"].shift(1)             # fiscal quarter of the previous row, same firm
toy["prev_ytd"] = toy.groupby("firm")["revenue"].shift(1)       # YTD revenue of the previous row, same firm

cond_first = toy["fq"] == 1                                     # IF: first fiscal quarter
cond_consecutive = toy["prev_fq"] == toy["fq"] - 1              # ELSE IF: previous row is the previous fiscal quarter

toy["cond_first"] = cond_first
toy["cond_consecutive"] = cond_consecutive
toy["rule_applied"] = np.select([cond_first, cond_consecutive],
                                ["first fiscal Q: X", "X - previous X"], default="missing")
toy["revenueq"] = np.select([cond_first, cond_consecutive],
                            [toy["revenue"], toy["revenue"] - toy["prev_ytd"]], default=np.nan)
toy

	firm	q	fiscalmonth	revenue	fq	prev_fq	prev_ytd	cond_first	cond_consecutive	rule_applied	revenueq
0	AAA	2022q3	9.0	75.0	3.0	NaN	NaN	False	False	missing	NaN
1	AAA	2022q4	12.0	100.0	4.0	3.0	75.0	False	True	X - previous X	25.0
2	AAA	2023q1	3.0	30.0	1.0	4.0	100.0	True	False	first fiscal Q: X	30.0
3	AAA	2023q2	6.0	55.0	2.0	1.0	30.0	False	True	X - previous X	25.0
4	AAA	2023q3	9.0	85.0	3.0	2.0	55.0	False	True	X - previous X	30.0
5	BBB	2022q3	12.0	400.0	4.0	NaN	NaN	False	False	missing	NaN
6	BBB	2022q4	3.0	110.0	1.0	4.0	400.0	True	False	first fiscal Q: X	110.0
7	BBB	2023q1	NaN	NaN	NaN	1.0	110.0	False	False	missing	NaN
8	BBB	2023q2	9.0	300.0	3.0	NaN	NaN	False	False	missing	NaN
9	BBB	2023q3	12.0	390.0	4.0	3.0	300.0	False	True	X - previous X	90.0

Read the table row by row:

AAA 2022q3 (fq = 3): it is not a first fiscal quarter, and its previous row belongs to no one (it is the first row of AAA, so prev_fq is missing): both conditions are False → default = missing. We cannot know the revenue of this quarter without the previous one.
AAA 2022q4 (fq = 4, prev_fq = 3): the second condition is True → 100 − 75 = 25.
AAA 2023q1 (fq = 1): the first condition is True → 30, the YTD amount itself. Notice that the second condition is False here (4 ≠ 1 − 1); but even if both were True, np.select would use the first one.
BBB 2022q3 (fq = 4): first row of BBB → missing. Notice that shift(1) within the firm did not take the 85 of AAA: that is the golden rule of panel data.
BBB 2022q4 (fq = 1) → 110.
BBB 2023q1 has no data → missing; and BBB 2023q2 (fq = 3) has a missing previous quarter (prev_fq is NaN, and NaN == 2 is False) → missing. If we had used diff().fillna(revenue), this row would show 300, a YTD amount disguised as a quarterly amount.
BBB 2023q3 (fq = 4, prev_fq = 3) → 390 − 300 = 90.

The column rule_applied shows that the same np.select can return text: it is a good way to check which branch of your algorithm was used in each row.

4.3.2 The function for the whole panel

Now we write the same logic as a function, and apply it to uspanel:

df["fq"] = np.where(df["fiscalmonth"].isin([3, 6, 9, 12]), df["fiscalmonth"] / 3, np.nan)

def ytd_to_quarterly(data, col):
    """Converts a Year-to-Date column into quarterly amounts. data must be sorted by firm and q."""
    prev_ytd = data.groupby("firm")[col].shift(1)
    prev_fq = data.groupby("firm")["fq"].shift(1)
    conditions = [data["fq"] == 1, prev_fq == data["fq"] - 1]
    values = [data[col], data[col] - prev_ytd]
    return np.select(conditions, values, default=np.nan)

df["revenueq"] = ytd_to_quarterly(df, "revenue")
df.loc[df["firm"] == "AAPL", ["q", "fiscalmonth", "revenue", "revenueq"]].tail(6)

	q	fiscalmonth	revenue	revenueq
1170	2023q1	6.0	211990000.0	94836000.0
1171	2023q2	9.0	293787000.0	81797000.0
1172	2023q3	12.0	383285000.0	89498000.0
1173	2023q4	3.0	119575000.0	119575000.0
1174	2024q1	6.0	210328000.0	90753000.0
1175	2024q2	9.0	296105000.0	85777000.0

Apple’s 2023q4 quarterly revenue is 119,575 million dollars, which is exactly what Apple reported for its Q1 of fiscal 2024. Always verify your code with a number you can check in a public source.

5 CHALLENGE 1: Quarterly EBIT and net income

Create the YTD columns ebit_ytd = revenue - cogs - sgae - depreciation and netincome_ytd = ebit\_ytd + otherincome + extraordinaryitems - finexp - incometax.
Using the function ytd_to_quarterly, create the quarterly columns ebitq and netincomeq.
Verify with an assert that the sum of the 4 quarterly revenues of Apple of fiscal year 2023 (2022q4 to 2023q3) is equal to the annual revenue of the row with fiscalmonth = 12 (2023q3).

# Write your code here

6 Trailing-twelve-month (TTM) amounts and financial ratios

6.1 What is a TTM amount?

Quarterly amounts have two problems for financial analysis: they are seasonal (a retailer sells much more in Q4 than in Q1), and they cover only 3 months, while ratios such as ROA or earnings yield are usually expressed per year. Annual amounts solve both problems, but they are published only once a year.

The trailing-twelve-month (TTM) amount solves both problems at the same time: in each quarter, it is the sum of the last 4 quarterly amounts, the current one and the 3 previous ones:

X^{TTM}_t = X^{q}_t + X^{q}_{t-1} + X^{q}_{t-2} + X^{q}_{t-3}

Then, every quarter we have a number that covers one complete year (so it has no seasonality), and that is updated every quarter with the most recent information. In the quarter in which a firm closes its fiscal year, the TTM amount is equal to the annual amount. Financial websites (Yahoo Finance, Bloomberg) report the P/E ratio and other ratios with TTM earnings.

For example, Apple’s TTM revenue in 2023q3 (the end of its fiscal year 2023) is the sum of the quarterly revenues of 2022q4, 2023q1, 2023q2 and 2023q3, and it must be equal to the annual revenue of fiscal 2023.

6.2 How we compute it: `groupby` + `transform` + `rolling`

We compute a TTM amount with one line of code:

df["revenue_ttm"] = df.groupby("firm")["revenueq"].transform(lambda s: s.rolling(4, min_periods=4).sum())

Read it from the inside out:

s.rolling(4, min_periods=4).sum(): for a column s, rolling(4) creates a moving window of 4 consecutive rows (the current row and the 3 previous rows), and .sum() adds the values of each window. min_periods=4 says: “if the window does not have 4 non-missing values, return a missing value”. Then, the first 3 quarters of each firm, and any window with a missing quarter, give a missing TTM amount, instead of a sum of fewer than 4 quarters (which would not be one year).
lambda s: ...: a lambda is a small function without a name, written in one line. lambda s: s.rolling(4, min_periods=4).sum() is the same as:
```
def ttm(s):
    return s.rolling(4, min_periods=4).sum()
```
We use a lambda because we need this function only once.
df.groupby("firm")["revenueq"].transform(...): splits the column revenueq into one piece per firm, applies the function to each piece separately, and puts the results back in the same rows of the original data frame. transform always returns one value per row (unlike agg, which returns one value per group), so we can assign the result as a new column.

Why do we need the groupby? Because the rolling window must never mix two firms. Let’s see it with a small panel of two firms with 5 quarters each:

toy2 = pd.DataFrame({"firm": ["AAA"] * 5 + ["BBB"] * 5,
                     "q": ["2022q1", "2022q2", "2022q3", "2022q4", "2023q1"] * 2,
                     "revenueq": [10, 20, 30, 40, 50, 1, 2, np.nan, 4, 5]})
toy2["ttm_wrong"] = toy2["revenueq"].rolling(4, min_periods=4).sum()           # no groupby
toy2["ttm_no_min"] = toy2.groupby("firm")["revenueq"].transform(lambda s: s.rolling(4, min_periods=1).sum())
toy2["ttm_right"] = toy2.groupby("firm")["revenueq"].transform(lambda s: s.rolling(4, min_periods=4).sum())
toy2

	firm	q	revenueq	ttm_wrong	ttm_no_min	ttm_right
0	AAA	2022q1	10.0	NaN	10.0	NaN
1	AAA	2022q2	20.0	NaN	30.0	NaN
2	AAA	2022q3	30.0	NaN	60.0	NaN
3	AAA	2022q4	40.0	100.0	100.0	100.0
4	AAA	2023q1	50.0	140.0	140.0	140.0
5	BBB	2022q1	1.0	121.0	1.0	NaN
6	BBB	2022q2	2.0	93.0	3.0	NaN
7	BBB	2022q3	NaN	NaN	3.0	NaN
8	BBB	2022q4	4.0	NaN	7.0	NaN
9	BBB	2023q1	5.0	NaN	11.0	NaN

ttm_wrong (no groupby): BBB’s first row (2022q1) adds AAA’s last 3 quarters (30 + 40 + 50) to BBB’s 1 = 121, a number that mixes two firms.
ttm_no_min (min_periods=1): the first rows of each firm add only 1, 2 or 3 quarters, and the windows with BBB’s missing quarter add only 3 quarters. These are not one-year amounts.
ttm_right: AAA 2022q4 = 10 + 20 + 30 + 40 = 100, AAA 2023q1 = 20 + 30 + 40 + 50 = 140; all the windows of BBB contain the missing 2022q3, so BBB has no valid TTM amount.

Now with the real data:

df["revenue_ttm"] = df.groupby("firm")["revenueq"].transform(lambda s: s.rolling(4, min_periods=4).sum())
df.loc[df["firm"] == "AAPL", ["q", "fiscalmonth", "revenueq", "revenue_ttm"]].tail(5)

	q	fiscalmonth	revenueq	revenue_ttm
1171	2023q2	9.0	81797000.0	383933000.0
1172	2023q3	12.0	89498000.0	383285000.0
1173	2023q4	3.0	119575000.0	385706000.0
1174	2024q1	6.0	90753000.0	381623000.0
1175	2024q2	9.0	85777000.0	385603000.0

Apple’s TTM revenue in 2023q3 (fiscalmonth = 12) is 383,285,000 thousand dollars, exactly its annual revenue of fiscal 2023, as expected.

6.3 Return on Assets with a lagged denominator

As in the Algorithms course, we divide a flow (income of a period) by the stock of assets at the beginning of the period:

ROA_t = \frac{NetIncome^{TTM}_t}{TotalAssets_{t-4}}

With quarterly data, the total assets of one year ago are 4 rows before, within the same firm: groupby("firm")["totalassets"].shift(4).

6.4 When does an investor know the numbers? Reporting lags

A firm does not publish its financial statements on the last day of the quarter. US public firms must file their quarterly report (10-Q) within 40–45 days, and their annual report (10-K) within 60–90 days after the end of the fiscal year. Then:

The statements of a fiscal year that ended in December 2022 were public by March 2023.
If on December 31, 2022 you select stocks using the December 2022 statements, you are using information from the future: look-ahead bias.

In this course, the decision date for a screen based on annual statements is the end of March of the next year.

7 CHALLENGE 2: A stock screen with Return on Assets

You are a portfolio manager on March 31, 2023. You want to select the 20 S&P 500 firms with the highest ROA, using the most recent annual report that each firm had published at that date.

Create ttm columns for net income (netincome_ttm) and the column totalassets_lag4. Then create roa_ttm.
Create the data frame annual with the rows where fiscalmonth == 12 (annual reports). Explain why, in these rows, netincome_ytd is the annual net income, and why roa_ttm is the annual ROA (net income of the fiscal year divided by total assets at the end of the previous fiscal year).
Write a function screen(data, ratio, n, last_q, first_q) that:
- keeps the rows of firms in the S&P 500 (partind not missing) with fiscal-year ends between first_q and last_q (e.g., between “2022q1” and “2022q4”) and a non-missing ratio;
- keeps the last annual report of each firm in that window;
- returns a data frame with the n firms with the highest value of the ratio (columns firm, Nombre, q, ratio), sorted from highest to lowest.
Run screen(annual, "roa_ttm", 20, "2022q4", "2022q1"). Show the result. How many firms passed the filters before selecting the top 20?
Why do we use last_q = "2022q4" and not “2023q1” if the decision date is March 31, 2023? Which firm of the list has a fiscal year that does not end in December?
Critical thinking: we only screen the firms that are in the S&P 500 today (partind). Why does this create survivorship bias in a historical screen? Which firms are we missing?

# Write your code here

8 The logistic regression model, step by step

Many decisions in Finance are about events: will a client default? will a firm report a loss? will this stock be one of the big winners next quarter? The event is a binary variable: Y=1 if it happens and Y=0 if not. We want a model for the probability that Y=1 given some financial variables X.

In the Algorithms course you used a logistic regression to predict probabilities. Now we will understand how the model is built, so that we can interpret it correctly.

8.1 A first example: will the firm report a loss next quarter?

We start with an event that financial ratios predict very well: a net loss in the next quarter, explained by the current quarterly ROA. We use the quarterly ROA winsorized by quarter at 1%–99% (winsorizing replaces extreme values with the 1st and 99th percentiles of each quarter):

g = df.groupby("firm")
df["roaq"] = df["netincomeq"] / g["totalassets"].shift(1)
df = df.replace([np.inf, -np.inf], np.nan)

def winsorize(s, low=0.01, high=0.99):
    """Replaces values below/above the low/high percentiles by those percentiles."""
    return s.clip(lower=s.quantile(low), upper=s.quantile(high))

df["roaqw"] = df.groupby("q")["roaq"].transform(winsorize)

next_ni = g["netincomeq"].shift(-1)                      # net income of the NEXT quarter, same firm
df["loss_next"] = np.where(next_ni.isna(), np.nan, (next_ni < 0).astype(float))

loss = df.dropna(subset=["loss_next", "roaqw"])
print(f"{len(loss):,} firm-quarters; {loss['loss_next'].mean():.1%} of them had a loss in the next quarter")

241,510 firm-quarters; 31.7% of them had a loss in the next quarter

8.2 Why not a linear regression?

We could run a linear regression of the 0/1 variable on ROA. This is called the linear probability model:

lpm = smf.ols("loss_next ~ roaqw", data=loss).fit()
pred = lpm.fittedvalues
print(f"Predicted 'probabilities' go from {pred.min():.2f} to {pred.max():.2f}")
print(f"{(pred < 0).mean():.1%} are below 0 and {(pred > 1).mean():.1%} are above 1")

Predicted 'probabilities' go from -0.26 to 2.80
0.6% are below 0 and 1.9% are above 1

A probability cannot be negative or higher than 1. The problem is the straight line: a line has no limits. We need a curve that stays between 0 and 1, and that is what the logistic model does.

8.3 The ladder: probability → odds → log-odds

The logistic model uses two transformations of the probability p. Think of them as a ladder:

Scale	Formula	Possible values	Example: p=0.2	p=0.5	p=0.8
Probability	p	0 to 1	0.20	0.50	0.80
Odds	\frac{p}{1-p}	0 to +\infty	0.25	1	4
Log-odds (logit)	\ln\left(\frac{p}{1-p}\right)	-\infty to +\infty	−1.39	0	+1.39

The odds compare the probability that the event happens with the probability that it does not happen. Odds of 4 mean “4 to 1”: for every time the event does not happen, it happens 4 times. This is the language of sports betting.
Odds of 1 mean a 50/50 event. Odds below 1 mean the event is less likely than not.
The log of the odds can take any value, negative or positive, exactly like the result of a regression equation b_0+b_1X.

That is the whole trick of the logistic model: we model the log-odds with a linear equation:

\ln\left(\frac{p}{1-p}\right) = b_0 + b_1 X

p = np.linspace(0.01, 0.99, 99)
fig, ax = plt.subplots(1, 2, figsize=(11, 3.8))
ax[0].plot(p, p / (1 - p)); ax[0].set_title("Odds = p / (1-p)"); ax[0].set_xlabel("probability p")
ax[1].plot(p, np.log(p / (1 - p))); ax[1].set_title("Log-odds = ln(p / (1-p))"); ax[1].set_xlabel("probability p")
for a in ax: a.grid(alpha=0.3)
plt.tight_layout(); plt.show()

8.4 Going down the ladder: the sigmoid function

If the model gives us the log-odds z=b_0+b_1X, we go back to the probability in two steps: odds=e^{z}, and p=\frac{odds}{1+odds}. Putting both steps together:

p=\frac{e^{z}}{1+e^{z}}=\frac{1}{1+e^{-z}}

This is the sigmoid (logistic) function. Let’s see how b_0 and b_1 change the curve:

x = np.linspace(-0.3, 0.3, 300)
sigmoid = lambda z: 1 / (1 + np.exp(-z))
fig, ax = plt.subplots(1, 2, figsize=(11, 3.8))
for b1 in [-5, -15, -40]:
    ax[0].plot(x, sigmoid(-1 + b1 * x), label=f"b0=-1, b1={b1}")
for b0 in [-2, -1, 0]:
    ax[1].plot(x, sigmoid(b0 - 15 * x), label=f"b0={b0}, b1=-15")
for a in ax:
    a.set_xlabel("X (e.g., ROA)"); a.set_ylabel("p"); a.legend(); a.grid(alpha=0.3)
ax[0].set_title("b1 controls the slope and direction"); ax[1].set_title("b0 moves the curve up/down")
plt.tight_layout(); plt.show()

The curve never goes below 0 or above 1. The coefficient b_1 tells us the direction (negative: more ROA, less probability of a loss) and how fast the probability changes.

8.5 How the computer estimates b_0 and b_1

There is no formula like in OLS. The computer uses maximum likelihood: it looks for the coefficients that make the outcomes we observed as probable as possible. For each firm-quarter, the model gives a probability p_i; the likelihood of an observation is p_i if the event happened and (1-p_i) if it did not. Maximizing the product of these likelihoods is the same as minimizing the log-loss that you minimized with gradient descent in the Algorithms course. statsmodels uses a faster algorithm (Newton’s method), but the idea is the same.

logit_loss = smf.logit("loss_next ~ roaqw", data=loss).fit(disp=0)
print(logit_loss.summary2().tables[1].round(4))
b0, b1 = logit_loss.params["Intercept"], logit_loss.params["roaqw"]

             Coef.  Std.Err.         z  P>|z|   [0.025   0.975]
Intercept  -1.0012    0.0052 -191.0224    0.0  -1.0115  -0.9910
roaqw     -32.7658    0.1890 -173.4045    0.0 -33.1361 -32.3954

8.6 Interpreting the coefficients: why we must exponentiate

Sign and significance are read as in OLS: b_1 is negative and its p-value is practically 0, so a higher ROA is significantly related to a lower probability of a loss next quarter.

Magnitude is different. b_1 is the change in the log-odds when ROA increases by 1 unit. Nobody thinks in log-odds, so we climb down one step of the ladder. If X increases by \Delta:

odds(X+\Delta)=e^{b_0+b_1(X+\Delta)}=e^{b_0+b_1X}\cdot e^{b_1\Delta}=odds(X)\cdot e^{b_1\Delta}

Then e^{b_1\Delta} is the factor that multiplies the odds. It is called the odds ratio. Three rules:

e^{b_1\Delta}>1: the odds increase; e^{b_1\Delta}<1: the odds decrease; e^{b_1\Delta}=1 (when b_1=0): no relationship.
Choose a realistic \Delta. ROA is a ratio: an increase of 1 unit (100 percentage points!) never happens. A change of +0.01 (one percentage point of quarterly ROA) makes sense.
The odds ratio multiplies the odds, not the probability.

odds_ratio = np.exp(b1 * 0.01)
print(f"b1 = {b1:.4f}; odds ratio for +0.01 in ROA = e^(b1 x 0.01) = {odds_ratio:.4f}")
print(f"The odds of a loss are multiplied by {odds_ratio:.4f}: they change by {odds_ratio - 1:+.2%}")

b1 = -32.7658; odds ratio for +0.01 in ROA = e^(b1 x 0.01) = 0.7206
The odds of a loss are multiplied by 0.7206: they change by -27.94%

8.7 From odds back to probabilities: the effect depends on where you start

Why not just say “the probability falls by X%”? Because the change in probability depends on the starting point. Take an odds ratio of 0.5 (the odds are cut in half) and apply it to firms with different initial probabilities:

rows = []
for p0 in [0.05, 0.20, 0.50, 0.80, 0.95]:
    odds0 = p0 / (1 - p0)
    odds1 = odds0 * 0.5                    # the SAME odds ratio for everybody
    p1 = odds1 / (1 + odds1)
    rows.append([p0, odds0, odds1, p1, p1 - p0])
pd.DataFrame(rows, columns=["p before", "odds before", "odds after (x0.5)", "p after", "change in p"]).round(3)

	p before	odds before	odds after (x0.5)	p after	change in p
0	0.05	0.053	0.026	0.026	-0.024
1	0.20	0.250	0.125	0.111	-0.089
2	0.50	1.000	0.500	0.333	-0.167
3	0.80	4.000	2.000	0.667	-0.133
4	0.95	19.000	9.500	0.905	-0.045

The same odds ratio moves the probability by only 2.4 percentage points for a firm at p=0.05, but by 16.7 points for a firm at p=0.5. This is the non-linearity of the sigmoid curve: the effect on the probability is largest in the middle of the curve and small at the extremes. That is why we report the magnitude with the odds ratio (constant for all firms), and when we want probabilities we calculate them for specific firms.

8.8 The intercept

When all the X are 0, the log-odds are b_0. Then e^{b_0} are the odds of the event for a firm with X=0, and the probability is:

p(X=0)=\frac{e^{b_0}}{1+e^{b_0}}

p_zero = np.exp(b0) / (1 + np.exp(b0))
print(f"e^b0 = {np.exp(b0):.4f} -> probability of a loss for a firm with ROA = 0: {p_zero:.4f}")

e^b0 = 0.3674 -> probability of a loss for a firm with ROA = 0: 0.2687

Note that we exponentiate b_0 alone: the \Delta multiplies only the coefficient of the variable that changes.

8.9 Predicted probabilities and marginal effects

The method predict goes down the whole ladder for us:

firms_example = pd.DataFrame({"roaqw": [-0.05, 0.0, 0.02, 0.05]})
firms_example["prob_loss"] = logit_loss.predict(firms_example)
firms_example.round(4)

	roaqw	prob_loss
0	-0.05	0.6541
1	0.00	0.2687
2	0.02	0.1602
3	0.05	0.0666

Another useful summary is the average marginal effect (AME): the average, over all observations, of the change in probability for a very small change in X. It is expressed in probability units, so it is easy to communicate, but remember that it is an average: for a specific firm the effect can be much larger or smaller.

print(logit_loss.get_margeff(at="overall").summary_frame().round(4))

        dy/dx  Std. Err.         z  Pr(>|z|)  Conf. Int. Low  Cont. Int. Hi.
roaqw -4.9526     0.0224 -220.6364       0.0         -4.9966         -4.9086

Common mistakes when interpreting a logistic regression

Wrong statement	Why it is wrong	Correct statement
“b_1=-30, so the probability falls 30 points when ROA increases 1 unit”	b_1 is in log-odds, not in probability	“The log-odds fall by 30 per unit of ROA; the odds are multiplied by e^{b_1\Delta}”
“The odds ratio is 1.30, so the probability increases 30%”	The odds increase 30%, not the probability	“The odds are 1.30 times higher”
“e^{b_1} is the probability of the event”	e^{b_1} is a ratio of odds	Use `predict` for probabilities
“The odds ratio for \Delta=0.10 is e^{0.10}\cdot b_1”	The \Delta goes inside the exponential	e^{b_1\times 0.10}
“To interpret the intercept with \Delta=0.10 I compute e^{0.10\,b_0}”	\Delta only applies to the variable that changes	The odds at X=0 are e^{b_0}

9 Predicting the big winners of the next quarter

Now the real investment question. Can financial ratios tell us which stocks will be among the top 20% of returns in the next quarter?

Event: Y=1 if the stock return of the next quarter is in the top quintile of the returns of all firms in that quarter; 0 otherwise. By construction, about 20% of the observations are 1.
Features (known at the end of quarter t):
- oepspw: operating earnings yield, TTM operating earnings per share divided by price, winsorized by quarter: \frac{EBIT^{TTM}_t/shares_t}{price_t}. A high value means the stock is “cheap” relative to its earnings (a value stock).
- bmrw: book-to-market ratio, winsorized by quarter: \frac{TotalAssets_t-TotalLiabilities_t}{MarketValue_t}.

About the timing

The statements of quarter t are published 40–60 days after the end of the quarter, so part of the next quarter’s return happens before investors know the numbers. A strict design would use the return of quarter t+2. We use t+1 because it is the standard in many studies, but keep this limitation in mind (Challenge 5 asks you about it).

10 CHALLENGE 3: Building the dataset

Calculate the quarterly simple stock return r with adjprice, within each firm, and the return of the next quarter r_next (shift(-1) within each firm).
For each quarter, classify r_next into quintiles (1 = lowest, 5 = highest) with df.groupby("q")["r_next"].transform(quintile), using this function (the last quarter of the data has no next-quarter returns, and qcut cannot split an empty group):
```
def quintile(x):
    """Quintile (1 to 5) of each value of x; missing if x has fewer than 5 values."""
    if x.notna().sum() < 5:
        return pd.Series(np.nan, index=x.index)
    return pd.qcut(x, 5, labels=False) + 1
```
Create top_next = 1 if the quintile is 5, 0 if it is 1–4, and missing if r_next is missing.
Create marketvalue = originalprice \times sharesoutstanding, oepsp, bmr, and their winsorized versions by quarter (oepspw, bmrw) with the function winsorize. Replace infinite values with missing values.
Create the data frame data with the rows that have non-missing top_next, oepspw and bmrw. How many observations does it have? What percentage has top_next = 1? Why is it so close to 20%?
Verify with an assert that the first r of every firm (its first quarter with a price) is missing.

# Write your code here

11 CHALLENGE 4: The logistic regression and its interpretation

Hand calculation first. In a text cell, fill this table using the formulas of the ladder (you may use a calculator), and then verify it with Python:

p odds log-odds

0.20 ? ?

? 0.5 ?

? ? 0.4
Run the logistic regression top_next ~ oepspw + bmrw with smf.logit and show the summary.
Interpret the sign and significance of each coefficient with your own words.
Calculate the odds ratio of oepspw for an increase of +0.10 and interpret it.
Calculate the odds and the probability of being a top-quintile stock for a firm with oepspw = 0 and bmrw = 0 (intercept). Compare with the 20% base rate.
Using predict, calculate the probability of being in the top quintile for two firms with bmrw = 0.5: firm A with oepspw = -0.20 (operating losses) and firm B with oepspw = +0.10.
Show the average marginal effects (get_margeff) and explain the difference between the AME of oepspw and its odds ratio.
Critical thinking: the coefficient of oepspw is negative: cheaper stocks (relative to earnings) are less likely to be among the top 20% next quarter. Does this contradict the value effect? Hint: think about which kind of stock has the most extreme returns, up and down. Run the same model for the event “bottom quintile next quarter” and compare the signs.

p	odds	log-odds
0.20	?	?
?	0.5	?
?	?	0.4

# Write your code here

12 CHALLENGE 5: Bug hunt

Each piece of code runs without errors, but the result is wrong. Find the bug, explain it, and fix it.

5.1 TTM net income:

df["netincome_ttm"] = df.groupby("firm")["netincomeq"].transform(lambda s: s.rolling(4, min_periods=1).sum())

5.2 Return on Assets with TTM net income, in the quarterly panel sorted by firm and quarter:

df["roa_ttm"] = df["netincome_ttm"] / df.groupby("firm")["totalassets"].shift(1)

5.3 Annual stock return (4 quarters) with the panel sorted by firm and quarter:

df["r_annual"] = df["adjprice"] / df["adjprice"].shift(4) - 1

5.4 Top quintile of next-quarter returns:

df["quint_next"] = pd.qcut(df["r_next"], 5, labels=False) + 1

Write your answers here

13 DataCamp

You will receive an invitation to DataCamp with free access during the course. Take the chapter “Logistic Regression for Defaults” (Chapter 2 of the course Credit Risk Modeling in Python). It applies the same model to loan defaults.

14 W1 submission

Submit your .ipynb file through Canvas and answer the Canvas Quiz W1 before the first class of next week.