Mathew K Analytics

Lesson 18 · Real-World Data Analytics

Stock Screener in Python: P/E, Dividends & Value Stocks (S&P 500) | Data Analytics #18

Build a fundamental stock screener in Python on real data for 300+ S&P 500 companies: market cap leaders, P/E and price-to-book distributions, dividend…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 18: Company Fundamentals from Real Financial Statements#

  • Video eighteen of the hundred-video real-world data analytics series.
  • Analyzing real fundamentals for over three hundred real S&P 500 companies, valuation, profitability, and dividends.
  • Let's get into it.

This lesson is for learners who have worked with price time series and now want to analyse companies themselves. The business question is: across the S&P 500, which companies look cheap or expensive, which ones are profitable, and who pays dividends?

The dataset, sp500_fundamentals.csv, is a cross-sectional snapshot: one row per company rather than one row per day. It covers 337 S&P 500 companies with 14 columns, including Symbol, Name, Sector, Price, valuation ratios (Price/Earnings, Price/Sales, Price/Book), Dividend Yield, Earnings/Share, Market Cap, EBITDA and the 52-week low and high, plus a link to each company's SEC filings.

In this lesson you will:

  • audit missing values and decide what they mean in business terms
  • rank, screen and group companies with nlargest, nsmallest, boolean filters and groupby
  • bucket companies by size with pd.cut and compare valuations fairly
  • build and check a simple value-stock screen

Prerequisites: you should be comfortable filtering DataFrames with boolean conditions and using groupby; earlier lessons in the series cover both.

Part 1: A Real Snapshot, Not a Price Chart#

Fundamental data describes the business behind a share price: earnings, sales, book value and dividends. It is a snapshot taken at one point in time, so each row is a company rather than a date. This step imports pandas, numpy and matplotlib and loads sp500_fundamentals.csv with pd.read_csv. The output (337, 14) shows 337 companies and 14 columns. Note that the S&P 500 has around 500 members, so this file covers only part of the index; keep that in mind before making claims about the whole market.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
companies = pd.read_csv('sp500_fundamentals.csv')
companies.shape
(337, 14)

Part 2: Real Columns Available#

Knowing exactly what each column holds prevents misreading the ratios later. The code calls columns.tolist() and then head(3), but Jupyter only shows the last expression, so the output is the first three rows: 3M, A. O. Smith and Abbott Laboratories. You can see that Dividend Yield is stored as a decimal (0.0204 means 2.04%), and Market Cap and EBITDA are in raw dollars shown in scientific notation, so 7.986760e+10 is about $79.9 billion. EBITDA means earnings before interest, taxes, depreciation and amortisation, a rough measure of operating cash profit.

companies.columns.tolist()
companies.head(3)
Symbol Name Sector Price Price/Earnings Dividend Yield Earnings/Share 52 Week Low 52 Week High Market Cap EBITDA Price/Sales Price/Book SEC Filings
0 MMM 3M Industrial Conglomerates 153.13 29.504818 0.0204 5.19 139.34 177.41 7.986760e+10 6.240000e+09 3.191640 24.477303 http://www.sec.gov/cgi-bin/browse-edgar?action...
1 AOS A. O. Smith Building Products 56.72 15.125334 0.0250 3.75 54.16 81.87 7.817640e+09 7.953000e+08 2.050851 4.162936 http://www.sec.gov/cgi-bin/browse-edgar?action...
2 ABT Abbott Laboratories Health Care Equipment 85.60 23.977590 0.0294 3.57 81.97 139.06 1.490992e+11 1.174400e+10 3.303479 2.863930 http://www.sec.gov/cgi-bin/browse-edgar?action...

Part 3: Real Missing Data Audit#

In financial data, a missing value often has a business meaning rather than being an error. The code first counts missing values per column with isna().sum(), sorted largest first, but that result is not displayed because it is not the last line. The displayed output comes from isna().mean() on Dividend Yield: the mean of True and False values gives the share of missing rows, here 22.3%. Most of these are likely companies that simply do not pay a dividend, so filling them with an average would be wrong. Run the first line on its own to see every column's count.

companies.isna().sum().sort_values(ascending=False)
round(companies['Dividend Yield'].isna().mean() * 100, 1)
np.float64(22.3)

Part 4: Real Clean Subset for Valuation Analysis#

