MQL5におけるSQLiteの機能：銘柄とマジックナンバー別の取引統計を表示するダッシュボード
MetaTrader 5
—
例
|
37
5
内容
はじめに
MQL5.comは、アルゴリズム取引の分野において、ユーザーに多様なリファレンス情報や教育コンテンツへの幅広いアクセスを提供するリソースです。豊富なデータベースと高い機能性を備えているにもかかわらず、一部のユーザーにとっては、特定のソリューションを見つけたり、提供されているサンプルを自身のニーズに適応させることが難しい場合があります。そうしたケースでは、フォーラムを通じてコミュニティと交流し、有益なアドバイスや場合によっては完成済みのソリューションを得ることも可能です。
本記事の目的は、よくある課題の一つ、つまり、取引履歴および口座統計を表示する情報パネルの作成を実演・解決することにあります。このパネルの開発プロセスを、mql5.comにある既存の資料のみを使って進めていきます。これは、実用的なソリューションであるだけでなく、学習やリファレンス資料を実際の状況にどのように応用できるかを示す好例でもあります。
タスクの形成
リクエストに応じて、口座上の取引履歴および銘柄・マジックナンバー（口座上で取引しているエキスパートアドバイザー(EA)）別の取引統計情報を表示できる情報パネルを作成する必要があります。また、口座全体の取引統計も表示する必要があります。取引履歴は、取引がおこなわれた時間順に並べ替える必要があります。銘柄およびマジックナンバー別の統計は、純利益順に並べ替えます。
使用するプログラムの種類はインジケーターです。データはバッファに出力しません。つまり、バッファを使用しないインジケーターであり、主な作業領域はグラフィックパネルとなります。パネルは視覚的に2つの領域に分けられます。左側には、口座上で取引がおこなわれたすべての銘柄およびマジックナンバーのリストを表示し、右側には、銘柄およびマジックナンバーごとの統計情報をリスト形式で表示するか、特定の銘柄またはマジックナンバー、あるいは口座全体の最終統計を表示します。表示内容の切り替えは、パネル上のボタン、または右側の統計テーブル内の銘柄やマジックナンバーの行をクリックすることでおこないます。
記事「指標やEAのデータを表示するダッシュボードの作成」では、情報パネルを使用しています。
記事「SQLite：MQL5でのSQLデータベースのネイティブな処理」を参考にして、口座全体や銘柄・マジックナンバー別の統計を取得します。
情報パネル
最初の記事でダッシュボードの作成について紹介して以来、そのコードにはいくつかの変更と改良が加えられました。本記事の目的は、これらの変更をすべて詳細に説明することではありません。そのため、ここでは変更点の概要を簡単に見ていきます。具体的にどのような変更が加えられたのかは、指定された記事から最初のバージョンのパネルをダウンロードし、本記事に添付されたコードと比較することで確認できます。
チャートを切り替える際にパネルの位置や表示に関するエラーを修正しました。現在では、子ダッシュボードをメインのダッシュボードに添付することが可能です。特に、子ダッシュボードを折りたたんでヘッダー部分だけを表示することで、ボタンとして機能させることができます。また、パネル内でのテーブルの配置に関する制限を取り除きました。以前は、テーブルはパネル内、すなわち表示可能な領域内にしか描画できませんでした。しかし、場合によっては、リストがダッシュボードのサイズを超えるような長いまたは幅広いテーブルをスクロール表示させる必要があります。そのため、テーブルの初期座標をダッシュボードの表示領域外に設定できるようにすることで、スクロール機能を実現できるのです。本日はまさにこの対応をおこないます。ただし、ダッシュボードおよびテーブルのクラス自体には変更を加えません（機能性の観点からはクラスの修正が望ましいですが）、これは適切なアプローチを取ることで、完全ではないサンプルコードからでも必要な機能を実現できることを示すためです。
それでは、テーブルおよびダッシュボードクラスのコードに加えられた変更点を簡単に見ていきましょう。
テーブルセルクラスには、セルのテキストを格納するための変数と、その変数を処理するためのメソッドが追加されました。
//+------------------------------------------------------------------+ //| 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
子ダッシュボードを親ダッシュボードにバインドできるようになったため、パネルを操作するいくつかのメソッドがprotectedセクションからpublicセクションへ移動されました。これらのメソッドは外部からアクセスできる必要があるためです。さらに、新しいメソッドも追加されました。
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#の共同プロジェクトに携わった際、DBは別の担当者が管理していて、私は提供されたコネクタを使ってDBをプロジェクトに接続するだけでした。そのため、今回はmql5.comの参考資料や記事を頼りに学ぶ必要がありました。「SQLite：MQL5でのSQLデータベースのネイティブな処理」という記事を読んだとき、すぐにドキュメントへの参照、特にDatabasePrepare関数に注目しました。この関数の中には取引履歴テーブルの作成例があり、それを基に取引テーブルが作られています。これこそ私たちが必要としている機能の一つです。忍耐強く取り組み、例と関数をじっくり学んでいきましょう。
まず、取引履歴と取引のデータを格納するための2つの構造体が示されています。
//--- 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); }
- データベースを作成します。
- データベースに取引テーブルを作成します。
- 取引履歴を要求し、作成されたテーブルに取引を入力します。
- 口座の種類を確認します。ネット取引では、取引のみに基づいて取引履歴を作成することは不可能であるため、ヘッジが必要です。
- 取引テーブルに基づいて取引テーブルが作成され、取引テーブルデータに基づいて取引データが取引テーブルに入力されます。
提示されたスクリプトでは、作成したテーブルから最初の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 Numberを持っていることを意味します。
SQLクエリを使用すれば、Magic Numberの観点から取引を分析することが可能です。
//--- 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：戦略ごとの総利益（すべての正の利益値の合計）
- gross_loss：戦略ごとの総損失（すべての負の利益値の合計）
- total_commission：戦略による取引の手数料合計
- total_swap：戦略による取引のスワップ合計
- total_profit：gross_profitとgross_lossの合計
- net_profit：gross_profit、gross_loss、total_commission、total_swapの合計
- win_trades：利益が0より大きい取引の数
- loss_trades：利益が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)
利益および損失の計算において、スワップや手数料は含まれていません。これにより、純粋なコストが見えるようになります。たとえば、あるストラテジーがわずかに利益を出しているように見えても、スワップや手数料の影響で実際には総合的に不採算である可能性があります。
銘柄ごとの取引分析
取引を銘柄単位で分析することも可能です。そのためには、以下のようなSQLクエリを実行します。
//--- 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)、一方で、6銘柄ではプロフィットファクターが1を超えています(profit_factor > 1)。これはまさに、EURJPYにおいてスワップや手数料が戦略の収益性を損なっている典型的な例です。
続きを見てみましょう。
これら3種類のクエリを含む完全なソースコードは、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); }
関数のコードはヘルプからコピーしたものですが、口座インデックスのフィールドをもう1つ追加しました。
同様に、取引テーブル用の関数も実装しましょう。こちらにも、同様に口座インデックスのフィールドを含めるようにします。
//+------------------------------------------------------------------+ //| 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関数の例から引用していますが、口座インデックスを格納するための変数を追加実装しました。また、ヘルプの例ではクエリ文字列を作成する際に、long型のデータに対してint型が指定されていると思われる誤りがあったため、その部分のリクエスト文を修正しています。
//--- 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キーワードを使用して、重複しない一意のシンボル名だけを含むリストを取得しています。このリストはアルファベット順で並びます。。
同様に、すべてのマジックナンバーをデータベースから取得して、昇順でリストに格納する関数を実装しましょう。
//+------------------------------------------------------------------+ //| 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; }
ここでは、ロングポジションとショートポジションを考慮するための文字列を追加し、純利益の降順でリストが並ぶようにしています。これにより、最も大きな利益を生み出した銘柄がテーブルの先頭に表示されます。
同様にして、データベースからマジックナンバーごとの取引統計を取得し、それを配列に保存する関数を作成します。
//+------------------------------------------------------------------------------------+ //|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キーを押しながらホイールを回転させることで横方向にスクロールされます。
選択された銘柄またはマジックナンバーの統計は、目的の銘柄またはマジックナンバーの統計行をクリックすることで表示されます。そのために、テーブル行上にあるカーソルの位置および行のクリックを追跡します。このような機能は、他のプロジェクトでも使用できるようにダッシュボードクラス内に実装するのが合理的ですが、ここではダッシュボードクラスを修正せずに同様のことが可能であることを示します。
ダッシュボードの組み立て
以前に作成した\MQL5\Indicators\StatisticsBy\フォルダに、StatisticsBy.mq5という名前の新しいインジケーターファイルを作成します。
テーブルとダッシュボードのクラスファイル、およびDBを処理するための関数ファイルをインクルードし、インジケーターにレンダリングされたバッファがないことを通知します。
//+------------------------------------------------------------------+ //| 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\)で、データベースサブディレクトリ(\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; }
この関数は2つの役割を同時に果たします。(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関数を使ってハイライトされ、その関数から返されたカーソル下の行インデックスを返します。左パネルには銘柄やマジックナンバーの小さなリストが表示されるため、スクロール処理は簡略化されており、計算された座標へテーブルを即座に移動させています。一方で右パネルの場合は、もう少し複雑な処理が必要です。
左パネルのテーブル内におけるマウスカーソルのオフセット処理用ハンドラ関数
//+------------------------------------------------------------------+ //| 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; }
ここでは、3つのテーブルとそれぞれのヘッダーが1つの関数で処理されています。処理内容は、関数に渡されるテーブルの種類によって変わります。大きなテーブルをスクロールする際は、高さ方向だけでなく幅方向のスクロールも必要です。幅方向のスクロールを制御するフラグが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); } }
ここでは、パネルの表示領域を超えているテーブルの部分を描画しないようにするだけでなく、ループの開始をパネルの上部に表示されている最初のテーブル行から始めるように制限しています。テーブルを上方向にスクロールすると、最初の行がパネルの上端から大きくはみ出すことがあります。そのため、無駄にループを回してパネル外の行を描画しようとしないよう、パネル上部に表示されている最初のテーブル行のインデックスを計算し、そこからループを開始します。この方法は比較的大きなテーブルに対して特に効果的で、数百行に及ぶテーブルをスクロールする際の大幅な動作遅延を軽減します。
テーブルにマジックナンバーを入力する関数埋める関数
//+------------------------------------------------------------------+ //| 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); } } }
この2つの関数は本質的に1つのテーブルを描画しています。1つ目の関数は、列インデックスに基づいてタイトルが付けられた列見出しのヘッダー部分を描画し、2つ目の関数は、そのヘッダーに対応した値を持つテーブル本体をヘッダーの下に描画します。一般的に、これらの関数はペアで動作します。
取引統計ヘッダーテーブルを埋める関数
//+------------------------------------------------------------------+ //| 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); } }
銘柄別またはマジックナンバー別の統計テーブルを表示する際には、統計テーブルのヘッダーを描画する必要があります。しかし、統計データのテーブルは2種類あり、1つは銘柄用、もう1つはマジックナンバー用です。これらのテーブルのヘッダーは、最初の列のみが異なり、それ以外の列は共通しています。ここでは、テーブルのIDを確認し、それに応じて最初の列のヘッダーに「symbol」または「magic number」と表示しています。
銘柄用取引統計テーブルを埋める関数
//+------------------------------------------------------------------+ //| 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); } } }
マジックナンバー用取引統計テーブルを埋める関数
//+------------------------------------------------------------------+ //| 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); } } }
銘柄とマジックナンバー用統計テーブルを埋める2つの類似した関数。
ヘッダーの種類に応じて構造体からデータを返す関数
//+------------------------------------------------------------------+ //| 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; } }
この関数は、統計テーブルの種類（銘柄/マジックナンバー/口座）、データの種類（列ヘッダーの値）、および構造体配列内のデータインデックスを受け取ります。渡されたデータに応じて、対応する構造体配列から値が返されます。処理を簡略化するため、構造体内の値が整数であっても、戻り値は常に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; }
マジックナンバーに基づいて、統計配列内の銘柄のインデックスを返す関数
//+------------------------------------------------------------------------+ //| 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; }
両方の関数は、該当する銘柄またはマジックナンバーが存在する統計配列内のインデックスを返します。
選択された銘柄、マジックナンバー、または口座に対する最終的な統計を表示する関数
//+--------------------------------------------------------------------+ //| 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; }
この関数は、選択された銘柄、マジックナンバー、または口座全体に対する最終的な統計テーブルを描画するために設計されています。関数は、統計テーブルの種類、およびシンボル名、マジックナンバーの文字列値、またはアカウント番号を受け取ります。渡された文字列から、対応する統計データ構造体配列内の銘柄、マジックナンバー、またはアカウントのインデックスを特定します。必要な構造体データは、取得したインデックスを使用して抽出され、次にテーブルのセル座標に従って描画されたテーブル内に配置されます。このとき、表示されるテキストの水平方向のオフセットは、見出しがテーブルセルの左端に揃えられ、値のテキストがセルの右端に揃えられるように計算されます。すべてのデータは4列で表示され、パネル上で視覚的に「項目名 -- 値」の2列構成が2セットにグループ化される形式になります。
インジケーターをコンパイルして、結果を確認してみましょう。
すべての宣言された機能が期待通りに動作していることが確認できます。テーブル内のテキストが、カーソルを動かしたりスクロールしたりする際にわずかに「点滅」して見えることがありますが、これは最適でない再描画方式によるもので、テーブルの表示部分全体が継続的に再描画されているためです。カーソルの下にあるテーブル行だけを対象としたより複雑なロジックを導入すればこれを回避することもできますが、それは本プロジェクトの目的ではありません。
テーブルは、マウスホイールの回転によって縦方向にスクロールでき、Shiftキーを押しながらホイールを回すことで横方向にスクロールできます。動画を注意深く見ると、マジックナンバーが0の統計を表示した際に、ロングおよびショートポジション数がゼロと表示されているのに気づくでしょう。これは、データベースからのクエリにおける取引定義の誤りによるものです。取引テーブルは、取引履歴テーブルから作成されます。たとえば、ポジションがエキスパートアドバイザー（この場合マジックナンバー600）によって開かれ、手動でクローズされた場合、オープン取引にはマジックナンバーが指定されますが、クローズ取引には0が指定されます。これは取引履歴で確認できます。
ここでは、クローズされた取引を基に取引を特定していますが、その際のマジックナンバーは0となっています。マジックナンバーが0の状態では、対応するオープン取引を特定することができません。その結果、マジックナンバー0に対してはロングポジションもショートポジションも検出されないという状況が発生します。したがって、取引テーブルを作成する際には、「ポジションがEAによって開かれ、手動でクローズされる」または「手動で開かれ、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.が保有しています。これらの資料の全部または一部の複製や再プリントは禁じられています。
リプレイシステムの開発（第72回）：異例のコミュニケーション(I)
私たちが本日作成する内容は、理解が難しいものになるでしょう。したがって本稿では、初期段階についてのみ説明します。この段階は次のステップに進むための重要な前提条件となるため、ぜひ注意深く読んでください。この資料の目的はあくまで学習にあります。提示された概念を実際に応用するのではなく、あくまで理解・習得することが目的です。
高度なICT取引システムの開発：インジケーターへのオーダーブロックの実装
この記事では、オーダーブロックのミティゲーションを検出し、描画し、アラートを発するインジケーターの作り方を学びます。また、チャート上でこれらのブロックを正確に特定する方法や、正確なアラートの設定方法、価格の動きをより理解しやすくするために矩形で位置を可視化する方法についても詳しく解説します。このインジケーターは、スマートマネーコンセプトやインナーサークルトレーダーの手法を用いるトレーダーにとって重要なツールとなるでしょう。
初級から中級まで：配列(IV)
この記事では、C、C++、Javaなどの言語で実装されていることと非常によく似たことを実現する方法について見ていきます。つまり、関数や手続きにほぼ無限の数のパラメータを渡す方法についてです。これは一見すると高度なトピックに思えるかもしれませんが、前回までの内容をきちんと理解していれば、実装は決して難しくありません。もちろん、それらの内容を本当に理解していることが前提です。
取引におけるニューラルネットワーク：NAFSによるノード依存型グラフ表現
NAFS (Node-Adaptive Feature Smoothing)手法を紹介します。これは、パラメータの学習を必要としない非パラメトリックなノード表現生成手法です。NAFSは、各ノードの近傍ノードに基づいて特徴量を抽出し、それらを適応的に統合することで最終的なノード表現を生成します。
取引の機会を逃しています。
- 無料取引アプリ
- 8千を超えるシグナルをコピー
- 金融ニュースで金融マーケットを探索
大量の取引履歴を扱うことができるツールが不足している。
残念なことに、このツールキットは、他の多くのツールと同様、履歴を要求すると単にハングアップする。
履歴を取得するのに5分かかる。その後、ウィンドウを使って何かをすることは不可能である。
大量の取引履歴を扱うツールが不足している。
残念ながら、このツールキットは、他の多くのツールキットと同様、履歴を要求するとハングアップしてしまう。
履歴を取得するのに5分。その後、ウィンドウを使って何かをすることは不可能である。
投資家が口座にアクセスすることはできますか？
残念ながら、そのような可能性はありません。デモ口座で、非同期OrderSendを使用して、1時間に異なるシンボル/マジックで必要な数のポジションをオープン/クローズするスクリプトを使用します。
モスクワ証券取引所で働きたくない
モスクワ証券取引所で働きたくない
当然だ。1つのシンボルで複数のロボットが動作している場合（またはロボット+手動取引）、合計ポジションを除いて、ポジションに関連するすべてのものは、ネットでは役に立たない。