Good morning,
This is a complex problem and part of a much larger model but I have highlighted the section of code that is slowing down my full macro and created a test workbook that has a data sample and the code I am currently using which is linked here: MEGA
To explain what I am doing there are three key parts
1. A matrix that goes from years 0 to 70 and in each year an allocation (adding up to 100%) between 10 asset classes (Matrix Tab)
2. A 2 column list going from years 0 to 70 that maps a number from 1-20 for each year. (Data Map Tab)
3. Underlying data that has 1000 scenarios spanning over 20 years with each year in each scenario having an investment return (Underlying Data). I call these 21000 row columns "Return Streams"
What I am doing is creating a new return stream based on the allocations in the matrix tab that extends out to 70 years per scenario. As the data only goes up to year 20 after that i am repeating a random years returns as per the 2nd column in the Data Map tab.
Right now my code creates an array from the allocation and an array from the underlying data and uses sumproduct to calculate the return. This is looped 70 times for each of the 1000 scenarios.
This takes a few seconds but the actual data runs out to 10000 scenarios and takes about 45 seconds.
I am thinking there is probably a more efficient way to do this with matrices that would speed it up significantly but I cant think through exactly how to do it.
If anyone is so inclined as to take a look at my code and my data and suggest ways to improve the speed I would be extremely grateful.
Thanks
Nick
This is a complex problem and part of a much larger model but I have highlighted the section of code that is slowing down my full macro and created a test workbook that has a data sample and the code I am currently using which is linked here: MEGA
To explain what I am doing there are three key parts
1. A matrix that goes from years 0 to 70 and in each year an allocation (adding up to 100%) between 10 asset classes (Matrix Tab)
2. A 2 column list going from years 0 to 70 that maps a number from 1-20 for each year. (Data Map Tab)
3. Underlying data that has 1000 scenarios spanning over 20 years with each year in each scenario having an investment return (Underlying Data). I call these 21000 row columns "Return Streams"
What I am doing is creating a new return stream based on the allocations in the matrix tab that extends out to 70 years per scenario. As the data only goes up to year 20 after that i am repeating a random years returns as per the 2nd column in the Data Map tab.
Right now my code creates an array from the allocation and an array from the underlying data and uses sumproduct to calculate the return. This is looped 70 times for each of the 1000 scenarios.
This takes a few seconds but the actual data runs out to 10000 scenarios and takes about 45 seconds.
I am thinking there is probably a more efficient way to do this with matrices that would speed it up significantly but I cant think through exactly how to do it.
If anyone is so inclined as to take a look at my code and my data and suggest ways to improve the speed I would be extremely grateful.
Thanks
Nick
