Untitled
Greg
plain_text
5 months ago
8.9 kB
3
Indexable
Sub UpdateLedgersWithValues()
Dim reconWorkbook As Workbook
Dim editedOutputWorkbook As Workbook
Dim accountsSheet As Worksheet
Dim ledgersSheet As Worksheet
Dim pasteSheet As Worksheet
Dim portiaWorkbook As Workbook
Dim portiaSheet As Worksheet
Dim folderPath As String
Dim folderName As String
Dim editedOutputFilename As String
Dim portiaFilename As String
Dim accountCell As Range
Dim lastCol As Long
Dim fundNumber As String
Dim cusip As String
Dim rValue As Variant
Dim pasteRange As Range
Dim lastRow As Long
Dim foundRow As Range
Dim firstAddress As String
Dim portiaValue As Variant
Dim accountRow As Range
Dim yValues As Collection
Dim zValues As Collection
Dim value As Variant
Dim maxCount As Long
Dim mostCommonValue As Variant
Dim dict As Object
Dim i As Long
' 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"
' Open the Edited Output workbook
Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
Set accountsSheet = ThisWorkbook.Sheets("accounts")
Set ledgersSheet = ThisWorkbook.Sheets("ledgers")
Set pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file")
' Open the Portia workbook
portiaFilename = folderPath & folderName & ".Portia Settled Cash Balances.xlsx"
On Error Resume Next
Set portiaWorkbook = Workbooks.Open(portiaFilename)
On Error GoTo 0
If portiaWorkbook Is Nothing Then
MsgBox "The Portia Settled Cash Balances file was not found!", vbCritical
Exit Sub
End If
Set portiaSheet = portiaWorkbook.Sheets("prior day settled cash")
' 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 = accountRow.Offset(0, -1).Value ' Column A
cusip = accountRow.Offset(0, 1).Value ' Column C
' Debugging: Output fund number and CUSIP
Debug.Print "Fund Number: " & fundNumber & ", CUSIP: " & cusip
' Update Row 2 (Invested Cash)
lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
Set pasteRange = pasteSheet.Range("A1:R" & lastRow)
Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRow Is Nothing Then
' Get values for invested cash from pasteSheet
If foundRow.Offset(0, 10).Value = cusip Then ' Column K
If Not IsError(foundRow.Offset(0, 17).Value) Then ' Column R
rValue = foundRow.Offset(0, 17).Value
ledgersSheet.Cells(2, accountCell.Column).Value = rValue
Debug.Print "Row 2 - R Value: " & rValue
Else
ledgersSheet.Cells(2, accountCell.Column).Value = "Error"
Debug.Print "Row 2 - Error in R Value for Fund Number: " & fundNumber
End If
Else
Debug.Print "Row 2 - CUSIP mismatch for Fund Number: " & fundNumber
End If
Else
Debug.Print "Row 2 - Fund Number not found in Paste Sheet: " & fundNumber
End If
' Initialize collections for Y and Z values
Set yValues = New Collection
Set zValues = New Collection
' Update Row 3 (Uninvested Cash)
lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
Set pasteRange = pasteSheet.Range("A1:R" & lastRow)
Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRow Is Nothing Then
firstAddress = foundRow.Address
Do
' Check the conditions for Y and Z values
If Right(foundRow.Offset(0, 5).Value, 4) = "/000" And foundRow.Offset(0, 6).Value = "US DOLLAR" Then
If IsNumeric(foundRow.Offset(0, 24).Value) And Not IsEmpty(foundRow.Offset(0, 24).Value) Then
yValues.Add foundRow.Offset(0, 24).Value ' Add Column Y
ElseIf IsNumeric(foundRow.Offset(0, 25).Value) And Not IsEmpty(foundRow.Offset(0, 25).Value) Then
zValues.Add -foundRow.Offset(0, 25).Value ' Add Column Z as negative
End If
End If
Set foundRow = pasteRange.FindNext(foundRow)
Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
End If
' Calculate the most common value for Row 3
If yValues.Count > 0 Then
Set dict = CreateObject("Scripting.Dictionary")
For Each value In yValues
If Not dict.Exists(value) Then
dict(value) = 1
Else
dict(value) = dict(value) + 1
End If
Next value
' Find the most common value for Y
For Each value In dict.Keys
If dict(value) > maxCount Then
maxCount = dict(value)
mostCommonValue = value
End If
Next value
ledgersSheet.Cells(3, accountCell.Column).Value = mostCommonValue
ElseIf zValues.Count > 0 Then
' If no valid Y values, check Z values
Set dict = CreateObject("Scripting.Dictionary")
For Each value In zValues
If Not dict.Exists(value) Then
dict(value) = 1
Else
dict(value) = dict(value) + 1
End If
Next value
' Find the most common value for Z
maxCount = 0
For Each value In dict.Keys
If dict(value) > maxCount Then
maxCount = dict(value)
mostCommonValue = value
End If
Next value
ledgersSheet.Cells(3, accountCell.Column).Value = mostCommonValue
Else
ledgersSheet.Cells(3, accountCell.Column).Value = 0 ' Default to 0 if no matches
End If
' Update Row 8 (Portia Values)
lastRow = portiaSheet.Cells(portiaSheet.Rows.Count, 1).End(xlUp).Row
Set foundRow = portiaSheet.Range("A1:C" & lastRow).Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole) ' Match account number
If Not foundRow Is Nothing Then
firstAddress = foundRow.Address
Do
If CStr(foundRow.Offset(0, 1).Value) Like "*-CASH-*" Then
portiaValue = foundRow.Offset(0, 2).Value ' Column C
ledgersSheet.Cells(8, accountCell.Column).Value = portiaValue
Debug.Print "Row 8 - Portia Value: " & portiaValue
End If
Set foundRow = portiaSheet.Range("A1:C" & lastRow).FindNext(foundRow)
Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
Else
Debug.Print "Row 8 - Account Number not found in Portia Sheet: " & accountCell.Value
End If
Else
Debug.Print "Account not found for: " & accountCell.Value
End If
End If
Next accountCell
' Close the workbooks
editedOutputWorkbook.Close SaveChanges:=False
portiaWorkbook.Close SaveChanges:=False
MsgBox "Ledgers updated successfully!"
End Sub
Editor is loading...
Leave a Comment