Valuation comparisons need every key ratio present for each company, otherwise rankings and averages quietly use different sets of firms. The code lists the needed columns and uses dropna(subset=valuation_cols) to remove any row missing one of them, then .copy() so later column additions do not trigger pandas warnings about editing a slice. The output (282, 14) shows 282 companies remain, so 55 were dropped. Notice that Dividend Yield is not in the list, so non-payers are still kept.

valuation_cols = ['Symbol', 'Name', 'Sector', 'Price', 'Price/Earnings', 'Market Cap', 'EBITDA', 'Price/Sales', 'Price/Book']
clean = companies.dropna(subset=valuation_cols).copy()
clean.shape
(282, 14)

Part 5: Real Market Cap Leaders#

Market capitalisation, share price multiplied by shares outstanding, is the standard measure of company size. nlargest(10, 'Market Cap') returns the ten rows with the highest values, and the column list keeps only the symbol, name and size. The output is led by Alphabet Class A at about $4.61 trillion, Apple at about $4.58 trillion and Alphabet Class C at about $4.56 trillion, followed by Microsoft, Amazon, Broadcom, Meta, Micron, Eli Lilly and AMD. Alphabet appears twice because it has two share classes; watch for duplicates like this when counting companies.

top_market_cap = clean.nlargest(10, 'Market Cap')[['Symbol', 'Name', 'Market Cap']]
top_market_cap
Symbol Name Market Cap
19 GOOGL Alphabet Inc. (Class A) 4.607988e+12
39 AAPL Apple Inc. 4.583336e+12
20 GOOG Alphabet Inc. (Class C) 4.560616e+12
320 MSFT Microsoft 3.344578e+12
22 AMZN Amazon 2.911304e+12
72 AVGO Broadcom 2.115308e+12
314 META Meta Platforms 1.605578e+12
319 MU Micron Technology 1.095030e+12
290 LLY Lilly (Eli) 9.853742e+11
6 AMD Advanced Micro Devices 8.415529e+11

Part 6: Real Price-to-Earnings Distribution#

The price-to-earnings (P/E) ratio is the share price divided by earnings per share: how many dollars investors pay for one dollar of annual profit. describe() summarises its distribution across the 282 clean companies. The output shows a median of 26.02 but a mean of 34.96, with a maximum of 460.00 and a standard deviation of 44.84. When the mean sits well above the median, a few very high values are pulling it up. For skewed ratios like P/E, the median is usually the better summary of a typical company.

clean['Price/Earnings'].describe().round(2)
count    282.00
mean      34.96
std       44.84
min        2.44
25%       17.83
50%       26.02
75%       35.58
max      460.00
Name: Price/Earnings, dtype: float64

Part 7: Visualizing Real P/E Distribution#

A histogram shows the shape of a distribution better than summary numbers. plt.hist sorts P/E values into 40 bins, and .clip(upper=100) caps anything above 100 at 100 so a few extreme values do not stretch the axis and squash the rest of the chart. The chart is saved to pe_distribution.png and closed, so there is no inline output. When you open it, look for the main cluster in the teens to thirties and a spike at 100, which collects every company with a P/E of 100 or more.

plt.figure(figsize=(10, 5))
plt.hist(clean['Price/Earnings'].clip(upper=100), bins=40, color='steelblue', edgecolor='white')
plt.xlabel('Real Price-to-Earnings Ratio')
plt.ylabel('Real Number of Companies')
plt.title('Real P/E Ratio Distribution, S&P 500')
plt.tight_layout()
plt.savefig('pe_distribution.png', dpi=120)
plt.close()

Part 8: Real Cheapest Stocks by P/E#

Low P/E stocks are a classic starting point for finding cheap companies. The code first keeps only positive P/E values with clean['Price/Earnings'] > 0, since a negative P/E comes from losses and is not meaningful as cheapness, then nsmallest(10, ...) returns the ten lowest. The output shows Cigna at about 2.44, Charter Communications at 3.90 and Allstate at 4.56 at the top, with cable, insurance, utilities and packaged foods well represented. A very low P/E can reflect one-off gains or expected decline, so it is a prompt for research, not a buy signal.

