Capacidades de SQLite en MQL5: Ejemplo de panel interactivo con estadísticas comerciales por símbolos y números mágicos
MetaTrader 5
—
Ejemplos
|
271
5
Contenido
- Introducción
- Fijamos un objetivo
- Panel informativo
- Funciones para trabajar con la base de datos
- Montamos el proyecto - panel informativo
- Conclusión
Introducción
MQL5.com es un recurso que ofrece a los usuarios acceso a una amplia variedad de referencias e información educativa en el campo del trading algorítmico. A pesar de la amplitud de la base de datos y de sus posibilidades, algunos usuarios siguen teniendo dificultades para encontrar soluciones concretas o adaptar los ejemplos a sus necesidades. Para estos casos, el recurso ofrece la oportunidad de interactuar con la comunidad a través del foro, donde se pueden obtener consejos útiles o incluso soluciones ya preparadas.
El objetivo de este artículo consiste en demostrar y resolver una de las tareas típicas: crear un panel informativo para mostrar la historia de transacciones y las estadísticas de una cuenta. Asimismo, examinaremos el proceso de elaboración de este panel, basándonos únicamente en los materiales existentes en mql5.com, lo cual resulta interesante no solo como solución práctica, sino también como ejemplo de aplicación de los materiales de formación y la guía de referencia en condiciones reales.
Fijamos la tarea a realizar
Nuestra tarea consistirá en crear un panel informativo en el que se pueda mostrar, previa solicitud, información sobre la historia de transacciones en la cuenta y estadísticas de transacciones según el símbolo y el número mágico (expertos que operan en la cuenta). También deberemos mostrar todas las estadísticas comerciales de la cuenta. La historia de transacciones deberá clasificarse según la hora de ejecución de las transacciones. Las estadísticas comerciales de los símbolos y números mágicos deberán clasificarse según el beneficio neto (Net Profit).
El programa lo haremos de tipo Indicador. No enviaremos ningún dato a los búferes. Es decir, será un indicador sin búfer cuyo principal espacio de trabajo será el panel gráfico. El panel se dividirá visualmente en dos áreas: en la parte izquierda mostraremos las listas de todos los símbolos y números mágicos negociados en la cuenta, mientras que en la parte derecha mostraremos o bien tablas estadísticas sobre los símbolos y números mágicos en forma de lista, o bien las estadísticas finales sobre cualquier símbolo o número mágico seleccionado, o sobre la negociación en la cuenta en su totalidad. Controlaremos la muestra de determinadas listas utilizando los botones del panel, o clicando en la fila con las estadísticas según el símbolo/número mágico de la tabla de la derecha del panel.
Toaremos el panel de instrumentos del artículo "Cómo crear un panel informativo para mostrar datos en indicadores y asesores".
El artículo "SQLite: trabajo nativo con bases de datos en SQL en MQL5" nos ayudará a obtener estadísticas según la cuenta y los símbolos y números mágicos (estrategias comerciales).
Panel informativo
Desde la primera publicación del artículo sobre la creación de un panel informativo, su código ha sufrido algunos cambios y mejoras. La descripción detallada de todos los cambios introducidos en el código queda fuera del alcance de este artículo. Así que haremos un breve repaso de los cambios. Y para ver qué y cómo se ha mejorado exactamente, podrá descargar la primera versión del panel del artículo anterior y compararla con el código del mismo panel adjunto al artículo.
Hemos corregido errores de posicionamiento y de visualización del panel en algunas situaciones al cambiar de gráfico. Ahora podremos adjuntar paneles secundarios al panel. En concreto, podremos crear botones a partir de ellos colapsando el panel hijo y dejando solo su encabezado, que servirá de botón. Asimismo, hemos eliminado las restricciones a la colocación de tablas dentro de un panel. Inicialmente, no se podía dibujar una tabla fuera del panel, solo en el ámbito. En algunos casos, las tablas dibujadas en un panel deberán colocarse fuera del área visible del panel. Esto será necesario para desplazarse por tablas largas o anchas cuyas listas sean mayores que el tamaño del panel. Así, si permitimos que las coordenadas iniciales de la tabla se sitúen fuera del panel, podremos hacer que la tabla se desplace. Eso es exactamente lo que vamos a implementar hoy. Sin embargo, no cambiaremos las clases del panel y sus tablas (aunque sea más correcto en el sentido de seguir utilizando la clase de panel finalizado), para mostrar claramente que, si tenemos ejemplos que no resulten del todo adecuados en términos de funcionalidad, con el enfoque correcto siempre podremos llevar la funcionalidad al resultado deseado.
Veamos brevemente lo que se ha mejorado en el código de las clases de tabla y panel.
En la clase de celda de la tabla hemos añadido la variable para guardar el texto de la celda y los métodos para trabajar con esta celda:
//+------------------------------------------------------------------+ //| 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){} };
En la clase de datos de la tabla, el identificador puede tener ahora un valor negativo, al igual que las coordenadas de la tabla pueden ser negativas. Asimismo, hemos añadido un método para establecer el identificador de la tabla:
//+------------------------------------------------------------------+ //| 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); }
También hemos añadido un constructor por defecto:
//--- 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(); }
El constructor por defecto permitirá declarar un objeto de la clase sin crearlo utilizando el operador new, mientras que los valores de coordenadas con el tipo int permitirán establecer las coordenadas iniciales de las tablas fuera de la ventana del panel.
En la clase de objeto panel se declararán las nuevas variables:
//+------------------------------------------------------------------+ //| 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
Como ahora podemos vincular hijos al panel padre, algunos métodos de trabajo con el panel se han movido de la sección protegida a la sección pública; estos métodos requieren acceso desde el exterior, y se han añadido nuevos métodos:
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
Todos los nuevos métodos y modificaciones realizadas permiten ahora adjuntar paneles hijos al panel padre y usarlos como objetos independientes, pero dependientes de su padre. Ahora podemos crear tablas desplazables si son mayores que el tamaño del propio panel. Antes, las tablas solo podían tener un tamaño inferior al del propio panel. En la clase panel no existe funcionalidad de desplazamiento de tabla: la organizaremos directamente desde el programa principal. Más adelante, si es necesario, añadiremos dicha funcionalidad a la clase panel y a sus tablas. Pero por el momento no tenemos tal necesidad.
Naturalmente, aquí hemos considerado solo una pequeña parte de los cambios realizados en la clase panel y sus tablas; solo los métodos declarados. Desde la primera publicación hemos ido introduciendo gradualmente mejoras en una parte bastante amplia del código. Si lo desea, siempre podrá descargar la primera versión del panel desde el artículo con su descripción y compararla con la versión presentada en este artículo. El archivo del panel informativo deberá encontrarse en el directorio de este proyecto: \MQL5\Indicators\StatisticsBy\Dashboard\Dashboard\Dashboard.mqh.
Funciones para trabajar con la base de datos
Trabajé con bases de datos hace mucho y durante muy poco tiempo, en un proyecto colaborativo para la industria del videojuego en C# en el que la base de datos corría a cargo de otra persona y yo simplemente usaba el conector proporcionado para conectar la base al proyecto. Así que aquí he tenido que armarme de material de referencia y artículos del recurso mql5.com. Cuando empecé a estudiar el artículo "SQLite: trabajo nativo con bases de datos en SQL en MQL5", inmediatamente vi referencias en la documentación, en primer lugar a la función DatabasePrepare(). Y ahí hay un ejemplo que crea una tabla de operaciones, y usando esta como base, una tabla de transacciones. ¡Es una de las cosas que necesitamos! Ármese de paciencia y estudie el ejemplo y sus funciones.
En primer lugar veremos dos estructuras para almacenar datos de operaciones y transacciones:
//--- 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 };
A continuación, analizaremos la lógica:
//+------------------------------------------------------------------+ //| 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); }
- Se crea una base de datos,
- Se crea una tabla de operaciones en la base de datos,
- Se solicita la historia de transacciones y estas se introducen en la tabla creada,
- Se comprueba el tipo de cuenta. Tiene que haber cobertura, porque para la compensación no se puede crear un historia de transacciones basada únicamente en las operaciones.
- Se crea una tabla de transacciones a partir de la tabla de operaciones, y en la tabla de transacciones se introducen los datos de las transacciones a partir de los datos de la tabla de operaciones.
El script presentado también imprimirá las diez primeras operaciones y las diez primeras transacciones de las tablas creadas. No necesitamos esto.
Basándonos en la lógica, necesitaremos crear varias funciones basadas en las presentadas en el ejemplo y a partir de las líneas de código en el cuerpo del script de ejemplo:
//+------------------------------------------------------------------+ //| 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); } //+------------------------------------------------------------------+
Además de la estructura y los campos de la tabla que se muestran en el ejemplo, también necesitaremos un campo que almacene el número de cuenta, ya que será necesario para crear una tabla de estadísticas sobre el comercio en la cuenta. En otras palabras, estadísticas comerciales completas.
¿Cómo crearemos las tablas de estadísticas? Vale, echemos un vistazo más abajo al artículo y encontremos la información. Eso es exactamente lo que necesitamos:
Análisis del portafolio según la estrategia
En los resultados del script anterior de DatabasePrepare, podemos ver que se negocia con varios pares de divisas. Pero además, ahí también vemos en la columna [magic] valores de 100 a 600. Esto significa que hay varias estrategias operando en la cuenta comercial, cada una de las cuales tiene su propio Magic Number para identificar sus operaciones.
Con la ayuda de la consulta SQL podemos analizar el comercio en términos de valores de magic:
//--- get trading statistics for Expert Advisors by Magic Number request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT MAGIC," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY MAGIC" " ) as r");
Resultado:
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
Podemos ver que 4 de las 6 estrategias han mostrado beneficios. Y para cada una de las estrategias, hemos obtenido medidas estadísticas:
- trades — número de transacciones en la estrategia,
- gross_profit — beneficio total de la estrategia (suma de todos los valores positivos de profit),
- gross_loss — pérdida total de la estrategia (suma de todos los valores negativos de profit),
- total_commission — suma de todas las comisiones de las transacciones de la estrategia,
- total_swap — suma de todos los swaps de las transacciones de la estrategia,
- total_profit — suma de gross_profit y gross_loss,
- net_profit — suma (gross_profit + gross_loss + total_commission + total_swap),
- win_trades — número de transacciones con profit>0,
- loss_trades — número de transacciones con profit>0,
- expected_payoff — expectativa de una operación sin swaps ni comisiones = net_profit/trades,
- win_percent — porcentaje de transacciones ganadoras,
- loss_percent — porcentaje de transacciones perdedoras,
- average_profit — beneficio medio = gross_profit/win_trades,
- average_loss — pérdida media = gross_loss /loss_trades,
- profit_factor — factor de beneficio = gross_profit/gross_loss.
Esta estadística de cálculo de pérdidas y ganancias no consideran los swaps ni las comisiones que se hayan cargado en la posición. Esto permitirá ver estos costes en su forma más pura. Puede resultar que la estrategia en sí genere un pequeño beneficio, pero debido a los swaps y las comisiones resulte poco rentable.
Análisis de las operaciones por símbolos
Podemos analizar el comercio por símbolos. Para ello, realizaremos una solicitud así:
//--- 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");
Resultado:
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
Las estadísticas nos dicen que solo 5 de cada 10 símbolos han tenido un beneficio neto (net_profit>0), aunque 6 de cada 10 han tenido un factor de beneficio positivo (profit_factor>1). Este es exactamente el caso cuando los swaps y las comisiones hacen que la estrategia resulte perdedora en EURJPY.
Seguimos leyendo el artículo y vemos:
El código fuente completo con estos 3 tipos de consultas se puede encontrar en el ejemplo DatabaseExecute().
¡Estupendo! Clicamos en el enlace a la ayuda y obtenemos el código completo del ejemplo para esta función:
//--- 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); } //+------------------------------------------------------------------+
Podemos copiarlo en un editor, compilarlo y luego ejecutarlo para ver el resultado de su funcionamiento en el registro.
Ahora tenemos ejemplos de cómo debemos trabajar con la base de datos para obtener el resultado deseado. Solo requerirá que mejoremos ligeramente los códigos proporcionados en la ayuda. Por ejemplo, necesitaremos que los datos estén clasificados según un cierto campo, u obtener solo valores únicos de las tablas. Para ello, podemos leer la información de ayuda sobre SQL, y basándonos en los conocimientos y ejemplos que hemos adquirido, podremos hacer lo que necesitemos para el proyecto que tenemos en mente.
Vamos a crear ahora en el directorio del terminal \MQL5\Indicators\ la nueva carpeta StaticticsBy\. En ella se contendrán todos los archivos de este proyecto.
En la carpeta creada, crearemos el nuevo archivo SQLiteFunc.mqh y empezaremos a rellenarlo con funciones para trabajar con la base de datos.
En primer lugar, escribiremos las estructuras necesarias:
//+------------------------------------------------------------------+ //| 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 };
Las estructuras se han copiado de los ejemplos mostrados anteriormente en la Documentación, pero les hemos dado nombres diferentes y hemos añadido campos con el número de cuenta y el número de posiciones largas y cortas mediante los cuales será posible hacer una selección de la base de datos.
Necesitamos la historia de transacciones para crear una tabla de operaciones en la base de datos. Por ello, escribiremos la función para obtener la historia de transacciones en el mismo archivo:
//+------------------------------------------------------------------+ //| 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; }
Aquí también escribiremos una función para eliminar la tabla con el nombre especificado de la base de datos:
//+------------------------------------------------------------------+ //| 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); }
Y escribiremos una función para crear una tabla de operaciones:
//+------------------------------------------------------------------+ //| 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); }
El código de la función se ha copiado de la ayuda, pero aquí añadiremos un campo más: el número de cuenta.
Por analogía, escribiremos una función para crear una tabla de transacciones donde también se añadirá el campo con el número de cuenta:
//+------------------------------------------------------------------+ //| 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); }
Luego escribiremos una función para rellenar la tabla de la base de datos con los datos de las operaciones:
//+------------------------------------------------------------------+ //| 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); }
El código de la función se ha tomado del ejemplo de la ayuda de la función DatabaseExecute(). Aquí hemos creado adicionalmente una variable para almacenar el número de cuenta y hemos corregido el texto de la consulta, porque probablemente haya un error en la ayuda: al componer el texto de la línea de consulta, los tipos de datos se especifican como int para datos que tienen el tipo long:
//--- add each deal to the table using the following query string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON,HOUR)" "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d,%d)", deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason, hour);
Aquí hemos corregido esto añadiendo el número de cuenta y eliminando la hora de entrada en la operación, ya que aquí no la necesitamos.
Ahora escribiremos una función para rellenar la tabla de transacciones basándonos en la tabla de operaciones:
//+------------------------------------------------------------------+ //| 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; }
Al igual que en las funciones anteriores, aquí añadiremos el número de cuenta.
A continuación, escribiremos una función que rellenará la lista de todas las transacciones de la base de datos:
//+------------------------------------------------------------------+ //| 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; }
Aquí hemos añadido la clasificación de la lista de transacciones por hora descendente de entrada en el mercado. Si no lo hacemos así, la última operación se situará al final de la lista, por lo que aparecerá en la parte inferior de la tabla que se muestra en el panel. Y esto resulta muy incómodo. Si se clasifica en orden descendente, la última operación se situará al principio de la tabla, en la parte superior del panel, y las últimas transacciones resultarán visibles inmediatamente sin necesidad de desplazarse mucho por la lista de transacciones.
Luego escribiremos una función que rellenará una lista de todos los símbolos negociados desde la base de datos:
//+------------------------------------------------------------------+ //| 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; }
Para obtener una lista que solo contenga nombres de símbolos únicos y no repetidos, deberemos utilizar la palabra clave "DISTINCT" y obtener la lista por orden alfabético.
Por analogía, escribiremos una función que rellenará la lista de todos los números mágicos en orden ascendente desde la base de datos:
//+------------------------------------------------------------------+ //| 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; }
Después crearemos una función que obtendrá de la base de datos las estadísticas comerciales símbolo a símbolo y las almacenará en un array:
//+------------------------------------------------------------------------------+ //|Get symbol-based trading statistics from the database and save it to the array| //+------------------------------------------------------------------------------+ bool GetTradingStatsBySymbols(int database, string db_name, SSymbolStats &array[]) { int request=DatabasePrepare(database, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, " " r.long_trades as long_trades," " r.short_trades as short_trades " "FROM " " (" " SELECT SYMBOL," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades, " " sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, " " sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY SYMBOL ORDER BY net_profit DESC" " ) as r"); if(request==INVALID_HANDLE) { Print("DB: ", db_name, " request failed with code ", GetLastError()); DatabaseClose(database); return false; } //--- get entries from request results SSymbolStats symbol_stats; for(int i=0; DatabaseReadBind(request, symbol_stats) ; i++) { ArrayResize(array, i+1); array[i].name=symbol_stats.name; array[i].trades=symbol_stats.trades; array[i].long_trades=symbol_stats.long_trades; array[i].short_trades=symbol_stats.short_trades; array[i].gross_profit=symbol_stats.gross_profit; array[i].gross_loss=symbol_stats.gross_loss; array[i].total_commission=symbol_stats.total_commission; array[i].total_swap=symbol_stats.total_swap; array[i].total_profit=symbol_stats.total_profit; array[i].net_profit=symbol_stats.net_profit; array[i].win_trades=symbol_stats.win_trades; array[i].loss_trades=symbol_stats.loss_trades; array[i].expected_payoff=symbol_stats.expected_payoff; array[i].win_percent=symbol_stats.win_percent; array[i].loss_percent=symbol_stats.loss_percent; array[i].average_profit=symbol_stats.average_profit; array[i].average_loss=symbol_stats.average_loss; array[i].profit_factor=symbol_stats.profit_factor; } //--- remove the query after use DatabaseFinalize(request); return true; }
Aquí añadiremos filas para el registro de posiciones largas y cortas, y obtendremos una lista clasificada según el beneficio neto decreciente, de modo que los símbolos para los que hemos obtenido un gran beneficio estarán al principio de la tabla.
Del mismo modo, escribiremos una función para obtener de la base de datos y guardar en un array las estadísticas de trading según el número mágico:
//+------------------------------------------------------------------------------------+ //|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; }
Por último, escribiremos una función similar que obtendrá las estadísticas comerciales de una cuenta de la base de datos y las almacenará en un array:
//+---------------------------------------------------------------------------------+ //| 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; }
Eso es todo, ya hemos preparado las cosas básicas para crear el proyecto: hemos seleccionado el panel y creado funciones para trabajar con la base de datos según la información de la documentación. Ahora solo necesitaremos implementar la lógica de interacción del panel y sus tablas con la base de datos mediante funciones escritas. Es probable que las listas colocadas en las tablas del panel sean bastante extensas, y que el tamaño de las tablas supere el del panel. En este caso, deberemos desplazar las tablas vertical y horizontalmente. Implementaremos tal funcionalidad directamente en el indicador creado: las tablas se desplazarán verticalmente girando la rueda del ratón, y horizontalmente, usando la rueda del ratón con la tecla Shift pulsada.
Las estadísticas del símbolo o número mágico seleccionado se mostrarán al hacer clic en la línea de estadísticas del símbolo o número mágico deseado. Para ello, monitorearemos el posicionamiento del cursor sobre las filas de la tabla y clicaremos en una fila. Lo correcto será implementar dicha funcionalidad en la clase panel, para que pueda ser usada en otros proyectos. Pero aquí mostraremos cómo podemos hacer lo mismo sin modificar las clases del panel.
Montamos el proyecto - panel informativo
En la carpeta creada anteriormente \MQL5\Indicators\StatisticsBy\ crearemos un nuevo archivo de indicador con el nombre StatisticsBy.mq5.
Luego conectaremos los archivos de clases de tablas y paneles y el archivo de funciones para trabajar con la base de datos, y especificaremos que el indicador no tiene búferes a dibujar:
//+------------------------------------------------------------------+ //| 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"
A continuación, escribiremos las macrosustituciones, un array de ubicaciones de las columnas de las tablas estadísticas, los parámetros de entrada y las variables globales:
#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
Para especificar el número de columnas de las tablas de estadísticas y especificar el número y la disposición de los datos en la tabla, deberemos utilizar el array en el que se encuentran las constantes de los nombres de los encabezados de la tabla y, en consecuencia, de los datos bajo estos encabezados. Si tenemos que cambiar el orden de los diferentes datos de la tabla, bastará con cambiar el orden de su declaración en este array y recompilar el indicador. También podremos eliminar datos innecesarios comentándolos en este array, o añadir nuevos datos. Pero al añadir datos nuevos, tendremos que agregarlos a las funciones de la base de datos y a otras funciones que calculan y muestran los datos de la tabla.
Ahora analizaremos el manejador OnInit() del indicador, donde se crean la base de datos y el panel con su contenido gráfico:
//+------------------------------------------------------------------+ //| 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); }
De hecho, aquí primero comprobaremos el tipo de registro de posiciones, y si es de compensación, el indicador finalizará su funcionamiento, porque para este registro de posiciones resulta imposible crear una tabla de operaciones de una manera simple, solo por transacciones de entrada-salida.
A continuación, crearemos una base de datos en el directorio de datos del terminal (TERMINAL_DATA_PATH + \MQL5\Files\) en la carpeta con el nombre del programa en el subdirectorio Database (\StatisticsBy\Database\). Una vez creada con éxito la base de datos, crearemos un panel y lo rellenaremos con contenido: botones de control y paneles para mostrar tablas:
Lo interesante es que aquí, en lugar de botones, usaremos paneles secundarios adjuntos a la ventana principal minimizada desde los que solo será visible el título del panel. Este tiene sus propios manejadores para interactuar con el cursor del ratón, y de esta forma, hemos creado botones de paneles ordinarios que interactuarán con el usuario y enviarán eventos de interacción del ratón al programa principal.
En el manejador OnDeinit(), cerraremos la base de datos y el panel:
//+------------------------------------------------------------------+ //| 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(); } }
Dejaremos vacío el manejador OnCalculate() del indicador (el indicador no calcula nada):
//+------------------------------------------------------------------+ //| 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); }
El trabajo completo de interacción interactiva entre el panel y el usuario se realizará en el manejador de eventos del indicador.
Vamos a analizar el manejador de eventos OnChartEvent() en su totalidad. Su código se comentará con detalle. Si estudia detenidamente los comentarios del manejador de eventos, la lógica completa de interacción del panel con el usuario quedará clara:
//+------------------------------------------------------------------+ //| 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; } } }
Ahora veamos el resto de las funciones que se llaman desde el manejador de eventos. El código de cada función está detalladamente comentado, por lo que no debería dar lugar a malentendidos.
Función que retorna el índice de la fila de la tabla según las coordenadas del cursor:
//+------------------------------------------------------------------+ //| 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; }
La función cumple dos tareas a la vez: (1) devolverá el número de fila de la tabla sobre la que está situado el cursor del ratón, y (2) resaltará dicha fila con un color de fondo.
Función de manejador para desplazar la rueda del ratón dentro de la tabla del panel izquierdo:
//+------------------------------------------------------------------+ //| 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; }
Al desplazar la rueda del ratón, si el cursor se encuentra sobre una tabla del panel, la tabla también deberá desplazarse, en caso de que su tamaño supere el tamaño del panel. Eso es lo que hace este manejador: desplaza la tabla según las coordenadas iniciales indicadas. Además, la fila bajo el cursor se resaltará usando la función TableSelectRowByMouse(), y se retornará el índice de la fila bajo el cursor devuelto por esta función. El panel izquierdo mostrará listas de símbolos y números mágicos de tamaño reducido, por lo que el desplazamiento se simplificará: desplazaremos directamente la tabla a las coordenadas calculadas. Para el panel derecho, todo será un poco más complicado.
Función de manejador del desplazamiento del cursor del ratón dentro de la tabla del panel izquierdo:
//+------------------------------------------------------------------+ //| 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; }
También en este caso, como en la función anterior, buscaremos y seleccionaremos la línea situada bajo el cursor. Pero la tabla no se desplazará, claro.
Función de manejador para desplazar la rueda del ratón dentro de la tabla del panel derecho:
//+------------------------------------------------------------------+ //| 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; }
Aquí se procesarán tres tablas y los encabezados de las mismas en una sola función. Todo dependerá del tipo de tabla que se pase a la función. Al desplazar tablas grandes, no solo habrá que desplazarlas en altura, sino también en anchura. La bandera shift_flag es responsable de la anchura de desplazamiento: supone una indicación de que la tecla Shift se mantiene pulsada cuando se gira la rueda del ratón. Al desplazarse por la tabla propiamente dicha, el encabezado situado en el otro panel se desplazará al mismo tiempo.
Función de manejador del desplazamiento del cursor del ratón dentro de la tabla del panel derecho:
//+------------------------------------------------------------------+ //| 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; }
En general, aquí (y en las funciones comentadas anteriormente) solo necesitaremos encontrar el número de fila de la tabla sobre la que se encuentra el cursor. Todo lo demás son "lindezas" para resaltar la línea bajo el cursor que acabarán consumiendo más recursos de la CPU. Y es que después de todo, deberemos redibujar constantemente toda la parte visible de la tabla, y cuanto más grande sea el panel y la tabla en él, más espacio tendremos que redibujar. Obviamente, el espacio físicamente dibujable aquí (y más allá) estará limitado por el tamaño del panel, pero seguirá sin ser óptimo. Si implementáramos selecciones de filas así en la clase de panel, sería diferente: redibujaríamos solo las filas de la tabla adyacentes al cursor, recordando el color de fondo antes y después de resaltar, y luego restaurándolo. Pero aquí, como se trata solo e un ejemplo, implementaremos todo directamente en las funciones del programa, y no nos complicaremos demasiado.
Función de manejador para el clic de ratón dentro de una tabla en un panel:
//+------------------------------------------------------------------+ //| 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; }
Al clicar en una fila de la tabla, necesitaremos encontrar y devolver el número de fila dentro de la cual se ha hecho el clic para poder seguir procesándolo. Cosa que, por cierto, ya hemos mostrado en el manejador de eventos personalizado en OnChartEvent().
Función que rellena la tabla con nombres de símbolos:
//+------------------------------------------------------------------+ //| 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); } }
Aquí, además de no dibujar áreas de la tabla que se extiendan más allá del panel, también limitaremos el inicio del ciclo a la fila de la tabla que resulta visible desde arriba primero. Me explico: al desplazarnos por la tabla, la primera fila puede salirse del panel superior. Y para no crear un "ciclo" y no intentar dibujar filas de la tabla fuera del panel que de todas formas no se dibujarán, necesitaremos calcular el índice de la primera fila de tabla visible desde arriba e iniciar el ciclo a partir de ella. Si las tablas son suficientemente grandes, este enfoque ofrecerá resultados tangibles, eliminando fuertes ralentizaciones al desplazarnos por una tabla con cientos de filas.
Función que rellena la tabla con los valores de los números mágicos:
//+------------------------------------------------------------------+ //| 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); } }
Función que rellena la tabla de encabezados de transacciones:
//+------------------------------------------------------------------+ //| 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); } }
Función que rellena la tabla de transacciones:
//+------------------------------------------------------------------+ //| 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); } } }
Las dos funciones dibujan esencialmente una tabla. La primera función dibujará un encabezado de tabla con columnas tituladas según el índice de la columna, mientras que la segunda función dibujará una tabla debajo del encabezado con los valores correspondientes a los encabezados de las columnas. En general, las funciones trabajarán en pareja.
Función que rellena la tabla de encabezados de las estadísticas comerciales:
//+------------------------------------------------------------------+ //| 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); } }
Al imprimir una tabla estadística según el símbolo o el número mágico, deberemos dibujar el encabezado de la tabla estadística. Pero tenemos dos tablas con estadísticas, una para los símbolos y otra para los números mágicos. Los encabezados de estas tablas solo difieren en la primera columna, y todos los demás son iguales. Aquí se comprobará el identificador de la tabla y, en función de él, se firmará el encabezado de la primera columna con un símbolo o un número mágico.
Función que rellena la tabla de estadísticas comerciales por símbolos:
//+------------------------------------------------------------------+ //| 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); } } }
Función que rellena la tabla de estadísticas comerciales por números mágicos:
//+------------------------------------------------------------------+ //| 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); } } }
Dos funciones del mismo tipo que realizarán el rellenado de las tablas de estadísticas según el símbolo y el número mágico.
Función que devuelve los datos de una estructura según el tipo de encabezado:
//+------------------------------------------------------------------+ //| 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; } }
Se pasarán a la función el tipo de tabla estadística (símbolo/número mágico/cuenta), el tipo de datos (valor de encabezado de la columna) y el índice de los datos en el array de estructuras. Dependiendo de los datos pasados a la función, se retornará el valor del array de estructuras correspondiente. Por simplicidad, siempre se devuelve un valor de tipo double, aunque en la estructura sea un valor entero. En la siguiente función, este valor se devolverá como una línea con el número deseado de decimales.
Función que devuelve los datos de la estructura según el tipo de encabezado en forma de línea:
//+------------------------------------------------------------------+ //| 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)); }
Las funciones se utilizan para enviar los valores de las celdas de las tablas estadísticas a las tablas del panel.
Función que retorna el índice de un símbolo en el array de estadísticas de símbolos:
//+------------------------------------------------------------------+ //| 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; }
Función que devuelve el índice de un símbolo en el array de estadísticas según el número mágico:
//+------------------------------------------------------------------------+ //| 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; }
Ambas funciones devuelven el índice del símbolo o número mágico buscado en el correspondiente array de estadísticas de símbolos o números mágicos.
Función que muestra las estadísticas resumidas de un símbolo, número mágico o cuenta seleccionados:
//+------------------------------------------------------------------+ //| 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; }
La función está diseñada para dibujar una tabla resumen de estadísticas según el símbolo o número mágico seleccionados, o según la cuenta al completo. A la función se transmitirá el tipo de la tabla estadística y el nombre del símbolo, o el valor de la línea del número mágico o el número de cuenta. El índice del símbolo, número mágico o cuenta en el correspondiente array de estructuras de datos estadísticos se determinará a partir del texto. Obtendremos todos los datos estadísticos de la estructura requerida según el índice obtenido en la estructura, y luego los clasificaremos en la tabla dibujada según las coordenadas de la ubicación de sus celdas. En este caso, los desplazamientos horizontales del texto visualizado se calcularán de tal forma que el encabezado de los datos quede ligado al borde izquierdo de la celda de la tabla, mientras que el texto del valor de estos datos queda ligado al borde derecho de su celda de la tabla. Todos los datos se mostrarán en cuatro columnas, de forma que visualmente se agrupen en el panel en dos columnas como "encabezado -- valor".
Vamos a compilar el indicador y a comprobar por nosotros mismos lo que tenemos:
Bien, vemos que toda la funcionalidad indicada funciona como esperábamos. Sí, percibimos un leve "parpadeo" del texto en las tablas al mover el cursor y desplazarnos por ellas. Pero este es el resultado de un esquema de redibujado subóptimo: toda la parte visible de la tabla se redibuja constantemente. Podemos evitar esto usando una lógica más compleja para manejar las filas de la tabla bajo el cursor, lo cual queda fuera del alcance de este artículo.
Las tablas pueden desplazarse verticalmente girando la rueda del ratón, y horizontalmente girando la rueda con la tecla Shift pulsada. Si observamos detenidamente el vídeo, notaremos que cuando se muestran las estadísticas de número mágico con un valor 0, el número de posiciones largas y cortas se muestra como cero. Esto es resultado de un error al definir la transacción en una consulta de la base de datos. La tabla de transacciones se crea a partir de la tabla de transacciones. Si una posición ha sido abierta por un asesor experto (en este caso con el número mágico 600) y cerrada manualmente, entonces la operación de apertura tendrá un número mágico, mientras que la operación de cierre tendrá un cero. Puede comprobarlo al consultar la historia de operaciones:
Aquí se toman las transacciones de cierre para determinar la operación, y esta tiene un número mágico cero. Y en el número mágico cero es imposible encontrar la operación de apertura: no existe. En consecuencia, no se encontrarán posiciones ni largas ni cortas para el número mágico cero. Por lo tanto, al crear una tabla de transacciones, valdrá la pena considerar la posibilidad de que una posición pueda ser abierta por un asesor experto y cerrada manualmente y viceversa. Si se tiene esto en cuenta, no debería haber más errores de este tipo.
Conclusión
En el artículo de hoy, hemos planteado la tarea de crear un panel informativo que muestre estadísticas comerciales, y hemos tenido que resolverla utilizando ejemplos de artículos y la documentación de este recurso. Y, como podemos ver, incluso sin saber nada, siempre resulta fácil encontrar respuestas a nuestras preguntas utilizando la enorme base de conocimientos que ofrece el recurso, y elaborar un producto bastante viable.
Estudie la información que ofrece el sitio, lea los artículos y la documentación, contacte con colegas más experimentados, perfeccione los ejemplos encontrados para sus tareas, ¡y seguro que todo saldrá bien!
Todos los archivos de las clases y funciones, así como del indicador que hemos analizado hoy se adjuntan al artículo. También se adjunta un archivo que se puede descomprimir en el directorio de datos del terminal; todos los archivos necesarios se colocarán en la carpeta\MQL5\Indicators\StatisticsBy y pueden ser inmediatamente compilados y ejecutados el archivo del indicador.
Traducción del ruso hecha por MetaQuotes Ltd.
Artículo original: https://www.mql5.com/ru/articles/16233
Archivos adjuntos |
Dashboard.mqh
(256.99 KB)
SQLiteFunc.mqh
(64.62 KB)
StatisticsBy.mq5
(164.58 KB)
MQL5.zip
(45.47 KB)
Advertencia: todos los derechos de estos materiales pertenecen a MetaQuotes Ltd. Queda totalmente prohibido el copiado total o parcial.
Este artículo ha sido escrito por un usuario del sitio web y refleja su punto de vista personal. MetaQuotes Ltd. no se responsabiliza de la exactitud de la información ofrecida, ni de las posibles consecuencias del uso de las soluciones, estrategias o recomendaciones descritas.
Características del Wizard MQL5 que debe conocer (Parte 41): Aprendizaje por refuerzo con redes neuronales (Deep-Q-Networks, DQN)
Deep-Q-Network es un algoritmo de aprendizaje de refuerzo que involucra redes neuronales para proyectar el próximo valor Q y la acción ideal durante el proceso de entrenamiento de un módulo de aprendizaje automático. Ya hemos considerado un algoritmo de aprendizaje de refuerzo alternativo, Q-Learning. Por lo tanto, este artículo presenta otro ejemplo de cómo un MLP entrenado con aprendizaje de refuerzo se puede utilizar dentro de una clase de señal personalizada.
Aprendizaje automático y Data Science (Parte 30): La pareja ideal para predecir el mercado bursátil: redes neuronales convolucionales (CNN) y recurrentes (RNN)
En este artículo exploramos la integración dinámica de redes neuronales convolucionales (CNN) y redes neuronales recurrentes (RNN) en la predicción bursátil. Aprovechando la capacidad de las CNN para extraer patrones y la destreza de las RNN para manejar datos secuenciales. Veamos cómo esta potente combinación puede mejorar la precisión y la eficacia de los algoritmos de negociación.
Redes neuronales en el trading: Modelos de difusión direccional (DDM)
Hoy proponemos al lector familiarizarse con los modelos de difusión direccional que explotan el ruido anisotrópico y direccional dependiente de los datos durante la difusión directa para capturar representaciones gráficas significativas.
Métodos de optimización de la biblioteca ALGLIB (Parte II)
En este artículo seguiremos analizando los métodos restantes de optimización de la biblioteca ALGLIB, prestando especial atención a su comprobación con funciones multivariantes complejas. Esto nos permitirá no solo evaluar el rendimiento de cada algoritmo, sino también identificar sus puntos fuertes y débiles en diferentes condiciones.
Está perdiendo oportunidades comerciales:
- Aplicaciones de trading gratuitas
- 8 000+ señales para copiar
- Noticias económicas para analizar los mercados financieros
Registro
Entrada
Usted acepta la política del sitio web y las condiciones de uso
Si no tiene cuenta de usuario, regístrese
Faltan herramientas que permitan trabajar con un amplio historial de operaciones.
Por desgracia, este conjunto de herramientas simplemente se cuelga al solicitar el historial, como muchos otros.
Tarda cinco minutos en obtener el historial. Entonces es imposible hacer nada con la ventana - carga completa de la CPU.
Faltan herramientas para gestionar un amplio historial comercial.
Por desgracia, este conjunto de herramientas se cuelga al solicitar el historial, como muchos otros.
Cinco minutos para obtener el historial. Entonces es imposible hacer nada con la ventana - carga completa de la CPU.
¿Puedo tener acceso como inversor a la cuenta?
Lamentablemente, no existe tal posibilidad. Pero puede crear algo así usted mismo: en una cuenta demo, utilice un script para abrir/cerrar el número requerido de posiciones por diferentes símbolos/magias en una hora utilizando OrderSend asíncrono.
No quiere trabajar en la Bolsa de Moscú
No quiere trabajar en la Bolsa de Moscú
Naturalmente. Todo lo relacionado con la posición, excepto la posición total, es inútil en la compensación si más de un robot trabaja en un símbolo (o robot más operación manual).