Betriebsblind Finde Fehler nicht

Aleksi-Trader 2023.03.20 09:16

Hallo Leute, ich habe ein teil in mein EA der SQL Lite code enthält, ich bekomme folgende fehlermeldung:

2023.03.20 04:27:46.563 TEST KENNZ (DE40,M1) database error, no such column: r.average_profit

kann mir jemand helfen den Fehler zu finden?

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
   double            drawdown;               // maximum drawdown for the symbol
   double            max_drawdown;           // maximum drawdown percentage for the symbol
   double            sharpe_ratio;           // Sharpe ratio for the symbol
   double            sortino_ratio;          // Sortino ratio for the symbol
   double            profit_to_loss_ratio;   // profit to loss ratio for the symbol
   double            net_profit_percentage;  // net profit percentage for the symbol
  };

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.loss_trades != 0 then r.average_profit/abs(r.average_loss) else null end) as CRV,"
                               "(case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor,"
                               "(case when r.net_profit != 0.0 then max(r.net_profit - cumulative_max_net_profit) / cumulative_max_net_profit else 0 end) as max_relative_drawdown,"
                               "(case when r.net_profit != 0.0 then(r.net_profit - cumulative_max_net_profit) / cumulative_max_net_profit else  0 end) as relative_drawdown,"
                               "(case when r.net_profit != 0.0 then(r.net_profit - cumulative_max_net_profit) else 0 end) as absolute_drawdown,"
                               "r.net_profit / stddev_pop(r.net_profit) as sharpe_ratio,"
                               "r.net_profit / stddev_samp(case when r.profit < 0 then r.profit end) as sortino_ratio "
                               "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(profittax+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 query results
   for(i; 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;
      stats[i].drawdown=symbol_stats.drawdown;
      stats[i].max_drawdown=symbol_stats.max_drawdown;
      stats[i].sharpe_ratio=symbol_stats.sharpe_ratio;
      stats[i].sortino_ratio=symbol_stats.sortino_ratio;
      stats[i].profit_to_loss_ratio=symbol_stats.profit_to_loss_ratio;
      stats[i].net_profit_percentage=symbol_stats.net_profit_percentage;

     }
   ArrayResize(stats, i);
   Print("Trade statistics by Symbol");
   ArrayPrint(stats);
   Print("");
//--- delete the query
   DatabaseFinalize(request);

  }
//+------------------------------------------------------------------+
//+------------------------------------------------------------------+
//| Create the DEALS table                                           |
//+------------------------------------------------------------------+
bool CreateTableDeals(int database)
  {
//--- if the DEALS table already exists, delete it
   if(!DeleteTable(database, "DEALS"))
     {
      return(false);
     }
//--- check if the table exists
   if(!DatabaseTableExists(database, "DEALS"))
      //--- create the table
      if(!DatabaseExecute(database, "CREATE TABLE DEALS("
                          "ID          INT KEY NOT NULL,"
                          "ORDER_ID    INT     NOT NULL,"
                          "POSITION_ID INT     NOT NULL,"
                          "TIME        INT     NOT NULL,"
                          "TYPE        INT     NOT NULL,"
                          "ENTRY       INT     NOT NULL,"
                          "SYMBOL      CHAR(10),"
                          "VOLUME      REAL,"
                          "PRICE       REAL,"
                          "PROFIT      REAL,"
                          "PROFITTAX   REAL,"
                          "TAX         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;        // the ticket of the order by which the deal was executed
   long     position_ticket;     // ID of the position the deal belongs to
   datetime time;                // deal execution time
   long     type ;               // deal type
   long     entry ;              // deal direction
   string   symbol;              // the symbol the deal was executed for
   double   volume;              // operation volume
   double   price;               // price
   double   profit;              // financial result
   double   profittax;              // financial result with tax
   double   tax=26;              // tax
   double   taxres;              // financial result tax
   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);
      profittax=      NormalizeDouble(HistoryDealGetDouble(deal_ticket, DEAL_PROFIT)-MathAbs((HistoryDealGetDouble(deal_ticket, DEAL_PROFIT)/100)*tax),2);
      taxres=         NormalizeDouble(MathAbs((HistoryDealGetDouble(deal_ticket, DEAL_PROFIT)/100)*tax),2);
      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,PROFITTAX,TAX,SWAP,COMMISSION,MAGIC,REASON,HOUR)"
                                       "VALUES (%d,%d,%d,%d,%d,%d,'%s',%G,%G,%G,%G,%G,%G,%G,%d,%d,%d)",
                                       deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, profittax,taxres, 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);
  }

Dominik Egert 2023.03.20 09:32 #1

Du benutzt den Wert in der SELECT für CRV, zu diesem Zeitpunkt gibt es diesen aber noch nicht.

Ich empfehle dir einen VIEW zu bauen, der das in einer Subquery abbildet.

Die Funktion DatabaseReadBind kannst du auch so schreiben:

DatabaseReadBind(request, stats[i])

Diskussion Servicedesk: Faulheit, Autismus oder Fragen von Neueinsteigern zu

Aleksi-Trader 2023.03.20 18:42 #2

Dominik Christian Egert #:

Du benutzt den Wert in der SELECT für CRV, zu diesem Zeitpunkt gibt es diesen aber noch nicht.

Ich empfehle dir einen VIEW zu bauen, der das in einer Subquery abbildet.

Die Funktion DatabaseReadBind kannst du auch so schreiben:

Wie meist das ?

das is dich das selbe spiel wie für

expected_payoff

Dominik Egert 2023.03.20 22:07 #3

Aleksi-Trader #:
Wie meist das ?

das is dich das selbe spiel wie für

 "(case when r.loss_trades != 0 then r.average_profit/abs(r.average_loss) else null end) as CRV,"

Dominik Egert 2023.03.20 22:12 #4

Diese Zeile führt zu deinem Fehler. r.* hat keinen solchen Wert.

Außerdem definierst du den average-Wert ja eine Zeile drüber, deswegen ist dieser zu diesem Zeitpunkt nicht verfügbar. Tausche diese Werte mit der average-Definition aus...

 "(case when r.loss_trades != 0 then (r.gross_profit/r.win_trades)/abs(r.gross_loss/r.loss_trades) else null end) as CRV,"

Neuer Kommentar