cheapest = clean[clean['Price/Earnings'] > 0].nsmallest(10, 'Price/Earnings')[['Symbol', 'Name', 'Sector', 'Price/Earnings']]
cheapest
Symbol Name Sector Price/Earnings
106 CI Cigna Health Care Services 2.439539
101 CHTR Charter Communications Cable & Satellite 3.897457
18 ALL Allstate Property & Casualty Insurance 4.559513
118 CMCSA Comcast Cable & Satellite 4.876471
42 ACGL Arch Capital Group Property & Casualty Insurance 6.872307
164 EIX Edison International Electric Utilities 7.602174
7 AES AES Corporation Independent Power Producers & Energy Traders 7.640625
309 MKC McCormick & Company Packaged Foods & Meats 7.765574
47 T AT&T Integrated Telecommunication Services 8.157894
215 GIS General Mills Packaged Foods & Meats 8.266503

Part 9: Real Sector-Level Average Valuation#

Valuations differ by industry, so comparing a company with its own sector is fairer than comparing it with the whole market. groupby('Sector') gathers companies by sector, .mean() averages their P/E, and sort_values(ascending=False) puts the most expensive first. The output shows Real Estate Services at 244.27, Other Specialized REITs at 139.40 and Semiconductors at 132.79 at the top. Many sectors here contain only a handful of companies, so one extreme P/E can dominate the mean; checking the median with .median() is a sensible follow-up.

sector_pe = clean.groupby('Sector')['Price/Earnings'].mean().sort_values(ascending=False)
sector_pe.head(10).round(2)
Sector
Real Estate Services                    244.27
Other Specialized REITs                 139.40
Semiconductors                          132.79
Distributors                            118.94
Fertilizers & Agricultural Chemicals     74.38
Electronic Components                    64.92
Data Center REITs                        62.30
Health Care REITs                        59.84
Aerospace & Defense                      59.42
Interactive Home Entertainment           57.47
Name: Price/Earnings, dtype: float64

Part 10: Real Sector Company Counts#

Before trusting a sector average you need to know how many companies sit behind it. value_counts() counts rows per sector, sorted from most to least. The output shows Health Care Equipment with 11 companies, Electric Utilities with 10, and Aerospace & Defense and Financial Exchanges & Data with 8 each, with the rest of the top ten at 6 or 7. These are small groups, which confirms that the sector P/E averages from the previous step rest on very few companies and can swing with a single outlier.

sector_counts = clean['Sector'].value_counts()
sector_counts.head(10)
Sector
Health Care Equipment                           11
Electric Utilities                              10
Aerospace & Defense                              8
Financial Exchanges & Data                       8
Oil & Gas Exploration & Production               7
Semiconductors                                   7
Packaged Foods & Meats                           7
Hotels, Resorts & Cruise Lines                   6
Building Products                                6
Industrial Machinery & Supplies & Components     6
Name: count, dtype: int64

Part 11: Real Dividend-Paying Companies#

Dividends matter to income investors, and whether a company pays one says something about its maturity and cash flow. The code keeps rows where Dividend Yield is present using notna(), treating a missing yield as not paying, then divides by the total number of companies and converts to a percentage. The output, 77.7, means about 77.7% of the 337 companies pay a dividend. This mirrors the 22.3% missing share found earlier, since the two figures add up to 100%.

dividend_payers = companies[companies['Dividend Yield'].notna()]
pct_paying = round(len(dividend_payers) / len(companies) * 100, 1)
pct_paying
77.7

Part 12: Real Highest Dividend Yields#

Dividend yield is the annual dividend divided by the share price, so it shows the cash return from holding a share. nlargest(10, 'Dividend Yield') lists the highest yielders. The output is led by Conagra Brands at 0.1054 (10.54%), Alexandria Real Estate Equities at 8.21% and Campbell Soup at 7.39%, with packaged food companies taking four of the ten places. A very high yield often appears because the share price has fallen sharply, and it can signal that investors expect the dividend to be cut.

top_yield = dividend_payers.nlargest(10, 'Dividend Yield')[['Symbol', 'Name', 'Sector', 'Dividend Yield']]
top_yield
Symbol Name Sector Dividend Yield
119 CAG Conagra Brands Packaged Foods & Meats 0.1054
14 ARE Alexandria Real Estate Equities Office REITs 0.0821
83 CPB Campbell Soup Company Packaged Foods & Meats 0.0739
215 GIS General Mills Packaged Foods & Meats 0.0722
23 AMCR Amcor Paper & Plastic Packaging Products & Materials 0.0670
281 KHC Kraft Heinz Packaged Foods & Meats 0.0666
227 DOC Healthpeak Properties Health Care REITs 0.0637
298 LYB LyondellBasell Specialty Chemicals 0.0618
21 MO Altria Tobacco 0.0609
254 IP International Paper Paper & Plastic Packaging Products & Materials 0.0553

