Untitled

Sub UpdateLedgersWithValues()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim ledgersSheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim ssTransactionsSheet As Worksheet
    Dim portiaWorkbook As Workbook
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim portiaFilename As String
    Dim accountRow As Range
    Dim foundRow As Range
    Dim firstAddress As String
    Dim lastCol As Long
    Dim accountCell As Range
    Dim fundNumber As String
    Dim cusip As String
    Dim rValue As Variant
    Dim pasteRange As Range
    Dim lastRow As Long
    Dim matchFound As Boolean
    Dim cashValues As Collection
    Dim valueCount As Object
    Dim uninvestedCash As Variant
    Dim maxCount As Long
    Dim mostCommonValue As Variant
    Dim columnYValue As Double
    Dim columnZValue As Double
    Dim portiaValue As Variant
    
    ' 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 filename
    portiaFilename = folderPath & folderName & ".Portia Settled Cash Balances.xlsx"

    ' Open the Edited Output workbook
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    
    ' Check if the "ledgers" sheet exists
    On Error Resume Next
    Set ledgersSheet = ThisWorkbook.Sheets("ledgers")
    On Error GoTo 0
    
    If ledgersSheet Is Nothing Then
        MsgBox "The 'ledgers' sheet was not found!", vbCritical
        Exit Sub
    End If
    
    Set pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file")
    Set ssTransactionsSheet = editedOutputWorkbook.Sheets("Paste SS Transactions")

    ' Find the last column in Row 1 of the ledgers sheet
    lastCol = ledgersSheet.Cells(1, ledgersSheet.Columns.Count).End(xlToLeft).Column

    ' Loop through each account in Row 1
    For Each accountCell In ledgersSheet.Range("B1:" & ledgersSheet.Cells(1, lastCol).Address)
        If accountCell.Value <> "" Then
            ' Find account in the accounts sheet
            Set accountRow = accountsSheet.Columns("B").Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)

            If Not accountRow Is Nothing Then
                fundNumber = CStr(accountRow.Offset(0, -1).Value) ' Column A, ensure it's a string
                cusip = CStr(accountRow.Offset(0, 1).Value) ' Column C, ensure it's a string
                
                ' Debugging: Output fund number
                Debug.Print "Fund Number: " & fundNumber

                ' Find the row in the Paste SS Transactions sheet for invested cash (with CUSIP matching)
                lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
                Set pasteRange = pasteSheet.Range("A1:R" & lastRow)

                ' Initialize the search for FundName in the Paste SS Transactions sheet
                Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                
                matchFound = False ' Reset match flag for each account

                If Not foundRow Is Nothing Then
                    firstAddress = foundRow.Address ' Remember the first match to avoid looping endlessly

                    ' Loop through all matches of the FundNumber for invested cash
                    Do
                        If foundRow.Offset(0, 10).Value = cusip Then ' Column K for CUSIP
                            If Not IsError(foundRow.Offset(0, 17).Value) Then ' Column R for invested cash
                                rValue = foundRow.Offset(0, 17).Value ' Column R is the 18th column from A
                                ledgersSheet.Cells(2, accountCell.Column).Value = rValue
                                matchFound = True
                                
                                ' Debugging: Output the retrieved R value
                                Debug.Print "Invested Cash R Value: " & rValue
                            Else
                                ' Handle the error case
                                ledgersSheet.Cells(2, accountCell.Column).Value = "Error"
                                Debug.Print "Error in Invested Cash for Fund Number: " & fundNumber
                            End If
                        End If
                        
                        ' Continue searching for the next occurrence of the FundNumber
                        Set foundRow = pasteRange.FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                End If
                
                If Not matchFound Then
                    ledgersSheet.Cells(2, accountCell.Column).Value = ""
                End If

                ' Reset for uninvested cash calculation
                Set foundRow = Nothing
                matchFound = False 
                Set cashValues = New Collection 

                ' Find the row in the Paste SS Transactions sheet for cash (without CUSIP matching)
                lastRow = ssTransactionsSheet.Cells(ssTransactionsSheet.Rows.Count, 1).End(xlUp).Row
                Set pasteRange = ssTransactionsSheet.Range("A1:Y" & lastRow)

                ' Initialize the search for FundName in the Paste SS Transactions sheet
                Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                
                If Not foundRow Is Nothing Then
                    firstAddress = foundRow.Address ' Remember the first match to avoid looping endlessly

                    ' Loop through all matches of the FundNumber
                    Do
                        ' Ensure Column F ends with "/000" and Column G is "US DOLLAR"
                        If Right(CStr(foundRow.Offset(0, 5).Value), 4) = "/000" And CStr(foundRow.Offset(0, 6).Value) = "US DOLLAR" Then
                            
                            ' Handle blanks by assigning 0 if the cell is empty
                            If IsEmpty(foundRow.Offset(0, 24).Value) Then
                                columnYValue = 0
                            Else
                                columnYValue = foundRow.Offset(0, 24).Value
                            End If

                            If IsEmpty(foundRow.Offset(0, 25).Value) Then
                                columnZValue = 0
                            Else
                                columnZValue = foundRow.Offset(0, 25).Value
                            End If

                            ' Debugging: Output the values found
                            Debug.Print "Found FundNumber: " & fundNumber & " | Y: " & columnYValue & " | Z: " & columnZValue

                            ' Add Y value to the collection unless it's 0; if Y is 0, add negative Z
                            If columnYValue > 0 Then
                                cashValues.Add columnYValue
                            Else
                                cashValues.Add -columnZValue
                            End If
                            matchFound = True
                        End If
                        
                        ' Continue searching for the next occurrence of the FundNumber
                        Set foundRow = pasteRange.FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                End If
                
                ' Debugging: Output how many values were found
                Debug.Print "Number of matches for FundNumber " & fundNumber & ": " & cashValues.Count

                ' If matches were found, calculate the most common value from Column Y
                If matchFound And cashValues.Count > 0 Then
                    Set valueCount = CreateObject("Scripting.Dictionary")
                    
                    ' Count occurrences of each cash value
                    For Each uninvestedCash In cashValues
                        If valueCount.Exists(uninvestedCash) Then
                            valueCount(uninvestedCash) = valueCount(uninvestedCash) + 1
                        Else
                            valueCount.Add uninvestedCash, 1
                        End If
                    Next uninvestedCash
                    
                    ' Find the most common cash value
                    maxCount = 0
                    For Each uninvestedCash In valueCount.Keys
                        If valueCount(uninvestedCash) > maxCount Then
                            maxCount = valueCount(uninvestedCash)
                            mostCommonValue = uninvestedCash
                        End If
                    Next uninvestedCash
                    
                    ' Set the most common value in Row 3 of the ledgers sheet
                    ledgersSheet.Cells(3, accountCell.Column).Value = mostCommonValue
                    
                    ' Debugging: Output the most common uninvested cash value
                    Debug.Print "Most Common Uninvested Cash: " & mostCommonValue
                Else
                    ' If no match was found, leave the cell blank
                    ledgersSheet.Cells(3, accountCell.Column).Value = ""
                    Debug.Print "No valid cash values found for FundNumber: " & fundNumber
                End If

                ' If invested and uninvested cash are found, sum them in row 4
                Dim valueRow2 As Variant
                Dim valueRow3 As Variant

                valueRow2 = ledgersSheet.Cells(2, accountCell.Column).Value
                valueRow3 = ledgersSheet.Cells(3, accountCell.Column).Value

                If IsNumeric(valueRow2) And IsNumeric(valueRow3) Then
                    ledgersSheet.Cells(4, accountCell.Column).Value = valueRow2 + valueRow3
                Else
                    ledgersSheet.Cells(4, accountCell.Column).Value = "N/A" ' Handle cases where values are not numeric
                End If

                ' Retrieve balance from the Portia workbook for row 8
                On Error Resume Next
                Set portiaWorkbook = Workbooks.Open(portiaFilename, ReadOnly:=True)
                On Error GoTo 0
                
                If Not portiaWorkbook Is Nothing Then
                    Dim portiaSheet As Worksheet
                    Set portiaSheet = portiaWorkbook.Sheets(1) ' Adjust the sheet index as necessary

                    ' Assuming the balance is in Column C and we're matching with Column A
                    lastRow = portiaSheet.Cells(portiaSheet.Rows.Count, 1).End(xlUp).Row
                    Set foundRow = portiaSheet.Range("A1:A" & lastRow).Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)

                    If Not foundRow Is Nothing Then
                        portiaValue = foundRow.Offset(0, 2).Value ' Column C
                        ledgersSheet.Cells(8, accountCell.Column).Value = portiaValue
                        Debug.Print "Portia Balance for " & accountCell.Value & ": " & portiaValue
                    Else
                        ledgersSheet.Cells(8, accountCell.Column).Value = "N/A" ' No match found
                        Debug.Print "No matching account in Portia for: " & accountCell.Value
                    End If
                    
                    portiaWorkbook.Close False
                Else
                    MsgBox "Failed to open the Portia workbook!", vbCritical
                End If
            End If
        End If
    Next accountCell

    ' Save and close the edited output workbook
    editedOutputWorkbook.Save
    editedOutputWorkbook.Close

    MsgBox "Update complete!", vbInformation
End Sub