Untitled

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 filename
    editedOutputFilename = folderPath & folderName & ".SS Daily Cash Transactions_Edited_Output.xlsx"
    
    ' Construct the Portia transactions filename
    portiaTransactionsFilename = folderPath & folderName & ".Portia Transactions.xlsx" ' Change the filename as necessary

    ' Open the Edited Output workbook
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set transactionsSheet = ThisWorkbook.Sheets("transactions")

    ' Clear previous data and formatting in transactions sheet
    With transactionsSheet
        .Cells.ClearContents
        .Cells.ClearFormats
    End With

    ' Start pasting rows from row 2
    transactionsRow = 2

    ' Loop through each fund number in Column A of 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 pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file ")
        lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
        Set foundRow = pasteSheet.Range("A1:A" & lastRow).Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address ' Save the first address to avoid endless loops
            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
                            transactionsSheet.Cells(transactionsRow, 2).Value = "BK"       ' Column B
                            transactionsSheet.Cells(transactionsRow, 3).Value = foundRow.Offset(0, 12).Value ' Column M
                            transactionsSheet.Cells(transactionsRow, 4).Value = foundRow.Offset(0, 13).Value ' Column N
                            
                            ' Check for values in Column W and adjust accordingly
                            If Not IsEmpty(valueW) Then
                                transactionsSheet.Cells(transactionsRow, 5).Value = -valueW ' Column W multiplied by -1
                            Else
                                transactionsSheet.Cells(transactionsRow, 5).Value = valueV ' Column V
                            End If
                            
                            ' Copy values from AF and AG
                            transactionsSheet.Cells(transactionsRow, 6).Value = foundRow.Offset(0, 31).Value ' Column AF
                            transactionsSheet.Cells(transactionsRow, 7).Value = foundRow.Offset(0, 32).Value ' Column AG
                            
                            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.Range("A1:A" & lastRow).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 accounts sheet
    For Each accountRow In accountsSheet.Range("B2:B" & lastRow)
        accountNumber = accountRow.Value
        
        ' Open the Portia transactions workbook
        Set portiaTransactionsWorkbook = Workbooks.Open(portiaTransactionsFilename)
        Set portiaTransactionsSheet = portiaTransactionsWorkbook.Sheets(1) ' Adjust the sheet index if necessary

        ' Find the account number in the Portia transactions sheet
        lastRow = portiaTransactionsSheet.Cells(portiaTransactionsSheet.Rows.Count, 1).End(xlUp).Row
        Set foundRow = portiaTransactionsSheet.Range("A1:A" & lastRow).Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole)

        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address ' Save the first address to avoid endless loops
            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
                                .Cells(transactionsRow, 2).Value = foundRow.Offset(0, 3).Value ' Column D
                                .Cells(transactionsRow, 3).Value = updatedGValue             ' Column G with apostrophe
                                .Cells(transactionsRow, 4).Value = foundRow.Offset(0, 21).Value ' Column V
                            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.Range("A1:A" & lastRow).FindNext(foundRow)
            Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
        End If

        portiaTransactionsWorkbook.Close False ' Close the Portia transactions workbook
    Next accountRow

    ' Save the edited output workbook
    editedOutputWorkbook.Close False
    
    MsgBox "Transaction extraction complete!", vbInformation
End Sub