Learn about clean historical data sourcing

What makes historical data "dirty"?

Historical stock data becomes corrupted through four main channels:

1. Survivorship Bias

Why this happens:

Free data providers often maintain only currently-listed tickers
Each year ~5-8% of stocks delist (bankruptcy, acquisitions, compliance failures)
A backtest from 2000-2020 missing dotcom busts or 2008 failures will never see those losses

Mathematical impact:

Let's derive the bias magnitude. Suppose:

True population: $N_0$ stocks at time $t=0$
Survival rate: $s$ per year
Your dataset has only survivors: $N_s = N_0 \cdot s^T$ after $T$ years

If average return is $\mu$ for survivors and $\mu_{dead} = -0.8$ (80% loss) for delistings:

$\text{True return} = s \cdot \mu + (1-s) \cdot \mu_{dead}$

$\text{Biased return} = \mu$

$\text{Bias} = \mu - [s \cdot \mu + (1-s) \cdot \mu_{dead}] = (1-s)(\mu - \mu_{dead})$

With $s=0.94$ (6% annual delisting), $\mu=0.12$ , $\mu_{dead}=-0.8$ :

$\text{Bias} = 0.06 \times (0.12 - (-0.8)) = 0.06 \times 0.92 = 5.5\% \text{ per year}$

Your backtest is inflated by 5.5 percentage points annually.

2. Look-Ahead Bias

How it sneaks in:

Price data is usually provided adjusted for splits/dividends. On2020-01-15, you see:

Adjusted price: $100
This reflects a 2-for-1 split on 2020-06-01

The trap: If your strategy on 2020-01-15 uses " $100" in calculations expecting it to mean "share price," you're implicitly using post-split information. The **unadjusted** price was$ 200.

Correct usage: Always use adjusted prices for returns, unadjusted for absolute price levels (like "buy stocks under $10").

Formula Adjustment Factor Derivation

When a stock splits $n:1$ or pays dividend $D$ :

$\text{Adjustment Factor} = \frac{P_{\text{close}} - D}{P_{\text{close}}}$

For split: $\text{Factor} = \frac{1}{n}$ Adjusted price on date $t$ : $P_{\text{adj}}(t) = P_{\text{raw}}(t) \times \prod_{i: t_i > t} \text{Factor}_i$

This multiplies forward through all future corporate actions. It's correct for return calculations but creates look-ahead bias if used for price-level rules.

Why this matters: Your backtest on 2020-01-15 "sees" $100 only because of a June split. The real market saw$ 200. Any strategy checking "is price< $150?" gets the wrong answer.

| Action | Effect | Adjustment | |-----|------------| | Split $n:1$ | Shares multiply by $n$ | Divide all prior prices by $n$ | | Dividend $D$ | Cash paid out | Subtract $D$ from prior prices | | Merger | Ticker changes | Link histories with correct ratio | | Spinoff | New entity created | Split parent value proportionally |

Derivation of dividend adjustment:

Day before dividend: stock worth $P$ , includes $D$ cash value. $P = P_{\text{business}} + D$

Day after (ex-dividend): cash is gone, business remains: $P_{\text{ex}} = P_{\text{business}} P - D$

The price "drops" by $D$ , but shareholder value is preserved (stock + cash = constant). Adjusted series must show no drop:

$P_{\text{adj, before}} = P_{\text{raw, before}} \times \frac{P_{\text{ex}}}{P}= P_{\text{raw, before}} \times \frac{P-D}{P}$

4. Point-in-Time Data

Why financial data changes retroactively:

Restatements: Company discovers accounting error, revises 3 years of earnings
Reclassifications: Sector assignments change (is Tesla automotive or tech?)
Filing delays: 10-K due 90 days after fiscal year-end, but many file late

What you need:

For a backtest on 2015-03-15:

Use financials as filed by2015-03-15, not the "final restated" version from 2018
Use sector classifications as of 2015-03-15
If 10-K wasn't filed yet, you don't have that data

