Example of searching for a trading strategy using SQLite

Let's try to use SQLite to solve practical problems. We will import structures into the MqlRates database with the history of quotes and analyze them in order to identify patterns and search for potential trading strategies. Of course, any chosen logic can also be implemented in MQL5, but SQL allows you to do it in a different way, in many cases more efficiently and using many interesting built-in SQL functions. The subject of the book, aimed at learning MQL5, does not allow going deep into this technology, but we mention it as worthy of the attention of an algorithmic trader.

The script for converting quotes history into a database format is called DBquotesImport.mq5. In the input parameters, you can set the prefix of the database name and the size of the transaction (the number of records in one transaction).

To add MqlRates structures to the database using our ORM layer, the script defines an auxiliary MqlRatesDB structure which provides the rules for binding structure fields to base columns. Since our script only writes data to the database and does not read it from there, it does not need to be bound using the DatabaseReadBind function, which would impose a restriction on the "simplicity" of the structure. The absence of a constraint makes it possible to derive the MqlRatesDB structure from MqlRates (and do not repeat the description of the fields).

The database name is formed from the prefix Database, name, and timeframe of the current chart on which the script is running. A single table "MqlRatesDB" is created in the database with the field configuration specified by the DB_FIELD macros. Please note that the primary key will not be generated by the database, but is taken directly from the bars, from the time field (bar opening time).

Next, using packages of TransactionSize bars, we request bars from the history and add them to the table. This is a job of the helper function ReadChunk, called in a loop as long as there is data (the function returns true) or the user won't stop the script manually. The function code is shown below.

Upon completion of the process, we ask the database for the number of generated records in the table and output it to the log.

The ReadChunk function looks as follows.

It calls the built-in CopyRates function through which the rates bars array is filled. Then the bars are transferred to the ratesDB array so that using just one statement db.insert(ratesDB) we could write information to the database (we have formalized in MqlRatesDB how to do it correctly).

The presence of the DBTransaction object (with the automatic "commit" option enabled) inside the block means that all operations with the array are "overlaid" with a transaction. To indicate progress, during the processing of each block of bars, the label of the first bar is displayed in the log.

While the function CopyRates returns the data and their insertion into the database is successful, the loop in OnStart continues with the shift of the numbers of the copied bars deep into the history. When the end of the available history or the bar limit set in the terminal settings is reached, CopyRates will return error 4401 (HISTORY_NOT_FOUND) and the script will exit.

Let's run the script on the EURUSD, H1 chart. The log should show something like this.

We now have the base QuotesEURUSDH1.sqlite, on which you can experiment to test various trading hypotheses. You can open it in the MetaEditor to make sure that the data is transferred correctly.

Let's check one of the simplest strategies based on regularities in history. We will find the statistics of two consecutive bars in the same direction, broken down by intraday time and day of the week. If there is a tangible advantage for some combination of time and day of the week, it can be considered in the future as a signal to enter the market in the direction of the first bar.

First, let's design an SQL query that requests quotes for a certain period and calculates the price movement on each bar, that is, the difference between adjacent opening prices.

Since the time for bars is stored as a number of seconds (by the standards of datetime in MQL5 and, concurrently, the "Unix epoch" of SQL), it is desirable to convert their display to a string for easy reading, so let's start the SELECT query from the datetime field based on DATETIME function:

This field will not participate in the analysis and is given here only for the user. After that, the price is displayed for reference, so that we can check the calculation of price increments by debug printing.

Since we are going to, if necessary, select a certain period from the entire file, the condition will require the time field in "a pure form", and it should also be added to the request. In addition, according to the planned analysis of quotes, we will need to isolate from the bar label its intraday time, as well as the day of the week (their numbering corresponds to that adopted in MQL5, 0 is Sunday). Let's call the last two columns of the query intraday and day, respectively, and the TIME and STRFTIME functions are used to get them.

To calculate the price increment in SQL, you can use the LAG function. It returns the value of the specified column with an offset of the specified number of rows. For example, LAG(X, 1) means getting the X value in the previous entry, with the second parameter 1 that means the offset defaulting to 1, i.e. it can be omitted to get the equivalent entry LAG(X). To get the value of the next entry, call LAG(X,-1). In any case, when using LAG, an additional syntactic construction is required that specifies the sorting order of records, in the simplest case, in the form of OVER(ORDER BY column).

Thus, to get the price increment between the opening prices of two neighboring bars, we write:

This column is predictive because it looks into the future.

We can reveal that two bars formed in the same direction by multiplying increments by them: positive values indicate a consistent rise or fall:

This indicator is chosen as the simplest to use in calculation: for real trading systems, you can choose a more complex criterion.

To evaluate the profit generated by the system on the backtest, you need to multiply the direction of the previous bar (which acts as an indicator of future movement) by the price increment on the next bar. The direction is calculated in the column direction (using the SIGN function), for reference only. The profit estimate in the estimate column is the product of the previous movement direction and the increment of the next bar (delta): if the direction is preserved, we get a positive result (in points).

In expressions in an SQL command, you cannot use AS aliases defined in the same command. That is why we cannot determine estimate as delta * direction, and we have to repeat the calculation of the product explicitly. However, we recall that columns delta and direction are not needed for programmatic analysis and are added here only to visualize the table in front of the user.

