Your Backtest Is Cheating

Dec 26, 2018

The S&P 500 fell roughly 2.7 percent during the shortened December 24 session. A stock screener that looked stable in September can now produce a portfolio with a materially different risk profile, even when its ranking formula has not changed.

This is not only a market problem. It is a database problem.

Most stock screeners are implemented as if historical market data were a static matrix:

date × symbol → price, volume, fundamentals

That model is incomplete. Symbols change. Companies merge. Securities delist. Index constituents enter and leave. Fundamentals become public at specific times. Splits and dividends rewrite the apparent price history. Vendors correct old records. The result is a data set whose contents depend on when it was queried.

A historical stock screen is therefore not a filter over rows. It is an as-of query over a versioned data system.

This distinction is easy to ignore during a rising market. It becomes expensive during a crash because the errors are not evenly distributed. Failed companies disappear. recent winners dominate the surviving universe. stale fundamentals become more common. corporate-action mistakes create false momentum signals. The screen appears defensive because the database has quietly removed part of the loss distribution.

This article describes a point-in-time market data pipeline for stock screeners, factor research, and backtesting. The objective is not a more sophisticated ranking model. The objective is to make a simple model produce the same answer when it is rerun six months later.

The three questions every historical row must answer

Every record used by a stock screener should answer three different time questions:

1. When did the economic event occur?
2. When did the market learn about it?
3. When did the database record it?

For a daily closing price, those times may be nearly identical. For an earnings report, they are not.

A company can report quarterly revenue after the close on February 14. The quarter may have ended on December 31. A vendor may ingest the filing on February 15 and revise a parsing error on February 18.

The following schema is materially safer than a table keyed only by fiscal period:

symbol
fiscal_period_end
published_at
recorded_at
revenue
operating_income
shares_diluted
source_document

A screen executed at the close on February 14 cannot use the filing if it was published after the close. A screen executed on February 16 may use it. A faithful replay must also decide whether to reproduce the original parsed value or use the corrected value.

There is no single timestamp called date that can represent all of this.

Survivorship bias is a join error

Survivorship bias is usually described as a statistical problem. In production systems, it often begins as an ordinary join.

Suppose the current security master contains 3,000 active U.S. common stocks. A developer joins that table to ten years of prices and calculates historical factor returns. Securities that were acquired, liquidated, or delisted are absent before the calculation begins.

The query is fast, coherent, and wrong.

The correct universe is a set of membership intervals:

security_id
universe_id
valid_from
valid_to
reason_added
reason_removed

A security belongs to the universe on date t when:

valid_from <= t < valid_to

Use an internal security_id, not a ticker, as the durable key. Tickers are labels. They can be reused.

A minimal point-in-time universe filter in Python is straightforward:

from __future__ import annotations

import pandas as pd


def members_on(
    memberships: pd.DataFrame,
    as_of: pd.Timestamp,
) -> pd.DataFrame:
    required = {
        "security_id",
        "valid_from",
        "valid_to",
    }
    missing = required.difference(memberships.columns)
    if missing:
        raise ValueError("missing columns: %s" % sorted(missing))

    frame = memberships.copy()
    frame["valid_from"] = pd.to_datetime(
        frame["valid_from"],
        utc=True,
    )
    frame["valid_to"] = pd.to_datetime(
        frame["valid_to"],
        utc=True,
        errors="coerce",
    )

    active = (
        (frame["valid_from"] <= as_of)
        & (
            frame["valid_to"].isna()
            | (as_of < frame["valid_to"])
        )
    )
    return frame.loc[active].copy()

The implementation is not the hard part. The hard part is retaining removed securities and the dates on which removals became effective.

A stock screener without delisted stocks can still be useful for present-day idea generation. It cannot support honest historical claims.

Look-ahead bias is usually a timestamp mismatch

Look-ahead bias does not require an obviously fraudulent rule such as selecting next quarter’s earnings winners. It can appear through a reasonable query that uses the wrong timestamp.

Consider a value screen using enterprise value divided by trailing operating income. The price is known at the market close. The income figure is known only after the relevant filing is public. Joining on fiscal quarter end gives the model information weeks before an investor could have had it.

The correct operation is an as-of join from each market timestamp to the latest record whose published_at is not later than that timestamp.

from __future__ import annotations

