MQL5 中的 SQLite 功能示例：按交易品种及 Magic 编码展示交易统计信息的仪表盘
MetaTrader 5
—
示例
|
49
5
内容
引言
MQL5.com 是一个资源平台，为用户提供算法交易领域丰富的参考和教育资料。尽管资料库庞大、功能全面，部分用户仍难以找到具体解决方案或把示例同自身需求匹配起来。为此，平台通过社区向用户提供互动场景，用户可在此获得有用建议，甚至得到现成方案。
本文旨在演示并解决一个典型任务——创建一个信息面板，用于展示账户的交易历史与统计数据。我们将完全依赖mql5.com现有材料开发该面板；这不仅是一份实际方案，也是培训与参考资料在真实场景中应用的范例。
分解目标任务
需创建一块信息面板，按需求显示：账户交易历史以及按交易品种及 Magic 编码（EA在该账户上的交易）细分的交易统计。它还将展示账户完整的交易统计信息。交易记录根据交易执行时间排序。交易对象和magic编码的统计信息按净利润排序。
程序类型是指标。我们不会向缓冲区输出任何数据。即这是一款无缓冲区的指标，其主工作区为图形面板。面板将分为左右两个可视区域：左侧列出账户所有交易过的品种及其对应的 Magic编码；右侧则以列表形式展示按品种或按Magic编码统计的交易表，或显示所选品种、Magic编码乃至整个账户的最终交易统计。我们将通过面板上的按钮，或点击右侧统计表中某一行品种/Magic编码信息，来控制对应列表的显示与切换。
在文章《在指标和EA中创建一个仪表盘来显示数据》中，我们将使用一个信息面板。
文章《SQLIite：在 MQL5 中原生操作 SQL 数据库》将帮助我们按交易品种和 Magic 编码（交易策略）获取账户统计信息。
信息面板
自第一篇关于创建仪表盘的文章发布以来，其代码已经历了一些修改和改进。本文并不打算详细描述代码所做出的所有变更。因此，我们仅对这些改动进行快速回顾。您可以通过下载该文章中提到的第一版面板，并将其与本文附件中的代码进行对比，来确切地看到哪些内容被修改了，以及它们是如何被修改的。
在切换图表时面板定位与显示的某些bug已经被我修复。现在我们可以将子仪表盘附加到主主仪表盘上。特别是，我们可以将它们折叠成按钮，仅保留其标题作为按钮使用。移除了表格在面板内定位的限制。最初，表格只能在可见区域内绘制，无法超出面板范围。在某些情况下，面板上的表格需要被放置在面板的可见区域之外。这对于滚动那些长度或宽度超出仪表盘尺寸的长列表或宽表格是必需的。因此，如果我们允许表格的初始坐标位于仪表盘之外，就可以实现表格的滚动。这正是我们今天要做的事情。但我们不会修改仪表盘及其表格的类（尽管从后续使用修改后面板类的角度来看，这样做更为正确），以便清晰地展示：即使使用功能上并不完全匹配的示例，只要方法得当，经过调整也终将得到预期结果。
让我们快速了解一下表格和仪表盘类代码中已修改的内容。
表格单元类现已新增用于存储单元格文本的变量并 提供了操作该变量的方法：
//+------------------------------------------------------------------+ //| 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){} };
在表格数据类中，ID 现在允许为负值，就像表格坐标可以为负一样。新增了设置表格 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); }
添加默认构造函数：
//--- 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(); }
默认构造函数允许你在不使用new运算符的情况下声明类对象，而int坐标让我们能够把表的初始坐标设置在仪表盘窗口之外。
在面板对象类中新声明了变量：
//+------------------------------------------------------------------+ //| 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
既然我们现在可以将子面板绑定到父面板，一些处理面板的方法已经从受保护部分移动到了公共部分。因为这些方法需要从外部被访问。同时，添加了新的方法：
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
所有新增的方法与改进使我们能将子面板附加到父仪表盘上，并将它们作为独立对象使用，同时仍依赖于其父对象。当表格尺寸大于仪表盘自身尺寸时，就可以创建可滚动的表格了。此前，表格只能小于仪表盘尺寸。仪表盘类本身并不包含表格滚动功能——我们将在主程序中直接实现这一功能。如有需要，后续再将此功能集成到面板类及其表格中。但目前暂无此需求。
自然，这里我们只讨论了仪表盘类及其表格所做更改的一小部分——仅涉及已声明的方法。自首次发布以来，代码的大部分区域都已逐步改进。您随时可以从这篇文章中下载我们仪表盘的第一个版本，并与本文呈现的版本进行对比。仪表盘的程序文件应位于项目目录：\MQL5\Indicators\StatisticsBy\Dashboard\Dashboard.mqh。
数据库函数
我在数据库方面的经验并不丰富。过去曾参与一个 C# 游戏行业的联合项目，当时由另一位同事负责数据库，而我仅使用他提供的连接器将数据库接入项目。因此，我必须借助 mql5.com 上的参考资料和文章。在学习文章《SQLIite：在 MQL5 中原生操作 SQL 数据库》时，我立即注意到文中的DatabasePrepare()函数。该函数包含一个创建成交（deals）表示例，而成交表又被用于创建交易（trades）表。这正是我们所需要的！让我们带着耐心，一起研究这个示例及其函数。
首先，我们看到两个用于存储成交与交易数据的结构：
//--- 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 };
接着，我们分析代码逻辑：
//+------------------------------------------------------------------+ //| 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); }
- 创建一个数据库，
- 在数据库中创建一个成交表，
- 拉取成交历史并将成交记录写入已创建的表中，
- 检查账户类型。必须为 Hedge（锁仓）模式；对于 Netting（净额）模式，仅凭成交记录无法还原完整的交易历史。
- 基于成交表创建交易表，并根据成交表的数据将交易信息写入交易表。
示例脚本还会打印所建表中前 10 条成交和前 10 条交易记录。我们不需要这一步。
根据示例脚本的逻辑，我们需要基于其中展示的函数及代码片段，创建若干自定义函数：
//+------------------------------------------------------------------+ //| 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); } //+------------------------------------------------------------------+
除了示例中已给出的结构和表字段外，我们还需要额外增加一个“账户索引”字段，用于生成该账户的完整交易统计表。也就是说，我们要的是全量交易统计。
如何制作统计表？请阅读本文！这恰恰正是我们需要的：
按策略进行组合分析
上例DatabasePrepare脚本的运行结果清楚地表明，交易涉及多个货币对。此外，[magic] 列的取值从 100 到 600。意味着该账户由多条策略共同管理，每条策略使用各自的 Magic 编码来标识其成交。
借助一条 SQL 查询，我们就能按magic编码对交易进行分析：
//--- 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");
结果：
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
6个策略中有4个是盈利的。我们获得了每条策略的统计数据：
- 交易数（trades） — 策略执行的交易次数，
- 毛利润（gross_profit） — 按策略计算的总利润（所有正profit值的总和），
- 总亏损（gross_loss） — 按策略计算的总亏损（所有负profit值的总和），
- 总佣金（total_commission） — 按策略交易计算的所有佣金的总和，
- 总掉期（total_swap） — 按策略交易计算的所有掉期的总和，
- 总利润（total_profit） — gross_profit 和gross_loss 的总和，
- 净利润（net_profit） — gross_profit + gross_loss + total_commission + total_swap的累计值，
- 盈利交易（win_trades） —profit>0的交易次数，
- 亏损交易（loss_trades） — profit<0的交易次数，
- 预期收益（expected_payoff） — 排除掉期和佣金后的每笔交易预期收益 = net_profit/trades，
- 盈利百分比（win_percent） — 盈利交易所占的百分比，
- 亏损百分比（loss_percent）— 亏损交易所占的百分比，
- 平均利润（average_profit） — 平均盈利 = gross_profit/win_trades，
- 平均亏损（average_loss） — 平均亏损 = gross_loss /loss_trades，
- 利润因子（profit_factor） — 利润因子 = gross_profit/gross_loss。
计算利润和亏损的统计数据不考虑持仓产生的掉期和佣金。这可以让你看到实际成本。可能会出现策略盈利很少，但由于掉期和佣金而总体上是不盈利的情况。
按交易品种分析交易
我们可以按交易品种分析交易。为此，请执行以下查询：
//--- 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");
结果：
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
统计数据表明，在10个交易品种中，有5个品种实现了净利润（net_profit>0），而在10个品种中有6个品种的利润因子为正（profit_factor>1）。这正是掉期和佣金使EURJPY策略变得无利可图的情况。
让我们继续阅读这篇文章：
您可以在DatabaseExecute()函数的示例中找到包含这三种查询类型的完整源代码。
太棒了！点击链接查看帮助文档，并获取该函数示例的完整代码：
//--- 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); } //+------------------------------------------------------------------+
您可以将代码复制到编辑器中，进行编译和运行，然后在日志中查看其工作结果。
现在我们有了使用数据库获取所需结果的示例。这只需要对帮助文档中展示的代码进行少量修改。例如，我们需要将数据按某个字段排序，或者从表中获取唯一的值。为此，我们可以查阅关于 SQL 的参考信息。基于我们获得的知识和示例，我们将能够完成项目中的相关任务。
在 \MQL5\Indicators 终端文件夹中，创建 StaticticsBy 文件夹。该文件夹将包含项目的所有文件。
在创建的文件夹中，创建新文件 SQLiteFunc.mqh 并开始在其中编写处理数据库的函数。
首先，让我们编写必要的结构体：
//+------------------------------------------------------------------+ //| 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 };
这些结构体是从文档中上面展示的示例中复制的，但它们被赋予了不同的名称。此外，我们还添加了包含账户索引的字段，以及短线和长线头寸的数量，通过这些字段可以从数据库中进行筛选。
我们需要交易历史来在数据库中创建交易表。因此，我们将在同一个文件中编写获取交易历史的函数：
//+------------------------------------------------------------------+ //| 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; }
此处我们还将创建一个函数，用于从数据库中删除指定名称的表。
//+------------------------------------------------------------------+ //| 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); }
让我们编写交易表函数：
//+------------------------------------------------------------------+ //| 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); }
代码从帮助文档中拷贝而来。不过，我添加了一个账户索引字段。
同样地，我们也为交易表函数增加账户索引字段：
//+------------------------------------------------------------------+ //| 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); }
编写将成交数据写入数据库表的函数：
//+------------------------------------------------------------------+ //| 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); }
函数代码取自帮助文档中 DatabaseExecute() 的示例。我新增了一个用于对账户索引进行排序的额外变量 ，并修正了请求文本，因为帮助中可能存在错误，在构建查询字符串时，int类型被错误地指定给了long类型的数据。
//--- 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);
我们通过添加账户索引并移除成交入场时间字段的方式修复了这个问题。
接下来编写基于成交表填充交易表的函数：
//+------------------------------------------------------------------+ //| 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; }
如上述函数所示，添加了账户索引。
让我们写一个函数，从数据库中填充所有交易列表。
//+------------------------------------------------------------------+ //| 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; }
我们按入场时间倒序排列交易列表。如果不这样做，最新交易会排在末尾，在面板的表格里就会出现在最底部。这很不方便。倒序后，最新交易直接移到表头，在仪表盘最上方一眼可见，无需滚动就能查看。
接下来编写从数据库填充所有已交易品种列表的函数。
//+------------------------------------------------------------------+ //| 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; }
使用 DISTINCT获取仅包含唯一、不重复品种名称的列表。并按字母顺序排列。
同样地，实现从数据库按升序填充所有 Magic 编码列表的函数。
//+------------------------------------------------------------------+ //| 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; }
创建函数，从数据库获取基于交易品种的交易统计并保存到数组中：
//+------------------------------------------------------------------------------+ //|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; }
我在这段代码里区分了多头与空头持仓，并按净利润降序排序，让盈利最高的品种排在表格最前面。
接下来编写按magic编码获取交易统计并保存到数组的函数。
//+------------------------------------------------------------------------------------+ //|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; }
最后，我们编写一个类似的基于账户的交易统计函数：
//+---------------------------------------------------------------------------------+ //| 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; }
我们已经为创建项目准备好了基本要素。具体来说，我们已经选择了仪表盘，并根据文档信息创建了处理数据库的函数。接下来我们只需要创建仪表盘及其表格与数据库交互的逻辑，利用已实现的函数即可。考虑到仪表盘上的表格列表可能相当庞大，表格尺寸可能会超出仪表盘的显示范围。在这种情况下，我们需要实现表格的垂直和水平滚动功能。我们将直接在正在创建的指标中实现这一功能——通过鼠标滚轮实现表格的垂直滚动，通过按住Shift键的同时使用鼠标滚轮实现水平滚动。
当点击所需品种或magic编码的统计行时，将显示所选交易品种或magic编码的统计数据。为此，我们将跟踪鼠标在表格行上的位置，并监听行点击事件。将此类功能添加到仪表盘类中是合理的，这样它就可以在其他项目中复用。但在这里，我们将展示如何在不修改仪表盘类的情况下实现相同的功能。
组装仪表盘
在先前创建的 \MQL5\IndicatorsStatisticsBy 文件夹中，创建一个名为 StatisticsBy.mq5 的新指标文件。
包含表格和仪表盘类的文件，以及处理数据库的函数文件，并声明该指标没有缓冲区：
//+------------------------------------------------------------------+ //| 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"
接下来，添加宏替换、统计表格列的位置数组、输入参数和全局变量：
#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
为了指定统计表格中的列数，以及确定表格中数据的数量和位置，使用一个包含表头名称常量以及相应表头下数据的数组会比较方便。如果需要更改表格中不同数据的顺序，只需更改它们在此数组中的声明顺序并重新编译指标即可。我们还可以通过在此数组中注释掉不需要的数据来移除它们，或者添加新的数据。但在添加新数据时，我们需要将其添加到数据库函数中，以及其他计算和显示表格数据的函数中。
让我们来看一下指标的处理程序OnInit()，在这里创建数据库和带有其图形内容的仪表盘：
//+------------------------------------------------------------------+ //| 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); }
本质上，这里首先检查持仓核算类型，如果是净额核算，则指标停止工作，因为对于这种持仓核算方式，无法简单创建交易表格（仅适用于开仓-平仓交易）。
接下来，在终端数据文件夹（TERMINAL_DATA_PATH + \MQL5\Files\）中，在程序名称的文件夹内的 Database 子目录（\StatisticsBy\Database\）中创建数据库。成功创建数据库后，将创建仪表盘并填充内容——控制按钮和显示表格的仪表盘：
有趣的是，我们并没有使用按钮，而是使用了附加到主窗口的折叠形式子仪表盘——只能看到仪表盘的标题。它们有自己的鼠标光标交互处理程序，因此我们使用常规仪表盘创建了按钮，这些按钮与用户交互并将鼠标交互事件发送到主程序。
在 OnDeinit() 处理程序中关闭数据库和仪表盘：
//+------------------------------------------------------------------+ //| 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(); } }
OnCalculate()处理函数为空（指标不做任何计算）：
//+------------------------------------------------------------------+ //| 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); }
仪表盘与用户的交互工作全部在指标的事件处理程序中完成。
让我们完整地看一下 OnChartEvent()事件处理程序。其代码已经做了详尽的注释。如果我们仔细研究事件处理程序的注释，仪表盘与用户交互的整个逻辑就会变得清晰起来：
//+------------------------------------------------------------------+ //| 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; } } }
现在我们来考虑事件处理程序中调用的其他函数。每个函数的代码都做了详尽的注释，应该不会引起任何误解。
根据光标坐标返回表格行索引的函数：
//+------------------------------------------------------------------+ //| 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; }
该函数同时执行两项任务：(1) 返回鼠标光标所在的表格行号，以及 (2) 用背景色突出显示该行。
左侧面板表格内的鼠标滚轮滚动处理函数：
//+------------------------------------------------------------------+ //| 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; }
当滚动鼠标滚轮时，如果光标位于面板上的表格上方，并且表格的大小超过了面板的大小，那么表格也应该随之滚动。这正是这个处理函数所做的——它沿着指定的初始坐标移动表格。此外，使用 TableSelectRowByMouse() 函数来突出显示光标下的行，并返回该函数发送的光标下行的索引。左侧面板显示的是交易品种和magic编码的小列表，因此这里实现了简化的滚动——我们立即将表格移动到计算出的坐标。至于右侧面板，情况则稍微复杂一些。
左侧面板表格内鼠标光标偏移的处理函数：
//+------------------------------------------------------------------+ //| 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; }
与前一个函数类似，这里也查找并选择了光标下的行。但表格不会滚动。
右侧面板表格内的鼠标滚轮滚动处理函数：
//+------------------------------------------------------------------+ //| 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; }
这里，在一个函数中处理了三个表格及其各自的表头。这完全取决于传递给该函数的表格类型。在滚动大型表格时，它们不仅需要垂直滚动，还需要水平滚动。负责宽度滚动的标志是 shift_flag —— 这是在旋转鼠标滚轮时按住 Shift 键的标志。当滚动表格本身时，位于另一个面板上的表头也会随之滚动。
右侧面板表格内鼠标光标偏移的处理函数：
//+------------------------------------------------------------------+ //| 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; }
总的来说，在这里（以及上面讨论的函数中），我们只需要找到光标所在的表格行号即可。其他所有操作都只是为了处理“视觉效果”，即突出显示光标下的行，而这最终会导致 CPU 资源的消耗增加。毕竟，我们必须不断地重绘表格的整个可见部分，而仪表盘和其上的表格越大，我们需要绘制的区域就越大。当然，实际绘制的区域会受到仪表盘尺寸的限制，但这仍然不是最优的方案。如果我们是在仪表盘类中执行这样的行选择操作，情况就会完全不同——我们只会重绘光标附近的表格行，记住高亮前后的背景色，然后再恢复它。但在这里，为了简单起见，我们直接在程序函数中完成所有操作。
仪表盘表格内处理鼠标点击的函数：
//+------------------------------------------------------------------+ //| 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; }
当点击表格行时，我们需要找到并返回点击发生的行索引，以便进一步处理。这一点已经在 OnChartEvent() 中的用户事件处理程序中展示过了。
向表格填充交易品种名称的函数：
//+------------------------------------------------------------------+ //| 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); } }
在这里，除了不绘制超出面板的表格区域外，我们还限制了循环的起始点，使其从表格顶部第一个可见行开始。当表格向上滚动时，第一行可能会远远超出面板的顶部。为了避免再次“空转”循环，以及避免尝试绘制那些无论如何都不会被绘制的超出面板的表格行，我们需要计算从顶部可见的第一个表格行的索引，并从该索引开始循环。对于相当大的表格来说，这种方法能产生显著的效果，可以消除在滚动包含数百行的表格时出现的严重卡顿。
该函数用magic编码来填充表格：
//+------------------------------------------------------------------+ //| 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); } }
该函数用于填充交易表头：
//+------------------------------------------------------------------+ //| 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); } }
该函数用于填充交易表：
//+------------------------------------------------------------------+ //| 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); } } }
这两个函数本质上绘制的是同一个表格。一个函数绘制带有列标题的表格表头，列标题根据列索引来命名；而另一个函数则在表头下方绘制表格主体，填充与列标题相对应的值。总的来说，这两个函数是成对工作的。
填充交易统计表头的函数：
//+------------------------------------------------------------------+ //| 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); } }
在按交易品种或magic编码显示统计表格时，需要绘制统计表格的表头。但我们有两个统计数据表格：一个用于交易品种，另一个用于magic编码。这些表格的表头仅在第一列有所不同，其余列都相同。这里会检查表格ID，并根据ID，用符号或魔术数字来标记第一列的表头。
填充交易品种交易统计表格的函数：
//+------------------------------------------------------------------+ //| 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); } } }
填充magic编码交易统计表格的函数：
//+------------------------------------------------------------------+ //| 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); } } }
用于填充交易品种和magic编码统计表格的两个相似函数。
根据表头类型从结构体返回数据的函数：
//+------------------------------------------------------------------+ //| 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; } }
该函数接收统计表格类型（品种/magic编码/账户）、数据类型（列头值）以及结构体数组中的数据索引。根据传递给函数的数据，返回来自相应结构体数组中的一个值。为了简化，该值始终以double类型返回，即使它在结构体中是整数。在接下来的函数中，这个值会被作为字符串返回，并带有所需的十进制位数。
根据表头类型从结构体返回数据（作为字符串）的函数：
//+------------------------------------------------------------------+ //| 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)); }
这些函数用于将统计表格中单元格的值发送到仪表盘表格中。
返回交易品种在品种统计数组中索引的函数：
//+------------------------------------------------------------------+ //| 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; }
该函数从基于magic编码的统计数组中返回交易品种的索引：
//+------------------------------------------------------------------------+ //| 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; }
这两个函数都返回在相应的交易品种或magic编码统计数组中搜索到的相应索引。
显示所选交易品种、magic编码或账户的最终统计数据的函数：
//+--------------------------------------------------------------------+ //| 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; }
该函数用于绘制所选交易品种、magic编码或整个账户的最终统计表格。该函数接收统计表格的类型，以及交易品种的名称、magic编码的字符串值或账户号码。文本确定了交易品种、magic编码或账户在相应的统计数据结构数组中的索引。我们使用获得的索引从所需的结构体中获取所有统计数据，然后将它们根据单元格的坐标在渲染的表格中进行排列。在这种情况下，显示文本的水平偏移量是这样计算的：数据标题绑定到表格单元格的左边缘，而数据值的文本绑定到其表格单元格的右边缘。所有数据都显示在四列中，以便它们在面板上视觉上以两列的形式分组显示，即“标题 – 值”。
让我们编译这个指标，看看我们得到了什么：
我们可以看到，所有声明的功能都按预期工作。我们可以看到，当移动光标和滚动表格时，表格中的文本有轻微的“闪烁”。但这是一种次优重绘方案的结果——表格的整个可见部分都在不断重绘。可以通过更复杂的处理鼠标下表格行的逻辑来避免这种情况，但这尚不再本文考虑范围内。
表格可以通过旋转鼠标滚轮垂直滚动，按住Shift键的同时旋转滚轮可以水平滚动。如果我们仔细观察视频，我们会注意到，当显示magic编码值为0的统计数据时，多头和空头的数量显示为0。这是由从数据库查询中定义交易时的一个错误引起的。交易表是从成交表创建的。如果一个仓位是由EA（在这种情况下magic编码为600）开仓而手动平仓，那么开仓成交将有指定的magic编码，而平仓成交的magic编码则为0。这可以在成交历史记录中看到：
在这里，我们使用平仓成交来确定交易，而它的magic编码是0。无法使用magic编码0找到开仓成交。因此，对于magic编码0，既找不到多头仓位也找不到空头仓位。因此，在创建交易表时，值得考虑仓位可以由EA开仓手动平仓，反之亦然的可能性。如果我们考虑到这一点，那么这样的错误就不再应该发生了。
结论
在本文中，我们给自己设定了创建一个显示交易统计数据的仪表盘的任务，并且不得不通过参考本资源上的文章和文档示例来解决这个问题。正如我们所见，即使我们不知道某些东西，也可以很容易地利用该资源提供的庞大知识库找到问题的答案，并开发出一个功能完善的产品。
研究网站上提供的信息，阅读文章和文档，与更有经验的前辈交流，将找到的示例调整以适应您的任务，一切肯定都会成功！
本文讨论的类、函数和指标的所有文件都附在文章中。还附带了一个存档文件，可以解压到终端数据目录中。所有必要的文件都放置在 \MQL5\Indicators\StatisticsBy 文件夹中，以便可以立即进行编译，并且可以启动指标文件。
本文由MetaQuotes Ltd译自俄文
原文地址： https://www.mql5.com/ru/articles/16233
附加的文件 |
Dashboard.mqh
(256.99 KB)
SQLiteFunc.mqh
(64.62 KB)
StatisticsBy.mq5
(164.58 KB)
MQL5.zip
(45.47 KB)
注意: MetaQuotes Ltd.将保留所有关于这些材料的权利。全部或部分复制或者转载这些材料将被禁止。
使用Python与MQL5进行多个交易品种分析（第二部分）：主成分分析在投资组合优化中的应用
交易账户风险管理是所有交易者面临的共同挑战。我们如何在MetaTrader 5中开发能够动态学习不同交易品种的高、中、低风险模式的交易应用？通过主成分分析（PCA），我们可以更有效地控制投资组合的方差。本文将演示如何从MetaTrader 5获取的市场数据中，训练出这三种风险模式的交易模型。
新手在交易中的10个基本错误
新手在交易中会犯的10个基本错误: 在市场刚开始时交易, 获利时不适当地仓促, 在损失的时候追加投资, 从最好的仓位开始平仓, 翻本心理, 最优越的仓位, 用永远买进的规则进行交易, 在第一天就平掉获利的仓位,当发出建一个相反的仓位警示时平仓, 犹豫。
您应当知道的 MQL5 向导技术（第 44 部分）：平均真实范围（ATR）技术指标
ATR 振荡指标是一款非常流行的指标，权当波动率代表，尤其是在交易量数据稀缺的外汇市场当中。我们以形态为基础来验证这一点，就如我们对先前指标所做那样，并分享策略和测试报告，致谢 MQL5 向导库的类和汇编。
目前缺乏能让您处理大量交易历史记录的工具。
遗憾的是，这个工具包和其他许多工具包一样，在请求历史记录时就会挂掉。
获取历史记录需要五分钟。然后就无法在窗口中进行任何操作了--CPU 负载过大。
缺乏处理大量交易历史的工具。
遗憾的是，这个工具包和其他许多工具包一样，在请求历史记录时就会挂起。
五分钟才能获得历史记录。然后就无法在窗口中做任何事情了--CPU 负载过高。
我可以让投资者进入账户吗？
很遗憾，没有这种可能性。但您可以自己创建这样的账户：在模拟账户上使用脚本，使用异步订单发送（asynchronous OrderSend）功能，在一小时内按不同的符号/魔法开仓/平仓所需的仓位数量。
不想在莫斯科证券交易所工作
不想在莫斯科证券交易所工作
自然。如果一个符号上有多个机器人工作（或机器人加人工交易），那么除了总仓位外，所有与仓位有关的东西在净额结算时都是没用的。