Untitled
Greg
plain_text
6 months ago
8.1 kB
6
Indexable
Sub ExtractTransactions() Dim reconWorkbook As Workbook Dim editedOutputWorkbook As Workbook Dim portiaTransactionsWorkbook As Workbook Dim accountsSheet As Worksheet Dim transactionsSheet As Worksheet Dim pasteSheet As Worksheet Dim portiaTransactionsSheet As Worksheet Dim folderPath As String Dim folderName As String Dim editedOutputFilename As String Dim portiaTransactionsFilename As String Dim lastRow As Long Dim accountRow As Range Dim foundRow As Range Dim accountNumber As String Dim fundNumber As String Dim transactionsRow As Long Dim firstAddress As String Dim valueW As Variant Dim valueV As Variant Dim cashCUSIP As String Dim updatedGValue As String ' Set folder path and extract folder name folderPath = ThisWorkbook.Path & "\" folderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") + 1) ' Construct the edited output and Portia transactions filenames editedOutputFilename = folderPath & folderName & ".SS Daily Cash Transactions_Edited_Output.xlsx" portiaTransactionsFilename = folderPath & folderName & ".Manual Cash Trans.xlsx" ' Adjust the filename as necessary ' Open the Edited Output workbook and Portia workbook only once Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename) Set accountsSheet = ThisWorkbook.Sheets("accounts") Set transactionsSheet = ThisWorkbook.Sheets("transactions") Set portiaTransactionsWorkbook = Workbooks.Open(portiaTransactionsFilename) Set portiaTransactionsSheet = portiaTransactionsWorkbook.Sheets(1) ' Adjust the sheet index if necessary ' Clear previous data and formatting in transactions sheet With transactionsSheet .Cells.ClearContents .Cells.ClearFormats End With ' Add column headers in row 1 from A:H transactionsSheet.Range("A1:H1").Value = Array("Account", "Source", "Date", "CUSIP", "Description", "Amount", "Principal", "Interest") ' Start pasting rows from row 2 transactionsRow = 2 ' Set Paste SS Transactions sheet Set pasteSheet = editedOutputWorkbook.Sheets("Paste SS Transactions") ' Loop through each fund number in Column A of the accounts sheet lastRow = accountsSheet.Cells(accountsSheet.Rows.Count, 1).End(xlUp).Row For Each accountRow In accountsSheet.Range("A2:A" & lastRow) fundNumber = accountRow.Value cashCUSIP = accountRow.Offset(0, 2).Value ' Get the CUSIP from Column C ' Find the fund number in the Paste SS Transactions sheet Set foundRow = pasteSheet.Columns("A").Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole) If Not foundRow Is Nothing Then firstAddress = foundRow.Address Do ' Check for additional criteria in columns F and G If Right(foundRow.Offset(0, 5).Value, 4) = "/000" And foundRow.Offset(0, 6).Value = "US DOLLAR" Then ' Get values for W and V valueW = foundRow.Offset(0, 22).Value ' Column W valueV = foundRow.Offset(0, 21).Value ' Column V ' Check if either valueW or valueV exists If Not IsEmpty(valueW) Or Not IsEmpty(valueV) Then ' Check if CUSIP matches, if so, exclude If foundRow.Offset(0, 14).Value <> cashCUSIP Then ' Column O ' Copy values into the transactions sheet transactionsSheet.Cells(transactionsRow, 1).Value = fundNumber ' Column A - Account transactionsSheet.Cells(transactionsRow, 2).Value = "BK" ' Column B - Source transactionsSheet.Cells(transactionsRow, 3).Value = foundRow.Offset(0, 12).Value ' Column M - Date transactionsSheet.Cells(transactionsRow, 4).Value = foundRow.Offset(0, 13).Value ' Column N - CUSIP transactionsSheet.Cells(transactionsRow, 5).Value = "" ' Blank column after Date for BK ' Check for values in Column W and adjust accordingly If Not IsEmpty(valueW) Then transactionsSheet.Cells(transactionsRow, 6).Value = -valueW ' Column W multiplied by -1 (Amount) Else transactionsSheet.Cells(transactionsRow, 6).Value = valueV ' Column V (Amount) End If ' Copy values from AF and AG for Principal and Interest transactionsSheet.Cells(transactionsRow, 7).Value = foundRow.Offset(0, 31).Value ' Column AF - Principal transactionsSheet.Cells(transactionsRow, 8).Value = foundRow.Offset(0, 32).Value ' Column AG - Interest transactionsRow = transactionsRow + 1 ' Move to the next row in transactions sheet End If End If End If ' Continue searching for the next occurrence of the FundNumber Set foundRow = pasteSheet.Columns("A").FindNext(foundRow) Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress End If Next accountRow ' Now loop through each account number in Column B of the accounts sheet For Each accountRow In accountsSheet.Range("B2:B" & lastRow) accountNumber = accountRow.Value ' Find the account number in the Portia transactions sheet Set foundRow = portiaTransactionsSheet.Columns("A").Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole) If Not foundRow Is Nothing Then firstAddress = foundRow.Address Do ' Check if Column F is "-CASH-" and that there is a value in W or V before pasting If foundRow.Offset(0, 5).Value = "-CASH-" Then ' Column F valueW = foundRow.Offset(0, 22).Value ' Column W valueV = foundRow.Offset(0, 21).Value ' Column V If Not IsEmpty(valueW) Or Not IsEmpty(valueV) Then ' Update value of G by adding an apostrophe updatedGValue = "'" & foundRow.Offset(0, 6).Value ' Column G with an apostrophe ' Check if the updated value of G matches the cash CUSIP, if so, exclude If updatedGValue <> cashCUSIP Then ' Paste values into the transactions sheet without formatting With transactionsSheet .Cells(transactionsRow, 1).Value = foundRow.Offset(0, 2).Value ' Column C - Account .Cells(transactionsRow, 2).Value = foundRow.Offset(0, 3).Value ' Column D - Source .Cells(transactionsRow, 3).Value = updatedGValue ' Column G with apostrophe - Date .Cells(transactionsRow, 4).Value = foundRow.Offset(0, 21).Value ' Column V - CUSIP End With transactionsRow = transactionsRow + 1 ' Move to the next row in transactions sheet End If End If End If ' Continue searching for the next occurrence of the AccountNumber Set foundRow = portiaTransactionsSheet.Columns("A").FindNext(foundRow) Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress End If Next accountRow ' Close the workbooks after processing editedOutputWorkbook.Close False portiaTransactionsWorkbook.Close False MsgBox "Transaction extraction complete!", vbInformation End Sub
Editor is loading...
Leave a Comment