Part 13: Real Earnings Per Share Distribution#

Earnings per share (EPS) is profit divided by the number of shares, and a negative value means the company made a loss. The code drops rows without EPS using dropna(subset=['Earnings/Share']), compares each value with zero to get True or False, and .sum() counts the Trues. The output shows 24 companies reported negative earnings. Loss-making companies need different valuation tools, because ratios like P/E become negative or meaningless when earnings are below zero.

eps_clean = companies.dropna(subset=['Earnings/Share'])
negative_eps_count = (eps_clean['Earnings/Share'] < 0).sum()
negative_eps_count
np.int64(24)

Part 14: Real Companies with Negative Earnings#

Seeing which companies made losses helps you judge whether these are temporary setbacks or deeper problems. The code filters to negative EPS, keeps a few useful columns, and sorts with sort_values('Earnings/Share') so the largest losses per share come first. The output shows FMC Corporation at -19.62, Centene at -13.05 and J.M. Smucker at -11.79, followed by Molson Coors, Celanese and Moderna. Several are large, established firms, which suggests one-off charges such as write-downs rather than failing businesses, something you would confirm in the SEC filings.

unprofitable = eps_clean[eps_clean['Earnings/Share'] < 0][['Symbol', 'Name', 'Sector', 'Earnings/Share']]
unprofitable.sort_values('Earnings/Share').head(10)
Symbol Name Sector Earnings/Share
199 FMC FMC Corporation Fertilizers & Agricultural Chemicals -19.62
96 CNC Centene Corporation Managed Health Care -13.05
263 SJM J.M. Smucker Company (The) Packaged Foods & Meats -11.79
325 TAP Molson Coors Beverage Company Brewers -10.55
94 CE Celanese Specialty Chemicals -9.86
322 MRNA Moderna Biotechnology -8.14
14 ARE Alexandria Real Estate Equities Office REITs -6.27
254 IP International Paper Paper & Plastic Packaging Products & Materials -5.19
281 KHC Kraft Heinz Packaged Foods & Meats -4.86
156 DOW Dow Inc. Commodity Chemicals -4.00

Part 15: Real Price-to-Book Ratio#

Price-to-book (P/B) compares the share price with the accounting value of the company's net assets per share. A low P/B can suggest a company trades close to the value of what it owns. After dropping missing values, describe() shows 324 companies with a median of 2.94, but a minimum of -569.50 and a maximum of 497.96. The mean of 2.68 sits below the median because large negative values pull it down. Those extremes come from tiny or negative book values, which make the ratio unstable.

pb_clean = companies.dropna(subset=['Price/Book'])
pb_clean['Price/Book'].describe().round(2)
count    324.00
mean       2.68
std       50.78
min     -569.50
25%        1.62
50%        2.94
75%        6.69
max      497.96
Name: Price/Book, dtype: float64

Part 16: Real Negative Book Value Companies#

A negative P/B means the company has negative book value: its liabilities exceed its recorded assets. That can come from heavy share buybacks or past losses, not necessarily distress. The code filters Price/Book < 0 and counts the rows with len(). The output shows 24 companies with negative book value. For these firms P/B is meaningless as a cheapness measure, so any value screen using P/B should exclude or treat them separately, otherwise they look like the cheapest stocks in the market.

negative_book = pb_clean[pb_clean['Price/Book'] < 0][['Symbol', 'Name', 'Price/Book']]
len(negative_book)
24

Part 17: Real Correlation Among Valuation Metrics#

Checking how valuation ratios relate tells you whether they measure the same thing or add different information. .corr() on the four columns produces a correlation matrix, where 1 means perfect positive and -1 perfect negative relationship. The output shows weak links throughout: P/E and Price/Sales at 0.288, Price/Sales and Dividend Yield at -0.286, and P/E and P/B at just 0.025. The negative signs with yield fit intuition, as expensive stocks tend to pay lower yields. Because pandas correlation is sensitive to the extreme values seen earlier, these numbers are rough.

