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