Untitled
Greg
plain_text
6 months ago
5.0 kB
6
Indexable
Sub PairOffExceptions() Dim reconWorkbook As Workbook Dim exceptionsSheet As Worksheet Dim transactionsSheet As Worksheet Dim transactionList As Collection Dim lastTransactionRow As Long Dim transactionRow As Long Dim currentTransaction As Variant Dim compareTransaction As Variant Dim offsetTolerance As Double Dim i As Long, j As Long, k As Long Dim cusip As String, paydownValue As Double, gValue As Double, hValue As Double Dim foundG As Boolean, foundH As Boolean Dim sumOfComponents As Double, componentCount As Integer ' Initialize workbook and sheets Set reconWorkbook = ThisWorkbook Set exceptionsSheet = reconWorkbook.Sheets("exceptions") Set transactionsSheet = reconWorkbook.Sheets("transactions") ' Initialize collection for in-memory storage Set transactionList = New Collection ' Define offset tolerance offsetTolerance = 0.02 ' Load transactions into memory, filtering by "PAYDOWN RECEIVED" and specific transaction types lastTransactionRow = transactionsSheet.Cells(transactionsSheet.Rows.Count, "A").End(xlUp).Row For transactionRow = 2 To lastTransactionRow If transactionsSheet.Cells(transactionRow, "A").Value = "6QFG" Or transactionsSheet.Cells(transactionRow, "A").Value = "RUSECP" Then Dim transData(0 To 7) As Variant ' Array to store transaction details transData(0) = transactionsSheet.Cells(transactionRow, "A").Value ' Column A (Transaction ID) transData(1) = transactionsSheet.Cells(transactionRow, "D").Value ' Column D (CUSIP) transData(2) = transactionsSheet.Cells(transactionRow, "F").Value ' Column F (Amount) transData(3) = transactionsSheet.Cells(transactionRow, "B").Value ' Column B (Source) transData(4) = transactionsSheet.Cells(transactionRow, "E").Value ' Column E (Description) transData(5) = transactionsSheet.Cells(transactionRow, "G").Value ' Column G (Principle) transData(6) = transactionsSheet.Cells(transactionRow, "H").Value ' Column H (Interest) transData(7) = False ' Processed flag transactionList.Add transData ' Add the transaction to the list End If Next transactionRow ' Iterate through transactions in memory For i = 1 To transactionList.Count currentTransaction = transactionList(i) ' Check for "PAYDOWN RECEIVED" in column E and if the transaction hasn't been processed If currentTransaction(4) = "PAYDOWN RECEIVED" And currentTransaction(7) = False Then cusip = currentTransaction(1) paydownValue = currentTransaction(2) gValue = currentTransaction(5) hValue = currentTransaction(6) foundG = False foundH = False sumOfComponents = 0 componentCount = 0 ' Loop through remaining transactions to find matches for G and H For j = 1 To transactionList.Count compareTransaction = transactionList(j) If j <> i And compareTransaction(7) = False And compareTransaction(1) = cusip Then Dim compareAmount As Double compareAmount = compareTransaction(2) ' Check for G and H component matching within tolerance If Abs(compareAmount + gValue) <= offsetTolerance Then foundG = True sumOfComponents = sumOfComponents + compareAmount transactionList(j)(7) = True componentCount = componentCount + 1 ElseIf Abs(compareAmount + hValue) <= offsetTolerance Then foundH = True sumOfComponents = sumOfComponents + compareAmount transactionList(j)(7) = True componentCount = componentCount + 1 End If ' If both components match and sum close to paydown value, mark as processed If foundG And foundH And Abs(paydownValue + sumOfComponents) <= offsetTolerance Then currentTransaction(7) = True Exit For End If End If Next j ' If all components matched, delete rows in reverse order If foundG And foundH And Abs(paydownValue + sumOfComponents) <= offsetTolerance Then exceptionsSheet.Rows(i + 1).Delete For k = transactionList.Count To 1 Step -1 If transactionList(k)(7) = True Then exceptionsSheet.Rows(k + 1).Delete End If Next k End If End If Next i MsgBox "Pair-off exceptions complete!", vbInformation End Sub
Editor is loading...
Leave a Comment