Trim Balance History

Microsoft Excel
1

I see there is a nice tool for google sheets to trim balance history. Does anyone have a similar tool for excel?

1 Like
2

Good question, @buzzmaster1. Unfortunately, I believe the answer is that there isn’t an updated version for Excel.

The Sheets Balance History Trimmer (in the Tiller Community Solutions add-on) is something we built a long time ago. I think it does a good job of diminishing granularity as you get further out in time by keeping a balance every day for recent months then fading to a balance every month further out.

Would be great to see a modern version of this for Excel (and possibly an updated version for Sheets)…

1 Like
3

I used Perplexity to generate VBA code to trim down to weekly data. It worked fine on my sheet. I will make a couple of changes like going to monthly if older than a certain age. But here is the code if anyone wants it:

Sub KeepWeeklyDataAllAccounts()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim currentDate As Date
Dim accountColumn As Integer
Dim dateColumn As Integer
Dim dict As Object
Dim key As String

Set ws = ThisWorkbook.Worksheets("Balance History")
Set dict = CreateObject("Scripting.Dictionary")

' Adjust these column numbers based on your data structure
dateColumn = 1  ' Assuming date is in column A
accountColumn = 12  ' Assuming account is in column B

lastRow = ws.Cells(ws.Rows.Count, dateColumn).End(xlUp).Row

Application.ScreenUpdating = False

' First pass: Identify rows to keep
For i = 2 To lastRow ' Assuming row 1 is header
    currentDate = ws.Cells(i, dateColumn).Value
    accountNumber = ws.Cells(i, accountColumn).Value
    weekStart = DateSerial(Year(currentDate), Month(currentDate), Day(currentDate) - Weekday(currentDate, vbMonday) + 1)
    
    key = accountNumber & "_" & Format(weekStart, "yyyy-mm-dd")
    
    If Not dict.Exists(key) Then
        dict.Add key, i
    End If
Next i

' Second pass: Delete rows not in the dictionary
For i = lastRow To 2 Step -1
    currentDate = ws.Cells(i, dateColumn).Value
    accountNumber = ws.Cells(i, accountColumn).Value
    weekStart = DateSerial(Year(currentDate), Month(currentDate), Day(currentDate) - Weekday(currentDate, vbMonday) + 1)
    
    key = accountNumber & "_" & Format(weekStart, "yyyy-mm-dd")
    
    If dict(key) <> i Then
        ws.Rows(i).Delete
    End If
Next i

Application.ScreenUpdating = True

MsgBox "Weekly data for all accounts has been kept. All other rows have been removed.", vbInformation

End Sub

2 Likes
4

I could not find the trim balance history add on. How do I find this tool? It sounded helpful!
Thanks,
Kim

6

The Add-On is for Google Sheets. For Excel, I used the VBA code I posted here.

7

Yes, @kimberleync… as @buzzmaster1 notes, we have the workflow only in the Tiller Community Solutions add-on for Google Sheets.