At the end of the SQL command, we specify the table from which the selection is made, and the filtering conditions for the backtest date range: two parameters "from" and "to".

Optionally, we can add a constraint LIMIT?3 (and enter some small value, for example, 10) so that visual verification of the query results at first does not force you to look through tens of thousands of records.

You can check the operation of the SQL command using the DatabasePrint function, however, the function, unfortunately, does not allow you to work with prepared queries with parameters. Therefore, we will have to replace SQL parameter preparation '?n' with query string formatting using StringFormat and substitute parameter values there. Alternatively, it would be possible to completely avoid DatabasePrint and output the results to the log independently, line by line (through an array DBRow).

Thus, the final fragment of the request will turn into:

It should be noted that the datetime values in this query will be coming from MQL5 in the "machine" format, i.e., the number of seconds since the beginning of 1970. If we want to debug the same SQL query in the MetaEditor, then it is more convenient to write the date range condition using date literals (strings), as follows:

Again, we need to use the STRFTIME function here (the '%s' modifier in SQL sets the transfer of the specified date string to the "Unix epoch" label; the fact that '%s' resembles an MQL5 format string is just a coincidence).

Save the designed SQL query in a separate text file DBQuotesIntradayLag.sql and connect it as a resource to the test script of the same name, DBQuotesIntradayLag.mq5.

The first parameter of the script allows you to set a prefix in the name of the database, which should already exist after launching DBquotesImport.mq5 on the chart with the same symbol and timeframe. The subsequent inputs are for the date range and length limit of the debug printout to the log.

The table with quotes is known in advance, from the previous script.

In the OnStart function, we open the database and make sure that the quotes table is available.

Next, we substitute the parameters in the SQL query string. We pay attention not only to the substitution of SQL parameters '?n' for format sequences but also double the percent symbols '%' first because otherwise the function StringFormat will perceive them as its own commands, and will not miss them in SQL.

All these manipulations were required only to execute the request in the context of the DatabasePrint function. In the working version of the analytical script, we would read the results of the query and analyze them programmatically, bypassing formatting and calling DatabasePrint.

Finally, let's execute the SQL query and output the table with the results to the log.

Here is what we will see for 10 bars EURUSD,H1 at the beginning of 2022.

It is easy to make sure that the intraday time of the bar is correctly allocated, as well as the day of the week - 1, which corresponds to Monday. You can also check the delta increment. The product and estimate values are empty on the first row because they require the missing previous row to be calculated.

Let's complicate our SQL query by grouping records with the same time of day combinations (intraday) and day of the week (day), and calculating a certain target indicator that characterizes the success of trading for each of these combinations. Let's take as such an indicator the average cell size product divided by the standard deviation of the same products. The larger the average product of price increments of neighboring bars, the greater the expected profit, and the smaller the spread of these products, the more stable the forecast. The name of the indicator in the SQL query is objective.

In addition to the target indicator, we will also calculate the profit estimate (backtest_profit) and profit factor (backtest_PF). We will estimate profit as the sum of price increments (estimate) for all bars in the context of intraday time and day of the week (the size of the opening bar as a price increment is an analog of the future profit in points per one bar). The profit factor is traditionally the quotient of positive and negative increments.

The first SQL query has become nested, from which we now accumulate data with an external SQL query. Grouping by all combinations of time and day of the week provides an "extra" from GROUP BY intraday, day. In addition, we have added sorting by target indicator (ORDER BY objective DESC) so that the best options are at the top of the table.

In the nested query, we removed the LIMIT parameter, because the number of groups became acceptable, much less than the number of analyzed bars. So, for H1 we get 120 options (24 * 5).

The extended query is placed in the text file DBQuotesIntradayLagGroup.sql, which in turn is connected as a resource to the test script of the same name, DBQuotesIntradayLagGroup.mq5. Its source code differs little from the previous one, so we will immediately show the result of its launch for the default date range: from the beginning of 2015 to the beginning of 2021 (excluding 2021 and 2022).

Thus, the analysis tells us that the 16-hour H1 bar on Friday is the best candidate to continue the trend based on the previous bar. Next in preference is the Wednesday 20 o'clock bar. And so on.

However, it is desirable to check the found settings on the forward period.

To do this, we can execute the current SQL query not only on the "past" date range (in our test until 2021) but once more in the "future" (from the beginning of 2021). The results of both queries should be joined (JOIN) by our groups (intraday, day). Then, while maintaining the sorting by the target indicator, we will see in the adjacent columns the profit and profit factor for the same combinations of time and day of the week, and how much they sank.

Here's the final SQL query (abbreviated):

The full text of the request is provided in the file DBQuotesIntradayBackAndForward.sql. It is connected as a resource in the script DBQuotesIntradayBackAndForward.mq5.

By running the script with default settings, we get the following indicators (with abbreviations):

So, the trading system with the best trading schedules found continues to show profit in the "future" period, although not as large as on the backtest.

Of course, the considered example is only a particular case of a trading system. We could, for example, find combinations of the time and day of the week when a reversal strategy works on neighboring bars, or based on other principles altogether (analysis of ticks, calendar, portfolio of trading signals, etc.).

The bottom line is that the SQLite engine provides many convenient tools that would need to be implemented in MQL5 on your own. To tell the truth, learning SQL takes time. The platform allows you to choose the optimal combination of two technologies for efficient programming.