Investment Model Allocation based upon Percentage and Dollar Amounts

[luckyajr]

Hi,

I am working on a project in which I need to create an investment allocation spreadsheet. The idea is that there can be anywhere from 2 to 6 investment accounts, each with varying dollar amounts. Each account is then then defined as either a "taxable" or "tax deferred" account. From there, I want to apply those accounts to an investment model with targeted asset class percentages (e.g. 8% to Large Cap, 12% to Emerging Markets, etc. totalling to 100%). Each asset class will either be allocated to a "taxable" or a "tax deferred" account(s), and one asset class will be allocated to both registrations (due to percentage differences). Next, based upon the asset class percentage and the total portfolio amount, the trade amount to be placed for the asset class is then formulated.

This next part is where I am stuck. I want to create a formula (or macro) that will dictate which particular account(s) the asset class should be traded to, where everything ends up allocated to the investment model.

So, as an example, there could be a scenario where I have 3 accounts (1 "taxable" and 2 "tax deferred" accounts) that will all be considered "one bucket of money" and I want to allocate those accounts to the investment model which is based upon different asset classes. Each asset class has a defined $ trade amount and is also assigned to an account registration ("taxable"/"tax deferred" or "both").

I'm not sure if I explained this well or not. Apologies if I didn't. Thank you in advance, I sincerely appreciate it.

 

[NdNoviceHlp]

It should work for as many accts as you want. Trial some more. Make sure your numbers on sheet2 are correct and A7:A22 add up to 100%. If it still doesn't work maybe abit more info will be needed to figure out what's wrong (ie. what didn't calculate correctly?) Changing the format won't be difficult. How do the tickers get to D&:D22? Dave

 

[luckyajr]

Trial this:

Input these amounts:
C25: $355,122.22
C26: $425,111.67
C27: $85,000.55
C28: $115,322.44

Then run the MACRO. It has calculates up to the 2 remaining cells and then "Not Responding" at the same place for 7 times in a row. Is this happening to you. It has also happened on some other scenarios as well.

Also, when running certain other trials with varying amounts in the Account Balances (C25:C30) it will complete the calculation, but for some trade amounts in the "MultiTrade" scenario the amount in parenthesis will say "(-35)", and instead of displaying "MultiTrade" it will say "$0.01".
Any thoughts??

 

[luckyajr]

Also trialed some more and some would calculate everything but the "Allocate to Both*", which would be left blank in column F and on to the right.

 

[NdNoviceHlp]

Do your gross amounts for each account (ie. taxable/tax deferred) add up to the same gross totals of the input? I used those numbers and it runs as it should. I should mention that B25 down requires exact input (ie. Taxable or Tax Deferred) as does C7:C22. C12 returns FALSE for me without altering Sheet2N25 to equal Sheet1C3. I trialled adding the additional number both as Taxable and Tax Deferred... the code works. It executes until the current total balance in G goes to 0. The percentages in A have to total the gross for each acct (ie, Taxable). On the 1 cent thing...there should be some kind of code addition that sets a minimimum trade for each assett acct (ie. don't trade if there's less funds in the assett account than the trade fee.) Anyways, column A % and the output from the formulas in C (based on sheet2) drive the whole thing. I assume that your inputted numbers somehow affect these? Dave
ps. If there's nothing left over from both accts to allocate to both then nothing happens (it's done last)

 

[luckyajr]

