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
- In MT5, open View → Symbols (or press Ctrl+U).
- Select your gold symbol (XAUUSD, GOLD or similar).
- Open the Bars tab.
- Choose the timeframe (D1 or H1 to start) and the date range, then click Request.
- Click Export Bars and save the CSV file.
- 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
- Look-ahead: using today’s signal on today’s move.
- No costs: add spread and commission per trade; for leveraged CFDs, add swap.
- Testing only the good years: include trending and ranging periods.
- Changing settings until it looks good: keep the last 30% of data untouched, then test once on it.
- Too few trades: under about 30 trades, results are mostly luck.
- 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
- Forward test the same rules on demo for at least a month.
- Journal each trade. Our free trading journal spreadsheet works alongside your backtest file.
- 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.
More in Strategies & technical analysis
- Hull Moving Average on Gold (XAUUSD): Settings, Signals and a Test Against the EMA
- Best MACD Settings for Gold (XAUUSD): 12/26/9 vs Faster and Slower, by Timeframe
- SMC vs ICT: What Is the Difference, and Which Should Gold Traders Learn First?
- Fair Value Gap vs Order Block: The Difference, Which Comes First, and How Gold Traders Use Both
- The ICT Mentorship Model on Gold: Sweep, Displacement and Fair Value Gap Entry, Step by Step
Latest on the blog
- Gold Down Days in a Row: What Happened Next After 4, 5 and 6 Straight Falls
- The Philly Fed Survey for Gold Traders: Diffusion Index, Prices Paid and the Thursday Reaction
- NR7 Breakout on Gold: How the Narrowest Day of the Last Seven Behaves
- What Is the Fed Beige Book? A Gold Trader's Guide to District Reports, Price Clues and Market Reaction