Publish Data with Arrays

Current Situation

Assume you need to export huge amount of data fastest way, you would have hard time if you had used ExcelSetValue, ExcelSetString because both functions are working data by data. This means to send 1000 data, functions will be used 1000 times. Even in fastest environments this will require processor time and resources.

Approach

We recommend you to use ExcelSetFromArray, ExcelSetFromArrayV, ExcelSetFromArrayH functions. These Array functions can publish data within 1 function call. ExcelSetFromArray is able to send double/integer values to excel with single call. ExcelSetFromArray required 2D array to work with. ArraySetFromArrayV is designed to work with 1D arrays and data will be deployed vertically, ArraySetFromArrayH does same horizontally.

The Problematic

Some of Clients such as Tradestation,Multicharts does not support 2D Arrays, nor 1D Arrays with DLLs. This is not XLSgate restriction, this is Trading-Application-Side restriction.

Alternative

We have implemented alternative functions for Tradestation,Multichart clients. Pleae use ExcelSetFromParsedV and ExcelSetFromParsedH functions, they are well documented and can speed up publishing numbers by x1000 easly.

Usage

With ExcelSetFromArrayV/H you need to pass the amount of data as last parameter. This is very important, this is required due to DLL communication rules. If you have an array with 300 items and you pass different count to these functions, this may lead to crashes etc. Please use these functions carefully.

Metatrader4 Usage

// Lets export close data to 1D array
// we will export last 300 close prices
double M30[300]; int c=0;
while(true)
{ 
  c++; if (c>=ArraySize(M30)) break;
  M30=Close;            
}
// Single call to export 300 Data to Excel beginnning by D5, Vertically 300 columns
ExcelSetFromArrayV("D5",M30,ArraySize(M30));
// Single call to export 300 Data to Excel beginnning by D5, Horizontally 300 rows
ExcelSetFromArrayH("D5",M30,ArraySize(M30));

Multicharts.NET Usage

Here is very basic Multicharts.NET Example to export Close price with single call.

using System;
using System.Drawing;
using System.Linq;
using PowerLanguage.Function;
using ATCenterProxy.interop;
using System.Runtime.InteropServices;

namespace PowerLanguage.Strategy {
	public class FastExporter : SignalObject {
		public FastExporter(object _ctx):base(_ctx){}				
		private int idx=0;
		private bool Excel = false;
		private double[] ToExport = new Double[300];
		
		protected override void Create() {		} 
		protected override void StartCalc() {
			// initialize XLSGate
			if (ExcelInit("From MC.NET") && ExcelStart("")) {
				ExcelSetString("A1","Running XLSgate v"+ExcelGateVersion());
				Excel=true;										
			}			
		}
		protected override void CalcBar(){
			if (Excel)
			{
				if (idx<ToExport.Length)
				{
					ToExport[idx++]=Bars.Close[0];
					
				}			 
				if (Bars.LastBarOnChart) 
				{
					ExcelSetFromArrayH("D5",ToExport,idx);
					ExcelSetFromArrayV("D5",ToExport,idx); 
				}
			}
		} 
		
		[DllImport("xlsgate-a64.dll")]  public static extern bool ExcelStart(string Filename);
		[DllImport("xlsgate-a64.dll")]  public static extern bool ExcelSetString(string Address,string Value);
        [DllImport("xlsgate-a64.dll")]  public static extern bool ExcelInit(string ClientName);
		[DllImport("xlsgate-a64.dll")]  public static extern int ExcelGateVersion();
        [DllImport("xlsgate-a64.dll")]  public static extern bool ExcelSetFromArrayH( string Address, [MarshalAs(UnmanagedType.LPArray)] double[] Data, int Count);
        [DllImport("xlsgate-a64.dll")]  public static extern bool ExcelSetFromArrayV( string Address, [MarshalAs(UnmanagedType.LPArray)] double[] Data, int Count);		
	}
}

Array Matrix

Function ExcelSetFromArray is able to publish data from 2D Array. Advantage is that you could publish 100 columns x 100 rows with single call! Unfortunately it is a bit tricky to use this function

#property strict
#include <xlsgate.mqh>
double OHLC[,4];
int OnInit()
  {  
  if (ExcelInit("MT4"+Symbol()))
     if (ExcelStart(""))
         {
         ArrayResize(OHLC,100);
         // Generate OHLC Data from Last 100 Bars
         for(int i=0;i<100;i++)
            {         
            double H = High[i];
            double L = Low[i];
            double O = Open[i];
            double C = Close[i];
            OHLC[i,0]=H;
            OHLC[i,1]=L;
            OHLC[i,2]=O;
            OHLC[i,3]=C;      
            }
         // Publish data 4 columns x 100 rows
         ExcelSetFromArray("E5",OHLC,ArraySize(OHLC),100,4);
         }
  
  return(INIT_SUCCEEDED);
}
void OnDeinit(const int reason) {   }
void OnTick()  {   }

Conclusion

Once the concept has been understood it is very easy to use Array Data Export functions of XLSgate. Ninjatrader should also support Array functions, you can review MC.NET example to adapt it to Ninjatrader.