Test before you trade. This free backtesting log helps you systematically test strategies on historical data before risking real capital.
Why Backtesting Matters
Before trading a strategy live:
- Verify the edge exists - Does it actually work?
- Understand characteristics - Drawdowns, win rate, etc.
- Build confidence - Trust your system
- Identify weaknesses - Where does it fail?
- Set expectations - Know what to expect
What’s Included
Strategy Documentation
Before testing, document:
- Strategy name and version
- Market/instrument
- Timeframe
- Entry rules (specific)
- Exit rules (specific)
- Position sizing approach
- Date range to test
Backtest Trade Log
Record each historical trade:
- Trade number
- Date
- Entry price (historical)
- Exit price (historical)
- Direction (long/short)
- Stop loss
- Target
- P&L (points/%)
- Setup quality rating
- Notes
Results Dashboard
Automatic calculations:
- Total trades
- Win rate
- Average win
- Average loss
- Profit factor
- Expectancy
- Maximum drawdown
- Sharpe ratio estimate
- Return/drawdown ratio
Statistical Analysis
Validate your results:
- Sample size adequacy
- Standard deviation
- T-test significance
- Confidence intervals
- Is edge statistically significant?
Sample Size Calculator
Determine if you have enough data:
- Input win rate
- Input avg win/loss ratio
- Calculate minimum sample needed
- Understand confidence level
Forward Test Comparison
Track live vs backtest:
- Backtest metrics
- Forward test metrics
- Variance analysis
- Execution impact
Test Log History
Track all tests:
- Test date
- Strategy version
- Results summary
- Decision (trade/discard)
- Notes
How to Use This Template
Step 1: Download
Click the download button to get the Excel file.
Step 2: Document Your Strategy
Write out specific, testable rules before starting.
Step 3: Conduct Backtest
Walk through historical charts, logging each trade that meets your criteria.
Step 4: Analyze Results
Review the dashboard and statistical analysis.
Step 5: Decide
Based on results, decide to trade live, modify, or discard.
Backtesting Process
1. Define Rules
Write specific entry and exit rules. If you can’t define them precisely, you can’t test them.
2. Select Sample
Choose date range and market conditions to test. Include various conditions.
3. Walk Forward
Move through charts bar-by-bar, recording trades as they would trigger.
4. Record Everything
Log every trade, even the ugly ones. No cherry-picking.
5. Analyze
Calculate statistics and assess significance.
6. Validate
Forward test on out-of-sample data.
Key Backtest Metrics
| Metric | What It Tells You |
|---|---|
| Win Rate | How often you’re right |
| Profit Factor | Gross profit / Gross loss |
| Expectancy | Expected value per trade |
| Max Drawdown | Worst peak-to-trough decline |
| Sharpe Ratio | Risk-adjusted returns |
| Calmar Ratio | Return / Max drawdown |
Statistical Significance
Not all backtests are meaningful:
Sample Size Matters
- 30 trades: Very low confidence
- 100 trades: Minimum useful
- 200+ trades: Better confidence
- 500+ trades: Strong confidence
T-Test for Edge
This template calculates whether your edge is statistically significant at 95% confidence.
Confidence Intervals
See the range of expected performance, not just point estimates.
Common Backtesting Mistakes
Curve Fitting
Over-optimizing rules to fit historical data. Solution: Keep rules simple.
Look-Ahead Bias
Using information not available at trade time. Solution: Walk forward bar-by-bar.
Survivorship Bias
Only testing stocks that exist today. Solution: Use delisted stock data.
Ignoring Costs
Not accounting for commissions and slippage. Solution: Include realistic costs.
Cherry-Picking
Selecting favorable test periods. Solution: Test multiple conditions.
Backtest to Live Reality
Backtest results rarely match live:
| Factor | Backtest | Live |
|---|---|---|
| Execution | Perfect fills | Slippage |
| Emotions | None | Significant |
| Discipline | Perfect | Variable |
| Costs | Estimated | Real |
| Conditions | Historical | Current |
Plan for 20-30% degradation from backtest to live.
Forward Testing Protocol
After backtest, forward test:
- Trade small size for 30+ trades
- Compare to backtest expectations
- If variance > 20%, investigate
- Only scale up after validation
Limitations of Spreadsheet Backtesting
Manual backtesting has constraints:
- Time-consuming - Manual testing is slow
- Subjectivity risk - Easy to bend rules
- Limited scope - Can only test simple rules
- No optimization - Can’t test parameter ranges
- Human error - Mistakes in recording
When to Use Trading Software
For complex backtesting:
- Multiple parameters to optimize
- Large datasets (years of data)
- Algorithmic strategies
- Portfolio-level testing
This spreadsheet is best for manual, discretionary strategy validation.
Complement with JournalPlus
JournalPlus helps validate strategies in live trading:
- Track live performance - Compare to backtest
- AI pattern analysis - Find what actually works
- Automatic logging - No manual trade entry
- Indian broker support - Zerodha, Upstox data
One-time payment. Validate your edge with real data.
Download Your Template
Don’t trade strategies blind. Test first, trade second.
