Systematic trading

Building a simple moving average crossover strategy in Microsoft Excel

Systematic trading · Published 2026-10-21

A spreadsheet is the most transparent backtesting tool there is: every number is visible and every formula can be inspected. Building a moving-average crossover strategy in Excel is a good way to learn how a backtest works before moving to code. This tutorial walks through the columns and formulas, and the mistakes that make spreadsheet backtests look better than they should.

The rule

A simple moving average (SMA) is the average closing price over the last N days. A crossover strategy uses two: a short average (for example 50 days) and a long one (for example 200 days). When the short average is above the long one, the trend is considered up and the strategy holds the asset; when it is below, the strategy holds cash. The 50/200 version is often called the "golden cross" when the short average rises above the long one, and the "death cross" when it falls below. A short-selling version goes short instead of to cash; we stick to long-or-cash here.

Step 1: get and lay out the data

Download daily prices for an index fund or stock from any source that provides adjusted closes, which account for dividends and splits. Put dates in column A and adjusted closes in column B, oldest at the top, with headers in row 1. Check for missing dates and obviously wrong prices before going further.

Step 2: compute the moving averages

In column C (short SMA), starting on the 50th data row, enter a formula averaging the last 50 closes, for example in C51: =AVERAGE(B2:B51). Copy it down; Excel's relative references move the window one row at a time. In column D (long SMA), start on the 200th data row: =AVERAGE(B2:B201) in D201, copied down. Rows before each average has enough data stay blank.

Step 3: the signal

In column E, on rows where both averages exist, compute the signal: 1 if the short average is above the long one, 0 otherwise. In E201: =IF(C201>D201,1,0), copied down.

Step 4: the position, lagged by one day

This is the step that most spreadsheet backtests get wrong. The signal in row t is computed from the closing price on day t. You cannot also earn the return from day t−1 to day t with that signal, because you only knew it at the end of day t. The position held over day t+1 must use the signal from day t.

In column F (position), set F202 to =E201 and copy down. The position is the previous day's signal. Skipping this lag introduces look-ahead bias and can make even a useless rule look profitable.

Step 5: returns

In column G, compute the asset's daily return: in G202, =B202/B201-1. In column H, the strategy's gross return: =F202*G202. When the position is 0 the strategy earns nothing; a more realistic version would add the interest earned on cash.

Step 6: transaction costs

Each change in position costs money: commissions, the bid-ask spread and slippage. In column I, flag trades: =ABS(F202-F201), which equals 1 on days the position changes. Choose a cost per trade as a fraction, for example 0.1 per cent, and put it in a named cell such as Cost. Net return in column J: =H202-I202*Cost. Changing the cost cell immediately shows how sensitive the result is.

Step 7: equity curves and drawdown

• Column K, buy-and-hold equity: start at 1 in K201, then =K201*(1+G202).
• Column L, strategy equity: start at 1, then =L201*(1+J202).
• Column M, running peak of the strategy: =MAX(M201,L202), starting with M201 = L201.
• Column N, drawdown: =L202/M202-1. The minimum of this column is the maximum drawdown.

Plot columns K and L on a line chart, ideally with a logarithmic axis for long histories.

Step 8: summary statistics

Compute, for both the strategy and buy-and-hold: annualised return, =(last equity)^(252/number of days)-1; annualised volatility, =STDEV.S(returns)*SQRT(252); a simple Sharpe ratio (return divided by volatility, ignoring the risk-free rate for simplicity); maximum drawdown; and the number of trades, the sum of column I. The convention of 252 trading days a year is approximate.

What the research says about moving-average rules

William Brock, Josef Lakonishok and Blake LeBaron (1992) tested moving-average and trading-range rules on the Dow Jones index from 1897 to 1986 and found that buy signals were followed by higher returns than sell signals, a result that challenged simple random-walk models. Ryan Sullivan, Allan Timmermann and Halbert White (1999) then showed how to correct for data snooping, the fact that thousands of rules had been tried on the same index. After the correction, the best rules' apparent superiority over the original sample was largely not confirmed in later data. The lesson is not that moving averages never work, but that the specific parameters which looked best historically are likely to disappoint.

Moving-average filters are more often used as risk overlays than as stand-alone strategies, as discussed in our article on market timing models.

Next steps

• Try several pairs of averages and record every result, not just the best, to see how fragile the outcome is.
• Split the data into an early design period and a later test period.
• Add interest on cash and a short-selling variant.
• Rebuild the same backtest in Python and check that the numbers match exactly. Our guide to open-source tools suggests where to start.