Introduction
The use of integrations with other products offers additional challenge in trading.
There can be many usages thereof, so I will give some of them below.
You can collect ticks and pass them to MS SQL SERVER for further analysis. Having a large tick history, you can collect any period starting from the minimum time slice and up to any non-standard periods. Having real tick quotes, you may debug tick-data dependent strategies known as 'scalpers'.
You can use a store for quick analysis of data taken from other applications, for example, from MS Excel or other third-party software or from you own products.
For example, you can unload the entire history from History Center of the terminal into MS SQL. Then you won't need to store the history in MT4. This will help to relieve the terminal memory.
You can calculate neural networks using quotes stored in MS SQL SERVER: for example, STATISTICA - 7.8 to allow you to download quotes from SQL can be solved in the real-time mode by passing the network signals into MT4.
You can develop your own program in another language and for another symbol, and pass signals using MS SQL SERVER, having left only executing functions for the client terminal and relieving it from serious calculations.
The Following Software Products Were Used for This Project
- MS SQL SERVER 2000 Developer - BASE
- VISUAL C++ 6.0 SP5 - to create DLL "YZMSSQLExpertSample.dll"
- MDAC 7
The minimal set to be installed:
1 MS SQL SERVER 2000 Developer
2 MDAC 7
I debugged the program using MDAC 7. However, it is possible that everything works ok on some older versions. If you aren't going to compile the DLL, you needn't to install or have Visual C++ 6.0 installed. You can use a ready DLL. However, I hardwired the user name in it, the name of DSN, and connections. So you will have to repeat all above-listed in your version of the program. I won't describe here how to install MS SQL SERVER or Visual C++ 6.0, these things are outside the scope of this specific article.
After the necessary software products have been installed, we should create a DSN:
dsn=MT4_SQL_BASE;", "yuraz", "qwerty"
Example of Tick Receiving in MS SQL
All experiments were conducted with MS SQL SERVER 2000 Developer. In Visual C++ 6.0, YZMSSQLExpertSample.DLL was created using the method of accessing to MS SQL via ADO. MDAC 7 or MDAC 8 must be installed. I will only describe the examples of how to create procedures and tables. The minimal set of what we have to create in MS SQL are the base, tables and procedures. Let's consider the table and procedures of working with tick quotes. You can add some other functions, if you want.
It is necessary to create a base and tables in MS SQL. I created a new base named MT4TRADE. Then we should create tables in it:
MT4TICK - Table of Ticks
//-----------------------------------------------------------------------------------
//
// Structure of MT4TICK Base
//
// idc - formed automatically, unique number of record
// ServerDateTime - Filled out automatically, when adding a record
// Server local time - time when the quote was placed in the table
// (it doesn't have anything in common with the date and time passed by MT4)
// it is the time counted by the server itself - it will be the same as the time
// of the machine, on which the server has been launched.
//---
// iDateTime - date and time in MT4 format, passed from MT4
// sSymbol - symbol
// cAsk - quote Ask
// cBid - quote Bid
//
CREATE TABLE [dbo].[MT4TICK] (
[idc] [bigint] IDENTITY (1, 1) NOT NULL ,
[ServerDateTime] [datetime] NULL ,
[iDateTime] [bigint] NULL ,
[sSymbol] [char] (6) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
[cAsk] [numeric](18, 4) NULL ,
[cBid] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
--- Include automated filling out the ServerDateTime field with the date and time of server MS SQL
ALTER TABLE [dbo].[MT4TICK] ADD
CONSTRAINT [DF_MT4TICK_ServerDateTime] DEFAULT (getdate()) FOR [ServerDateTime]
GO
Below is how the tick receiving and tabulating procedure appears:
//
// @RetCode int out --- used for returning
// ,@psSymbol char(6) --- symbol
// ,@piDateTime bigint --- date and time of tick arrival
// ,@pdAsk float --- Ask
// ,@pdBid float --- Bid
//
// The procedure just returns 0
// if we analyze the code of returning to MQL4, we can see that the quote has reached the procedure and has been tabulated
//
//
CREATE PROCEDURE dbo.YZ_MT4_TICK
@RetCode int out
,@psSymbol char(6)
,@piDateTime bigint
,@pdAsk float
,@pdBid float
AS
insert into MT4TICK ( sSymbol, iDateTime, cAsk, cBid ) values ( @psSymbol , @piDateTime, @pdAsk , @pdBid )
select @RetCode=0
return @RetCode
We can see from the above description what procedures and for what purposes are used.
@RetCode - it doesn't bear any functionality when being passed from DLL, it serves for receiving the termination code only.
The MS SQL SERVER setup is finished. A script for creating a standard configuration is attached to this article.
Let's Fantasize: Possible Solutions and Pluses
We can create a data storage and place/extract information from it. In this manner, we can relieve the MT 4 Client Terminal from the necessity to store quotes history. Now the quotes history is stored on MS SQL Server and we can operate with this information, extract it sooner and export it to other applications. We can use the data to be analyzed in NEURAL packages, most of which can work with SQL storages.
In the real time, the terminal may continue forming signals from indicators passing them to the storage and fixing them in this manner. An external application can extract the signal and the history in the real time, analyze them and form signals fixing the execution and the storage of the log on MS SQL Server, and send them to the terminal to execute.
Thus, we obtain integration and functional distribution among applications involved in an automated trading complex.
Well, if there is no need anymore to store historical quotes, we can set it up in the following way. Set the minimum bars in Tools>Options>Charts, for example, for 5000. The terminal starts working faster since it doesn't need to allocate memory for large history.
Source Texts
DLL Code:
//+------------------------------------------------------------------+
//| Sample DLL for MQL4 |
//| Copyright c 2004-2008, MetaQuotes Software Corp. |
//| http://www.metaquotes.net |
//+------------------------------------------------------------------+
//+------------------------------------------------------------------+
//
// YURAZ 2008 YZMSSQLExpertSample
//
// Example DLL Integrating MT4 with MS SQL 2000
//
// ADO MS SQL SERVER
//
// software used
//
// VISUAL C++ 6 , SP5 , MDAC 7 , MS SQL2000 + SP4
//
//+------------------------------------------------------------------+
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
//----
#define MT4_EXPFUNC __declspec(dllexport)
//+------------------------------------------------------------------+
//| |
//+------------------------------------------------------------------+
#pragma pack(push,1)
struct RateInfo
{
unsigned int ctm;
double open;
double low;
double high;
double close;
double vol;
double vol1;
double vol2;
double vol3;
double vol4;
double vol5;
};
#pragma pack(pop)
struct MqlStr
{
int len;
char *string;
};
static int CompareMqlStr(const void *left,const void *right);
static int SQLexecProcedure( char *nprc );
static int SQLexecProcedureSignal( char *sSymbol, char* sProcedure );
// static int _YZSQLsqlstrinsql( char *Symbol , unsigned int DateTime , double Ask, double Bid, char *NamePrc );
static int _YZSQLprocedure ( char *sSymbol, unsigned int pDateTime, double Ask, double Bid, char *NamePrc );
static int _YZSQLprocedureHISTORYPut(char *Symbol,unsigned int Period, unsigned int DateTime,double Open,
double High,double Low, double Close ,double Volume, unsigned int Bar ,char *Procedure);
//+------------------------------------------------------------------+
//| |
//+------------------------------------------------------------------+
BOOL APIENTRY DllMain(HANDLE hModule,DWORD ul_reason_for_call,LPVOID lpReserved)
{
//----
switch(ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH:
case DLL_PROCESS_DETACH:
break;
}
//----
return(TRUE);
}
// place ticks in MS SQL
// call the procedure as an SQL line passing parameters "exec YZ_MT4_TICK ?,?,?,?"
/*
MT4_EXPFUNC int __stdcall SQLsqlstringTickPut(char *Symbol,unsigned int DateTime,double Ask,double Bid,char *sSQLstring)
{
int ccc = _YZSQLsqlstrinsql( Symbol , DateTime , Ask , Bid , sSQLstring );
return(ccc);
}
*/
// call as a procedure passing parameters
MT4_EXPFUNC int __stdcall SQLProcedureTickPut(char *Symbol,unsigned int DateTime,double Ask,double Bid,char *Procedure)
{
int ccc = _YZSQLprocedure( Symbol , DateTime , Ask , Bid ,Procedure );
return(ccc);
}
// place a specific candlestick in MS SQL history
MT4_EXPFUNC int __stdcall SQLProcedureHistoryPut(char *Symbol,unsigned int Period , unsigned int DateTime,
double Open,double High,double Low, double Close ,double Volume,unsigned int Bar ,char *Procedure)
{
int ccc = _YZSQLprocedureHISTORYPut(Symbol,Period,DateTime,Open,High,Low,Close,Volume,Bar,Procedure);
return(ccc);
}
// call procedure sProcedure
//
// return -1 error
//
MT4_EXPFUNC int __stdcall SQLProcedureGetInt(char *sProcedure)
{
int Ret = SQLexecProcedure( sProcedure );
return((int)Ret);
}
MT4_EXPFUNC int __stdcall SQLProcedureGetSignal (char *sSymbol, char *sProcedure)
{
int Ret = SQLexecProcedureSignal( sSymbol, sProcedure );
return((int)Ret);
}
//////////////////////////////////
#include "stdafx.h"
#include <stdio.h>
#import "C:\Program Files\Common Files\System\ado\msado20.tlb" \
rename("EOF","ADOEOF") rename("BOF","ADOBOF")
using namespace ADODB;
inline void TESTHR(HRESULT x) { if FAILED(x) _com_issue_error(x); };
// procedure call method
int _YZSQLprocedure( char *sSymbol, unsigned int pDateTime, double Ask, double Bid, char *NamePrc )
{
HRESULT hr = S_OK;
_CommandPtr pCmd = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strMessage, strAuthorID;
::CoInitialize(NULL);
long codRet = -1;
try {
_ParameterPtr Par1;
_ParameterPtr Par2;
_ParameterPtr Par3;
_ParameterPtr Par4;
_ParameterPtr Par5;
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open("dsn=MT4_SQL_BASE;", "yuraz", "qwerty", adConnectUnspecified);
pConnection->CursorLocation = adUseClient;
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->CommandText = NamePrc; // procedure name
pCmd->CommandType = adCmdStoredProc;
Par1 = pCmd->CreateParameter( _bstr_t("@P1"), adInteger, adParamOutput,0, codRet );
pCmd->Parameters->Append( Par1 );
Par1 = pCmd->CreateParameter("@psSymbol",adChar, adParamInput, strlen(sSymbol) ,sSymbol );
pCmd->Parameters->Append(Par1);
Par2 = pCmd->CreateParameter("@piDateTime", adDouble , adParamInput, sizeof(double) , (double)pDateTime );
pCmd->Parameters->Append(Par2);
Par3 = pCmd->CreateParameter("@pdAsk", adDouble, adParamInput, 4, Ask );
pCmd->Parameters->Append(Par3);
Par4 = pCmd->CreateParameter("@pdBid", adDouble, adParamInput, 4, Bid );
pCmd->Parameters->Append(Par4);
pCmd->ActiveConnection = pConnection;
int hr = pCmd->Execute( 0, 0, adCmdStoredProc );
if( FAILED(hr) )
{
codRet = -1;
}
else
{
Par1 = pCmd->Parameters->GetItem(_bstr_t("@P1")); // obtain from the procedure
codRet = Par1->GetValue();
}
}
catch(_com_error ) {
//
// if necessary, process the execution error
//
codRet = -1;
}
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
return((int)codRet);
}
// place in history Symbol , Period . DateTime, Open , High , Low , Close , Value , Bar
int _YZSQLprocedureHISTORYPut(char *pSymbol,unsigned int pPeriod, unsigned int pDateTime,double pOpen,double pHigh,
double pLow, double pClose ,double pVolume, unsigned int pBar ,char *pProcedure )
{
HRESULT hr = S_OK;
_CommandPtr pCmd = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strMessage, strAuthorID;
::CoInitialize(NULL);
long codRet = -1;
try {
_ParameterPtr ParReturn; //
_ParameterPtr Par1; // SYMBOL
_ParameterPtr Par2; // PERIOD
_ParameterPtr Par3; // DATETIME
_ParameterPtr Par4; // OPEN
_ParameterPtr Par5; // HIGH
_ParameterPtr Par6; // LOW
_ParameterPtr Par7; // CLOSE
_ParameterPtr Par8; // VOLUME
_ParameterPtr Par9; // BAR
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open("dsn=MT4_SQL_BASE;", "yuraz", "qwerty", adConnectUnspecified);
pConnection->CursorLocation = adUseClient;
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->CommandText = pProcedure; // procedure name
pCmd->CommandType = adCmdStoredProc;
ParReturn = pCmd->CreateParameter( _bstr_t("@P1"), adInteger, adParamOutput,0, codRet );
pCmd->Parameters->Append( ParReturn );
Par1 = pCmd->CreateParameter("@psSymbol",adChar, adParamInput, strlen(pSymbol) ,pSymbol );
pCmd->Parameters->Append(Par1);
Par2 = pCmd->CreateParameter("@piDateTime", adDouble , adParamInput, sizeof(double) , (double)pPeriod );
pCmd->Parameters->Append(Par2);
Par3 = pCmd->CreateParameter("@piDateTime", adDouble , adParamInput, sizeof(double) , (double)pDateTime );
pCmd->Parameters->Append(Par3);
Par4 = pCmd->CreateParameter("@pdOpen", adDouble, adParamInput, 4, pOpen );
pCmd->Parameters->Append(Par4);
Par5 = pCmd->CreateParameter("@pdHigh", adDouble, adParamInput, 4, pHigh );
pCmd->Parameters->Append(Par5);
Par6 = pCmd->CreateParameter("@pdLow", adDouble, adParamInput, 4, pLow );
pCmd->Parameters->Append(Par6);
Par7 = pCmd->CreateParameter("@pdClose", adDouble, adParamInput, 4, pClose );
pCmd->Parameters->Append(Par7);
Par8 = pCmd->CreateParameter("@pdVolume", adDouble, adParamInput, 4, pVolume );
pCmd->Parameters->Append(Par8);
Par9 = pCmd->CreateParameter("@piBar", adDouble , adParamInput, sizeof(double) , (double)pBar );
pCmd->Parameters->Append(Par9);
pCmd->ActiveConnection = pConnection;
int hr = pCmd->Execute( 0, 0, adCmdStoredProc );
if( FAILED(hr) )
{
codRet = -1;
}
else
{
ParReturn = pCmd->Parameters->GetItem(_bstr_t("@P1")); // obtain from the procedure
codRet = ParReturn->GetValue();
}
}
catch(_com_error ) {
//
// if necessary, process the execution error
//
codRet = -1;
}
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
return((int)codRet);
}
//
// return the value returned by the procedure
//
int SQLexecProcedure( char *nprc )
{
HRESULT hr = S_OK;
_CommandPtr pcmd = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strMessage, strAuthorID;
::CoInitialize(NULL);
long codRet = -1;
try {
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open("dsn=MT4_SQL_BASE;", "yuraz", "qwerty", adConnectUnspecified);
pConnection->CursorLocation = adUseClient;
TESTHR(pcmd.CreateInstance(__uuidof(Command)));
pcmd->CommandText = nprc; // procedure name
pcmd->CommandType = adCmdStoredProc;
_ParameterPtr pParm1 = pcmd->CreateParameter( _bstr_t("@P1"), adInteger, adParamOutput,0, codRet );
pcmd->Parameters->Append( pParm1 );
pcmd->ActiveConnection = pConnection;
int hr = pcmd->Execute( 0, 0, adCmdStoredProc );
if( FAILED(hr) )
{
codRet = -1;
}
else
{
pParm1 = pcmd->Parameters->GetItem(_bstr_t("@P1")); // obtain from the procedure
codRet = pParm1->GetValue();
}
}
catch(_com_error ) {
//
// if necessary, process the execution error
//
codRet = -1;
}
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
return((int)codRet);
}
//
//
//
int SQLexecProcedureSignal( char *sSymbol, char* sProcedure )
{
HRESULT hr = S_OK;
_CommandPtr pcmd = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strMessage;
_bstr_t strAuthorID;
::CoInitialize(NULL);
long codRet = 0;
try {
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open("dsn=MT4_SQL_BASE;", "yuraz", "qwerty", adConnectUnspecified);
pConnection->CursorLocation = adUseClient;
TESTHR(pcmd.CreateInstance(__uuidof(Command)));
pcmd->CommandText = sProcedure; // procedure name
pcmd->CommandType = adCmdStoredProc;
_ParameterPtr pParm1 = pcmd->CreateParameter("@psSymbol",adChar, adParamInput, strlen(sSymbol) ,sSymbol );
pcmd->Parameters->Append(pParm1);
_ParameterPtr pParm2 = pcmd->CreateParameter( _bstr_t("@P1"), adInteger, adParamOutput,0, codRet );
pcmd->Parameters->Append( pParm2 );
pcmd->ActiveConnection = pConnection;
int hr = pcmd->Execute( 0, 0, adCmdStoredProc );
if( FAILED(hr) )
{
bool bSuccess = false;
}
pParm2 = pcmd->Parameters->GetItem(_bstr_t("@P1")); // obtain from the procedure
codRet = pParm2->GetValue();
// printf("\n [%d] \n",codRet ); // OBTAINING from the procedure
}
catch(_com_error ) {
//
// if necessary, process the execution error
//
}
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
return((int)codRet);
}
Example of calling from MQL4 -
// Comments are reduced to make it appear simpler, the comments in the attached files are complete
//+------------------------------------------------------------------+
//| |
//| Copyright c 1999-2006, MetaQuotes Software Corp. |
//| http://www.metaquotes.ru |
//| YZMSSQLSample.mq4 |
//| Yuriy Zaitsev |
//+------------------------------------------------------------------+
// Example of integrating with MS SQL |
//+------------------------------------------------------------------+
#property copyright "YURAZ Copyright(C) 2008"
#property link "yzy @ mail.ru"
//+------------------------------------------------------------------+
// DLL function library
//+------------------------------------------------------------------+
#import "YZMSSQLExpertSample.dll"
// Performing any actions on MS SQL Server, procedure is called
SQLProcedureGetInt
// Collecting ticks
int SQLProcedureTickPut( string, int , double , double ,string );
int Prc = 0;
int init()
{
//
// SQLProcedureGetInt The function, once having called a certain procedure,
// will return into MT4 int value, for example, parameters
// stored on MS SQL server, formed by another software
//
Prc = SQLProcedureGetInt ("YZ_MT4_T1");
return(0);
}
int start()
{
int a;
int RetCode = SQLProcedureTickPut( Symbol(), TimeCurrent() , Ask, Bid ,"YZ_MT4_TICK"); // call to the tick collecting procedure
Print(" SQLProcedureTickPut (YZ_MT4_NEWDAY)"+ RetCode );
// Example:
// on MS SQL server, you can filter signals formed using third-party software
// neural networks
// other software products
//
/*
int Signal = SQLProcedureGetSignal (Symbol() , "YZ_MT4_SIGNAL" ); // procedure MS SQL , will return signal
Print(" SQLProcedureGetSignal (Symbol() , YZ_MT4_SIGNAL )"+ Signal );
if ( Signal == OP_BUY )
{
// the procedure has returned the signal and is recommending to buy
}
if ( Signal == OP_SELL )
{
// the procedure has returned the signal and is recommending to sell
}
*/
return(0);
}
Script loading history onto MS SQL Server:
//
// YURAZ 2008 yzh @ mail.ru
//
// script loading history onto MS SQL
// reload all history for all currency pairs and for all TIMEFRAMES
// in MS SQL
//
#import "YZMSSQLExpertSample.dll"
int SQLProcedureHistoryPut( string, int , int, double , double ,double , double ,double ,int, string );
static int mPeriod[8]={PERIOD_M1,PERIOD_M5,PERIOD_M15,PERIOD_M30,PERIOD_H1,PERIOD_H4,PERIOD_D1,PERIOD_W1,PERIOD_MN1};
void start()
{
PutHistor("EURUSD");
PutHistor("USDCHF");
Comment(" LOADING COMPLETE " );
}
void PutHistor(string sSymbol)
{
for ( int iPeriod = 0; iPeriod <= 8 ; iPeriod++ )
{
int pPERIOD_XX = mPeriod[iPeriod];
int Bar = iBars(sSymbol,pPERIOD_XX ); // obtain the depth of history for the given timeframe
// no progress bar organized
for ( int iBar = Bar; iBar >= 0 ; iBar--)
{
Comment( "WAIT TIMEFRAME "+pPERIOD_XX+" SYMBOL "+sSymbol+" BARS "+iBar );
double o = iOpen (sSymbol,pPERIOD_XX,iBar);
double h = iHigh (sSymbol,pPERIOD_XX,iBar);
double l = iLow (sSymbol,pPERIOD_XX,iBar);
double c = iClose (sSymbol,pPERIOD_XX,iBar);
double v = iVolume(sSymbol,pPERIOD_XX,iBar);
datetime d = iTime (sSymbol,pPERIOD_XX,iBar);
int RetCode = SQLProcedureHistoryPut( sSymbol,pPERIOD_XX,d,o,h,l,c,v,iBar, "YZ_MT4_HISTORY");
// Print ( " YZ_MT4_HITSRY "+RetCode);
}
}
}
Attention: Unfortunately, all history is loaded rather slowly using the script, but it fixes bar number clearly and with high quality.
The best solution would be unloading quotes into a text file and loading them into MS SQL through IMPRT EXPORT DTS. Loading M1 history of 1999-2008 for each symbol will take a few minutes.
The bar index is not unloaded when unloading into a text file. If you decide that the bar index will be just the line number, you will have the problem of missed bars and, if modifying or reloading, the numbers of unloaded bars may be different in MS SQL and in MT 4. I haven't solved this problem yet, but I suppose it can be solved through reloading of history after a high-quality history updating in MT 4 itself.
Description of Files Attached
CreateSQLallDate.txt (9.0 Kb)- Script in SQL format as an example of how to create bases, tables, procedures on MS SQL Server.
SQLGETHISTORY.mq4 (1.4 Kb)- Script to load history in MS SQL
YZMSSQLExpertSample.rar (89.9 Kb) - DLL project
YZMSSQLSample.mq4 (13.1 Kb) - To be attached as an EA to the symbol chart, the ticks of which should be collected; you can attach it to any timeframe.
Conclusion
Integrating with other software products will expand the functionality of MetaTrader 4 and allow to distribute tasks and functions of an automated trading system more efficiently.
CreateSQLallDate.txt (9.0 Kb) SQLGETHISTORY.mq4 (1.4 Kb) YZMSSQLExpertSample.rar (89.9 Kb) YZMSSQLSample.mq4 (13.1 Kb) 
