QuantCoin

Resampling OHLCV Candles Without Corrupting Them

Resampling OHLCV bars to a longer interval looks like a one-liner and contains at least four ways to corrupt your dataset. Each column has its own correct aggregation: open takes the first value, high the maximum, low the minimum, close the last, volume the sum. Get any of those wrong and you have invented prices. Get the interval labelling wrong and every bar in the file is stamped with a timestamp it precedes, which is lookahead applied uniformly across your entire history.

Educational material, not trading advice. Algorithmic crypto trading is high-risk and most retail algo traders lose money.

The per-column rules

There is exactly one correct aggregation per OHLCV column and no room for preference.

agg = {
    "open":   "first",
    "high":   "max",
    "low":    "min",
    "close":  "last",
    "volume": "sum",
}
hourly = minute.resample("1h", label="left", closed="left").agg(agg)

Mistakes here are not subtle in effect but are easy to make in code. Using mean for open or close manufactures a price that never traded. Using last for high produces a “high” lower than prices within the bar, which breaks any rule that references extremes. Using max for volume understates activity by an arbitrary factor. And if your source carries additional columns — quote volume, trade count, a VWAP — each needs its own rule: counts sum, and a VWAP must be recomputed as a volume-weighted average rather than averaged, because averaging averages is wrong whenever the weights differ.

A consistency check worth running after any resample: low <= open <= high, low <= close <= high, and volume >= 0, on every row. It catches misconfigured aggregations immediately and costs nothing.

The label and closed arguments

closed decides which end of the interval is included; label decides which end names the resulting bar. These are the two arguments that cause silent, total corruption.

Consider aggregating minute bars into an hour. With closed="left", the interval covers 12:00 up to but not including 13:00. With label="left", the resulting row is timestamped 12:00. That combination means the bar named 12:00 summarizes 12:00–12:59 — data that was only complete at 13:00.

That is the crucial consequence: a left-labelled bar’s timestamp is the moment the interval opened, not the moment its information became available. If your backtest reads the row labelled 12:00 and makes a decision “at 12:00”, it is using the whole hour’s high, low, and close an hour before they existed. Every bar. This is the most damaging leak in the resampling step, and it produces results that look brilliant rather than broken.

Two ways to handle it. Either use right labels so the timestamp is the availability time, or keep left labels — the common convention for OHLCV — and make the backtest treat a bar as actionable only on the bar after it. The shift is what the position = signal.shift(1) idiom is for; see auditing your code for lookahead bias. What matters is that you know which convention your data uses, because a source you didn’t generate may use either.

Timezones and boundaries

Resampling to daily bars requires deciding when a day starts, and crypto has no natural answer. Continuous markets have no session close, so a “daily” candle is a convention imposed by whoever built the dataset. UTC midnight is the most common and the most defensible for research, precisely because it is arbitrary in the same way for everyone.

The failure mode is mixing conventions. Resample one series on UTC boundaries and another on local ones and you have introduced a fixed offset between them, which shows up as spurious lead-lag structure — a “predictive” relationship that is entirely an artefact of the boundary choice. Any cross-asset or cross-source study is exposed to this.

Practical rules: store everything as timezone-aware UTC, convert at the edges only, and never use naive timestamps in a pipeline that touches more than one source. If daylight-saving transitions can enter through any input, they will create duplicated or missing hours, and resampling over them produces bars of the wrong length.

Gaps, and why they are not zeros

Resampling an interval that contains no data produces a row, and what goes in that row matters. By default an aggregation over an empty interval yields missing values for prices and zero for a summed volume. Filling those prices forward from the previous close is usually the honest choice: it says “nothing traded, the last known price stands”. Filling them with zero invents a price of zero and will produce a return of −100% followed by an infinite one.

Distinguish two situations that look identical in the file:

  • A genuine no-trade interval. Real for illiquid pairs on quiet intervals. Forward-fill the price, keep volume at zero, and make sure the strategy cannot trade in a bar with no volume — a backtest that fills orders in an empty bar has invented a counterparty.
  • Missing data. The venue was down, the collector failed, the history simply isn’t there. Forward-filling here fabricates a flat, calm period that never happened, which lowers measured volatility and inflates risk-adjusted metrics.

Because both look like absent rows, they have to be distinguished by external knowledge — coverage windows, collector logs, or the pattern of the gap. Tag them rather than silently filling; more on the diagnostics in data quality checks for crypto price history.

Aggregation is lossy, and that’s the point

Every resample discards the intrabar path, and the discarded path is exactly what some strategies depend on. From an hourly bar you cannot tell whether the high preceded the low. Any rule that references the order of intrabar events — a stop and a target both inside one bar, for instance — is unanswerable at that resolution, and a backtest that answers it anyway is guessing in whichever direction its implementation happens to favour.

The implication: choose the bar interval so the strategy’s decision is representable at that interval, and if a rule needs the path, test it on finer data. Coarsening is always available; recovering the path is not — a good argument for archiving the finest data you can afford to store.

A short checklist

  • Explicit agg dict — first/max/min/last/sum, never a default mean.
  • label and closed stated explicitly, never left to a default, and documented next to the dataset.
  • A shift or a right label so no decision uses a bar before it closed.
  • UTC everywhere, converted only at the boundaries.
  • Invariant assertions on high/low/open/close ordering after every transform.
  • Gaps tagged, not silently filled, with no fills permitted in zero-volume bars.

None of this is interesting work, and all of it is upstream of every result you will produce. A validation scheme like walk-forward validation protects you from fitting noise; it does nothing at all about a dataset whose timestamps are an hour early.

market-datapythonbacktesting