forex-scalping.com

Average M5 candle range by hour: a ten minute spreadsheet job and what it changes

You don't need special software to learn how big an M5 candle usually is at each hour. In MT5, open View, then Symbols, then the Bars tab, pick EURUSD and M5, request a few months, and export to CSV. Open it in a spreadsheet.

Add a column called Range with =(High-Low)*10000 for EURUSD, or *100 for yen pairs. Add a column with =HOUR(Time). If your broker's server time isn't UTC, add or subtract the offset first, so the hours mean something to you. Then =AVERAGEIFS(Range, Hour, 7) for each hour from 0 to 23, or make a pivot table with hour down the side.

Illustrative figures for a quiet pair, not a measurement of any broker:
02:00 UTC: 2.4 pips
07:00: 5.6 pips
08:00: 6.4 pips
13:00: 8.1 pips
18:00: 3.4 pips
21:00: 2.0 pips

Now the spread. A 0.6 pip cost is 25% of the average candle at 02:00 and 7% at 13:00. A 4 pip stop is nearly two whole candles of room at 02:00, and half a candle at 13:00. The same stop distance means very different things at different hours, and so does the same cost.

Once the table exists, decisions about stop size, which hours to skip and which pairs fit a session can be argued from numbers rather than from how the chart felt that day.

Fairman, the hourly average candle range is one of the most useful ten minute jobs a scalper can do. A natural next column is the median alongside the average, since a few news bars can inflate the average for a given hour. If the two differ a lot, the hour is driven by occasional spikes rather than steady movement, which matters for how a target is chosen.

Use the median alongside the average, because news bars drag the mean. Suppose in 20 sessions the 12:00 hour has 19 bars of 4.5 pips and one NFP bar of 30. The mean is (19 times 4.5 plus 30) divided by 20, which is 5.8 pips, but the typical bar is 4.5. In a spreadsheet the median by hour needs an array formula such as =MEDIAN(IF(Hour=12,Range)), confirmed with Ctrl+Shift+Enter in older Excel. Adding an 80th percentile column tells you how large the candle gets when it's active, which is the number a stop has to survive. Three columns, average, median and 80th percentile, are enough.

Server time moves around, so convert before you trust the hour column. Many brokers run servers on New York close time, UTC+3 in the US summer and UTC+2 in the US winter, and they change on the US date. In 2026 the UK goes back on 25 October and the US on 1 November. So the gap between London and the server is 2 hours until 25 October, 3 hours from 25 October to 1 November, then 2 again. If your data spans those dates, a London 08:00 bar sits at server hour 10, 11 or 10 depending on the week. Convert each row to UTC with a date check and the problem disappears.

Slice it a second way once there's enough data. One year of M5 bars is roughly 75,000 rows, and a sheet can handle that. Add a weekday column and look at hour by weekday, but keep at least 40 or 50 bars in every cell before reading anything into it. Hour 13 on a Friday has about 12 bars a week, so a year gives 600. Some hours will look odd because of the data calendar, such as US jobs on the first Friday or CPI mid month. Fridays after 16:00 London often show smaller ranges than Tuesdays at the same hour, and the table will show whether that's true for your pair.

Pip scaling needs care across instruments. Multiplying by 10,000 works for EURUSD and by 100 for USDJPY, but a pip on gold is whatever your broker's digits make it, so use dollars of range for XAUUSD instead. Compare pairs by dividing each hour's range by the pair's own daily average, which gives a 0 to 3 style index with 1.0 meaning an average hour. That puts EURGBP and GBPJPY on one page. A candle that's a 0.4 on one pair and a 2.1 on another tells you where the opportunity is at that hour, without arguing about pip definitions.