MQL5 Cookbook — Macroeconomic events database
Denis Kirichenko | 15 March, 2023
Introduction
This article will focus on how to group and manage data that describes macroeconomic calendar events.
In the modern world, information flows are all-pervasive. So, one has to deal with big data when analyzing events. Although, to a greater extent, the article covers issues related not to content, but to form, nevertheless, it seems that the correct organization and structuring of data contributes a lot to the fact that these data will turn into information.
We will solve these tasks by means of SQLite. The developer has added support for handling SQLite directly from MQL5 in build 2265 (December 6, 2019). Before that, I had to use various connectors, for example, as described in the article SQL and MQL5: Working with SQLite database.
1. Documentation and additional materials
Let's skim through Documentation, namely the sections about database handling. The developer provides 26 native features:
- DatabaseOpen();
- DatabaseClose();
- DatabaseImport();
- DatabaseExport();
- DatabasePrint();
- DatabaseTableExists();
- DatabaseExecute();
- DatabasePrepare();
- DatabaseReset();
- DatabaseBind();
- DatabaseBindArray();
- DatabaseRead();
- DatabaseReadBind();
- DatabaseFinalize();
- DatabaseTransactionBegin();
- DatabaseTransactionCommit();
- DatabaseTransactionRollback();
- DatabaseColumnsCount();
- DatabaseColumnName();
- DatabaseColumnType();
- DatabaseColumnSize();
- DatabaseColumnText();
- DatabaseColumnInteger();
- DatabaseColumnLong();
- DatabaseColumnDouble();
- DatabaseColumnBlob().
There are also blocks of statistical and mathematical functions that were added recently. The article SQLite: Native handling of SQL databases in MQL5 can serve as a starting point for studying this functionality.
2. CDatabase class
Let's create the CDatabase class for the convenience of handling databases. First, describe the class composition. Then check its operation using examples.
The data members of the CDatabase class include the following:
- m_name - database file name (with the extension);
- m_handle - database handle;
- m_flags - combination of flags;
- m_table_names – table names;
- m_curr_table_name – current table name;
- m_sql_request_ha – last SQL query handle;
- m_sql_request – last SQL query.
As for the methods, I would divide them into several groups:
- Methods that include native functions for handling databases (API MQL5 functions);
- Methods for handling tables;
- Methods for handling requests;
- Methods for working with views;
- Methods for obtaining data member values (get methods).
SQLite features multiple request forms that can be both simple and complex. My objective is not to create a custom method in the CDatabase class for each such form. If the class does not have a method for a particular request, then the request can be formed using the universal CDatabase::Select() method.
Now let's look at the examples of using CDatabase class features.
2.1 Creating a database
Let's create our first calendar database using the 1_create_calendar_db.mq5 script. The script is to have only a few lines of code.
//--- include #include "..\CDatabase.mqh" //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { CDatabase db_obj; string file_name="Databases\\test_calendar_db.sqlite"; uint flags=DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE; if(!db_obj.Open(file_name, flags)) ::PrintFormat("Failed to create a calendar database \"%s\"!", file_name); db_obj.Close(); } //+------------------------------------------------------------------+
After running the script, we will see that the database file test_calendar_db.sqlite has appeared in %MQL5\Files\Databases (Fig. 1).
Fig. 1. test_calendar_db.sqlite database file
If we open this file in the code editor, we will see that the database is empty (Fig. 2).
Fig. 2. test_calendar_db database
2.2 Creating a table
Let's try to fill in the database. To do this, create the COUNTRIES table, in which we will enter a list of countries whose calendar events will subsequently be processed by our queries. The 2_create_countries_table.mq5 script will do the job.
//--- include #include "..\CDatabase.mqh" //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { //--- open a database CDatabase db_obj; string file_name="Databases\\test_calendar_db.sqlite"; uint flags=DATABASE_OPEN_READWRITE; if(!db_obj.Open(file_name, flags)) { ::PrintFormat("Failed to open a calendar database \"%s\"!", file_name); db_obj.Close(); return; } //--- create a table string table_name="COUNTRIES"; string params[]= { "COUNTRY_ID UNSIGNED BIG INT PRIMARY KEY NOT NULL,", // 1) country ID "NAME TEXT," // 2) country name "CODE TEXT," // 3) country code "CONTINENT TEXT," // 4) country continent "CURRENCY TEXT," // 5) currency code "CURRENCY_SYMBOL TEXT," // 6) currency symbol "URL_NAME TEXT" // 7) country URL }; if(!db_obj.CreateTable(table_name, params)) { ::PrintFormat("Failed to create a table \"%s\"!", table_name); db_obj.Close(); return; } db_obj.Close(); } //+------------------------------------------------------------------+
After running the script, we can find that the COUNTRIES table has appeared in the database (Fig. 3).
Fig. 3. Empty COUNTRIES table
2.3 Filling in the table
Let's populate a new table with data. To do this, use the CiCalendarInfo class features. Find more details about the class in the article MQL5 Cookbook – Economic Calendar. The task is performed by the 3_fill_in_countries_table.mq5 script.
//--- include #include "..\CalendarInfo.mqh" #include "..\CDatabase.mqh" //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { //--- open a database CDatabase db_obj; string file_name="Databases\\test_calendar_db.sqlite"; uint flags=DATABASE_OPEN_READWRITE; if(!db_obj.Open(file_name, flags)) { db_obj.Close(); return; } //--- open a table string table_name="COUNTRIES"; if(db_obj.SelectTable(table_name)) if(db_obj.EmptyTable()) { db_obj.FinalizeSqlRequest(); string col_names[]= { "COUNTRY_ID", "NAME", "CODE", "CONTINENT", "CURRENCY", "CURRENCY_SYMBOL", "URL_NAME" }; //--- fill in the table CiCalendarInfo calendar_info; if(calendar_info.Init()) { MqlCalendarCountry calendar_countries[]; if(calendar_info.GetCountries(calendar_countries)) { if(db_obj.TransactionBegin()) for(int c_idx=0; c_idx<::ArraySize(calendar_countries); c_idx++) { MqlCalendarCountry curr_country=calendar_countries[c_idx]; string col_vals[]; ::ArrayResize(col_vals, 7); col_vals[0]=::StringFormat("%I64u", curr_country.id); col_vals[1]=::StringFormat("'%s'", curr_country.name); col_vals[2]=::StringFormat("'%s'", curr_country.code); col_vals[3]="NULL"; SCountryByContinent curr_country_continent_data; if(curr_country_continent_data.Init(curr_country.code)) col_vals[3]=::StringFormat("'%s'", curr_country_continent_data.ContinentDescription()); col_vals[4]=::StringFormat("'%s'", curr_country.currency); col_vals[5]=::StringFormat("'%s'", curr_country.currency_symbol); col_vals[6]=::StringFormat("'%s'", curr_country.url_name); if(!db_obj.InsertSingleRow(col_names, col_vals)) { db_obj.TransactionRollback(); db_obj.Close(); return; } db_obj.FinalizeSqlRequest(); } if(!db_obj.TransactionCommit()) ::PrintFormat("Failed to complete transaction execution, error %d", ::GetLastError()); } //--- print if(db_obj.PrintTable()<0) ::PrintFormat("Failed to print the table \"%s\", error %d", table_name, ::GetLastError()); } } db_obj.Close(); } //+------------------------------------------------------------------+
Print out the COUNTRIES table data in the log.
3_fill_in_countries_table (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY CURRENCY_SYMBOL URL_NAME 3_fill_in_countries_table (EURUSD,H1) --+----------------------------------------------------------------------------------------- 3_fill_in_countries_table (EURUSD,H1) 1| 554 New Zealand NZ Australia/Oceania NZD $ new-zealand 3_fill_in_countries_table (EURUSD,H1) 2| 999 European Union EU Europe EUR € european-union 3_fill_in_countries_table (EURUSD,H1) 3| 392 Japan JP Asia JPY ¥ japan 3_fill_in_countries_table (EURUSD,H1) 4| 124 Canada CA North America CAD $ canada 3_fill_in_countries_table (EURUSD,H1) 5| 36 Australia AU Australia/Oceania AUD $ australia 3_fill_in_countries_table (EURUSD,H1) 6| 156 China CN Asia CNY ¥ china 3_fill_in_countries_table (EURUSD,H1) 7| 380 Italy IT Europe EUR € italy 3_fill_in_countries_table (EURUSD,H1) 8| 702 Singapore SG Asia SGD R$ singapore 3_fill_in_countries_table (EURUSD,H1) 9| 276 Germany DE Europe EUR € germany 3_fill_in_countries_table (EURUSD,H1) 10| 250 France FR Europe EUR € france 3_fill_in_countries_table (EURUSD,H1) 11| 76 Brazil BR South America BRL R$ brazil 3_fill_in_countries_table (EURUSD,H1) 12| 484 Mexico MX North America MXN Mex$ mexico 3_fill_in_countries_table (EURUSD,H1) 13| 710 South Africa ZA Africa ZAR R south-africa 3_fill_in_countries_table (EURUSD,H1) 14| 344 Hong Kong HK Asia HKD HK$ hong-kong 3_fill_in_countries_table (EURUSD,H1) 15| 356 India IN Asia INR ₹ india 3_fill_in_countries_table (EURUSD,H1) 16| 578 Norway NO Europe NOK Kr norway 3_fill_in_countries_table (EURUSD,H1) 17| 840 United States US North America USD $ united-states 3_fill_in_countries_table (EURUSD,H1) 18| 826 United Kingdom GB Europe GBP £ united-kingdom 3_fill_in_countries_table (EURUSD,H1) 19| 756 Switzerland CH Europe CHF ₣ switzerland 3_fill_in_countries_table (EURUSD,H1) 20| 410 South Korea KR Asia KRW ₩ south-korea 3_fill_in_countries_table (EURUSD,H1) 21| 724 Spain ES Europe EUR € spain 3_fill_in_countries_table (EURUSD,H1) 22| 752 Sweden SE Europe SEK Kr sweden 3_fill_in_countries_table (EURUSD,H1) 23| 0 Worldwide WW World ALL worldwide
In MetaEditor, the table looks like this (Fig. 4).
Fig. 4. Filled COUNTRIES table
2.4 Selecting some of table columns
Let's handle the COUNTRIES table data. Suppose that we want to select the following columns:
- "COUNTRY_ID";
- "COUNTRY_NAME";
- "COUNTRY_CODE";
- "COUNTRY_CONTINENT";
- "CURRENCY".
Create an SQL query using the 4_select_some_columns.mq5 script the following way:
//--- include #include "..\CDatabase.mqh" //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { //--- open a database CDatabase db_obj; string file_name="Databases\\test_calendar_db.sqlite"; uint flags=DATABASE_OPEN_READONLY; if(!db_obj.Open(file_name, flags)) { db_obj.Close(); return; } //--- check a table string table_name="COUNTRIES"; if(db_obj.SelectTable(table_name)) { string col_names_to_select[]= { "COUNTRY_ID", "NAME", "CODE", "CONTINENT", "CURRENCY" }; if(!db_obj.SelectFrom(col_names_to_select)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest(); } db_obj.Close(); } //+------------------------------------------------------------------+
When printing out the query, we get:
4_select_some_columns (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY 4_select_some_columns (EURUSD,H1) --+---------------------------------------------------------- 4_select_some_columns (EURUSD,H1) 1| 554 New Zealand NZ Australia/Oceania NZD 4_select_some_columns (EURUSD,H1) 2| 999 European Union EU Europe EUR 4_select_some_columns (EURUSD,H1) 3| 392 Japan JP Asia JPY 4_select_some_columns (EURUSD,H1) 4| 124 Canada CA North America CAD 4_select_some_columns (EURUSD,H1) 5| 36 Australia AU Australia/Oceania AUD 4_select_some_columns (EURUSD,H1) 6| 156 China CN Asia CNY 4_select_some_columns (EURUSD,H1) 7| 380 Italy IT Europe EUR 4_select_some_columns (EURUSD,H1) 8| 702 Singapore SG Asia SGD 4_select_some_columns (EURUSD,H1) 9| 276 Germany DE Europe EUR 4_select_some_columns (EURUSD,H1) 10| 250 France FR Europe EUR 4_select_some_columns (EURUSD,H1) 11| 76 Brazil BR South America BRL 4_select_some_columns (EURUSD,H1) 12| 484 Mexico MX North America MXN 4_select_some_columns (EURUSD,H1) 13| 710 South Africa ZA Africa ZAR 4_select_some_columns (EURUSD,H1) 14| 344 Hong Kong HK Asia HKD 4_select_some_columns (EURUSD,H1) 15| 356 India IN Asia INR 4_select_some_columns (EURUSD,H1) 16| 578 Norway NO Europe NOK 4_select_some_columns (EURUSD,H1) 17| 840 United States US North America USD 4_select_some_columns (EURUSD,H1) 18| 826 United Kingdom GB Europe GBP 4_select_some_columns (EURUSD,H1) 19| 756 Switzerland CH Europe CHF 4_select_some_columns (EURUSD,H1) 20| 410 South Korea KR Asia KRW 4_select_some_columns (EURUSD,H1) 21| 724 Spain ES Europe EUR 4_select_some_columns (EURUSD,H1) 22| 752 Sweden SE Europe SEK 4_select_some_columns (EURUSD,H1) 23| 0 Worldwide WW World ALL
Obviously, the selection was made without any sorting.
2.5 Selecting some of the sorted table columns
Let's sort the data in the table by the "COUNTRY_ID" column. This request has the following implementation in the 5_select_some_sorted_columns.mq5 script:
//--- include #include "..\CDatabase.mqh" //+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { //--- open a database CDatabase db_obj; string file_name="Databases\\test_calendar_db.sqlite"; uint flags=DATABASE_OPEN_READONLY; if(!db_obj.Open(file_name, flags)) { db_obj.Close(); return; } //--- check a table string table_name="COUNTRIES"; if(db_obj.SelectTable(table_name)) { string col_names_to_select[]= { "COUNTRY_ID", "NAME", "CODE", "CONTINENT", "CURRENCY" }; string ord_names[1]; ord_names[0]=col_names_to_select[0]; if(!db_obj.SelectFromOrderedBy(col_names_to_select, ord_names)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest(); } db_obj.Close(); } //+------------------------------------------------------------------+
The result of the query execution appears in the log:
5_select_some_sorted_columns (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY 5_select_some_sorted_columns (EURUSD,H1) --+---------------------------------------------------------- 5_select_some_sorted_columns (EURUSD,H1) 1| 0 Worldwide WW World ALL 5_select_some_sorted_columns (EURUSD,H1) 2| 36 Australia AU Australia/Oceania AUD 5_select_some_sorted_columns (EURUSD,H1) 3| 76 Brazil BR South America BRL 5_select_some_sorted_columns (EURUSD,H1) 4| 124 Canada CA North America CAD 5_select_some_sorted_columns (EURUSD,H1) 5| 156 China CN Asia CNY 5_select_some_sorted_columns (EURUSD,H1) 6| 250 France FR Europe EUR 5_select_some_sorted_columns (EURUSD,H1) 7| 276 Germany DE Europe EUR 5_select_some_sorted_columns (EURUSD,H1) 8| 344 Hong Kong HK Asia HKD 5_select_some_sorted_columns (EURUSD,H1) 9| 356 India IN Asia INR 5_select_some_sorted_columns (EURUSD,H1) 10| 380 Italy IT Europe EUR 5_select_some_sorted_columns (EURUSD,H1) 11| 392 Japan JP Asia JPY 5_select_some_sorted_columns (EURUSD,H1) 12| 410 South Korea KR Asia KRW 5_select_some_sorted_columns (EURUSD,H1) 13| 484 Mexico MX North America MXN 5_select_some_sorted_columns (EURUSD,H1) 14| 554 New Zealand NZ Australia/Oceania NZD 5_select_some_sorted_columns (EURUSD,H1) 15| 578 Norway NO Europe NOK 5_select_some_sorted_columns (EURUSD,H1) 16| 702 Singapore SG Asia SGD 5_select_some_sorted_columns (EURUSD,H1) 17| 710 South Africa ZA Africa ZAR 5_select_some_sorted_columns (EURUSD,H1) 18| 724 Spain ES Europe EUR 5_select_some_sorted_columns (EURUSD,H1) 19| 752 Sweden SE Europe SEK 5_select_some_sorted_columns (EURUSD,H1) 20| 756 Switzerland CH Europe CHF 5_select_some_sorted_columns (EURUSD,H1) 21| 826 United Kingdom GB Europe GBP 5_select_some_sorted_columns (EURUSD,H1) 22| 840 United States US North America USD 5_select_some_sorted_columns (EURUSD,H1) 23| 999 European Union EU Europe EUR
The script works correctly - the "COUNTRY_ID" column starts at 0 and ends at 999.
2.6 Selecting grouped results of a specified table column
Now let's use the 6_select_some_grouped_columns.mq5 script to get grouped country names by continent. The task is to get the number of countries included in each row of the continent. Countries are selected from the “NAME” column. After running the script, the following lines appear in the log:
6_select_some_grouped_columns (EURUSD,H1) #| CONTINENT COUNT(NAME) 6_select_some_grouped_columns (EURUSD,H1) -+------------------------------ 6_select_some_grouped_columns (EURUSD,H1) 1| Africa 1 6_select_some_grouped_columns (EURUSD,H1) 2| Asia 6 6_select_some_grouped_columns (EURUSD,H1) 3| Australia/Oceania 2 6_select_some_grouped_columns (EURUSD,H1) 4| Europe 9 6_select_some_grouped_columns (EURUSD,H1) 5| North America 3 6_select_some_grouped_columns (EURUSD,H1) 6| South America 1 6_select_some_grouped_columns (EURUSD,H1) 7| World 1
“Europe” includes the most countries – 9, while “Africa” and “South America” have only 1 each. Besides, there is also “World”.
2.7 Selecting unique values of a specified table column
Now use the 7_select_distinct_columns.mq5 script to collect unique values in the CURRENCY column. There are countries using the same currency. To weed out repetitions, run the script. We can see the following result:
7_select_distinct_columns (EURUSD,H1) 1| NZD 7_select_distinct_columns (EURUSD,H1) 2| EUR 7_select_distinct_columns (EURUSD,H1) 3| JPY 7_select_distinct_columns (EURUSD,H1) 4| CAD 7_select_distinct_columns (EURUSD,H1) 5| AUD 7_select_distinct_columns (EURUSD,H1) 6| CNY 7_select_distinct_columns (EURUSD,H1) 7| SGD 7_select_distinct_columns (EURUSD,H1) 8| BRL 7_select_distinct_columns (EURUSD,H1) 9| MXN 7_select_distinct_columns (EURUSD,H1) 10| ZAR 7_select_distinct_columns (EURUSD,H1) 11| HKD 7_select_distinct_columns (EURUSD,H1) 12| INR 7_select_distinct_columns (EURUSD,H1) 13| NOK 7_select_distinct_columns (EURUSD,H1) 14| USD 7_select_distinct_columns (EURUSD,H1) 15| GBP 7_select_distinct_columns (EURUSD,H1) 16| CHF 7_select_distinct_columns (EURUSD,H1) 17| KRW 7_select_distinct_columns (EURUSD,H1) 18| SEK 7_select_distinct_columns (EURUSD,H1) 19| ALL
Thus, the calendar has events for a total of 18 currencies and one group of events that applies to all currencies.
It is easy to see that the methods for selecting grouped results and selecting unique values have similarities. Let's demonstrate this with an example.
The 8_compare_ grouped_and_distinct_columns.mq5 script displays the following results in the log:
8_compare_ grouped_and_distinct_columns (EURUSD,H1) 8_compare_ grouped_and_distinct_columns (EURUSD,H1) Method CDatabase::SelectFromGroupBy() 8_compare_ grouped_and_distinct_columns (EURUSD,H1) #| CONTINENT 8_compare_ grouped_and_distinct_columns (EURUSD,H1) -+------------------ 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 1| Africa 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 2| Asia 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 3| Australia/Oceania 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 4| Europe 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 5| North America 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 6| South America 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 7| World 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 8_compare_ grouped_and_distinct_columns (EURUSD,H1) Method CDatabase::SelectDistinctFrom() 8_compare_ grouped_and_distinct_columns (EURUSD,H1) #| CONTINENT 8_compare_ grouped_and_distinct_columns (EURUSD,H1) -+------------------ 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 1| Australia/Oceania 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 2| Europe 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 3| Asia 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 4| North America 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 5| South America 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 6| Africa 8_compare_ grouped_and_distinct_columns (EURUSD,H1) 7| World
The methods have returned the same content results because we defined the "CONTINENT" column as a grouping column (field) for the first method. Interestingly, the first method has also sorted the selection for us.
2.8 Selecting ordered unique values of a specified table column
CURRENCY column values were displayed by the 7_select_distinct_columns.mq5 script in an unsorted way. Let's make a selection with sorting (9_select_sorted_distinct_columns.mq5 script). Let the column "COUNTRY_ID" become the sorting criterion. As a result of log manipulations, we get:
9_select_sorted_distinct_columns (EURUSD,H1) #| CURRENCY 9_select_sorted_distinct_columns (EURUSD,H1) --+--------- 9_select_sorted_distinct_columns (EURUSD,H1) 1| ALL 9_select_sorted_distinct_columns (EURUSD,H1) 2| AUD 9_select_sorted_distinct_columns (EURUSD,H1) 3| BRL 9_select_sorted_distinct_columns (EURUSD,H1) 4| CAD 9_select_sorted_distinct_columns (EURUSD,H1) 5| CNY 9_select_sorted_distinct_columns (EURUSD,H1) 6| EUR 9_select_sorted_distinct_columns (EURUSD,H1) 7| HKD 9_select_sorted_distinct_columns (EURUSD,H1) 8| INR 9_select_sorted_distinct_columns (EURUSD,H1) 9| JPY 9_select_sorted_distinct_columns (EURUSD,H1) 10| KRW 9_select_sorted_distinct_columns (EURUSD,H1) 11| MXN 9_select_sorted_distinct_columns (EURUSD,H1) 12| NZD 9_select_sorted_distinct_columns (EURUSD,H1) 13| NOK 9_select_sorted_distinct_columns (EURUSD,H1) 14| SGD 9_select_sorted_distinct_columns (EURUSD,H1) 15| ZAR 9_select_sorted_distinct_columns (EURUSD,H1) 16| SEK 9_select_sorted_distinct_columns (EURUSD,H1) 17| CHF 9_select_sorted_distinct_columns (EURUSD,H1) 18| GBP 9_select_sorted_distinct_columns (EURUSD,H1) 19| USD
Now all currencies are sorted. By default, sorting is performed in ascending order.
2.9 Selecting some of the table columns by condition
Previously, we have already created an SQL query to select table columns. Now let's make it so that we can get the columns when some condition is met. Suppose that we want to select countries whose ID is equal to or greater than 392 and equal to or less than 840. This task is solved by the 10_select_some_columns_where.mq5 script.
After running the script, we will see the following in the log:
10_select_some_columns_where (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY 10_select_some_columns_where (EURUSD,H1) --+---------------------------------------------------------- 10_select_some_columns_where (EURUSD,H1) 1| 392 Japan JP Asia JPY 10_select_some_columns_where (EURUSD,H1) 2| 410 South Korea KR Asia KRW 10_select_some_columns_where (EURUSD,H1) 3| 484 Mexico MX North America MXN 10_select_some_columns_where (EURUSD,H1) 4| 554 New Zealand NZ Australia/Oceania NZD 10_select_some_columns_where (EURUSD,H1) 5| 578 Norway NO Europe NOK 10_select_some_columns_where (EURUSD,H1) 6| 702 Singapore SG Asia SGD 10_select_some_columns_where (EURUSD,H1) 7| 710 South Africa ZA Africa ZAR 10_select_some_columns_where (EURUSD,H1) 8| 724 Spain ES Europe EUR 10_select_some_columns_where (EURUSD,H1) 9| 752 Sweden SE Europe SEK 10_select_some_columns_where (EURUSD,H1) 10| 756 Switzerland CH Europe CHF 10_select_some_columns_where (EURUSD,H1) 11| 826 United Kingdom GB Europe GBP 10_select_some_columns_where (EURUSD,H1) 12| 840 United States US North America USD
In other words, the sample starts with the country code of 392 and ends with 840.
2.10 Selecting some of the sorted table columns by condition
Let's make the previous problem more complicated. Let's add a sorting criterion to the sample - this is the country's belonging to the continent. The current task is solved in the 11_select_some_sorted_columns_where.mq5 script. After running it, we will see the following rows in the log:
11_select_some_sorted_columns_where (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY 11_select_some_sorted_columns_where (EURUSD,H1) --+---------------------------------------------------------- 11_select_some_sorted_columns_where (EURUSD,H1) 1| 710 South Africa ZA Africa ZAR 11_select_some_sorted_columns_where (EURUSD,H1) 2| 392 Japan JP Asia JPY 11_select_some_sorted_columns_where (EURUSD,H1) 3| 410 South Korea KR Asia KRW 11_select_some_sorted_columns_where (EURUSD,H1) 4| 702 Singapore SG Asia SGD 11_select_some_sorted_columns_where (EURUSD,H1) 5| 554 New Zealand NZ Australia/Oceania NZD 11_select_some_sorted_columns_where (EURUSD,H1) 6| 578 Norway NO Europe NOK 11_select_some_sorted_columns_where (EURUSD,H1) 7| 724 Spain ES Europe EUR 11_select_some_sorted_columns_where (EURUSD,H1) 8| 752 Sweden SE Europe SEK 11_select_some_sorted_columns_where (EURUSD,H1) 9| 756 Switzerland CH Europe CHF 11_select_some_sorted_columns_where (EURUSD,H1) 10| 826 United Kingdom GB Europe GBP 11_select_some_sorted_columns_where (EURUSD,H1) 11| 484 Mexico MX North America MXN 11_select_some_sorted_columns_where (EURUSD,H1) 12| 840 United States US North America USD
As a result, "South Africa" comes first in the sample since the continent "Africa" comes first in the list of continents.
2.11 Updating some of the table columns by condition
Suppose that we are faced with the task of updating the rows in the selected columns. Moreover, we need to do this after fulfilling a preliminary condition.
Let's take the Asian countries and reset the values for them in the CURRENCY and CURRENCY_SYMBOL columns. The task is performed by the 12_update_some_columns.mq5 script.
As a result of its execution, we get the following table:
12_update_some_columns (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY CURRENCY_SYMBOL URL_NAME 12_update_some_columns (EURUSD,H1) --+----------------------------------------------------------------------------------------- 12_update_some_columns (EURUSD,H1) 1| 554 New Zealand NZ Australia/Oceania NZD $ new-zealand 12_update_some_columns (EURUSD,H1) 2| 999 European Union EU Europe EUR € european-union 12_update_some_columns (EURUSD,H1) 3| 392 Japan JP Asia None None japan 12_update_some_columns (EURUSD,H1) 4| 124 Canada CA North America CAD $ canada 12_update_some_columns (EURUSD,H1) 5| 36 Australia AU Australia/Oceania AUD $ australia 12_update_some_columns (EURUSD,H1) 6| 156 China CN Asia None None china 12_update_some_columns (EURUSD,H1) 7| 380 Italy IT Europe EUR € italy 12_update_some_columns (EURUSD,H1) 8| 702 Singapore SG Asia None None singapore 12_update_some_columns (EURUSD,H1) 9| 276 Germany DE Europe EUR € germany 12_update_some_columns (EURUSD,H1) 10| 250 France FR Europe EUR € france 12_update_some_columns (EURUSD,H1) 11| 76 Brazil BR South America BRL R$ brazil 12_update_some_columns (EURUSD,H1) 12| 484 Mexico MX North America MXN Mex$ mexico 12_update_some_columns (EURUSD,H1) 13| 710 South Africa ZA Africa ZAR R south-africa 12_update_some_columns (EURUSD,H1) 14| 344 Hong Kong HK Asia None None hong-kong 12_update_some_columns (EURUSD,H1) 15| 356 India IN Asia None None india 12_update_some_columns (EURUSD,H1) 16| 578 Norway NO Europe NOK Kr norway 12_update_some_columns (EURUSD,H1) 17| 840 United States US North America USD $ united-states 12_update_some_columns (EURUSD,H1) 18| 826 United Kingdom GB Europe GBP £ united-kingdom 12_update_some_columns (EURUSD,H1) 19| 756 Switzerland CH Europe CHF ₣ switzerland 12_update_some_columns (EURUSD,H1) 20| 410 South Korea KR Asia None None south-korea 12_update_some_columns (EURUSD,H1) 21| 724 Spain ES Europe EUR € spain 12_update_some_columns (EURUSD,H1) 22| 752 Sweden SE Europe SEK Kr sweden 12_update_some_columns (EURUSD,H1) 23| 0 Worldwide WW World ALL worldwide
2.12 Replacing and adding some table rows
Let's continue our work with the tables. Now let's try to replace some rows in the selected table.
Suppose that we need to replace the current symbol "Mex$" with "Peso mexicano" for “Mexico” in the CURRENCY_SYMBOL column. We will entrust this task to the 13_replace_some_rows.mq5 script.
In the current version of the COUNTRIES table, Mexico has the following entry:
| COUNTRY_ID | NAME | CODE | CONTINENT | CURRENCY | CURRENCY_SYMBOL | URL_NAME |
|---|---|---|---|---|---|---|
| 484 | Mexico | MX | North America | MXN | Mex$ |
|
In order to replace this row in the table, we need to set a unique value for the selected row. Otherwise, SQLite will not understand what we want to replace.
Let's assume that this value will be the name of the country (the NAME column). Then the replacement function will be represented as follows in the code:
//--- the replaced row for "COUNTRY_NAME=Mexico" string col_names[]= { "NAME", "CURRENCY_SYMBOL" }; string col_vals[2]; col_vals[0]=::StringFormat("'%s'", "Mexico"); col_vals[1]=::StringFormat("'%s'", "Peso mexicano"); if(!db_obj.Replace(col_names, col_vals)) { db_obj.Close(); return; }
When executing the script, we get the following error:
11_replace_some_rows (EURUSD,H1) database error, NOT NULL constraint failed: COUNTRIES.COUNTRY_ID 11_replace_some_rows (EURUSD,H1) CDatabase::Replace: failed with code 5619
Obviously, the NOT NULL constraint has been violated. The thing is that initially, when creating the table, it was specified that the COUNTRY_ID column cannot contain null. So, it is necessary to add a value for this column. In order not to get a half-empty line, let's add values for all columns.
//--- the replaced row for "COUNTRY_NAME=Mexico" string col_names[]= { "COUNTRY_ID", "NAME", "CODE", "CONTINENT", "CURRENCY", "CURRENCY_SYMBOL", "URL_NAME" }; string col_vals[7]; col_vals[0]=::StringFormat("%I64u", 484); col_vals[1]=::StringFormat("'%s'", "Mexico"); col_vals[2]=::StringFormat("'%s'", "MX"); col_vals[3]=::StringFormat("'%s'", "North America"); col_vals[4]=::StringFormat("'%s'", "MXN"); col_vals[5]=::StringFormat("'%s'", "Peso mexicano"); col_vals[6]=::StringFormat("'%s'", "mexico"); if(!db_obj.Replace(col_names, col_vals)) { db_obj.Close(); return; }
Now the script will work just fine. The following entries appear in the log:
13_replace_some_rows (EURUSD,H1) 'Mexico' row before replacement 13_replace_some_rows (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY CURRENCY_SYMBOL URL_NAME 13_replace_some_rows (EURUSD,H1) -+----------------------------------------------------------------------- 13_replace_some_rows (EURUSD,H1) 1| 484 Mexico MX North America MXN Mex$ mexico 13_replace_some_rows (EURUSD,H1) 13_replace_some_rows (EURUSD,H1) 'Mexico' row after replacement 13_replace_some_rows (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY CURRENCY_SYMBOL URL_NAME 13_replace_some_rows (EURUSD,H1) -+----------------------------------------------------------------------- 13_replace_some_rows (EURUSD,H1) 1| 484 Mexico MX North America MXN Peso mexicano mexico
It is worth noting that if there were no line containing data about Mexico, then it would simply be added. In other words, the replacement operation is also the operation of adding table rows.
2.13 Deleting some of the table rows
Now let's see how we can reduce the number of table rows rather than increasing it. To do this, create the 14_delete_some_rows.mq5 script, which will delete Asia-related rows from the selected table on request.
After running the script, display the final table:
14_delete_some_rows (EURUSD,H1) #| COUNTRY_ID NAME CODE CONTINENT CURRENCY CURRENCY_SYMBOL URL_NAME 14_delete_some_rows (EURUSD,H1) --+----------------------------------------------------------------------------------------- 14_delete_some_rows (EURUSD,H1) 1| 554 New Zealand NZ Australia/Oceania NZD $ new-zealand 14_delete_some_rows (EURUSD,H1) 2| 999 European Union EU Europe EUR € european-union 14_delete_some_rows (EURUSD,H1) 3| 124 Canada CA North America CAD $ canada 14_delete_some_rows (EURUSD,H1) 4| 36 Australia AU Australia/Oceania AUD $ australia 14_delete_some_rows (EURUSD,H1) 5| 380 Italy IT Europe EUR € italy 14_delete_some_rows (EURUSD,H1) 6| 276 Germany DE Europe EUR € germany 14_delete_some_rows (EURUSD,H1) 7| 250 France FR Europe EUR € france 14_delete_some_rows (EURUSD,H1) 8| 76 Brazil BR South America BRL R$ brazil 14_delete_some_rows (EURUSD,H1) 9| 710 South Africa ZA Africa ZAR R south-africa 14_delete_some_rows (EURUSD,H1) 10| 578 Norway NO Europe NOK Kr norway 14_delete_some_rows (EURUSD,H1) 11| 840 United States US North America USD $ united-states 14_delete_some_rows (EURUSD,H1) 12| 826 United Kingdom GB Europe GBP £ united-kingdom 14_delete_some_rows (EURUSD,H1) 13| 756 Switzerland CH Europe CHF ₣ switzerland 14_delete_some_rows (EURUSD,H1) 14| 724 Spain ES Europe EUR € spain 14_delete_some_rows (EURUSD,H1) 15| 752 Sweden SE Europe SEK Kr sweden 14_delete_some_rows (EURUSD,H1) 16| 0 Worldwide WW World ALL worldwide 14_delete_some_rows (EURUSD,H1) 17| 484 Mexico MX North America MXN Peso mexicano mexico
No Asia-related rows were found.
2.14 Adding columns to the table
Adding new columns to a table is also a pretty common task.
Suppose that we need to expand our COUNTRIES table and add a column containing the number of macroeconomic events that fall into the calendar.
The task will be performed by the 15_add_new_column.mq5 script.
After executing the script, check the table (Fig. 5). Now it features the new column EVENTS_NUM.
Fig. 5. New EVENTS_NUM column in the COUNTRIES table
2.15 Renaming columns in the table
Renaming columns is done by CDatabase::RenameColumn(const string _curr_name, const string _new_name). Set the current and new column names as parameters. The 16_rename_column.mq5 script replaces the EVENTS_NUM column name with EVENTS_NUMBER.
Fig. 6. Renamed EVENTS_NUMBER column in the COUNTRIES table
Now the table looks as follows (Fig. 6).
2.16 Concatenating rows
Suppose that we need to concatenate the sampling results within a single table. This can be achieved by the CDatabase::Union() method. The task is performed by the 17_union_some_columns.mq5 script.
Suppose that we have two tables - EUROPEAN_COUNTRIES and NORTH_AMERICAN_COUNTRIES. The first table features European countries, while the second one contains North American countries. Let's first create tables to concatenate their rows. Each of the tables will be the resulting selection from the COUNTRIES table. This looks as follows in the code:
//--- create 2 tables string table1_name, table2_name, sql_request; table1_name="EUROPEAN_COUNTRIES"; table2_name="NORTH_AMERICAN_COUNTRIES"; sql_request="SELECT COUNTRY_ID AS id, NAME AS name, CURRENCY " "as currency FROM COUNTRIES " "WHERE CONTINENT='North America'"; if(!db_obj.CreateTableAs(table2_name, sql_request, true)) { db_obj.Close(); return; } db_obj.FinalizeSqlRequest(); sql_request="SELECT COUNTRY_ID AS id, NAME AS name, CURRENCY " "as currency FROM COUNTRIES " "WHERE CONTINENT='Europe'"; if(!db_obj.CreateTableAs(table1_name, sql_request, true)) { db_obj.Close(); return; } db_obj.FinalizeSqlRequest();
While running the script, we get the following entries in the log:
16_union_some_columns (EURUSD,H1) #| id name currency 16_union_some_columns (EURUSD,H1) --+---------------------------- 16_union_some_columns (EURUSD,H1) 1| 124 Canada CAD 16_union_some_columns (EURUSD,H1) 2| 250 France EUR 16_union_some_columns (EURUSD,H1) 3| 276 Germany EUR 16_union_some_columns (EURUSD,H1) 4| 380 Italy EUR 16_union_some_columns (EURUSD,H1) 5| 484 Mexico MXN 16_union_some_columns (EURUSD,H1) 6| 578 Norway NOK 16_union_some_columns (EURUSD,H1) 7| 724 Spain EUR 16_union_some_columns (EURUSD,H1) 8| 752 Sweden SEK 16_union_some_columns (EURUSD,H1) 9| 756 Switzerland CHF 16_union_some_columns (EURUSD,H1) 10| 826 United Kingdom GBP 16_union_some_columns (EURUSD,H1) 11| 840 United States USD 16_union_some_columns (EURUSD,H1) 12| 999 European Union EUR
The resulting sample includes European and North American countries.
2.17 Sample difference
Suppose that we have two samples. We need to find entries in the first sample that are not present in the second one. This can be achieved by the CDatabase::Except() method.
Let's take the COUNTRIES and EUROPEAN_COUNTRIES tables as an example. Let's see which countries remain if the EXCEPT operator is applied to the first table.
The solution is provided by the 18_except_some_columns.mq5 script.
The following lines will be displayed in the log as a result of the script execution:
18_except_some_columns (EURUSD,H1) #| COUNTRY_ID NAME CURRENCY 18_except_some_columns (EURUSD,H1) -+---------------------------------- 18_except_some_columns (EURUSD,H1) 1| 0 Worldwide ALL 18_except_some_columns (EURUSD,H1) 2| 36 Australia AUD 18_except_some_columns (EURUSD,H1) 3| 76 Brazil BRL 18_except_some_columns (EURUSD,H1) 4| 124 Canada CAD 18_except_some_columns (EURUSD,H1) 5| 484 Mexico MXN 18_except_some_columns (EURUSD,H1) 6| 554 New Zealand NZD 18_except_some_columns (EURUSD,H1) 7| 710 South Africa ZAR 18_except_some_columns (EURUSD,H1) 8| 840 United States USD
As we can see, the sample does not contain European countries. The Asian ones are absent as well since they were removed earlier.
2.18 Sample intersection
Now let's find out what common features the samples have. In other words, the task is to find common rows of samples.
First, let's update the COUNTRIES table and return it to its original form, which included Asian countries.
Create two temporary tables with "id", "name" and "currency" columns. The first will include countries whose value in the COUNTRY_ID column does not exceed 578, and the second will include countries whose value in the same column is at least 392.
//--- create temporary tables string table1_name, table2_name, sql_request; table1_name="Table1"; table2_name="Table2"; sql_request="SELECT COUNTRY_ID AS id, NAME AS name, CURRENCY " "as currency FROM COUNTRIES " "WHERE COUNTRY_ID<=578"; if(!db_obj.CreateTableAs(table1_name, sql_request, true, true)) { db_obj.Close(); return; } db_obj.FinalizeSqlRequest(); //--- print the temporary table string temp_col_names[]= {"*"}; if(db_obj.SelectTable(table1_name, true)) if(db_obj.SelectFrom(temp_col_names)) { ::Print(" \nTable #1: "); db_obj.PrintSqlRequest(); db_obj.FinalizeSqlRequest(); } sql_request="SELECT COUNTRY_ID AS id, NAME AS name, CURRENCY " "as currency FROM COUNTRIES " "WHERE COUNTRY_ID>=392"; if(!db_obj.CreateTableAs(table2_name, sql_request, true, true)) { db_obj.Close(); return; } db_obj.FinalizeSqlRequest(); //--- print the temporary table if(db_obj.SelectTable(table2_name, true)) if(db_obj.SelectFrom(temp_col_names)) { ::Print(" \nTable #2: "); db_obj.PrintSqlRequest(); db_obj.FinalizeSqlRequest(); }
Let's use the features of the CDatabase::Intersect() method in the 19_intersect_some_columns.mq5 script. As a result, we get the following lines in the log:
19_intersect_some_columns (EURUSD,H1) #| id name currency 19_intersect_some_columns (EURUSD,H1) -+------------------------- 19_intersect_some_columns (EURUSD,H1) 1| 392 Japan JPY 19_intersect_some_columns (EURUSD,H1) 2| 410 South Korea KRW 19_intersect_some_columns (EURUSD,H1) 3| 484 Mexico MXN 19_intersect_some_columns (EURUSD,H1) 4| 554 New Zealand NZD 19_intersect_some_columns (EURUSD,H1) 5| 578 Norway NOK
The script has worked correctly. We got a list of countries where the country's minimum id value is 392, while the maximum one is 578.
2.19 Creating views
A viewis a sort of a virtual table. The convenience is that you can display data selected from any other table.
We will create views using the bool CDatabase::CreateView() and bool CDatabase::CreateViewWhere() methods.The first one creates some kind of unconditional view, while the second one creates a view according to a specified condition.
Let's consider the following example. We have the COUNTRIES table. Suppose that we need to select all countries by the NAME, CONTINENT, and CURRENCY columns for the new virtual table.
Let's solve this problem with the 20_create_view.mq5 script. The result is the “All_countries” view (Fig. 7).
Fig. 7. “All_countries” view
Let's complicate the example and now select European countries only. The 21_create_view_where.mq5 script will do this. As a result, we have a virtual table containing only European countries (Fig. 8).
Fig. 8. “European” view
Views are not full-fledged tables since we cannot add, delete or update rows in them. But they can be used to conveniently aggregate the results of complex queries and select individual columns, while changing names without affecting the relationships between the tables themselves.
2.20 Removing views
We can remove a previously created view using the CDatabase::DropView() method.
The method is similar to its counterpart, which removes DropTable() tables. In the previous examples, it was the view deletion method that was called before the view was created.
Now it is time to say a few words about the IF EXISTS construction. If we try to delete a non-existent view with this construction, the method returns 'true', otherwise it returns 'false'.
Let's see how the script works 22_drop_view.mq5.
//--- drop a view string table_name="COUNTRIES"; if(db_obj.SelectTable(table_name)) for(int idx=0; idx<2; idx++) { string view_name=::StringFormat("European%d", idx+1); bool if_exists=idx; if(db_obj.DropView(view_name, if_exists)) ::PrintFormat("A view \"%s\" has been successfully dropped!", view_name); db_obj.FinalizeSqlRequest(); }
It first tries to remove the non-existent "European_countries1" view without calling IF EXISTS. As a result, we get error 5601:
22_drop_view (EURUSD,H1) database error, no such view: European1 22_drop_view (EURUSD,H1) CDatabase::Select: failed with code 5601 22_drop_view (EURUSD,H1) A view "European2" has been successfully dropped!
After that, the script tries to delete the also non-existent "European_countries2" view using IF EXISTS. Deleting the second view will be successful, even though no deletion actually takes place.
2.21 Renaming the table
Let's say that we are faced with the task of renaming the table itself. To do this, let's turn to the CDatabase::RenameTable() method. The renaming is done by the 23_rename_table.mq5 script.
Fig. 9. Renamed COUNTRIES1 table
As a result, the current table will be named COUNTRIES1 (Fig. 9).
3. Database of macroeconomic events
In this section, I propose to start creating a relational database of macroeconomic events covered in the Calendar.
So, let's first create a table structure that will make up the future database. The article "MQL5 Cookbook – Economic Calendar" already considered of calendar structures. Therefore, in our case, it is quite easy to create database tables and set relationships for them.
3.1 Tables and relational connections
The database is to contain three source tables:
- COUNTRIES;
- EVENTS;
- EVENT_VALUES.
The relational connections between tables are shown in Fig. 10.
Fig. 10. The structure of connections between tables in the Calendar_DB database
The COUNTRIES table becomes the parent one for the EVENTS table. The latter, in turn, becomes a child one for the former.
The primary key for the COUNTRIES table is the COUNTRY_ID column (field). In the image, it is preceded by the "+" sign. For the EVENTS table, the key is the EVENT_ID column, while the COUNTRY_ID column is an external foreign key. In the structure, it is preceded by the "#" symbol.
The EVENTS table becomes the parent one for the EVENT_VALUES table, while the second one becomes the child one for the first.
In the EVENT_VALUES table, the primary key is the VALUE_ID column (field), while the external key is EVENT_ID.
The keys are needed precisely in order to implement the relationships between the tables indicated above. The relationships, in turn, contribute to the integrity of the data in the database.
The relationships between the three tables are in the one-to-many form (1..*). I believe, it will not be difficult to decipher them. The first connection between countries and events can be represented as follows: one country has many macroeconomic events, while one event has only one country. The second connection between events and event values can be illustrated as follows: one event has many values, while any value has only one event.
Let's move on to the code. The sCreateAndFillCalendarDB.mq5 script features the following stages:
- creating a calendar database;
- creating database tables;
- filling tables.
Let's see, for example, how the EVENTS table is created. The final query for creating this table looks like this:
CREATE TABLE IF NOT EXISTS EVENTS ( EVENT_ID [UNSIGNED BIG INT] PRIMARY KEY NOT NULL, TYPE TEXT, SECTOR TEXT, FREQUENCY TEXT, TIME_MODE TEXT, COUNTRY_ID [UNSIGNED BIG INT] NOT NULL, UNIT TEXT, IMPORTANCE TEXT, MULTIPLIER TEXT, DIGITS [UNSIGNED INT], SOURCE TEXT, CODE TEXT, NAME TEXT, FOREIGN KEY ( COUNTRY_ID ) REFERENCES COUNTRIES (COUNTRY_ID) ON UPDATE CASCADE ON DELETE CASCADE )
The strings where the external key is created are of particular interest. The FOREIGN KEY (COUNTRY_ID) line means that the table has an external key by the COUNTRY_ID field. The REFERENCES COUNTRIES(COUNTRY_ID) construction is used to refer to the COUNTRIES parent table.
The ON UPDATE CASCADE and ON DELETE CASCADE expressions mean that if a related row is deleted or modified from the parent table, the rows in the child table will also be deleted or modified.
As for filling tables, below is a block of code where the COUNTRIES table is being filled.
//--- Table 1 MqlCalendarCountry calendar_countries[]; table_name="COUNTRIES"; if(db_obj.SelectTable(table_name)) if(db_obj.EmptyTable()) { db_obj.FinalizeSqlRequest(); string col_names[]= { "COUNTRY_ID", // 1 "NAME", // 2 "CODE", // 3 "CONTINENT", // 4 "CURRENCY", // 5 "CURRENCY_SYMBOL",// 6 "URL_NAME" // 7 }; CiCalendarInfo calendar_info; if(calendar_info.Init()) { if(calendar_info.GetCountries(calendar_countries)) { if(db_obj.TransactionBegin()) for(int c_idx=0; c_idx<::ArraySize(calendar_countries); c_idx++) { MqlCalendarCountry curr_country=calendar_countries[c_idx]; string col_vals[]; ::ArrayResize(col_vals, 7); col_vals[0]=::StringFormat("%I64u", curr_country.id); col_vals[1]=::StringFormat("'%s'", curr_country.name); col_vals[2]=::StringFormat("'%s'", curr_country.code); col_vals[3]="NULL"; SCountryByContinent curr_country_continent_data; if(curr_country_continent_data.Init(curr_country.code)) col_vals[3]=::StringFormat("'%s'", curr_country_continent_data.ContinentDescription()); col_vals[4]=::StringFormat("'%s'", curr_country.currency); col_vals[5]=::StringFormat("'%s'", curr_country.currency_symbol); col_vals[6]=::StringFormat("'%s'", curr_country.url_name); if(!db_obj.InsertSingleRow(col_names, col_vals)) { db_obj.TransactionRollback(); db_obj.Close(); return; } db_obj.FinalizeSqlRequest(); } if(!db_obj.TransactionCommit()) ::PrintFormat("Failed to complete transaction execution, error %d", ::GetLastError()); } //--- print if(db_obj.PrintTable()<0) ::PrintFormat("Failed to print the table \"%s\", error %d", table_name, ::GetLastError()); } }
First, for further work with the table, we need to select it using the CDatabase::SelectTable() method. Here we can draw an analogy with how a trading position is selected using the ::PositionSelect() native function for its further processing.
Then the CDatabase::EmptyTable() method preliminarily clears the table.
Next, go through the countries in a loop and fill the table by columns
- "COUNTRY_ID",
- "COUNTRY_NAME",
- "COUNTRY_CODE",
- "CONTINENT",
- "CURRENCY",
- "CURRENCY_SYMBOL",
- "URL_NAME".
The final row is inserted into the table by the CDatabase::InsertSingleRow() method. Filling the table involves a transactional mechanism. Find out more in the section "Accelerating transactions by wrapping them into DatabaseTransactionBegin()/DatabaseTransactionCommit()".
As a result of filling three tables, the following results were obtained: the COUNTRIES table contains 23 entries, the EVENTS table contains 1500 entries, while the EVENT_VALUES table has 158 696 entries (Fig. 11).
Fig. 11. Filled EVENT_VALUES table
Now that we have the data, we can begin to form the queries.
3.2 Database queries
By and large, all database queries can be divided into two groups:
1) requests that receive data from the database;
2) queries that change data in the database.
First, let's handle the examples of getting information from the calendar database.
3.2.1 Sample number of events by country
Let's start by asking the database how many macroeconomic events there are in each country. Create the following query by referring to the "EVENTS" table:
SELECT COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num FROM EVENTS GROUP BY COUNTRY_ID
In the MQL5 code, such a request is implemented as follows:
//--- 1) group events number by country id string table_name="EVENTS"; if(db_obj.SelectTable(table_name)) { string col_names_to_select[]= { "COUNTRY_ID AS id", "COUNT(EVENT_ID) AS events_num" }; string gr_names[]= { "COUNTRY_ID" }; if(!db_obj.SelectFromGroupBy(col_names_to_select, gr_names)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
The result is the following selection by the COUNTRY_ID and COUNT(EVENT_ID) source columns displayed in the terminal log:
sRequest1 (EURUSD,H1) #| id events_num sRequest1 (EURUSD,H1) --+--------------- sRequest1 (EURUSD,H1) 1| 0 7 sRequest1 (EURUSD,H1) 2| 36 85 sRequest1 (EURUSD,H1) 3| 76 55 sRequest1 (EURUSD,H1) 4| 124 74 sRequest1 (EURUSD,H1) 5| 156 40 sRequest1 (EURUSD,H1) 6| 250 43 sRequest1 (EURUSD,H1) 7| 276 62 sRequest1 (EURUSD,H1) 8| 344 26 sRequest1 (EURUSD,H1) 9| 356 57 sRequest1 (EURUSD,H1) 10| 380 52 sRequest1 (EURUSD,H1) 11| 392 124 sRequest1 (EURUSD,H1) 12| 410 36 sRequest1 (EURUSD,H1) 13| 484 47 sRequest1 (EURUSD,H1) 14| 554 82 sRequest1 (EURUSD,H1) 15| 578 47 sRequest1 (EURUSD,H1) 16| 702 27 sRequest1 (EURUSD,H1) 17| 710 54 sRequest1 (EURUSD,H1) 18| 724 39 sRequest1 (EURUSD,H1) 19| 752 59 sRequest1 (EURUSD,H1) 20| 756 40 sRequest1 (EURUSD,H1) 21| 826 115 sRequest1 (EURUSD,H1) 22| 840 247 sRequest1 (EURUSD,H1) 23| 999 82
The selection does not look very readable, because the “id” column is a country ID, not a country name. The country names can be found in the COUNTRIES table.
To get the name of the country and the number of country events, we need to create a compound query (a query within a query).
The first version of such a compound query looks like this:
SELECT c.NAME AS country, ev.events_num AS events_number FROM COUNTRIES c JOIN ( SELECT COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num FROM EVENTS GROUP BY COUNTRY_ID ) AS ev ON c.COUNTRY_ID = ev.id
This option uses the query we created at the beginning. But now it has become part of another query, thereby changing its form to the form of a subquery.
The second version of the request can be implemented in the form of CTE:
WITH ev_cnt AS ( SELECT COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num FROM EVENTS GROUP BY COUNTRY_ID ) SELECT c.NAME AS country, ev.events_num AS events_number FROM COUNTRIES c INNER JOIN ev_cnt AS ev ON c.COUNTRY_ID = ev.id
In the MQL5 code, a compound query is implemented as follows:
//--- 2) group events number by country name using a subquery ::Print("\nGroup events number by country name using a subquery:\n"); string subquery=db_obj.SqlRequest(); string new_sql_request=::StringFormat("SELECT c.NAME AS country," "ev.events_num AS events_number FROM COUNTRIES c " "JOIN(%s) AS ev " "ON c.COUNTRY_ID=ev.id", subquery); if(!db_obj.Select(new_sql_request)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
The table expression (CTE) is implemented as follows:
//--- 3) group events number by country name using CTE ::Print("\nGroup events number by country name using CTE:\n"); new_sql_request=::StringFormat("WITH ev_cnt AS (%s)" "SELECT c.NAME AS country," "ev.events_num AS events_number FROM COUNTRIES c " "INNER JOIN ev_cnt AS ev ON c.COUNTRY_ID=ev.id", subquery); if(!db_obj.Select(new_sql_request)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
Both options print the following query results in the log:
sRequest1 (EURUSD,H1) #| country events_number sRequest1 (EURUSD,H1) --+----------------------------- sRequest1 (EURUSD,H1) 1| Worldwide 7 sRequest1 (EURUSD,H1) 2| Australia 85 sRequest1 (EURUSD,H1) 3| Brazil 55 sRequest1 (EURUSD,H1) 4| Canada 74 sRequest1 (EURUSD,H1) 5| China 40 sRequest1 (EURUSD,H1) 6| France 43 sRequest1 (EURUSD,H1) 7| Germany 62 sRequest1 (EURUSD,H1) 8| Hong Kong 26 sRequest1 (EURUSD,H1) 9| India 57 sRequest1 (EURUSD,H1) 10| Italy 52 sRequest1 (EURUSD,H1) 11| Japan 124 sRequest1 (EURUSD,H1) 12| South Korea 36 sRequest1 (EURUSD,H1) 13| Mexico 47 sRequest1 (EURUSD,H1) 14| New Zealand 82 sRequest1 (EURUSD,H1) 15| Norway 47 sRequest1 (EURUSD,H1) 16| Singapore 27 sRequest1 (EURUSD,H1) 17| South Africa 54 sRequest1 (EURUSD,H1) 18| Spain 39 sRequest1 (EURUSD,H1) 19| Sweden 59 sRequest1 (EURUSD,H1) 20| Switzerland 40 sRequest1 (EURUSD,H1) 21| United Kingdom 115 sRequest1 (EURUSD,H1) 22| United States 247 sRequest1 (EURUSD,H1) 23| European Union 82
It is easy to see that the canlendar pays the utmost attention to U.S. events - there are 247 of them in it.
Let's complicate the task a little and add a column to the sample, in which we calculate how many important events occur in a particular country. The importance is defined in the IMPORTANCE column. We will select only those events that have the High value.
First, let's work with the EVENTS table. Here we will need to create two samples. The first sample is a count of the number of events by country. This task has already been completed above. The second sample is a count of the number of important events by country. Finally, we need to combine two samples.
The SQL code of the query looks as follows:
SELECT evn.COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num, imp.high AS imp_events_num FROM EVENTS evn JOIN ( SELECT COUNTRY_ID AS id, COUNT(IMPORTANCE) AS high FROM EVENTS WHERE IMPORTANCE = 'High' GROUP BY COUNTRY_ID ) AS imp ON evn.COUNTRY_ID = imp.id GROUP BY COUNTRY_ID
As for the MQL5 implementation, the code looks like this:
//--- 5) important events - ids, events number and important events number ::Print("\nGroup events number and important events number by country id"); subquery=db_obj.SqlRequest(); string new_sql_request4=::StringFormat("SELECT ev.COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num," "imp.high AS imp_events_num " "FROM EVENTS ev JOIN (%s) AS imp " "ON ev.COUNTRY_ID=imp.id GROUP BY COUNTRY_ID", subquery); if(!db_obj.Select(new_sql_request4)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
As a result, we get the following log entries:
sRequest1 (EURUSD,H1) Group events number and important events number by country id: sRequest1 (EURUSD,H1) sRequest1 (EURUSD,H1) #| id events_num imp_events_num sRequest1 (EURUSD,H1) --+------------------------------ sRequest1 (EURUSD,H1) 1| 0 7 2 sRequest1 (EURUSD,H1) 2| 36 85 5 sRequest1 (EURUSD,H1) 3| 76 55 2 sRequest1 (EURUSD,H1) 4| 124 74 10 sRequest1 (EURUSD,H1) 5| 156 40 5 sRequest1 (EURUSD,H1) 6| 250 43 1 sRequest1 (EURUSD,H1) 7| 276 62 3 sRequest1 (EURUSD,H1) 8| 344 26 1 sRequest1 (EURUSD,H1) 9| 356 57 2 sRequest1 (EURUSD,H1) 10| 392 124 7 sRequest1 (EURUSD,H1) 11| 410 36 2 sRequest1 (EURUSD,H1) 12| 484 47 2 sRequest1 (EURUSD,H1) 13| 554 82 8 sRequest1 (EURUSD,H1) 14| 578 47 2 sRequest1 (EURUSD,H1) 15| 702 27 1 sRequest1 (EURUSD,H1) 16| 710 54 2 sRequest1 (EURUSD,H1) 17| 752 59 1 sRequest1 (EURUSD,H1) 18| 756 40 4 sRequest1 (EURUSD,H1) 19| 826 115 13 sRequest1 (EURUSD,H1) 20| 840 247 20 sRequest1 (EURUSD,H1) 21| 999 82 11
In the final selection, it remains only to replace the “id” column with “country”.
Let's create a compound query again. We will take advantage of the fact that its parts were written earlier. At the end, sort the sample in descending order of the values in the 'imp_events_number' column. The compound query looks like this:
SELECT c.NAME AS country, ev.events_num AS events_number, ev.imp_events_num AS imp_events_number FROM COUNTRIES c JOIN ( SELECT ev.COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num, imp.high AS imp_events_num FROM EVENTS ev JOIN ( SELECT COUNTRY_ID AS id, COUNT(IMPORTANCE) AS high FROM EVENTS WHERE IMPORTANCE = 'High' GROUP BY COUNTRY_ID ) AS imp ON ev.COUNTRY_ID = imp.id GROUP BY COUNTRY_ID ) AS ev ON c.COUNTRY_ID = ev.id ORDER BY imp_events_number DESC
In the MQL5 code, the request is implemented as follows:
//--- 6) important events - countries, events number and important events number ::Print("\nGroup events number and important events number by country:\n"); subquery=db_obj.SqlRequest(); string new_sql_request5=::StringFormat("SELECT c.NAME AS country," "ev.events_num AS events_number," "ev.imp_events_num AS imp_events_number " "FROM COUNTRIES c " "JOIN(%s) AS ev " "ON c.COUNTRY_ID=ev.id " "ORDER BY imp_events_number DESC", subquery); if(!db_obj.Select(new_sql_request5)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
Now we get the desired sample in the log:
sRequest1 (EURUSD,H1) Group events number and important events number by country: sRequest1 (EURUSD,H1) sRequest1 (EURUSD,H1) #| country events_number imp_events_number sRequest1 (EURUSD,H1) --+----------------------------------------------- sRequest1 (EURUSD,H1) 1| United States 247 20 sRequest1 (EURUSD,H1) 2| United Kingdom 115 13 sRequest1 (EURUSD,H1) 3| European Union 82 11 sRequest1 (EURUSD,H1) 4| Canada 74 10 sRequest1 (EURUSD,H1) 5| New Zealand 82 8 sRequest1 (EURUSD,H1) 6| Japan 124 7 sRequest1 (EURUSD,H1) 7| Australia 85 5 sRequest1 (EURUSD,H1) 8| China 40 5 sRequest1 (EURUSD,H1) 9| Switzerland 40 4 sRequest1 (EURUSD,H1) 10| Germany 62 3 sRequest1 (EURUSD,H1) 11| Worldwide 7 2 sRequest1 (EURUSD,H1) 12| Brazil 55 2 sRequest1 (EURUSD,H1) 13| India 57 2 sRequest1 (EURUSD,H1) 14| South Korea 36 2 sRequest1 (EURUSD,H1) 15| Mexico 47 2 sRequest1 (EURUSD,H1) 16| Norway 47 2 sRequest1 (EURUSD,H1) 17| South Africa 54 2 sRequest1 (EURUSD,H1) 18| France 43 1 sRequest1 (EURUSD,H1) 19| Hong Kong 26 1 sRequest1 (EURUSD,H1) 20| Singapore 27 1 sRequest1 (EURUSD,H1) 21| Sweden 59 1
As can be seen from the sample, the United States has the most important events - 20. UK comes second - 13. The third place is occupied by EU - 11. Japan comes 6th - 7.
Let's use a query to find those countries that have no important events at all. To do this, we need to find the difference between the two samples. The first sample will include all countries taken from the COUNTRIES table, while the second one - the column with countries from the previous composite query.
In this case, the SQL code will be as follows:
SELECT NAME FROM COUNTRIES EXCEPT SELECT country FROM ( SELECT c.NAME AS country, ev.events_num AS events_number, ev.imp_events_num AS imp_events_number FROM COUNTRIES c JOIN ( SELECT ev.COUNTRY_ID AS id, COUNT(EVENT_ID) AS events_num, imp.high AS imp_events_num FROM EVENTS ev JOIN ( SELECT COUNTRY_ID AS id, COUNT(IMPORTANCE) AS high FROM EVENTS WHERE IMPORTANCE = 'High' GROUP BY COUNTRY_ID ) AS imp ON ev.COUNTRY_ID = imp.id GROUP BY COUNTRY_ID ) AS ev ON c.COUNTRY_ID = ev.id )
MQL5 code looks simpler as we take advantage of the fact that the previous query becomes our new subquery.
//--- 7) countries having no important events ::Print("\nCountries having no important events:\n"); string last_request=db_obj.SqlRequest(); string new_sql_request6=::StringFormat("SELECT NAME FROM COUNTRIES " "EXCEPT SELECT country FROM (%s)", last_request); if(!db_obj.Select(new_sql_request6)) { db_obj.Close(); return; } //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
Upon completion of the code execution, we will receive the following entries in the log:
sRequest1 (EURUSD,H1) Countries having no important events: sRequest1 (EURUSD,H1) sRequest1 (EURUSD,H1) #| NAME sRequest1 (EURUSD,H1) -+------ sRequest1 (EURUSD,H1) 1| Italy sRequest1 (EURUSD,H1) 2| Spain
So, of all countries, only Italy and Spain do not have important events. Requests about country events in MQL5 were executed in the sRequest1.mq5 script.
3.2.2 Sample of GDP values by country
In this example, we will make a database query to get a selection of GDP values for various countries. As the value of GDP, we will take the parameter “Gross domestic product (GDP) q/q” (for the 3rd quarter).
There will be several samples, so the query will be compound.
First, let's find out the economies of which countries have a quarterly GDP indicator.
The SQL code looks as follows:
SELECT COUNTRY_ID, EVENT_ID FROM EVENTS WHERE (NAME LIKE 'GDP q/q' AND SECTOR = 'Gross Domestic Product')
MQL5 implementation looks as follows (sRequest2.mq5 script):
//--- 1) countries by id where the indicator '%GDP q/q%' exists string col_names[]= {"COUNTRY_ID", "EVENT_ID"}; string where_condition="(NAME LIKE 'GDP q/q' AND SECTOR='Gross Domestic Product')"; if(!db_obj.SelectFromWhere(col_names, where_condition)) { db_obj.Close(); return; } ::Print("\nCountries by id where the indicator 'GDP q/q' exists:\n"); //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
Below is a printout from the log after the query is executed:
sRequest2 (EURUSD,H1) Countries by id where the indicator 'GDP q/q' exists: sRequest2 (EURUSD,H1) sRequest2 (EURUSD,H1) #| COUNTRY_ID EVENT_ID sRequest2 (EURUSD,H1) --+--------------------- sRequest2 (EURUSD,H1) 1| 554 554010024 sRequest2 (EURUSD,H1) 2| 999 999030016 sRequest2 (EURUSD,H1) 3| 392 392010001 sRequest2 (EURUSD,H1) 4| 124 124010022 sRequest2 (EURUSD,H1) 5| 36 36010019 sRequest2 (EURUSD,H1) 6| 156 156010004 sRequest2 (EURUSD,H1) 7| 380 380010020 sRequest2 (EURUSD,H1) 8| 702 702010004 sRequest2 (EURUSD,H1) 9| 276 276010008 sRequest2 (EURUSD,H1) 10| 250 250010005 sRequest2 (EURUSD,H1) 11| 76 76010010 sRequest2 (EURUSD,H1) 12| 484 484020016 sRequest2 (EURUSD,H1) 13| 710 710060009 sRequest2 (EURUSD,H1) 14| 344 344020002 sRequest2 (EURUSD,H1) 15| 578 578020012 sRequest2 (EURUSD,H1) 16| 840 840010007 sRequest2 (EURUSD,H1) 17| 826 826010037 sRequest2 (EURUSD,H1) 18| 756 756040001 sRequest2 (EURUSD,H1) 19| 410 410010011 sRequest2 (EURUSD,H1) 20| 724 724010005 sRequest2 (EURUSD,H1) 21| 752 752010019
As we can see, the required indicator exists in 21 countries. The indicator is not used in India as a global one ("Worldwide").
Now we need to get a sample of indicator values for the 3rd quarter and associate it with the first selection by event id.
The SQL query looks as follows:
SELECT evs.COUNTRY_ID AS country_id, evals.EVENT_ID AS event_id, evals.VALUE_ID AS value_id, evals.PERIOD AS period, evals.TIME AS time, evals.ACTUAL AS actual FROM EVENT_VALUES evals JOIN ( SELECT COUNTRY_ID, EVENT_ID FROM EVENTS WHERE (NAME LIKE 'GDP q/q' AND SECTOR = 'Gross Domestic Product') ) AS evs ON evals.event_id = evs.EVENT_ID WHERE (period = '2022.07.01 00:00' )
As for the MQL5 code, the compound query is implemented as follows:
//--- 2) 'GDP y/y' event and last values string subquery=db_obj.SqlRequest(); string new_sql_request1=::StringFormat("SELECT evs.COUNTRY_ID AS country_id," "evals.EVENT_ID AS event_id," "evals.VALUE_ID AS value_id," "evals.PERIOD AS period," "evals.TIME AS time," "evals.ACTUAL AS actual " "FROM EVENT_VALUES evals " "JOIN(%s) AS evs ON evals.event_id = evs.event_id " " WHERE (period = \'2022.07.01 00:00\')", subquery); if(!db_obj.Select(new_sql_request1)) { db_obj.Close(); return; } ::Print("\n'GDP y/y' event and last values:\n"); //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
After the execution, the following lines appear in the log:
sRequest2 (EURUSD,H1) 'GDP q/q' event and last values: sRequest2 (EURUSD,H1) sRequest2 (EURUSD,H1) #| country_id event_id value_id period time actual sRequest2 (EURUSD,H1) --+----------------------------------------------------------------------- sRequest2 (EURUSD,H1) 1| 554 554010024 168293 2022.07.01 00:00 2022.12.14 23:45 2.0 sRequest2 (EURUSD,H1) 2| 999 999030016 158836 2022.07.01 00:00 2022.10.31 12:00 0.2 sRequest2 (EURUSD,H1) 3| 999 999030016 158837 2022.07.01 00:00 2022.11.15 12:00 0.2 sRequest2 (EURUSD,H1) 4| 999 999030016 158838 2022.07.01 00:00 2022.12.07 12:00 0.3 sRequest2 (EURUSD,H1) 5| 392 392010001 165181 2022.07.01 00:00 2022.11.15 01:50 -0.3 sRequest2 (EURUSD,H1) 6| 392 392010001 165182 2022.07.01 00:00 2022.12.08 01:50 -0.2 sRequest2 (EURUSD,H1) 7| 124 124010022 161963 2022.07.01 00:00 2022.11.29 15:30 0.7 sRequest2 (EURUSD,H1) 8| 36 36010019 173679 2022.07.01 00:00 2022.12.07 02:30 0.6 sRequest2 (EURUSD,H1) 9| 156 156010004 172459 2022.07.01 00:00 2022.10.24 04:00 3.9 sRequest2 (EURUSD,H1) 10| 380 380010020 162296 2022.07.01 00:00 2022.10.31 11:00 0.5 sRequest2 (EURUSD,H1) 11| 380 380010020 162297 2022.07.01 00:00 2022.11.30 11:00 0.5 sRequest2 (EURUSD,H1) 12| 702 702010004 167581 2022.07.01 00:00 2022.10.14 02:00 1.5 sRequest2 (EURUSD,H1) 13| 702 702010004 174527 2022.07.01 00:00 2022.11.23 02:00 1.1 sRequest2 (EURUSD,H1) 14| 276 276010008 172410 2022.07.01 00:00 2022.10.28 10:00 0.3 sRequest2 (EURUSD,H1) 15| 276 276010008 157759 2022.07.01 00:00 2022.11.25 09:00 0.4 sRequest2 (EURUSD,H1) 16| 250 250010005 169062 2022.07.01 00:00 2022.10.28 07:30 0.2 sRequest2 (EURUSD,H1) 17| 250 250010005 169389 2022.07.01 00:00 2022.11.30 09:45 0.2 sRequest2 (EURUSD,H1) 18| 76 76010010 173825 2022.07.01 00:00 2022.12.01 14:00 0.4 sRequest2 (EURUSD,H1) 19| 484 484020016 166108 2022.07.01 00:00 2022.10.31 14:00 1.0 sRequest2 (EURUSD,H1) 20| 484 484020016 166109 2022.07.01 00:00 2022.11.25 14:00 0.9 sRequest2 (EURUSD,H1) 21| 710 710060009 175234 2022.07.01 00:00 2022.12.06 11:30 1.6 sRequest2 (EURUSD,H1) 22| 344 344020002 155337 2022.07.01 00:00 2022.10.31 10:30 -2.6 sRequest2 (EURUSD,H1) 23| 344 344020002 155338 2022.07.01 00:00 2022.11.11 10:30 -2.6 sRequest2 (EURUSD,H1) 24| 578 578020012 172320 2022.07.01 00:00 2022.11.18 09:00 1.5 sRequest2 (EURUSD,H1) 25| 840 840010007 163417 2022.07.01 00:00 2022.10.27 14:30 2.6 sRequest2 (EURUSD,H1) 26| 840 840010007 163418 2022.07.01 00:00 2022.11.30 15:30 2.9 sRequest2 (EURUSD,H1) 27| 840 840010007 163419 2022.07.01 00:00 2022.12.22 15:30 3.2 sRequest2 (EURUSD,H1) 28| 826 826010037 157174 2022.07.01 00:00 2022.11.11 09:00 -0.2 sRequest2 (EURUSD,H1) 29| 826 826010037 157175 2022.07.01 00:00 2022.12.22 09:00 -0.3 sRequest2 (EURUSD,H1) 30| 756 756040001 159276 2022.07.01 00:00 2022.11.29 10:00 0.2 sRequest2 (EURUSD,H1) 31| 410 410010011 161626 2022.07.01 00:00 2022.10.27 01:00 0.3 sRequest2 (EURUSD,H1) 32| 410 410010011 161627 2022.07.01 00:00 2022.12.01 01:00 0.3 sRequest2 (EURUSD,H1) 33| 724 724010005 159814 2022.07.01 00:00 2022.10.28 09:00 0.2 sRequest2 (EURUSD,H1) 34| 724 724010005 159815 2022.07.01 00:00 2022.12.23 10:00 0.1 sRequest2 (EURUSD,H1) 35| 752 752010019 170359 2022.07.01 00:00 2022.10.28 08:00 0.7 sRequest2 (EURUSD,H1) 36| 752 752010019 171381 2022.07.01 00:00 2022.11.29 09:00 0.6
It is easy to see that there are several values in the sample for some events with the same event_id. For example, entries 2-4 refer to the EU parameter. The GDP has been estimated in several readings, so there are several parameter values. As a result, the final sample contains 36 entries, which is clearly more than the number of countries the parameter has been calculated for.
If we need to make a sample by getting only the latest values for a given event, then we need to add the abilities to group and sort group results in the query. Then we get the following compound SQL query:
SELECT evs.COUNTRY_ID AS country_id, evals.EVENT_ID AS event_id, evals.VALUE_ID AS value_id, evals.PERIOD AS period, evals.TIME AS time, evals.ACTUAL AS actual FROM EVENT_VALUES evals JOIN ( SELECT COUNTRY_ID, EVENT_ID FROM EVENTS WHERE (NAME LIKE 'GDP q/q' AND SECTOR = 'Gross Domestic Product') ) AS evs ON evals.event_id = evs.EVENT_ID WHERE (period = '2022.07.01 00:00' ) GROUP BY evals.event_id HAVING MAX(value_id)
The entries will be grouped by the "event_id" column (field). If there are multiple entries, the one with the maximum value by the "value_id" column (field) is used. Thus, only one of the three entries for EU will be selected in this case:
| country_id | event_id | value_id | period | time | actual |
|---|---|---|---|---|---|
| 999 | 999030016 | 158838 | 2022.07.01 00:00 | 2022.12.07 12:00 | 0.3 |
As a result, the following entries appear in the log:
sRequest2 (EURUSD,H1) 'GDP q/q' event and grouped last values: sRequest2 (EURUSD,H1) sRequest2 (EURUSD,H1) #| country_id event_id value_id period time actual sRequest2 (EURUSD,H1) --+----------------------------------------------------------------------- sRequest2 (EURUSD,H1) 1| 36 36010019 173679 2022.07.01 00:00 2022.12.07 02:30 0.6 sRequest2 (EURUSD,H1) 2| 76 76010010 173825 2022.07.01 00:00 2022.12.01 14:00 0.4 sRequest2 (EURUSD,H1) 3| 124 124010022 161963 2022.07.01 00:00 2022.11.29 15:30 0.7 sRequest2 (EURUSD,H1) 4| 156 156010004 172459 2022.07.01 00:00 2022.10.24 04:00 3.9 sRequest2 (EURUSD,H1) 5| 250 250010005 169389 2022.07.01 00:00 2022.11.30 09:45 0.2 sRequest2 (EURUSD,H1) 6| 276 276010008 172410 2022.07.01 00:00 2022.10.28 10:00 0.3 sRequest2 (EURUSD,H1) 7| 344 344020002 155338 2022.07.01 00:00 2022.11.11 10:30 -2.6 sRequest2 (EURUSD,H1) 8| 380 380010020 162297 2022.07.01 00:00 2022.11.30 11:00 0.5 sRequest2 (EURUSD,H1) 9| 392 392010001 165182 2022.07.01 00:00 2022.12.08 01:50 -0.2 sRequest2 (EURUSD,H1) 10| 410 410010011 161627 2022.07.01 00:00 2022.12.01 01:00 0.3 sRequest2 (EURUSD,H1) 11| 484 484020016 166109 2022.07.01 00:00 2022.11.25 14:00 0.9 sRequest2 (EURUSD,H1) 12| 554 554010024 168293 2022.07.01 00:00 2022.12.14 23:45 2.0 sRequest2 (EURUSD,H1) 13| 578 578020012 172320 2022.07.01 00:00 2022.11.18 09:00 1.5 sRequest2 (EURUSD,H1) 14| 702 702010004 174527 2022.07.01 00:00 2022.11.23 02:00 1.1 sRequest2 (EURUSD,H1) 15| 710 710060009 175234 2022.07.01 00:00 2022.12.06 11:30 1.6 sRequest2 (EURUSD,H1) 16| 724 724010005 159815 2022.07.01 00:00 2022.12.23 10:00 0.1 sRequest2 (EURUSD,H1) 17| 752 752010019 171381 2022.07.01 00:00 2022.11.29 09:00 0.6 sRequest2 (EURUSD,H1) 18| 756 756040001 159276 2022.07.01 00:00 2022.11.29 10:00 0.2 sRequest2 (EURUSD,H1) 19| 826 826010037 157175 2022.07.01 00:00 2022.12.22 09:00 -0.3 sRequest2 (EURUSD,H1) 20| 840 840010007 163419 2022.07.01 00:00 2022.12.22 15:30 3.2 sRequest2 (EURUSD,H1) 21| 999 999030016 158838 2022.07.01 00:00 2022.12.07 12:00 0.3
Now there are 21 entries in the sample. Finally, we need to replace the country code with its name. Let's change the previous SLQ query to the following one:
SELECT c.NAME AS country, ev_evals.event_id AS event_id, ev_evals.value_id AS value_id, ev_evals.period AS period, ev_evals.TIME AS time, ev_evals.ACTUAL AS actual FROM COUNTRIES c JOIN ( SELECT evs.COUNTRY_ID AS country_id, evals.EVENT_ID AS event_id, evals.VALUE_ID AS value_id, evals.PERIOD AS period, evals.TIME AS time, evals.ACTUAL AS actual FROM EVENT_VALUES evals JOIN ( SELECT COUNTRY_ID, EVENT_ID FROM EVENTS WHERE (NAME LIKE 'GDP q/q' AND SECTOR = 'Gross Domestic Product') ) AS evs ON evals.event_id = evs.EVENT_ID WHERE (period = '2022.07.01 00:00') GROUP BY evals.event_id HAVING MAX(value_id) ) AS ev_evals ON c.COUNTRY_ID = ev_evals.country_id
Implement the following compound query in MQL5 along the way:
//--- 4) 'GDP q/q' event and grouped last values with country names subquery=db_obj.SqlRequest(); string new_sql_request3=::StringFormat("SELECT c.NAME AS country," "ev_evals.event_id AS event_id," "ev_evals.value_id AS value_id," "ev_evals.period AS period," "ev_evals.TIME AS time," "ev_evals.ACTUAL AS actual " "FROM COUNTRIES c JOIN (%s) " "AS ev_evals ON c.COUNTRY_ID = ev_evals.country_id", subquery); if(!db_obj.Select(new_sql_request3)) { db_obj.Close(); return; } ::Print("\n'GDP q/q' event and grouped last values with country names:\n"); //--- print the SQL request if(db_obj.PrintSqlRequest()<0) ::PrintFormat("Failed to print the SQL request, error %d", ::GetLastError()); db_obj.FinalizeSqlRequest();
The desired sample will be printed in the journal:
sRequest2 (EURUSD,H1) 'GDP q/q' event and grouped last values with country names: sRequest2 (EURUSD,H1) sRequest2 (EURUSD,H1) #| country event_id value_id period time actual sRequest2 (EURUSD,H1) --+--------------------------------------------------------------------------- sRequest2 (EURUSD,H1) 1| Australia 36010019 173679 2022.07.01 00:00 2022.12.07 02:30 0.6 sRequest2 (EURUSD,H1) 2| Brazil 76010010 173825 2022.07.01 00:00 2022.12.01 14:00 0.4 sRequest2 (EURUSD,H1) 3| Canada 124010022 161963 2022.07.01 00:00 2022.11.29 15:30 0.7 sRequest2 (EURUSD,H1) 4| China 156010004 172459 2022.07.01 00:00 2022.10.24 04:00 3.9 sRequest2 (EURUSD,H1) 5| France 250010005 169389 2022.07.01 00:00 2022.11.30 09:45 0.2 sRequest2 (EURUSD,H1) 6| Germany 276010008 172410 2022.07.01 00:00 2022.10.28 10:00 0.3 sRequest2 (EURUSD,H1) 7| Hong Kong 344020002 155338 2022.07.01 00:00 2022.11.11 10:30 -2.6 sRequest2 (EURUSD,H1) 8| Italy 380010020 162297 2022.07.01 00:00 2022.11.30 11:00 0.5 sRequest2 (EURUSD,H1) 9| Japan 392010001 165182 2022.07.01 00:00 2022.12.08 01:50 -0.2 sRequest2 (EURUSD,H1) 10| South Korea 410010011 161627 2022.07.01 00:00 2022.12.01 01:00 0.3 sRequest2 (EURUSD,H1) 11| Mexico 484020016 166109 2022.07.01 00:00 2022.11.25 14:00 0.9 sRequest2 (EURUSD,H1) 12| New Zealand 554010024 168293 2022.07.01 00:00 2022.12.14 23:45 2.0 sRequest2 (EURUSD,H1) 13| Norway 578020012 172320 2022.07.01 00:00 2022.11.18 09:00 1.5 sRequest2 (EURUSD,H1) 14| Singapore 702010004 174527 2022.07.01 00:00 2022.11.23 02:00 1.1 sRequest2 (EURUSD,H1) 15| South Africa 710060009 175234 2022.07.01 00:00 2022.12.06 11:30 1.6 sRequest2 (EURUSD,H1) 16| Spain 724010005 159815 2022.07.01 00:00 2022.12.23 10:00 0.1 sRequest2 (EURUSD,H1) 17| Sweden 752010019 171381 2022.07.01 00:00 2022.11.29 09:00 0.6 sRequest2 (EURUSD,H1) 18| Switzerland 756040001 159276 2022.07.01 00:00 2022.11.29 10:00 0.2 sRequest2 (EURUSD,H1) 19| United Kingdom 826010037 157175 2022.07.01 00:00 2022.12.22 09:00 -0.3 sRequest2 (EURUSD,H1) 20| United States 840010007 163419 2022.07.01 00:00 2022.12.22 15:30 3.2 sRequest2 (EURUSD,H1) 21| European Union 999030016 158838 2022.07.01 00:00 2022.12.07 12:00 0.3
Even though the problem was solved in several approaches, the possibility of including one query in another made it much easier.
Conclusion
I hope that the article will arouse interest among those traders and developers who use macroeconomic data to create their strategies. I would also dare to suggest that no macro parameters allow building a good strategy. However, they may be useful as an addition to the original neural network data.