Strategies & technical analysis

How to Backtest a Gold Trading Strategy in Excel: Formulas, Columns and a Worked Example

You do not need to code to test a gold idea. A spreadsheet can test simple rules on daily or hourly XAUUSD candles, and because you can see every row, it is harder to fool yourself. This guide shows exactly how: getting the data out of MT5, the columns to build, the formulas to type, how to add costs and avoid the classic look-ahead mistake, and a worked example run on ten years of real gold data.

Gold spot$3,528.2−99.8 (−2.75%)NextUS Q3 GDP (second estimate) and October PCE inflationin 4d 20h · Wed 13:30 GMTMarket opencloses in 4h 34mRange$123.2
Gold price, last 12 monthsGold price, last 12 monthsHigh $5,318 (29 Jan 2026) · low $3,536 (20 Nov 2026) · 12-month change −14.2%$4,000$4,500$5,000$3,536Dec ’25Feb ’26Apr ’26Jun ’26Aug ’26Oct ’26
COMEX gold, daily closes, last 12 months · data to 20 Nov 2026 · xauusdglobal.com

The short answer

To backtest a gold strategy in Excel: (1) export daily or hourly XAUUSD candles from MT5 as a CSV, (2) put one candle per row, (3) build columns for the indicator, the signal, the profit or loss, the cost and the running total, (4) apply each signal to the next row’s price change to avoid look-ahead, and (5) summarise: total profit, number of trades, win rate and maximum drawdown.

In our worked example on ten years of real gold data, a simple “hold gold only above its 200-day average” rule entered 39 times and was out of the market 28% of the time. Your own sheet will show you the profit, drawdown and win rate for any rule you build. That is the kind of honest, checkable answer a spreadsheet gives you.

What a spreadsheet can and cannot test

Good for Not good for
Daily or hourly rules Tick-by-tick scalping
Moving averages, breakouts, simple filters Rules that need intrabar order (did the stop or target hit first?) on low timeframes
A few thousand rows Hundreds of thousands of rows
Seeing every trade clearly Fast testing of many settings

For the full method (splitting data, choosing tools, reading the result), see our lesson on backtesting gold with free data.

Step 1: Get the data out of MT5

  1. In MT5, open View → Symbols (or press Ctrl+U).
  2. Select your gold symbol (XAUUSD, GOLD or similar).
  3. Open the Bars tab.
  4. Choose the timeframe (D1 or H1 to start) and the date range, then click Request.
  5. Click Export Bars and save the CSV file.
  6. Open it in Excel or Google Sheets.

You will get columns like Date, Open, High, Low, Close, and volume. Check for missing days and odd values (a zero, or a candle much bigger than its neighbours) before you start. Note that your broker’s history may be shorter for hourly data.

Step 2: Write the rule in one sentence

A spreadsheet needs a rule with no judgement. For example:

“At each daily close, hold one ounce of gold if the close is above its 200-day simple moving average. Otherwise hold nothing. Pay $0.30 per ounce each time I enter.”

If you cannot write your rule this clearly, you cannot test it yet. Our trading rules card helps you write it.

Step 3: Build the columns

Assume your data starts in row 2, with Date in column A and Close in column B. For a 200-day average, the first full value is in row 201.

Column Name Formula in row 201 (copy down) What it does
C SMA 200 =AVERAGE(B2:B201) 200-day average of the close
D Signal =IF(B201>C201,1,0) 1 = hold gold, 0 = flat
E Next change =B202-B201 Tomorrow’s price change
F P&L =D201*E201 Profit or loss from holding tomorrow
G New entry =IF(AND(D201=1,D200=0),1,0) 1 on the day a new trade starts
H Cost =-G201*0.30 $0.30 per ounce for each new entry
I Equity =I200+F201+H201 Running total (set I200 to 0)
J Peak =MAX(J200,I201) Highest equity so far (set J200 to 0)
K Drawdown =I201-J201 How far below the peak you are

The look-ahead trap

Notice that column F multiplies today’s signal by tomorrow’s change (column E looks one row ahead). That is correct: you only know today’s signal at today’s close, so it can only earn tomorrow’s move.

The classic mistake is =D201*(B201-B200), which uses today’s signal on today’s move. That assumes you knew the close before it happened, and it makes almost any strategy look brilliant.

Step 4: Summarise the results

Put these in a small results box:

Result Formula
Net profit per ounce =SUM(F:F)+SUM(H:H)
Number of entries =SUM(G:G)
Days in the market =SUM(D:D)
Maximum drawdown =MIN(K:K)
Buy and hold last close minus first close of the test

For win rate per trade, you need one row per trade. A simple way: add a column that adds up F while D = 1 and resets when a new trade starts, then copy the final value of each trade to a separate sheet. Then use =COUNTIF(range,">0")/COUNT(range).

Use the expectancy calculator with your average win, average loss and win rate.

The worked example: ten years of gold

We ran exactly this kind of spreadsheet logic on our own COMEX gold futures (GC=F) daily closes, from 4 January 2016 ($1,075.1) to 11 November 2026 ($4,000.5), 2,731 trading days, with $0.30 per ounce per entry.

