Cleaning & validating market data

Detect gaps and missing bars, catch bad ticks and outliers, understand adjusted vs unadjusted prices, and meet survivorship bias — the errors that silently poison backtests.

Here is a hard truth the tutorials skip: most backtest disasters are data problems, not strategy problems. A single stray tick, a mishandled split, or a dataset that quietly excludes failed companies can turn a losing idea into a "winner" on your screen. This lesson is your data hygiene checklist — run it before you trust anything.

Gaps and missing bars

Real feeds drop bars — an exchange outage, a thin overnight session, a vendor hiccup. If your indicator assumes evenly spaced time and there is a silent hole, its windows quietly span the wrong period. First, *look* for the gaps.

pythonDetect missing bars against an expected grid

import pandas as pd

def find_gaps(df, freq=class="s">"B"):
    class="s">""class="s">"Compare the index to the calendar we EXPECT (e.g. business days)."class="s">""
    expected = pd.date_range(df.index.min(), df.index.max(), freq=freq)
    missing = expected.difference(df.index)
    return missing

gaps = find_gaps(df, freq=class="s">"B")
print(fclass="s">"{len(gaps)} expected bars are missing")
print(gaps[:class="n">5])

class="c"># Also catch duplicated timestamps — a common vendor bug.
dupes = df.index[df.index.duplicated()]
print(fclass="s">"{len(dupes)} duplicate timestamps")
df = df[~df.index.duplicated(keep=class="s">"last")].sort_index()

Bad ticks and outliers

A "fat-finger" print or a decimal error can inject a price that is 10x reality for a single bar. It wrecks any indicator that uses highs/lows and can trigger phantom signals. Two cheap, robust detectors catch most of them.

pythonFlag impossible bars and return spikes

import numpy as np

def validate_bars(df):
    problems = {}

    class="c"># class="n">1. Structural impossibilities in OHLC relationships.
    bad_hl = df[class="s">"high"] < df[class="s">"low"]
    bad_range = (df[class="s">"high"] < df[[class="s">"open", class="s">"close"]].max(axis=class="n">1)) | \
                (df[class="s">"low"]  > df[[class="s">"open", class="s">"close"]].min(axis=class="n">1))
    problems[class="s">"high_lt_low"]   = int(bad_hl.sum())
    problems[class="s">"close_outside"] = int(bad_range.sum())

    class="c"># class="n">2. Non-positive prices are always wrong for cash instruments.
    cols = [class="s">"open", class="s">"high", class="s">"low", class="s">"close"]
    problems[class="s">"non_positive"] = int((df[cols] <= class="n">0).any(axis=class="n">1).sum())

    class="c"># class="n">3. Statistical outliers: returns beyond ~class="n">8 robust std devs.
    ret = df[class="s">"close"].pct_change()
    med = ret.median()
    mad = (ret - med).abs().median()
    z = (ret - med) / (class="n">1.4826 * mad + 1e-class="n">12)   class="c"># MAD-based robust z-score
    problems[class="s">"return_spikes"] = int((z.abs() > class="n">8).sum())

    return problems

print(validate_bars(df))

Notice we use a MAD-based (median absolute deviation) z-score, not the ordinary standard deviation. Why? A single huge outlier inflates the normal standard deviation so much that it *hides itself*. The median and MAD are robust — one bad tick barely moves them — so the outlier stands out clearly. Once flagged, decide per case: drop the bar, cap it, or replace it with an interpolated value.

Adjusted vs unadjusted prices

This one silently breaks more equity backtests than anything else. When a stock does a 2-for-1 split, its price halves overnight — but nothing bad happened; you just own twice as many shares. Dividends likewise drop the price by the payout on the ex-date. If your data is *unadjusted*, your code sees a 50% "crash" that never was, and fires a stop or a signal on a phantom move.

Adjusted prices retroactively scale historical bars so splits and dividends leave no artificial gap. This is the correct series for backtesting returns.
Unadjusted (raw) prices are the actual traded prices on the day — what you need for live order prices and anything tick-accurate.

pythonGet both, and see the difference

import yfinance as yf

t = yf.Ticker(class="s">"AAPL")
raw = t.history(start=class="s">"class="n">2020-class="n">06-class="n">01", end=class="s">"class="n">2020-class="n">09-class="n">15", auto_adjust=False)

class="c"># Around Apple's class="n">4-for-class="n">1 split (class="n">2020-class="n">08-class="n">31), the raw close ~quarters,
class="c"># but the split-adjusted close is continuous.
print(raw[[class="s">"Close", class="s">"Stock Splits"]].loc[class="s">"class="n">2020-class="n">08-class="n">28":class="s">"class="n">2020-class="n">09-class="n">02"])

class="c"># Rule of thumb:
class="c">#   backtest returns  -> auto_adjust=True  (adjusted)
class="c">#   live order prices -> auto_adjust=False (raw)

Survivorship bias — the invisible thumb on the scale

Suppose you backtest "buy the S&P 500 constituents" using today's 500 companies over the last 20 years. Every firm in that list survived — the ones that went bankrupt or were delisted are simply absent. You have accidentally guaranteed you only ever bought winners. This survivorship bias can add several percent of purely fictional annual return.

A reusable validation gate

Wrap these checks into one function you call the moment data arrives. A strategy should never run on data that has not passed through it.

pythonclean_ohlcv.py — one gate for all incoming data

def clean_ohlcv(df, freq=class="s">"B"):
    df = df.copy()
    cols = [class="s">"open", class="s">"high", class="s">"low", class="s">"close"]

    class="c"># class="n">1. De-duplicate and sort the time index.
    df = df[~df.index.duplicated(keep=class="s">"last")].sort_index()

    class="c"># class="n">2. Drop structurally impossible bars.
    valid = (df[class="s">"high"] >= df[class="s">"low"]) & (df[cols] > class="n">0).all(axis=class="n">1)
    dropped = int((~valid).sum())
    df = df[valid]

    class="c"># class="n">3. Report gaps but do not silently fabricate bars.
    grid = pd.date_range(df.index.min(), df.index.max(), freq=freq)
    gaps = grid.difference(df.index)

    class="c"># class="n">4. Forward-fill prices only; volume gaps are genuine zeros.
    if class="s">"volume" in df:
        df[class="s">"volume"] = df[class="s">"volume"].fillna(class="n">0)
    df[cols] = df[cols].ffill()

    print(fclass="s">"cleaned: dropped {dropped} bad bars, {len(gaps)} calendar gaps noted")
    return df

That completes your toolkit. You can stand up a reproducible environment, source clean OHLCV for stocks and crypto in UTC, reshape it in pandas, compute every core indicator by hand, and — crucially — validate the data before you believe a single result. Part 4 puts this to work: turning these signals into a strategy and its first honest backtest.

← Previous

Computing indicators from scratch

From hypothesis to rules