The ability to calculate product for an aggregated by group.
Requirement: Calculate compounded rate of return when you are given monthly rate of return
Say,
I HAVE:
Client | Financial Product | Date | Return | Return +1 |
Customer A | Product A | Jan2016 | 2% | $1.02 |
| Product A | Feb2016 | 3% | $1.03 |
| Product A | Mar2016 | 1% | $1.01 |
| Product A | Apr2016 | 7% | $1.07 |
| Product A | May2016 | -5% | $0.95 |
| Product A | Jun2016 | 3% | $1.03 |
| Product A | Jul2016 | -0.50% | $1.00 |
| Product A | Aug2016 | 3% | $1.03 |
| Product A | Sep2016 | 2% | $1.02 |
| Product A | Oct2016 | 5% | $1.05 |
| Product A | Nov2016 | 1% | $1.01 |
| Product A | Dec2016 | -1% | $0.99 |
Customer A | Product B | | | |
.... | .... | | | |
..... | ..... | | | |
....... | ....... | | | |
......... | ......... | | | |
......... | ......... | | | |
* I am using 12 months for ease of defining but the period can be more than 12 months too.
I WANT:
A slider with "Date" variable. When you move the slider and select a time period say May2016 to Oct2016 you get the output , which is product of Variable (Return+1) i.e 0.95*1.03*1.00*1.03*1.02 *1.05 = 1.079.
This way I can calculate compunded return for each product for same time period in this case May2016 to Oct2016.
This can be achieved in BASE SAS by creating a table htat comtains pre calculated returns for all the date combunations.
What do I men by date combinations: for 12 months- ther will be 66 distinct combinations say
Jan-Feb
Jan march
jan-dec
feb-march
..
..
..
.
.
nov-dec
May to october -- as explained in this case is only 1 of those combinations.
Once you have the dataset, instead of using Slider, we can use two drop-down's for Start_Date and End_date variable. It is little cucumebrsome but the idea is to calculate product of observations selected/aggregated by group/sub-group.