Rule Entries Time in the market
Hold above the 50-day SMA 89 59.2%
Hold above the 200-day SMA 39 72.2%
Buy and hold 1 100%

XAUUSD Global calculation on COMEX gold futures daily closes via Yahoo Finance. One ounce, no leverage, no swap. Past results do not predict future results.

What the example teaches

  • Your sheet answers the question you ask it. Here the question was simple: “How often does this rule enter, and how much of the time is it in the market?” Build the columns, fill them down, and the summary row answers it in seconds. Add the profit, drawdown and win-rate rows to see the rest for yourself.
  • The filters spent much less time in the market. A trend filter steps out when gold drops below its average and back in when it recovers. Whether that helps depends on what you care about. If you care about not holding through deep falls, compare the maximum drawdown columns of each rule.
  • Trend filters have many small entries and a few long trends. Expect a lot of short, small trades and some long ones. That is normal for trend-following and is why the quiet stretches feel so long.
  • This is a daily, long-only, one-ounce example. On a leveraged CFD you would also pay swap every night you hold.
  • Results depend on settings, timeframe and costs. A 50-day and a 200-day filter on the same data behave differently, and a different decade could change the picture again. Test it on your own data before risking money.

The point is not the result. The point is that a spreadsheet gave an honest, checkable answer in an hour.

Testing a stop-and-target rule

Rules with a stop-loss and a target need the High and Low columns:

  • Stop hit (for a buy): =IF(Low<=Stop,1,0)
  • Target hit: =IF(High>=Target,1,0)
  • Both hit in the same candle? On a daily or hourly candle you cannot know which came first. Assume the stop hit first. That is the cautious choice.

This “which came first” problem is the biggest limit of spreadsheet backtests on gold, because gold often has long wicks. See why gold has big wicks.

Common Excel backtest mistakes

  1. Look-ahead: using today’s signal on today’s move.
  2. No costs: add spread and commission per trade; for leveraged CFDs, add swap.
  3. Testing only the good years: include trending and ranging periods.
  4. Changing settings until it looks good: keep the last 30% of data untouched, then test once on it.
  5. Too few trades: under about 30 trades, results are mostly luck.
  6. Mixing data sources: futures, spot and different brokers’ prices differ a little. Use one source.

Our guide is my trading strategy really working? explains how to judge the results fairly.

After the backtest

  1. Forward test the same rules on demo for at least a month.
  2. Journal each trade. Our free trading journal spreadsheet works alongside your backtest file.
  3. Compare live results with the backtest. If they are much worse, costs or execution are the reason.

Key takeaways

  • Excel or Google Sheets can backtest simple daily or hourly gold rules with a few columns.
  • Export data from MT5: Symbols → Bars → Export.
  • Apply each signal to the next row’s change to avoid look-ahead.
  • Always include costs, and measure maximum drawdown, not just profit.
  • In our ten-year example, the 50- and 200-day filters spent much less time in the market than simply holding. Whether that helps is what your drawdown column tells you.

Sources

  • Worked example: XAUUSD Global calculation on COMEX gold futures (GC=F) daily closes via Yahoo Finance, 4 January 2016 to 11 November 2026, $0.30 per ounce per entry, one ounce, no leverage.
  • MT5 data export: MetaTrader 5 platform, Symbols window, Bars tab (MetaQuotes help).

Educational content only, not financial advice. Trading gold with leverage carries a high risk of loss.

Frequently asked questions

Can you backtest a trading strategy in Excel?

Yes, for simple rules on daily or hourly candles. Put one candle per row, build columns for the indicator, the signal, the position, the profit or loss, the cost and the running total, then summarise the results. For complex intraday rules or many thousands of rows, a platform tester or code is easier.

Where do I get gold data for an Excel backtest?

The easiest free source is your own MT5 platform. Open the Symbols window, choose your gold symbol, go to the Bars tab, pick the timeframe and dates, request the data and export it as a CSV file, which Excel and Google Sheets open directly.

What is look-ahead bias in an Excel backtest?

It is using information you would not have had at the time. The classic spreadsheet mistake is applying today’s signal to today’s price change. The signal is only known at today’s close, so it must be applied to tomorrow’s change. Shift the profit column down one row.

How do I add trading costs in Excel?

Add a column that marks each new entry (1 when the position changes from flat to in a trade) and multiply it by your cost per trade in dollars per ounce, such as $0.30. Subtract that from the running total. Without costs, most backtests look much better than real trading.

How many trades do I need in an Excel backtest?

Aim for at least 100 trades across different market conditions, both trending and ranging. With fewer than about 30, the result is mostly luck.

Can I use Google Sheets instead of Excel?

Yes. All the formulas in this guide, AVERAGE, IF, AND, MAX, MIN, SUM and COUNTIF, work the same way in Google Sheets. Large hourly files may be slow in Sheets.

Comments

No comments yet.

Leave a comment

Comments are checked before they appear. Be respectful: no promotion, no trade signals. Rules · Privacy. No email needed.