Python notebook: spread histogram by hour from an MT5 export

[FXS London Scalper]

A quick analysis that answers "when is my broker cheap?" with your own data.

Data collection: a simple MT5 script writes time, bid, ask to CSV every second (or on every tick) for two weeks.

Notebook outline:

Code: Select all

import pandas as pd
df = pd.read_csv('ticks.csv', names=['time','bid','ask'], parse_dates=['time'])
df['spread'] = (df.ask - df.bid) / 0.0001   # EURUSD pips
df['hour'] = df.time.dt.tz_localize('Etc/GMT-2').dt.tz_convert('Europe/London').dt.hour
summary = df.groupby('hour').spread.describe(percentiles=[.5,.9,.99])
summary[['50%','90%','99%']].plot(kind='bar')

Adjust the server timezone (many brokers run on GMT+2/+3). Getting this wrong shifts everything by two or three hours and leads to wrong conclusions.

What to look for:
- The hours where the median is lowest.
- The hours where the 99th percentile spikes, often more important than the median.
- Differences between Monday and the rest of the week.

If anyone runs this, post the chart (without broker name if you prefer). Comparing shapes across brokers is interesting.

[Shadow Trader]

A default platform export is built for accounting, not analysis. For scalping data, these fields tend to be missing or buried:

- Open and close timestamps down to the second, with the time zone stated. Server time is not local time, and daylight saving shifts it twice a year.
- Requested price versus filled price, when the platform exposes it. Without that pair, slippage cannot be measured at all.
- Spread at entry. Rarely exported, so it helps to record it separately or take a screenshot.
- Commission and swap as separate columns, not merged into profit. Mixing them hides what the strategy earns before costs.
- Order type for each leg, market, limit or stop, because they slip differently.
- Ticket numbers that match between the account history and the deal-level history. Partial closes can split one idea into several rows.

Deal-level history is generally more useful than the position-level view, since it shows each fill on its own. The position view can show a single combined average price that disguises what actually happened.

Exporting to CSV instead of HTML saves hours. HTML statements look tidy but are awkward to parse, and the rounding in them can differ from the underlying values.

One more small habit: save the raw export untouched and work on a copy. Edited columns and re-sorted rows have a way of destroying the original order of events, and that order is often the whole point when a fill looks strange.

Which columns does anyone add by hand to trade records that the platform export never includes?