Without this: You're trading with future knowledge (the restatement), inflating performance.

Common mistake "Cleaned data means filled gaps"

Wrong intuition: "I'll just forward-fill missing prices or use the next available value."

Why it feels right: Gaps in data are annoying; filling them makes your code cleaner and prevents crashes.

The trap:

A missing price might mean the stock was halted (pending news) or illiquid
Forward-filling shows you "holding" at yesterday's price, but you couldn't have traded there
Your backtest assumes you exited at a price that didn't exist

The fix:

Mark missing data explicitly (NaN)
Skip trading signals on days without data
Or use last-available price but flag the position as illiquid (apply wider slipage)

Real example: During 2008 crisis, many corporate bonds had no quotes for days. Forward-filling shows your bond portfolio stable at old prices while reality had30% crashes. Your backtest would show you exiting at pre-crash prices = pure fiction.

Clean data requirements checklist

Formula Data Quality Standards

For production-grade backtesting, your data must satisfy:

Survivorship-free: $\mathbb{P}(\text{ticker in dataset}) = \mathbb{P}(\text{existed at } t) \text{ for all } t$ Every ticker that traded on date $t$ appears in your $t$ data, including delistings.
Corporate-action adjusted: All prices in return series satisfy: $\frac{P_{\text{adj}}(t+1)}{P_{\text{adj}}(t)} - 1 = \text{true return including dividends}$
Point-in-time aligned: For fundamental data: $\text{Data}(t) = \{\text{information filed/public by } t\}$ No future information leakage.
Timestamp integrity: Prices reflect when you could have traded:
- Close prices: available after market close + processing (use next day open)
- Intraday: actual tick timestamps, not rounded/bucketed post-hoc

Data source tiers

Tier 1: Professional (Expensive but Clean)

Bloomberg Terminal: $24,000/year, point-in-time fundamentals, delisting returns
Refinitiv (Reuters): Similar pricing, global coverage
FactSet: Institutional-grade, strong corporate actions

Pros: Survivorship-free, accurate splits/dividends, point-in-time toggle Cons: Cost prohibitive for individuals

Tier 2: Specialized Backtest Vendors

Norgate Data: $300-800/year, US equities with delisted stocks, accurate adjustments
CSI Data: Similar, includes futures
Sharadar (Quandl/Nasdaq Data Link): $50-200/month, good fundamentals, delisting coverage

Pros: Built for backtesting, documented methodology, affordable Cons: Limited asset classes, US-focused mostly

Tier 3: Free/API (Requires Heavy Cleaning)

Yahoo Finance: Free, survivorship bias, spoty delisting data, occasional errors
Alpha Vantage: Free tier limited, better than Yahoo
IEX Cloud: Cheap API, current data focus

Pros: Accessible, good for learning Cons: Manual survivorship correction needed, verify every corporate action, no point-in-time fundamentals

Worked example Cleaning Yahoo Finance data

Task: Build a survivorship-bias-free dataset from free sources.

Step 1 – Get delisted tickers:

# Yahoo doesn't list delistings; use external source
# Download historical S&P 500 changes from Wikipedia
# Or scrape NASDAQ delisting lists

Why this step: Free data has only survivors; you must manually add the dead.

Step 2 – Download and verify splits:

import yfinance as yf
data = yf.download('AAPL', start='2010-01-01', actions=True)
# Check 'Stock Splits' column
# Verify against Apple investor relations website

Why this step: Yahoo's split data has errors (~2% of tickers). Cross-check major names.

Step 3 – Calculate custom adjustments:

# If Yahoo's adjusted close seems wrong, recompute:
adj_factor = 1.0
for date in reversed(dates):
    if split_on[date]:
        adj_factor /= split_ratio[date]
    if dividend_on[date]:
        adj_factor *= (price[date] - dividend[date]) / price[date]
    adjusted_prices[date] = raw_prices[date] * adj_factor

