MT4 to Excel

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.

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