SB123

11-15-2008, 04:08 PM

Hello,

This is my first post here, so thanks for the effort in putting up the site.

I am having some trouble with a particular area in my code. I am stumped as to how to move forward and was hoping that someone could redirect me or provide some guidance. I am trying to split balances both as a weighted average as well as stacked one on top of the other. The balances then need to be carried forward into the next structure/group so that the process can be repeated.

I have built the idea on the sheet, but I haven't been able to make the logic work in VB. I keep getting stuck at accumulating balances when they are being stacked. See Column 11 in the Array NoteHold for reference.

Because this is a bit hard to describe, I am also attaching my working Excel file. The code is below and is also in the Excel File. The Sheet is a good place to see how the code should work. I apologize for the Long Code. I have been trying different methods and have possibly left unnecessary information in the code.

Sub Loss()

Dim DefaultValue As Double

Notes = 13 'represents "All Notes"

DefaultValue = Range("i23") 'Value of the Family (Portfolio Value)

ReDim NoteHold(Notes, 13)

'Load Notes in the same Family-Loan Structure

j = 1

For i = 1 To Notes

If Cells(5 + i, 4) & Cells(5 + i, 5) = Range("e24") Then 'Modify as Family-Note combo in DB

NoteHold(j, 1) = Cells(5 + i, 4) & Cells(5 + i, 5) 'Loan Name (Constant)

NoteHold(j, 2) = "1" & Cells(5 + i, 13) 'Note Name (Constant)

NoteHold(j, 3) = Cells(5 + i, 9) 'Note Amount (Constant)

NoteHold(j, 4) = Len(Cells(5 + i, 13)) 'Note Length (Constant)

NoteHold(j, 8) = j 'Note Count (Constant)

j = j + 1

End If

Next

'Fill in Overall Loan Structure

For i = 1 To Notes

MaxLen = Application.WorksheetFunction.Max(NoteHold(i, 4), MaxLen)

Next

For i = 1 To Notes

For j = 1 To MaxLen

If j <= NoteHold(i, 4) Then

NoteHold(i, 9) = NoteHold(i, 2)

Else

NoteHold(i, 9) = Left(NoteHold(i, 9), j) & IIf(j / 2 <> Round(j / 2, 0), "A", "1")

End If

Next

LoanBal = NoteHold(i, 3) + LoanBal

Next

'Load Initial Loss and Loan Balance

LoanLoss = Application.WorksheetFunction.Max(LoanBal - DefaultValue, 0)

For n = 1 To Notes

NoteHold(n, 5) = LoanBal

NoteHold(n, 7) = LoanLoss

Next

'Loop through the Notes Structures where MaxLen is the length of the full Note Name

For i = 1 To MaxLen + 1

If i / 2 = Round(i / 2, 0) Then

'Splits Loans Horizontally to pass Total Bal of "Aplha" for "i"

For n = 1 To Notes

NoteHold(n, 10) = NoteHold(n, 10) & Mid(NoteHold(n, 9), i, 1)

SumNoteBal = 0

For m = 1 To Notes

If NoteHold(n, 10) = Left(NoteHold(m, 9), i) Then

SumNoteBal = NoteHold(m, 3) + SumNoteBal

End If

Next

NoteHold(n, 6) = SumNoteBal

Next

''---------------------------------STUCK HERE----------------------

'Counts Notes in "i-Alpha" position

For n = 1 To Notes

For m = 1 To Notes

NoteCount = 0

If Left(NoteHold(n, 10), i) <> Left(NoteHold(m, 9), i) Then

NoteCount = NoteCount + 1

End If

NoteHold(n, 12) = NoteCount

Next

Next

For n = 1 To Notes

For m = n To NoteHold(n, 12)

If Left(NoteHold(n, 10), i) = Left(NoteHold(m, 9), i) Then

CumNoteBal = NoteHold(n, 5) - NoteHold(m, 3)

End If

Next

NoteHold(n, 11) = CumNoteBal

Next

''-----------------------------------------------------------------

'Applies the Loss

For n = 1 To Notes

NoteHold(n, 7) = Application.WorksheetFunction.Min _

(Application.WorksheetFunction.Max _

(NoteHold(n, 7) - (NoteHold(n, 5) - NoteHold(n, 11)), 0), NoteHold(n, 6))

' NoteHold(n, 7) = Application.WorksheetFunction.Max _

' (Application.WorksheetFunction.Min _

' (NoteHold(n, 6), NoteHold(n, 7) - (NoteHold(n, 5) - NoteHold(n, 11))), 0)

Next

'Set New Note Allocation for Loan Group

For n = 1 To Notes

NoteHold(n, 5) = NoteHold(n, 6)

Next

Else

'Counting "Numbers" in the Note Structure for given position

For n = 1 To Notes

NoteHold(n, 10) = NoteHold(n, 10) & Mid(NoteHold(n, 9), i, 1)

SumNoteBal = 0

For m = 1 To Notes

If NoteHold(n, 10) = Left(NoteHold(m, 9), i) Then

SumNoteBal = NoteHold(m, 3) + SumNoteBal

End If

Next

NoteHold(n, 6) = SumNoteBal

Next

'Vertical splitting of Losses at given position

For n = 1 To Notes

NoteHold(n, 7) = NoteHold(n, 7) * NoteHold(n, 6) / NoteHold(n, 5)

Next

'Set New Note Allocation for Loan Group

For n = 1 To Notes

NoteHold(n, 5) = NoteHold(n, 6)

Next

End If

For n = 1 To Notes

Cells(n + 51, i + 5).Value = NoteHold(n, 7)

Next

Next

End Sub

