How to backtest a strategy with a spreadsheet and some patience

Backtesting is checking whether your rules would have worked on data you have not traded. You do not need paid software to do it. A charting platform with a replay function, a spreadsheet, and a few evenings will give you a more honest answer than most automated tools, because doing it by hand forces you to define rules precisely enough to follow.

For a part-time swing trader this is not optional. Taking three trades a week means a year to gather a hundred trades. Backtesting is how you find out whether an idea is worth a year of your life before you spend the year.

Define the rules until there is nothing left to interpret

Almost every failed backtest fails here. If your rule is “enter when the trend is strong”, you will make a hundred small judgement calls during the test, every one of them influenced by knowing what happened next, and the result will be meaningless.

Write rules a stranger could follow without asking you a question:

• Entry: the exact condition. What has to be true, on what time frame, at what price.
• Stop: where, measured from what. See where to place a stop loss.
• Target or exit: the same, in advance.
• Size: the risk per trade as a percentage.
• Filters: what disqualifies an otherwise valid setup.
• Universe: which instruments you are testing on and why.

If you cannot write these down, you do not have a strategy yet. That alone is worth discovering, and it is free.

The manual method

1. Pick a period you did not live through attentively. Anything you remember well is contaminated, because you already know what happened.
2. Cover the right side of the chart. Most platforms have a replay or bar-by-bar mode. If yours does not, drag the chart so future price is off screen. This is the entire integrity of the exercise.
3. Step forward one bar at a time. When your entry condition triggers, log the trade before revealing what happens next.
4. Log everything, in a spreadsheet. Date, instrument, entry, stop, target, the risk in R, the outcome in R, holding period, and a short note on why it qualified.
5. Keep going until you have a real sample. A hundred trades minimum. Two hundred is better. This is tedious, and the tedium is the price of an answer you can trust.
6. Then calculate. Win rate, average win, average loss, expectancy, longest losing streak, worst drawdown. The formulas are in expectancy: how to tell whether a strategy is working.

Expect this to take several evenings. That is a fair price for knowing whether an approach is worth trading.

Which biases will flatter your results?

Every one of these makes results look better than reality. Assume you are guilty of at least two.

Hindsight bias. You know how the period ended, so ambiguous setups mysteriously resolve in the right direction. Bar-by-bar replay is the only real defence.

Survivorship bias. Testing on today’s index members means testing on companies that survived. The ones that were delisted are invisible, and they are exactly the ones that would have hurt.

Optimisation, or curve fitting. You try a 20-day lookback, then 22, then 18, and settle on the one with the best numbers. You have now fitted your rules to the noise in that specific period. It will not repeat. A rule that only works at exactly 22 and falls apart at 20 and 24 is not a rule, it is a coincidence.

Cherry-picked periods. A trend-following system tested only through a strong trend will look superb. Test through conditions that should hurt it, on purpose.

Ignoring costs. Subtract spread and commission from every single trade. On tight stops this is not a rounding error, it can be most of the edge.

Assuming perfect fills. You will not always get your price. Assume you get a slightly worse one, especially on stops and in fast markets.

Out-of-sample testing, which is the part that matters

Split your data. Develop the rules on the first portion, then test the finished, frozen rules on a period you have never looked at.

If it holds up out of sample, you have something worth trading small. If it falls apart, you fitted the first period and learned nothing about the market, only about that stretch of it. This step is where most promising strategies die, which is precisely why it is the step people skip.

Once you look at the out-of-sample period and then adjust the rules, it is no longer out of sample. You have used it up. Set aside a fresh period or accept a less certain answer.

What can a backtest not tell you?

This is the section most articles leave out.

It cannot tell you whether you will follow the rules. Clicking through history is emotionally free. Holding the same position through a real drawdown with real money is not. A backtest measures the strategy, never the trader.

It cannot tell you the future will resemble the past. Conditions change. A backtest gives you a reasonable prior, not a forecast.

It cannot rescue a small sample. Forty trades on one instrument is an anecdote with a spreadsheet attached.

It cannot tell you the market will keep offering the setup. Some edges get crowded out. A backtest cannot see that coming.

What it does give you, and this is valuable enough on its own, is a realistic expectation of the pain. Knowing that your system’s worst historical run was nine consecutive losses is the difference between abandoning it on loss seven and recognising an ordinary streak. That is the argument in what a normal losing streak looks like.

Forward testing before real money

A backtest that survives out-of-sample deserves one more stage: trade it in real time, on paper or at minimal size, for a few months. This catches the things history cannot show you, mainly whether the setups are as obvious in real time as they were in replay, and whether you can actually execute around your job. How long to spend here is the subject of how long should you paper trade.

The uncomfortable likely outcome

Most ideas do not survive this process. That is the process working. Every strategy you kill in a spreadsheet is one you did not fund with real money over a year, and the arithmetic on that is strongly in your favour even when it feels like wasted effort.

The reason to do it in a spreadsheet rather than with an automated tool, at least the first few times, is that the manual version teaches you what your own rules actually mean. Automation will happily test a rule you have not understood.

The full testing method, with the spreadsheet columns I use, is in The 9-to-5 Trader.

Educational only, not financial advice. Trading carries a real risk of loss.