Automatically calculate values from table

Hello All,

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!

Alright guys, I'm trying to inch into this... Right now I'm just trying to set the starting values and I'm getting an [SIZE=11px]Overflow Error 6[/SIZE] at

[VBA]EdSV = ThisWorkbook.Sheets("Outputs").Range("K4").Value[/VBA]

... total code so far is below:

[VBA]
Sub MVUpdate()
Dim EdSV As Integer
Dim PySV As Integer
Dim OMSV As Integer
Dim AegSV As Integer

EdSV = ThisWorkbook.Sheets("Outputs").Range("K4").Value
PySV = ThisWorkbook.Sheets("Outputs").Range("L4").Value
OMSV = ThisWorkbook.Sheets("Outputs").Range("M4").Value
AegSV = ThisWorkbook.Sheets("Outputs").Range("N4").Value
End Sub
[/VBA]

Any thoughts?

Fixed...

[vba]
Sub MVUpdate()
Dim EdSV As Integer
Dim PySV As Integer
Dim OMSV As Integer
Dim AegSV As Integer

ThisWorkbook.Sheets("Outputs").Range("K4").Value = EdSV
ThisWorkbook.Sheets("Outputs").Range("L4").Value = PySV
ThisWorkbook.Sheets("Outputs").Range("M4").Value = OMSV
ThisWorkbook.Sheets("Outputs").Range("N4").Value = AegSV

End Sub[/vba]

Next I'm trying to select the nonblank cells in the allocations table and have this... so far it works in that it is successful in selecting the nonblank cells. If you run it, you will end up with each nonblank cell in the range "selected."

What I want to do now is pass these selections on to variables:

5% should = EdAllocation1
15% should = EdAllocation2
...

This specific feature is important because it preserves the ability to make "on the fly" changes to allocations without requiring a change in VBA.

But my dilemma is 2-fold:

Fold 1:

Each portfolio (Eddard, Pycelle, Oberyn, Aegon) might have a different set of allocations. Meaning Eddard's assets are allocated across 5 indices (MSCI ACWI, EAFE, etc.) and Pycelle might have his assets allocated across 8.

How do I handle this?

I'm guessing setting a base variable name, like:

For Each ErSel, set Sel1 = EdAllocation + n

n +1

or something like that... idk, I'm drowning over here!

Fold 2:

I need to capture the name for each selection. So 5% is MSCI ACWI, 15% is MSCI EAFE, etc.

The reason for this is I need to perform an index(match) to Table2 to capture the price for specific dates. For YTD for example, I want to capture the most recent price max(table2[date]) and the earliest price (min,table2[date]... lol, the prices for these dates will have to be passed to a later formula which is below:

All these variables, index(match)-es, min/max dates, all will add up to a formula, for demonstration sake, is below:

EdSV * EdAllocation1 * (1+(maxdateprice - mindateprice)/mindateprice) + EdSV * EdAllocation2 * (1+(maxdateprice - mindateprice)/mindateprice)... EdSV * EdAllocation5 * (1+(maxdateprice - mindateprice)/mindateprice)

I guess, now that I look at it, it might be easier to store the expression: (1+(maxdateprice - mindateprice)/mindateprice) as a variable, itself, lets call it EdYTDPercentChange, so written another way:

EdSV * EdAllocation1 * EdYTDPercentChange + EdSV * EdAllocation2 * EdYTDPercentChange ... + EdSV * EdAllocation5 * EdYTDPercentChange = EdCP

Then EdCP = ThisWorkbook.Sheets("Outputs").Range("E27")

[vba]
Sub MVUpdate()
Dim EdSV As Integer
Dim PySV As Integer
Dim OMSV As Integer
Dim AegSV As Integer

ThisWorkbook.Sheets("Outputs").Range("K4").Value = EdSV
ThisWorkbook.Sheets("Outputs").Range("L4").Value = PySV
ThisWorkbook.Sheets("Outputs").Range("M4").Value = OMSV
ThisWorkbook.Sheets("Outputs").Range("N4").Value = AegSV

Dim EdR As Range, Er As Range, ErSel As Range

Set EdR = ThisWorkbook.Sheets("Outputs").Range("K5:K20")
Set ErSel = Nothing

For Each Er In EdR
If Er.Value <> "" Then
If ErSel Is Nothing Then
Set ErSel = Er
Else
Set ErSel = Union(ErSel, Er)
End If
End If
Next Er
If Not ErSel Is Nothing Then ErSel.Select

End Sub
[/vba]

I think this gets me the array??? But how do I now store the cell values in the actual variables, as in:

EAlloc1 = 5% | EAlloc2 = 15% | EAlloc3 = 40%... n

[vba]
Sub MVUpdate()
Dim EndowSV As Integer
Dim PenSV As Integer
Dim OperSV As Integer
Dim AllegSV As Integer

ThisWorkbook.Sheets("Outputs").Range("K4").Value = EndowSV
ThisWorkbook.Sheets("Outputs").Range("L4").Value = PenSV
ThisWorkbook.Sheets("Outputs").Range("M4").Value = OperSV
ThisWorkbook.Sheets("Outputs").Range("N4").Value = AllegSV

Dim EndowR As Range, Er As Range, ErSel As Range

Set EndowR = ThisWorkbook.Sheets("Outputs").Range("K5:K20")
Set ErSel = Nothing

For Each Er In EndowR
If Er.Value <> "" Then
If ErSel Is Nothing Then
Set ErSel = Er
Else
Set ErSel = Union(ErSel, Er)
End If
End If
Next Er
If Not ErSel Is Nothing Then ErSel.Select

MsgBox Selection.CountLarge

Dim i, x As Integer
x = ErSel.Count

Dim EAlloc() As Integer
ReDim EAlloc(1 To x) As Integer

For i = 1 To x

EAlloc(i) = i

Next
End Sub
[/vba]

THEN how do I capture the names of the indices so I can start working on my index(match) to get to my ultimate calculation:

CV = SV * EAlloc1 * EPerChange + SV * EAlloc2 * EPerChange ... SV * EAllocn * EPerChange

Thank you Pike!

Can you help me get to the final equation:

CurrentValue = StartingValue * myArray(1) * PercentChange + StartingValue * myArray(2) * PercentChange + ..... StartingValue * myArray(n) * PercentChange

Where PercentChange = (1+(index(match(a) + index(match(b))/index(match(b))
**index(match) will be done to pull prices for specific dates

Quote from pike

Hyello,
I can attempt to answer a specific question about excel/vba but not solve an equation ,, you could try recording a macro to fill in the blanks or make a worksheet formula of the problem.
Can you expnad and ask a specific question?

Hi Pike!

Now that the nonblanks have been populated into array variables, how would I insert that variable into the following equation?:

CurrentValue = StartingValue * myArray(1) * PercentChange + StartingValue * myArray(2) * PercentChange + ..... StartingValue * myArray(n) * PercentChange

For n iterations of the array?

Quote from pike

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?)