Backtest Data Bugs: An Audit Sheet for Agent-Built Analysis

Before accepting an agent-built backtest, ask the developer to trace one simulated decision to the exact input rows it used. Check when those rows became available, whether their values were observed and which time units the code assumed. A completed calculation does not answer those questions.

This audit sheet checks the data and code behind a historical simulation. It does not select a strategy or establish investment performance. The worked records below are fictional teaching fixtures, not client observations or results from a trading system.

Freeze the input contract before rerunning the model

Save the dataset version, code revision and configuration used for the result under review. Keep the original output alongside any corrected run. Otherwise, a repair can change both the calculation and its evidence without leaving a useful comparison.

For each input, distinguish three times: when the underlying event happened, when its value became available to the simulated process, and the simulated decision time. Retrieval time alone does not establish historical availability. A file downloaded today may contain revisions that were unavailable at the earlier decision.

Also record the entities that were eligible at each historical cutoff. A present-day list cannot establish historical membership. If removed entities or earlier data versions cannot be recovered, identify the resulting coverage limit rather than silently treating today’s survivors as the original population.

These are proposed acceptance rules. Apply them to the actual data contract; a fixture passing them does not certify an entire backtest.

A past timestamp can still carry future information

Consider a fictional observation with an event time of 09:00 UTC. Its initial value became available at 09:01; a correction arrived at 09:04. A decision at 09:03 can use the initial version under this example’s contract. It cannot use the correction merely because both versions describe the 09:00 event.

The join must select an eligible version by its availability time. In pandas merge_asof, a backward match chooses a key at or before the left-hand key; nearest matching can select a later row. Sort the time keys, group by the relevant entity and declare a staleness tolerance. Choose whether equal timestamps are eligible from the process’s timing contract. A backward join on the wrong timestamp still leaks information.

For a deterministic pipeline, a useful additional test is to remove records unavailable at a chosen cutoff and recompute the inputs used at that cutoff. A changed earlier feature deserves investigation. Keep configuration and random seeds fixed; first establish that repeated identical runs agree. This comparison is a proposed check, not proof that every possible leakage path has been covered.

Missing, zero and expired need different states

An observed count of zero can be valid. An absent quote is not an observed zero, and a record whose use has expired needs a separate treatment. Replacing all three with 0 makes later arithmetic possible by destroying the distinction the reviewer needs.

For the fictional fixture below, the decision time is 09:03 UTC on 1 January 2024. Values are abstract counts, not prices. A record is excluded at or after its declared expiry. That boundary is a teaching convention, not a universal contract rule.

An expired contract may still require a settlement calculation using the appropriate rules and data. Excluding an expired record from ordinary input selection does not mean its economic value is zero. Keep record eligibility, settlement and missing settlement evidence separate.

Forward-filling a last observation also needs a declared limit. Its original observation and availability times must survive the fill. A new row timestamp cannot make an old value fresh.

Run a small fixture before inspecting a large result

This Python example uses only the standard library and performs no network or account operations. Its assertions check the four states above and one duration conversion. The labels describe only these checks; VALID is not a certification of all data quality.

from datetime import datetime


def utc(text):
    return datetime.fromisoformat(text.replace("Z", "+00:00"))


decision = utc("2024-01-01T09:03:00Z")

# name, value, available_at, expires_at
records = [
    ("observed_zero", 0, "2024-01-01T09:00:00Z", None),
    ("absent", None, None, None),
    ("late_revision", 7, "2024-01-01T09:04:00Z", None),
    ("expired", 7, "2024-01-01T09:00:00Z",
     "2024-01-01T09:02:00Z"),
]


def classify(record, cutoff):
    _, value, available, expiry = record
    if value is None or available is None:
        return "MISSING"
    if utc(available) > cutoff:
        return "NOT_YET_AVAILABLE"
    if expiry is not None and cutoff >= utc(expiry):
        return "EXPIRED"
    return "VALID_ZERO" if value == 0 else "VALID"


states = {r[0]: classify(r, decision) for r in records}
assert states == {
    "observed_zero": "VALID_ZERO",
    "absent": "MISSING",
    "late_revision": "NOT_YET_AVAILABLE",
    "expired": "EXPIRED",
}

