forked from rosh/MQL5Book
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
| |
| |
*/
| |
//+------------------------------------------------------------------+
|