import pandas as pd


def attach_latest_fundamentals(
    prices: pd.DataFrame,
    fundamentals: pd.DataFrame,
) -> pd.DataFrame:
    left = prices.copy()
    right = fundamentals.copy()

    left["market_at"] = pd.to_datetime(
        left["market_at"],
        utc=True,
    )
    right["published_at"] = pd.to_datetime(
        right["published_at"],
        utc=True,
    )

    left = left.sort_values(
        ["security_id", "market_at"]
    )
    right = right.sort_values(
        ["security_id", "published_at"]
    )

    joined = pd.merge_asof(
        left,
        right,
        by="security_id",
        left_on="market_at",
        right_on="published_at",
        direction="backward",
        allow_exact_matches=True,
    )

    invalid = (
        joined["published_at"].notna()
        & (joined["published_at"] > joined["market_at"])
    )
    if invalid.any():
        raise AssertionError(
            "future fundamentals entered the screen"
        )

    return joined

This pattern should be used for fundamentals, analyst estimates, credit ratings, index membership, borrow availability, and any other state that changes over time.

The useful abstraction is not “latest value.” It is “latest value known as of this event time.”

Adjusted close is not raw market data

A vendor’s adjusted close is a derived series. It is convenient, but it is not a neutral input.

A split-adjusted price is appropriate for measuring price continuity. A total-return adjusted price also incorporates dividends. A back-adjusted series changes historical values when a new corporate action occurs. If the vendor corrects an old dividend, the entire preceding series can change.

That behavior creates two operational risks.

First, a strategy may not reproduce after the vendor rewrites history. Second, a feature may accidentally mix total-return and price-return concepts.

Store raw closes and corporate actions separately:

daily_prices:
    security_id
    session_date
    close
    volume
    recorded_at

corporate_actions:
    security_id
    effective_date
    action_type
    split_ratio
    cash_amount
    announced_at
    recorded_at

Then derive the series required by the calculation.

For a forward total-return index, explicit share accounting is easier to audit than a mysterious adjustment factor:

from __future__ import annotations

from dataclasses import dataclass
from typing import Iterable, Optional


@dataclass(frozen=True)
class Session:
    close: float
    split_ratio: float = 1.0
    cash_dividend: float = 0.0


def total_return_index(
    sessions: Iterable[Session],
    initial_value: float = 100.0,
) -> list[float]:
    rows = list(sessions)
    if not rows:
        return []

    if rows[0].close <= 0:
        raise ValueError("initial close must be positive")

    shares = initial_value / rows[0].close
    values = [initial_value]

    for row in rows[1:]:
        if row.close <= 0:
            raise ValueError("close must be positive")
        if row.split_ratio <= 0:
            raise ValueError("split ratio must be positive")
        if row.cash_dividend < 0:
            raise ValueError(
                "cash dividend cannot be negative"
            )

        shares *= row.split_ratio
        cash = shares * row.cash_dividend
        shares += cash / row.close
        values.append(shares * row.close)

    return values

This example assumes the dividend amount is quoted per post-split share when a split and dividend share an effective date. A production corporate-actions engine must define that convention explicitly and test it against the source documents.

The important property is auditability. The screen should be able to explain why a historical price changed.

The first chart should be an underwater chart

Cumulative return charts hide timing. Two strategies can finish at the same value while imposing different operational and psychological costs.

An underwater chart plots percentage decline from the previous peak:

D_t = V_t / max(V_s for s ≤ t) - 1

The series is always zero at a new high and negative below the high. It answers three questions directly:

• How deep was the loss?
• How long did the strategy remain below its peak?
• Did the screen fail before, during, or after the benchmark decline?

The chart below compares the screened portfolio with its benchmark using the same calendar, return convention, and rebalance timing.

Generate the chart data in Python from portfolio values, not from frontend calculations:

from __future__ import annotations

import json
from pathlib import Path

import pandas as pd


def drawdown(values: pd.Series) -> pd.Series:
    if values.empty:
        return values.astype(float)

    values = values.astype(float)
    if (values <= 0).any():
        raise ValueError(
            "portfolio values must remain positive"
        )

    peaks = values.cummax()
    return values.div(peaks).sub(1.0)