Thanks in Advance,

Steve

This is my first post here, so thanks for the effort in putting up the site.

I am having some trouble with a particular area in my code. I am stumped as to how to move forward and was hoping that someone could redirect me or provide some guidance. I am trying to split balances both as a weighted average as well as stacked one on top of the other. The balances then need to be carried forward into the next structure/group so that the process can be repeated.

I have built the idea on the sheet, but I haven't been able to make the logic work in VB. I keep getting stuck at accumulating balances when they are being stacked. See Column 11 in the Array NoteHold for reference.

Because this is a bit hard to describe, I am also attaching my working Excel file. The code is below and is also in the Excel File. The Sheet is a good place to see how the code should work. I apologize for the Long Code. I have been trying different methods and have possibly left unnecessary information in the code.

Sub Loss()

Dim DefaultValue As Double

Notes = 13 'represents "All Notes"

DefaultValue = Range("i23") 'Value of the Family (Portfolio Value)

ReDim NoteHold(Notes, 13)

'Load Notes in the same Family-Loan Structure

j = 1

For i = 1 To Notes

If Cells(5 + i, 4) & Cells(5 + i, 5) = Range("e24") Then 'Modify as Family-Note combo in DB

NoteHold(j, 1) = Cells(5 + i, 4) & Cells(5 + i, 5) 'Loan Name (Constant)

NoteHold(j, 2) = "1" & Cells(5 + i, 13) 'Note Name (Constant)

NoteHold(j, 3) = Cells(5 + i, 9) 'Note Amount (Constant)

NoteHold(j, 4) = Len(Cells(5 + i, 13)) 'Note Length (Constant)

NoteHold(j, 8) = j 'Note Count (Constant)

j = j + 1

End If

Next

'Fill in Overall Loan Structure

For i = 1 To Notes

MaxLen = Application.WorksheetFunction.Max(NoteHold(i, 4), MaxLen)

Next

For i = 1 To Notes

For j = 1 To MaxLen

If j <= NoteHold(i, 4) Then

NoteHold(i, 9) = NoteHold(i, 2)

Else

NoteHold(i, 9) = Left(NoteHold(i, 9), j) & IIf(j / 2 <> Round(j / 2, 0), "A", "1")

End If

Next

LoanBal = NoteHold(i, 3) + LoanBal

Next

'Load Initial Loss and Loan Balance

LoanLoss = Application.WorksheetFunction.Max(LoanBal - DefaultValue, 0)

For n = 1 To Notes

NoteHold(n, 5) = LoanBal

NoteHold(n, 7) = LoanLoss

Next

'Loop through the Notes Structures where MaxLen is the length of the full Note Name

For i = 1 To MaxLen + 1

If i / 2 = Round(i / 2, 0) Then

'Splits Loans Horizontally to pass Total Bal of "Aplha" for "i"

For n = 1 To Notes

NoteHold(n, 10) = NoteHold(n, 10) & Mid(NoteHold(n, 9), i, 1)

SumNoteBal = 0

For m = 1 To Notes

If NoteHold(n, 10) = Left(NoteHold(m, 9), i) Then

SumNoteBal = NoteHold(m, 3) + SumNoteBal

End If

Next

NoteHold(n, 6) = SumNoteBal

Next

''---------------------------------STUCK HERE----------------------

'Counts Notes in "i-Alpha" position

For n = 1 To Notes

For m = 1 To Notes

NoteCount = 0

If Left(NoteHold(n, 10), i) <> Left(NoteHold(m, 9), i) Then

NoteCount = NoteCount + 1

End If

NoteHold(n, 12) = NoteCount

Next

Next

For n = 1 To Notes

For m = n To NoteHold(n, 12)

If Left(NoteHold(n, 10), i) = Left(NoteHold(m, 9), i) Then

CumNoteBal = NoteHold(n, 5) - NoteHold(m, 3)

End If

Next

NoteHold(n, 11) = CumNoteBal

Next

''-----------------------------------------------------------------

'Applies the Loss

For n = 1 To Notes

NoteHold(n, 7) = Application.WorksheetFunction.Min _

(Application.WorksheetFunction.Max _

(NoteHold(n, 7) - (NoteHold(n, 5) - NoteHold(n, 11)), 0), NoteHold(n, 6))

' NoteHold(n, 7) = Application.WorksheetFunction.Max _

' (Application.WorksheetFunction.Min _

' (NoteHold(n, 6), NoteHold(n, 7) - (NoteHold(n, 5) - NoteHold(n, 11))), 0)

Next

'Set New Note Allocation for Loan Group

For n = 1 To Notes

NoteHold(n, 5) = NoteHold(n, 6)

Next

Else

'Counting "Numbers" in the Note Structure for given position

For n = 1 To Notes

NoteHold(n, 10) = NoteHold(n, 10) & Mid(NoteHold(n, 9), i, 1)

SumNoteBal = 0

For m = 1 To Notes

If NoteHold(n, 10) = Left(NoteHold(m, 9), i) Then

SumNoteBal = NoteHold(m, 3) + SumNoteBal

End If

Next

NoteHold(n, 6) = SumNoteBal

Next

'Vertical splitting of Losses at given position

For n = 1 To Notes

NoteHold(n, 7) = NoteHold(n, 7) * NoteHold(n, 6) / NoteHold(n, 5)

Next

'Set New Note Allocation for Loan Group

For n = 1 To Notes

NoteHold(n, 5) = NoteHold(n, 6)

Next

End If

For n = 1 To Notes

Cells(n + 51, i + 5).Value = NoteHold(n, 7)

Next

Next

End Sub

Thanks in Advance,

Steve