Summary: Fix date formatting and missing data gaps when exporting MT4 account history to Excel. Includes FXT file structure insights and timezone adjustment tips from MQL4 docs.




Last Thursday night, right before the NFP news, I needed to pull three months of tick-by-tick history from my MT4 terminal to run some correlation analysis in Excel. Should've been a five-minute job. Two hours later, I was staring at a spreadsheet where half the timestamps showed up as "######" and the EURUSD data had a mysterious two-hour gap right in the middle of the London session.

The built-in "Save as Report" function in MT4 is fine for basic stuff, but if you need raw, unfiltered history for serious number crunching, it falls short. My usual workflow is to go to the "History Center" (F2), select the symbol, and export to CSV. Sounds simple. But here's what nobody tells you.

The first time I opened that CSV in Excel, the dates in the "Time" column were completely scrambled. Excel was interpreting the MM/DD/YYYY format as DD/MM/YYYY, or vice versa, depending on your regional settings. Then there was the timezone nightmare. The timestamps in the exported CSV are stored in server time (which is usually GMT+2 or GMT+3 depending on daylight savings), but my Excel was set to my local timezone. So every single trade's open and close times were off by hours.

I remembered skimming through the MQL4 Documentation (docs.mql4.com) section on "File Functions" a while back. There's a small subsection on FileWrite and FileRead that mentions how the TIME_DATE and TIME_MINUTES flags handle datetime values. What stuck with me was a single sentence: "When writing datetime values to a CSV file, the format depends on the current terminal's timezone settings." That was the clue.

So I stopped relying on the manual export and wrote a small script to pull the data directly using HistorySelect() and HistoryOrderGetDouble(). This gave me raw integer timestamps instead of formatted strings. I could then manually add the timezone offset. Here's what that script looked like:

``mql4
void OnStart()
{
int handle = FileOpen("MyHistory.csv", FILE_CSV|FILE_WRITE, ",");
if(handle == INVALID_HANDLE) return;

HistorySelect(0, TimeCurrent());
int total = HistoryDealsTotal();

FileWrite(handle, "Ticket", "Time", "Type", "Volume", "Price");

for(int i=0; i {
ulong ticket = HistoryDealGetTicket(i);
datetime openTime = (datetime)HistoryDealGetInteger(ticket, DEAL_TIME);
long type = HistoryDealGetInteger(ticket, DEAL_TYPE);
double volume = HistoryDealGetDouble(ticket, DEAL_VOLUME);
double price = HistoryDealGetDouble(ticket, DEAL_PRICE);

// Manually adjust for GMT+3 (summer time)
openTime += 10800; // 3 hours in seconds

FileWrite(handle, ticket, TimeToString(openTime, TIME_DATE|TIME_MINUTES), type, volume, price);
}
FileClose(handle);
}
`

That fixed the timestamp issue, but then I ran into a completely different problem. The gap. When I plotted the equity curve, I noticed a straight line connecting two points that were two hours apart, with no trades in between. The data was literally missing.

After digging around on the MetaQuotes help center (help.metaquotes.net), I found a forum thread where a developer mentioned that the History Center export only shows "active" trades from the current terminal cache. If you've ever done a "Compact" operation on your database, or if your broker uses custom FXT file compression, some older tick data might be archived and not visible in the standard export. The hidden trick here is to go to Tools > Options > Charts and increase the "Max bars in history" and "Max bars in chart" values to something like 5,000,000 before you open the History Center. Then, before exporting, right-click inside the History Center list and select "Refresh" to force the terminal to reload all FXT data from the server. This forces MT4 to pull the full dataset from the .hst files, not just what's currently loaded in memory.

One more thing about Excel: If you're still getting the "######" errors, it's because Excel's column width is too small, but more importantly, if your datetime format still looks like a number (e.g., 44927.5), that means Excel is treating it as a serial date. You need to select the column, go to Format Cells > Custom, and type yyyy-mm-dd hh:mm:ss`. If that doesn't work, check your Windows Region settings—Excel uses the system's default date separator.

The biggest lesson I've learned from all this? Never trust the visual preview in the History Center. Always write a quick MQL4 script to pull raw data when you're doing serious backtesting prep. It takes ten minutes to set up and saves you hours of manually cleaning corrupt data.

Reference: MQL4 Documentation – "File Functions" (docs.mql4.com) and MetaQuotes Help Center – "History Center Export" (help.metaquotes.net)

---

This article was originally published on FXEAR.com. All rights reserved.