def write_drawdown_json(
    frame: pd.DataFrame,
    destination: str,
) -> None:
    required = {
        "date",
        "strategy_value",
        "benchmark_value",
    }
    missing = required.difference(frame.columns)
    if missing:
        raise ValueError("missing columns: %s" % sorted(missing))

    data = frame.copy()
    data["date"] = pd.to_datetime(
        data["date"],
        utc=True,
    )
    data = data.sort_values("date")

    data["strategy"] = drawdown(
        data["strategy_value"]
    )
    data["benchmark"] = drawdown(
        data["benchmark_value"]
    )

    records = [
        {
            "date": row.date.strftime("%Y-%m-%d"),
            "strategy": round(float(row.strategy), 8),
            "benchmark": round(float(row.benchmark), 8),
        }
        for row in data.itertuples(index=False)
    ]

    Path(destination).write_text(
        json.dumps(records, indent=2),
        encoding="utf-8",
    )

Do not calculate drawdown from a return series with missing sessions unless the missing-session policy is defined. A suspended security, a market holiday, and a data outage are different events.

A stock screener should be a deterministic function

A useful design target is:

screen_result = f(
    decision_time,
    universe_version,
    market_data_version,
    fundamental_data_version,
    corporate_action_version,
    strategy_code_version,
    configuration
)

Every input should be addressable and immutable. If an upstream vendor sends a correction, write a new version. Do not overwrite the prior record.

This permits two distinct backtests:

1. As-known backtest: uses only values available and recorded at the historical decision time.
2. As-corrected backtest: uses the best current reconstruction of what was economically true, while still respecting publication times.

The first measures the strategy an operator could actually have run. The second measures the strategy logic with data-entry noise reduced. The gap between them is operational risk.

A simple screen configuration can be serialized and hashed:

from __future__ import annotations

import hashlib
import json
from typing import Any, Mapping


def configuration_hash(
    configuration: Mapping[str, Any],
) -> str:
    payload = json.dumps(
        configuration,
        sort_keys=True,
        separators=(",", ":"),
        ensure_ascii=True,
    ).encode("utf-8")

    return hashlib.sha256(payload).hexdigest()


screen = {
    "universe": "us_common_stocks",
    "minimum_price": 5.0,
    "minimum_dollar_volume_20d": 2_000_000.0,
    "momentum_lookback_sessions": 126,
    "momentum_skip_sessions": 5,
    "rebalance": "monthly_close",
    "positions": 50,
}

print(configuration_hash(screen))

The hash is not a substitute for source control. It is a compact identity for a complete parameter set and is useful in logs, result paths, and database keys.

Separate observation time from execution time

A common backtest error uses the closing price to calculate a screen and then assumes execution at the same close.

That is not possible unless the complete ranking and order submission occur before the close using information already available. A daily strategy should state its event sequence.

For example:

16:00 New York: market closes
16:05: official closing prices begin arriving
16:20: screen is calculated
next session 09:30: orders become eligible

Execution at the next open introduces overnight risk and opening auction effects. Execution at the next volume-weighted average price introduces intraday exposure and implementation assumptions.

The screen should emit target positions. A separate execution model should translate targets into fills.

from __future__ import annotations

import pandas as pd


def next_session_targets(
    scores: pd.DataFrame,
    positions: int,
) -> pd.DataFrame:
    if positions <= 0:
        raise ValueError("positions must be positive")

    frame = scores.copy()
    frame["decision_at"] = pd.to_datetime(
        frame["decision_at"],
        utc=True,
    )
    frame["execution_session"] = (
        pd.to_datetime(
            frame["session_date"],
            utc=True,
        )
        .shift(-1)
    )

    ranked = frame.sort_values(
        ["decision_at", "score", "security_id"],
        ascending=[True, False, True],
    )

    selected = (
        ranked.groupby("decision_at", sort=False)
        .head(positions)
        .copy()
    )
    selected["target_weight"] = 1.0 / positions

    return selected[
        [
            "decision_at",
            "execution_session",
            "security_id",
            "target_weight",
        ]
    ]

The final sort key on security_id is deliberate. Equal scores must produce deterministic results.

Missing data is a position, not an exception

Many research pipelines remove rows containing null values. In a stock screener, that operation can create an unintended strategy.

A missing fundamental may mean:

