Hi Guys and Gals!!!
I have two goals operating but to focus on one, I'm trying to create a PIVOTCHART from a dataset. On the tab titled "Where the Action Is", you will find a table set up for a Pivot Chart. In column "Maths" of that table, I want to ultimately calculate a new market value based on a percent change on the current price from its minimum date. To put it mathematically:
To pick a random record, lets use MSCI ACWI for 1/9/2019. The Maths cell should = 1000000*(1+(66.550003-64.139999)/64.139999)
...for any MSCI ACWI (or any index) record, the % change should be calculated against the minimum date, which for MSCI ACWI is (1/2/2019 = 64.139999)
The idea here is I could create a pivot chart of the hypothetical growth of $1MM for any given index.
I've been trying to kinda start with the "core" of the formula which would be to index(match) the price for the minimum date of an index and keep getting an #N/A error:
[SIZE=12px]=INDEX([Price],MATCH(1,INDEX(([Index]="MSCI ACWI")*([Date]=MIN(IF([Index]="MSCI ACWI",[Index]))),0),0))[/SIZE]
Other than that... if you're interested please take a look at the 'Outputs' tab. My boss basically wants to be able to see what our asset allocation performance would be, assuming market performance. There is an allocations table, which would allow me to edit (on the fly) how much of our portfolio is subject to each index. Below that you will see three calculation methods: YTD, Monthly, and Weekly. In each, you will see what is essentially a math problem for each portfolio, which is [SIZE=11px]Starting Value * Allocation * 1+Performance + n times[/SIZE]. Same thing for the Prior Period, then basic math problems for % change, nominal change, and day's cash.
YTD should always be CP = max dates for indices against min dates for indices | PP = start value
Monthly should always be CP = dropdown of months (January, February, etc), and automatically select the max date for that month and calculate performance against min date for the index... so July % change would be July 31 vs. Jan 2) | PP exact same except using max date for preceding month
Weekly should always be CP = dropdown of dates from table2, automatically calculate previous period date as 7 days prior, math similar to above
I kinda started to attempt some VBA to do this, because these values don't necessarily need to be stored to be readily available for a chart or something... if someone wants a chart, I could just run a few periods through Monthly or Weekly to cull the data. The problem with that is I'm not that great at VBA, lol
[SIZE=24px]Thanks in Advance For All Your Help!!![/SIZE]