MQL5Book/Scripts/p7/DBQuotesIntradayBackAndForward.mq5

266 lines
20 KiB
MQL5
Raw Permalink Normal View History

2025-05-30 16:09:41 +02:00
//+------------------------------------------------------------------+
//| DBQuotesIntradayBackAndForward.mq5 |
//| Copyright 2022, MetaQuotes Ltd. |
//| https://www.mql5.com |
//+------------------------------------------------------------------+
#property description "Display profit estimation on backtest and forward tests performed on 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 BacktestStart = D'2015.01.01';
input datetime ForwardStart = D'2021.01.01';
const string Table = "MqlRatesDB";
#resource "DBQuotesIntradayBackAndForward.sql" as string sql1
/*
Copy & paste example for SQL query in MetaEditor DB viewer
SELECT * FROM
(
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) * (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
) backtest
JOIN
(
SELECT
AVG(product) / STDDEV(product) AS objective2,
SUM(estimate) AS forward_profit,
SUM(CASE WHEN estimate >= 0 THEN estimate ELSE 0 END) / SUM(CASE WHEN estimate < 0 THEN -estimate ELSE 0 END) AS forward_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) * (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', '2021-01-01'))
)
GROUP BY intraday, day
) forward
USING(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, BacktestStart, ForwardStart, ForwardStart);
Print(sqlfmt);
// SQL-query execution and print out
DatabasePrint(db.getHandle(), sqlfmt, 0);
}
//+------------------------------------------------------------------+
/*
db.isOpen()=true / ok
db.hasTable(Table)=true / ok
SELECT * FROM
(
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) * (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
) backtest
JOIN
(
SELECT
SUM(estimate) AS forward_profit,
SUM(CASE WHEN estimate >= 0 THEN estimate ELSE 0 END) / SUM(CASE WHEN estimate < 0 THEN -estimate ELSE 0 END) AS forward_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) * SIGN(open - LAG(open) OVER (ORDER BY time)) AS estimate
FROM MqlRatesDB
WHERE (time >= 1609459200)
)
GROUP BY intraday, day
) forward
USING(intraday, day)
ORDER BY objective DESC
#| objective backtest_profit backtest_PF intraday day forward_profit forward_PF
---+-------------------------------------------------------------------------------------------------------------------
1| 0.16713214428916 0.073200000000001 1.46040631486258 16:00:00 5 0.00492000000000048 1.12852664576804
2| 0.118128291843983 0.0433099999999995 1.33678071539657 20:00:00 3 0.00788000000000055 1.277856135402
3| 0.103701251751617 0.00929999999999853 1.14148790506616 05:00:00 2 0.00221000000000082 1.12149532710285
4| 0.102930330078208 0.0164399999999973 1.1932071923845 08:00:00 4 0.00140999999999969 1.07253086419751
5| 0.089531492651001 0.0064300000000006 1.10167615433271 07:00:00 2 -0.00911999999999869 0.561749159058204
6| 0.0827628326995007 -8.99999999970369e-05 0.999601152226913 17:00:00 4 0.00907000000000091 1.18809622563254
7| 0.0823433025146974 0.0159700000000012 1.21665988332657 21:00:00 1 0.0025099999999999 1.12131464475591
8| 0.0767938336191962 0.00522999999999874 1.04226945769012 13:00:00 1 -0.00849000000000055 0.753913043478245
9| 0.0657741522256548 0.0162299999999986 1.09699976093712 15:00:00 2 0.0142399999999997 1.34979120609187
10| 0.0635243373432768 0.00932000000000044 1.08294766820933 22:00:00 3 -0.0045699999999993 0.828967065868293
11| 0.0623455237646223 0.0154000000000003 1.10044351682755 15:00:00 1 -0.020020000000001 0.568534482758611
12| 0.0611311003782229 0.0191399999999957 1.10094404303568 17:00:00 3 -0.00448999999999944 0.932389700346342
13| 0.0603091366315941 0.0277700000000005 1.41929639136343 05:00:00 5 -0.0029899999999996 0.837852494577024
14| 0.0585389379786665 0.00334999999999996 1.03894443152755 04:00:00 3 0.00153999999999987 1.07519531249999
15| 0.057377314038757 0.0193600000000012 1.23684854416444 04:00:00 4 -0.0151399999999986 0.514588008977275
16| 0.0573063422330552 0.0117299999999985 1.08600337268127 12:00:00 4 -0.00972999999999979 0.750640697078428
17| 0.0572803333187238 0.0144899999999994 1.28108632395731 06:00:00 5 0.000499999999999945 1.03192848020434
18| 0.0543313045372004 0.0231100000000006 1.13573358393046 10:00:00 2 0.019229999999998 1.42106415590097
19| 0.0522234308193375 0.00711999999999868 1.08198986642099 08:00:00 3 -0.0075500000000005 0.705423332032761
20| 0.0509042604023954 0.0146599999999975 1.09737628694784 11:00:00 4 0.00931000000000037 1.28349573690623
21| 0.050544127390581 0.0134200000000027 1.11372881355935 12:00:00 2 0.00378999999999963 1.1078849985767
22| 0.0505094536724595 0.0120799999999981 1.175555878506 05:00:00 3 0.00445000000000073 1.29845741113353
23| 0.0492348110892364 -0.00231000000000092 0.962711864406766 06:00:00 3 -0.00483999999999896 0.698066126013787
24| 0.0479673290683895 0.00780000000000114 1.12751348700346 02:00:00 2 0.00957000000000052 1.93639921722122
25| 0.0462716009204473 0.0299700000000018 1.18895403820693 16:00:00 1 -0.019829999999999 0.647278548559248
26| 0.0458139381349133 0.00218999999999991 1.01262466132472 21:00:00 3 0.0115300000000005 1.27354685646502
27| 0.0408907469745224 0.000309999999998034 1.0049871299871 07:00:00 3 0.000669999999999837 1.0384394721744
28| 0.0393633749488827 -0.00718000000000019 0.924221635883902 03:00:00 5 -0.00324999999999998 0.879718726868986
29| 0.0369741494098486 -0.0319400000000005 0.870173156653929 16:00:00 4 -0.0142600000000006 0.778192564940107
30| 0.0353247937148132 0.0161000000000002 1.08557457212714 16:00:00 3 -0.0183900000000006 0.674109516214764
31| 0.0321292435692497 0.00850000000000062 1.06220270764728 13:00:00 4 0.0109700000000008 1.40212609970678
32| 0.0305136698247891 0.0232900000000011 1.20561490244549 21:00:00 4 -0.010350000000001 0.639874739039643
33| 0.0289062786935775 0.0255699999999999 1.21062602965404 13:00:00 2 0.0141600000000007 1.5627980922099
34| 0.0271064989563544 -0.0347399999999973 0.778344924392285 09:00:00 3 -0.0066799999999998 0.820478366030643
35| 0.0266151766395079 -0.0027100000000011 0.981655723278947 11:00:00 5 0.0109199999999983 1.37956204379554
36| 0.0257887732684008 0.00848999999999878 1.0626892121391 09:00:00 5 -1.00000000000655e-05 0.999681933842237
37| 0.0245688226705506 0.00143000000000026 1.02301255230126 07:00:00 5 -0.0033999999999994 0.803126809496275
38| 0.0239247828463491 -0.0229999999999997 0.842541247347165 13:00:00 5 0.00840999999999936 1.29939480242076
39| 0.0214966998043054 -0.0251300000000025 0.869914069779468 15:00:00 3 -0.00419000000000047 0.929991645781112
40| 0.0208336620016311 0.00290999999999753 1.02191430077564 12:00:00 1 -0.01241 0.723423222643192
41| 0.0206033857840952 -0.00157000000000052 0.991458571350849 16:00:00 2 -0.0195599999999984 0.641429880843286
42| 0.0198926317510929 -0.000850000000000684 0.988288784789189 02:00:00 4 -0.00311999999999912 0.854748603351994
43| 0.0182102258283443 -0.00463000000000169 0.960376551133918 19:00:00 1 -0.00382999999999944 0.883657351154331
44| 0.0161172998833366 0.0147599999999994 1.17186772240335 04:00:00 2 0.00600999999999941 1.31765327695558
45| 0.0149682763181311 -0.0395100000000017 0.84912937223155 15:00:00 4 -0.0263600000000002 0.619295205083764
46| 0.0145351167678307 0.00109999999999699 1.00680608835538 10:00:00 3 -0.0100600000000002 0.775346136668152
47| 0.0115935896337062 -0.00084000000000084 0.98976109215016 23:00:00 3 -0.00368000000000013 0.800650054171171
48| 0.0101605494765125 -0.0217299999999982 0.886224409654964 14:00:00 4 0.00175999999999976 1.04548979064357
49| 0.00958770083330551 0.00705999999999918 1.13908589440503 01:00:00 3 -0.00507999999999986 0.693236714975862
50| 0.00608558150441834 0.00773000000000157 1.13345994475141 07:00:00 1 -0.00625999999999949 0.681100356597068
51| 0.00492752747188311 0.0188500000000016 1.24222564893346 04:00:00 1 0.00463000000000058 1.19037828947371
52| 0.00119434960576392 0.0206499999999974 1.18782972530469 19:00:00 5 -0.0048499999999978 0.834527465029073
53| -0.000838154722261324 0.0116900000000013 1.05888575458393 10:00:00 4 0.00574999999999859 1.13026733121882
54| -0.00202838775890642 -0.00484999999999958 0.923969274180913 07:00:00 4 -0.00330000000000008 0.83128834355828
55| -0.00211381131508719 0.0123100000000016 1.06296353127718 17:00:00 2 -0.00424999999999975 0.922289266776381
56| -0.00295885735463623 0.0110399999999997 1.19031201516979 01:00:00 4 -0.000290000000001012 0.980218281036766
57| -0.00300915367363466 -0.0132099999999999 0.872305461575641 21:00:00 2 0.00117000000000056 1.05058365758757
58| -0.00442017218158207 0.012690000000003 1.09391651865011 14:00:00 2 -0.00929000000000046 0.757060669456058
59| -0.00454035248777325 0.00692999999999855 1.04569130348783 09:00:00 2 -0.00823999999999925 0.812129502963995
60| -0.00597400371437388 -0.00529000000000268 0.964899475814461 18:00:00 5 0.000800000000001244 1.02171552660155
61| -0.00739364354810872 -0.0152200000000016 0.881951446521357 14:00:00 1 -0.00385999999999975 0.899792315680172
62| -0.00901765750534236 -0.0267299999999984 0.774316109422505 22:00:00 5 0.00232000000000032 1.11611611611614
63| -0.00930357306364694 -0.0085399999999991 0.86239123428941 23:00:00 1 0.00185999999999886 1.1510966693744
64| -0.00943902403661846 -0.0468 0.747545582047685 23:00:00 5 -0.0334499999999986 0.3552428681573
65| -0.0154584740504693 0.00992000000000326 1.09304943251105 03:00:00 1 -0.00683000000000078 0.78945745992599
66| -0.0157180125534758 -0.00272999999999612 0.978291984732855 12:00:00 3 -0.00719999999999976 0.771718452758407
67| -0.0170471590021677 -0.0230099999999998 0.695070235886563 23:00:00 2 -0.00379999999999892 0.755627009646364
68| -0.0183799552509626 -0.0104800000000005 0.921456943715802 13:00:00 3 0.00522000000000056 1.15086705202314
69| -0.0209693895581613 0.00687999999999778 1.05500919485086 14:00:00 3 -0.000919999999999366 0.971250000000019
70| -0.0211148080619913 0.00145000000000417 1.01785934228358 22:00:00 2 0.00246000000000068 1.1538461538462
71| -0.022375359030735 -0.0162200000000015 0.798007471980061 02:00:00 5 -0.00108999999999981 0.939712389380541
72| -0.0276963976069807 -0.00566999999999873 0.931405758528929 03:00:00 2 -0.000949999999998896 0.964150943396268
73| -0.0297160844533128 -0.0166299999999993 0.886158269441406 20:00:00 4 0.00743000000000027 1.30117551682206
74| -0.0301792471418656 0.011540000000001 1.08227577356339 09:00:00 1 0.00107999999999997 1.03134978229318
75| -0.0332789718520231 -0.00461000000000089 0.92851604899983 01:00:00 5 -0.0025400000000011 0.822625698323958
76| -0.033767151799259 -0.00590000000000157 0.895036470378908 01:00:00 2 7.0000000002457e-05 1.00585284280957
77| -0.0351809058498753 -0.0296499999999968 0.679494108745024 04:00:00 5 -0.00198999999999927 0.911239964317603
78| -0.0358166251670762 -0.0191800000000029 0.743102062684134 05:00:00 1 -0.000670000000001503 0.968003820439281
79| -0.038052881227643 -0.0114300000000001 0.838399547575284 23:00:00 4 -0.00961000000000034 0.568089887640445
80| -0.038397862746323 -0.0167500000000012 0.889875082182767 11:00:00 3 0.0152699999999998 1.46063348416289
81| -0.039724911138701 -0.00453999999999999 0.940513626834382 22:00:00 1 -0.0185900000000001 0.387882779058273
82| -0.0408406951795748 -0.0580999999999992 0.804961562993053 15:00:00 5 0.0119299999999998 1.21163739577789
83| -0.0443689865111622 -0.0186799999999998 0.78261375538229 05:00:00 4 -0.00776999999999961 0.738823529411779
84| -0.0448716595913865 0.00143999999999878 1.01407074457689 20:00:00 5 0.00618000000000096 1.23180795198804
85| -0.044984167587657 -0.0308200000000005 0.776666666666663 20:00:00 2 -0.00129000000000079 0.945454545454513
86| -0.0463706489926068 -0.00487999999999933 0.979101537407394 17:00:00 5 0.013159999999999 1.2471825694966
87| -0.0519635249552873 -0.0152199999999982 0.861837327523617 20:00:00 1 -0.00120999999999971 0.946436476316967
88| -0.0525362253765298 -0.0242299999999984 0.851468154232828 18:00:00 2 -0.01322 0.725896744764672
89| -0.0532284987864957 -0.0200199999999997 0.864802809292276 18:00:00 1 -0.0088199999999995 0.764862703279134
90| -0.0533104397953623 0.00575000000000125 1.06522972206468 01:00:00 1 0.0186700000000004 2.24466666666674
91| -0.0553214020292255 -3.00000000008627e-05 0.999647556390967 08:00:00 1 -0.00504000000000038 0.7618147448015
92| -0.0587282865228479 -0.00770000000000004 0.947321611821851 11:00:00 1 -0.0224199999999999 0.560994713138833
93| -0.0606411180940267 -0.0611000000000004 0.647594878301995 09:00:00 4 -0.000249999999998973 0.993981704381344
94| -0.0606727956038308 -0.047079999999998 0.697778918988326 18:00:00 3 -0.01135 0.739857895943161
95| -0.0658053715209465 -0.0170099999999997 0.90405009025271 18:00:00 4 0.01947 1.52721364744111
96| -0.0660313886783148 -0.00735000000000152 0.958920187793419 17:00:00 1 0.0168900000000005 1.51212856276532
97| -0.0663811860556746 -0.014619999999999 0.92068572668584 11:00:00 2 0.00511000000000006 1.12865055387714
98| -0.066476045086213 -0.0240500000000008 0.654900272635954 00:00:00 4 -0.00852999999999948 0.345356868764446
99| -0.0666325857658802 -0.0165500000000016 0.915776081424929 10:00:00 1 0.000679999999998904 1.0144037280237
100| -0.0691986027849773 -0.0189199999999987 0.760506329113941 02:00:00 3 -0.00666000000000067 0.619428571428557
101| -0.0697287093075506 -0.00283999999999929 0.952563888424931 06:00:00 1 -0.00201000000000051 0.887142055025236
102| -0.0698406627783849 -0.0243999999999995 0.873823559830388 10:00:00 5 -0.00396999999999958 0.919878910191734
103| -0.0700099737151066 0.00629999999999775 1.07211538461536 03:00:00 3 0.000419999999998866 1.02045786653672
104| -0.0734717138519144 -0.00658999999999721 0.951040118870748 19:00:00 2 0.00441999999999831 1.15552427867693
105| -0.0750612413078805 -0.00437000000000043 0.95588532202705 03:00:00 4 -0.00525000000000064 0.817136886102382
106| -0.0759898392818295 -0.00520000000000231 0.919728311207127 06:00:00 2 -0.00207000000000002 0.890069038767926
107| -0.0775250903401206 -0.0100600000000002 0.923730098559513 14:00:00 5 0.00632999999999839 1.14911660777381
108| -0.0800435048271025 -0.0124500000000001 0.905688962957352 19:00:00 3 0.00657999999999914 1.26028481012654
109| -0.0804303804315245 -0.0245099999999987 0.764440172993765 08:00:00 5 -0.00529000000000091 0.786865431103914
110| -0.0814131025461459 -0.0189100000000015 0.820605255668329 21:00:00 5 -0.0018800000000001 0.918579471632738
111| -0.0899571263478305 -0.0321900000000028 0.721250432975386 22:00:00 4 0.00919000000000092 1.44938875305629
112| -0.0909772560603298 -0.0226100000000016 0.851161872161138 19:00:00 4 0.00591999999999948 1.16403435854806
113| -0.0961794181717023 -0.00846999999999931 0.936377976414036 12:00:00 5 0.00716999999999968 1.26206140350876
114| -0.108868074018582 -0.0246099999999998 0.634920634920637 00:00:00 5 -0.00812999999999997 0.409586056644885
115| -0.109368419185336 -0.0250700000000013 0.744496534855268 08:00:00 2 -0.00333999999999979 0.845441925034718
116| -0.121893581607986 -0.0234599999999998 0.610945273631843 00:00:00 3 -0.00710000000000099 0.408333333333295
117| -0.135416609546408 -0.0898899999999971 0.343437294573087 00:00:00 1 -0.0282299999999989 0.126276694521833
118| -0.142128458003631 -0.0255200000000018 0.681835182645536 06:00:00 4 -0.000400000000000178 0.975874547647758
119| -0.142196924506816 -0.0205700000000004 0.629769618430515 00:00:00 2 -0.00465999999999966 0.507919746568069
120| -0.15200009633513 -0.0301499999999988 0.708864426419475 02:00:00 1 0.00109000000000137 1.06385471587589
*/
//+------------------------------------------------------------------+