metric_cols = ['Price/Earnings', 'Price/Sales', 'Price/Book', 'Dividend Yield']
metric_corr = companies[metric_cols].corr()
metric_corr.round(3)
Price/Earnings Price/Sales Price/Book Dividend Yield
Price/Earnings 1.000 0.288 0.025 -0.089
Price/Sales 0.288 1.000 0.087 -0.286
Price/Book 0.025 0.087 1.000 -0.111
Dividend Yield -0.089 -0.286 -0.111 1.000

Part 18: Real Value Stock Screen#

A screen combines several filters to narrow hundreds of companies to a short list worth researching. This one looks for value stocks: P/E between 0 and 15, and P/B below 3. Each condition is wrapped in brackets and joined with &, which pandas needs for element-wise AND. The output shows 38 companies pass. A common mistake is using Python's and instead of &, which raises an error on Series, or forgetting the brackets, which breaks operator precedence. Note that this filter still allows negative P/B values through.

value_screen = clean[(clean['Price/Earnings'] < 15) & (clean['Price/Earnings'] > 0) & (clean['Price/Book'] < 3)]
len(value_screen)
38

Part 19: Real Value Screen Results#

A screen is only useful once you look at who passed. The code selects the key columns, sorts by Price/Earnings and shows the first ten. The output overlaps heavily with the cheapest P/E list: Cigna (P/E 2.44, P/B 1.74), Charter Communications (3.90, 1.08) and Allstate (4.56, 1.80) lead, and Fidelity National Information Services appears at 8.33. General Mills dropped out compared with the cheapest P/E list, presumably because its P/B is 3 or above. Read each result as a lead to investigate, not a recommendation.

value_screen[['Symbol', 'Name', 'Sector', 'Price/Earnings', 'Price/Book']].sort_values('Price/Earnings').head(10)
Symbol Name Sector Price/Earnings Price/Book
106 CI Cigna Health Care Services 2.439539 1.738923
101 CHTR Charter Communications Cable & Satellite 3.897457 1.081229
18 ALL Allstate Property & Casualty Insurance 4.559513 1.795960
118 CMCSA Comcast Cable & Satellite 4.876471 1.007739
42 ACGL Arch Capital Group Property & Casualty Insurance 6.872307 1.344388
164 EIX Edison International Electric Utilities 7.602174 1.561335
7 AES AES Corporation Independent Power Producers & Energy Traders 7.640625 2.366893
309 MKC McCormick & Company Packaged Foods & Meats 7.765574 1.823606
47 T AT&T Integrated Telecommunication Services 8.157894 1.579014
194 FIS Fidelity National Information Services Transaction & Payment Processing Services 8.331396 1.391127

Part 20: Real Market Cap Buckets#

Grouping companies by size lets you compare like with like. pd.cut assigns each market cap to a range defined by cap_bins, with edges at $10 billion, $50 billion and $200 billion, and np.inf as an open top end; labels gives each range a readable name. value_counts() shows 142 mid-caps, 89 large caps, 32 mega caps and 19 small caps. Few small caps appear because S&P 500 membership requires a large company, so the categories reflect this universe, not the whole market.

cap_bins = [0, 10e9, 50e9, 200e9, np.inf]
cap_labels = ['Small (<$10B)', 'Mid ($10-50B)', 'Large ($50-200B)', 'Mega (>$200B)']
clean['CapTier'] = pd.cut(clean['Market Cap'], bins=cap_bins, labels=cap_labels)
clean['CapTier'].value_counts()
CapTier
Mid ($10-50B)       142
Large ($50-200B)     89
Mega (>$200B)        32
Small (<$10B)        19
Name: count, dtype: int64

Part 21: Real Valuation by Market Cap Tier#

Investors often pay more for very large, dominant companies, and this step tests that idea. groupby('CapTier', observed=True) groups by size tier, where observed=True tells pandas to show only categories that actually appear, and .mean() averages the P/E. The output shows mega caps at 44.72, large caps at 37.30, small caps at 34.39 and mid caps at 31.37. The biggest companies carry the highest average valuations here, though with only 32 mega caps a few high P/E names can drive that result.

