· 8 years ago · Jan 20, 2018, 04:26 AM
1-- QUERY - Cleaning the Import
2DELETE FROM Book1 WHERE C15 IN(
3 SELECT RunID from PrimusRuns
4);
5
6SELECT C15 FROM Book1 WHERE C15 IN(
7 SELECT RunID from PrimusRuns
8);
9
10SELECT * from PrimusRuns WHERE RunID = '38a16339-05cc-44f5-a1c3-d5a497b6330b'
11
12SELECT DATE(RunDateStart) FROM PrimusRuns GROUP BY DATE(RunDateStart)
13ORDER BY RunDateStart DESC
14
15-- NOT NEEDED ANYMORE
16INSERT INTO PrimusRuns (RunID, TransientName, RunDateStart, RunType, TestDateStart, Gross, CPS, Shares, MaxExposure, PercentWin, Duration)
17 SELECT RunId, TransientName, RunDateStart, RunType, TestDateFrom, GrossPnL, CPS, SharesTrader, MaxExposure, PercentageWinners, TimeFrame
18 FROM Runs;
19
20-- QUERY -- Inserting the Import
21INSERT INTO PrimusRuns (RunId, TransientName, RunDateStart, RunType, TestDateStart,
22 Gross, CPS, Shares, MaxExposure, PercentWin, Duration)
23SELECT a.C15, a.C1, JULIANDAY(a.C2), a.C3,JULIANDAY(a.C4),
24 a.C6, a.C8, a.C9, a.C10, a.C11, a.C14
25FROM Book1 as a;
26
27-- QUERY -- delete multiple day back tests
28DELETE FROM PrimusRuns WHERE Duration LIKE 'Se%'
29
30-- QUERY - Just Live Results
31SELECT TransientName, SUM(Gross), SUM(Shares) FROM PrimusRuns WHERE RunType = 'Live' GROUP BY TransientName
32
33
34-- QUERY - Remove Backtests with no live runs
35DELETE FROM PrimusRuns WHERE RunId IN (
36SELECT RunId FROM PrimusRuns t
37WHERE t.RunType = 'Backtesting'
38 AND NOT EXISTS (
39 SELECT 1
40 FROM PrimusRuns i
41 WHERE i.RunType = 'Live'
42 AND i.TransientName = t.TransientName
43 AND DATE(i.RunDateStart) = DATE(t.TestDateStart)));
44
45
46-- QUERY - Removing Duplicate Backtests
47DELETE FROM PrimusRuns WHERE RunID IN (
48SELECT MAX(RunID) FROM PrimusRuns
49WHERE RunType = 'Backtesting'
50GROUP BY TestDateStart, TransientName
51HAVING COUNT(*) > 1);
52
53SELECT DATE(RunDateStart),* FROM PrimusRuns WHERE RunType = 'Live'
54AND DATE(RunDateStart) > DATE('2018-01-01')
55
56
57-- QUERY -- Check if there are missing back tests
58select
59 DATE(t.RunDateStart) as dd,
60 t.TransientName
61from PrimusRuns t
62where t.RunType = 'Live' and DATE(t.RunDateStart) > DATE('2018-01-01') --AND DATE(t.RunDateStart) < DATE('2017-12-29')
63 and not exists (
64 select 1
65 from PrimusRuns i
66 where i.RunType = 'Backtesting'
67 and i.TransientName = t.TransientName
68 and DATE(i.TestDateStart) = DATE(t.RunDateStart)
69 ) ORDER BY dd
70
71SELECT * FROM PrimusRuns WHERE TransientName = 'OPG MM_News OPG MM_S_200_Sankalp Krishnan'
72AND DATE(TestDateStart) = DATE('2018-01-05')
73
74-- Create a query that does a live backtesting comparison only for boxes that have both live and backtest
75-- missing backtests can be re-done if the box still exists
76-- -- it will take a very long time to do this.
77
78-- you can have these boxes run automatically
79-- date has to still be set manually
80-- need to create a box location map
81SELECT A.TransientName, A.LiveGross, A.LiveShares, B.BTGross, B.BTShares FROM
82 (SELECT TransientName
83 , SUM(Gross) AS LiveGross
84 , SUM(Shares) AS LiveShares
85 FROM PrimusRuns
86 WHERE RunType = 'Live'
87 and DATE(RunDateStart) <= DATE('2018-12-31')
88 and DATE(RunDateStart) >= DATE('2018-01-03')
89 GROUP BY TransientName) As A
90 ,
91 (SELECT TransientName
92 , SUM(Gross) AS BTGross
93 ,SUM(Shares) AS BTShares
94 FROM PrimusRuns
95 WHERE RunType = 'Backtesting'
96 and DATE(TestDateStart) <= DATE('2018-12-31')
97 and DATE(TestDateStart) >= DATE('2018-01-03')
98 GROUP BY TransientName) AS B
99WHERE A.TransientName = B.TransientName ORDER BY B.TransientName
100
101-- Get all the transient names
102SELECT TransientName FROM PrimusRuns GROUP BY TransientName
103
104-- insert them into new table
105INSERT INTO TransientNames (TransientName)
106SELECT TransientName FROM PrimusRuns GROUP BY TransientName
107
108
109-- group by box id - all sizes will be combined
110SELECT MAX(TN.TransientName), A.BoxID As ID, A.LiveGross, A.LiveShares, B.BTGross, B.BTShares FROM
111 (SELECT LTN.BoxID
112 , SUM(Gross) AS LiveGross
113 , SUM(Shares) AS LiveShares
114 FROM PrimusRuns AS LPR
115 JOIN TransientNames AS LTN ON LPR.TransientName = LTN.TransientName
116 WHERE RunType = 'Live'
117 and DATE(RunDateStart) <= DATE('2017-12-31')
118 and DATE(RunDateStart) >= DATE('2017-02-01')
119 GROUP BY LTN.BoxID) As A
120 ,
121 (SELECT BTN.BoxID
122 , SUM(Gross) AS BTGross
123 ,SUM(Shares) AS BTShares
124 FROM PrimusRuns AS BPR
125 JOIN TransientNames AS BTN ON BPR.TransientName = BTN.TransientName
126 WHERE RunType = 'Backtesting'
127 and DATE(TestDateStart) <= DATE('2017-12-31')
128 and DATE(TestDateStart) >= DATE('2017-02-01')
129 GROUP BY BTN.BoxID) AS B
130JOIN TransientNames AS TN ON A.BoxID = TN.BoxID
131WHERE A.BoxID = B.BoxID
132GROUP BY TN.BoxID
133ORDER BY A.BoxID
134
135-- group by strategy id
136SELECT A.Strategy As ID, A.LiveGross, A.LiveShares, B.BTGross, B.BTShares FROM
137 (SELECT LBtS.StrategyID
138 , LS.Strategy
139 , SUM(Gross) AS LiveGross
140 , SUM(Shares) AS LiveShares
141 FROM PrimusRuns AS LPR
142 JOIN TransientNames AS LTN ON LPR.TransientName = LTN.TransientName
143 JOIN BoxIdtoStrategyId AS LBtS ON LBtS.BoxID = LTN.BoxID
144 JOIN Strategies as LS ON LS.ID = LBtS.StrategyID
145 WHERE RunType = 'Live'
146 and DATE(RunDateStart) <= DATE('2017-12-31')
147 and DATE(RunDateStart) >= DATE('2017-02-01')
148 GROUP BY LBtS.StrategyID) As A
149 ,
150 (SELECT BBtS.StrategyID
151 , SUM(Gross) AS BTGross
152 ,SUM(Shares) AS BTShares
153 FROM PrimusRuns AS BPR
154 JOIN TransientNames AS BTN ON BPR.TransientName = BTN.TransientName
155 JOIN BoxIdtoStrategyId AS BBtS ON BBtS.BoxID = BTN.BoxID
156 JOIN Strategies as BS ON BS.ID = BBtS.StrategyID
157 WHERE RunType = 'Backtesting'
158 and DATE(TestDateStart) <= DATE('2017-12-31')
159 and DATE(TestDateStart) >= DATE('2017-02-01')
160 GROUP BY BBtS.StrategyID) AS B
161WHERE A.StrategyID = B.StrategyID
162
163SELECT DATE(RunDateStart) FROM PrimusRuns WHERE RunType = 'Live'
164 GROUP BY RunDateStart ORDER BY RunDateStart DESC