Pulling history from MT4 into Excel?

To add comments, please log in or register
Dominic Gilbert
1642
Dominic Gilbert  

Hey All,

Starting off very broad here, but is is possible (maybe with the help of a third party app) to pull the closed trades in the history log automatically into an excel spreadsheet?

Cheers,

-Dom

Simon Gniadkowski
17599
Simon Gniadkowski  
DomGilberto:

Hey All,

Starting off very broad here, but is is possible (maybe with the help of a third party app) to pull the closed trades in the history log automatically into an excel spreadsheet?

https://www.mql5.com/en/forum/146248

https://www.mql5.com/en/forum/129072

https://www.mql5.com/en/forum/140506

Dominic Gilbert
1642
Dominic Gilbert  
Ok read them, thanks.

Can you tell me if I am able to use FileOpen / FileWrite in conjunction with an excel spreadsheet that I have already mapped out? Basically, I am wanting to be able to use these functions, but then actually place the specific trade history details in a predefined area within the specific spreadsheet I want?
Simon Gniadkowski
17599
Simon Gniadkowski  
DomGilberto:
Ok read them, thanks.

Can you tell me if I am able to use FileOpen / FileWrite in conjunction with an excel spreadsheet that I have already mapped out? Basically, I am wanting to be able to use these functions, but then actually place the specific trade history details in a predefined area within the specific spreadsheet I want?
.xls is a proprietary format, you can write to .csv but if excel has the file open your mql4 won't be able to write to it.
Alain Verleyen
36041
Alain Verleyen  
RaptorUK:
.xls is a proprietary format, you can write to .csv but if excel has the file open your mql4 won't be able to write to it.

Free dll exists to write directly into a Excel sheet. The OP can search for it if it's what he needs.
Simon Gniadkowski
17599
Simon Gniadkowski  
angevoyageur:
Free dll exists to write directly into a Excel sheet. The OP can search for it if it's what he need.
Correct me if I'm wrong . . . but that "writes" to Excel not to an Excel file, it may do what the OP wants but it isn't what he was talking about in his last post, File Open/File Write reference . . .
Alain Verleyen
36041
Alain Verleyen  
RaptorUK:
Correct me if I'm wrong . . . but that "writes" to Excel not to an Excel file, it may do what the OP wants but it isn't what he was talking about in his last post, File Open/File Write reference . . .

As you said, one can't write an xls file from scratch. But with a dll (wrapping excel API) you can open excel, open a spreadsheet, fill in this one with values (or even formulas) and then save the spreadsheet. I have used 2 different dll with these possibilities.
Alain Verleyen
36041
Alain Verleyen  
cyclops993
115
cyclops993  

DomGilberto:
Ok read them, thanks.

Can you tell me if I am able to use FileOpen / FileWrite in conjunction with an excel spreadsheet that I have already mapped out? Basically, I am wanting to be able to use these functions, but then actually place the specific trade history details in a predefined area within the specific spreadsheet I want?

You can do it using only file reads and writes by taking advantage of Excel's ability to save workbooks in pure XML format (i.e. a textual, non-binary format).

Filling in specific cells in a template is then relatively easy. Creating new rows, e.g. in order to display a variable-length list of trades, is harder but still possible.

You create the template by saving the workbook in "XML Spreadsheet (*.xml)" format. What you have then is a text file (which can be opened in Notepad etc). You can also change the extension of the saved file from .xml to .xlsx so that Windows knows to associate it with Excel.

In MT4 you then read in the template file, replace any variables with the values you want, and write the modified text out to a new file. For example, you could put the variable {BALANCE} into a cell in the template, and use the MT4 code to scan the text it is reading in, and replace the text {BALANCE} with the value of the balance before writing the modified text to the new file.

The only issue with that simple route is that you need to read and write the files in FILE_BIN mode (rather than CSV mode). MT4 has a 4KB limit on file reads. You can read the file in multiple chunks, but you need to watch out for your markers being split over a 4KB boundary - e.g. in one read the text ends with {BALA, and in the next read the text starts with NCE}

Creating extra rows is relatively straightforward: at a predefined point you simply need to add extra <Row> entries into the text you are writing to the output file. However, you also need to set the ss:ExpandedRowCount property of the containing <Table> to the new number of rows...

cyclops993
115
cyclops993  
cyclops993:

You can do it using only file reads and writes by taking advantage of Excel's ability to save workbooks in pure XML format (i.e. a textual, non-binary format).

For example...

You save the Template.xslt in the attached zip file into the experts\files subdirectory of MT4, and then run the following script. This reads the template, replaces the markers {BALANCE} and {EQUITY}, and writes the revised spreadsheet into a file called Output.xslt in experts\files

int start()
{
   bool bSuccess = false;
   
   int InputFile = FileOpen("Template.xlsx", FILE_READ | FILE_BIN);
   if (InputFile <= 0) {
      MessageBox("Template file is missing from experts-files directory");
   } else {
      int OutputFile = FileOpen("Output.xlsx", FILE_WRITE | FILE_BIN);
      if (OutputFile <= 0) {
         MessageBox("Unable to open output file");
      } else {
         if (FileSize(InputFile) > 4095) {
            MessageBox("This example cannot process template files which are larger than 4KB. You need to alter the example to read (and write) the file in multiple chunks.");
         } else {
            string strText = FileReadString(InputFile, 4095);
            if (StringFind(strText, "<Workbook") < 0) {
               MessageBox("Template file is not a valid XML Spreadsheet");
            } else {
               // Do the replacements
               strText = DoStringReplacement(strText, "{BALANCE}", DoubleToStr(AccountBalance(), 2));
               strText = DoStringReplacement(strText, "{EQUITY}", DoubleToStr(AccountEquity(), 2));
               
               // Write the modified text to the output file 
               FileWriteString(OutputFile, strText, StringLen(strText));
               bSuccess = true; 
            }
         }
         FileClose(OutputFile);
      }
      FileClose(InputFile);
   }
   
   // Only display the success message after the file handles have been closed and the new file can
   // therefore be opened in Excel
   if (bSuccess) {
      MessageBox("Successfully created output file. (Could use Win32 call ShellExecuteA to open it in Excel automatically)");
   }

   return(0);
}

// Generic function for doing a single text replacement inside a string
string DoStringReplacement(string InputText, string LookFor, string ReplaceWith)
{
   int pos = StringFind(InputText, LookFor);
   if (pos < 0) return (InputText);
   return (StringConcatenate(StringSubstr(InputText, 0, pos) , ReplaceWith , StringSubstr(InputText, pos + StringLen(LookFor))));
}
Files:
To add comments, please log in or register