Preparing financial time series
Why data preparation follows a fixed order — duplicates, price basis, missing values, calendar alignment, outliers, resampling, minimum history.
A universe of 30 securities, ten years of daily bars, and one stubborn fact: three of those tickers were delisted in year seven, two more have a gap where an exchange holiday collided with a data-vendor error, and one printed a close that is ten times its neighbours because a stock split never got applied to the historical rows. None of this is unusual. It is the ordinary condition of financial data, and every statistic computed downstream — a return series, a rolling volatility, a feature, a backtest — inherits whatever decisions were made about it first.
The problem is not that the data is messy. Real market data is always somewhat messy. The problem is that the order in which the mess gets cleaned changes the numbers that come out the other end, and most of the mistakes that ruin a research result trace back to reordering, skipping, or silently guessing at one of these steps.
The fixed order, and why it is fixed
Quant Compose’s data preparation pipeline applies seven steps in one sequence, and that sequence is not a stylistic choice:
Each stage depends on a clean input from the one before it. Reverse any two steps and a later stage either operates on the wrong data or hides a problem the next stage was supposed to catch.
1. Duplicates first, because everything else counts rows. A (ticker, date) pair should appear exactly once. When a feed emits the same row twice — a common artefact of overnight re-runs or vendor resends — every later step that counts observations, checks for gaps, or computes a rolling window is silently working from an inflated row count. A security with 252 duplicated trading days looks like it has a full year of history when it has half of one printed twice. Removing duplicates before anything else is counted is not optional housekeeping; it is the precondition for every count that follows meaning what it claims to mean.
2. Price basis before missing values, because a policy applied to the wrong price is worse than no policy. “Price basis” means deciding, explicitly, which column represents the tradable price: raw close or corporate-action-adjusted close. A stock that pays a $2 dividend on a $50 close shows an apparent 4% overnight drop if the unadjusted close is used and the adjustment is skipped. Decide the basis before filling anything, because a forward-fill or interpolation step applied across a stale, un-rebased price will propagate the wrong number forward.
3. Missing values before calendar alignment, because you cannot align a calendar you haven’t finished populating. Calendar alignmentCalendar alignment Restricting a dataset to the dates every security in the universe actually traded on, so a thinly traded name does not create phantom gaps elsewhere. asks “does every security have a row on every date the group trades?” — a question that only makes sense once genuinely missing single-day gaps have already been resolved by an explicit policy: drop, forward-fill, backward-fill, or interpolate. Quant Compose defaults to drop, which is the conservative choice — it removes the row rather than inventing a price for it. Forward-fill is the common alternative for short, isolated gaps, but it is a policy that manufactures a data point, and it should never be applied silently.
4. Calendar alignment before outlier treatment, because a bar-count mismatch will look like an outlier problem. Once each series has a policy-consistent value on every date it has a value at all, calendar alignment keeps only the dates every security in the group actually trades on. A security domiciled in a market with different public holidays than the rest of the universe will otherwise show phantom gaps in a naive union of dates, and those gaps produce return spikes when the series is later diffed across a multi-day interval it wasn’t supposed to span. Quant Compose warns when this step removes more than 25% of rows — a threshold worth treating as a signal that the universe itself, not just the calendar, needs a second look.
5. Outlier treatment before resampling, because an aggregation step will smear a single bad print across a whole week or month. With row counts and calendars settled, extreme single-day moves can be evaluated against the rest of the same series rather than against an artefact of misalignment. Quant Compose supports winsorising daily returns at the 1st and 99th percentile, or clipping at ±4 standard deviations with the price series rebuilt from the clipped returns. Both policies default to off — outlier treatment changes the data, and a research result that depends on it should say so.
6. Resampling after outliers are handled, because averaging a raw spike compounds the error into every bar it touches. Converting daily bars to weekly (Friday close, 52 observations per year) or monthly (month-end close, 12 observations per year) means an unhandled outlier in daily returns doesn’t survive as an averaged distortion in a lower-frequency series computed from raw, unadjusted prices. Note the specific discipline here: resampling operates on price levels with an explicit close-selection rule, not by averaging returns, which would double-count compounding and produce a number no trader could reconcile against the actual weekly move.
7. Minimum history last, because it is a gate, not a transformation. After every earlier step, a security is checked against a minimum number of usable observations — Quant Compose flags an average below 260 observations per security, a rough single-year floor. This has to come last: applying a minimum-history filter before duplicates are removed or gaps are filled would reject securities that only look sparse because of an unresolved earlier step, or accept ones that only look sufficient because of duplicated rows.
A worked example
Consider a two-security universe, Ticker A and Ticker B, over five trading days.
| Date | A close (raw) | B close (raw) | Notes |
|---|---|---|---|
| Day 1 | 100.00 | 50.00 | |
| Day 2 | 101.20 | — | B missing (vendor gap) |
| Day 3 | 500.60 | 50.30 | A: 4-for-1 split not yet reflected in raw price |
| Day 4 | 125.40 | 50.10 | A adjusted; B: exchange holiday not shared with A’s market |
| Day 5 | 126.90 | 50.45 |
Applying the pipeline in order: duplicates — none found. Price basis — Ticker A’s raw prices are rebased using the split ratio, so day 3’s true adjusted close is , not . Missing values — B’s day 2 gap is forward-filled from day 1’s under the project’s stated policy. Calendar alignment — day 4 is a holiday only in B’s home market with no trade printed; under a strict alignment policy that date is dropped from both series so the two remain synchronised. Outlier treatment — with the split correctly rebased, no return in the cleaned series exceeds a 4-sigma threshold, so nothing is clipped. Resampling — left at daily. Minimum history — five observations is far short of the 260-day floor, so in practice this toy universe would be flagged, not passed through to feature construction.
Skip the price-basis step and run outlier detection directly on the raw series instead, and the day 3 return for Ticker A computes as — a number that would be clipped as an outlier, permanently discarding real information, when the actual problem was an unadjusted split that price-basis correction would have fixed for free.
Corporate actions: detection, not silent adjustment
Point-in-timePoint-in-time Stamping every record with the date it was actually knowable, so a later restatement or correction cannot leak into an earlier decision. correctness and corporate-action handling are related but distinct problems. A corporate-action check flags a price jump consistent with an unadjusted split or capital event — that is detection, and Quant Compose’s implementation of it is currently unverified against a published reference case rather than validated. It does not silently rewrite history on your behalf; it names what it suspects so a researcher can decide whether the loaded data source has already applied the adjustment or needs it applied. Yahoo Finance, one of the workspace’s real data providers, carries its own restatement risk after corporate actions — a price series pulled today can differ from the same series pulled a year ago once a split or special dividend has been backfilled by the vendor. That is a property of the data source, not something a preparation pipeline can correct after the fact; it is a reason to record the provider, the pull date, and the content hash alongside every dataset used in a result, which is what the workspace’s evidence layer is built to do.
Why order-sensitivity is not a technicality
It is tempting to treat a fixed pipeline order as an implementation detail rather than a methodological one. It is not. A featureFeature A quantity computed from raw data and given to a model as an input; a feature choice is a hypothesis about market structure, not a fact. built from a badly ordered pipeline carries the ordering error into every model that consumes it, and a model trained on that feature will happily learn the artefact rather than the signal. This is the same failure family covered in Look-ahead bias and data leakage: the ten ways it happens — the pipeline steps above are precisely where several of those ten mechanisms first enter a dataset, well before any model or feature is involved. Once a cleaned series exists, the next question is what to build from it, covered in Feature engineering for financial data.