Untitled
Greg
plain_text
6 months ago
9.5 kB
4
Indexable
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 folderPath As String
Dim folderName As String
Dim editedOutputFilename 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
' 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")
' 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
Editor is loading...
Leave a Comment