Merging Price & Fundamentals Data

Merging Price & Fundamentals Data

Scene: your first week on a systematic value desk. The PM stops at your screen: "Run me the value screen - most earnings growth per unit of P/E, top three names before the close." One catch: prices arrive from the market-data feed, fundamentals from the quarterly filings database. Two tables, one shared column - the ticker. Welcome to the most-used operation in financial data work: the merge.

Quant fundamental analysts combine daily price data with quarterly fundamentals (P/E, EPS growth, revenue) exactly this way. This join is at the heart of systematic value investing.

📜 The Fama-French Factor Model - Why Fundamentals Matter

1934 - Benjamin Graham & David Dodd (Security Analysis): The founding text of fundamental investing. Graham - Warren Buffett's mentor - argued that stocks had an "intrinsic value" based on earnings, assets, and dividends, and that prices regularly diverged from this value. The P/E ratio (price-to-earnings) as an investment signal originated here.

1964 - CAPM predicted that the only thing that explained returns was market beta - sensitivity to the overall market. If CAPM was right, P/E ratios should have no predictive power once you controlled for beta.

1992 - Eugene Fama & Kenneth French (Fama won the Nobel in 2013) published "The Cross-Section of Expected Stock Returns" in the Journal of Finance. Using 30 years of data, they showed empirically that market beta had almost no explanatory power once you controlled for two other characteristics:

1. Size: small-cap stocks systematically outperformed large-caps
2. Value (book-to-market): stocks cheap by fundamentals outperformed growth stocks

1993 - the companion paper "Common Risk Factors in the Returns on Stocks and Bonds" (Journal of Financial Economics) packaged those two effects into tradable factor portfolios - SMB (small minus big) and HML (high minus low book-to-market) - giving the three-factor model that demolished CAPM's monopoly. The "value premium" became one of the most replicated findings in empirical finance.

The value score you are computing (eps_growth / pe_ratio) is a simplified version of the signals that Fama-French documented. Today, every major quant fund - AQR, Dimensional Fund Advisors (where French heads investment policy), Two Sigma - runs multi-factor models that include fundamental data merged with price data exactly as you are about to do.

The pd.merge() call in your code is the quantitative implementation of 90 years of financial theory.

🧠 The mental model: VLOOKUP that grew up

If you know Excel, pd.merge() is VLOOKUP done properly: for every row of the left table, pandas finds the row of the right table carrying the same key (on='ticker') and glues its columns on. If you know SQL, it is literally a JOIN.

merged = pd.merge(
    left=price_df,
    right=fundamentals_df,
    on='ticker',        # join key
    how='left'          # keep all rows from left
)

The matches are the easy part. The whole game is the orphans - rows with no partner on the other side. The how= argument is your orphan policy:

The join type is a portfolio decision

Look at the two tables in the starter: the price feed covers 5 tickers, but the fundamentals database holds 6 - Goldman Sachs (GS) files earnings, yet our feed has no price for it. Choose how='outer' and the merge invents a GS row with a NaN close; with its tempting 12.1 P/E, GS then muscles straight into your top-3 value screen. A screen that recommends a stock you cannot price is not a screen - it is a bug wearing a suit.

The value score

A simple quant value signal - earnings growth per unit of price multiple:

value_score = eps_growth / pe_ratio

High score = fast-growing earnings at a low multiple, the classic Graham-style bargain (a close cousin of the inverse PEG ratio).

Your Task

The starter merges with how='outer' - the wrong orphan policy for a trading screen.

Predict before you run: with 5 price rows, 6 fundamentals rows and 5 shared tickers, how many rows will merged have, and which ticker will be sitting there with a NaN close? Run the starter as-is to check your prediction. Then fix the join type so only priced stocks are ranked - and predict who inherits GS's stolen podium spot (look for the bank trading at 11.2× earnings).