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
CodePudding user response：
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...