Speed up all your Excel Operations

No Comments

Current Situation

Classic Set operations such as ExcelSetValue,ExcelSetString are slow because you have to call 1 functions per cell. Imagine you need to publish 1000 cells, you will have to call this function 1000 times. Does not sound very efficient. On other side functions ExcelSetFromArray/ExcelSetFromArrayH/ExcelSetFromArrayV/ExcelSEtFromParsedV/ExcelSEtFromParsedH either wont supported from all platforms or they can only work with numbers. If your trading platform supports and you need to Set numbers then use these functions. But what if you need to mix Numbers, Strings, Formulas? This can be a time and resource consuming coding with classic functions. Classic functions are for small amount of operations. As soon you need to publish many data, use either Array functions or ExcelSetFromParsedAuto function

ExcelSetFromParsedAuto Details

This function accepts a string as second parameter. This parameter contains all your data or part of them. Warning: Some platforms may not support very long strings, so you have to find out string lenght limitations and send only strings in that lenght as maximum lenght, this is your homework to find out string length limitation.

Second string can be seperated by \t (Tab) for new column and \n (NewLine) for new Row. Lets make some example:

ExcelSetFromParsedAuto("A1","1\t2\t3\t4");  // Data in 4 columns (1 2 3 4 ) beginning from cell A1
ExcelSetFromParsedAuto("A1","1\n2\n3\n4");  // Data in 4 rows beginning from cell A1
ExcelSetFromParsedAuto("A1","1\n2\n3\n=A1+A2+A3");  // Data in 3 rows beginning from cell A1 and last cell is a SUM() formula
ExcelSetFromParsedAuto("A1","1\t2\t3\t4\n1\t2\t3\t4\n1\t2\t3\t4\n1\t2\t3\t4");  // Data in 4 rows x 4 columns beginning from cell A1

Yes you can output STRING, NUMBERS and FORMULAs with this function, amazing isnt it?

Demonstration

Lets make a demo application in Metatrader4. Important: Almost all XLSGate functions are platform independent, you could code same in all other platforms too

#property copyright "Copyright 2008-2015 Fx1 Inc"
#property link      "http://www.fx1.net"
#property version   "1.00"
#property strict

#include <xlsgate.mqh>

extern string PublishCell     = "E5";
extern string SheetName       = "Demo1";
extern string ExcelFile       = ""; // Put empty string if you want to create new sheet
extern string BrokerInfoCell  ="E3";

bool xls=false;
int LastUpdate=-1;

int OnInit()
  {

   if (ExcelInit("Exporter") && ExcelStart(ExcelFile))
   {
      xls=true;
      ExcelSheetAdd(SheetName);
      if (BrokerInfoCell!="") ExcelSetString(SheetName+"!"+BrokerInfoCell,AccountCompany()+" "+AccountServer()+" AccountNo:"+DoubleToStr(AccountNumber(),0)+" Type:"+(IsDemo()? "DEMO" : "REAL"));
   } else 
   {
      Alert("Make sure XLSApp is running and try again");
      return(INIT_FAILED);               
   }

   return(INIT_SUCCEEDED);
  }
//------------------------------------------------------------------------------------------------------------------------------------
void OnDeinit(const int reason)
  {
        
  }
//------------------------------------------------------------------------------------------------------------------------------------
bool done=false;
void OnTick()
  {
  if (!done) { Update(); done=true; }
  Update();
  }
//------------------------------------------------------------------------------------------------------------------------------------
void Update()
{
   if (TimeLocal()-LastUpdate<2) return;
   LastUpdate=TimeLocal();
   string alllines="LastUpdate\tOrderTicket\tEntryDate\tType\tLots\tSymbol\tEntryPrice\tStopLoss\tTakeProfit\tPrice\tCommission\tSwap\tP/L\tComment\n";
   string emptyline="\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n";
   for(int i=0;i<OrdersTotal();i++)
   {
   string line="";
   if (OrderSelect(i,SELECT_BY_POS))
      {
      int D = MarketInfo(OrderSymbol(),MODE_DIGITS);
      double cAsk = MarketInfo(OrderSymbol(),MODE_ASK);
      double cBid = MarketInfo(OrderSymbol(),MODE_BID);
      double cPrice = 0.0;
      line=TimeToString(TimeLocal(),TIME_DATE|TIME_MINUTES|TIME_SECONDS)+"\t";
      line+=DoubleToStr(OrderTicket(),0)+"\t";
      line+=TimeToString(OrderOpenTime(),TIME_DATE|TIME_MINUTES|TIME_SECONDS)+"\t";
      switch(OrderType())
      {
         case OP_BUY : line+="buy\t"; cPrice=cBid; break;
         case OP_SELL : line+="sell\t"; cPrice=cAsk; break;
         case OP_BUYLIMIT : line+="buylimit\t"; cPrice=cBid; break;
         case OP_SELLLIMIT : line+="selllimit\t"; cPrice=cAsk; break;
         case OP_BUYSTOP : line+="buystop\t"; cPrice=cBid; break;
         case OP_SELLSTOP : line+="sellstop\t"; cPrice=cAsk; break;         
      }
      
      line+=DoubleToStr(OrderLots(),2)+"\t";
      line+=OrderSymbol()+"\t";
      line+=DoubleToStr(OrderOpenPrice(),D)+"\t";
      line+=DoubleToStr(OrderStopLoss(),D)+"\t";
      line+=DoubleToStr(OrderTakeProfit(),D)+"\t";
      line+=DoubleToStr(cPrice,D)+"\t";
      line+=DoubleToStr(OrderCommission(),2)+"\t";
      line+=DoubleToStr(OrderSwap(),2)+"\t";
      line+=DoubleToStr(OrderProfit(),2)+"\t";
      line+=OrderComment()+"\t";
      alllines+=line+"\n";      
      }
   }
   alllines+=emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline+emptyline;
   
   if (!ExcelSetFromParsedAuto(SheetName+"!"+PublishCell,alllines))
   {
   Print("Error");
   }
}

Output:
parsed2

Final words

As you can see from MQL Demonstration, it just takes us 1 single command to publish and keep updating live trade results. For a speed Set operation you need to code efficient, ExcelSetFromParsedAuto is your close friend. This function will work in any Trading platform because it takes string as input instead of complicated types