pandas resample('5min') on M1: which bars land in the 10:05 candle, and the label trap

[Fairman]

pandas resample('5min') on M1: which bars land in the 10:05 candle, and the label trap

If you build M5 bars from M1 data in pandas and compare them with the platform's chart, a one bar difference is common. This is the cause.

I used a toy frame with the M1 open prices 1.0, 2.0, up to 12.0 starting at 10:00, so you can see which bar went where.

Code: Select all

ohlc = {"open": "first", "high": "max", "low": "min", "close": "last"}
m5 = m1.resample("5min").agg(ohlc)
m5r = m1.resample("5min", label="right", closed="right").agg(ohlc)

The default is left labelled and left closed. The bar stamped 10:05 contains M1 bars 10:05 to 10:09, and its open is 6.0, the open of the 10:05 bar. That's what MT5 and most platforms show, since a bar is stamped with its opening time.

With label and closed both set to right, the bar stamped 10:05 contains 10:01 to 10:05, opens at 2.0, and the very first 10:00 bar sits alone in a bucket labelled 10:00. At the end of the data there is a partial bucket labelled 10:15 holding only the 10:11 bar. Every M5 candle then differs from the platform's, and a strategy that looks at closes sees a different number.

It goes wrong when someone copies a snippet for daily or weekly data, where right labelling is conventional, and uses it for intraday bars.

Check one bar by hand against the chart before running anything on the full data.

[Fairman]

The other trap is the timezone. MT5 server time is often GMT+2 or GMT+3 depending on the daylight saving period, and the CSV you export is in that time. If you convert to London time before resampling, the 5 minute edges are unaffected, but a daily resample is, because the daily bar then starts at midnight in the converted zone and not at the platform's day start. A daily candle in pandas stamped at 00:00 London doesn't match a broker's daily candle that begins around 22:00 London. Decide which day boundary your rules use before you resample.

[Fairman]

Incomplete bars are the third item. If the last M5 bar in your data has only 2 of its 5 M1 bars, it will look like a small candle. If a live strategy acts on it as if it were complete, the signal is wrong. A simple guard is to count the M1 bars per M5 bucket with .agg and a size column, then drop buckets with fewer than 5 when the data is meant to be continuous. For FX that's the rule everywhere except around the weekend and a few holidays, where gaps are legitimate and need their own handling.

[Fairman]

To verify without trusting your memory, write a test with five M1 bars you can compute by hand: opens 1 to 5, highs 1.5 to 5.5, lows 0.5 to 4.5, closes 1.2 to 5.2. The M5 candle should open at 1, high at 5.5, low at 0.5 and close at 5.2. Run the resample on that frame and assert on those four numbers. It takes a few minutes, and it will fail the moment someone changes the label or closed arguments, so you find out in the test rather than in the equity curve.

[Fairman]

One last point: volume and spread columns need their own aggregation. Summing the tick volume is right for volume, but spread should be averaged or take its max, depending on what you want to know. A column you don't name in the agg dict simply won't appear in the result, so list every column explicitly. A worked example: three M1 bars with spreads of 0.6, 0.7 and 2.4 pips give a mean of 1.23 and a max of 2.4, and the max is what you'd want to filter on, since a mean of 1.23 hides the 2.4 pip bar in the middle of a news spike.