tier_pe = clean.groupby('CapTier', observed=True)['Price/Earnings'].mean()
tier_pe.round(2)
CapTier
Small (<$10B)       34.39
Mid ($10-50B)       31.37
Large ($50-200B)    37.30
Mega (>$200B)       44.72
Name: Price/Earnings, dtype: float64

Part 22: Real EBITDA Margin Proxy#

EBITDA relative to market cap shows how much operating cash profit a company generates for each dollar of its market value, a little like an earnings yield. Despite the heading, it is not a true margin, which would divide by revenue. The code divides EBITDA by Market Cap and summarises with describe(). The output shows a median of 0.0853, about 8.5 cents of EBITDA per dollar of market value, with a minimum of -0.0179 and a maximum of 1.0806. Higher values can mean a cheaper company, or one the market expects to shrink.

ebitda_clean = clean.dropna(subset=['EBITDA'])
ebitda_to_cap = (ebitda_clean['EBITDA'] / ebitda_clean['Market Cap'])
ebitda_to_cap.describe().round(4)
count    282.0000
mean       0.1028
std        0.0865
min       -0.0179
25%        0.0562
50%        0.0853
75%        0.1326
max        1.0806
dtype: float64

Part 23: Real Highest Cash-Generation Efficiency#

Ranking by this ratio highlights companies that generate a lot of operating profit relative to their price. assign adds the ratio as a new column called EBITDA_to_Cap, and nlargest(10, ...) lists the top ten. The output shows Charter Communications far ahead at 1.08, followed by APA Corporation at 0.40 and Comcast at 0.40. A ratio above 1 means EBITDA exceeds the entire market value, which is unusual and worth checking against the filings. Many of these names also appeared in the cheap P/E list, which is a reassuring consistency.

ebitda_clean = ebitda_clean.assign(EBITDA_to_Cap=ebitda_to_cap)
ebitda_clean.nlargest(10, 'EBITDA_to_Cap')[['Symbol', 'Name', 'EBITDA_to_Cap']]
Symbol Name EBITDA_to_Cap
101 CHTR Charter Communications 1.080556
38 APA APA Corporation 0.404677
118 CMCSA Comcast 0.398148
7 AES AES Corporation 0.358917
164 EIX Edison International 0.318551
18 ALL Allstate 0.279726
83 CPB Campbell Soup Company 0.277572
331 MOS Mosaic Company (The) 0.262370
47 T AT&T 0.257732
216 GM General Motors 0.243689

Part 24: Visualizing Real Sector Valuation Spread#

A box plot shows the middle, the spread and the outliers of each group at a glance. The code picks the six sectors with the most companies, builds a list of P/E values for each with .clip(upper=80) to tame extremes, and passes them to plt.boxplot with tick_labels naming each box. plt.xticks(rotation=30, ha='right') angles the long sector names so they fit. The chart is saved to sector_pe_boxplot.png without inline output. Look at how wide each box is and whether any sector's median clearly sits above the others.

top_sectors_by_count = clean['Sector'].value_counts().head(6).index
plt.figure(figsize=(11, 6))
plot_data = [clean.loc[clean['Sector'] == s, 'Price/Earnings'].clip(upper=80) for s in top_sectors_by_count]
plt.boxplot(plot_data, tick_labels=top_sectors_by_count)
plt.xticks(rotation=30, ha='right')
plt.ylabel('Real Price-to-Earnings Ratio')
plt.title('Real P/E Spread Across Top Sectors')
plt.tight_layout()
plt.savefig('sector_pe_boxplot.png', dpi=120)
plt.close()

Part 25: Real 52-Week Range Analysis#

The 52-week range shows where a stock trades relative to its recent history, which helps spot momentum and possible bargains or traps. The code drops rows missing the range or price, then uses assign to add PctFromHigh, the percentage gap between the current price and the 52-week high. The output covers 324 companies: the median stock sits 13.8% below its high, the mean is 17.7% below, and the furthest is 69.5% below. A maximum of -0.0 means at least one stock was trading at its 52-week high.

range_clean = companies.dropna(subset=['52 Week Low', '52 Week High', 'Price'])
range_clean = range_clean.assign(PctFromHigh=(range_clean['Price'] - range_clean['52 Week High']) / range_clean['52 Week High'] * 100)
range_clean['PctFromHigh'].describe().round(1)
count    324.0
mean     -17.7
std       14.3
min      -69.5
25%      -25.7
50%      -13.8
75%       -7.3
max       -0.0
Name: PctFromHigh, dtype: float64