I'm still following your logic with everything. And yes, my numbers all seem to still work. I'm just trialing many different scenarios to test, and most of the time it works efficiently. But like I said, every now and then it doesn't. I'm not "freezing" up any more but I will still do that thing that you described in your "p.s." in your previous post where it will leave allocate to both blank (even though all the remaining balances equal exactly the amount left to be traded in "allocate to both*". I like the idea of the minimium trade code being added so that there are no trades placed for less than the trade cost.
So, now where do we stand? Seems like we're pretty close, but I'm still confused. Sorry and thanks!

 

[NdNoviceHlp]

It's likely that the minimum trade thing is throwing things off. When the allocate to both problem happens is there a remaining current balance in G for both Taxable and Tax Deferred? Does this sum exactly to the amount remaining in E (set up a ws formula check)... there may be some fractionation error that needs to be addressed. I don't understand how you want the acct# formatted and how/where the ticker symbol is for allocate to both (could it be comma delineated in D?). Dave

 

[NdNoviceHlp]

Trial changing these 2 lines of code to address the minimum the minimum trade thing. Dave

Do While Temptot > Sheets("Sheet2").Range("A" & 1).Value '1
Do While RemTot > Sheets("Sheet2").Range("A" & 1).Value '1

 

[luckyajr]

Thanks, but have a dumb question. Where am I placing this code and what is it replacing? Thanks!

 

[luckyajr]

...and just noticed your post above the code.
To answer your question...YES, when the "Allocate to Both*" is left blank, the total remaining for the accounts in G matches exactly to the remaining amount in E.
Account numbers are formatted like this: ###-###### (e.g. 123-456789)
If it is a multiaccount I would rather the ticker not show up in cell D. This is because it may make it more difficult for a user to know which ticker goes to which account.
I don't mind it being added to the mix in F and onto the right by comma delineattion next to the respective trade amount and account like it is currently being via your code.

 

[NdNoviceHlp]

There was a logic error re. endless loop. Trial this. Dave

Sub FillF5()
Dim LargeTemp2 As Double, Cnt2 As Integer, Cnt3 As Integer
Dim LargeTemp As Double, Cnt As Integer, Temptot As Double
Dim TotTax As Double, Lastrow As Integer, Counter As Integer
Dim Cnt2Temp As Integer, CntTemp As Integer, RemTot As Double
Dim RemGrs As Double, Cnt4 As Integer, CellCnt As Integer
Dim Cnt5 As Integer, Temp As Double
'fee in Sheets("Sheet2").Range("A" & 1).Value
'"G" has current balance of untraded amount
'"I" has total trade fee
' "J" has # of trades

'include all accts
With Sheets("Sheet1")
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
'clear previous results
'*******Caution clears E7:j22 and G25:J & lastrow
Sheets("Sheet1").Range("G25:J" & Lastrow).ClearContents
Sheets("Sheet1").Range("E7:J22").ClearContents

'transfer acct total to "G" for current balance
For Cnt2 = 25 To Lastrow
Sheets("Sheet1").Range("G" & Cnt2).Value = Sheets("Sheet1").Range("C" & Cnt2).Value
Next Cnt2
'gross amts
For Cnt3 = 7 To 22
Sheets("Sheet1").Range("E" & Cnt3).Value = Sheets("Sheet1").Range("A" & Cnt3).Value * _
                                            Sheets("Sheet1").Range("B" & 1).Value
Next Cnt3

For Counter = 1 To 2
Select Case Counter
'taxable/tax deferred
Case 1: Temptot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A30"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
Case 2: Temptot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
End Select
Do While Temptot > Sheets("Sheet2").Range("A" & 1).Value '1
LargeTemp = 0
LargeTemp2 = 0
If Counter = 1 Then
'current balance taxable
For Cnt2 = 25 To Lastrow
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Taxable" Then
If LargeTemp2 < Sheets("Sheet1").Range("G" & Cnt2).Value Then
LargeTemp2 = Sheets("Sheet1").Range("G" & Cnt2).Value
Cnt2Temp = Cnt2
End If
End If
Next Cnt2
'grs amounts taxable
For Cnt = 7 To 22
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Taxable" Then
If LargeTemp < Sheets("Sheet1").Range("e" & Cnt).Value Then
Temp = Sheets("Sheet1").Range("e" & Cnt).Value
If LargeTemp2 >= Temp Then
LargeTemp = Sheets("Sheet1").Range("e" & Cnt).Value
End If
CntTemp = Cnt
End If
End If
Next Cnt
'current balance tax defer
Else
For Cnt2 = 25 To Lastrow
If Sheets("Sheet1").Range("B" & Cnt2).Value = "Tax Deferred" Then
If LargeTemp2 < Sheets("Sheet1").Range("G" & Cnt2).Value Then
LargeTemp2 = Sheets("Sheet1").Range("G" & Cnt2).Value
Cnt2Temp = Cnt2
End If
End If
Next Cnt2
'grs amounts tax defer
For Cnt = 7 To 22
If Sheets("Sheet1").Range("F" & Cnt).Value = "" And _
Sheets("Sheet1").Range("C" & Cnt).Value = "Tax Deferred" Then
If LargeTemp < Sheets("Sheet1").Range("e" & Cnt).Value Then
Temp = Sheets("Sheet1").Range("e" & Cnt).Value
If LargeTemp2 >= Temp Then
LargeTemp = Sheets("Sheet1").Range("e" & Cnt).Value
End If
CntTemp = Cnt
End If
End If
Next Cnt
End If
If LargeTemp = 0 Or LargeTemp2 - LargeTemp <= 0 Then
Exit Do
End If

'insert acct #
Sheets("Sheet1").Range("F" & CntTemp).Value = Sheets("Sheet1").Range("A" & Cnt2Temp).Value
If CntTemp <> 22 Then 'no fee for #22
'total fee (fee amt in Sheets("Sheet2").Range("A" & 1).Value)
Sheets("Sheet1").Range("I" & Cnt2Temp).Value = _
Sheets("Sheet1").Range("I" & Cnt2Temp).Value + Sheets("Sheet2").Range("A" & 1).Value
'net trade amt
Sheets("Sheet1").Range("e" & CntTemp).Value = Sheets("Sheet1").Range("e" & CntTemp).Value - _
                                               Sheets("Sheet2").Range("A" & 1).Value
End If
'current balnce
Sheets("Sheet1").Range("G" & Cnt2Temp).Value = LargeTemp2 - LargeTemp
'trade cnter
Sheets("Sheet1").Range("j" & Cnt2Temp).Value = _
Sheets("Sheet1").Range("j" & Cnt2Temp).Value + 1
Temptot = Temptot - LargeTemp
MsgBox "View"
Loop
Next Counter

'********multi trade taxable/tax defer
For Counter = 1 To 2
Select Case Counter
Case 1: RemTot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A30"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
Case 2: RemTot = _
Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("B25:B" & Lastrow), _
Worksheets("Sheet2").Range("A31"), Worksheets("Sheet1").Range("G25:G" & Lastrow))
End Select

