Pulling history from MT4 into Excel?
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
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?
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.
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 . . .
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...
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)))); }
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
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