CSjAtf6b

1for(i = startdate+1; i <= enddate; i++) {
2   return[i]=0;
3   for(n = 0;  n < count(instruments); n++) {
4     return[i] += price[i,n] / price[i-1, n] * weight[n];
5   }
6 }
7	
8|date       |Port A    |Port B  |
9 |-------------------------------|
10 |2018-01-01 |1.014756  |1.013   |
11 |-------------------------------|
12 |2018-01-02 |1.016847  |1.01124 |
13  ...
14	
15DROP TABLE IF EXISTS x_ports;
16DROP TABLE IF EXISTS x_weights;
17DROP TABLE IF EXISTS x_prices;
18
19CREATE TABLE IF NOT EXISTS x_ports (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));
20CREATE TABLE IF NOT EXISTS x_weights (id INT NOT NULL AUTO_INCREMENT, port_id INT, inst_id INT, weight DOUBLE, PRIMARY KEY (id));
21CREATE TABLE IF NOT EXISTS x_prices (id INT NOT NULL AUTO_INCREMENT, inst_id INT, trade_date DATE, price DOUBLE, PRIMARY KEY (id));
22
23INSERT INTO x_ports (name) VALUES ('PORT A');
24INSERT INTO x_ports (name) VALUES ('PORT B');
25
26INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 1, 20.0);
27INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 2, 80.0);
28INSERT INTO x_weights (port_id, inst_id, weight) VALUES (2, 1, 100.0);
29
30INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-01', 1.12);
31INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-02', 1.13);
32INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-03', 1.12);
33INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-04', 1.12);
34INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-05', 1.13);
35INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-06', 1.14);
36
37INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-01', 50.23);
38INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-02', 50.45);
39INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-03', 50.30);
40INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-04', 50.29);
41INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-05', 50.40);
42INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-06', 50.66);
43
44
45# GETTING THE DATES
46
47SET @DtShort='2018-01-01';
48SET @DtLong=@DtShort;
49
50SELECT
51    @DtShort:=@DtLong as date_prev,
52    @DtLong:=dt.trade_date as date_curent
53FROM
54    (SELECT DISTINCT trade_date FROM x_prices ORDER BY trade_date) dt;
55
56
57# GETTING RETURN FOR SINGLE DAY
58
59SET @DtToday='2018-01-03';
60SET @DtYesterday='2018-01-02';
61
62SELECT
63    x2.trade_date,
64    x2.portfolio,
65    sum(x2.val*x2.weight)/sum(x2.weight) as ret
66FROM
67
68    (SELECT
69        x1.trade_date, 
70        x1.portfolio,
71        sum(x1.weight)/2.0 as weight,
72        sum(x1.val_end)/sum(x1.val_start) as val, 
73        sum(x1.val_start) as val_start,
74        sum(x1.val_end) as val_end
75    FROM
76
77        (SELECT
78            @DtToday as trade_date,
79            prt.name as portfolio,
80            wts.inst_id as iid,
81            wts.weight,
82            if(prc.trade_date=@DtToday,prc.price*wts.weight,0) as val_start,
83            if(prc.trade_date=@DtYesterday,prc.price*wts.weight,0) as val_end
84        FROM
85            x_ports prt,
86            x_weights wts,
87            x_prices prc
88        WHERE
89            wts.port_id=prt.id and 
90            prc.inst_id=wts.inst_id and
91            (prc.trade_date=@DtToday or prc.trade_date=@DtYesterday)) x1
92
93    GROUP BY x1.portfolio) x2
94
95GROUP BY x2.portfolio;
96	
97SELECT Z.Trade_Date
98     , max(case when name = 'Port A' then P_RETURN end) as PortA
99     , max(case when name = 'Port B' then P_RETURN end) as PortB
100FROM (
101## Raw data
102SELECT CurDay.*, sum(CurDay.Price/NextDay.Price*CurDay.Weight)/CurDay.Inst_Cnt as P_Return
103FROM (SELECT x1.*, @RN:=@RN+1 rn,x2.inst_cnt
104      FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
105            FROM x_ports Prt
106            INNER JOIN x_weights W
107              on W.Port_ID = prt.ID
108            INNER JOIN x_prices Prc
109              on Prc.INST_ID = W.INST_ID
110            ORDER BY W.port_id, W.inst_id,trade_Date) x1
111     CROSS join (SELECT @RN:=0) r
112     INNER join (SELECT count(*) inst_Cnt, port_ID 
113                 FROM x_weights
114                 GROUP BY Port_ID) x2
115        on X1.Port_ID = X2.Port_ID) CurDay
116LEFT JOIN (SELECT x1.*, @RN2:=@RN2+1 rn2
117           FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
118                 FROM x_ports Prt
119                 INNER JOIN x_weights W
120                   on W.Port_ID = prt.ID
121                 INNER JOIN x_prices Prc
122                   on Prc.INST_ID = W.INST_ID
123                 ORDER BY W.port_id, W.inst_id,trade_Date) x1
124                 CROSS join (SELECT @RN2:=0) r
125           ) NextDay
126   on NextDay.Port_ID = CurDay.Port_ID
127  and NextDay.Inst_ID = curday.Inst_ID
128  and NextDay.RN2+1 = CurDay.RN
129GROUP BY CurDay.Port_ID,  CurDay.Inst_ID,  CurDay.Trade_Date) Z
130##END RAW DATA
131GROUP BY Trade_Date;
132
133
134
135
136+---+---------------------+------------------+------------------+
137|   |     Trade_Date      |      PortA       |      PortB       |
138+---+---------------------+------------------+------------------+
139| 1 | 01.01.2018 00:00:00 | NULL             | NULL             |
140| 2 | 02.01.2018 00:00:00 | 40,1751941071073 | 100,892857142857 |
141| 3 | 03.01.2018 00:00:00 | 39,8810703666997 | 99,1150442477876 |
142| 4 | 04.01.2018 00:00:00 | 39,9920477137177 | 100              |
143| 5 | 05.01.2018 00:00:00 | 40,0874925432492 | 100,892857142857 |
144| 6 | 06.01.2018 00:00:00 | 40,2063492063492 | 100,884955752212 |
145+---+---------------------+------------------+------------------+