Do While RemTot > Sheets("Sheet2").Range("A" & 1).Value '1
RemGrs = 0
For Cnt4 = 7 To 22
'tot grs taxable remaining
If Counter = 1 Then
If Sheets("Sheet1").Range("C" & Cnt4).Value = "Taxable" Then
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
End If
'tot grs tax defer remaining
Else
If Sheets("Sheet1").Range("C" & Cnt4).Value = "Tax Deferred" Then
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
End If
End If
Next Cnt4

If RemGrs = 0 Then
Exit Do
End If

CellCnt = 6
For Cnt5 = 25 To Lastrow
If Sheets("Sheet1").Range("G" & Cnt5).Value > 0 Then
If Sheets("Sheet1").Range("C" & Cnt4).Value = Sheets("Sheet1").Range("B" & Cnt5).Value Then
If Sheets("Sheet1").Range("G" & Cnt5).Value <= RemGrs Then

If Cnt4 <> 22 Then 'no fee for #22
'total fee
Sheets("Sheet1").Range("I" & Cnt5).Value = _
Sheets("Sheet1").Range("I" & Cnt5).Value + Sheets("Sheet2").Range("A" & 1).Value
If Sheets("Sheet1").Range("e" & Cnt4).Value >= Sheets("Sheet1").Range("G" & Cnt5).Value Then
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                           Sheets("Sheet1").Range("G" & Cnt5).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
       "(" & Sheets("Sheet1").Range("G" & Cnt5).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("g" & Cnt5).Value
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
Else
'current balance remaining
Sheets("Sheet1").Range("G" & Cnt5).Value = Sheets("Sheet1").Range("G" & Cnt5).Value - _
                                           Sheets("Sheet1").Range("e" & Cnt4).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
        "(" & Sheets("Sheet1").Range("e" & Cnt4).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("e" & Cnt4).Value