• the company has not filed,
• the filing was late,
• the vendor failed to parse it,
• the field is not economically meaningful,
• the security changed reporting status,
• the value is genuinely zero,
• the value existed but was unavailable at the decision time.

Dropping the row assigns a zero probability of selection. Filling with zero assigns a numeric claim. Forward-filling can expose stale information. Each choice has portfolio consequences.

Represent availability explicitly:

from __future__ import annotations

import pandas as pd


def classify_signal(
    value: pd.Series,
    published_at: pd.Series,
    decision_at: pd.Series,
    stale_after_days: int,
) -> pd.DataFrame:
    published_at = pd.to_datetime(
        published_at,
        utc=True,
    )
    decision_at = pd.to_datetime(
        decision_at,
        utc=True,
    )

    age = decision_at.sub(published_at).dt.days
    known = (
        value.notna()
        & published_at.notna()
        & (published_at <= decision_at)
    )
    stale = known & (age > stale_after_days)

    status = pd.Series(
        "missing",
        index=value.index,
        dtype=object,
    )
    status.loc[known] = "available"
    status.loc[stale] = "stale"

    return pd.DataFrame(
        {
            "value": value,
            "status": status,
            "age_days": age,
        }
    )

A production screen can exclude stale records, penalize them, or route them to a separate model. The policy should be visible in the configuration and the output.

Corporate actions can manufacture alpha

A split error can create an apparent 50 percent or 90 percent one-day loss. A special dividend can resemble a crash. A ticker change can break a return chain. A merger can leave a stale price attached to an inactive symbol.

These errors are attractive to ranking models because they are extreme. A mean-reversion screen buys the false collapse. A momentum screen avoids the false loser or selects the false winner. The backtest records alpha generated by an ETL defect.

Run invariants before calculating factors:

from __future__ import annotations

import pandas as pd


def validate_daily_prices(
    prices: pd.DataFrame,
) -> pd.DataFrame:
    required = {
        "security_id",
        "session_date",
        "close",
        "volume",
    }
    missing = required.difference(prices.columns)
    if missing:
        raise ValueError("missing columns: %s" % sorted(missing))

    frame = prices.copy()
    frame["session_date"] = pd.to_datetime(
        frame["session_date"],
        utc=True,
    )

    duplicates = frame.duplicated(
        ["security_id", "session_date"],
        keep=False,
    )

    nonpositive_close = frame["close"] <= 0
    negative_volume = frame["volume"] < 0

    ordered = frame.sort_values(
        ["security_id", "session_date"]
    )
    ordered["raw_return"] = (
        ordered.groupby("security_id")["close"]
        .pct_change()
    )
    extreme_move = ordered["raw_return"].abs() > 0.40

    issues = ordered.loc[
        duplicates
        | nonpositive_close
        | negative_volume
        | extreme_move,
        [
            "security_id",
            "session_date",
            "close",
            "volume",
            "raw_return",
        ],
    ].copy()

    return issues

An extreme move is not automatically an error. It is an event that requires reconciliation against splits, dividends, mergers, bankruptcies, and source documents.

Treat this report as a queue, not as a filter. Silently clipping returns replaces observable failures with hidden assumptions.

Rebalancing creates a distributed-systems problem

A screener that covers several thousand securities may depend on prices, fundamentals, reference data, borrow data, and corporate actions from different systems. Those feeds do not finish simultaneously.

If the screen starts when 98 percent of the data is present, the missing 2 percent may not be random. Illiquid securities, foreign issuers, and companies with recent actions are often delayed. The screen then changes based on ingestion timing.

Use a batch manifest:

batch_id
market_session
dataset
expected_partitions
received_partitions
source_watermark
sealed_at
content_hash

A dataset is eligible for a production screen only after it is sealed. Late records create a new batch version.

The pipeline should fail closed. An incomplete universe should not become a smaller valid universe merely because the query returned fewer rows.

A small batch gate can enforce that rule:

from __future__ import annotations

from dataclasses import dataclass


@dataclass(frozen=True)
class BatchManifest:
    dataset: str
    expected_partitions: int
    received_partitions: int
    sealed: bool
    content_hash: str