Why this step: You control the adjustment logic, catch errors.

Result: Usable but imperfect data. Expect ~1-2% residual errors vs. professional sources.

Data validation techniques

Formula Statistical Checks

Before trusting data, run these tests:

1. Return sanity: $\mathbb{P}(|r_t| > 0.5) < 0.001$ Daily returns > 50% are rare (splits/errors). Flag and investigate.

2. Volume consistency: $\text{Volume}(t) > 0 \text{ when } P(t) \neq P(t-1)$ Price changes with zero volume = data error.

3. Duplicate timestamps: $\forall t: |\{i : \text{timestamp}_i = t\}| = 1$ Each timestamp appears once. Duplicates = feed glitch.

4. OHLC integrity: $L_t \leq O_t, C_t \leq H_t \quad \text{and} \quad H_t \geq L_t$ Low ≤ Open, Close ≤ High. Violations = corrupted data.

Worked example Automated data quality pipeline

def validate_ohlc(df):
    """Check OHLC integrity."""
    violations = (
        (df['Low'] > df['Open']) |
        (df['Low'] > df['Close']) |
        (df['High'] < df['Open']) |
        (df['High'] < df['Close'])
    )
    return df[violations]  # Returns bad rows
def detect_split_errors(df, threshold=0.4):
    """Find unadjusted splits (40%+ overnight moves)."""
    returns = df['Close'].pct_change()
    suspect = returns[abs(returns) > threshold]
    return suspect
# Run on every ticker
bad_ohlc = validate_ohlc(price_data)
split_issues = detect_split_errors(price_data)

Why this step: Catches 90% of data errors before they ruin your backtest. The10-minute validation saves weeks of debugging false signals.

Recall Explain to a 12-year-old

Imagine you're testing whether your new skateboard trick works by watching videos of only the kids who succeeded and made it to the X Games. All the videos of kids who tried and failed aren't in your collection because they gave up skating. If you learn the trick based only on success videos, you'll think it's way easier than it really is—you never saw the broken arms and quit attempts.

Clean historical data is like getting videos of everyone who tried the trick, including the failures. In stock backtesting:

Dirty data = only companies that survived (the X Games winners)
Clean data = all companies including the bankruptcies (everyone who tried)

Also, some videos are edited after the fact—maybe they cut out the part where the kid fell first. That's look-ahead bias: the video shows info that wasn't obvious when it was filmed. Clean data means seeing everything exactly as it happened, with no edited-in future knowledge.

Connections

6.2.01-Define-backtesting-vs-paper-trading – Why clean data matters more in backtesting
6.2.03-Understand-transaction-cost-modeling – Clean data must include realistic fills
6.1.02-Understand-compounding-vs-averagereturns – Survivorship bias inflates compound returns
5.3.01-Learn-about-the-efficient-market-hypothesis – Point-in-time data tests true EMH
6.4.01-Identify-overfitting-in-strategy-parameters – Dirty data creates false parameter optima

#flashcards/stock-market

What is survivorship bias in historical data? :: When your dataset contains only securities that survived to the present, excluding delisted/bankrupt companies, creating an upward-biased performance record because failures are invisible.

Why does survivorship bias inflate backtest returns?

Because it removes all the losing trades from bankruptcies/delistings (typically -80% to -100% losses), leaving only the winners. With ~6% annual delistings and losers at -80%, this adds ~5.5% fake annual return.

What is look-ahead bias?

Using information a backtest that wasn't available at the historical decision time—like restated financials, future index memberships, or adjusted prices used incorrectly for price-level rules.

How should you use adjusted vs. unadjusted prices?

Use adjusted prices for return calculations (they account for splits/dividends), but unadjusted prices for absolute price rules (like "buy stocks under $10") to avoid look-ahead bias from future corporate actions.

What is point-in-time data?

Historical data that reflects information exactly as it was known on a specific date, not as restated later—critical for fundamentals which are often revised years after initial filing.

