97 lines
5.2 KiB
MQL5
97 lines
5.2 KiB
MQL5
//+------------------------------------------------------------------+
|
|
//| DBQuotesIntradayLag.mq5 |
|
|
//| Copyright 2022, MetaQuotes Ltd. |
|
|
//| https://www.mql5.com |
|
|
//+------------------------------------------------------------------+
|
|
#property description "Display quotes imported into a database, with LAGs.\nUse DBquotesImport.mq5 to generate and populate the database beforehand."
|
|
#property script_show_inputs
|
|
|
|
#include "..\..\Include\DBSQLite.mqh"
|
|
#include "..\..\Include\Periods.mqh"
|
|
|
|
//+------------------------------------------------------------------+
|
|
//| Inputs |
|
|
//+------------------------------------------------------------------+
|
|
input string Database = "MQL5Book/DB/Quotes";
|
|
input datetime SubsetStart = D'2022.01.01';
|
|
input datetime SubsetStop = D'2023.01.01';
|
|
input int Limit = 10;
|
|
|
|
const string Table = "MqlRatesDB";
|
|
|
|
#resource "DBQuotesIntradayLag.sql" as string sql1
|
|
|
|
/*
|
|
Copy & paste example for SQL query in MetaEditor DB viewer
|
|
|
|
SELECT
|
|
DATETIME(time, 'unixepoch') as datetime,
|
|
time,
|
|
TIME(time, 'unixepoch') AS intraday,
|
|
STRFTIME('%w', time, 'unixepoch') AS day,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) AS delta,
|
|
SIGN(open - LAG(open) OVER (ORDER BY time)) AS direction,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) * (open - LAG(open) OVER (ORDER BY time)) AS product,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) * SIGN(open - LAG(open) OVER (ORDER BY time)) AS estimate
|
|
FROM MqlRatesDB
|
|
WHERE (time >= STRFTIME('%s', '2015-01-01') AND time < STRFTIME('%s', '2021-01-01'))
|
|
*/
|
|
|
|
//+------------------------------------------------------------------+
|
|
//| Script program start function |
|
|
//+------------------------------------------------------------------+
|
|
void OnStart()
|
|
{
|
|
Print("");
|
|
DBSQLite db(Database + _Symbol + PeriodToString());
|
|
if(!PRTF(db.isOpen())) return;
|
|
if(!PRTF(db.hasTable(Table))) return;
|
|
|
|
// custom "preparation" of SQL-query for formatting
|
|
string sqlrep = sql1;
|
|
// single percent sign would be consumed by StringFormat,
|
|
// we need to preserve it 'as is' for proper SQL execution
|
|
StringReplace(sqlrep, "%", "%%");
|
|
StringReplace(sqlrep, "?1", "%ld");
|
|
StringReplace(sqlrep, "?2", "%ld");
|
|
StringReplace(sqlrep, "?3", "%d");
|
|
|
|
// actual parameter substitution
|
|
const string sqlfmt = StringFormat(sqlrep, SubsetStart, SubsetStop, Limit);
|
|
Print(sqlfmt);
|
|
|
|
// SQL-query execution and print out
|
|
DatabasePrint(db.getHandle(), sqlfmt, 0);
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
/*
|
|
|
|
db.isOpen()=true / ok
|
|
db.hasTable(Table)=true / ok
|
|
SELECT
|
|
DATETIME(time, 'unixepoch') as datetime,
|
|
time,
|
|
TIME(time, 'unixepoch') AS intraday,
|
|
STRFTIME('%w', time, 'unixepoch') AS day,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) AS delta,
|
|
SIGN(open - LAG(open) OVER (ORDER BY time)) AS direction,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) * (open - LAG(open) OVER (ORDER BY time)) AS product,
|
|
(LAG(open,-1) OVER (ORDER BY time) - open) * SIGN(open - LAG(open) OVER (ORDER BY time)) AS estimate
|
|
FROM MqlRatesDB
|
|
WHERE (time >= 1640995200 AND time < 1672531200)
|
|
ORDER BY time LIMIT 10;
|
|
#| datetime open time intraday day delta direction product estimate
|
|
--+--------------------------------------------------------------------------------------------------------------------------------
|
|
1| 2022-01-03 00:00:00 1.13693 1641168000 00:00:00 1 0.000320000000000098
|
|
2| 2022-01-03 01:00:00 1.13725 1641171600 01:00:00 1 2.99999999999745e-05 1 9.59999999999478e-09 2.99999999999745e-05
|
|
3| 2022-01-03 02:00:00 1.13728 1641175200 02:00:00 1 -0.00106000000000006 1 -3.17999999999748e-08 -0.00106000000000006
|
|
4| 2022-01-03 03:00:00 1.13622 1641178800 03:00:00 1 -0.000340000000000007 -1 3.60400000000028e-07 0.000340000000000007
|
|
5| 2022-01-03 04:00:00 1.13588 1641182400 04:00:00 1 -0.00157999999999991 -1 5.37199999999982e-07 0.00157999999999991
|
|
6| 2022-01-03 05:00:00 1.1343 1641186000 05:00:00 1 0.000529999999999919 -1 -8.37399999999827e-07 -0.000529999999999919
|
|
7| 2022-01-03 06:00:00 1.13483 1641189600 06:00:00 1 -0.000769999999999937 1 -4.08099999999905e-07 -0.000769999999999937
|
|
8| 2022-01-03 07:00:00 1.13406 1641193200 07:00:00 1 -0.000260000000000149 -1 2.00200000000098e-07 0.000260000000000149
|
|
9| 2022-01-03 08:00:00 1.1338 1641196800 08:00:00 1 0.00051000000000001 -1 -1.32600000000079e-07 -0.00051000000000001
|
|
10| 2022-01-03 09:00:00 1.13431 1641200400 09:00:00 1 0.000480000000000036 1 2.44800000000023e-07 0.000480000000000036
|
|
|
|
*/
|
|
//+------------------------------------------------------------------+
|