def require_complete(
    manifest: BatchManifest,
) -> None:
    if not manifest.sealed:
        raise RuntimeError(
            "%s batch is not sealed"
            % manifest.dataset
        )

    if (
        manifest.received_partitions
        != manifest.expected_partitions
    ):
        raise RuntimeError(
            "%s batch is incomplete: %d of %d partitions"
            % (
                manifest.dataset,
                manifest.received_partitions,
                manifest.expected_partitions,
            )
        )

    if not manifest.content_hash:
        raise RuntimeError(
            "%s batch has no content hash"
            % manifest.dataset
        )

This is ordinary data engineering. In quantitative systems, ordinary data engineering determines the return series.

Measure turnover before optimizing the ranker

A ranking model can appear stable while producing expensive portfolio changes near the selection cutoff.

If the portfolio holds the top 50 names, a small score revision around ranks 45 through 60 can replace many positions. Those changes may be caused by corrected data rather than new economic information.

Report at least:

• gross turnover,
• one-way turnover,
• count of entries and exits,
• median rank change,
• selection changes caused by data revisions,
• selection changes caused by new observations.

One-way turnover for target weights is:

T_t = 0.5 * sum(|w_{i,t} - w_{i,t-1}|)

from __future__ import annotations

import pandas as pd


def one_way_turnover(
    previous: pd.Series,
    current: pd.Series,
) -> float:
    symbols = previous.index.union(current.index)

    old = previous.reindex(
        symbols,
        fill_value=0.0,
    ).astype(float)
    new = current.reindex(
        symbols,
        fill_value=0.0,
    ).astype(float)

    return 0.5 * float((new - old).abs().sum())

A screen that changes materially when one vendor field is revised is not necessarily wrong. It is fragile. Fragility should be measured before capital is assigned.

A minimal audit record

Every production run should write one immutable audit record containing:

run_id
decision_at
execution_policy
universe_batch_id
price_batch_id
fundamental_batch_id
corporate_action_batch_id
strategy_commit
configuration_hash
eligible_security_count
selected_security_count
rejected_security_count
result_hash
created_at

For each rejected security, retain a machine-readable reason:

PRICE_TOO_LOW
LIQUIDITY_TOO_LOW
FUNDAMENTAL_NOT_AVAILABLE
FUNDAMENTAL_STALE
CORPORATE_ACTION_UNRESOLVED
SECURITY_NOT_ACTIVE
DATA_BATCH_INCOMPLETE

This makes the screen explainable without requiring a forensic query across temporary tables.

It also creates a useful operational metric: the rejection-rate distribution. A sudden increase in FUNDAMENTAL_NOT_AVAILABLE is probably a feed problem. A sudden increase in CORPORATE_ACTION_UNRESOLVED may indicate a vendor mapping failure. A sudden fall in total eligible securities may indicate that the universe join is wrong.

Tests that matter

Unit tests for arithmetic are necessary but insufficient. The costly failures occur at temporal boundaries.

Maintain fixtures for:

• a 2-for-1 split,
• a reverse split,
• an ordinary cash dividend,
• a special dividend,
• a ticker change,
• an acquisition for cash,
• an acquisition for stock,
• a bankruptcy and delisting,
• a late filing,
• a restated filing,
• a security entering an index,
• a security leaving an index,
• a market holiday,
• a trading suspension,
• a vendor correction received after the original run.

The highest-value test is replay equivalence:

same code
same configuration
same immutable input versions
same result hash

If that condition does not hold, the stock screener is not reproducible.

What the December decline should change

A market decline should change prices, volatility, liquidity, correlations, and portfolio losses. It should not change the meaning of the historical database.

If a strategy’s backtest improves after failed securities disappear from the vendor universe, the strategy did not improve. If a drawdown vanishes after an adjusted-close revision, risk did not vanish. If a screen selects a company using a filing that had not been published, the model did not predict the result.

The immediate temptation after a loss is to change the ranking formula. The higher-priority task is to prove that the existing formula was evaluated against the information set it was supposed to use.

A reliable stock screener has four properties:

1. The universe is point-in-time and includes delisted securities.
2. Every feature is joined by publication time, not reporting period.
3. Prices and corporate actions are stored separately and versioned.
4. Every run can be reproduced from immutable inputs and a code revision.

These controls do not guarantee alpha. They prevent database behavior from being misreported as alpha.

During a quiet market, that distinction looks academic. During a crash, it is the difference between a model failure and an accounting error.