//--- Structure to store the deal
struct Deal
{
ulong ticket; // DEAL_TICKET
long order_ticket; // DEAL_ORDER
long position_ticket; // DEAL_POSITION_ID
datetime time; // DEAL_TIME
char type; // DEAL_TYPE
char entry; // DEAL_ENTRY
string symbol; // DEAL_SYMBOL
double volume; // DEAL_VOLUME
double price; // DEAL_PRICE
double profit; // DEAL_PROFIT
double swap; // DEAL_SWAP
double commission; // DEAL_COMMISSION
long magic; // DEAL_MAGIC
char reason; // DEAL_REASON
};
//--- Structure to store the trade: the order of members corresponds to the position in the terminal
struct Trade
{
datetime time_in; // entry time
ulong ticket; // position ID
char type; // buy or sell
double volume; // volume
string symbol; // symbol
double price_in; // entry price
datetime time_out; // exit time
double price_out; // exit price
double commission; // entry and exit commission
double swap; // swap
double profit; // profit or loss
};
//+------------------------------------------------------------------+
//| Script program start function |
//+------------------------------------------------------------------+
void OnStart()
{
//--- create the file name
string filename=IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+"_trades.sqlite";
//--- open/create the database in the common terminal folder
int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON);
if(db==INVALID_HANDLE)
{
Print("DB: ", filename, " open failed with code ", GetLastError());
return;
}
//--- create the DEALS table
if(!CreateTableDeals(db))
{
DatabaseClose(db);
return;
}
//--- request the entire trading history
datetime from_date=0;
datetime to_date=TimeCurrent();
//--- request the history of deals in the specified interval
HistorySelect(from_date, to_date);
int deals_total=HistoryDealsTotal();
PrintFormat("Deals in the trading history: %d ", deals_total);
//--- add deals to the table
if(!InsertDeals(db))
return;
//--- show the first 10 deals
Deal deals[], deal;
ArrayResize(deals, 10);
int request=DatabasePrepare(db, "SELECT * FROM DEALS");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
int i;
for(i=0; DatabaseReadBind(request, deal); i++)
{
if(i>=10)
break;
deals[i].ticket=deal.ticket;
deals[i].order_ticket=deal.order_ticket;
deals[i].position_ticket=deal.position_ticket;
deals[i].time=deal.time;
deals[i].type=deal.type;
deals[i].entry=deal.entry;
deals[i].symbol=deal.symbol;
deals[i].volume=deal.volume;
deals[i].price=deal.price;
deals[i].profit=deal.profit;
deals[i].swap=deal.swap;
deals[i].commission=deal.commission;
deals[i].magic=deal.magic;
deals[i].reason=deal.reason;
}
//--- print the deals
if(i>0)
{
ArrayResize(deals, i);
PrintFormat("The first %d deals:", i);
ArrayPrint(deals);
}
//--- delete request after use
DatabaseFinalize(request);
//--- make sure that hedging system for open position management is used on the account
if((ENUM_ACCOUNT_MARGIN_MODE)AccountInfoInteger(ACCOUNT_MARGIN_MODE)!=ACCOUNT_MARGIN_MODE_RETAIL_HEDGING)
{
//--- deals cannot be transformed to trades using a simple method through transactions, therefore complete operation
DatabaseClose(db);
return;
}
//--- now create the TRADES table based on the DEALS table
if(!CreateTableTrades(db))
{
DatabaseClose(db);
return;
}
//--- fill in the TRADES table using an SQL query based on DEALS table data
ulong start=GetMicrosecondCount();
if(DatabaseTableExists(db, "DEALS"))
//--- populate the TRADES table
if(!DatabaseExecute(db, "INSERT INTO TRADES(TIME_IN,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) "
"SELECT "
" d1.time as time_in,"
" d1.position_id as ticket,"
" d1.type as type,"
" d1.volume as volume,"
" d1.symbol as symbol,"
" d1.price as price_in,"
" d2.time as time_out,"
" d2.price as price_out,"
" d1.commission+d2.commission as commission,"
" d2.swap as swap,"
" d2.profit as profit "
"FROM DEALS d1 "
"INNER JOIN DEALS d2 ON d1.position_id=d2.position_id "
"WHERE d1.entry=0 AND d2.entry=1 "))
{
Print("DB: fillng the TRADES table failed with code ", GetLastError());
return;
}
ulong transaction_time=GetMicrosecondCount()-start;
//--- show the first 10 deals
Trade trades[], trade;
ArrayResize(trades, 10);
request=DatabasePrepare(db, "SELECT * FROM TRADES");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
for(i=0; DatabaseReadBind(request, trade); i++)
{
if(i>=10)
break;
trades[i].time_in=trade.time_in;
trades[i].ticket=trade.ticket;
trades[i].type=trade.type;
trades[i].volume=trade.volume;
trades[i].symbol=trade.symbol;
trades[i].price_in=trade.price_in;
trades[i].time_out=trade.time_out;
trades[i].price_out=trade.price_out;
trades[i].commission=trade.commission;
trades[i].swap=trade.swap;
trades[i].profit=trade.profit;
}
//--- print trades
if(i>0)
{
ArrayResize(trades, i);
PrintFormat("\r\nThe first %d trades:", i);
ArrayPrint(trades);
PrintFormat("Filling the TRADES table took %.2f milliseconds",double(transaction_time)/1000);
}
//--- delete request after use
DatabaseFinalize(request);
//--- close the database
DatabaseClose(db);
}
/*
Results:
Deals in the trading history: 2741
The first 10 deals:
[ticket] [order_ticket] [position_ticket] [time] [type] [entry] [symbol] [volume] [price] [profit] [swap] [commission] [magic] [reason]
[0] 34429573 0 0 2019.09.05 22:39:59 2 0 "" 0.00000 0.00000 2000.00000 0.0000 0.00000 0 0
[1] 34432127 51447238 51447238 2019.09.06 06:00:03 0 0 "USDCAD" 0.10000 1.32320 0.00000 0.0000 -0.16000 500 3
[2] 34432128 51447239 51447239 2019.09.06 06:00:03 1 0 "USDCHF" 0.10000 0.98697 0.00000 0.0000 -0.16000 500 3
[3] 34432450 51447565 51447565 2019.09.06 07:00:00 0 0 "EURUSD" 0.10000 1.10348 0.00000 0.0000 -0.18000 400 3
[4] 34432456 51447571 51447571 2019.09.06 07:00:00 1 0 "AUDUSD" 0.10000 0.68203 0.00000 0.0000 -0.11000 400 3
[5] 34432879 51448053 51448053 2019.09.06 08:00:00 1 0 "USDCHF" 0.10000 0.98701 0.00000 0.0000 -0.16000 600 3
[6] 34432888 51448064 51448064 2019.09.06 08:00:00 0 0 "USDJPY" 0.10000 106.96200 0.00000 0.0000 -0.16000 600 3
[7] 34435147 51450470 51450470 2019.09.06 10:30:00 1 0 "EURUSD" 0.10000 1.10399 0.00000 0.0000 -0.18000 100 3
[8] 34435152 51450476 51450476 2019.09.06 10:30:00 0 0 "GBPUSD" 0.10000 1.23038 0.00000 0.0000 -0.20000 100 3
[9] 34435154 51450479 51450479 2019.09.06 10:30:00 1 0 "EURJPY" 0.10000 118.12000 0.00000 0.0000 -0.18000 200 3
The first 10 trades:
[time_in] [ticket] [type] [volume] [symbol] [price_in] [time_out] [price_out] [commission] [swap] [profit]
[0] 2019.09.06 06:00:03 51447238 0 0.10000 "USDCAD" 1.32320 2019.09.06 18:00:00 1.31761 -0.32000 0.00000 -42.43000
[1] 2019.09.06 06:00:03 51447239 1 0.10000 "USDCHF" 0.98697 2019.09.06 18:00:00 0.98641 -0.32000 0.00000 5.68000
[2] 2019.09.06 07:00:00 51447565 0 0.10000 "EURUSD" 1.10348 2019.09.09 03:30:00 1.10217 -0.36000 -1.31000 -13.10000
[3] 2019.09.06 07:00:00 51447571 1 0.10000 "AUDUSD" 0.68203 2019.09.09 03:30:00 0.68419 -0.22000 0.03000 -21.60000
[4] 2019.09.06 08:00:00 51448053 1 0.10000 "USDCHF" 0.98701 2019.09.06 18:00:01 0.98640 -0.32000 0.00000 6.18000
[5] 2019.09.06 08:00:00 51448064 0 0.10000 "USDJPY" 106.96200 2019.09.06 18:00:01 106.77000 -0.32000 0.00000 -17.98000
[6] 2019.09.06 10:30:00 51450470 1 0.10000 "EURUSD" 1.10399 2019.09.06 14:30:00 1.10242 -0.36000 0.00000 15.70000
[7] 2019.09.06 10:30:00 51450476 0 0.10000 "GBPUSD" 1.23038 2019.09.06 14:30:00 1.23040 -0.40000 0.00000 0.20000
[8] 2019.09.06 10:30:00 51450479 1 0.10000 "EURJPY" 118.12000 2019.09.06 14:30:00 117.94100 -0.36000 0.00000 16.73000
[9] 2019.09.06 10:30:00 51450480 0 0.10000 "GBPJPY" 131.65300 2019.09.06 14:30:01 131.62500 -0.40000 0.00000 -2.62000
Filling the TRADES table took 12.51 milliseconds
*/
//+------------------------------------------------------------------+
//| Creates the DEALS table |
//+------------------------------------------------------------------+
bool CreateTableDeals(int database)
{
//--- if the DEALS table already exists, delete it
if(!DeleteTable(database, "DEALS"))
{
return(false);
}
//--- check if the table exists
if(!DatabaseTableExists(database, "DEALS"))
//--- create the table
if(!DatabaseExecute(database, "CREATE TABLE DEALS("
"ID INT KEY NOT NULL,"
"ORDER_ID INT NOT NULL,"
"POSITION_ID INT NOT NULL,"
"TIME INT NOT NULL,"
"TYPE INT NOT NULL,"
"ENTRY INT NOT NULL,"
"SYMBOL CHAR(10),"
"VOLUME REAL,"
"PRICE REAL,"
"PROFIT REAL,"
"SWAP REAL,"
"COMMISSION REAL,"
"MAGIC INT,"
"REASON INT );"))
{
Print("DB: create the DEALS table failed with code ", GetLastError());
return(false);
}
//--- the table has been successfully created
return(true);
}
//+------------------------------------------------------------------+
//| Deletes a table with the specified name from the database |
//+------------------------------------------------------------------+
bool DeleteTable(int database, string table_name)
{
if(!DatabaseExecute(database, "DROP TABLE IF EXISTS "+table_name))
{
Print("Failed to drop the DEALS table with code ", GetLastError());
return(false);
}
//--- the table has been successfully deleted
return(true);
}
//+------------------------------------------------------------------+
//| Adds deals to the database table |
//+------------------------------------------------------------------+
bool InsertDeals(int database)
{
//--- Auxiliary variables
ulong deal_ticket; // deal ticket
long order_ticket; // the ticket of the order by which the deal was executed
long position_ticket; // ID of the position to which the deal belongs
datetime time; // deal execution time
long type ; // deal type
long entry ; // deal direction
string symbol; // the symbol fro which the deal was executed
double volume; // operation volume
double price; // price
double profit; // financial result
double swap; // swap
double commission; // commission
long magic; // Magic number (Expert Advisor ID)
long reason; // deal execution reason or source
//--- go through all deals and add them to the database
bool failed=false;
int deals=HistoryDealsTotal();
// --- lock the database before executing transactions
DatabaseTransactionBegin(database);
for(int i=0; i<deals; i++)
{
deal_ticket= HistoryDealGetTicket(i);
order_ticket= HistoryDealGetInteger(deal_ticket, DEAL_ORDER);
position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID);
time= (datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME);
type= HistoryDealGetInteger(deal_ticket, DEAL_TYPE);
entry= HistoryDealGetInteger(deal_ticket, DEAL_ENTRY);
symbol= HistoryDealGetString(deal_ticket, DEAL_SYMBOL);
volume= HistoryDealGetDouble(deal_ticket, DEAL_VOLUME);
price= HistoryDealGetDouble(deal_ticket, DEAL_PRICE);
profit= HistoryDealGetDouble(deal_ticket, DEAL_PROFIT);
swap= HistoryDealGetDouble(deal_ticket, DEAL_SWAP);
commission= HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION);
magic= HistoryDealGetInteger(deal_ticket, DEAL_MAGIC);
reason= HistoryDealGetInteger(deal_ticket, DEAL_REASON);
//--- add each deal to the table using the following request
string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)"
"VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d)",
deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason);
if(!DatabaseExecute(database, request_text))
{
PrintFormat("%s: failed to insert deal #%d with code %d", __FUNCTION__, deal_ticket, GetLastError());
PrintFormat("i=%d: deal #%d %s", i, deal_ticket, symbol);
failed=true;
break;
}
}
//--- check for transaction execution errors
if(failed)
{
//--- roll back all transactions and unlock the database
DatabaseTransactionRollback(database);
PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError());
return(false);
}
//--- all transactions have been performed successfully - record changes and unlock the database
DatabaseTransactionCommit(database);
return(true);
}
//+------------------------------------------------------------------+
//| Creates the TRADES table |
//+------------------------------------------------------------------+
bool CreateTableTrades(int database)
{
//--- if the TRADES table already exists, delete it
if(!DeleteTable(database, "TRADES"))
return(false);
//--- check if the table exists
if(!DatabaseTableExists(database, "TRADES"))
//--- create the table
if(!DatabaseExecute(database, "CREATE TABLE TRADES("
"TIME_IN INT NOT NULL,"
"TICKET INT NOT NULL,"
"TYPE INT NOT NULL,"
"VOLUME REAL,"
"SYMBOL CHAR(10),"
"PRICE_IN REAL,"
"TIME_OUT INT NOT NULL,"
"PRICE_OUT REAL,"
"COMMISSION REAL,"
"SWAP REAL,"
"PROFIT REAL);"))
{
Print("DB: create the TRADES table failed with code ", GetLastError());
return(false);
}
//--- the table has been successfully created
return(true);
}
//+------------------------------------------------------------------+ |