Cumulative Returns over Daily Returns SQL-CodePudding

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 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,0005
 30/09/22 |  A  | 1000 | 03/09/22 | 0,0296 | 0,0301
 30/09/22 |  A  | 1000 | 04/09/22 | 0,0602 | 0,6321
 30/09/22 |  A  | 1000 | 05/10/22 | -0,0177| 0,06144

CodePudding user response：

Take sum over the window you want to and order by futuredate this will give you cumulative sum over a window.

In this case per date_simul & stock you will get cumulative sum of returns

select date_simul, Stock, "N Simu", FutureDate, Return,
sum(Return) over (partition by date_simul,Stock order by FutureDate asc) as cumul
from portfolio
order by FutureDate asc