How can I calculate portfolio return in MySQL?

Question

I am stuck on a MySQL problem. I am trying to calculate the return series of a portfolio using:

 for(i = startdate+1; i <= enddate; i++) {
   return[i]=0;
   for(n = 0;  n < count(instruments); n++) {
     return[i] += price[i,n] / price[i-1, n] * weight[n];
   }
 }

So, the return of portfolio today is calculated as a sum of price_today/price_yesterday*weight over the instruments in the portfolio.

I created a scribble at http://rextester.com/FUC35243.

If it doesn't work, the code is:

DROP TABLE IF EXISTS x_ports;
DROP TABLE IF EXISTS x_weights;
DROP TABLE IF EXISTS x_prices;

CREATE TABLE IF NOT EXISTS x_ports (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_weights (id INT NOT NULL AUTO_INCREMENT, port_id INT, inst_id INT, weight DOUBLE, PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_prices (id INT NOT NULL AUTO_INCREMENT, inst_id INT, trade_date DATE, price DOUBLE, PRIMARY KEY (id));

INSERT INTO x_ports (name) VALUES ('PORT A');
INSERT INTO x_ports (name) VALUES ('PORT B');

INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 1, 20.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 2, 80.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (2, 1, 100.0);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-01', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-02', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-03', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-04', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-05', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-06', 1.14);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-01', 50.23);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-02', 50.45);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-03', 50.30);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-04', 50.29);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-05', 50.40);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-06', 50.66);


# GETTING THE DATES

SET @DtShort='2018-01-01';
SET @DtLong=@DtShort;

SELECT
    @DtShort:=@DtLong as date_prev,
    @DtLong:=dt.trade_date as date_curent
FROM
    (SELECT DISTINCT trade_date FROM x_prices ORDER BY trade_date) dt;


# GETTING RETURN FOR SINGLE DAY

SET @DtToday='2018-01-03';
SET @DtYesterday='2018-01-02';

SELECT
    x2.trade_date,
    x2.portfolio,
    sum(x2.val*x2.weight)/sum(x2.weight) as ret
FROM

    (SELECT
        x1.trade_date, 
        x1.portfolio,
        sum(x1.weight)/2.0 as weight,
        sum(x1.val_end)/sum(x1.val_start) as val, 
        sum(x1.val_start) as val_start,
        sum(x1.val_end) as val_end
    FROM

        (SELECT
            @DtToday as trade_date,
            prt.name as portfolio,
            wts.inst_id as iid,
            wts.weight,
            if(prc.trade_date=@DtToday,prc.price*wts.weight,0) as val_start,
            if(prc.trade_date=@DtYesterday,prc.price*wts.weight,0) as val_end
        FROM
            x_ports prt,
            x_weights wts,
            x_prices prc
        WHERE
            wts.port_id=prt.id and 
            prc.inst_id=wts.inst_id and
            (prc.trade_date=@DtToday or prc.trade_date=@DtYesterday)) x1

    GROUP BY x1.portfolio) x2

GROUP BY x2.portfolio;

I hope to be able to produce a result looking like this:

Date        Port A      Port B
--------------------------------------------
01/01/2010      
02/01/2010  1.005289596 1.004379853
03/01/2010  0.995851496 0.997026759
04/01/2010  0.999840954 0.999801193
05/01/2010  1.003535565 1.002187314
06/01/2010  1.005896896 1.00515873

The return for Port A on the 2/1/2018 should be calculated as 1.13/1.12*20/(20+80) + 50.45/50.23*80/(20+80).

The return for Port B on the 2/1/2018 should be calculated as 50.45/50.23*100/100, or possibly 1.13/1.12*0/(0+100) + 50.45/50.23*100/(0+100).

FYI, in the looping function above, I only calculate at the nominator (or the unscaled weight) so that Port A would be calculated as 1.13/1.12*20+50.45/50.23*80, which I see as the crucial step when calculating the return. The return is then found by dividing it by the sum of the weight.

Though it certainly can be done better, I can get the dates and I can calculate the return of a single day, but I just can't put the two together.

Answer 1

Simulating analytics is no fun! Demo

The math on this doesn't seem right to me; as I'm no where close to your 'looks like results'

I'd like to be able to reuse CurDay but as the version is lower I couldn't use a common table expression.

What this does:

• X1 generate the join of the tables
• X2 gives us a count of the instruments in a portfolio used later in math
• r generates a uservariable on which we can assign rows @rn and @Rn2
• CurDay generate a rownumber ordered correctly so we can join
• NextDay generates a copy of CurDay so we can join curday to next day on RN+1
• Z allows us to do the math and group by current day and prepare for pivot on the portfolio name.
• Outer most select allows us to pivot the data so we have date+2 columns

.

 SELECT Z.Trade_Date
     , sum(case when name = 'Port A' then P_RETURN end) as PortA
     , sum(case when name = 'Port B' then P_RETURN end) as PortB
FROM (
## Raw data
SELECT CurDay.*, NextDay.Price/CurDay.Price*CurDay.Weight/CurDay.Inst_Total_Weight as P_Return
FROM (SELECT x1.*, @RN:=@RN+1 rn,x2.inst_cnt, x2.Inst_Total_Weight
      FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
            FROM x_ports Prt
            INNER JOIN x_weights W
              on W.Port_ID = prt.ID
            INNER JOIN x_prices Prc
              on Prc.INST_ID = W.INST_ID
            ORDER BY W.port_id, W.inst_id,trade_Date) x1
     CROSS join (SELECT @RN:=0) r
     INNER join (SELECT count(*) inst_Cnt, port_ID, sum(Weight) as Inst_Total_Weight 
                 FROM x_weights
                 GROUP BY Port_ID) x2
        on X1.Port_ID = X2.Port_ID) CurDay
LEFT JOIN (SELECT x1.*, @RN2:=@RN2+1 rn2
           FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
                 FROM x_ports Prt
                 INNER JOIN x_weights W
                   on W.Port_ID = prt.ID
                 INNER JOIN x_prices Prc
                   on Prc.INST_ID = W.INST_ID
                 ORDER BY W.port_id, W.inst_id,trade_Date) x1
                 CROSS join (SELECT @RN2:=0) r
           ) NextDay
   on NextDay.Port_ID = CurDay.Port_ID
  and NextDay.Inst_ID = curday.Inst_ID
  and NextDay.RN2 = CurDay.RN+1
GROUP BY CurDay.Port_ID,  CurDay.Inst_ID,  CurDay.Trade_Date) Z
##END RAW DATA
GROUP BY Trade_Date;




+----+---------------------+-------------------+-------------------+
|    |     Trade_Date      |       PortA       |       PortB       |
+----+---------------------+-------------------+-------------------+
|  1 | 01.01.2018 00:00:00 | 1,00528959642786  | 1,00892857142857  |
|  2 | 02.01.2018 00:00:00 | 0,995851495829569 | 0,991150442477876 |
|  3 | 03.01.2018 00:00:00 | 0,999840954274354 | 1                 |
|  4 | 04.01.2018 00:00:00 | 1,0035355651507   | 1,00892857142857  |
|  5 | 05.01.2018 00:00:00 | 1,00589689563141  | 1,00884955752212  |
|  6 | 06.01.2018 00:00:00 | NULL              | NULL              |
+----+---------------------+-------------------+-------------------+