Hope you can help me as i am stuck.
I have a query with 4 columns: id, name, date and monthly return.
I want to calculate the year to date compounded return per id using the Formula:
(1+return1)*(1+return2)…-1.
My data looks like this although it is an example:
| ID | Name | Date | Monthly Return |
|---|---|---|---|
| 1 | Name1 | 31/03/2023 | -0.02 |
| 1 | Name1 | 30/04/2023 | 0.05 |
| 1 | Name1 | 31/05/2023 | 0.06 |
| 1 | Name1 | 30/06/2023 | 0.01 |
| 2 | Name2 | 31/03/2023 | -0.01 |
| 2 | Name2 | 30/04/2023 | 0.01 |
| 2 | Name2 | 31/05/2023 | 0.05 |
| 3 | Name3 | 31/03/2023 | 0.05 |
| 3 | Name3 | 30/04/2023 | 0.05 |
I keep getting only the sum and not the compound effect when i do the query. Writing this from my phone - but if anyone want the code i Can upload it in a comment.
I am using MySQL and working in workbench 8.0.33
Hope you can help me.
Tried implementing the formula and grouping on ID but i only get the sum of the monthy returns per ID.
