Manual Backtesting in Excel: When It Works

What Excel Is Genuinely Good At

The analysis layer is where spreadsheets shine, and where many tools are weak:

• The trade journal — every column you need (the minimal set), sortable and filterable.
• Statistics on demand — expectancy, profit factor, max drawdown, longest streak are a few formulas; segment by session or regime with a pivot table.
• Cost modeling — dedicated columns for spread, commission, swap make the gross-to-net audit transparent and reusable.
• The equity curve — one line chart of cumulative R, the fastest way to spot fragility.
• Stress testing — duplicate the sheet, double the cost columns, see if the edge survives.

For these, Excel/Google Sheets is free, flexible, and more capable than most purpose-built tools.

What Excel Cannot Do

It cannot replay. The entire point of a manual backtest is making decisions with the future hidden (what is manual backtesting), and a spreadsheet of historical prices shows every row at once. “Backtesting in Excel” by scrolling a price column and deciding where you’d have traded is the textbook hindsight-bias mistake — your eye has already seen row 5,000 while you pretend to decide at row 4,000. Excel also can’t show candles forming or carry bid/ask spread in any usable way.

The Right Division of Labor

Task	Best tool
Hiding the future, showing price form	Replay tool
Placing simulated trades under uncertainty	Replay tool
Realistic fills (bid/ask, intrabar)	Tick replay
Logging trades	Excel, or the tool’s built-in journal
Expectancy / drawdown / streaks	Excel, or built-in stats
Cost audit (swap, commission)	Excel — no replay tool models these
Segmentation, stress tests	Excel

A clean workflow: trade in a replay tool (which handles hidden-future decisions and, in tick tools like StrategyTune, records P&L automatically), then export or copy the trade list into Excel for the swap/commission audit and the deeper statistics the tool doesn’t compute. Tools that auto-journal cover the basics; Excel earns its place on the cost columns and custom analysis.

When Excel-Only Is Acceptable

One legitimate case: end-of-day / weekly position strategies where you decide from today’s completed daily candle and check the next completed candle for the result, never looking ahead. Done strictly — reveal one row at a time, decide, then reveal the next — this respects the hidden future and Excel is a fine, even ideal, environment. The discipline is entirely on you, which is the method’s weakness; a replay tool enforces what here you must self-enforce.

Frequently Asked Questions

Can I really not backtest properly in Excel alone?

For intraday strategies, not properly — Excel can't hide intraday future bars or show candles forming, so any 'replay' is hindsight-contaminated. For strict end-of-day strategies where you reveal one daily row at a time, Excel-only is legitimate. For everything else, pair it with a replay tool.

What spreadsheet formulas do I need for backtest stats?

Expectancy: =AVERAGE(result_in_R column). Win rate: =COUNTIF(results,">0")/COUNT(results). Max drawdown: a running-max column minus the running equity column, then =MIN of that. Profit factor: =SUMIF(R,">0")/-SUMIF(R,"<0"). Longest streak needs a small helper column. All standard, all free.

Should I build my own Excel backtester or use a tool?

Use a replay tool for the decision-making (it solves hidden-future enforcement and realistic fills, which Excel can't), and use Excel for analysis the tool doesn't do — especially the swap/commission cost audit. Building decision-making in Excel reinvents the hard part badly.

Do replay tools export to Excel?

Many provide a trade history you can copy or export; some compute the core stats in-app already. Either way, Excel remains useful for the cost columns (no replay tool models swap or commission) and any segmentation or stress testing beyond the tool's built-in reports.