Sheets("Sheet1").Range("e" & Cnt4).Value = 0
End If
Else
If Sheets("Sheet1").Range("e" & Cnt4).Value > Sheets("Sheet1").Range("G" & Cnt5).Value Then
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                             Sheets("Sheet1").Range("G" & Cnt5).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                            "(" & Sheets("Sheet1").Range("G" & Cnt5).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("G" & Cnt5).Value
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
Else
'current balance remaining
Sheets("Sheet1").Range("G" & Cnt5).Value = Sheets("Sheet1").Range("G" & Cnt5).Value - _
                                           Sheets("Sheet1").Range("e" & Cnt4).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                            "(" & Sheets("Sheet1").Range("e" & Cnt4).Value & ")"
RemTot = RemTot - Sheets("Sheet1").Range("e" & Cnt4).Value
Sheets("Sheet1").Range("e" & Cnt4).Value = 0
End If
End If
CellCnt = CellCnt + 1
MsgBox "View"
'trade cnt
Sheets("Sheet1").Range("j" & Cnt5).Value = _
Sheets("Sheet1").Range("j" & Cnt5).Value + 1
End If
End If
End If
Next Cnt5
If Sheets("Sheet1").Range("e" & Cnt4).Value = 0 Then
Sheets("Sheet1").Range("e" & Cnt4).Value = "MultiTrade"
End If
Loop
Next Counter

'multi allocate to both
For Cnt4 = 7 To 22
If Sheets("Sheet1").Range("F" & Cnt4).Value = "" Then
RemGrs = Sheets("Sheet1").Range("E" & Cnt4).Value
Exit For
End If
Next Cnt4
If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("G25:G" & Lastrow)) <= _
                          Sheets("Sheet1").Range("E" & Cnt4).Value Then
CellCnt = 6
For Cnt5 = 25 To Lastrow
'trade cnt
Sheets("Sheet1").Range("j" & Cnt5).Value = _
              Sheets("Sheet1").Range("j" & Cnt5).Value + 1
'net remaining
Sheets("Sheet1").Range("e" & Cnt4).Value = Sheets("Sheet1").Range("e" & Cnt4).Value - _
                                           Sheets("Sheet1").Range("G" & Cnt5).Value
If Cnt4 <> 22 Then 'no fee for #22
'trade fee
Sheets("Sheet1").Range("I" & Cnt5).Value = _
Sheets("Sheet1").Range("I" & Cnt5).Value + Sheets("Sheet2").Range("A" & 1).Value
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
    "(" & Sheets("Sheet1").Range("G" & Cnt5).Value - Sheets("Sheet2").Range("A" & 1).Value & ")"
Else
'insert acct #
Sheets("Sheet1").Cells(Cnt4, CellCnt) = Sheets("Sheet1").Range("A" & Cnt5).Value & _
                                         "(" & Sheets("Sheet1").Range("G" & Cnt5).Value & ")"
End If
Sheets("Sheet1").Range("G" & Cnt5).Value = 0
CellCnt = CellCnt + 1
MsgBox "View"
Next Cnt5
If Sheets("Sheet1").Range("e" & Cnt4).Value = 0 Then
Sheets("Sheet1").Range("e" & Cnt4).Value = "MultiTrade"
End If
End If
End Sub