Data Cleaning

Quick Answer

Data cleaning turns a raw feed into a series fit to backtest on, and works best as a fixed, coded pipeline. Order matters: normalise timestamps, detect outliers on raw prices, handle missing values, then apply corporate-action adjustments. No step may use information a live system lacked, and every correction is logged so figures trace back to a raw print.

Definition: Data Cleaning

Data Cleaning is the reproducible pipeline that turns a raw market feed into a series fit to backtest on, correcting bad ticks, gaps, corporate actions and timestamps in a deliberate, causal order.

Key takeaways: Data Cleaning

• Data cleaning is a fixed, reproducible pipeline, not a one-off manual scrub
• Order matters: normalise timestamps, filter outliers on raw data, handle gaps, then adjust and align
• No step may use future information; keep the pipeline causal or point-in-time
• Store raw and cleaned data with full provenance so any result is traceable and reversible
• Clean conservatively — preserve real risk events; over-cleaning is as harmful as under-cleaning

Data Cleaning at a glance

Data Cleaning in simple words

Data cleaning is everything you do to raw market data before you trust a backtest run on it: removing erroneous prices, patching gaps sensibly, adjusting for splits and dividends, and fixing timestamps. It is best thought of as a pipeline — a fixed sequence of steps applied the same way every time. Getting the order right matters, because cleaning one problem before another can either fix the data or quietly corrupt it further.

What Data Cleaning is for

A data-cleaning pipeline exists because every other data-quality problem — bad ticks, missing values, corporate actions, timestamps — has to be resolved before signals are computed, and doing it ad hoc produces backtests nobody can reproduce or trust.

Data Cleaning — professional explanation

Cleaning is a pipeline, not a one-off

Raw data carries several distinct faults at once, and each has its own remedy, so cleaning is a sequence of well-defined stages rather than a single scrub. Treating it as a pipeline — ingest, validate, correct, adjust, align, store — makes the process repeatable and auditable, so the same raw input always yields the same clean output. This matters because a backtest is only as trustworthy as the data beneath it, and an undocumented, hand-edited dataset cannot be reproduced or debugged. The pipeline should be code, versioned and rerunnable, not a series of manual spreadsheet fixes.

Order of operations

The sequence of cleaning steps is not arbitrary; doing them in the wrong order corrupts the result. Outlier detection should generally run on raw prices before corporate-action adjustment, because adjustment rescales the series and can turn a genuine split into an apparent outlier or mask a real bad tick. Timestamp normalisation should precede any cross-series alignment, so events are ordered correctly before they are joined. Missing-value handling interacts with both: you must know whether a gap is a halt, a holiday or a fault before deciding to forward-fill, mark untradeable or leave it. A defensible order is: normalise timestamps, detect and correct outliers on raw data, classify and handle missing values, apply corporate-action adjustments, then align and resample.

Cleaning must not introduce look-ahead

The cardinal rule is that no cleaning step may use information from after the point being cleaned in a way a live system could not. Back-filling a gap, interpolating across it, standardising with full-sample statistics, or applying back-adjustment factors that depend on future actions all inject future knowledge. A pipeline built for backtesting must either operate point-in-time — cleaning each date using only data available by then — or be restricted to transformations that are provably causal. This is the difference between a dataset that is merely tidy and one that is honestly usable for simulation.

Reproducibility and provenance

A clean dataset is only valuable if you can say exactly how it was produced. That means recording the raw source and its version, the cleaning code and its version, every parameter used, and a log of every correction applied, so any figure in a backtest can be traced back to a raw print. Storing both the raw and the cleaned series lets you re-derive alternatives, reverse a decision, or audit a suspicious result. Without this provenance, two researchers running the same strategy on nominally the same data can get different numbers and never discover why, which quietly undermines every conclusion drawn from the backtest.

Cleaning as a boundary, not a cure

Cleaning improves data but cannot manufacture information that was never captured, and over-cleaning is its own hazard. Smoothing away real gaps, deleting inconvenient outliers, or filling untradeable halts makes the series prettier while making the backtest less honest. The goal is fidelity to the tradeable market, not visual smoothness, so a good pipeline is conservative: it corrects clear errors, flags the ambiguous cases for review, and preserves genuine risk events intact. The output should be documented with its known limitations, because a backtest inherits every assumption baked into the cleaning, and pretending the data is perfect is itself a modelling error.

How Data Cleaning looks visually

Worked example: Data Cleaning

Ad hoc cleaning vs Pipeline cleaning

Limitations of Data Cleaning

• Cleaning cannot recover information that the raw feed never captured
• Over-cleaning smooths away genuine gaps and outliers that are real risk
• The correct order of operations depends on the data and must be reasoned, not assumed
• A point-in-time pipeline is more expensive than a single precomputed clean series
• Every cleaning choice is an assumption the backtest silently inherits

How professionals treat Data Cleaning

Institutional data engineering treats cleaning as versioned, testable code with a fixed and justified order of operations, run identically every time so the clean output is fully reproducible from the raw source. They enforce causality in every step to prevent look-ahead, store raw alongside cleaned data with full provenance, and log every correction for audit. Crucially they treat cleaning conservatively — correcting clear errors, flagging ambiguous ones, and preserving genuine risk events — and they document the residual limitations so downstream backtests know exactly what assumptions they inherit.

Common misconceptions about Data Cleaning

Common mistakes with Data Cleaning

• Applying corporate-action adjustment before outlier detection, distorting the bad-tick filter
• Cleaning data by hand in a spreadsheet so the process cannot be reproduced or audited
• Using back-fill, interpolation or full-sample statistics in a step, injecting look-ahead
• Discarding the raw data after cleaning, leaving no way to reverse or re-derive decisions
• Over-cleaning until genuine news gaps and circuit moves vanish from the series
• Failing to log corrections, so a suspicious backtest result cannot be traced to its cause

Data Cleaning: frequently asked questions

Voice search: how people ask about Data Cleaning

Natural-language questions people ask about Data Cleaning.

Sources & references

• Chan, E. P. (2013). Algorithmic Trading: Winning Strategies and Their Rationale. John Wiley & Sons (data preparation and pitfalls).
• Mechanics — cleaning order and causal (point-in-time) transforms here are computed conventions (EV-1), not external facts.