Essential Excel Macros for Streamlining Bank Statement Data Processing

Why Use Excel Macros for Bank Statement Data?

Bank statements often arrive in inconsistent formats, packed with extra columns, merged data, or unwanted rows. Manually cleaning and organizing these statements is tedious, error-prone, and time-consuming. By leveraging Excel macros, you can automate repetitive tasks, minimize mistakes, and drastically cut processing time. Before you start, ensure you have a clean dataset by following our guide on cleaning and organizing bank statement data.

Below you'll find a curated selection of essential macros, each addressing a common pain point in bank statement processing. All macros are written in VBA (Visual Basic for Applications) and can be added to your workbook through the VBA editor (ALT + F11 in Excel).

1. Remove Blank Rows

Blank rows can interfere with sorting, filtering, and analysis. This macro searches for entirely blank rows and deletes them in one sweep.

Sub RemoveBlankRows()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

2. Split Date and Description Columns

Sometimes banks export date and description into a single column. This macro separates them, assuming they're divided by a space or a delimiter, and places them into adjacent columns.

Sub SplitDateDescription()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Columns("A:A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, Space:=True
End Sub

Tip: Adjust the DataType and delimiter according to your statement's actual format.

3. Standardize Date Formats

Dates can come in various regional formats, causing issues in sorting or analysis. Use this macro to convert all dates in Column A to a standard format.

Sub StandardizeDates()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow 'Assuming header in row 1
        If IsDate(ws.Cells(i, 1)) Then
            ws.Cells(i, 1).Value = Format(ws.Cells(i, 1).Value, "yyyy-mm-dd")
        End If
    Next i
End Sub

4. Highlight Duplicate Transactions

Duplicate entries can skew financial analysis. This macro highlights duplicate rows based on transaction amount and date.

Sub HighlightDuplicates()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:C" & LastRow).FormatConditions.AddUniqueValues
    ws.Range("A1:C" & LastRow).FormatConditions(ws.Range("A1:C" & LastRow).FormatConditions.Count).SetFirstPriority
    ws.Range("A1:C" & LastRow).FormatConditions(1).DupeUnique = xlDuplicate
    ws.Range("A1:C" & LastRow).FormatConditions(1).Interior.Color = vbYellow
End Sub

Modify the range to match the columns containing your transaction date and amount.

5. Categorize Transactions Automatically

Assigning categories (e.g., "Groceries", "Utilities") to transactions can be automated based on keywords. This macro checks transaction descriptions and assigns categories in a new column. You can also achieve this with Excel formulas for categorization.

Sub AutoCategorize()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 'Assume descriptions are in column B
    For i = 2 To LastRow
        Select Case True
            Case InStr(1, ws.Cells(i, 2).Value, "Walmart", vbTextCompare) > 0
                ws.Cells(i, 4).Value = "Groceries"
            Case InStr(1, ws.Cells(i, 2).Value, "Shell", vbTextCompare) > 0
                ws.Cells(i, 4).Value = "Fuel"
            Case InStr(1, ws.Cells(i, 2).Value, "Netflix", vbTextCompare) > 0
                ws.Cells(i, 4).Value = "Entertainment"
            Case Else
                ws.Cells(i, 4).Value = "Other"
        End Select
    Next i
End Sub

Expand the Select Case block with additional keywords and categories to suit your needs.

6. Summarize Monthly Totals

Quickly calculate total inflows and outflows by month. This macro assumes your dates are in column A and amounts in column C.

Sub MonthlySummary()
    Dim ws As Worksheet
    Dim dict As Object
    Dim LastRow As Long, i As Long
    Dim dt As String, amt As Double
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        If IsDate(ws.Cells(i, 1).Value) Then
            dt = Format(ws.Cells(i, 1).Value, "yyyy-mm")
            amt = ws.Cells(i, 3).Value
            If dict.Exists(dt) Then
                dict(dt) = dict(dt) + amt
            Else
                dict.Add dt, amt
            End If
        End If
    Next i
    ' Output summary
    ws.Range("E1").Value = "Month"
    ws.Range("F1").Value = "Total"
    i = 2
    For Each dt In dict.Keys
        ws.Cells(i, 5).Value = dt
        ws.Cells(i, 6).Value = dict(dt)
        i = i + 1
    Next dt
End Sub

For a more modern approach to automation, you can also automate imports with Power Query.

How to Add and Use These Macros

1. Open your workbook and press ALT + F11 to launch the VBA editor.
2. Insert a new module with Insert > Module.
3. Copy and paste the desired macro(s) into the module window.
4. Close the editor and return to Excel.
5. Run the macro via ALT + F8, then select the macro by name.

Tips for Safe Macro Use

• Work on a copy of your statement data to prevent accidental loss.
• Test macros on a small dataset before applying them broadly.
• Customize column references and keyword lists for your specific bank statement layout.

Empowering your bank statement workflows with macros not only saves time but also ensures consistent, accurate results. With a bit of customization, these tools can transform how you process financial data in Excel.