Frequently (and quite practical) question for MetaTrader users is «How to copy quotes in Excel?» . With the help of the library ATcl this is programmed quite easily. The library includes a «large» program working with Excel, but for frequent needs made a short script that makes the simplest thing - exporting data from MT4 to a table.
You will need - ATcl library and the script that is discussed in this article. In the next versions it will be included in the distribution. The script attached to the article should be placed in the terminal's script directory (do not forget to copy, only the text is applied), and then for exporting quotations - just drag it to the desired schedule.
The script is based on the package Cawt - automation of access to MS-Office. Documentation for all its functions: http://www.cawt.tcl3d.org/download/CawtReference.html
Of all the abundance of the Cawt functional, we only need:
- Excel Open - open a new Excel
- Excel AddWorkbook - in the already opened exe create a new book
- Excel GetWorksheetIdByIndex - select a sheet by serial number
- Excel SelectRangeById - select a range of sheet cells
- Excel SetRangeValues - fill the range with values
- Cawt Destroy - removing previously created COM objects
For a complete description of these functions, see the Cawt documentation, and their use is most commented on in the code
A few words about the application of ATcl in the script. Mainly used methods
- Set - set the value of the named variable, which can then be substituted into the following expressions
- Eval - performs variable substitution and executes scripts
at the initial stage you can generally do just these two functions when writing your programs.
The basis of the script RatesToExcel is the function:
int CopyRatesToExcel(const MqlRates &rates[],int pos=0,int count=WHOLE_ARRAY) { // create new interp. ATcl *tcl=new ATcl; if (tcl==NULL) return -1; // objects Tcl_Obj Excel=0; // Excel instance Tcl_Obj Workbook=0; // book Tcl_Obj Worksheet=0; // spreadsheet Tcl_Obj Range=0; // cell range Tcl_Obj data=0; // data to export // consistently execute commands // note: do {...} while(0); It is necessary not to make many nestings {{{}}} do { // if there is a Cawt package in your tcl distribution system, then comment follows command // specify the path to the libraries included in ATcl tcl.Eval("lappend auto_path [ file join [pwd] MQL4 Libraries ATcl lib ]"); // requires Cawt package if (tcl.Eval("package require cawt")!=TCL_OK) { // if there was some error, // then its descriptions can be obtained as Result () as an object // or StringResult () as a string PrintFormat("package Cawt error %s",tcl.StringResult()); break; } // opening Excel // At the same time we create an Excel variable in the interpreter for substitutions like $ Excel if (tcl.Eval("set Excel [ Excel Open ]")!=TCL_OK) { PrintFormat("[ Excel Open ] error %s",tcl.StringResult()); break; } Excel=tcl.Ref(tcl.Result()); // save the result, then call the correct destructor // open empty WorkBook // (it will be created immediately with one sheet) if (tcl.Eval("set Workbook [ Excel AddWorkbook $Excel ]")!=TCL_OK) { PrintFormat("[ Excel AddWorkbook ] error %s",tcl.StringResult()); break; } Workbook=tcl.Ref(tcl.Result()); // to then call the correct destructor // select Spreadsheet (tab) if (tcl.Eval("set Worksheet [ Excel GetWorksheetIdByIndex $Workbook 1 ]")!=TCL_OK) { PrintFormat("[ Excel GetWorksheetById ] error %s",tcl.StringResult()); break; } Worksheet=tcl.Ref(tcl.Result()); // to then call the correct destructor // prepare data // it was possible to do with built-in Obj (const MqlRates & [], ..) but it will not format the time data=tcl.Ref(tcl.Obj()); // there will be a list of lines // first line - column headers tcl.AppendObj(data,tcl.List( tcl.Obj("time"), tcl.Obj("open"), tcl.Obj("high"), tcl.Obj("low"), tcl.Obj("close"), tcl.Obj("tick_volume"), tcl.Obj("spread"), tcl.Obj("real_volume") )); // fill in the data int total=ArraySize(rates); if (count==WHOLE_ARRAY) count=total; for(int i=0;i<count && i+pos<total;i++) { // add lines (rows) tcl.AppendObj(data,tcl.List( tcl.Obj(TimeToString(rates[i+pos].time,TIME_DATE|TIME_MINUTES|TIME_SECONDS)), tcl.Obj(rates[i+pos].open), tcl.Obj(rates[i+pos].high), tcl.Obj(rates[i+pos].low), tcl.Obj(rates[i+pos].close), tcl.Obj(rates[i+pos].tick_volume), tcl.Obj(rates[i+pos].spread), tcl.Obj(rates[i+pos].real_volume) )); } // select cells tcl.Set("Row",1); tcl.Set("Col",1); tcl.Set("EndRow",total); tcl.Set("EndCol",8); if (tcl.Eval("set Range [ Excel SelectRangeByIndex $Worksheet $Row $Col $EndRow $EndCol ]")!=TCL_OK) { PrintFormat("[ Excel SelectRangeByIndex ] error %s",tcl.StringResult()); break; } Range=tcl.Ref(tcl.Result()); // to then call the correct destructor // copy data to cells tcl.SetObj("Data",data); if (tcl.Eval("Excel SetRangeValues $Range $Data")!=TCL_OK) { PrintFormat("[ Excel GetWorksheetById ] error %s",tcl.StringResult()); break; } } while(0); // delete all created objects if (Range!=0) { tcl.Eval("Cawt Destroy $Range"); tcl.Unref(Range); } if (Worksheet!=0) { tcl.Eval("Cawt Destroy $Worksheet"); tcl.Unref(Worksheet); } if (Workbook!=0) { tcl.Eval("Cawt Destroy $Workbook"); tcl.Unref(Workbook); } if (Excel!=0) { tcl.Eval("Cawt Destroy $Excel"); tcl.Unref(Excel); } if (data!=0) { tcl.Unref(data); } delete tcl; return 0; }
The full script calling this function is attached to the article. ratestoexcel.mq4
Similar methods you can not only send data to Excel but also read them, create tables, similarly refer to other MS-Office programs. That is, using the ATcl library to integrate its advisors, indicators and scripts with the usual environment for the trader.
If you are well versed with system services, then with ATcl you can integrate your programs not only with office applications, but generally with the entire system - for this there is a package TWAPI providing access to the Windows API. The Cawt mentioned above is built on the basis of this library and its functions are available to you.
If you are interested in a project, you can help financially
other ways: donate
if possible, specify the features that are most interesting for you
In consequence of BAN, no orders through freelancing are possible,
now only exclusively through personal contacts in the profile.
and my products on the market are apparently suppressed in rotation,
but you can read them on the display: https://www.mql5.com/users/nektomk/seller
About ATcl
The ATcl library provides a compact API for the full use of Tcl:
- You can use in any MQL program: script, indicator or expert
- in one program multiple interpreters can be used, including Safe
- simple conversion of Mql data types to Tcl objects and back. (string, long, double)
- similarly supports arrays and Mql series
- management of Tcl objects - creation, deletion, duplication, obtaining of nested elements
- creating, reading, installing, and deleting variables and hashes for Tcl
- execution of Tcl scripts and procedures
- Object design - the library is represented by a class
- It's easy to install - just unpack the archive
Expand the capabilities of your MQL programs, use DBMS, supplement with network tools, integrate them with any programs using Tcl.
Original ATcl site is http://nektomk.ru/en:atcl:start
Partial duplicated in SourceForge: https://sourceforge.net/projects/mt-atcl/