Why does missing data forward-fill create bias?

A missing price might mean the stock was halted or illiquid—you couldn't have traded there. Forward-filling assumes you exited at a price that didn't exist, especially dangerous during crises when quotes vanish for days.

What four checks validate OHLC data integrity?

1) Low≤ Open and Close ≤ High, 2) High ≥ Low, 3) Price changes must have volume > 0, 4) No duplicate timestamps. Violations indicate corrupted data.

How do you adjust prices backward for a dividend?

Multiply all prior prices by (P - D)/P where P is the price before dividend and D is the dividend amount, preserving the proportional return while removing the cash drop.

What's the difference between Tier 1 and Tier 2 data sources?

Tier 1 (Bloomberg, Refinitiv) are

20k+/year professional terminals with full point-in-time and delisting coverage. Tier 2 (Norgate, Sharadar) are

300-800/year backtest-focused vendors with survivorship-free data but less asset class coverage.

Why must corporate action adjustments be applied in reverse chronological order?

Because each adjustment factor depends on the post-action price. Starting from today and working backward, you multiply by each factor (splits, dividends) to reconstruct what historical prices "should be" in today's share structure.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Clean historical data sourcing matlab hai ke apke backtest ke results reliable aur realistic ho. Jab aap purana stock data download karte ho free sources se jaise Yahoo Finance, toh usme bohot sari hidden problems hotiain. Sabse badi problem hai survivorship bias—iska matlab ke aapke dataset mein sirf woh companies hain jo aj bhi zinda hain, joh bankrupt hui ya delist hui woh gayab ho gayi. Toh aapka backtest dikhata hai sirf winners ka data, aur har saal ke 6-8% losers ko ignore kar deta hai. Result? Aapko lagta hai strategy 40% return de rahi hai, lekin reality mein woh 10% bhi nahi degi kyunki aapne sare failures ko count hi nahi kiya.

Dusri badi problem hai corporate actions jaise stock splits aur dividends. Jab Apple ne 7-for-1 split kiya 2014 mein, toh agar aapka data properly adjusted nahi hai, toh chart pe dikhega ki pricek din mein 85% crash ho gayi—lekin woh crash thi hi nahi, bas shares multiply ho gaye. Adjusted prices use karne ka sahi tarika hai returns calculate karne ke liye, lekin agar aap "buy stocks under $10" jaisa rule laga rahe ho toh unadjusted price use karna padega, warna future ka information already apke backtest mein aa jayega (look-ahead bias).

Professional traders Bloomberg ya Refinitiv jaise expensive sources use karte hain ( $24,000/year) kyunki woh point-in-time data dete hain—matlab exactly woh information jo us din available thi, bad mein revise ki gayi nahi. Individual traders ke liye Norgate Data ya Sharadar jaise affordable options hain ($ 300-800/year) jo survivorship-free data aur accurate corporate actions provide karte hain.Agar aap free data use kar rahe ho, toh manually validate karna padega—delisted stocks ki list alag se download karo, OHLC integrity check karo (Low <= Open, Close <= High), aur har bade corporate action ko verify karo company ke investor relations website se.

Bottom line: Dirty data pe backtest karna matlab apne ap ko fool karna. Thoda paisa lagao quality data pe, ya fir free data ko thoroughly clean karo, warna apki strategy real trading mein fail hogi aur aapko samajh nahi ayega kyun.

What makes historical data "dirty"?

1. Survivorship Bias

2. Look-Ahead Bias

3. Corporate Action Errors

4. Point-in-Time Data

Clean data requirements checklist

Data source tiers

Tier 1: Professional (Expensive but Clean)

Tier 2: Specialized Backtest Vendors

Tier 3: Free/API (Requires Heavy Cleaning)

Data validation techniques

Connections

Concept Map

Hinglish (regional understanding)

Test yourself — Backtesting Frameworks

Connections