Building M1 bars from bid ticks or from mid prices in pandas

[Fairman]

Building M1 bars from bid ticks or from mid prices in pandas

If you collect tick data and build your own bars, the price you use changes the results more than people expect.

Most retail platforms draw charts from the bid. So the M1 bars in MT5 are bid bars. If you build bars from mid prices, the average of bid and ask, your bars sit half a spread higher. If you build them from the ask, a full spread higher.

That matters for any test that uses bar levels to decide entries. A long entry on a breakout of the M1 high using bid bars would actually fill at the ask, so the real entry is a spread above the bar high. Using mid bars, it's half a spread above.

In pandas, with a DataFrame of ticks indexed by timestamp and columns bid and ask:

bars = df['bid'].resample('1min').ohlc()
ask_bars = df['ask'].resample('1min').ohlc()

Keep both. Then simulate longs entering at the ask and exiting at the bid, and shorts the reverse. That reproduces the real cost without guessing a fixed spread.

A small detail: resample with the default settings labels each bar by its start time and includes ticks from the start up to but not including the next minute. MT5 does the same, so the bars should line up if your timestamps are in server time.

Empty minutes produce NaN rows. I drop them with dropna rather than forward filling, because a minute with no ticks is information.

[FXS HongKong Scalper]

The bid versus mid choice is often overlooked. Another detail is how to handle ticks where only the ask changes. Some feeds send ticks with the bid unchanged, which do not change bid bars but do change mid bars. If the test uses tick volume from the bars, the count can differ between the two methods. Keeping both bid and ask bars, and using bid for short entries and ask for long entries, is the most accurate approach, even if it means storing more data.

[Fairman]

Tick volume is easy to add at the same time. df['bid'].resample('1min').count() gives the number of ticks per bar, which is what MT5 calls tick volume. It's not traded volume, but it does show when activity picks up. Keeping it in the bar file lets you filter tests to bars with some minimum activity, which helps avoid simulated trades in minutes when nobody was really trading. It also catches days when your feed had gaps. If the tick count drops to near zero for an hour in the London session, the data is missing, not quiet. Flag those days and leave them out of tests.

[Fairman]

Spread per bar is also useful. Compute the spread on each tick as ask minus bid, then take the mean and max per minute. A test can then refuse entries in minutes where the max spread was above a threshold, which is close to what a live spread filter would do. Without it, the test happily enters during rollover or news spikes at prices you'd never accept live. If your entry rules ignore spread, the test fills at prices a live account wouldn't accept. A filter of, say, no entries when the minute's max spread was over three times the median is close to what you'd do by hand.

[Fairman]

Timezone handling is where I've seen the most mistakes. If you save tick timestamps in UTC and then compare with MT5 bars in server time, everything will be offset by two or three hours. I'd store everything in UTC and convert MT5 data into UTC at load time using the broker's offset rules. One clear time standard across all files makes merging and testing much less error-prone. A test that looks great with mismatched times is usually a test that's reading the future by two hours. Checking a known event, like a 13:30 London release, against the bars is a quick way to catch it.

[Fairman]

Memory gets heavy with tick data. A month of EURUSD ticks can be tens of millions of rows. Reading files in chunks, resampling each chunk and concatenating the bars avoids loading everything at once. Saving the bars in parquet rather than CSV makes reloading much faster too. Keep the raw ticks archived, since you may want different bar types later, but test on bars whenever you can. Parquet also keeps dtypes, so timestamps don't get turned back into strings. Saving a separate small file of bar data per day is easier to manage than one huge file per month.