Part 26: Real Stocks Near Their 52-Week Low#

Stocks far below their 52-week high have fallen sharply, which can mean bad news or an overreaction. nsmallest(10, 'PctFromHigh') lists the ten largest falls. Despite the heading, this measures distance from the high, not closeness to the low. The output shows FMC Corporation at -69.50% (from $44.78 to $13.66), CoStar Group at -66.95% and Charter Communications at -65.89%, with Gartner, Lululemon and Intuit next. FMC and Charter also appear in earlier lists, a reminder that cheap-looking ratios often follow a falling price.

near_low = range_clean.nsmallest(10, 'PctFromHigh')[['Symbol', 'Name', 'Price', '52 Week High', 'PctFromHigh']]
near_low.round(2)
Symbol Name Price 52 Week High PctFromHigh
199 FMC FMC Corporation 13.66 44.78 -69.50
129 CSGP CoStar Group 32.20 97.43 -66.95
101 CHTR Charter Communications 144.05 422.29 -65.89
208 IT Gartner 162.20 434.12 -62.64
297 LULU Lululemon Athletica 131.18 340.25 -61.45
256 INTU Intuit 331.53 813.70 -59.26
250 PODD Insulet Corporation 144.94 354.88 -59.16
70 BSX Boston Scientific 48.31 109.50 -55.88
172 EPAM EPAM Systems 102.46 222.53 -53.96
221 GDDY GoDaddy 85.83 183.34 -53.19

Part 27: Saving the Real Cleaned Fundamentals Table#

Saving the cleaned table means the next analysis can start from consistent, filtered data. to_csv(index=False) writes the 282 cleaned companies, including the new CapTier column, without the index. The code reads the file back and compares row counts, and the output True confirms nothing was lost. Note that CapTier comes back as plain text when reloaded, so you would need to convert it to a category again if order matters.

clean.to_csv('sp500_fundamentals_clean.csv', index=False)
reloaded = pd.read_csv('sp500_fundamentals_clean.csv')
reloaded.shape[0] == clean.shape[0]
True

Part 28: Real Sanity Check on Value Screen#

A sanity check confirms your filter did what you meant. The code tests that every company in value_screen has a P/E below 15 and a P/B below 3, using .all() on each boolean Series and combining them with Python's and, which works here because each side is a single True or False. The output np.True_ confirms the screen is correct. Checks like this become more valuable as screens grow more complex and filters are edited over time.

(value_screen['Price/Earnings'] < 15).all() and (value_screen['Price/Book'] < 3).all()
np.True_

Part 29: Real Sector Diversity Check#

A value screen dominated by one industry is really a sector bet, so diversity matters. nunique() counts the number of distinct sectors among the companies that passed. The output, 23, means the 38 value stocks are spread across 23 different sectors. That suggests the screen is not just picking one cheap industry. To see whether any sector still dominates, run value_counts() on the same column.

value_screen_sectors = value_screen['Sector'].nunique()
value_screen_sectors
23

Part 30: Real Recap Print#

A one-sentence recap turns the analysis into a message a manager can read in seconds. The f-string combines the company count, dividend share, number of loss-makers, screen size and sector count calculated earlier. The printed output reads: across 337 S&P 500 companies, 77.7% pay a dividend, 24 reported negative earnings, and 38 passed a simple value screen across 23 different sectors. Note the mix of bases: the dividend figure uses all 337 companies, while the screen uses the 282 clean ones.

print(f'Across {len(companies)} real S&P 500 companies, {pct_paying}% pay a dividend, {negative_eps_count} reported negative earnings, and {len(value_screen)} passed a simple real value screen across {value_screen_sectors} different sectors.')
Across 337 real S&P 500 companies, 77.7% pay a dividend, 24 reported negative earnings, and 38 passed a simple real value screen across 23 different sectors.

Wrap-Up: What You Learned#

  • Fundamental data is a real cross-sectional snapshot, one row per company, a genuinely different shape than the time-series data used throughout the rest of this domain.
  • Real missing data in financial statements is not random noise, it often reflects a genuine business reality, like a company simply not paying a dividend.
  • A single real ratio like P/E never tells the whole story, pairing it with real price-to-book or real sector context gives a fuller picture.
  • Grouping real companies by sector or market cap tier before comparing valuation is what makes the real comparison actually fair.
  • A real simple value screen, combining multiple real filters, is the same basic logic professional analysts use, just built here from first principles.
  • Next video: real Value-at-Risk and drawdown analysis, returning to time series data to quantify tail risk more rigorously.

