Hi

what cells are the constants ?

Hi Pike!

Constants:

StartingValue = K4 for Eddard Stark | L4 for Pycelle, etc.

%Change references relative dates and names:

...lets say we're working with MSCI ACWI. The variable named myArray(1) already has a value set to 15%. The VBA then has to go to 'Table2' located on tab 'Where the Action Is', find the most recent date for MSCI ACWI which is 8/16/2019 and store the price from that date for the % change formula as "EACWIcv." It then needs to find the earliest date in MSCI ACWI for that year (2019)... which is 1/2/2019 and store the price from that date as "EACWIpv." It then needs to calculate the %Change which is: (1+(EACWIcv-EACWIpv)/EACWIpv)... in this case, EACWIcv = 71.18, EACWIpv = 64.139999 so the formula with the numerical values inserted would read (1+(71.18-64.139999)/64.139999) = 1.109759918 = %Change

Inserting the product of %Change in the formula we would get CurrentValue = **StartingValue** * myArray(1) * **1.109759918** + **StartingValue** * myArray(2) * 1.109759918 + ..... StartingValue * myArray(n) * **1.109759918**

Which is why %Change can be stored as a variable instead of calculated for each iteration of myArray(n)

Inserting numerical values for all the variables would read CurrentValue = **408,910,275** * **.15** * **1.109759918** + **408,910,275** *** .30** * **1.109759918** + **408,910,275** * **.25** * **1.109759918** + **408,910,275** * **.08** * **1.109759918** + **408,910,275** * **.22** * **1.109759918**, CurrentValue = 455,352,242

**StartingValue**

**myArray(n)**

**%Change**

If you take a look at the tab titled ''Outputs' you will see where this is being calculated in E27. For this particular method, Prior Period Close = StartingValue, so we see that represented as a simple cell reference. Below that we see the actual % change is is similar to the %Change formula above, but this is basically the YTD performance of the mixed portfolio, instead of individual indices. and we see a nominal change, so this is the dollar amount change, saying that with the existing asset allocation mixture, Eddard Stark has earned $46MM YTD...

Right now we're only working with the one portfolio, EddardStark

And for the one method: YTD

But this needs to be repeated for the other 3 portfolios, and for the 2 other methods: Monthly and Weekly.. These two methods have to calculate the "PP Close" the same way they calculate the CP Close, but for different dates. I can provide more detail if you're interested.

I know this is A LOT that I'm asking. I'm willing to pay because this will literally save me hours of frustration every week, as I'm expected to produce these numbers weekly (our statements, like most people, are monthly, and the higher ups want to get a sense of where we are on an intra-month basis). I'm just trying to put as much of it in VBA as possible because it will remove the human factor. Plus I want to be able to produce outputs on the fly, so with monthly (for example), I could pull a drop down and select July, or February, and everything calculates, instead of me scrolling, clicking, scrolling, etc... messing up somewhere, not catching it, getting yelled at. I thought I had a grasp on VBA until I attempted this.

Please send me a private message and we can settle on price and payment method (cashapp?)