forked from airat77786/MQL5Book
222 lines
14 KiB
MQL5
222 lines
14 KiB
MQL5
 | //+------------------------------------------------------------------+
| ||
//| DBQuotesIntradayLagGroup.mq5 |
| |||
//| Copyright 2022, MetaQuotes Ltd. |
| |||
//| https://www.mql5.com |
| |||
//+------------------------------------------------------------------+
| |||
#property description "Display quotes groupped by LAGs at intraday time and day of week.\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'2015.01.01';
| |||
input datetime SubsetStop = D'2021.01.01';
| |||
| |||
const string Table = "MqlRatesDB";
| |||
| |||
#resource "DBQuotesIntradayLagGroup.sql" as string sql1
| |||
| |||
/*
| |||
Copy & paste example for SQL query in MetaEditor DB viewer
| |||
| |||
SELECT
| |||
AVG(product) / STDDEV(product) AS objective,
| |||
SUM(estimate) AS backtest_profit,
| |||
SUM(CASE WHEN estimate >= 0 THEN estimate ELSE 0 END) / SUM(CASE WHEN estimate < 0 THEN -estimate ELSE 0 END) AS backtest_PF,
| |||
intraday, day
| |||
FROM
| |||
(
| |||
SELECT
| |||
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'))
| |||
)
| |||
GROUP BY intraday, day
| |||
ORDER BY objective DESC
| |||
*/
| |||
| |||
//+------------------------------------------------------------------+
| |||
//| 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");
| |||
| |||
// actual parameter substitution
| |||
const string sqlfmt = StringFormat(sqlrep, SubsetStart, SubsetStop);
| |||
Print(sqlfmt);
| |||
| |||
// SQL-query execution and print out
| |||
DatabasePrint(db.getHandle(), sqlfmt, 0);
| |||
}
| |||
//+------------------------------------------------------------------+
| |||
/*
| |||
| |||
db.isOpen()=true / ok
| |||
db.hasTable(Table)=true / ok
| |||
SELECT
| |||
AVG(product) / STDDEV(product) AS objective,
| |||
SUM(estimate) AS backtest_profit,
| |||
SUM(CASE WHEN estimate >= 0 THEN estimate ELSE 0 END) / SUM(CASE WHEN estimate < 0 THEN -estimate ELSE 0 END) AS backtest_PF,
| |||
intraday, day
| |||
FROM
| |||
(
| |||
SELECT
| |||
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 >= 1420070400 AND time < 1609459200)
| |||
)
| |||
GROUP BY intraday, day
| |||
ORDER BY objective DESC
| |||
#| objective backtest_profit backtest_PF intraday day
| |||
---+---------------------------------------------------------------------------
| |||
1| 0.16713214428916 0.073200000000001 1.46040631486258 16:00:00 5
| |||
2| 0.118128291843983 0.0433099999999995 1.33678071539657 20:00:00 3
| |||
3| 0.103701251751617 0.00929999999999853 1.14148790506616 05:00:00 2
| |||
4| 0.102930330078208 0.0164399999999973 1.1932071923845 08:00:00 4
| |||
5| 0.089531492651001 0.0064300000000006 1.10167615433271 07:00:00 2
| |||
6| 0.0827628326995007 -8.99999999970369e-05 0.999601152226913 17:00:00 4
| |||
7| 0.0823433025146974 0.0159700000000012 1.21665988332657 21:00:00 1
| |||
8| 0.0767938336191962 0.00522999999999874 1.04226945769012 13:00:00 1
| |||
9| 0.0657741522256548 0.0162299999999986 1.09699976093712 15:00:00 2
| |||
10| 0.0635243373432768 0.00932000000000044 1.08294766820933 22:00:00 3
| |||
11| 0.0623455237646223 0.0154000000000003 1.10044351682755 15:00:00 1
| |||
12| 0.0611311003782229 0.0191399999999957 1.10094404303568 17:00:00 3
| |||
13| 0.0603091366315941 0.0277700000000005 1.41929639136343 05:00:00 5
| |||
14| 0.0585389379786665 0.00334999999999996 1.03894443152755 04:00:00 3
| |||
15| 0.057377314038757 0.0193600000000012 1.23684854416444 04:00:00 4
| |||
16| 0.0573063422330552 0.0117299999999985 1.08600337268127 12:00:00 4
| |||
17| 0.0572803333187238 0.0144899999999994 1.28108632395731 06:00:00 5
| |||
18| 0.0543313045372004 0.0231100000000006 1.13573358393046 10:00:00 2
| |||
19| 0.0522234308193375 0.00711999999999868 1.08198986642099 08:00:00 3
| |||
20| 0.0509042604023954 0.0146599999999975 1.09737628694784 11:00:00 4
| |||
21| 0.050544127390581 0.0134200000000027 1.11372881355935 12:00:00 2
| |||
22| 0.0505094536724595 0.0120799999999981 1.175555878506 05:00:00 3
| |||
23| 0.0492348110892364 -0.00231000000000092 0.962711864406766 06:00:00 3
| |||
24| 0.0479673290683895 0.00780000000000114 1.12751348700346 02:00:00 2
| |||
25| 0.0462716009204473 0.0299700000000018 1.18895403820693 16:00:00 1
| |||
26| 0.0458139381349133 0.00218999999999991 1.01262466132472 21:00:00 3
| |||
27| 0.0408907469745224 0.000309999999998034 1.0049871299871 07:00:00 3
| |||
28| 0.0393633749488827 -0.00718000000000019 0.924221635883902 03:00:00 5
| |||
29| 0.0369741494098486 -0.0319400000000005 0.870173156653929 16:00:00 4
| |||
30| 0.0353247937148132 0.0161000000000002 1.08557457212714 16:00:00 3
| |||
31| 0.0321292435692497 0.00850000000000062 1.06220270764728 13:00:00 4
| |||
32| 0.0305136698247891 0.0232900000000011 1.20561490244549 21:00:00 4
| |||
33| 0.0289062786935775 0.0255699999999999 1.21062602965404 13:00:00 2
| |||
34| 0.0271064989563544 -0.0347399999999973 0.778344924392285 09:00:00 3
| |||
35| 0.0266151766395079 -0.0027100000000011 0.981655723278947 11:00:00 5
| |||
36| 0.0257887732684008 0.00848999999999878 1.0626892121391 09:00:00 5
| |||
37| 0.0245688226705506 0.00143000000000026 1.02301255230126 07:00:00 5
| |||
38| 0.0239247828463491 -0.0229999999999997 0.842541247347165 13:00:00 5
| |||
39| 0.0214966998043054 -0.0251300000000025 0.869914069779468 15:00:00 3
| |||
40| 0.0208336620016311 0.00290999999999753 1.02191430077564 12:00:00 1
| |||
41| 0.0206033857840952 -0.00157000000000052 0.991458571350849 16:00:00 2
| |||
42| 0.0198926317510929 -0.000850000000000684 0.988288784789189 02:00:00 4
| |||
43| 0.0182102258283443 -0.00463000000000169 0.960376551133918 19:00:00 1
| |||
44| 0.0161172998833366 0.0147599999999994 1.17186772240335 04:00:00 2
| |||
45| 0.0149682763181311 -0.0395100000000017 0.84912937223155 15:00:00 4
| |||
46| 0.0145351167678307 0.00109999999999699 1.00680608835538 10:00:00 3
| |||
47| 0.0115935896337062 -0.00084000000000084 0.98976109215016 23:00:00 3
| |||
48| 0.0101605494765125 -0.0217299999999982 0.886224409654964 14:00:00 4
| |||
49| 0.00958770083330551 0.00705999999999918 1.13908589440503 01:00:00 3
| |||
50| 0.00608558150441834 0.00773000000000157 1.13345994475141 07:00:00 1
| |||
51| 0.00492752747188311 0.0188500000000016 1.24222564893346 04:00:00 1
| |||
52| 0.00119434960576392 0.0206499999999974 1.18782972530469 19:00:00 5
| |||
53| -0.000838154722261324 0.0116900000000013 1.05888575458393 10:00:00 4
| |||
54| -0.00202838775890642 -0.00484999999999958 0.923969274180913 07:00:00 4
| |||
55| -0.00211381131508719 0.0123100000000016 1.06296353127718 17:00:00 2
| |||
56| -0.00295885735463623 0.0110399999999997 1.19031201516979 01:00:00 4
| |||
57| -0.00300915367363466 -0.0132099999999999 0.872305461575641 21:00:00 2
| |||
58| -0.00442017218158207 0.012690000000003 1.09391651865011 14:00:00 2
| |||
59| -0.00454035248777325 0.00692999999999855 1.04569130348783 09:00:00 2
| |||
60| -0.00597400371437388 -0.00529000000000268 0.964899475814461 18:00:00 5
| |||
61| -0.00739364354810872 -0.0152200000000016 0.881951446521357 14:00:00 1
| |||
62| -0.00901765750534236 -0.0267299999999984 0.774316109422505 22:00:00 5
| |||
63| -0.00930357306364694 -0.0085399999999991 0.86239123428941 23:00:00 1
| |||
64| -0.00943902403661846 -0.0468 0.747545582047685 23:00:00 5
| |||
65| -0.0154584740504693 0.00992000000000326 1.09304943251105 03:00:00 1
| |||
66| -0.0157180125534758 -0.00272999999999612 0.978291984732855 12:00:00 3
| |||
67| -0.0170471590021677 -0.0230099999999998 0.695070235886563 23:00:00 2
| |||
68| -0.0183799552509626 -0.0104800000000005 0.921456943715802 13:00:00 3
| |||
69| -0.0209693895581613 0.00687999999999778 1.05500919485086 14:00:00 3
| |||
70| -0.0211148080619913 0.00145000000000417 1.01785934228358 22:00:00 2
| |||
71| -0.022375359030735 -0.0162200000000015 0.798007471980061 02:00:00 5
| |||
72| -0.0276963976069807 -0.00566999999999873 0.931405758528929 03:00:00 2
| |||
73| -0.0297160844533128 -0.0166299999999993 0.886158269441406 20:00:00 4
| |||
74| -0.0301792471418656 0.011540000000001 1.08227577356339 09:00:00 1
| |||
75| -0.0332789718520231 -0.00461000000000089 0.92851604899983 01:00:00 5
| |||
76| -0.033767151799259 -0.00590000000000157 0.895036470378908 01:00:00 2
| |||
77| -0.0351809058498753 -0.0296499999999968 0.679494108745024 04:00:00 5
| |||
78| -0.0358166251670762 -0.0191800000000029 0.743102062684134 05:00:00 1
| |||
79| -0.038052881227643 -0.0114300000000001 0.838399547575284 23:00:00 4
| |||
80| -0.038397862746323 -0.0167500000000012 0.889875082182767 11:00:00 3
| |||
81| -0.039724911138701 -0.00453999999999999 0.940513626834382 22:00:00 1
| |||
82| -0.0408406951795748 -0.0580999999999992 0.804961562993053 15:00:00 5
| |||
83| -0.0443689865111622 -0.0186799999999998 0.78261375538229 05:00:00 4
| |||
84| -0.0448716595913865 0.00143999999999878 1.01407074457689 20:00:00 5
| |||
85| -0.044984167587657 -0.0308200000000005 0.776666666666663 20:00:00 2
| |||
86| -0.0463706489926068 -0.00487999999999933 0.979101537407394 17:00:00 5
| |||
87| -0.0519635249552873 -0.0152199999999982 0.861837327523617 20:00:00 1
| |||
88| -0.0525362253765298 -0.0242299999999984 0.851468154232828 18:00:00 2
| |||
89| -0.0532284987864957 -0.0200199999999997 0.864802809292276 18:00:00 1
| |||
90| -0.0533104397953623 0.00575000000000125 1.06522972206468 01:00:00 1
| |||
91| -0.0553214020292255 -3.00000000008627e-05 0.999647556390967 08:00:00 1
| |||
92| -0.0587282865228479 -0.00770000000000004 0.947321611821851 11:00:00 1
| |||
93| -0.0606411180940267 -0.0611000000000004 0.647594878301995 09:00:00 4
| |||
94| -0.0606727956038308 -0.047079999999998 0.697778918988326 18:00:00 3
| |||
95| -0.0658053715209465 -0.0170099999999997 0.90405009025271 18:00:00 4
| |||
96| -0.0660313886783148 -0.00735000000000152 0.958920187793419 17:00:00 1
| |||
97| -0.0663811860556746 -0.014619999999999 0.92068572668584 11:00:00 2
| |||
98| -0.066476045086213 -0.0240500000000008 0.654900272635954 00:00:00 4
| |||
99| -0.0666325857658802 -0.0165500000000016 0.915776081424929 10:00:00 1
| |||
100| -0.0691986027849773 -0.0189199999999987 0.760506329113941 02:00:00 3
| |||
101| -0.0697287093075506 -0.00283999999999929 0.952563888424931 06:00:00 1
| |||
102| -0.0698406627783849 -0.0243999999999995 0.873823559830388 10:00:00 5
| |||
103| -0.0700099737151066 0.00629999999999775 1.07211538461536 03:00:00 3
| |||
104| -0.0734717138519144 -0.00658999999999721 0.951040118870748 19:00:00 2
| |||
105| -0.0750612413078805 -0.00437000000000043 0.95588532202705 03:00:00 4
| |||
106| -0.0759898392818295 -0.00520000000000231 0.919728311207127 06:00:00 2
| |||
107| -0.0775250903401206 -0.0100600000000002 0.923730098559513 14:00:00 5
| |||
108| -0.0800435048271025 -0.0124500000000001 0.905688962957352 19:00:00 3
| |||
109| -0.0804303804315245 -0.0245099999999987 0.764440172993765 08:00:00 5
| |||
110| -0.0814131025461459 -0.0189100000000015 0.820605255668329 21:00:00 5
| |||
111| -0.0899571263478305 -0.0321900000000028 0.721250432975386 22:00:00 4
| |||
112| -0.0909772560603298 -0.0226100000000016 0.851161872161138 19:00:00 4
| |||
113| -0.0961794181717023 -0.00846999999999931 0.936377976414036 12:00:00 5
| |||
114| -0.108868074018582 -0.0246099999999998 0.634920634920637 00:00:00 5
| |||
115| -0.109368419185336 -0.0250700000000013 0.744496534855268 08:00:00 2
| |||
116| -0.121893581607986 -0.0234599999999998 0.610945273631843 00:00:00 3
| |||
117| -0.135416609546408 -0.0898899999999971 0.343437294573087 00:00:00 1
| |||
118| -0.142128458003631 -0.0255200000000018 0.681835182645536 06:00:00 4
| |||
119| -0.142196924506816 -0.0205700000000004 0.629769618430515 00:00:00 2
| |||
120| -0.15200009633513 -0.0301499999999988 0.708864426419475 02:00:00 1
| |||
| |||
*/
| |||
//+------------------------------------------------------------------+
|