The best backtest in the spreadsheet

The setup

Hypothetical educational case. The price series, strategy, trade outcomes, and statistics are invented. They are not a historical record, a performance claim, or a tradable signal. Lena tests a moving-average idea on a synthetic stock basket. After trying 120 parameter combinations, she selects the most attractive result and presents its 200 simulated trades as evidence of a robust edge. A research review asks what was selected, what was omitted, and what remains genuinely unseen.

Reconstruct the impressive headline

The selected configuration has 110 gross winning trades averaging $180 and 90 gross losing trades averaging $160. Gross gains are $19,800 and gross losses are $14,400, leaving $5,400 across 200 trades. The gross average is $27 per trade, and the win rate is 55%. Neither number tells us whether the strategy survives realistic costs or whether the selection process discovered signal rather than luck.

Lena assumed only $2 of round-trip cost per trade, reducing the result by $400 to $5,000. But the fictional execution review estimates $6 of commissions and explicit charges, $12 of spread cost, and $15 of additional slippage per completed round trip. The total is $33 per trade, or $6,600 across the sample. On the same gross outcomes, the revised net result is a $1,200 loss, averaging negative $6 per trade.

Define costs without counting them twice

These cost components only make sense because the synthetic backtest used frictionless reference prices. If fills had already been simulated at bid and ask, separately deducting the entire spread again would overstate costs. Likewise, implementation shortfall measured from a decision price may already contain spread and market impact. The research log needs a precise definition of the entry benchmark, exit benchmark, and additional charges.

A sensitivity table would test several total round-trip costs, not just the one most favorable to the strategy. At $15, the net result is $2,400; at $27 it is zero; at $45 it is negative $3,600. These are arithmetic scenarios, not forecasts. The break-even cost of $27 is especially useful because it reveals how little room the selected gross result has for execution uncertainty, missed fills, and operational mistakes.

The selection problem comes before the final chart

Lena did not choose this configuration before seeing the sample. She chose it because it outperformed 119 alternatives on that same sample. Even strategies with no durable predictive advantage can produce an appealing winner when many variations are tried. As a deliberately stylized illustration, if 120 independent tests each had a 5% chance of a false positive, the chance of at least one would be 1 − 0.95^120, about 99.79%.

The actual strategy variants are probably correlated, so that independence calculation is not a valid p-value for Lena's research. Its role is to expose the multiple-testing problem, not quantify it precisely. She must record all attempted variants, including discarded indicators, changed date windows, and manual exceptions. A polished final chart cannot reveal how many failed experiments were hidden behind it.

Reserve an honest evaluation set

A better fictional research design separates development, validation, and a final untouched test. Lena can use development data to form the idea and validation data to compare a limited, documented set of choices. Once she inspects the final test and changes the strategy in response, that data is no longer untouched. Repeatedly calling the same data “out of sample” does not restore independence.

In a new 60-trade test, assume 28 gross wins averaging $170 and 32 gross losses averaging $150. Gross result is $4,760 − $4,800 = negative $40. At $33 per round trip, costs add $1,980 and net result is negative $2,020. This small invented sample does not prove the strategy can never work, but it does not support the original confidence. The appropriate response is investigation or rejection, not automatic parameter changes until the holdout turns green.

The research review also keeps the two samples separate before presenting a combined number. The revised 200-trade development result is −$1,200; the new 60-trade result is −$2,020. Together they total −$3,220 across 260 trades, or about −$12.38 per trade. That arithmetic is valid, but pooling the samples does not turn the selected development result into independent evidence. The later test has a different role because it evaluates decisions fixed before those observations were examined.

Check information timing and the path of losses

The review also asks whether signals used data actually available before each modeled trade. A closing-price indicator cannot normally justify an earlier fill at that same close without a defensible execution mechanism. Universe selection should not silently discard failed or delisted securities. Corporate actions, timestamp alignment, and stale quotes can materially change simulated outcomes. Each check addresses a possible source of error rather than offering a guarantee of future profitability.

Finally, Lena records drawdowns, consecutive losses, turnover, and capital usage alongside aggregate profit. A strategy that eventually recovers may still breach a funding limit along the way. The NFA cautions that hypothetical results benefit from hindsight and cannot fully reproduce the experience of real financial risk. Paper testing can reveal workflow problems, but it does not establish live liquidity, scalability, or the ability to follow a strategy while losing actual money.

Decide what evidence could actually change the conclusion

Lena’s next action should depend on the source of the problem. If a timestamp bug allowed tomorrow’s data into today’s signal, the implementation needs correction and the affected results need to be withdrawn or clearly superseded. If the code is correct but plausible costs consume the apparent edge, changing the label on the chart will not help. If one extraordinary winner accounts for most of the gross profit, the research question becomes whether that dependence is economically credible and adequately represented in the sample.

She can start with a reproducibility packet: the 120 tested configurations, the exact synthetic dataset, the scoring rule that selected the winner, all cost conventions and the final frozen specification. The packet should allow another reviewer to regenerate both the attractive original result and the less attractive corrected result. Keeping the unsuccessful version is useful because it shows which assumption changed the conclusion. Reproducibility is a requirement for a credible calculation, not evidence that the strategy will succeed.

A useful sensitivity check separates changes in execution cost from changes in the underlying trades. Applying $45 instead of $33 to the same 200 completed trades increases total costs by $2,400 and moves net profit from −$1,200 to −$3,600. But if worse liquidity also causes some orders to remain unfilled, the gross trade list changes too. That requires a new execution simulation, not merely another fee deduction from the old list. The distinction stops a precise cost table from hiding a larger fill-model problem.

Finally, Lena records what would count as a new experiment and what would merely continue the old search. A revised rule inspired by the failed holdout can be investigated, but its performance on that holdout is development evidence. A genuinely later observation period can test the revised process only if the specification and evaluation criteria are fixed first. An honest conclusion can be that the current idea lacks support; the review is not required to produce a strategy to trade.

Where the reasoning goes wrong