forex-scalping.com

Exporting MT5 history to a spreadsheet: Deals against Positions, and where R comes from

In MT5, the History tab in the Toolbox can show Positions, Orders or Deals, and the choice changes what you get when you export.

Positions: one row per position, with open time, close time, volume, entry and exit prices, stop loss and take profit, commission, swap and profit. For a journal that's the one to use.
Deals: separate rows for the entry deal and the exit deal, each with its own commission and swap. Useful for reconciling costs, awkward for a journal.

To get it out, right click in the History tab and choose Report, then an HTML or an Excel compatible option. The wording differs between builds. Copying the rows with Ctrl+A and Ctrl+C and pasting into a sheet also works.

R can't come from the export alone. The S/L column shows the stop as it was when the position closed, so if you trailed it or moved it to breakeven, the original is gone. Record the initial stop yourself at entry.

Then, per position: risk in dollars equals volume times stop distance in pips times the pip value. Take 1.25 lots, entry 1.08030, initial stop 1.07950, so 8 pips and $100 of risk. Profit $150, commission $8.75 (at $7 per lot round turn), net $141.25, which is 1.41R.

Positions view is the right choice for a journal. One gotcha: the exported stop loss is the final value, not the one at entry. If the stop was moved to breakeven, R calculated from the exported stop will be wrong. For R to be meaningful, the initial stop needs recording at entry, either in the order comment or in a separate log. Some traders put the initial risk in pips in the comment field so it survives the export.

The times in the export are server time, not UTC and not your local time. Brokers usually use a GMT+2 or GMT+3 server with changes around the US daylight saving dates, so a trade shown at 16:30 may have happened at 14:30 UTC or 13:30 UTC depending on the season. For a journal that sorts trades by hour, the conversion matters, since an hour of error moves a London open trade into the Asian session in your statistics. Make a column for the server offset on each date and compute the UTC time from it, and keep the original server time column next to it.

The initial stop issue is the one that ruined my first attempt at an R based journal. I exported three months of positions, calculated R from the S/L column and got a beautiful result, then realised half my trades had been moved to breakeven so the stop distance was tiny and the R values were huge. The whole sheet was fiction.

Now I type the initial stop distance in pips into the order comment every time, like 8SL or 12SL. It takes two seconds and it survives the export, so I can split it out with a formula in the spreadsheet. On the odd trade where I forget, I go back to the M1 chart and check where the stop was when I opened.

The server time point is worth stressing too. My broker runs on GMT plus two, and for a long time my hour by hour stats showed a great 10:00 session that was really the London open. Adding a UTC column fixed my view of which hours actually make money.

Commission can appear in different places depending on the broker. Some charge it entirely on the entry deal, some split it between entry and exit, and the Positions view may sum both. Swap sits on the position. Check on a few trades by comparing the Positions row with the two deals behind it. If the commission in the position row equals the sum of the deal commissions, you can rely on the Positions view for net results. If it doesn't, the difference tells you which deal the broker puts the fee on, and where to look when a number doesn't make sense.

Reconcile the sheet against the balance. Add up profit, commission and swap over all the rows for a month and compare it with the change in balance between the first and last day, corrected for deposits and withdrawals. They should agree to the cent. If they don't, something is missing: a deposit entered as a trade, a dividend adjustment on an index CFD, a rebate, or a trade that is still open. A discrepancy of a few dollars suggests fees or an adjustment, and a big one suggests a missing row. It takes a few minutes and is the best check that the journal matches the account.

Add the setup name and the reason as columns in the same sheet, and keep the order of the rows the same as the export. A sheet that mixes your notes with the exported columns becomes hard to refresh. A safer layout puts the raw export on one tab and a second tab with formulas pulling from it by ticket number, where your own columns live. Next month's export can then be pasted over the first tab without touching your notes. The ticket number is the key. It stays the same across exports and can be looked up with VLOOKUP or XLOOKUP.