Skip to content
GetProfitable
Search

Manual replay and the spreadsheet backtest

Lesson 23 · about 12 min

You do not need to write code to backtest. The two simplest tools, bar-by-bar replay and a spreadsheet, will take you a long way, and they have a virtue the fancier tools lack: you see every trade, every fill assumption, and every cost, because you typed them yourself. Most of the errors in Modules 2 and 4 are impossible to make in a spreadsheet without noticing.

Manual replay

Replay means stepping forward through history one bar at a time, with the future hidden, and making decisions as you would live. Most charting platforms have a replay mode; failing that, you can cover the right side of a chart with a piece of paper and move it.

What replay is for:

  • Testing rules that are partly discretionary (Module 3, Lesson 2), which cannot be coded.
  • Checking that a coded rule produces the trades you think it does, by comparing the platform's historical trade markers with what you would have done on replay.
  • Training yourself to follow a system, which is a different skill from designing one.

What replay is not for: large samples. At a few minutes per trade, 200 trades is many hours, and it is hard to stay honest for that long. Replay 30 to 50 trades to validate rules, then move to a spreadsheet or code for the full test.

Rules for honest replay:

  1. Write the rules down before you start; do not change them mid-session.
  2. Record every decision as you make it, in a log, before advancing the bar.
  3. Fill at the next bar's open, not the bar you decided on, unless the rule is a resting order.
  4. Apply the costs from Module 4 to every trade.
  5. Do not skip a signal because "that one obviously loses"; that is hindsight entering through the side door.

The spreadsheet backtest

For any fully systematic rule on daily or slower bars, a spreadsheet is enough. The layout below is a template for a long-only breakout with an ATR stop, one row per bar. The columns are the calculation chain; each one uses only rows above it (earlier bars), which is what makes look-ahead impossible by construction.

Col Name Formula (row t) Notes
A Date data
B Open data
C High data
D Low data
E Close data
F TR max(C−D, abs(C−E[t−1]), abs(D−E[t−1])) True range
G ATR14 average of F over t−13 to t Volatility for stops
H HH20 max of C over t−20 to t−1 Prior 20-bar high; excludes today
I Signal if E[t−1] > H[t−1] and no open position, 1, else 0 Uses yesterday's close vs yesterday's HH
J Entry price if I = 1, B + slippage Next-bar open after signal
K Stop level if in position, entry − 2 × G at entry Fixed at entry; do not recompute
L Position 1 while open, else 0 Carry forward until exit
M Exit price if L = 1 and D ≤ K, min(K, B) − slippage; else if time stop, E Gap-aware stop fill: the worse of stop and open
N Trade R (M − J) ÷ (J − K) − cost in R Only on exit rows
O Cum R running sum of N Equity curve in R
P Peak running max of O
Q Drawdown P − O Max of this column is max drawdown

The entry column uses the previous bar's close against the previous bar's 20-bar high, then fills at the current bar's open. That single detail, offsetting the signal by one row, is the fix for the most common look-ahead error. The exit column uses min(K, B): if the bar opened below the stop, you fill at the open, not at the stop. (For a buy stop on a short exit, the same logic uses max.)

Filling it in

Below is a fragment showing how a trade flows through the columns. Slippage is 0.05 per side, commission negligible, so cost in R is 0.10 ÷ (J − K).

Date Open High Low Close ATR14 HH20 Signal Entry Stop Pos Exit R
d1 49.8 50.6 49.5 50.5 0.90 50.2 0 0
d2 50.7 51.4 50.4 51.1 0.92 50.5 1 50.75 48.91 1
d3 51.0 52.0 50.8 51.8 0.93 51.1 0 48.91 1
d4 51.5 51.9 48.7 48.9 1.05 51.4 0 48.91 0 48.86 −1.08

On d1, close 50.5 exceeds HH20 of 50.2, so d2 shows a signal, and the entry is d2's open of 50.7 plus 0.05 slippage: 50.75. The stop is 50.75 − 2 × 0.92 = 48.91. On d4, the low of 48.7 is below the stop; the open of 51.5 is above it, so the fill is at the stop minus slippage: 48.86. Risk per share was 1.84; the loss is 1.89 plus 0.10 ÷ 1.84 = 0.05R of cost, so −1.03 − 0.05 = −1.08R.

Summaries live on a second sheet: count of trades, win rate, average winner and loser, expectancy, profit factor, max drawdown from column Q, and standard error from the standard deviation of column N.

Key idea: A spreadsheet backtest forces every assumption into a visible cell. Offset the signal by one row, fill stops at the worse of stop and open, and subtract cost in R on every trade. If you can build this once, you understand what every other tool is doing for you.

Limits

Spreadsheets handle one instrument at a time comfortably and struggle with intraday data beyond a year or two, with portfolios, and with rules that need to look across many instruments on the same day. When you hit those limits, move to code; the spreadsheet you built is the specification for it, and the trade list it produced is the test case the code must reproduce.

Try it: Build the template above for one instrument and three years of daily data. Take the resulting trade list and check five trades by hand on the chart: entry bar, fill price, stop level, exit. If any of the five does not match what the rules say, you have found a formula error, and it is far better to find it here than in a live account.

Recap

  • Replay tests discretionary rules and validates coded ones; keep it to 30 to 50 trades and log before advancing.
  • A spreadsheet backtest is one row per bar with a calculation chain that only looks up, never down.
  • Offset the signal by one row and fill at the next open; fill a sell stop at min(stop, open) minus slippage.
  • Subtract cost in R on every trade; compute drawdown from a running peak column.
  • When the spreadsheet runs out of room, it becomes the specification and test case for code.

See it drawn

Original diagrams for the ideas on this page. Illustrative, not real market data.

Slippage on a market orderA buy order clears four price levels, so the average price paid is worse than the price first quoted.Buy 1,000 shares at marketpricesell orders resting (bar length = size)20.04300 shares20.03200 shares20.01200 shares20.00300 sharesnothing resting at 20.02order sweeps up the bookaverage fill 20.02SLIPPAGE0.02 a share$20.00 in totalintended 20.00Each level fills at its own price; the average is what you really paid.
Slippage on a market order. You click at 20.00, but only 300 shares are resting there, so the rest of the order fills at 20.01, 20.03 and 20.04. The average price paid is 20.02, and that two-cent gap is slippage.
An equity curve and its drawdownAn account balance rising over a year, falling from a peak to a trough, then climbing back to the old peak.ACCOUNT EQUITY$20k$12k$8k024681012TIME (MONTHS)PEAK $16,000TROUGH $12,000DRAWDOWN−25%RECOVERY
Equity curve and drawdown. An account balance plotted month by month. The fall from the $16,000 peak to the $12,000 trough is a 25% drawdown, and the shaded area lasts until the balance climbs back to the old peak.
The spread of outcomes behind an expectancyA histogram of forty trades: a tall block of small losses on the left, a low spread of larger wins on the right, and a line marking the average outcome.NUMBER OF TRADES051024 LOSSES, AVG −$20016 WINS, AVG +$600EXPECTANCY +$120−$400−$200$0+$200+$400+$600+$800PROFIT OR LOSS PER TRADEexpectancy = (40% × $600) − (60% × $200) = +$120 per trade
Expectancy: the average trade. Forty trades sorted by outcome: 24 small losses and 16 larger wins. Weighting each side by how often it happens gives the average result per trade, marked here by the dashed line at +$120.