//+------------------------------------------------------------------+ //| 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 */ //+------------------------------------------------------------------+