start_ms = 1_700_000_000_000
end_ms = start_ms + 3_600_000
elapsed_seconds = (end_ms - start_ms) / 1_000
assert elapsed_seconds == 3_600

print(states)
print("elapsed_seconds:", elapsed_seconds)

The expected output contains the four states in the table and elapsed_seconds: 3600.0. These numbers come from the declared fixture. They are not a measured defect rate or a financial outcome.

Try the corresponding failures in the real pipeline: replace a missing input with a default, admit the late revision, keep the expired record or omit the milliseconds conversion. Check the final report as well as the detector. A warning in an intermediate log is insufficient if the report still presents an unrestricted result.

The fixture deliberately omits schema parsing, duplicate resolution, settlement and a simulation engine. Those need their own tests. A malformed timestamp should produce an explicit error or unresolved state, not a guessed replacement value.

Make time units visible in the calculation

The example’s two integer timestamps differ by 3,600,000 milliseconds: one hour, or 3,600 seconds. Treating that difference as seconds produces a duration 1,000 times too large. The value remains numeric, so a type check alone will not catch the mistake.

pandas to_datetime exposes both unit and origin; declare them for numeric timestamps. Also establish what timezone a source’s naive timestamp represents before converting it to UTC. Setting a timezone is not evidence that the source used it.

Annualisation introduces another time contract. A chosen 365-calendar-day basis divided by a measured 30-calendar-day interval gives a scaling factor of about 12.1667. This is unit arithmetic, not a return calculation or a recommended convention. A count of sessions cannot be substituted for calendar days without changing the definition.

Keep the numerator, denominator, elapsed-time measure and any compounding formula beside the output. Test zero and negative durations as invalid inputs. A scaling factor must not conceal an unspecified time basis.

Check joins and comparison sources independently

A duplicate identifier can multiply records before aggregation. Declare whether each join is one-to-one, many-to-one or something else, and inspect unmatched records. pandas merge validation can check key uniqueness for those contracts; allowing many-to-many does not perform a uniqueness check. Null keys also need attention because pandas can match them to each other.

A comparison source can help identify a mismatch, but agreement is useful only when the definitions match. Record the entity, timestamp, availability, unit, adjustment policy and whether both sources depend on the same upstream feed. An approximate proxy is not an exact reference merely because its chart looks similar.

For an independent arithmetic check, calculate a small slice directly from its source rows using separately reviewed logic. Reusing the same helper function in both paths can reproduce the same defect twice. Agreement still cannot establish that the underlying source itself was correct.

No events is not a completed leakage test

A simulation can legitimately produce no events. That does not exercise every branch of its code. Record which branches actually ran and supply a fixture for a required branch that did not.

The Freqtrade lookahead-analysis documentation makes this limit explicit: signals that never trigger are not verified, so an apparently clean result can miss bias. That is a documented limitation of that tool, not evidence that it has tested this article’s expiry or time-unit cases.

Keep a valid no-event outcome separate from an unexecuted check. Report both without converting the untested check into a pass.

Copy this backtest defect sheet

Create one record for each suspected defect. Preserve the original and corrected evidence; do not overwrite a failed run.

Decision or calculation under review:
Dataset snapshot / code revision / configuration:
Historical entity membership / known exclusions:
Input row keys / original values / units:
Event time / available-at time / decision cutoff / timezone:
Version selection / equality rule / staleness limit:
Missing-value state / expiry or settlement rule:
Join cardinality / unmatched or duplicate records:
Elapsed-time unit / rate basis / formula:
Minimal fixture / expected output:
Actual output / exercised branch / evidence:
Independent calculation or proxy / limitations:
Verdict: REPRODUCED / NOT REPRODUCED / UNTESTED
Effect on the reported result: measured / UNKNOWN
Permitted use / blocked conclusion:
Repair / regression check / reviewer / date:

Reproduced means the recorded fixture demonstrates the defect. Not reproduced means that particular check did not show it; keep its coverage limits. Untested means the required path or evidence was not examined. None of these labels is a strategy-quality score.

The API coverage worksheet addresses which population an extraction represents. This sheet follows eligible historical inputs through the calculation. The pipeline audit case covers a related operational question: whether the job produced the required output at all.

If the team cannot explain a historical input’s availability, missing-value state or units, describe the calculation and one redacted example for an audit. Establish what the result can support before asking an agent to explain it.