CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Guillem
Guillem

Reputation: 125

Compounding Daily Returns SQL

I have a table which shows the simulated daily returns of different stocks. The variable date_simul is the date where the simulation was done, stock is the name of the stock simulated, N simul is the number of simulations for each stock (depending on the stock might be 1000, 5000 or 10000), simul is nth value simulated of the stock, FutureDate is the date where the stock is being simulated and Return, the daily return of the stock simulated in the future date.

SQL so far:

select date_simul, Stock, N Simu, FutureDate, Return
        exp(sum(ln(1+Return)) over (order by FutureDate asc)) - 1 as cumul
from portfolio
order by Stock, FutureDate;

I would like to get the cumulative return, day 1, (1 + r1) - 1, day 2, (1 + r1)*(1 + r2) - 1 and so on. Likewise, I wanted to use the fact that:

(1+r1)*(1+r2)*(1+r3) - 1 = exp(log(1+r1) +  log(1+r2) + log(1+r3)) - 1,

since a sum should be easier than a product. I have tried using the query above, but with no success.

Data:

date_simul|Stock|N Simu|FutureDate| Return
 30/09/22 |  A  | 1000 | 01/10/22 | -0,0073
 30/09/22 |  A  | 1000 | 02/09/22 | 0,0078
 30/09/22 |  A  | 1000 | 03/09/22 | 0,0296
 30/09/22 |  A  | 1000 | 04/09/22 | 0,0602
 30/09/22 |  A  | 1000 | 05/10/22 | -0,0177

Desired results:

date_simul|Stock|N Simu|FutureDate| Return | Cumul
 30/09/22 |  A  | 1000 | 01/10/22 | -0,0073| -0,0073
 30/09/22 |  A  | 1000 | 02/09/22 | 0,0078 | 0,0004
 30/09/22 |  A  | 1000 | 03/09/22 | 0,0296 | 0,0301
 30/09/22 |  A  | 1000 | 04/09/22 | 0,0602 | 0,0921
 30/09/22 |  A  | 1000 | 05/10/22 | -0,0177| 0,0727

Upvotes: 1

Views: 170

Answers (2)

MT0
MT0

Reputation: 167981

I assume your future dates should be after the date_simul value; currently they are not but if you fix your sample data so that all the futuredate values are in October 2022:

CREATE TABLE portfolio (date_simul, stock, n_simu, futuredate, return ) AS
  Select DATE '2022-09-30', 'A', 1000, DATE '2022-10-01', -0.0073 FROM DUAL UNION ALL
  Select DATE '2022-09-30', 'A', 1000, DATE '2022-10-02',  0.0078 FROM DUAL UNION ALL
  Select DATE '2022-09-30', 'A', 1000, DATE '2022-10-03',  0.0296 FROM DUAL UNION ALL
  Select DATE '2022-09-30', 'A', 1000, DATE '2022-10-04',  0.0602 FROM DUAL UNION ALL
  Select DATE '2022-09-30', 'A', 1000, DATE '2022-10-05', -0.0177 FROM DUAL;

Then your code (with appropriate rounding) gives the desired output:

select date_simul,
       Stock,
       N_Simu,
       FutureDate,
       Return,
       ROUND(exp(sum(ln(1+Return)) over (order by FutureDate asc)) - 1, 4) as cumul
from   portfolio
order by Stock, FutureDate;

Outputs your desired result:

DATE_SIMUL STOCK N_SIMU FUTUREDATE RETURN CUMUL
30-SEP-22 A 1000 01-OCT-22 -.0073 -.0073
30-SEP-22 A 1000 02-OCT-22 .0078 .0004
30-SEP-22 A 1000 03-OCT-22 .0296 .0301
30-SEP-22 A 1000 04-OCT-22 .0602 .0921
30-SEP-22 A 1000 05-OCT-22 -.0177 .0727

However, you could also use a MODEL clause:

select date_simul,
       Stock,
       N_Simu,
       FutureDate,
       Return,
       ROUND(cumul, 4) as cumul
from   portfolio
MODEL
  PARTITION BY (stock)
  DIMENSION BY (futuredate)
  MEASURES     (date_simul, n_simu, return, 0 AS cumul)
  RULES (
    cumul[ANY] = (1 + COALESCE(cumul[cv() - 1], 0)) * (1 + return[cv()]) - 1
  )

Which gives the same output.

fiddle

Upvotes: 1

d r
d r

Reputation: 7786

You could use analytic function with windowing clause:

WITH
    tbl AS
        (
            Select To_Date('30/09/22', 'dd/mm/yy') "DATE_SIMUL", 'A' "STOCK", To_Date('01/10/22', 'dd/mm/yy') "FUTURE_DATE", -0.0073 "RETURN" From Dual Union All
            Select To_Date('30/09/22', 'dd/mm/yy') "DATE_SIMUL", 'A' "STOCK", To_Date('02/09/22', 'dd/mm/yy') "FUTURE_DATE",  0.0078 "RETURN" From Dual Union All
            Select To_Date('30/09/22', 'dd/mm/yy') "DATE_SIMUL", 'A' "STOCK", To_Date('03/09/22', 'dd/mm/yy') "FUTURE_DATE",  0.0296 "RETURN" From Dual Union All
            Select To_Date('30/09/22', 'dd/mm/yy') "DATE_SIMUL", 'A' "STOCK", To_Date('04/09/22', 'dd/mm/yy') "FUTURE_DATE",  0.0602 "RETURN" From Dual Union All
            Select To_Date('30/09/22', 'dd/mm/yy') "DATE_SIMUL", 'A' "STOCK", To_Date('05/10/22', 'dd/mm/yy') "FUTURE_DATE", -0.0177 "RETURN" From Dual 
        )

