PinjHvT3

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	
8DROP TABLE IF EXISTS x_ports;
9DROP TABLE IF EXISTS x_weights;
10DROP TABLE IF EXISTS x_prices;
11
12CREATE TABLE IF NOT EXISTS x_ports (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));
13CREATE TABLE IF NOT EXISTS x_weights (id INT NOT NULL AUTO_INCREMENT, port_id INT, inst_id INT, weight DOUBLE, PRIMARY KEY (id));
14CREATE TABLE IF NOT EXISTS x_prices (id INT NOT NULL AUTO_INCREMENT, inst_id INT, trade_date DATE, price DOUBLE, PRIMARY KEY (id));
15
16INSERT INTO x_ports (name) VALUES ('PORT A');
17INSERT INTO x_ports (name) VALUES ('PORT B');
18
19INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 1, 20.0);
20INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 2, 80.0);
21INSERT INTO x_weights (port_id, inst_id, weight) VALUES (2, 1, 100.0);
22
23INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-01', 1.12);
24INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-02', 1.13);
25INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-03', 1.12);
26INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-04', 1.12);
27INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-05', 1.13);
28INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-06', 1.14);
29
30INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-01', 50.23);
31INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-02', 50.45);
32INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-03', 50.30);
33INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-04', 50.29);
34INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-05', 50.40);
35INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-06', 50.66);
36
37
38# GETTING THE DATES
39
40SET @DtShort='2018-01-01';
41SET @DtLong=@DtShort;
42
43SELECT
44    @DtShort:=@DtLong as date_prev,
45    @DtLong:=dt.trade_date as date_curent
46FROM
47    (SELECT DISTINCT trade_date FROM x_prices ORDER BY trade_date) dt;
48
49
50# GETTING RETURN FOR SINGLE DAY
51
52SET @DtToday='2018-01-03';
53SET @DtYesterday='2018-01-02';
54
55SELECT
56    x2.trade_date,
57    x2.portfolio,
58    sum(x2.val*x2.weight)/sum(x2.weight) as ret
59FROM
60
61    (SELECT
62        x1.trade_date, 
63        x1.portfolio,
64        sum(x1.weight)/2.0 as weight,
65        sum(x1.val_end)/sum(x1.val_start) as val, 
66        sum(x1.val_start) as val_start,
67        sum(x1.val_end) as val_end
68    FROM
69
70        (SELECT
71            @DtToday as trade_date,
72            prt.name as portfolio,
73            wts.inst_id as iid,
74            wts.weight,
75            if(prc.trade_date=@DtToday,prc.price*wts.weight,0) as val_start,
76            if(prc.trade_date=@DtYesterday,prc.price*wts.weight,0) as val_end
77        FROM
78            x_ports prt,
79            x_weights wts,
80            x_prices prc
81        WHERE
82            wts.port_id=prt.id and 
83            prc.inst_id=wts.inst_id and
84            (prc.trade_date=@DtToday or prc.trade_date=@DtYesterday)) x1
85
86    GROUP BY x1.portfolio) x2
87
88GROUP BY x2.portfolio;