Practice on your own

  1. Recalculate the sector P/E ranking with .median() instead of .mean(), keep only sectors with at least five companies, and compare the new top ten with the original.
  2. Tighten the value screen by adding Price/Book > 0 and Dividend Yield > 0.02, then count how many companies remain and how many sectors they cover.
  3. Group the clean companies by CapTier and calculate the median dividend yield and the share of dividend payers in each tier.

The next video returns to time-series data to measure Value-at-Risk and drawdowns more rigorously, quantifying tail risk.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. In the S&P 500 fundamentals data, many companies have a missing Dividend Yield. What does the lesson suggest this often means?

  2. What is the lesson's simple value screen?

  3. Why does the lesson filter Price/Earnings > 0 before using nsmallest to find the cheapest stocks?

  4. What does pd.cut(clean['Market Cap'], bins=cap_bins, labels=cap_labels) do?

  5. Why group by Sector before comparing P/E ratios?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Create a small fundamentals DataFrame (Symbol, Sector, PE) for 6 companies with PE values [12, 25, -5, 8, 40, 14]. Print the companies with positive PE below 15.

    💡 Hint

    Combine two boolean conditions with &.

    ✅ Show solution
    import pandas as pd
    
    df = pd.DataFrame({
        'Symbol': list('ABCDEF'),
        'Sector': ['Fin', 'Tech', 'Tech', 'Fin', 'Tech', 'Health'],
        'PE': [12, 25, -5, 8, 40, 14],
    })
    print(df[(df['PE'] > 0) & (df['PE'] < 15)])
    
  2. Make a fundamentals table with Symbol, Sector, MarketCap (in billions), and DividendYield (some NaN). Compute the percentage of companies paying a dividend, and the average market cap by sector sorted high to low.

    💡 Hint

    Use notna().mean() and groupby().mean().

    ✅ Show solution
    import numpy as np
    import pandas as pd
    
    df = pd.DataFrame({
        'Symbol': list('ABCDEFGH'),
        'Sector': ['Tech', 'Tech', 'Fin', 'Fin', 'Health', 'Health', 'Energy', 'Energy'],
        'MarketCap': [900, 300, 150, 60, 200, 20, 120, 40],
        'DividendYield': [np.nan, 0.5, 2.5, 3.0, np.nan, 1.8, 4.0, np.nan],
    })
    pct = df['DividendYield'].notna().mean() * 100
    print('Dividend payers %:', round(pct, 1))
    print(df.groupby('Sector')['MarketCap'].mean().sort_values(ascending=False))
    
  3. Generate 200 synthetic companies (seed 10): Sector random from ['Tech','Fin','Health','Energy'], MarketCap lognormal (mean 3, sigma 1, in billions), PE normal (mean 18, std 10), PB normal (mean 3, std 1.5). Apply a value screen (0 < PE < 15 and PB < 3), bucket MarketCap into Small (<10), Mid (10-50), Large (>50) with pd.cut, and print the count per tier among screened stocks and the average PE per tier.

    💡 Hint

    Use np.inf as the last bin edge and groupby with observed=True.

    ✅ Show solution
    import numpy as np
    import pandas as pd
    
    rng = np.random.default_rng(10)
    n = 200
    df = pd.DataFrame({
        'Sector': rng.choice(['Tech', 'Fin', 'Health', 'Energy'], n),
        'MarketCap': rng.lognormal(3, 1, n),
        'PE': rng.normal(18, 10, n),
        'PB': rng.normal(3, 1.5, n),
    })
    screen = df[(df['PE'] > 0) & (df['PE'] < 15) & (df['PB'] < 3)].copy()
    screen['Tier'] = pd.cut(screen['MarketCap'], bins=[0, 10, 50, np.inf],
                            labels=['Small', 'Mid', 'Large'])
    print('Screened companies:', len(screen))
    print(screen['Tier'].value_counts())
    print(screen.groupby('Tier', observed=True)['PE'].mean().round(2))
    

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.