Select 
    t.*,
    Sum(t.RETURN) OVER(PARTITION BY t.STOCK ORDER BY t.STOCK, t.RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "CUMUL"
From
    (SELECT ROW_NUMBER() OVER(Order By 1) "RN", tbl.* From tbl) t
Order By t.RN
/*  R e s u l t :
        RN DATE_SIMUL STOCK FUTURE_DATE     RETURN      CUMUL
---------- ---------- ----- ----------- ---------- ----------
         1 30-SEP-22  A     01-OCT-22      -0.0073    -0.0073 
         2 30-SEP-22  A     02-SEP-22        .0078      .0005 
         3 30-SEP-22  A     03-SEP-22        .0296      .0301 
         4 30-SEP-22  A     04-SEP-22        .0602      .0903 
         5 30-SEP-22  A     05-OCT-22      -0.0177      .0726
*/

More about analytic functions at: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
ROW_NUMBER() is added to preserve your order of rows - it is not needed if you have some other column in the table to order the rows by. In that case put From tbl t instead of subquery and in Sum() function put your ordering column instead of RN.

If it is FUTURE_DATE column then it would be...

Select 
    t.*,
    Sum(t.RETURN) OVER(PARTITION BY t.STOCK ORDER BY t.STOCK, t.FUTURE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "CUMUL"
From
    tbl t

/*  R e s u l t :
DATE_SIMUL STOCK FUTURE_DATE     RETURN      CUMUL
---------- ----- ----------- ---------- ----------
30-SEP-22  A     02-SEP-22        .0078      .0078 
30-SEP-22  A     03-SEP-22        .0296      .0374 
30-SEP-22  A     04-SEP-22        .0602      .0976 
30-SEP-22  A     01-OCT-22      -0.0073      .0903 
30-SEP-22  A     05-OCT-22      -0.0177      .0726
*/

Regards...
Addition
if you create CTE (I named it grid) to get elements (1 + RETURN) for later multiplication:

  grid AS
      (
          Select  ROW_NUMBER() OVER(ORDER BY FUTURE_DATE) "RN", 
                  tbl.*, 
                  1 + RETURN "ELEMENT"
          From tbl
      )
/*  R e s u l t :
        RN DATE_SIMUL STOCK FUTURE_DATE     RETURN    ELEMENT
---------- ---------- ----- ----------- ---------- ----------
         1 30-SEP-22  A     02-SEP-22        .0078     1.0078 
         2 30-SEP-22  A     03-SEP-22        .0296     1.0296 
         3 30-SEP-22  A     04-SEP-22        .0602     1.0602 
         4 30-SEP-22  A     01-OCT-22      -0.0073      .9927 
         5 30-SEP-22  A     05-OCT-22      -0.0177      .9823
*/

... then you can get cumulative compaund like below - using MODEL Clause

Select 
    RN, DATE_SIMUL, STOCK, FUTURE_DATE, RETURN, ELEMENT, CUMUL_COMPAUND
From
    (
        SELECT
            RN, DATE_SIMUL, STOCK, FUTURE_DATE, RETURN, ELEMENT,
            0 "CUMUL_COMPAUND"
        FROM
            grid
        ORDER BY RN
    )
MODEL
    PARTITION BY  (STOCK)
    DIMENSION BY  (RN)
    MEASURES      (DATE_SIMUL, FUTURE_DATE, RETURN, ELEMENT, CUMUL_COMPAUND)
RULES 
        (
            CUMUL_COMPAUND[ANY] =  CASE WHEN CV(RN) = 1 THEN ELEMENT[CV(RN)]
                                        WHEN CV(RN) = 2 THEN ELEMENT[CV(RN)] * ELEMENT[1]
                                        WHEN CV(RN) = 3 THEN ELEMENT[CV(RN)] * ELEMENT[1] * ELEMENT[2]
                                        WHEN CV(RN) = 4 THEN ELEMENT[CV(RN)] * ELEMENT[1] * ELEMENT[2] * ELEMENT[3]
                                        WHEN CV(RN) = 5 THEN ELEMENT[CV(RN)] * ELEMENT[1] * ELEMENT[2] * ELEMENT[3] * ELEMENT[4] 
                                  END - 1
        )
/*
        RN DATE_SIMUL STOCK FUTURE_DATE     RETURN    ELEMENT CUMUL_COMPAUND
---------- ---------- ----- ----------- ---------- ---------- --------------
         1 30-SEP-22  A     02-SEP-22        .0078     1.0078          .0078 
         2 30-SEP-22  A     03-SEP-22        .0296     1.0296      .03763088 
         3 30-SEP-22  A     04-SEP-22        .0602     1.0602     .100096259 
         4 30-SEP-22  A     01-OCT-22      -0.0073      .9927    .0920655563 
         5 30-SEP-22  A     05-OCT-22      -0.0177      .9823    .0727359959
*/

Regards...

Upvotes: 2

Related Questions