SQLite capabilities in MQL5: Example of a dashboard with trading statistics by symbols and magic numbers
MetaTrader 5
—
Examples
|
2 617
5
Contents
- Introduction
- Formulating the task
- Information Panel
- Functions for working with the database
- Assembling the dashboard
- Conclusion
Introduction
MQL5.com is a resource that offers users wide access to a variety of reference and educational information in the field of algorithmic trading. Despite the extensive database and capabilities, some users still have difficulty finding specific solutions or adapting examples to their needs. In such cases, the resource provides the opportunity to interact with the community via the forum, where you can get useful advice or even ready-made solutions.
The article aims to demonstrate and solve one of the typical tasks - creating an info panel to display the trading history and statistics on the account. We will look at the development process of this panel, relying solely on existing materials with mql5.com, which is interesting not only as a practical solution, but also as an example of the application of training and reference materials in real conditions.
Formulating the task
It is necessary to create an info panel where, upon request, it will be possible to display information about the trading history on the account and trading statistics broken down by symbols and magic numbers (experts trading on the account). It is also necessary to display full trading statistics for the account. Trading history should be sorted by the time of the trades performed. Trading statistics for symbols and magic numbers should be sorted by Net Profit.
The applied program type is Indicator. We will not output any data to the buffers. In other words, it will be a bufferless indicator with its main working space being a graphic panel. The panel will be visually divided into two areas - on the left we will display lists of all symbols and magic numbers, on which trading was conducted on the account, and on the right we will display either tables of statistics on symbols and magic numbers in the form of a list, or final statistics on any selected symbol or magic number or the entire trading on the account. We will manage the output of certain lists using the buttons on the panel, or by clicking on the line with statistics for a symbol/magic in the table on the right side of the panel.
In the article "Making a dashboard to display data in indicators and EAs", we will use an info panel.
The article "SQLite: Native handling of SQL databases in MQL5" will help us get statistics on the account and in terms of symbols and magic numbers (trading strategies).
Information Panel
Since the first article about creating a dashboard, its code has undergone some changes and improvements. It is not the plan or objective of this article to describe in detail all the changes made to the code. So let's have a quick overview of the changes. We can see what and how exactly was modified by downloading the first version of the panel from the specified article and comparing it with the code of the same panel, attached to the article.
I have fixed errors in positioning and displaying the panel in some situations when switching charts. Now we can attach child dashboards to the main one. In particular, we can make buttons out of them by collapsing the child dashboard and leaving only its header, which will serve as a button. The restrictions on the positioning of tables within the panel have been removed. Initially, the table could not be drawn outside the panel - only in the visibility area. In some cases, tables drawn on a panel should be positioned outside the visible area of the panel. This is necessary for scrolling long or wide tables whose lists are larger than the size of the dashboard. So, if we allow the table initial coordinates to be positioned outside the dashboard, we will be able to make the table scroll. This is exactly what we will do today. But we will not change the classes of the dashboard and its tables (although this is more correct in terms of further use of the modified panel class) so that we can clearly show that with the right approach it is always possible to bring the functionality to the desired result even having examples that are not quite suitable in terms of functionality.
Let's take a quick look at what has been modified in the table and dashboard class code.
The table cell class now features the variable for storing the cell text and the methods for handling the variable:
//+------------------------------------------------------------------+ //| Table cell class | //+------------------------------------------------------------------+ class CTableCell : public CObject { private: int m_row; // Row int m_col; // Column int m_x; // X coordinate int m_y; // Y coordinate string m_text; // Text in the cell public: //--- Methods of setting values void SetRow(const uint row) { this.m_row=(int)row; } void SetColumn(const uint col) { this.m_col=(int)col; } void SetX(const uint x) { this.m_x=(int)x; } void SetY(const uint y) { this.m_y=(int)y; } void SetXY(const uint x,const uint y) { this.m_x=(int)x; this.m_y=(int)y; } void SetText(const string text) { this.m_text=text; } //--- Methods of obtaining values int Row(void) const { return this.m_row; } int Column(void) const { return this.m_col; } int X(void) const { return this.m_x; } int Y(void) const { return this.m_y; } string Text(void) const { return this.m_text; } //--- Virtual method for comparing two objects virtual int Compare(const CObject *node,const int mode=0) const { const CTableCell *compared=node; return(this.Column()>compared.Column() ? 1 : this.Column()<compared.Column() ? -1 : 0); } //--- Constructor/destructor CTableCell(const int row,const int column) : m_row(row),m_col(column){} ~CTableCell(void){} };
In the table data class, the ID is now able to have a negative value, just as the table coordinates can be negative. Added the method to set table ID:
//+------------------------------------------------------------------+ //| Table data class | //+------------------------------------------------------------------+ class CTableData : public CObject { private: CArrayObj m_list_rows; // List of rows int m_id; // Table ID int m_x1; // X1 coordinate int m_y1; // Y1 coordinate int m_x2; // X2 coordinate int m_y2; // Y2 coordinate int m_w; // Width int m_h; // Height string m_name; // Table name public: //--- Set table (1) ID and (2) name void SetID(const int id) { this.m_id=id; } void SetName(const string name) { this.m_name=name; } //--- Return table (1) ID and (2) name int ID(void) const { return this.m_id; } string Name(void) const { return this.m_name; } //--- Set coordinate (1) X1, (2) X2 void SetX1(const int x1) { this.m_x1=x1; } void SetX2(const int x2) { this.m_x2=x2; } //--- Set coordinate (1) Y1, (2) Y2 void SetY1(const int y1) { this.m_y1=y1; } void SetY2(const int y2) { this.m_y2=y2; } //--- Set table coordinates void SetCoords(const int x1,const int y1,const int x2,const int y2) { this.SetX1(x1); this.SetY1(y1); this.SetX2(x2); this.SetY2(y2); }
Added the default constructor:
//--- Constructor/destructor CTableData(void) : m_id(-1) { this.m_list_rows.Clear(); this.m_name=""; } CTableData(const uint id) : m_id((int)id) { this.m_list_rows.Clear(); this.m_name=""; } ~CTableData(void) { this.m_list_rows.Clear(); }
The default constructor will allow you to declare an object of a class without creating it using the new operator, while int coordinates will allow us to set the initial coordinates of tables outside the dashboard window.
New variables have been declared in the panel object class:
//+------------------------------------------------------------------+ //| Dashboard class | //+------------------------------------------------------------------+ class CDashboard : public CObject { private: CTableData m_table_tmp; // Table object for search CCanvas m_canvas; // Canvas CCanvas m_workspace; // Work space CArrayObj m_list_table; // List of tables CArrayObj m_list_obj; // List of linked panels ENUM_PROGRAM_TYPE m_program_type; // Program type ENUM_MOUSE_STATE m_mouse_state; // Mouse button status uint m_id; // Object ID long m_chart_id; // ChartID int m_chart_w; // Chart width int m_chart_h; // Chart height int m_x; // X coordinate int m_y; // Y coordinate int m_w; // Width int m_h; // Height int m_x_dock; // X coordinate of the pinned collapsed panel int m_y_dock; // Y coordinate of the pinned collapsed panel int m_diff_x; // Offset of local X coordinate relative to parent int m_diff_y; // Offset of local Y coordinate relative to parent bool m_header; // Header presence flag bool m_butt_close; // Close button presence flag bool m_butt_minimize; // Collapse/expand button presence flag bool m_butt_pin; // Pin button presence flag bool m_wider_wnd; // Flag for exceeding the horizontal size of the window width panel bool m_higher_wnd; // Flag for exceeding the vertical size of the window height panel bool m_movable; // Panel movability flag int m_header_h; // Header height int m_wnd; // Chart subwindow index int m_title_x_shift; // Horizontal offset of the header text int m_title_y_shift; // Vertical offset of the header text uchar m_header_alpha; // Header transparency uchar m_header_alpha_c; // Current header transparency color m_header_back_color; // Header background color color m_header_back_color_c; // Current header background color color m_header_fore_color; // Header text color color m_header_fore_color_c; // Current header text color color m_header_border_color; // Header border color color m_header_border_color_c; // Current header border color color m_butt_close_back_color; // Close button background color color m_butt_close_back_color_c; // Current close button background color color m_butt_close_fore_color; // Close button icon color color m_butt_close_fore_color_c; // Current close button color color m_butt_min_back_color; // Expand/collapse button background color color m_butt_min_back_color_c; // Current expand/collapse button background color color m_butt_min_fore_color; // Expand/collapse button icon color color m_butt_min_fore_color_c; // Current expand/collapse button icon color color m_butt_pin_back_color; // Pin button background color color m_butt_pin_back_color_c; // Current pin button background color color m_butt_pin_fore_color; // Pin button icon color color m_butt_pin_fore_color_c; // Current pin button icon color uchar m_alpha; // Panel transparency uchar m_alpha_c; // Current panel transparency uchar m_fore_alpha; // Text transparency uchar m_fore_alpha_c; // Current text transparency color m_back_color; // Background color color m_back_color_c; // Current background color color m_fore_color; // Text color color m_fore_color_c; // Current text color color m_border_color; // Border color color m_border_color_c; // Current border color string m_title; // Title text string m_title_font; // Title font int m_title_font_size; // Title font size string m_font; // Font int m_font_size; // Font size bool m_minimized; // Collapsed panel window flag string m_program_name; // Program name string m_name_gv_x; // Name of the global terminal variable storing the X coordinate string m_name_gv_y; // Name of the global terminal variable storing the Y coordinate string m_name_gv_m; // Name of the global terminal variable storing the collapsed panel flag string m_name_gv_u; // Name of the global terminal variable storing the flag of the pinned panel string m_filename_bg; // File name to save background pixels string m_filename_ws; // File name for saving work space pixels uint m_array_wpx[]; // Array of pixels to save/restore the workspace uint m_array_ppx[]; // Array of pixels to save/restore the panel background int m_mouse_diff_x; // Offset the cursor relative to the X anchor angle int m_mouse_diff_y; // Offset the cursor relative to the Y anchor angle bool m_slave; // Flag of a linked (dependent) dashboard string m_name; // Dashboard name
Since we can now bind child dashboards to the parent one, some methods of handling the panel have been moved from the protected section to the public one. These methods require external access. Also, new methods have been added:
public: //--- Return (1) chart ID and (2) subwindow index long ChartID(void) const { return this.m_chart_id; } int SubWindow(void) const { return this.m_wnd; } //--- (1) Collapse and (2) expand the panel void Collapse(void); void Expand(void); //--- (1) Hide, (2) show and (3) bring the panel to the foreground void Hide(const bool redraw=false); void Show(const bool redraw=false); void BringToTop(void); //--- Return the hidden object flag bool IsHidden(void); //--- Set new header colors void SetHeaderNewColors(const color new_bg_color=clrNONE, const color title_new_color=clrNONE, const ushort new_alpha=USHORT_MAX) { this.m_header_back_color=(new_bg_color==clrNONE ? this.m_header_back_color : new_bg_color); this.m_header_back_color_c=this.m_header_back_color; this.m_header_fore_color=(title_new_color==clrNONE ? this.m_header_fore_color : title_new_color); this.m_header_fore_color_c=this.m_header_fore_color; this.m_header_alpha=uchar(new_alpha==USHORT_MAX ? this.m_header_alpha : (new_alpha>255 ? 255 : new_alpha)); this.m_header_alpha_c=this.m_header_alpha; } //--- Set new header properties void SetHeaderNewParams(const string title,const color new_bg_color, const color title_new_color, const ushort new_alpha=USHORT_MAX, const int title_x_shift=0,const int title_y_shift=0, const string font_name="Calibri",const int font_size=8,const uint font_flags=0) { this.SetHeaderFontParams(font_name, font_size, font_flags); this.SetTitleShift(title_x_shift,title_y_shift); this.SetHeaderNewColors(new_bg_color,title_new_color,new_alpha); this.RedrawHeaderArea(new_bg_color, title, title_new_color, new_alpha); } //--- Set the panel (1) width and (2) height bool SetWidth(const int width,const bool redraw=false); bool SetHeight(const int height,const bool redraw=false); //--- Display the panel void View(const string title) { this.Draw(title); } //--- Return the (1) CCanvas object, (2) working space, (3) object ID CCanvas *Canvas(void) { return &this.m_canvas; } CCanvas *Workspace(void) { return &this.m_workspace; } uint ID(void) const { return this.m_id; } //--- Return the panel (1) X and (2) Y coordinates int CoordX(void) const { return this.m_x; } int CoordY(void) const { return this.m_y; } //--- Return the panel (1) width and (2) height int Width(void) const { return this.m_w; } int Height(void) const { return this.m_h; } //--- Returns the offset of the dashboard (1) X and (2) Y local coordinate int CoordDiffX(void) const { return this.m_diff_x; } int CoordDiffY(void) const { return this.m_diff_y; } //--- Set the offset of the dashboard (1) X and (2) Y local coordinate void SetCoordDiffX(const int diff_x) { this.m_diff_x=diff_x; } void SetCoordDiffY(const int diff_y) { this.m_diff_y=diff_y; } //--- Set the offsets of the header text (1) horizontally, (2) vertically, (3) both void SetTitleXShift(const int shift) { this.m_title_x_shift=shift; } void SetTitleYShift(const int shift) { this.m_title_y_shift=shift; } void SetTitleShift(const int x_shift, const int y_shift) { if(this.m_title_x_shift!=x_shift) this.m_title_x_shift=x_shift; if(this.m_title_y_shift!=y_shift) this.m_title_y_shift=y_shift; } //--- Return the (1) width, (2) height and (3) size of the specified text int TextWidth(const string text) { return this.m_workspace.TextWidth(text); } int TextHeight(const string text) { return this.m_workspace.TextHeight(text); } void TextSize(const string text,int &width,int &height) { this.m_workspace.TextSize(text,width,height); } //--- Set the panel header (1) presence or (2) absence flag void SetPanelHeaderOn(const bool redraw=false); void SetPanelHeaderOff(const bool redraw=false); //--- Set the close button (1) presence, (2) absence flag void SetButtonCloseOn(const bool redraw=false); void SetButtonCloseOff(const bool redraw=false); //--- Set the collapse/expand button (1) presence, (2) absence flag void SetButtonMinimizeOn(const bool redraw=false); void SetButtonMinimizeOff(const bool redraw=false); //--- Sets the flag (1) of presence, (2) absence of the pin/unpin button void SetButtonPinOn(const bool redraw=false); void SetButtonPinOff(const bool redraw=false); //--- Set the panel coordinates bool SetCoords(const int x,const int y); //--- Set the panel size bool SetSizes(const int w,const int h,const bool update=false); //--- Set panel coordinates and size bool SetParams(const int x,const int y,const int w,const int h,const bool update=false); //--- Set the transparency of the panel (1) header and (2) working space void SetHeaderTransparency(const uchar value); void SetTransparency(const uchar value); //--- Sets the default font parameters (1) of the dashboard, (2) of the header void SetFontParams(const string name,const int size,const uint flags=0,const uint angle=0); void SetHeaderFontParams(const string name,const int size,const uint flags=0,const uint angle=0); //--- Return the set font parameters (1) of the dashboard, (2) of the header string FontParams(int &size,uint &flags,uint &angle); string FontHeaderParams(int &size,uint &flags,uint &angle); //--- Return the specified panel (1) font, (2) size and font flags string FontName(void) const { return this.m_workspace.FontNameGet(); } int FontSize(void) const { return this.m_workspace.FontSizeGet(); } uint FontFlags(void) const { return this.m_workspace.FontFlagsGet(); } //--- Return the set (1) font, (2) size, (3) flags of the header font string FontHeaderName(void) const { return this.m_canvas.FontNameGet(); } int FontHeaderSize(void) const { return this.m_canvas.FontSizeGet(); } uint FontHeaderFlags(void) const { return this.m_canvas.FontFlagsGet(); } //--- (1) Set and (2) return the color of the text of the dashboard work area void SetForeColor(const color clr) { this.m_fore_color=clr; } color ForeColor(void) const { return this.m_fore_color; } //--- Display (2) a text message, (2) a filled rectangle at the specified coordinates void DrawText(const string text,const int x,const int y,const color clr=clrNONE,const int width=WRONG_VALUE,const int height=WRONG_VALUE); void DrawRectangleFill(const int x,const int y,const int width,const int height,const color clr,const uchar alpha); //--- Create a new table bool CreateNewTable(const int id=WRONG_VALUE); //--- Return the tabular data object by (1) ID, (2) name and the (3) number of tables in the list CTableData *GetTable(const uint id); CTableData *GetTable(const string name); int TableTotal(void) const { return this.m_list_table.Total(); } //--- Return the flag of the presence of a table in the list by (1) ID and (2) name bool TableIsExist(const uint id); bool TableIsExist(const string name); //--- Draw a (1) background grid (2) with automatic cell size void DrawGrid(const uint table_id,const int x,const int y,const uint rows,const uint columns,const uint row_size,const uint col_size,const color line_color=clrNONE,bool alternating_color=true); void DrawGridAutoFill(const uint table_id,const uint border,const uint rows,const uint columns,const color line_color=clrNONE,bool alternating_color=true); //--- Erases everything drawn on the dashboard and restores the original appearance void Clear(void) { this.m_canvas.Erase(::ColorToARGB(this.m_back_color,this.m_alpha)); this.DrawFrame(); this.m_workspace.Erase(0x00FFFFFF); } //--- Print grid data (line intersection coordinates) void GridPrint(const uint table_id,const uint tabulation=0) { CTableData *table=this.GetTable(table_id); if(table==NULL) { ::PrintFormat("%s: Error. Failed to get table object with id %lu",__FUNCTION__,table_id); return; } table.Print(tabulation); } //--- Write the X and Y coordinate values of the specified table cell to variables void CellXY(const uint table_id,const uint row,const uint column, int &x, int &y) { CTableData *table=this.GetTable(table_id); if(table==NULL) { ::PrintFormat("%s: Error. Failed to get table object with id %lu",__FUNCTION__,table_id); return; } table.CellXY(row,column,x,y); } //--- Return the (1) X and (2) Y coordinate of the specified table cell int CellX(const uint table_id,const uint row,const uint column) { CTableData *table=this.GetTable(table_id); if(table==NULL) { ::PrintFormat("%s: Error. Failed to get table object with id %lu",__FUNCTION__,table_id); return WRONG_VALUE; } return table.CellX(row,column); } int CellY(const uint table_id,const uint row,const uint column) { CTableData *table=this.GetTable(table_id); if(table==NULL) { ::PrintFormat("%s: Error. Failed to get table object with id %lu",__FUNCTION__,table_id); return WRONG_VALUE; } return table.CellY(row,column); } //--- Write X1 and Y1, X2 and Y2 coordinate values of the specified table to the variables void TableCoords(const uint table_id,int &x1,int &y1,int &x2,int &y2) { x1=y1=x2=y2=WRONG_VALUE; CTableData *table=this.GetTable(table_id); if(table==NULL) return; x1=table.X1(); y1=table.Y1(); x2=table.X2(); y2=table.Y2(); } //--- Return the (1) X1, (2) Y1, (3) X2 and (4) Y2 coordinate of the specified table int TableX1(const uint table_id) { CTableData *table=this.GetTable(table_id); return(table!=NULL ? table.X1() : WRONG_VALUE); } int TableY1(const uint table_id) { CTableData *table=this.GetTable(table_id); return(table!=NULL ? table.Y1() : WRONG_VALUE); } int TableX2(const uint table_id) { CTableData *table=this.GetTable(table_id); return(table!=NULL ? table.X2() : WRONG_VALUE); } int TableY2(const uint table_id) { CTableData *table=this.GetTable(table_id); return(table!=NULL ? table.Y2() : WRONG_VALUE); } //--- Compare two objects by ID virtual int Compare(const CObject *node,const int mode=0) const { const CDashboard *obj=node; return(this.ID()>obj.ID() ? 1 : this.ID()<obj.ID() ? -1 : 0); } //--- Create and bind a new dashboard CDashboard *InsertNewPanel(const uint id, const int x, const int y, const int w, const int h) { CDashboard *obj=new CDashboard(id, this.CoordX()+x, this.CoordY()+y, w, (h>20 ? h : 21)); if(obj==NULL) return NULL; int diff_x=obj.CoordX()-this.CoordX(); int diff_y=obj.CoordY()-this.CoordY(); this.m_list_obj.Sort(); if(this.m_list_obj.Search(obj)==0 || !this.m_list_obj.Add(obj)) { delete obj; return NULL; } obj.SetCoordDiffX(diff_x); obj.SetCoordDiffY(diff_y); obj.SetAsSlave(); return obj; } //--- Return a pointer to a panel by (1) ID and (2) name CDashboard *GetPanel(const uint id) { for(int i=0;i<this.m_list_obj.Total();i++) { CDashboard *obj=this.m_list_obj.At(i); if(obj!=NULL && obj.ID()==id) return obj; } return NULL; } CDashboard *GetPanel(const string name) { for(int i=0;i<this.m_list_obj.Total();i++) { CDashboard *obj=this.m_list_obj.At(i); if(obj!=NULL && obj.Name()==name) return obj; } return NULL; } //--- (1) Set and (2) return the dependent object flag void SetAsSlave(void) { this.m_slave=true; this.m_movable=false; } bool IsSlave(void) const { return this.m_slave; } //--- Return a flag that the object with the specified name belongs to the dashboard (e.g. created by the dashboard object) bool IsOwnObject(const string object_name) const { string bmp=::ObjectGetString(this.m_chart_id,object_name,OBJPROP_BMPFILE); return(::StringFind(bmp,this.m_program_name+".ex5::")>WRONG_VALUE); } //--- (1) Set and (2) return the panel name void SetName(const string name) { this.m_name=name; } string Name(void) const { return this.m_name; } //--- Return the panel header text string HeaderTitle(void) const { return this.m_title; } //--- Event handler
All new methods and improvements made now allow us to attach child panels to the parent dashboard and use them as independent objects, but dependent on their parent. It is now possible to create scrollable tables if their size is larger than the size of the dashboard itself. Previously, tables could only be smaller in size than the dashboard itself. There is no table scrolling functionality in the dashboard class - we will arrange it directly from the main program. Subsequently, if necessary, such functionality will be added to the panel class and its tables. But at the moment there is no such need.
Naturally, here we have considered only a small part of the changes made to the dashboard class and its tables - only the declared methods. Improvements have been made gradually to a fairly large part of the code over the time since the first publication. You can always download the first version of the dashboard from the article and compare it with the version presented in the article. The dashboard file should be located in the project directory: \MQL5\Indicators\StatisticsBy\Dashboard\Dashboard.mqh.
Functions for working with the database
My experience with handling databases is not too extensive. It was a joint project for the gaming industry in C#, where another person was working on the DB, and I just used the provided connector to connect the DB to the project. Therefore, I had to arm myself with reference materials and articles on mql5.com. When studying the article "SQLite: Native handling of SQL databases in MQL5", I immediately saw references to the documentation, namely the DatabasePrepare() function. The function contains an example of creating a table of deals, which in turn is used to create a table of trades. This is one of the things we need! Let's arm ourselves with patience and study the example and its functions.
First, we see two structures for storing deal and trade data:
//--- 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; // login time ulong ticket; // position ID char type; // buy or sell double volume; // volume pair symbol; // symbol double price_in; // entry price datetime time_out; // exit time double price_out; // exit price double commission; // entry and exit fees double swap; // swap double profit; // profit or loss };
Next, we analyze the logic:
//+------------------------------------------------------------------+ //| 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); } //--- remove the query 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")) //--- fill in 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); } //--- remove the query after use DatabaseFinalize(request); //--- close the database DatabaseClose(db); }
- Create a database,
- create a deal table in the database,
- request the deal history and enter the deals into the created table,
- check the account type. There must be a hedge, since for netting it is simply impossible to create a trading history based on deals alone,
- a trade table is created based on the deal table, and trade data is entered into the trade table based on the deal table data.
The presented script also prints the first ten deals and the first ten trades from the created tables. We do not need this.
Based on the logic, we need to create several functions based on those presented in the example and from the lines of code in the body of the sample script:
//+------------------------------------------------------------------+ //| Create 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 a 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); } //+------------------------------------------------------------------+ //| Delete 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); } //+------------------------------------------------------------------+ //| Add deals to the database table | //+------------------------------------------------------------------+ bool InsertDeals(int database) { //--- auxiliary variables ulong deal_ticket; // deal ticket long order_ticket; // a ticket of an order a deal was executed by long position_ticket; // ID of a position a deal belongs to datetime time; // deal execution time long type ; // deal type long entry ; // deal direction string symbol; // a symbol a deal was executed for 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 query 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); } //+------------------------------------------------------------------+ //| Create 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 a 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); } //+------------------------------------------------------------------+
In addition to the fields of the structures and tables presented in the example, we also need a field in which the account index will be stored - this will be required to create a statistics table for trading on the account. In other words, we need complete trading statistics.
How can we make statistics tables? Read this article! This is exactly what we need:
Portfolio analysis by strategies
The results of the DatabasePrepare script operation shown above make it clear that trading is conducted on multiple currency pairs. Besides, the [magic] column shows the values from 100 to 600. This means that the trading account is managed by several strategies each of them having its own Magic Number to identify its deals.
An SQL query allows us to analyze trading in context of magic values:
//--- get trading statistics for Expert Advisors by Magic Number request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT MAGIC," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY MAGIC" " ) as r");
Result:
Trade statistics by Magic Number [magic] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor] [0] 100 242 2584.80000 -2110.00000 -33.36000 -93.53000 474.80000 347.91000 143 99 1.96198 59.09091 40.90909 18.07552 -21.31313 1.22502 [1] 200 254 3021.92000 -2834.50000 -29.45000 -98.22000 187.42000 59.75000 140 114 0.73787 55.11811 44.88189 21.58514 -24.86404 1.06612 [2] 300 250 2489.08000 -2381.57000 -34.37000 -96.58000 107.51000 -23.44000 134 116 0.43004 53.60000 46.40000 18.57522 -20.53078 1.04514 [3] 400 224 1272.50000 -1283.00000 -24.43000 -64.80000 -10.50000 -99.73000 131 93 -0.04687 58.48214 41.51786 9.71374 -13.79570 0.99182 [4] 500 198 1141.23000 -1051.91000 -27.66000 -63.36000 89.32000 -1.70000 116 82 0.45111 58.58586 41.41414 9.83819 -12.82817 1.08491 [5] 600 214 1317.10000 -1396.03000 -34.12000 -68.48000 -78.93000 -181.53000 116 98 -0.36883 54.20561 45.79439 11.35431 -14.24520 0.94346
4 out of 6 strategies have turned out to be profitable. We have received statistical values for each strategy:
- trades — number of trades by strategy,
- gross_profit — total profit by strategy (the sum of all positive profit values),
- gross_loss — total loss by strategy (the sum of all negative profit values),
- total_commission — sum of all commissions by strategy trades,
- total_swap — sum of all swaps by strategy trades,
- total_profit — gross_profit and gross_loss sum,
- net_profit — sum (gross_profit + gross_loss + total_commission + total_swap),
- win_trades — number of trades where profit>0,
- loss_trades — number of trades where profit<0,
- expected_payoff — expected payoff for the trade excluding swaps and commissions = net_profit/trades,
- win_percent — percentage of winning trades,
- loss_percent — percentage of losing trades,
- average_profit — average win = gross_profit/win_trades,
- average_loss — average loss = gross_loss /loss_trades,
- profit_factor — profit factor = gross_profit/gross_loss.
Statistics for calculating profit and loss does not consider swaps and commissions accrued on the position. This allows you to see the net costs. It may turn out that a strategy yields a small profit but is generally unprofitable due to swaps and commissions.
Analyzing deals by symbols
We are able to analyze trading by symbols. To do this, make the following query:
//--- get trading statistics per symbols int request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT SYMBOL," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY SYMBOL" " ) as r");
Result:
Trade statistics by Symbol [name] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor] [0] "AUDUSD" 112 503.20000 -568.00000 -8.83000 -24.64000 -64.80000 -98.27000 70 42 -0.57857 62.50000 37.50000 7.18857 -13.52381 0.88592 [1] "EURCHF" 125 607.71000 -956.85000 -11.77000 -45.02000 -349.14000 -405.93000 54 71 -2.79312 43.20000 56.80000 11.25389 -13.47676 0.63512 [2] "EURJPY" 127 1078.49000 -1057.83000 -10.61000 -45.76000 20.66000 -35.71000 64 63 0.16268 50.39370 49.60630 16.85141 -16.79095 1.01953 [3] "EURUSD" 233 1685.60000 -1386.80000 -41.00000 -83.76000 298.80000 174.04000 127 106 1.28240 54.50644 45.49356 13.27244 -13.08302 1.21546 [4] "GBPCHF" 125 1881.37000 -1424.72000 -22.60000 -51.56000 456.65000 382.49000 80 45 3.65320 64.00000 36.00000 23.51712 -31.66044 1.32052 [5] "GBPJPY" 127 1943.43000 -1776.67000 -18.84000 -52.46000 166.76000 95.46000 76 51 1.31307 59.84252 40.15748 25.57145 -34.83667 1.09386 [6] "GBPUSD" 121 1668.50000 -1438.20000 -7.96000 -49.93000 230.30000 172.41000 77 44 1.90331 63.63636 36.36364 21.66883 -32.68636 1.16013 [7] "USDCAD" 99 405.28000 -475.47000 -8.68000 -31.68000 -70.19000 -110.55000 51 48 -0.70899 51.51515 48.48485 7.94667 -9.90563 0.85238 [8] "USDCHF" 206 1588.32000 -1241.83000 -17.98000 -65.92000 346.49000 262.59000 131 75 1.68199 63.59223 36.40777 12.12458 -16.55773 1.27902 [9] "USDJPY" 107 464.73000 -730.64000 -35.12000 -34.24000 -265.91000 -335.27000 50 57 -2.48514 46.72897 53.27103 9.29460 -12.81825 0.63606
Statistics shows that the net profit was received on 5 out of 10 symbols (net_profit>0), while the profit factor was positive on 6 out of 10 symbols (profit_factor>1). This is exactly the case when swaps and commissions make the strategy unprofitable on EURJPY.
Let's read the article further:
Find the full source code with these three query types in the example for the DatabaseExecute() function.
Great! Follow the link to the help and get the full code from the example for this function:
//--- symbol statistics struct Symbol_Stats { string name; // symbol name int trades; // number of trades for the symbol double gross_profit; // total profit for the symbol double gross_loss; // total loss for the symbol double total_commission; // total commission for the symbol double total_swap; // total swaps for the symbol double total_profit; // total profit excluding swaps and commissions double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor }; //--- Magic Number statistics struct Magic_Stats { long magic; // EA's Magic Number int trades; // number of trades for the symbol double gross_profit; // total profit for the symbol double gross_loss; // total loss for the symbol double total_commission; // total commission for the symbol double total_swap; // total swaps for the symbol double total_profit; // total profit excluding swaps and commissions double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor }; //--- statistics by entry hour struct Hour_Stats { char hour_in; // market entry hour int trades; // number of trades in this entry hour double volume; // volume of trades in this entry hour double gross_profit; // total profit in this entry hour double gross_loss; // total loss in this entry hour double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor }; int ExtDealsTotal=0;; //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { //--- create the file name string filename=IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+"_stats.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; } PrintFormat("Deals in the trading history: %d ", ExtDealsTotal); //--- get trading statistics per symbols int request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT SYMBOL," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY SYMBOL" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", GetLastError()); DatabaseClose(db); return; } Symbol_Stats stats[], symbol_stats; ArrayResize(stats, ExtDealsTotal); int i=0; //--- get entries from request results for(; DatabaseReadBind(request, symbol_stats) ; i++) { stats[i].name=symbol_stats.name; stats[i].trades=symbol_stats.trades; stats[i].gross_profit=symbol_stats.gross_profit; stats[i].gross_loss=symbol_stats.gross_loss; stats[i].total_commission=symbol_stats.total_commission; stats[i].total_swap=symbol_stats.total_swap; stats[i].total_profit=symbol_stats.total_profit; stats[i].net_profit=symbol_stats.net_profit; stats[i].win_trades=symbol_stats.win_trades; stats[i].loss_trades=symbol_stats.loss_trades; stats[i].expected_payoff=symbol_stats.expected_payoff; stats[i].win_percent=symbol_stats.win_percent; stats[i].loss_percent=symbol_stats.loss_percent; stats[i].average_profit=symbol_stats.average_profit; stats[i].average_loss=symbol_stats.average_loss; stats[i].profit_factor=symbol_stats.profit_factor; } ArrayResize(stats, i); Print("Trade statistics by Symbol"); ArrayPrint(stats); Print(""); //--- delete the query DatabaseFinalize(request); //--- get trading statistics for Expert Advisors by Magic Number request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT MAGIC," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY MAGIC" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", GetLastError()); DatabaseClose(db); return; } Magic_Stats EA_stats[], magic_stats; ArrayResize(EA_stats, ExtDealsTotal); i=0; //--- display entries for(; DatabaseReadBind(request, magic_stats) ; i++) { EA_stats[i].magic=magic_stats.magic; EA_stats[i].trades=magic_stats.trades; EA_stats[i].gross_profit=magic_stats.gross_profit; EA_stats[i].gross_loss=magic_stats.gross_loss; EA_stats[i].total_commission=magic_stats.total_commission; EA_stats[i].total_swap=magic_stats.total_swap; EA_stats[i].total_profit=magic_stats.total_profit; EA_stats[i].net_profit=magic_stats.net_profit; EA_stats[i].win_trades=magic_stats.win_trades; EA_stats[i].loss_trades=magic_stats.loss_trades; EA_stats[i].expected_payoff=magic_stats.expected_payoff; EA_stats[i].win_percent=magic_stats.win_percent; EA_stats[i].loss_percent=magic_stats.loss_percent; EA_stats[i].average_profit=magic_stats.average_profit; EA_stats[i].average_loss=magic_stats.average_loss; EA_stats[i].profit_factor=magic_stats.profit_factor; } ArrayResize(EA_stats, i); Print("Trade statistics by Magic Number"); ArrayPrint(EA_stats); Print(""); //--- delete the query 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 if(DatabaseTableExists(db, "DEALS")) //--- fill in the TRADES table if(!DatabaseExecute(db, "INSERT INTO TRADES(TIME_IN,HOUR_IN,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) " "SELECT " " d1.time as time_in," " d1.hour as hour_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 table TRADES failed with code ", GetLastError()); return; } //--- get trading statistics by market entry hours request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT HOUR_IN," " count() as trades," " sum(volume) as volume," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(profit) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM TRADES " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY HOUR_IN" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", GetLastError()); DatabaseClose(db); return; } Hour_Stats hours_stats[], h_stats; ArrayResize(hours_stats, ExtDealsTotal); i=0; //--- display entries for(; DatabaseReadBind(request, h_stats) ; i++) { hours_stats[i].hour_in=h_stats.hour_in; hours_stats[i].trades=h_stats.trades; hours_stats[i].volume=h_stats.volume; hours_stats[i].gross_profit=h_stats.gross_profit; hours_stats[i].gross_loss=h_stats.gross_loss; hours_stats[i].net_profit=h_stats.net_profit; hours_stats[i].win_trades=h_stats.win_trades; hours_stats[i].loss_trades=h_stats.loss_trades; hours_stats[i].expected_payoff=h_stats.expected_payoff; hours_stats[i].win_percent=h_stats.win_percent; hours_stats[i].loss_percent=h_stats.loss_percent; hours_stats[i].average_profit=h_stats.average_profit; hours_stats[i].average_loss=h_stats.average_loss; hours_stats[i].profit_factor=h_stats.profit_factor; } ArrayResize(hours_stats, i); Print("Trade statistics by entry hour"); ArrayPrint(hours_stats); Print(""); //--- delete the query DatabaseFinalize(request); //--- close the database DatabaseClose(db); return; } /* Deals in the trading history: 2771 Trade statistics by Symbol [name] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor] [0] "AUDUSD" 112 503.20000 -568.00000 -8.83000 -24.64000 -64.80000 -98.27000 70 42 -0.57857 62.50000 37.50000 7.18857 -13.52381 0.88592 [1] "EURCHF" 125 607.71000 -956.85000 -11.77000 -45.02000 -349.14000 -405.93000 54 71 -2.79312 43.20000 56.80000 11.25389 -13.47676 0.63512 [2] "EURJPY" 127 1078.49000 -1057.83000 -10.61000 -45.76000 20.66000 -35.71000 64 63 0.16268 50.39370 49.60630 16.85141 -16.79095 1.01953 [3] "EURUSD" 233 1685.60000 -1386.80000 -41.00000 -83.76000 298.80000 174.04000 127 106 1.28240 54.50644 45.49356 13.27244 -13.08302 1.21546 [4] "GBPCHF" 125 1881.37000 -1424.72000 -22.60000 -51.56000 456.65000 382.49000 80 45 3.65320 64.00000 36.00000 23.51712 -31.66044 1.32052 [5] "GBPJPY" 127 1943.43000 -1776.67000 -18.84000 -52.46000 166.76000 95.46000 76 51 1.31307 59.84252 40.15748 25.57145 -34.83667 1.09386 [6] "GBPUSD" 121 1668.50000 -1438.20000 -7.96000 -49.93000 230.30000 172.41000 77 44 1.90331 63.63636 36.36364 21.66883 -32.68636 1.16013 [7] "USDCAD" 99 405.28000 -475.47000 -8.68000 -31.68000 -70.19000 -110.55000 51 48 -0.70899 51.51515 48.48485 7.94667 -9.90563 0.85238 [8] "USDCHF" 206 1588.32000 -1241.83000 -17.98000 -65.92000 346.49000 262.59000 131 75 1.68199 63.59223 36.40777 12.12458 -16.55773 1.27902 [9] "USDJPY" 107 464.73000 -730.64000 -35.12000 -34.24000 -265.91000 -335.27000 50 57 -2.48514 46.72897 53.27103 9.29460 -12.81825 0.63606 Trade statistics by Magic Number [magic] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor] [0] 100 242 2584.80000 -2110.00000 -33.36000 -93.53000 474.80000 347.91000 143 99 1.96198 59.09091 40.90909 18.07552 -21.31313 1.22502 [1] 200 254 3021.92000 -2834.50000 -29.45000 -98.22000 187.42000 59.75000 140 114 0.73787 55.11811 44.88189 21.58514 -24.86404 1.06612 [2] 300 250 2489.08000 -2381.57000 -34.37000 -96.58000 107.51000 -23.44000 134 116 0.43004 53.60000 46.40000 18.57522 -20.53078 1.04514 [3] 400 224 1272.50000 -1283.00000 -24.43000 -64.80000 -10.50000 -99.73000 131 93 -0.04687 58.48214 41.51786 9.71374 -13.79570 0.99182 [4] 500 198 1141.23000 -1051.91000 -27.66000 -63.36000 89.32000 -1.70000 116 82 0.45111 58.58586 41.41414 9.83819 -12.82817 1.08491 [5] 600 214 1317.10000 -1396.03000 -34.12000 -68.48000 -78.93000 -181.53000 116 98 -0.36883 54.20561 45.79439 11.35431 -14.24520 0.94346 Trade statistics by entry hour [hour_in] [trades] [volume] [gross_profit] [gross_loss] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor] [ 0] 0 50 5.00000 336.51000 -747.47000 -410.96000 21 29 -8.21920 42.00000 58.00000 16.02429 -25.77483 0.45020 [ 1] 1 20 2.00000 102.56000 -57.20000 45.36000 12 8 2.26800 60.00000 40.00000 8.54667 -7.15000 1.79301 [ 2] 2 6 0.60000 38.55000 -14.60000 23.95000 5 1 3.99167 83.33333 16.66667 7.71000 -14.60000 2.64041 [ 3] 3 38 3.80000 173.84000 -200.15000 -26.31000 22 16 -0.69237 57.89474 42.10526 7.90182 -12.50938 0.86855 [ 4] 4 60 6.00000 361.44000 -389.40000 -27.96000 27 33 -0.46600 45.00000 55.00000 13.38667 -11.80000 0.92820 [ 5] 5 32 3.20000 157.43000 -179.89000 -22.46000 20 12 -0.70187 62.50000 37.50000 7.87150 -14.99083 0.87515 [ 6] 6 18 1.80000 95.59000 -162.33000 -66.74000 11 7 -3.70778 61.11111 38.88889 8.69000 -23.19000 0.58886 [ 7] 7 14 1.40000 38.48000 -134.30000 -95.82000 9 5 -6.84429 64.28571 35.71429 4.27556 -26.86000 0.28652 [ 8] 8 42 4.20000 368.48000 -322.30000 46.18000 24 18 1.09952 57.14286 42.85714 15.35333 -17.90556 1.14328 [ 9] 9 118 11.80000 1121.62000 -875.21000 246.41000 72 46 2.08822 61.01695 38.98305 15.57806 -19.02630 1.28154 [10] 10 206 20.60000 2280.59000 -2021.80000 258.79000 115 91 1.25626 55.82524 44.17476 19.83122 -22.21758 1.12800 [11] 11 138 13.80000 1377.02000 -994.18000 382.84000 84 54 2.77420 60.86957 39.13043 16.39310 -18.41074 1.38508 [12] 12 152 15.20000 1247.56000 -1463.80000 -216.24000 84 68 -1.42263 55.26316 44.73684 14.85190 -21.52647 0.85227 [13] 13 64 6.40000 778.27000 -516.22000 262.05000 36 28 4.09453 56.25000 43.75000 21.61861 -18.43643 1.50763 [14] 14 62 6.20000 536.93000 -427.47000 109.46000 38 24 1.76548 61.29032 38.70968 14.12974 -17.81125 1.25606 [15] 15 50 5.00000 699.92000 -413.00000 286.92000 28 22 5.73840 56.00000 44.00000 24.99714 -18.77273 1.69472 [16] 16 88 8.80000 778.55000 -514.00000 264.55000 51 37 3.00625 57.95455 42.04545 15.26569 -13.89189 1.51469 [17] 17 76 7.60000 533.92000 -1019.46000 -485.54000 44 32 -6.38868 57.89474 42.10526 12.13455 -31.85813 0.52373 [18] 18 52 5.20000 237.17000 -246.78000 -9.61000 24 28 -0.18481 46.15385 53.84615 9.88208 -8.81357 0.96106 [19] 19 52 5.20000 407.67000 -150.36000 257.31000 30 22 4.94827 57.69231 42.30769 13.58900 -6.83455 2.71129 [20] 20 18 1.80000 65.92000 -89.09000 -23.17000 9 9 -1.28722 50.00000 50.00000 7.32444 -9.89889 0.73993 [21] 21 10 1.00000 41.86000 -32.38000 9.48000 7 3 0.94800 70.00000 30.00000 5.98000 -10.79333 1.29277 [22] 22 14 1.40000 45.55000 -83.72000 -38.17000 6 8 -2.72643 42.85714 57.14286 7.59167 -10.46500 0.54408 [23] 23 2 0.20000 1.20000 -1.90000 -0.70000 1 1 -0.35000 50.00000 50.00000 1.20000 -1.90000 0.63158 */ //+------------------------------------------------------------------+ //| Create 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 a 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," "HOUR INT," "REASON INT);")) { Print("DB: create the DEALS table failed with code ", GetLastError()); return(false); } //--- 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); ExtDealsTotal=HistoryDealsTotal(); //--- add deals to the table if(!InsertDeals(database)) return(false); //--- the table has been successfully created return(true); } //+------------------------------------------------------------------+ //| Delete 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); } //+------------------------------------------------------------------+ //| Add deals to the database table | //+------------------------------------------------------------------+ bool InsertDeals(int database) { //--- auxiliary variables ulong deal_ticket; // deal ticket long order_ticket; // a ticket of an order a deal was executed by long position_ticket; // ID of a position a deal belongs to datetime time; // deal execution time long type ; // deal type long entry ; // deal direction string symbol; // a symbol a deal was executed for 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 char hour; // deal execution hour MqlDateTime time_strusture; //--- 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); TimeToStruct(time, time_strusture); hour= (char)time_strusture.hour; //--- add each deal to the table using the following query string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON,HOUR)" "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d,%d)", deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason, hour); 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 ", __FUNCTION__, GetLastError()); return(false); } //--- all transactions have been performed successfully - record changes and unlock the database DatabaseTransactionCommit(database); return(true); } //+------------------------------------------------------------------+ //| Create 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 a table if(!DatabaseExecute(database, "CREATE TABLE TRADES(" "TIME_IN INT NOT NULL," "HOUR_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); } //+------------------------------------------------------------------+
You can copy it into the editor, compile it, run it, and see the results of its work in the journal.
Now we have examples of how to work with a database to get the result we need. It will only require a little modification of the codes presented in the help. For example, we need the data to be sorted by some field, or get only unique values from tables. To do this, we can read the reference info on SQL. Based on the knowledge and examples we have gained, we will be able to do what we need for the intended project.
In the \MQL5\Indicators\ terminal folder, create the StaticticsBy\ folder. It will contain all the files of the project.
In the created folder, create the new file SQLiteFunc.mqh and start filling it with functions for handling the database.
First of all, let's write the necessary structures:
//+------------------------------------------------------------------+ //| SQLiteFunc.mqh | //| Copyright 2024, MetaQuotes Ltd. | //| https://www.mql5.com | //+------------------------------------------------------------------+ #property copyright "Copyright 2024, MetaQuotes Ltd." #property link "https://www.mql5.com" //+------------------------------------------------------------------+ //| Structure for storing the deal | //+------------------------------------------------------------------+ struct SDeal { long account; // ACCOUNT 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 date: | //| the order of members corresponds to the position in the terminal | //+------------------------------------------------------------------+ struct STrade { long account; // account index datetime time_in; // login time ulong ticket; // position ID char type; // buy or sell double volume; // volume pair symbol; // symbol double price_in; // entry price datetime time_out; // exit time double price_out; // exit price double commission; // entry and exit fees double swap; // swap double profit; // profit or loss }; //+------------------------------------------------------------------+ //| Structure for storing statistics on a symbol | //+------------------------------------------------------------------+ struct SSymbolStats { string name; // symbol name int trades; // number of trades for the symbol double gross_profit; // total profit for the symbol double gross_loss; // total loss for the symbol double total_commission; // total commission for the symbol double total_swap; // total swaps for the symbol double total_profit; // total profit excluding swaps and commissions double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades long long_trades; // long trades long short_trades; // short trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor }; //+------------------------------------------------------------------+ //| Structure for storing statistics on Magic Number | //+------------------------------------------------------------------+ struct SMagicStats { long magic; // EA's Magic Number int trades; // number of trades for the symbol double gross_profit; // total profit for the symbol double gross_loss; // total loss for the symbol double total_commission; // total commission for the symbol double total_swap; // total swaps for the symbol double total_profit; // total profit excluding swaps and commissions double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades long long_trades; // long trades long short_trades; // short trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor }; //+------------------------------------------------------------------+ //| Structure for storing statistics on an account | //+------------------------------------------------------------------+ struct SAccountStats { long account; // account index int trades; // number of trades for the symbol double gross_profit; // total profit for the symbol double gross_loss; // total loss for the symbol double total_commission; // total commission for the symbol double total_swap; // total swaps for the symbol double total_profit; // total profit excluding swaps and commissions double net_profit; // net profit taking into account swaps and commissions int win_trades; // number of profitable trades int loss_trades; // number of losing trades long long_trades; // long trades long short_trades; // short trades double expected_payoff; // expected payoff for the trade excluding swaps and commissions double win_percent; // percentage of winning trades double loss_percent; // percentage of losing trades double average_profit; // average profit double average_loss; // average loss double profit_factor; // profit factor };
The structures are copied from the examples shown above in the Documentation, but they are given different names. Also, we have added fields with the account index with the number of short and long positions, by which it will be possible to make a selection from the database.
We need the deal history to create the deal table in the database. Therefore, we will write the function for obtaining the deal history in the same file:
//+------------------------------------------------------------------+ //| Request the deal history for the specified period | //+------------------------------------------------------------------+ bool GetHistoryDeals(const datetime from_date, const datetime to_date) { ResetLastError(); if(HistorySelect(from_date, to_date)) return true; Print("HistorySelect() failed. Error ", GetLastError()); return false; }
Here we will also enter a function for deleting a table with the specified name from the database:
//+------------------------------------------------------------------+ //| Delete a table with the specified name from the database | //+------------------------------------------------------------------+ bool DeleteTable(int database, string table_name) { ResetLastError(); 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); }
Let's write the deal table function:
//+------------------------------------------------------------------+ //| Create 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 ResetLastError(); if(!DatabaseTableExists(database, "DEALS")) //--- create a table if(!DatabaseExecute(database, "CREATE TABLE DEALS(" "ID INT KEY NOT NULL," "ACCOUNT INT 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); }
The function code is copied from the help. However, I have added one more field - account index.
Similarly, let's implement the trade table function also featuring the field with the account index:
//+------------------------------------------------------------------+ //| Create 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 ResetLastError(); if(!DatabaseTableExists(database, "TRADES")) //--- create a table if(!DatabaseExecute(database, "CREATE TABLE TRADES(" "ACCOUNT INT NOT NULL," "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); }
Let's write the function to fill the database table with deal data:
//+------------------------------------------------------------------+ //| Add deals to the database table | //+------------------------------------------------------------------+ bool InsertDeals(int database) { //--- auxiliary variables long account_login=AccountInfoInteger(ACCOUNT_LOGIN); // account index ulong deal_ticket; // deal ticket long order_ticket; // a ticket of an order a deal was executed by long position_ticket; // ID of a position a deal belongs to datetime time; // deal execution time long type ; // deal type long entry ; // deal direction string symbol; // a symbol a deal was executed for 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); ResetLastError(); 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 query string request_text=StringFormat("INSERT INTO DEALS (ID,ACCOUNT,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)" "VALUES (%I64d, %I64d, %I64d, %I64d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %I64d, %d)", deal_ticket, account_login, 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); }
The function code is taken from the DatabaseExecute() function example in the help. I have implemented an additional variable for storing the account index and fixed the request text, since there is most likely an error in the help: int data type is specified for long type data when composing the query string text :
//--- add each deal to the table using the following query string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON,HOUR)" "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d,%d)", deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason, hour);
We have fixed this by adding the account index and removing the deal entry hour, since we don't need the deal entry hour here.
Let's write the function for filling the trade table based on the deal table:
//+------------------------------------------------------------------+ //| Fill the TRADES table based on DEALS table | //+------------------------------------------------------------------+ bool FillTRADEStableBasedOnDEALStable(int database) { if(!DatabaseTableExists(database, "DEALS")) { PrintFormat("%s: Error. DEALS table is missing in the database", __FUNCTION__); return false; } //--- fill in the TRADES table if(!DatabaseExecute(database, "INSERT INTO TRADES(TIME_IN,ACCOUNT,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) " "SELECT " " d1.time as time_in," " d1.account as account," " 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 false; } return true; }
Just like in the functions above, the account index has been added here.
Let's write the function that fills a list of all trades from the database:
//+------------------------------------------------------------------+ //| Fill the list of all trades from the database | //+------------------------------------------------------------------+ bool FillsListTradesFromDB(int database, string db_name, STrade &array[]) { STrade trade; ResetLastError(); //--- Request a list of trades from the DB sorted by descending market entry time int request=DatabasePrepare(database, "SELECT * FROM TRADES ORDER BY time_in DESC"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- Read the data of the created trade table into the array of structures for(int i=0; DatabaseReadBind(request, trade); i++) { ArrayResize(array, i+1); array[i].account=trade.account; array[i].time_in=trade.time_in; array[i].ticket=trade.ticket; array[i].type=trade.type; array[i].volume=trade.volume; array[i].symbol=trade.symbol; array[i].price_in=trade.price_in; array[i].time_out=trade.time_out; array[i].price_out=trade.price_out; array[i].commission=trade.commission; array[i].swap=trade.swap; array[i].profit=trade.profit; } //--- remove the query after use DatabaseFinalize(request); return true; }
Here we have added sorting of the list of trades in descending order of market entry time. If you do not do this, the last trade will be at the end of the list, and accordingly, in the table displayed on the panel it will be at the very bottom. This is inconvenient. Sorting in descending order will move the latest trade to the very beginning of the table - up on the dashboard, and the latest trades will be immediately visible without a long scroll through the list of trades to get to them.
Let's write the function that fills in the list of all symbols traded from the database:
//+------------------------------------------------------------------+ //| Fill the list of all symbols from the database | //+------------------------------------------------------------------+ bool FillsListSymbolsFromDB(int database, string db_name, string &array[]) { //--- Check the presence of the created trade table in the database ResetLastError(); if(!DatabaseTableExists(database, "TRADES")) { //--- If the table has not been created yet, inform on how to create it if(GetLastError()==5126) Alert("First you need to get the trade history.\nClick the \"Get trade history\" button."); else Print("DatabaseTableExists() failed. Error ",GetLastError()); return false; } //--- request the list of all symbols trading was carried out on from the database. The list is sorted alphabetically int request=DatabasePrepare(database, "SELECT DISTINCT symbol FROM TRADES ORDER BY symbol ASC"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- Read the data of the created symbol table into the array for(int i=0; DatabaseRead(request); i++) { ArrayResize(array, i+1); DatabaseColumnText(request, 0, array[i]); } //--- remove the query after use DatabaseFinalize(request); return true; }
Use "DISTINCT" key word to obtain the list that will contain only unique, non-repeating symbol names. The list is obtained in alphabetical order.
Similarly, implement the function filling the list of all magic numbers in ascending order from the data base:
//+------------------------------------------------------------------+ //| Fill the list of all magic numbers from the database | //+------------------------------------------------------------------+ bool FillsListMagicsFromDB(int database, string db_name, long &array[]) { //--- Check the presence of the created trade table in the database ResetLastError(); if(!DatabaseTableExists(database, "DEALS")) { //--- If the table has not been created yet, inform on how to create it if(GetLastError()==5126) Alert("First you need to get the trade history.\nClick the \"Get trade history\" button."); else Print("DatabaseTableExists() failed. Error ",GetLastError()); return false; } //--- request the list of all magic numbers trading was carried out on from the database. The list is sorted in ascending order. int request=DatabasePrepare(database, "SELECT DISTINCT magic FROM DEALS ORDER BY magic ASC"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- Read the data of the created table of magic numbers into the array for(int i=0; DatabaseRead(request); i++) { ArrayResize(array, i+1); DatabaseColumnLong(request, 0, array[i]); } //--- remove the query after use DatabaseFinalize(request); return true; }
Create the function that gets symbol-based trading statistics from the database and saves it to the array:
//+------------------------------------------------------------------------------+ //|Get symbol-based trading statistics from the database and save it to the array| //+------------------------------------------------------------------------------+ bool GetTradingStatsBySymbols(int database, string db_name, SSymbolStats &array[]) { int request=DatabasePrepare(database, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, " " r.long_trades as long_trades," " r.short_trades as short_trades " "FROM " " (" " SELECT SYMBOL," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades, " " sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, " " sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY SYMBOL ORDER BY net_profit DESC" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- get entries from request results SSymbolStats symbol_stats; for(int i=0; DatabaseReadBind(request, symbol_stats) ; i++) { ArrayResize(array, i+1); array[i].name=symbol_stats.name; array[i].trades=symbol_stats.trades; array[i].long_trades=symbol_stats.long_trades; array[i].short_trades=symbol_stats.short_trades; array[i].gross_profit=symbol_stats.gross_profit; array[i].gross_loss=symbol_stats.gross_loss; array[i].total_commission=symbol_stats.total_commission; array[i].total_swap=symbol_stats.total_swap; array[i].total_profit=symbol_stats.total_profit; array[i].net_profit=symbol_stats.net_profit; array[i].win_trades=symbol_stats.win_trades; array[i].loss_trades=symbol_stats.loss_trades; array[i].expected_payoff=symbol_stats.expected_payoff; array[i].win_percent=symbol_stats.win_percent; array[i].loss_percent=symbol_stats.loss_percent; array[i].average_profit=symbol_stats.average_profit; array[i].average_loss=symbol_stats.average_loss; array[i].profit_factor=symbol_stats.profit_factor; } //--- remove the query after use DatabaseFinalize(request); return true; }
Here I have added strings to account for long and short positions and get the list sorted in descending order of net profit, so that the symbols that generated the biggest profits were at the beginning of the table.
Similarly, we will write the function for retrieving the magic number-based trading statistics from the database and saving it to the array:
//+------------------------------------------------------------------------------------+ //|Get magic number-based trading statistics from the database and save it to the array| //+------------------------------------------------------------------------------------+ bool GetTradingStatsByMagics(int database, string db_name, SMagicStats &array[]) { int request=DatabasePrepare(database, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, " " r.long_trades as long_trades," " r.short_trades as short_trades " "FROM " " (" " SELECT MAGIC," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades, " " sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, " " sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY MAGIC ORDER BY net_profit DESC" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- get entries from request results SMagicStats magic_stats; for(int i=0; DatabaseReadBind(request, magic_stats) ; i++) { ArrayResize(array, i+1); array[i].magic=magic_stats.magic; array[i].trades=magic_stats.trades; array[i].long_trades=magic_stats.long_trades; array[i].short_trades=magic_stats.short_trades; array[i].gross_profit=magic_stats.gross_profit; array[i].gross_loss=magic_stats.gross_loss; array[i].total_commission=magic_stats.total_commission; array[i].total_swap=magic_stats.total_swap; array[i].total_profit=magic_stats.total_profit; array[i].net_profit=magic_stats.net_profit; array[i].win_trades=magic_stats.win_trades; array[i].loss_trades=magic_stats.loss_trades; array[i].expected_payoff=magic_stats.expected_payoff; array[i].win_percent=magic_stats.win_percent; array[i].loss_percent=magic_stats.loss_percent; array[i].average_profit=magic_stats.average_profit; array[i].average_loss=magic_stats.average_loss; array[i].profit_factor=magic_stats.profit_factor; } //--- remove the query after use DatabaseFinalize(request); return true; }
Finally, let's write a similar function for account-based trading statistics:
//+---------------------------------------------------------------------------------+ //| Get account-based trading statistics from the database and save it to the array | //+---------------------------------------------------------------------------------+ bool GetTradingStatsByAccount(int database, string db_name, SAccountStats &array[]) { int request=DatabasePrepare(database, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, " " r.long_trades as long_trades," " r.short_trades as short_trades " "FROM " " (" " SELECT ACCOUNT," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades, " " sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, " " sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY ACCOUNT ORDER BY net_profit DESC" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- get entries from request results SAccountStats account_stats; for(int i=0; DatabaseReadBind(request, account_stats) ; i++) { ArrayResize(array, i+1); array[i].account=account_stats.account; array[i].trades=account_stats.trades; array[i].long_trades=account_stats.long_trades; array[i].short_trades=account_stats.short_trades; array[i].gross_profit=account_stats.gross_profit; array[i].gross_loss=account_stats.gross_loss; array[i].total_commission=account_stats.total_commission; array[i].total_swap=account_stats.total_swap; array[i].total_profit=account_stats.total_profit; array[i].net_profit=account_stats.net_profit; array[i].win_trades=account_stats.win_trades; array[i].loss_trades=account_stats.loss_trades; array[i].expected_payoff=account_stats.expected_payoff; array[i].win_percent=account_stats.win_percent; array[i].loss_percent=account_stats.loss_percent; array[i].average_profit=account_stats.average_profit; array[i].average_loss=account_stats.average_loss; array[i].profit_factor=account_stats.profit_factor; } //--- remove the query after use DatabaseFinalize(request); return true; }
We have prepared the basic things for creating a project. Namely, we have selected the dashboard and created functions for handling the database based on information from the documentation. All that remains for us to do is to create the logic for the interaction of the dashboard and its tables with the database using the implemented functions. It is likely that the lists placed in the tables on the dashboard will be quite extensive, and the sizes of the tables will exceed the dimensions of the dashboard. In this case, we will need to scroll the tables vertically and horizontally. We will implement this functionality directly in the indicator being created - tables will be scrolled vertically by rotating the mouse wheel, and horizontally - by using the mouse wheel with the Shift key held down.
Statistics for the selected symbol or magic number will be displayed when clicking on the statistics line of the desired symbol or magic number. To do this, we will track the location of the cursor over the table rows and clicking on the row. It would be reasonable to make such functionality in the dashboard class so that it can be used in other projects. But here we will show how we can do the same thing without modifying the dashboard classes.
Assembling the dashboard
In the previously created \MQL5\Indicators\StatisticsBy\ folder, create the new indicator file named StatisticsBy.mq5.
Include the table and dashboard classes files, as well as the function file for handling DB, and inform that the indicator has no rendered buffers:
//+------------------------------------------------------------------+ //| StatisticsBy.mq5 | //| Copyright 2024, MetaQuotes Ltd. | //| https://www.mql5.com | //+------------------------------------------------------------------+ #property copyright "Copyright 2024, MetaQuotes Ltd." #property link "https://www.mql5.com" #property version "1.00" #property indicator_chart_window #property indicator_buffers 0 #property indicator_plots 0 #include "Dashboard\Dashboard.mqh" #include "SQLiteFunc.mqh"
Next, add macro substitutions, array of statistics table columns location, inputs and global variables:
#property copyright "Copyright 2024, MetaQuotes Ltd." #property link "https://www.mql5.com" #property version "1.00" #property indicator_chart_window #property indicator_buffers 0 #property indicator_plots 0 #include "Dashboard\Dashboard.mqh" #include "SQLiteFunc.mqh" #define PROGRAM_NAME (MQLInfoString(MQL_PROGRAM_NAME)) // Program name #define DB_NAME (PROGRAM_NAME+"_DB.sqlite") // Database name #define DATE_FROM 0 // Start date of deal history #define DATE_TO (TimeCurrent()) // End date of deal history //--- Table cell width #define CELL_W_TRADES 94 // Width of trading history table cells #define CELL_W_SYMBOLS 62 // Width of used symbol table cells #define CELL_W_MAGICS 62 // Width of used magic number table cells #define CELL_H 16 // Table cell height //--- Dimensions of the final statistics table #define TABLE_STAT_ROWS 9 // Number of rows in the final statistics table #define TABLE_STAT_COLS 4 // Number of columns in the final statistics table //--- Tables #define TABLE_TRADES 1 // Trade history table ID #define TABLE_SYMBOLS 2 // ID of the table of symbols used in trading #define TABLE_MAGICS 3 // ID of the table of magic numbers used in trading #define TABLE_ACCOUNT 4 // Account statistics table ID #define TABLE_STATS 5 // ID of the final statistics table of the selected symbol or magic number //--- Table headers (full/short) #define H_TRADES "Trades" #define H_TRADES_S "Trades" #define H_LONG "Long" #define H_LONG_S "Long" #define H_SHORT "Short" #define H_SHORT_S "Short" #define H_GROSS_PROFIT "Gross Profit" #define H_GROSS_PROFIT_S "Gross Profit" #define H_GROSS_LOSS "Gross Loss" #define H_GROSS_LOSS_S "Gross Loss" #define H_COMMISSIONS "Commission total" #define H_COMMISSIONS_S "Fees" #define H_SWAPS "Swap total" #define H_SWAPS_S "Swaps" #define H_PROFITS "Profit Loss" #define H_PROFITS_S "P/L" #define H_NET_PROFIT "Net Profit" #define H_NET_PROFIT_S "Net Profit" #define H_WINS "Win trades" #define H_WINS_S "Win" #define H_LOST "Loss trades" #define H_LOST_S "Lost" #define H_EXP_PAYOFF "Expected Payoff" #define H_EXP_PAYOFF_S "Avg $" #define H_WIN_PRC "Win percent" #define H_WIN_PRC_S "Win %" #define H_LOSS_PRC "Loss percent" #define H_LOSS_PRC_S "Loss %" #define H_AVG_PROFIT "Average Profit" #define H_AVG_PROFIT_S "Avg Profit" #define H_AVG_LOSS "Average Loss" #define H_AVG_LOSS_S "Avg Loss" #define H_PRF_FACTOR "Profit factor" #define H_PRF_FACTOR_S "PF" //--- Array of the location of the statistics table columns from left to right string ArrayDataName[18]= { "HEADER", H_NET_PROFIT_S, H_TRADES_S, H_GROSS_PROFIT_S, H_GROSS_LOSS_S, H_COMMISSIONS_S, H_SWAPS_S, H_PROFITS_S, H_LONG_S, H_SHORT_S, H_WINS_S, H_LOST_S, H_EXP_PAYOFF_S, H_WIN_PRC_S, H_LOSS_PRC_S, H_AVG_PROFIT_S, H_AVG_LOSS_S, H_PRF_FACTOR_S, }; //--- input parameters input int InpPanelX = 20; /* Dashboard X */ // Panel X coordinate input int InpPanelY = 20; /* Dashboard Y */ // Panel Y coordinate input int InpUniqID = 0; /* Unique ID */ // Unique ID for the panel object //--- global variables int DBHandle; // Database handle int LPanelTable; // Active panel in the left field int RPanelTable; // Active panel in the right field long ArrayMagics[]; // Array of magic numbers string ArraySymbols[]; // Array of symbols STrade ArrayTrades[]; // Array of trades SSymbolStats ArraySymbolStats[]; // Array of statistics by symbols SMagicStats ArrayMagicStats[]; // Array of statistics by magic numbers SAccountStats ArrayAccountStats[]; // Array of statistics by account CDashboard *dashboard=NULL; // Pointer to the dashboard instance
To specify the number of columns in the statistics tables and to specify the amount and location of data in the table, it is convenient to use an array containing constants for the names of table headers and, accordingly, the data under these headers. If it is necessary to change the order of different data in the table, it is sufficient to change the order of their declaration in this array and recompile the indicator. We can also remove unnecessary data by commenting it out in this array, or add new ones. But when adding new data, we will need to add it to the database functions and other functions where table data is calculated and displayed.
Let's consider the OnInit() indicator handler where the database and the dashboard with its graphic content are created:
//+------------------------------------------------------------------+ //| Custom indicator initialization function | //+------------------------------------------------------------------+ int OnInit() { //--- 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) { //--- In case of a netting account, deals cannot be transformed to trades using a simple method through transactions, therefore we complete the operation Print("For a Netting account, there is no way to convert deals into trades in a simple way."); return INIT_FAILED; } //--- Specify the path and create the database (\MQL5\Files\StatisticsBy\Database\) string path=PROGRAM_NAME+"\\Database\\"; DBHandle=DatabaseOpen(path+DB_NAME,DATABASE_OPEN_CREATE); if(DBHandle==INVALID_HANDLE) { Print("DatabaseOpen() failed. Error ", GetLastError()); return(INIT_FAILED); } PrintFormat("Database \"%s\" successfully created at MQL5\\Files\\%s", DB_NAME, path); //--- Create the program window panel dashboard = new CDashboard(InpUniqID, InpPanelX, InpPanelY, 601, 300); if(dashboard==NULL) { Print("Error. Failed to create dashboard object"); return INIT_FAILED; } //--- Display the dashboard with the program name text in the window header dashboard.SetFontParams("Calibri",8); dashboard.SetName("Main"); dashboard.View(PROGRAM_NAME); //--- Draw the workspace //--- Button for selecting symbols CDashboard *panel1=dashboard.InsertNewPanel(dashboard.ID()+1, 3, 20, 49, 21); if(panel1!=NULL) { panel1.SetName("SymbolButton"); panel1.SetButtonCloseOff(); panel1.SetButtonMinimizeOff(); panel1.SetButtonPinOff(); panel1.View(""); panel1.Collapse(); panel1.SetHeaderNewParams("Symbol",clrLightGray,clrBlack,USHORT_MAX,5,-1); } //--- Button for selecting magic numbers CDashboard *panel2=dashboard.InsertNewPanel(dashboard.ID()+2, 54, 20, 48, 21); if(panel2!=NULL) { panel2.SetName("MagicButton"); panel2.SetButtonCloseOff(); panel2.SetButtonMinimizeOff(); panel2.SetButtonPinOff(); panel2.View(""); panel2.Collapse(); panel2.SetHeaderNewParams("Magic",clrLightGray,clrBlack,USHORT_MAX,8,-1); } //--- Button for creating a list of trades CDashboard *panel3=dashboard.InsertNewPanel(dashboard.ID()+3, 105, 20, 106, 21); if(panel3!=NULL) { panel3.SetName("TradesButton"); panel3.SetButtonCloseOff(); panel3.SetButtonMinimizeOff(); panel3.SetButtonPinOff(); panel3.View(""); panel3.Collapse(); panel3.SetHeaderNewParams("Get trade history",clrLightGray,clrBlack,USHORT_MAX,10,-1); } //--- Left panel for displaying the table of symbols/magic numbers CDashboard *panel4=dashboard.InsertNewPanel(dashboard.ID()+4, 2, 38, 101, dashboard.Height()-38-2); if(panel4!=NULL) { panel4.SetName("FieldL"); panel4.SetButtonCloseOff(); panel4.SetButtonMinimizeOff(); panel4.SetButtonPinOff(); panel4.View(""); panel4.SetPanelHeaderOff(true); panel4.SetFontParams("Calibri",8); } //--- Panel on the right for displaying statistics headers for the list of trades and the selected symbol/magic number CDashboard *panel5=dashboard.InsertNewPanel(dashboard.ID()+5, 104, 38, dashboard.Width()-104-2, 20); if(panel5!=NULL) { panel5.SetName("FieldH"); panel5.SetButtonCloseOff(); panel5.SetButtonMinimizeOff(); panel5.SetButtonPinOff(); panel5.View(""); panel5.SetPanelHeaderOff(true); panel5.SetFontParams("Calibri",8,FW_EXTRABOLD); } //--- Panel on the right for displaying statistics for the list of trades and the selected symbol/magic number CDashboard *panel6=dashboard.InsertNewPanel(dashboard.ID()+6, 104, 38+20, dashboard.Width()-104-2, dashboard.Height()-38-20-2); if(panel5!=NULL) { panel6.SetName("FieldR"); panel6.SetButtonCloseOff(); panel6.SetButtonMinimizeOff(); panel6.SetButtonPinOff(); panel6.View(""); panel6.SetPanelHeaderOff(true); panel6.SetFontParams("Calibri",8); } //--- All tables on the left and right panels are initially inactive LPanelTable=WRONG_VALUE; RPanelTable=WRONG_VALUE; //--- All is successful return(INIT_SUCCEEDED); }
In essence, here the type of position accounting is first checked, and if it is netting, then the indicator stops working, since it is impossible to create a trade table in a simple way (only for entry-exit transactions) for such position accounting.
Next, in the terminal data folder (TERMINAL_DATA_PATH + \MQL5\Files\), create a database in the folder with the program name in the Database subdirectory (\StatisticsBy\Database\). After successfully creating the database, the dashboard is created and filled with content - control buttons and panels for displaying tables:
Interestingly, instead of buttons, we use child dashboards attached to the main window in a collapsed form - only the dashboard header is visible. It has its own handlers for interacting with the mouse cursor, and so we created buttons from regular dashboards that interact with the user and send mouse interaction events to the main program.
Close the database and the dashboard in the OnDeinit() handler:
//+------------------------------------------------------------------+ //| Custom indicator deinitialization function | //+------------------------------------------------------------------+ void OnDeinit(const int reason) { //--- Close the database DatabaseClose(DBHandle); if(GetLastError()==ERR_DATABASE_INVALID_HANDLE) Print("Error. An invalid database handle was passed to the DatabaseClose() function"); //--- If the panel object exists, delete it if(dashboard!=NULL) { delete dashboard; ChartRedraw(); } }
Leave the OnCalculate() handler empty (the indicator does not calculate anything):
//+------------------------------------------------------------------+ //| Custom indicator iteration function | //+------------------------------------------------------------------+ int OnCalculate(const int rates_total, const int prev_calculated, const datetime &time[], const double &open[], const double &high[], const double &low[], const double &close[], const long &tick_volume[], const long &volume[], const int &spread[]) { //--- //--- return value of prev_calculated for the next call return(rates_total); }
All work on the dashboard interaction with the user is performed in the indicator event handler.
Let's look at the OnChartEvent() event handler in its entirety. Its code has been thoroughly commented. If we carefully study the event handler comments, the entire logic of the dashboard interaction with the user becomes clear:
//+------------------------------------------------------------------+ //| ChartEvent function | //+------------------------------------------------------------------+ void OnChartEvent(const int id, const long &lparam, const double &dparam, const string &sparam) { //--- Active dashboard ID int table_id=WRONG_VALUE; //--- Call the dashboard event handler, which in turn sends its events here dashboard.OnChartEvent(id,lparam,dparam,sparam); //--- If we received a user event from the program dashboard window if(id>CHARTEVENT_CUSTOM) { //--- Dashboard close button clicked if(id==1001) { //--- Here we can implement handling a click on the close button } //--- Clicking buttons for working with DB - the ID is always 1002, while clarification is done by the lparam value if(id==1002) { //--- get deal history from the server and trade history from the DB ("Get trade history" is clicked) if(lparam==3) { //--- If the deal history is not received, leave if(!GetHistoryDeals(DATE_FROM, DATE_TO)) return; //--- If there are no deals in history, inform of that and leave int deals_total=HistoryDealsTotal(); if(deals_total==0) { Print("No deals in history"); return; } //--- create DEALS table in the database if(!CreateTableDeals(DBHandle)) return; //--- enter deals to the created table if(!InsertDeals(DBHandle)) return; //--- Create TRADES table based on DEALS table if(!CreateTableTrades(DBHandle)) return; //--- Fill in the TRADES table using an SQL query based on DEALS table data if(!FillTRADEStableBasedOnDEALStable(DBHandle)) return; //--- Request a list of all trades from the DB if(!FillsListTradesFromDB(DBHandle, DB_NAME, ArrayTrades)) return; //--- Display the number of deals and trades in history on the dashboard dashboard.DrawText(" ",2,2,clrNONE,0,0); // erase previous displayed data dashboard.DrawText("Total deals in history: "+(string)deals_total+", trades: "+(string)ArrayTrades.Size(),216,3); //--- Get the pointer to the header panel CDashboard *panel_h=dashboard.GetPanel("FieldH"); if(panel_h==NULL) return; //--- Check the presence and get or create the table object for displaying the trade table header CTableData *table_h=NULL; if(!panel_h.TableIsExist(TABLE_TRADES) && !panel_h.CreateNewTable(TABLE_TRADES)) return; //--- Get the pointer to the trade header table object table_h=panel_h.GetTable(TABLE_TRADES); if(table_h==NULL) return; //--- Clear the table header panel and display the header table on it panel_h.Clear(); panel_h.DrawGrid(TABLE_TRADES,2,2,1,11,CELL_H,CELL_W_TRADES,C'200,200,200',false); //--- Fill in the trade header table FillsHeaderTradeTable(panel_h,table_h); //--- Get the pointer to the right panel CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r==NULL) return; //--- Check for availability and get or create a table object for displaying trades if(!panel_r.TableIsExist(TABLE_TRADES) && !panel_r.CreateNewTable(TABLE_TRADES)) return; //--- Get the pointer to the trade table object CTableData *table_r=panel_r.GetTable(TABLE_TRADES); if(table_r==NULL) return; //--- Clear the panel and display the trade table on it panel_r.Clear(); panel_r.DrawGrid(TABLE_TRADES,2,2,ArrayTrades.Size(),11,CELL_H,CELL_W_TRADES,C'220,220,220'); //--- Fill the table with trade data and specify TABLE_TRADES table is active to the right FillsTradeTable(panel_r,table_r); RPanelTable=TABLE_TRADES; } //--- If the display symbol button is pressed if(lparam==1) { //--- request the list of all symbols trading was carried out on from the database and fill in the symbol array if(!FillsListSymbolsFromDB(DBHandle, DB_NAME, ArraySymbols)) return; //--- Increase the symbol array by 1 to set "All symbols" (ALL) to it int size=(int)ArraySymbols.Size(); if(ArrayResize(ArraySymbols, size+1)==size+1) ArraySymbols[size]="ALL"; //--- Get the pointer to the left panel CDashboard *panel=dashboard.GetPanel("FieldL"); if(panel==NULL) return; //--- Check for availability and get or create a table object for displaying the list of symbols CTableData *table=NULL; if(!panel.TableIsExist(TABLE_SYMBOLS) && !panel.CreateNewTable(TABLE_SYMBOLS)) return; //--- Get the pointer to the table object table=panel.GetTable(TABLE_SYMBOLS); if(table==NULL) return; //--- Clear the panel and draw a symbol table on it panel.Clear(); panel.DrawGrid(TABLE_SYMBOLS,2,2,ArraySymbols.Size(),1,CELL_H,panel.Width()-5,C'220,220,220'); //--- Fill the table with symbol names and indicate that the TABLE_SYMBOLS table is active on the left panel FillsSymbolTable(panel,table); LPanelTable=TABLE_SYMBOLS; //--- get trading statistics by symbols if(!GetTradingStatsBySymbols(DBHandle, DB_NAME, ArraySymbolStats)) return; //--- Display the number of symbols used in trading dashboard.DrawText(" ",2,2,clrNONE,0,0); // Erase all dashboard contents dashboard.DrawText("Total number of symbols used in trade: "+(string)ArraySymbols.Size(),216,3); //--- Get the pointer to the header panel CDashboard *panel_h=dashboard.GetPanel("FieldH"); if(panel_h==NULL) return; //--- Check for presence and get or create a table object to display the symbol statistics table header CTableData *table_h=NULL; if(!panel_h.TableIsExist(TABLE_SYMBOLS) && !panel_h.CreateNewTable(TABLE_SYMBOLS)) return; //--- Get the pointer to the symbol statistics header table object table_h=panel_h.GetTable(TABLE_SYMBOLS); if(table_h==NULL) return; //--- Clear the table header panel and display the table on it RPanelTable=TABLE_SYMBOLS; panel_h.Clear(); panel_h.DrawGrid(TABLE_SYMBOLS,2,2,1,ArrayDataName.Size(),CELL_H,CELL_W_SYMBOLS,C'200,200,200',false); //--- Fill the symbol statistics header table FillsHeaderTradingStatsTable(panel_h,table_h); //--- Get the pointer to the right panel CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r==NULL) return; //--- Check for availability and get or create a table object for displaying the symbol statistics if(!panel_r.TableIsExist(TABLE_SYMBOLS) && !panel_r.CreateNewTable(TABLE_SYMBOLS)) return; //--- Get the pointer to the symbol statistics table object CTableData *table_r=panel_r.GetTable(TABLE_SYMBOLS); if(table_r==NULL) return; //--- Clear the panel and display the symbol statistics table on it panel_r.Clear(); panel_r.DrawGrid(TABLE_SYMBOLS,2,2,ArraySymbolStats.Size(),ArrayDataName.Size(),CELL_H,CELL_W_SYMBOLS,C'220,220,220'); //--- Fill the table with symbol statistics data and indicate that the TABLE_SYMBOLS table is active on the right FillsTradingStatsBySymbolsTable(panel_r,table_r); RPanelTable=TABLE_SYMBOLS; } //--- If the button to display magic numbers is clicked if(lparam==2) { //--- Request the list of all magic numbers trading was performed on from the DB and fill in the array of magic numbers if(!FillsListMagicsFromDB(DBHandle, DB_NAME, ArrayMagics)) return; //--- Increase the array of magic numbers by 1 to set "All magic numbers" to it (LONG_MAX value notifies of that) int size=(int)ArrayMagics.Size(); if(ArrayResize(ArrayMagics, size+1)==size+1) ArrayMagics[size]=LONG_MAX; //--- Get the pointer to the left panel CDashboard *panel=dashboard.GetPanel("FieldL"); if(panel==NULL) return; //--- Check for availability and get or create a table object for displaying magic numbers CTableData *table=NULL; if(!panel.TableIsExist(TABLE_MAGICS) && !panel.CreateNewTable(TABLE_MAGICS)) return; //--- Get the pointer to the table object table=panel.GetTable(TABLE_MAGICS); if(table==NULL) return; //--- Clear the panel and draw a table of magic numbers on it panel.Clear(); panel.DrawGrid(TABLE_MAGICS,2,2,ArrayMagics.Size(),1,CELL_H,panel.Width()-5,C'220,220,220'); //--- Fill the table with magic number values and indicate that TABLE_MAGICS table is active on the left panel FillsMagicTable(panel,table); LPanelTable=TABLE_MAGICS; //--- Get trading statistics in the context of magic numbers if(!GetTradingStatsByMagics(DBHandle, DB_NAME, ArrayMagicStats)) return; //--- Display the number of magic numbers used in trading dashboard.DrawText(" ",2,2,clrNONE,0,0); dashboard.DrawText("Total number of magics used in trade: "+(string)ArrayMagics.Size(),216,3); //--- Get the pointer to the header panel CDashboard *panel_h=dashboard.GetPanel("FieldH"); if(panel_h==NULL) return; //--- Check for presence and get or create a table object to display the magic number statistics table header CTableData *table_h=NULL; if(!panel_h.TableIsExist(TABLE_MAGICS) && !panel_h.CreateNewTable(TABLE_MAGICS)) return; //--- Get the pointer to the magic number statistics header table object table_h=panel_h.GetTable(TABLE_MAGICS); if(table_h==NULL) return; //--- Clear the table header panel and display the table on it panel_h.Clear(); panel_h.DrawGrid(TABLE_MAGICS,2,2,1,ArrayDataName.Size(),CELL_H,CELL_W_MAGICS,C'200,200,200',false); //--- Fill the symbol statistics header table FillsHeaderTradingStatsTable(panel_h,table_h); //--- Get the pointer to the right panel CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r==NULL) return; //--- Check for availability and get or create a table object for displaying the magic number statistics if(!panel_r.TableIsExist(TABLE_MAGICS) && !panel_r.CreateNewTable(TABLE_MAGICS)) return; //--- Get the pointer to the magic number statistics table object CTableData *table_r=panel_r.GetTable(TABLE_MAGICS); if(table_r==NULL) return; //--- Clear the panel and display the magic number statistics table on it panel_r.Clear(); panel_r.DrawGrid(TABLE_MAGICS,2,2,ArrayMagicStats.Size(),ArrayDataName.Size(),CELL_H,CELL_W_MAGICS,C'220,220,220'); //--- Fill the table with magic number statistics and indicate that TABLE_MAGICS table is active FillsTradingStatsByMagicsTable(panel_r,table_r); RPanelTable=TABLE_MAGICS; } } } //--- If we received a mouse wheel scroll event if(id==CHARTEVENT_MOUSE_WHEEL) { static int index_l_p=WRONG_VALUE; // Previous index of the row under the cursor in the table on the left panel static int index_r_p=WRONG_VALUE; // Previous index of the row under the cursor in the table on the right panel //--- consider the state of mouse buttons and wheel for this event int flg_keys = (int)(lparam>>32); // the flag of states of the Ctrl and Shift keys, and mouse buttons int x_cursor = (int)(short)lparam; // X coordinate where the mouse wheel event occurred int y_cursor = (int)(short)(lparam>>16); // Y coordinate where the mouse wheel event occurred int delta = (int)dparam; // total value of mouse scroll, triggers when +120 or -120 is reached //--- Get the pointer to the left panel and call the mouse wheel scroll handler for it int index_l=WRONG_VALUE; CDashboard *panel_l=dashboard.GetPanel("FieldL"); if(panel_l!=NULL) index_l=TableMouseWhellHandlerL(x_cursor,y_cursor,((flg_keys&0x0004)!=0),delta,panel_l,LPanelTable); //--- Get the pointer to the right panel and call the mouse wheel scroll handler for it int index_r=WRONG_VALUE; CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r!=NULL) index_r=TableMouseWhellHandlerR(x_cursor,y_cursor,((flg_keys&0x0004)!=0),delta,panel_r,RPanelTable); //--- If necessary, we can handle the table row, over which the cursor is located. //--- The line number is set in index_l for the left panel and in index_r for the right one //--- Update the chart after all changes that occurred in the mouse wheel scroll handlers if(index_l_p!=index_l) { index_l_p=index_l; ChartRedraw(); } if(index_r_p!=index_r) { index_r_p=index_r; ChartRedraw(); } } //--- In case the mouse movement event received if(id==CHARTEVENT_MOUSE_MOVE) { static int index_l_p=WRONG_VALUE; // Previous index of the row under the cursor in the table on the left panel static int index_r_p=WRONG_VALUE; // Previous index of the row under the cursor in the table on the right panel int x_cursor = (int)lparam; // Mouse cursor X coordinate int y_cursor = (int)dparam; // Mouse cursor Y coordinate //--- Get the pointer to the left panel and call the mouse movement handler for it int index_l=WRONG_VALUE; CDashboard *panel_l=dashboard.GetPanel("FieldL"); if(panel_l!=NULL) index_l=TableMouseMoveHandlerL(x_cursor,y_cursor,panel_l,LPanelTable); //--- Get the pointer to the right panel and call the mouse movement handler for it int index_r=WRONG_VALUE; CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r!=NULL) index_r=TableMouseMoveHandlerR(x_cursor,y_cursor,panel_r,RPanelTable); //--- If necessary, we can handle the table row, over which the cursor is located. //--- The line number is set in index_l for the left panel and in index_r for the right one //--- Update the chart after all changes that occurred in the mouse movement handlers if(index_l_p!=index_l) { index_l_p=index_l; ChartRedraw(); } if(index_r_p!=index_r) { index_r_p=index_r; ChartRedraw(); } } //--- In case the mouse click event received if(id==CHARTEVENT_CLICK) { int x_cursor = (int)lparam; // Mouse cursor X coordinate int y_cursor = (int)dparam; // Mouse cursor Y coordinate //--- Get the pointer to the left panel and call the mouse click handler int index_l=WRONG_VALUE; CDashboard *panel_l=dashboard.GetPanel("FieldL"); if(panel_l!=NULL) index_l=TableMouseClickHandler(x_cursor,y_cursor,panel_l,LPanelTable); //--- Get the pointer to the right panel and call the mouse click handler int index_r=WRONG_VALUE; CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_r!=NULL) index_r=TableMouseClickHandler(x_cursor,y_cursor,panel_r,RPanelTable); //--- Handle the clicked table row //--- If there was a click on a symbol from the list in the left panel if(LPanelTable==TABLE_SYMBOLS && index_l>WRONG_VALUE) { //--- Get the symbol table from the left panel CTableData *table=panel_l.GetTable(TABLE_SYMBOLS); if(table==NULL) return; //--- Get the only table cell with the index_l row CTableCell *cell=table.GetCell(index_l,0); if(cell==NULL) return; //--- If the last item (ALL) is clicked if(index_l==ArraySymbols.Size()-1) { //--- get and display account trading statistics and specify that TABLE_STATS panel is active on the right if(!GetTradingStatsByAccount(DBHandle, DB_NAME, ArrayAccountStats)) return; if(ViewStatistic(TABLE_ACCOUNT,(string)AccountInfoInteger(ACCOUNT_LOGIN))) RPanelTable=TABLE_STATS; } //--- Click on the symbol name - display statistics for the symbol and indicate that TABLE_STATS panel is active on the right else { if(ViewStatistic(TABLE_SYMBOLS,cell.Text())) RPanelTable=TABLE_STATS; } } //--- If there was a click on a magic number from the list in the left panel if(LPanelTable==TABLE_MAGICS && index_l>WRONG_VALUE) { //--- Get the magic number table from the left panel CTableData *table=panel_l.GetTable(TABLE_MAGICS); if(table==NULL) return; //--- Get the only table cell with the index_l row CTableCell *cell=table.GetCell(index_l,0); if(cell==NULL) return; //--- If the last item (ALL) is clicked if(index_l==ArrayMagics.Size()-1) { //--- get and display account trading statistics and specify that TABLE_STATS panel is active on the right if(!GetTradingStatsByAccount(DBHandle, DB_NAME, ArrayAccountStats)) return; if(ViewStatistic(TABLE_ACCOUNT,(string)AccountInfoInteger(ACCOUNT_LOGIN))) RPanelTable=TABLE_STATS; } //--- Click on the magic number value - display the magic number statistics and indicate that TABLE_STATS panel is active on the right else { if(ViewStatistic(TABLE_MAGICS,cell.Text())) RPanelTable=TABLE_STATS; } } //--- If there was a click on a symbol from the list in the right panel if(RPanelTable==TABLE_SYMBOLS && index_r>WRONG_VALUE) { //--- Get the table of symbol statistics from the right panel CTableData *table=panel_r.GetTable(TABLE_SYMBOLS); if(table==NULL) return; //--- Get the zero cell of the table with the index_r row index CTableCell *cell=table.GetCell(index_r,0); if(cell==NULL) return; //--- Display the summary statistics for the symbol and indicate that TABLE_STATS panel on the right is active if(ViewStatistic(TABLE_SYMBOLS,cell.Text())) RPanelTable=TABLE_STATS; } //--- If there was a click on a magic number from the list in the right panel if(RPanelTable==TABLE_MAGICS && index_r>WRONG_VALUE) { //--- Get the table of magic number statistics from the right panel CTableData *table=panel_r.GetTable(TABLE_MAGICS); if(table==NULL) return; //--- Get the zero cell of the table with the index_r row index CTableCell *cell=table.GetCell(index_r,0); if(cell==NULL) return; //--- Display the summary statistics for the magic number and indicate that TABLE_STATS panel on the right is active if(ViewStatistic(TABLE_MAGICS,cell.Text())) RPanelTable=TABLE_STATS; } } }
Now let's consider the rest of the functions called from the event handler. The code for each function is thoroughly commented and should not cause any misunderstanding.
The function that returns the index of a table row based on the cursor coordinates:
//+------------------------------------------------------------------+ //| Return the index of the table row by the cursor coordinates | //+------------------------------------------------------------------+ int TableSelectRowByMouse(const int x_cursor, const int y_cursor, const int cell_h, CDashboard *panel, CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return WRONG_VALUE; int index=WRONG_VALUE; // Index of the table row located under the cursor //--- In the loop by table rows int total=table.RowsTotal(); for(int i=0;i<total;i++) { //--- get the next zero cell of the table in the row with the loop index CTableCell *cell=table.GetCell(i,0); if(cell==NULL) continue; //--- Calculate the upper and lower coordinates of the table row location based on the cell Y coordinate int y1=panel.CoordY()+cell.Y()+1; int y2=y1+cell_h; //--- If the cursor is vertically inside the calculated coordinates of the table row if(y_cursor>y1 && y_cursor<y2) { //--- Write the row index, draw a rectangular area across the entire width of the table (selecting the row under the cursor) and return the row index index=cell.Row(); panel.DrawRectangleFill(2,cell.Y()+1,panel.Width()-4,y2-y1-1,C'220,220,220',240); return index; } } //--- Nothing found return WRONG_VALUE; }
The function performs two tasks at once: (1) returns the number of the table row, over which the mouse cursor is located, and (2) highlights this row with a background color.
Mouse wheel scroll handler function inside the left panel table:
//+------------------------------------------------------------------+ //| Mouse wheel scroll handler inside the left panel table | //+------------------------------------------------------------------+ int TableMouseWhellHandlerL(const int x_cursor,const int y_cursor,const bool shift_flag,const int delta,CDashboard *panel,const int table_id) { //--- Check the pointer to the left panel if(panel==NULL) return WRONG_VALUE; //--- Check the cursor location inside the panel if(x_cursor<panel.CoordX()+2 || x_cursor>panel.CoordX()+panel.Width() || y_cursor<panel.CoordY()+4 || y_cursor>panel.CoordY()+panel.Height()) return WRONG_VALUE; //--- Check if the table is present on the panel if(!panel.TableIsExist(table_id)) return WRONG_VALUE; //--- Get the pointer to the active table on the panel CTableData *table=panel.GetTable(table_id); if(table==NULL) return WRONG_VALUE; //--- Calculate the table offset by half the height of the table row int shift=CELL_H/2*(delta<0 ? -1 : 1); //--- Calculate the coordinates within which the table is shifted int y=table.Y1()+shift; if(y>2) y=2; if(y+table.Height()<panel.Height()-2) y=panel.Height()-2-table.Height(); if(table.Height()<panel.Height()) return WRONG_VALUE; //--- Clear the panel and display the active table on it int total=int(table_id==TABLE_SYMBOLS ? ArraySymbols.Size() : ArrayMagics.Size()); panel.Clear(); panel.DrawGrid(table_id,2,y,total,1,CELL_H,panel.Width()-5,C'220,220,220'); //--- Fill the table with values if(table_id==TABLE_SYMBOLS) FillsSymbolTable(panel,table); else FillsMagicTable(panel,table); //--- Get the row index of the table, over which the cursor is located int index=TableSelectRowByMouse(x_cursor,y_cursor,CELL_H,panel,table); return index; }
When scrolling the mouse wheel, if the cursor is over a table on the panel, the table should also be scrolled if its size exceeds the size of the panel. This is what this handler does - it shifts the table along the specified initial coordinates. In addition, the row under the cursor is highlighted using the TableSelectRowByMouse() function, and the index of the row under the cursor sent by that function is returned. The left panel displays small lists of symbols and magic numbers, so simplified scrolling is implemented here - we immediately shift the table to the calculated coordinates. As for the right panel, the case is a little more complicated.
The handler function for the mouse cursor offset inside the left panel table:
//+------------------------------------------------------------------+ //| Handler for the mouse cursor offset inside the left panel table | //+------------------------------------------------------------------+ int TableMouseMoveHandlerL(const int x_cursor,const int y_cursor,CDashboard *panel,const int table_id) { //--- Check the pointer to the left panel if(panel==NULL) return WRONG_VALUE; //--- Check if the table is present on the panel if(!panel.TableIsExist(table_id)) return WRONG_VALUE; //--- Get the pointer to the active table on the panel CTableData *table=panel.GetTable(table_id); if(table==NULL) return WRONG_VALUE; //--- Check the cursor location inside the panel //--- If the cursor is outside the panel, draw the active table and return -1 (to remove the selection of the row, over which the cursor was located) int total=int(table_id==TABLE_SYMBOLS ? ArraySymbols.Size() : ArrayMagics.Size()); if(x_cursor<panel.CoordX()+2 || x_cursor>panel.CoordX()+panel.Width() || y_cursor<panel.CoordY()+4 || y_cursor>panel.CoordY()+panel.Height()) { panel.Clear(); panel.DrawGrid(table_id,2,table.Y1(),total,1,CELL_H,panel.Width()-5,C'220,220,220'); return WRONG_VALUE; } //--- Clear the panel and display the active table on it panel.Clear(); panel.DrawGrid(table_id,2,table.Y1(),total,1,CELL_H,panel.Width()-5,C'220,220,220'); //--- Fill the table with values if(table_id==TABLE_SYMBOLS) FillsSymbolTable(panel,table); else FillsMagicTable(panel,table); //--- Get and return the row index of the table, over which the cursor is located int index=TableSelectRowByMouse(x_cursor,y_cursor,CELL_H,panel,table); return index; }
As in the previous function, the line under the cursor is searched for and selected here. But the table does not scroll.
The mouse wheel scroll handler function inside the right panel table:
//+------------------------------------------------------------------+ //| Mouse wheel scroll handler inside the right panel table | //+------------------------------------------------------------------+ int TableMouseWhellHandlerR(const int x_cursor,const int y_cursor,const bool shift_flag,const int delta,CDashboard *panel,const int table_id) { //--- Check the pointer to the right panel if(panel==NULL) return WRONG_VALUE; //--- Check the cursor location inside the panel if(x_cursor<panel.CoordX()+2 || x_cursor>panel.CoordX()+panel.Width() || y_cursor<panel.CoordY()+4 || y_cursor>panel.CoordY()+panel.Height()) return WRONG_VALUE; //--- Check if the table is present on the panel if(!panel.TableIsExist(table_id)) return WRONG_VALUE; //--- Get the pointer to the active table on the panel CTableData *table=panel.GetTable(table_id); if(table==NULL) return WRONG_VALUE; //--- Calculate the table vertical offset by half the height of the table row int shift_y=CELL_H/2*(delta<0 ? -1 : 1); //--- Calculate the table horizontal offset by the height of the table row int shift_x=(shift_flag ? CELL_H*(delta<0 ? -1 : 1) : 0); //--- Calculate the coordinates within which the table is shifted by Y int y=table.Y1()+shift_y; if(y>2) y=2; if(y+table.Height()<panel.Height()-2) y=panel.Height()-2-table.Height(); //--- Calculate the coordinates within which the table is shifted by X int x=0; if(shift_flag) { x=table.X1()+shift_x; if(x>2) x=2; if(x+table.Width()<panel.Width()-2) x=panel.Width()-2-table.Width(); } //--- If the entire table fits into the panel dimensions, there is no need to scroll anything, return -1 if(table.Height()<panel.Height() && table.Width()<panel.Width()) return WRONG_VALUE; //--- Define the table size int total=0; // number of rows int columns=0; // number of columns int cell_w=0; // table cell (column) width int cell_h=CELL_H; // table cell (row) height switch(table_id) { case TABLE_TRADES : total=(int)ArrayTrades.Size(); columns=11; cell_w=CELL_W_TRADES; break; case TABLE_SYMBOLS: total=(int)ArraySymbolStats.Size(); columns=(int)ArrayDataName.Size(); cell_w=CELL_W_SYMBOLS; break; case TABLE_MAGICS : total=(int)ArrayMagicStats.Size(); columns=(int)ArrayDataName.Size(); cell_w=CELL_W_MAGICS; break; case TABLE_STATS : total=TABLE_STAT_ROWS; columns=TABLE_STAT_COLS; cell_w=(panel.Width()-4)/TABLE_STAT_COLS; cell_h=(panel.Height()-4)/total; break; default : break; } //--- Clear the panel and display the active table on it panel.Clear(); panel.DrawGrid(table_id, (shift_flag ? x : table.X1()), (!shift_flag && table.Height()>panel.Height() ? y : table.Y1()), total,columns,cell_h,cell_w, (table_id!=TABLE_STATS ? C'220,220,220' : C'230,230,230'), (table_id!=TABLE_STATS)); //--- Fill the table with values switch(table_id) { case TABLE_TRADES : FillsTradeTable(panel,table); break; case TABLE_SYMBOLS: FillsTradingStatsBySymbolsTable(panel,table); break; case TABLE_MAGICS : FillsTradingStatsByMagicsTable(panel,table); break; default : break; } //--- Get the pointer to the header panel CDashboard *panel_h=dashboard.GetPanel("FieldH"); if(panel_h==NULL) return WRONG_VALUE; //--- Get the pointer to the header table CTableData *table_h=panel_h.GetTable(table_id); if(table_h==NULL) return WRONG_VALUE; //--- Clear the table header panel and display the table on it panel_h.Clear(); panel_h.DrawGrid(table_id,(shift_flag ? x : table_h.X1()),2,1,columns,cell_h,cell_w,C'200,200,200',false); //--- Fill the header table switch(table_id) { case TABLE_TRADES : FillsHeaderTradeTable(panel_h,table_h); break; case TABLE_SYMBOLS: case TABLE_MAGICS : FillsHeaderTradingStatsTable(panel_h,table_h); break; default : break; } //--- For the summary statistics table, there is no need to search for the row number under the cursor if(table.ID()==TABLE_STATS) return WRONG_VALUE; //--- Get the row index of the table, over which the cursor is located int index=TableSelectRowByMouse(x_cursor,y_cursor,cell_h,panel,table); return index; }
Here, three tables and the headers of these same tables are handled in one function. It all depends on the table type passed to the function. When scrolling large tables, they need to be scrolled not only in height, but also in width. The flag responsible for scrolling by width is shift_flag — this is a flag of holding down the Shift key while rotating the mouse wheel. When scrolling the table itself, the header located on another panel also scrolls along with it.
The handler function for the mouse cursor offset inside the right panel table:
//+------------------------------------------------------------------+ //| Handler for mouse cursor offset inside the right panel table | //+------------------------------------------------------------------+ int TableMouseMoveHandlerR(const int x_cursor,const int y_cursor,CDashboard *panel,const int table_id) { //--- Check the pointer to the left panel if(panel==NULL) return WRONG_VALUE; //--- Check if the table is present on the panel if(!panel.TableIsExist(table_id)) return WRONG_VALUE; //--- Get the pointer to the active table on the panel CTableData *table=panel.GetTable(table_id); if(table==NULL) return WRONG_VALUE; //--- Define the table size int total=0; // number of rows int columns=0; // number of columns int cell_w=0; // table cell (column) width int cell_h=CELL_H; // table cell (row) height switch(table_id) { case TABLE_TRADES : total=(int)ArrayTrades.Size(); columns=11; cell_w=CELL_W_TRADES; break; case TABLE_SYMBOLS: total=(int)ArraySymbolStats.Size(); columns=(int)ArrayDataName.Size(); cell_w=CELL_W_SYMBOLS; break; case TABLE_MAGICS : total=(int)ArrayMagicStats.Size(); columns=(int)ArrayDataName.Size(); cell_w=CELL_W_MAGICS; break; case TABLE_STATS : total=TABLE_STAT_ROWS; columns=TABLE_STAT_COLS; cell_w=(panel.Width()-4)/TABLE_STAT_COLS; cell_h=(panel.Height()-4)/total; break; default : break; } //--- Check the cursor location inside the panel //--- If the cursor is outside the panel, draw the active table and return -1 (to remove the selection of the row, over which the cursor was located) if(x_cursor<panel.CoordX()+2 || x_cursor>panel.CoordX()+panel.Width() || y_cursor<panel.CoordY()+4 || y_cursor>panel.CoordY()+panel.Height()) { panel.Clear(); panel.DrawGrid(table_id,table.X1(),table.Y1(),total,columns,cell_h,cell_w,(table_id!=TABLE_STATS ? C'220,220,220' : C'230,230,230'),(table_id!=TABLE_STATS)); return WRONG_VALUE; } //--- Clear the panel and display the active table on it panel.Clear(); panel.DrawGrid(table_id,table.X1(),table.Y1(),total,columns,cell_h,cell_w,(table_id!=TABLE_STATS ? C'220,220,220' : C'230,230,230'),(table_id!=TABLE_STATS)); //--- Fill the table with values switch(table_id) { case TABLE_TRADES : FillsTradeTable(panel,table); break; case TABLE_SYMBOLS: FillsTradingStatsBySymbolsTable(panel,table); break; case TABLE_MAGICS : FillsTradingStatsByMagicsTable(panel,table); break; default : break; } //--- For the summary statistics table, there is no need to search for the row number under the cursor if(table.ID()==TABLE_STATS) return WRONG_VALUE; //--- Get the row index of the table, over which the cursor is located int index=TableSelectRowByMouse(x_cursor,y_cursor,cell_h,panel,table); return index; }
In general, here (and in the functions discussed above) we only need to find the row number of the table, over which the cursor is located. Everything else is just servicing the "visual things" to highlight the row under the cursor, which ultimately leads to increased consumption of CPU resources. After all, we have to constantly redraw the entire visible part of the table, and the larger the dashboard and the table on it, the more space we have to draw. Of course, the physically drawn space is limited by the dashboard dimensions, but it is still not optimal. If we did such row selections in the dashboardel class, everything would be different - we would redraw only the table rows adjacent to the cursor, remembering the background color before and after highlighting, and then restoring it. But here we do everything directly in the program functions for the sake of simplicity.
The handler function for a mouse click inside the dashboard table:
//+------------------------------------------------------------------+ //| Handler for mouse click inside the dashboard table | //+------------------------------------------------------------------+ int TableMouseClickHandler(const int x_cursor,const int y_cursor,CDashboard *panel,const int table_id) { //--- Check the pointer to the left panel if(panel==NULL) return WRONG_VALUE; //--- Check the cursor location inside the panel if(x_cursor<panel.CoordX()+2 || x_cursor>panel.CoordX()+panel.Width() || y_cursor<panel.CoordY()+4 || y_cursor>panel.CoordY()+panel.Height()) return WRONG_VALUE; //--- Check if the table is present on the panel if(!panel.TableIsExist(table_id)) return WRONG_VALUE; //--- Get the pointer to the active table on the panel CTableData *table=panel.GetTable(table_id); if(table==NULL) return WRONG_VALUE; //--- For the summary statistics table, there is no need to search for the row number under the cursor if(table.ID()==TABLE_STATS) return WRONG_VALUE; //--- Get the index of the clicked table row int index=TableSelectRowByMouse(x_cursor,y_cursor,CELL_H,panel,table); return index; }
When clicking on a table row, we need to find and return the row index, in which the click occurred in order to handle it further. This has already been shown in the user event handler in OnChartEvent().
The function that fills the table with symbol names:
//+------------------------------------------------------------------+ //| Fill the table with symbol names | //+------------------------------------------------------------------+ void FillsSymbolTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Calculate the index of the row, from which we need to start filling the table CTableCell *cell=table.GetCell(0,0); if(cell==NULL) return; int y=panel.CoordY()+cell.Y()-2; int diff=panel.CoordY()-y; int index=diff/CELL_H; //--- Fill the table with values from the array starting with the 'index' row for(int i=index;i<(int)ArraySymbols.Size();i++) { CTableCell *cell=table.GetCell(i,0); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- display data from the array to the table cells cell.SetText(ArraySymbols[i]); panel.DrawText(cell.Text(),cell.X()+2,cell.Y()+1); } }
Here, in addition to not drawing areas of the table that extend beyond the panel, we also limit the start of the loop to the first table row that is visible at the top. When scrolling up the table, the very first row may go far beyond the panel at the top. In order not to "spin the loop" once again and not try to draw those table rows outside the panel that will not be drawn anyway, we need to calculate the index of the first table row visible from above, and start the loop from it. For fairly large tables, this approach produces noticeable results, eliminating the severe slowdowns when scrolling through a table with hundreds of rows.
The function filling the table with magic numbers:
//+------------------------------------------------------------------+ //| Fill the table with magic number values | //+------------------------------------------------------------------+ void FillsMagicTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Calculate the index of the row, from which we need to start filling the table CTableCell *cell=table.GetCell(0,0); if(cell==NULL) return; int y=panel.CoordY()+cell.Y()-2; int diff=panel.CoordY()-y; int index=diff/CELL_H; //--- Fill the table with values from the array starting with the 'index' row for(int i=index;i<(int)ArrayMagics.Size();i++) { CTableCell *cell=table.GetCell(i,0); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- display data from the array to the table cells string text=(i<(int)ArrayMagics.Size()-1 ? (string)ArrayMagics[i] : "ALL"); cell.SetText(text); panel.DrawText(cell.Text(),cell.X()+2,cell.Y()+1); } }
The function that fills the trade header table:
//+------------------------------------------------------------------+ //| Fill the trade header table | //+------------------------------------------------------------------+ void FillsHeaderTradeTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Fill the table with values int total=11; // 11 table columns CTableCell *cell=NULL; for(int i=0;i<total;i++) { //--- Get the i-th table cell from the zeroth (and only) table row cell=table.GetCell(0,i); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- Write the names of the headers depending on the loop index string cell_text=""; switch(i) { case 0 : cell_text="Time Entry In"; break; // login time case 1 : cell_text="Position ID"; break; // position ID case 2 : cell_text="Position Type"; break; // buy or sell case 3 : cell_text="Volume"; break; // volume case 4 : cell_text="Symbol"; break; // symbol case 5 : cell_text="Price Entry In"; break; // entry price case 6 : cell_text="Time Entry Out"; break; // exit time case 7 : cell_text="Price Entry Out"; break; // exit price case 8 : cell_text="Commission"; break; // entry and exit fees case 9 : cell_text="Swap"; break; // swap case 10 : cell_text="Profit"; break; // profit or loss default : break; } //--- display entries to cell tables cell.SetText(cell_text); panel.DrawText(cell.Text(),cell.X()+6,cell.Y()+2); } }
The function filling the trade table:
//+------------------------------------------------------------------+ //| Fill in the trade table | //+------------------------------------------------------------------+ void FillsTradeTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Fill in the table with values from the array CTableCell *cell=NULL; int total=(int)ArrayTrades.Size(); if(total==0) { PrintFormat("%s: Error: Trades array is empty",__FUNCTION__); return; } //--- Calculate the index of the row, from which we need to start filling the table cell=table.GetCell(0,0); if(cell==NULL) return; int y=panel.CoordY()+cell.Y()-2; int diff=panel.CoordY()-y; int index=diff/CELL_H; //--- In a loop by the number of rows (size of the trades array), starting from the 'index' row for(int i=index;i<total;i++) { //--- in a loop by the number of columns (11 for this table) for(int j=0;j<11;j++) { //--- get the next table cell cell=table.GetCell(i,j); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- Get table data from the trades array string cell_text=""; int digits=(int)SymbolInfoInteger(ArrayTrades[i].symbol,SYMBOL_DIGITS); switch(j) { case 0 : cell_text=TimeToString(ArrayTrades[i].time_in); break; // login time case 1 : cell_text=IntegerToString(ArrayTrades[i].ticket); break; // position ID case 2 : cell_text=(ArrayTrades[i].type==0 ? "Buy" : "Sell"); break; // buy or sell case 3 : cell_text=DoubleToString(ArrayTrades[i].volume,2); break; // volume case 4 : cell_text=ArrayTrades[i].symbol; break; // symbol case 5 : cell_text=DoubleToString(ArrayTrades[i].price_in,digits); break; // entry price case 6 : cell_text=TimeToString(ArrayTrades[i].time_out); break; // exit time case 7 : cell_text=DoubleToString(ArrayTrades[i].price_out,digits); break; // exit price case 8 : cell_text=DoubleToString(ArrayTrades[i].commission,2); break; // entry and exit fees case 9 : cell_text=DoubleToString(ArrayTrades[i].swap,2); break; // swap case 10 : cell_text=DoubleToString(ArrayTrades[i].profit,2); break; // profit or loss default : break; } //--- display entries to cell tables cell.SetText(cell_text); panel.DrawText(cell.Text(),cell.X()+6,cell.Y()+1); } } }
The two functions essentially draw one table. One function draws a table header with columns titled based on the column index, and the second function draws a table below the header with values corresponding to the column headers. In general, the functions work in pairs.
The function filling in the trading statistics header table:
//+------------------------------------------------------------------+ //| Fill the trading statistics header table | //+------------------------------------------------------------------+ void FillsHeaderTradingStatsTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Fill the table with values in a loop by the number of columns (by the size of the data in ArrayDataName) int total=(int)ArrayDataName.Size(); CTableCell *cell=NULL; for(int i=0;i<total;i++) { //--- get the next table cell cell=table.GetCell(0,i); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- Write the names of the headers depending on the loop index string cell_text=(i>0 ? ArrayDataName[i] : table.ID()==TABLE_SYMBOLS ? "Symbol" : "Magic"); //--- display entries to cell tables cell.SetText(cell_text); panel.DrawText(cell.Text(),cell.X()+6,cell.Y()+2); } }
When displaying a statistical table by symbols or magic numbers, it is necessary to draw a header for the statistics table. But we have two tables with statistical data: one for symbols, the second for magic numbers. The headers of these tables differ only in the first column, all the others are the same. Here the table ID is checked and, depending on it, the header of the first column is signed either with a symbol or a magic number.
The function filling in the symbol trading statistics table:
//+------------------------------------------------------------------+ //| Fill in the symbol trading statistics table | //+------------------------------------------------------------------+ void FillsTradingStatsBySymbolsTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Fill in the table with values from the array CTableCell *cell=NULL; int total=(int)ArraySymbolStats.Size(); if(total==0) { PrintFormat("%s: Error: The array of trading statistics by symbols is empty",__FUNCTION__); return; } //--- Calculate the index of the row, from which we need to start filling the table cell=table.GetCell(0,0); if(cell==NULL) return; int y=panel.CoordY()+cell.Y()-2; int diff=panel.CoordY()-y; int index=diff/CELL_H; //--- In a loop by the number of rows (size of the symbol statistics array), starting from the 'index' row for(int i=index;i<total;i++) { //--- in a loop by the number of statistics columns (array of the location of the statistics table columns from left to right) for(int j=0;j<(int)ArrayDataName.Size();j++) { //--- get the next table cell cell=table.GetCell(i,j); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- Get table data from the array of structures string cell_text=""; cell_text=GetDataStatsStr(TABLE_SYMBOLS, ArrayDataName[j],i); //--- display entries to cell tables cell.SetText(cell_text); panel.DrawText(cell.Text(),cell.X()+6,cell.Y()+1); } } }
The function filling in the magic number trading statistics table:
//+------------------------------------------------------------------+ //| Fill in the magic number trading statistics table | //+------------------------------------------------------------------+ void FillsTradingStatsByMagicsTable(CDashboard *panel,CTableData *table) { //--- Check the pointers to the panel and table if(panel==NULL || table==NULL) return; //--- Fill in the table with values from the array CTableCell *cell=NULL; int total=(int)ArrayMagicStats.Size(); if(total==0) { PrintFormat("%s: Error: The array of trading statistics by magics is empty",__FUNCTION__); return; } //--- Calculate the index of the row, from which we need to start filling the table cell=table.GetCell(0,0); if(cell==NULL) return; int y=panel.CoordY()+cell.Y()-2; int diff=panel.CoordY()-y; int index=diff/CELL_H; //--- In the loop by the number of rows (size of the magic number statistics array), starting from the 'index' row for(int i=index;i<total;i++) { //--- in a loop by the number of statistics columns (array of the location of the statistics table columns from left to right) for(int j=0;j<(int)ArrayDataName.Size();j++) { //--- get the next table cell cell=table.GetCell(i,j); if(cell==NULL) continue; //--- do not draw invisible areas of the table if(cell.X()>panel.CoordX()+panel.Width()) continue; if(cell.Y()>panel.CoordY()+panel.Height()) break; //--- Get table data from the array of structures string cell_text=GetDataStatsStr(TABLE_MAGICS, ArrayDataName[j],i); //--- display entries to cell tables cell.SetText(cell_text); panel.DrawText(cell.Text(),cell.X()+6,cell.Y()+1); } } }
Two similar functions that fill in the tables of statistics for symbols and magic numbers.
The function that returns data from the structure by header type:
//+------------------------------------------------------------------+ //| Return data from the structure by header type | //+------------------------------------------------------------------+ double GetDataStats(const int table_type, const string data_type, const int index) { //--- Depending on the data type in the table, return data from the fields of the data_type structure by 'index' array index switch(table_type) { case TABLE_SYMBOLS : return ( data_type==H_TRADES_S ? ArraySymbolStats[index].trades : data_type==H_GROSS_PROFIT_S ? ArraySymbolStats[index].gross_profit : data_type==H_GROSS_LOSS_S ? ArraySymbolStats[index].gross_loss : data_type==H_COMMISSIONS_S ? ArraySymbolStats[index].total_commission : data_type==H_SWAPS_S ? ArraySymbolStats[index].total_swap : data_type==H_PROFITS_S ? ArraySymbolStats[index].total_profit : data_type==H_NET_PROFIT_S ? ArraySymbolStats[index].net_profit : data_type==H_WINS_S ? ArraySymbolStats[index].win_trades : data_type==H_LOST_S ? ArraySymbolStats[index].loss_trades : data_type==H_LONG_S ? ArraySymbolStats[index].long_trades : data_type==H_SHORT_S ? ArraySymbolStats[index].short_trades : data_type==H_EXP_PAYOFF_S ? ArraySymbolStats[index].expected_payoff : data_type==H_WIN_PRC_S ? ArraySymbolStats[index].win_percent : data_type==H_LOSS_PRC_S ? ArraySymbolStats[index].loss_percent : data_type==H_AVG_PROFIT_S ? ArraySymbolStats[index].average_profit : data_type==H_AVG_LOSS_S ? ArraySymbolStats[index].average_loss : data_type==H_PRF_FACTOR_S ? ArraySymbolStats[index].profit_factor : 0 ); case TABLE_MAGICS : return ( data_type==H_TRADES_S ? ArrayMagicStats[index].trades : data_type==H_GROSS_PROFIT_S ? ArrayMagicStats[index].gross_profit : data_type==H_GROSS_LOSS_S ? ArrayMagicStats[index].gross_loss : data_type==H_COMMISSIONS_S ? ArrayMagicStats[index].total_commission : data_type==H_SWAPS_S ? ArrayMagicStats[index].total_swap : data_type==H_PROFITS_S ? ArrayMagicStats[index].total_profit : data_type==H_NET_PROFIT_S ? ArrayMagicStats[index].net_profit : data_type==H_WINS_S ? ArrayMagicStats[index].win_trades : data_type==H_LOST_S ? ArrayMagicStats[index].loss_trades : data_type==H_LONG_S ? ArrayMagicStats[index].long_trades : data_type==H_SHORT_S ? ArrayMagicStats[index].short_trades : data_type==H_EXP_PAYOFF_S ? ArrayMagicStats[index].expected_payoff : data_type==H_WIN_PRC_S ? ArrayMagicStats[index].win_percent : data_type==H_LOSS_PRC_S ? ArrayMagicStats[index].loss_percent : data_type==H_AVG_PROFIT_S ? ArrayMagicStats[index].average_profit : data_type==H_AVG_LOSS_S ? ArrayMagicStats[index].average_loss : data_type==H_PRF_FACTOR_S ? ArrayMagicStats[index].profit_factor : 0 ); case TABLE_ACCOUNT : return ( data_type==H_TRADES_S ? ArrayAccountStats[index].trades : data_type==H_GROSS_PROFIT_S ? ArrayAccountStats[index].gross_profit : data_type==H_GROSS_LOSS_S ? ArrayAccountStats[index].gross_loss : data_type==H_COMMISSIONS_S ? ArrayAccountStats[index].total_commission : data_type==H_SWAPS_S ? ArrayAccountStats[index].total_swap : data_type==H_PROFITS_S ? ArrayAccountStats[index].total_profit : data_type==H_NET_PROFIT_S ? ArrayAccountStats[index].net_profit : data_type==H_WINS_S ? ArrayAccountStats[index].win_trades : data_type==H_LOST_S ? ArrayAccountStats[index].loss_trades : data_type==H_LONG_S ? ArrayAccountStats[index].long_trades : data_type==H_SHORT_S ? ArrayAccountStats[index].short_trades : data_type==H_EXP_PAYOFF_S ? ArrayAccountStats[index].expected_payoff : data_type==H_WIN_PRC_S ? ArrayAccountStats[index].win_percent : data_type==H_LOSS_PRC_S ? ArrayAccountStats[index].loss_percent : data_type==H_AVG_PROFIT_S ? ArrayAccountStats[index].average_profit : data_type==H_AVG_LOSS_S ? ArrayAccountStats[index].average_loss : data_type==H_PRF_FACTOR_S ? ArrayAccountStats[index].profit_factor : 0 ); default : return 0; } }
The function receives the statistics table type (symbol/magic number/account), data type (column header value) and data index in the structure array. Depending on the data passed to the function, a value from the corresponding array of structures is returned. For simplicity, the value is always returned with the double type, even if it is an integer in the structure. In the following function, this value is returned as a string with the desired number of decimal places.
The function returning data from the structure by header type as a string:
//+------------------------------------------------------------------+ //| Return data from the structure by header type as a string | //+------------------------------------------------------------------+ string GetDataStatsStr(const int table_type, const string data_type, const int index) { //--- Depending on the data type, we determine the number of decimal places //--- (2 - for a real property and 0 - for an integer) int digits=(data_type==H_TRADES_S || data_type==H_WINS_S || data_type==H_LOST_S || data_type==H_LONG_S || data_type==H_SHORT_S ? 0 : 2); //--- If data type is "Header" if(data_type=="HEADER") { //--- return the name depending on the table type (symbol, magic number, account) switch(table_type) { case TABLE_SYMBOLS : return ArraySymbolStats[index].name; case TABLE_MAGICS : return (string)ArrayMagicStats[index].magic; case TABLE_ACCOUNT : return (string)ArrayAccountStats[index].account; default : return "Unknown:"+(string)table_type; } } //--- For all other data types, return their string value with the previously defined number of decimal places return(DoubleToString(GetDataStats(table_type, data_type, index),digits)); }
Functions are used to send the values of the cells of the statistics tables to the dashboard tables.
The function returning the symbol index in the array of symbol statistics:
//+------------------------------------------------------------------+ //| Return the index of the symbol in the symbol statistics array | //+------------------------------------------------------------------+ int GetIndexSymbol(const string symbol) { int total=(int)ArraySymbolStats.Size(); for(int i=0;i<total;i++) { if(ArraySymbolStats[i].name==symbol) return i; } return WRONG_VALUE; }
The function returning the symbol index in the statistics array by magic numbers:
//+------------------------------------------------------------------------+ //| Return the magic number index in the statistics array by magic numbers | //+------------------------------------------------------------------------+ int GetIndexMagic(const long magic) { int total=(int)ArrayMagicStats.Size(); for(int i=0;i<total;i++) { if(ArrayMagicStats[i].magic==magic) return i; } return WRONG_VALUE; }
Both functions return the index of the searched symbol or magic number in the corresponding array of symbols or magic number statistics.
The function that displays the final statistics for the selected symbol, magic number or account:
//+--------------------------------------------------------------------+ //| Display statistics for the selected symbol, magic number or account| //+--------------------------------------------------------------------+ bool ViewStatistic(const int table_type,const string cell_text) { //--- Get the pointers to the header panel and the right panel for displaying statistics CDashboard *panel_h=dashboard.GetPanel("FieldH"); CDashboard *panel_r=dashboard.GetPanel("FieldR"); if(panel_h==NULL || panel_r==NULL) return false; //--- Determine the source of statistical data (symbol/magic number/account) string source=(table_type==TABLE_SYMBOLS ? "symbol" : table_type==TABLE_MAGICS ? "magic" : "account"); int index=WRONG_VALUE; //--- Depending on the text in the selected table cell (cell_text) passed to the function, //--- get the index that contains the data in the corresponding statistics array switch(table_type) { case TABLE_SYMBOLS: index=GetIndexSymbol(cell_text); break; case TABLE_MAGICS : index=GetIndexMagic(StringToInteger(cell_text)); break; case TABLE_ACCOUNT: index=(ArrayAccountStats.Size()==1 ? 0 : -1); break; default : break; } //--- If the index could not be obtained, we assume that the corresponding statistics array is empty if(index==WRONG_VALUE) { PrintFormat("%s: Error. Empty array of %s statistics",__FUNCTION__,source); return false; } //--- Get and save the font properties set for the header bar int f_size,f_flags,f_angle; string f_name=panel_h.FontParams(f_size,f_flags,f_angle); //--- Clear the header bar and display the description of the selected data in Tahoma font size 8 panel_h.Clear(); panel_h.SetFontParams("Tahoma",8,f_flags,f_angle); panel_h.DrawText(StringFormat("Trade statistics by %s %s",source,cell_text),8,3,C'150,150,150'); //--- Return the header bar font to its previous saved properties panel_h.SetFontParams(f_name,f_size,f_flags,f_angle); //--- Check for availability and get or create a table object for displaying statistics on the right panel if(!panel_r.TableIsExist(TABLE_STATS) && !panel_r.CreateNewTable(TABLE_STATS)) return false; //--- Get the pointer to the created table CTableData *table_r=panel_r.GetTable(TABLE_STATS); if(table_r==NULL) return false; //--- Clear the right panel and draw a table on it panel_r.Clear(); panel_r.DrawGrid(TABLE_STATS,2,2,TABLE_STAT_ROWS,TABLE_STAT_COLS,(panel_r.Height()-4)/TABLE_STAT_ROWS,(panel_r.Width()-4)/TABLE_STAT_COLS,C'230,230,230',false); //--- Declare a structure for storing statistics data //--- (symbol/magic number/account) by previously obtained data index. //--- All fields of the SSymbolStats, SMagicStats and SAccountStats structures are the same, //--- except for the first field with the symbol name, magic value or account number. //--- Since the first field is not needed here, any of the three structure types is sufficient. //--- Fill the declared structure with data depending on the selected source SSymbolStats stats={}; switch(table_type) { case TABLE_SYMBOLS: stats.trades = ArraySymbolStats[index].trades; stats.gross_profit = ArraySymbolStats[index].gross_profit; stats.gross_loss = ArraySymbolStats[index].gross_loss; stats.total_commission= ArraySymbolStats[index].total_commission; stats.total_swap = ArraySymbolStats[index].total_swap; stats.total_profit = ArraySymbolStats[index].total_profit; stats.net_profit = ArraySymbolStats[index].net_profit; stats.win_trades = ArraySymbolStats[index].win_trades; stats.loss_trades = ArraySymbolStats[index].loss_trades; stats.long_trades = ArraySymbolStats[index].long_trades; stats.short_trades = ArraySymbolStats[index].short_trades; stats.expected_payoff = ArraySymbolStats[index].expected_payoff; stats.win_percent = ArraySymbolStats[index].win_percent; stats.loss_percent = ArraySymbolStats[index].loss_percent; stats.average_profit = ArraySymbolStats[index].average_profit; stats.average_loss = ArraySymbolStats[index].average_loss; stats.profit_factor = ArraySymbolStats[index].profit_factor; break; case TABLE_MAGICS : stats.trades = ArrayMagicStats[index].trades; stats.gross_profit = ArrayMagicStats[index].gross_profit; stats.gross_loss = ArrayMagicStats[index].gross_loss; stats.total_commission= ArrayMagicStats[index].total_commission; stats.total_swap = ArrayMagicStats[index].total_swap; stats.total_profit = ArrayMagicStats[index].total_profit; stats.net_profit = ArrayMagicStats[index].net_profit; stats.win_trades = ArrayMagicStats[index].win_trades; stats.loss_trades = ArrayMagicStats[index].loss_trades; stats.long_trades = ArrayMagicStats[index].long_trades; stats.short_trades = ArrayMagicStats[index].short_trades; stats.expected_payoff = ArrayMagicStats[index].expected_payoff; stats.win_percent = ArrayMagicStats[index].win_percent; stats.loss_percent = ArrayMagicStats[index].loss_percent; stats.average_profit = ArrayMagicStats[index].average_profit; stats.average_loss = ArrayMagicStats[index].average_loss; stats.profit_factor = ArrayMagicStats[index].profit_factor; break; case TABLE_ACCOUNT: stats.trades = ArrayAccountStats[index].trades; stats.gross_profit = ArrayAccountStats[index].gross_profit; stats.gross_loss = ArrayAccountStats[index].gross_loss; stats.total_commission= ArrayAccountStats[index].total_commission; stats.total_swap = ArrayAccountStats[index].total_swap; stats.total_profit = ArrayAccountStats[index].total_profit; stats.net_profit = ArrayAccountStats[index].net_profit; stats.win_trades = ArrayAccountStats[index].win_trades; stats.loss_trades = ArrayAccountStats[index].loss_trades; stats.long_trades = ArrayAccountStats[index].long_trades; stats.short_trades = ArrayAccountStats[index].short_trades; stats.expected_payoff = ArrayAccountStats[index].expected_payoff; stats.win_percent = ArrayAccountStats[index].win_percent; stats.loss_percent = ArrayAccountStats[index].loss_percent; stats.average_profit = ArrayAccountStats[index].average_profit; stats.average_loss = ArrayAccountStats[index].average_loss; stats.profit_factor = ArrayAccountStats[index].profit_factor; break; default: break; } //--- Get and save the font properties set for the right panel f_name=panel_r.FontParams(f_size,f_flags,f_angle); //--- Set a new font Tahoma size 8 for the right panel panel_r.SetFontParams("Tahoma",8,FW_BLACK,f_angle); //--- Variables for calculating the location of text in a table cell CTableCell *cellH=NULL, *cellV=NULL; int cols=table_r.ColumnsInRow(0); // number of columns in the statistics table int cw=table_r.Width()/cols; // width of one table column int y_shift=6; // shift text height int x_shift=21; // shift text width int tw=0; // text width string text=""; double value=0; //--- Left column (data -- value) //--- Get cells 0.0 and 0.1 of the table and send Trades and its value to their coordinates cellH=table_r.GetCell(0,0); cellV=table_r.GetCell(0,1); if(cellH==NULL || cellV==NULL) return false; text=(string)stats.trades; tw=panel_r.TextWidth(text); panel_r.DrawText(H_TRADES+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 1.0 and 1.1 of the table and send Long and its value to their coordinates cellH=table_r.GetCell(1,0); cellV=table_r.GetCell(1,1); if(cellH==NULL || cellV==NULL) return false; text=(string)stats.long_trades; tw=panel_r.TextWidth(text); panel_r.DrawText(H_LONG+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 2.0 and 2.1 of the table and send Short and its value to their coordinates cellH=table_r.GetCell(2,0); cellV=table_r.GetCell(2,1); if(cellH==NULL || cellV==NULL) return false; text=(string)stats.short_trades; tw=panel_r.TextWidth(text); panel_r.DrawText(H_SHORT+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 3.0 and 3.1 of the table and send Net Profit and its value to their coordinates cellH=table_r.GetCell(3,0); cellV=table_r.GetCell(3,1); if(cellH==NULL || cellV==NULL) return false; value=stats.net_profit; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_NET_PROFIT+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value>0 ? C'86,119,204' : value<0 ? C'234,50,50' : C'150,150,150')); //--- Get cells 4.0 and 4.1 of the table and send Profit Loss and its value to their coordinates cellH=table_r.GetCell(4,0); cellV=table_r.GetCell(4,1); if(cellH==NULL || cellV==NULL) return false; value=stats.total_profit; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_PROFITS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value>0 ? C'86,119,204' : value<0 ? C'234,50,50' : C'150,150,150')); //--- Get cells 5.0 and 5.1 of the table and send Gross Profit and its value to their coordinates cellH=table_r.GetCell(5,0); cellV=table_r.GetCell(5,1); if(cellH==NULL || cellV==NULL) return false; value=stats.gross_profit; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_GROSS_PROFIT+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value>0 ? C'86,119,204' : C'150,150,150')); //--- Get cells 6.0 and 6.1 of the table and send Gross Loss and its value to their coordinates cellH=table_r.GetCell(6,0); cellV=table_r.GetCell(6,1); if(cellH==NULL || cellV==NULL) return false; value=stats.gross_loss; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_GROSS_LOSS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value<0 ? C'234,50,50' : C'150,150,150')); //--- Get cells 7.0 and 7.1 of the table and send Commission and its value to their coordinates cellH=table_r.GetCell(7,0); cellV=table_r.GetCell(7,1); if(cellH==NULL || cellV==NULL) return false; value=stats.total_commission; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_COMMISSIONS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value<0 ? C'234,50,50' : C'150,150,150')); //--- Get cells 8.0 and 8.1 of the table and send Swap and its value to their coordinates cellH=table_r.GetCell(8,0); cellV=table_r.GetCell(8,1); if(cellH==NULL || cellV==NULL) return false; value=stats.total_swap; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_SWAPS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value<0 ? C'234,50,50' : C'150,150,150')); //--- Right column (data -- value) //--- Get cells 0.2 and 0.3 of the table and send Win trades and its value to their coordinates cellH=table_r.GetCell(0,2); cellV=table_r.GetCell(0,3); if(cellH==NULL || cellV==NULL) return false; text=(string)stats.win_trades; tw=panel_r.TextWidth(text); panel_r.DrawText(H_WINS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 1.2 and 1.3 of the table and send Loss trades and its value to their coordinates cellH=table_r.GetCell(1,2); cellV=table_r.GetCell(1,3); if(cellH==NULL || cellV==NULL) return false; text=(string)stats.loss_trades; tw=panel_r.TextWidth(text); panel_r.DrawText(H_LOST+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 2.2 and 2.3 of the table and send Expected Payoff and its value to their coordinates cellH=table_r.GetCell(2,2); cellV=table_r.GetCell(2,3); if(cellH==NULL || cellV==NULL) return false; value=stats.expected_payoff; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_EXP_PAYOFF+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Get cells 3.2 and 3.3 of the table and send Win percent and its value to their coordinates cellH=table_r.GetCell(3,2); cellV=table_r.GetCell(3,3); if(cellH==NULL || cellV==NULL) return false; value=stats.win_percent; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_WIN_PRC+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'86,119,204'); //--- Get cells 4.2 and 4.3 of the table and send Loss percent and its value to their coordinates cellH=table_r.GetCell(4,2); cellV=table_r.GetCell(4,3); if(cellH==NULL || cellV==NULL) return false; value=stats.loss_percent; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_LOSS_PRC+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'234,50,50'); //--- Get cells 5.2 and 5.3 of the table and send Average Profit and its value to their coordinates cellH=table_r.GetCell(5,2); cellV=table_r.GetCell(5,3); if(cellH==NULL || cellV==NULL) return false; value=stats.average_profit; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_AVG_PROFIT+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value>0 ? C'86,119,204' : C'150,150,150')); //--- Get cells 6.2 and 6.3 of the table and send Average Loss and its value to their coordinates cellH=table_r.GetCell(6,2); cellV=table_r.GetCell(6,3); if(cellH==NULL || cellV==NULL) return false; value=stats.average_loss; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_AVG_LOSS+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,(value<0 ? C'234,50,50' : C'150,150,150')); //--- Get cells 7.2 and 7.3 of the table and send Profit factor and its value to their coordinates cellH=table_r.GetCell(7,2); cellV=table_r.GetCell(7,3); if(cellH==NULL || cellV==NULL) return false; value=stats.profit_factor; text=DoubleToString(value,2); tw=panel_r.TextWidth(text); panel_r.DrawText(H_PRF_FACTOR+":",cellH.X()+x_shift,cellH.Y()+y_shift,C'150,150,150'); panel_r.DrawText(text,cellV.X()+cw-tw-x_shift,cellV.Y()+y_shift,C'150,150,150'); //--- Return the right panel font to its previous saved properties panel_r.SetFontParams(f_name,f_size,f_flags,f_angle); return true; }
The function is designed to draw the final statistics table for the selected symbol, magic number, or the entire account. The function receives the type of the statistics table and the name of the symbol, or the string value of the magic number, or the account number. The text determines the index of the symbol, magic number or account in the corresponding array of statistical data structures. From the required structure, we use the obtained index to get all statistical data into the structure, and then we arrange them in the rendered table according to the coordinates of its cells. In this case, the horizontal offsets of the displayed text are calculated in such a way that the data header is tied to the left edge of the table cell, and the text of the data value is tied to the right edge of its table cell. All data is displayed in four columns so that they are visually grouped on the panel in two columns in the form of "title -- value".
Let's compile the indicator and see what we got:
We can see that all the declared functionality works as expected. We can see slight "blinking" of text in tables when moving the cursor and scrolling tables. But this is the result of a suboptimal redrawing scheme - the entire visible part of the table is constantly redrawing. This can be avoided by more complex logic for handling table rows under the cursor, but this is not our objective here.
Tables can be scrolled vertically by rotating the mouse wheel, and horizontally by rotating the wheel while holding the Shift key. If we look closely at the video, we will notice that when displaying statistics for a magic number with a value of 0, the number of long and short positions is shown as zero. This is caused by an error in defining a trade in a query from the DB. The table of trades is created from the table of deals. If a position was opened by an EA (in this case with the magic number of 600) and closed manually, then the opening deal will have a magic number specified, while the closing one will have a zero magic number. This can be seen in the deal history:
Here, we use close deals to determine the trade and its magic number is zero. It is impossible to find an opening deal using the zero magic number. Accordingly, neither long nor short positions are found for the zero magic number. Thus, when creating a trade table, it is worth considering the possibility that a position can be opened by an EA and closed manually and vice versa. If we take this into account, then such errors should no longer occur.
Conclusion
In this article, we set ourselves the task of creating a dashboard displaying trading statistics, and we had to solve it using examples from articles and documentation on this resource. As we can see, even without knowing something, it is always easy to find answers to your questions, using the huge knowledge base offered by the resource, and make a fully functional product.
Study the information offered by the website, read articles and documentation, communicate with more experienced colleagues, refine the examples found to suit your tasks, and everything will definitely work out!
All files of the considered classes, functions and indicator are attached to the article. Also attached is an archive that can be unpacked into the terminal data directory. All the necessary files are placed in the \MQL5\Indicators\StatisticsBy folder, so that they can be immediately compiled and the indicator file can be launched.
Translated from Russian by MetaQuotes Ltd.
Original article: https://www.mql5.com/ru/articles/16233
Attached files |
Dashboard.mqh
(256.99 KB)
SQLiteFunc.mqh
(64.62 KB)
StatisticsBy.mq5
(164.58 KB)
MQL5.zip
(45.47 KB)
Warning: All rights to these materials are reserved by MetaQuotes Ltd. Copying or reprinting of these materials in whole or in part is prohibited.
This article was written by a user of the site and reflects their personal views. MetaQuotes Ltd is not responsible for the accuracy of the information presented, nor for any consequences resulting from the use of the solutions, strategies or recommendations described.
Neural Networks in Trading: Node-Adaptive Graph Representation with NAFS
We invite you to get acquainted with the NAFS (Node-Adaptive Feature Smoothing) method, which is a non-parametric approach to creating node representations that does not require parameter training. NAFS extracts features of each node given its neighbors and then adaptively combines these features to form a final representation.
MQL5 Wizard Techniques you should know (Part 69): Using Patterns of SAR and the RVI
The Parabolic-SAR (SAR) and the Relative Vigour Index (RVI) are another pair of indicators that could be used in conjunction within an MQL5 Expert Advisor. This indicator pair, like those we’ve covered in the past, is also complementary since SAR defines the trend while RVI checks momentum. As usual, we use the MQL5 wizard to build and test any potential this indicator pairing may have.
Creating a Trading Administrator Panel in MQL5 (Part XII): Integration of a Forex Values Calculator
Accurate calculation of key trading values is an indispensable part of every trader’s workflow. In this article, we will discuss, the integration of a powerful utility—the Forex Calculator—into the Trade Management Panel, further extending the functionality of our multi-panel Trading Administrator system. Efficiently determining risk, position size, and potential profit is essential when placing trades, and this new feature is designed to make that process faster and more intuitive within the panel. Join us as we explore the practical application of MQL5 in building advanced, trading panels.
Price Action Analysis Toolkit Development (Part 27): Liquidity Sweep With MA Filter Tool
Understanding the subtle dynamics behind price movements can give you a critical edge. One such phenomenon is the liquidity sweep, a deliberate strategy that large traders, especially institutions, use to push prices through key support or resistance levels. These levels often coincide with clusters of retail stop-loss orders, creating pockets of liquidity that big players can exploit to enter or exit sizeable positions with minimal slippage.
You are missing trading opportunities:
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
Registration
Log in
You agree to website policy and terms of use
If you do not have an account, please register
There is a lack of tools that would allow you to work with a large trading history.
Unfortunately, this toolkit simply hangs up when requesting history, like many others.
It takes five minutes to get the history. Then it is impossible to do anything with the window - full CPU load.
There is a lack of tools to deal with a large trading history.
Unfortunately, this toolkit just hangs up when requesting history, like many others.
Five minutes to get the history. Then it is impossible to do anything with the window - full CPU load.
Can I have investor access to the account?
Unfortunately, there is no such possibility. But you can create something like this yourself: on a demo account, use a script to open/close the required number of positions by different symbols/magics in an hour using asynchronous OrderSend.
Doesn't want to work on the Moscow stock exchange
Doesn't want to work on the Moscow stock exchange
Naturally. Everything related to position, except for total position, is useless at netting if more than one robot works on one symbol (or robot plus manual trading).