Backtesting a Moving Average Crossover in Excel
Summary
This tutorial builds a long-short moving average crossover backtest in a spreadsheet using a stock’s historical prices. It calculates short and long simple moving averages, generates buy and sell signals when they cross, records transaction prices, and computes trade returns and measures such as win rate and average return. Recording entry and exit prices also enables maximum adverse excursion analysis, which the author suggests could help evaluate stop-loss settings.
The tutorial then describes a separate equity-curve calculation: use adjusted prices for daily returns, apply lagged long or short holdings, compound the strategy’s log returns, and compare its performance with buy and hold. The author reports that the example strategy was unprofitable on the particular stock and period shown, and says a single example does not establish broader performance. The method also depends on assumptions about when orders can be filled; the author discusses using the closing auction and residual orders. The tutorial emphasizes lagging signals to reduce look-ahead bias and distinguishes price inputs for trade signals from adjusted prices used to reflect dividends in the equity curve.
Key ideas
- A moving average crossover strategy can be represented with short and long simple moving averages and buy or sell signals when their relationship changes.
- Trade signals should be lagged when assigning transaction prices to reduce look-ahead bias.
- Recording entry and exit prices supports trade-level measures such as hit ratio, average return, and maximum adverse excursion.
- A separate equity curve can apply lagged long or short positions to daily log returns and compare the result with buy and hold.
- The example performed poorly for the selected stock and period, so it does not demonstrate general profitability.
Tags
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.