Please check out the attached file for reference.
Tab 'Outputs' contains an allocations table. This is not the final table, as I'm hearing right now there will be additional indices added. But the idea here is I can assign a certain % of the "Starting Values" to each index. As the table stands in this file, 15% of the 408,910,275 for Eddard Stark is in MSCI ACWI, 30% in MSCI EAFE, 25% Russell 1000, etc.
The first step for the VBA should be to check this table for nonblank cells, and store those percentages and index names as variables (I think)
Then the VBA should check the table on Tab 'Where the Action Is' and capture 2 prices for each index name it matches from the nonblank cells of the allocation table.
Those two prices are as follows:
YTD = max date for the index | min date for the index
Monthly = max date of month selected (right now its July, so the max date would = 7/31/2019) | min date of month selected (7/1/2019)
Weekly = date selected from dropdown (right now its 8/16/2019) | - 7 days from date selected (8/9/2019)
Next... the VBA should perform market value calculations which work as follows, using YTD on Eddard Stark for example:
408,910,275 * nonblank1 (15% in this case) * (1 + (max date price for index - min date price for index) / (min date price for index) + 408,910,275 * nonblank2 (30%) * (1 + ...
this continues n times until its cycled through all the percentages in the column.
The final sum/product should be the CP (current period) Close (455,352,242, in this case)
For YTD, the PP Close will be the Starting Values, but for Monthly it will be the same calculation on the previous month (June, in this case) and Weekly it will be the same calculation on the previous week (8/9/2019 vs. 8/2/2019).
It then needs to repeat this for each owner (Pycelle, Oberyn Martell, Aegon).
I feel so stupid describing this because idk if I'm explaining it in a way that makes sense, but thanks so much for your help guys and gals!