A backtesting spreadsheet should reproduce each historical decision from data that was available at that time, apply realistic costs and preserve the rule version used. The template below gives the row structure and formulas; it does not repair biased source data or prove future profitability.
Copy-ready backtest log
Test_ID,Rule_Version,Market,Signal_Time,Data_Available_Time,Entry_Time,Direction,Entry_Price,Initial_Stop,Target,Quantity,Exit_Time,Exit_Price,Commission,Spread_Cost,Slippage,Financing,Gross_PnL,Net_PnL,Initial_Risk,Result_R,Equity,Running_Peak,Drawdown_Pct,In_Sample,Notes
Column responsibilities
| Group | Columns | Control |
|---|---|---|
| Experiment | Test ID, rule version, in/out-of-sample flag | Prevents silent rule mixing |
| Chronology | Signal, availability, entry and exit times | Detects look-ahead |
| Trade | Market, side, prices, stop, target and quantity | Reconstructs the rule |
| Costs | Commission, spread, slippage and financing | Converts gross to net |
| Risk | Initial risk, R result, equity, peak and drawdown | Shows capital path |
Spreadsheet formulas
Assume direction is entered as 1 for long and −1 for short.
- Gross P&L: Direction × (Exit Price − Entry Price) × Quantity × verified value per price unit.
- Net P&L: Gross P&L − Commission − Spread Cost − Slippage − Financing.
- Result R: Net P&L ÷ Initial Risk, when initial risk is positive.
- Equity: Prior Equity + Net P&L, excluding external deposits and withdrawals.
- Running Peak: maximum of prior running peak and current equity.
- Drawdown %: (Running Peak − Equity) ÷ Running Peak × 100.
Separate data availability from timestamp
A bar can be timestamped at its opening or closing boundary while the completed value is not known until later. Economic series can also be revised. Record when each input became available to the strategy, then enforce that the simulated order occurs afterward.
Include failed and unavailable instruments
Historical universes should retain delistings, expiries and membership dates. A current list of surviving instruments can create survivorship bias. Review Backtesting Biases before importing the sample.
Cost assumptions tab
| Field | Record |
|---|---|
| Commission | Source, unit, date range and tier |
| Spread | Bid/ask source or stated proxy |
| Slippage | Base, favorable and stressed method |
| Financing | Rate source, cutoff and day-count |
| Contract value | Instrument specification and currency conversion |
Backtesting Transaction Costs explains why these should be applied at the event that creates them.
Summary sheet
- Trade count and effective sample considerations
- Gross and net return
- Win rate, average win and average loss
- Expectancy and profit factor
- Maximum drawdown and recovery duration
- Turnover, exposure and cost totals
- In-sample versus out-of-sample results
- Concentration by market, period and setup
Research sequence
- Freeze the rule and data definitions.
- Protect a clean raw-data tab.
- Record every transformation.
- Run chronology and universe checks.
- Apply base and stressed costs.
- Separate development and evaluation results.
- Reproduce summary metrics from row-level data.
- Archive the workbook with a version and hash where practical.
Limits
A spreadsheet is appropriate for transparent, moderate-scale research but can become fragile when manual edits, hidden formulas or large datasets accumulate. Use validation checks and migrate to tested code when scale requires it. Continue with Backtesting and Strategy Validation. Return to MFXG tools for the journal and review templates.