The Ultimate Guide: Summarizing Merchant Fees in Excel with VBA

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical.

Main Problem Statement

You have a lengthy Excel file that lists merchant fee transactions, and you need to calculate the total fees for each date, location (name), and possibly by merchant name. This data needs to be summarized back into QuickBooks.

The Challenge with Sorting and Summarizing Data

Many users attempt to use Excel’s PivotTables or formulas like SUMIFS to summarize transactional data but often face issues when they need the output in a specific format for re-importation. This is particularly true if you have variable numbers of rows, dates, locations, and merchants.

The Step-by-Step Solution

Using VBA allows us to automate this summarization process based on your transactional data’s needs. Here’s how we can achieve it:

Sample Data Layout

Column C: Date
Column E: Name (Location)
Column I: Merchant Fee Amounts

Step-by-Step Implementation with VBA Code:

The following example assumes your transactions are on a sheet named “SquareFees”. You will place this code into an Excel module and run it when needed.

Sub SummarizeMerchantFees()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SquareFees")

    ' Variables to hold our data values
    Dim lastRow As Long, dateValue As String, nameValue As String, feeAmount As Double

    Application.ScreenUpdating = False  ' Improve performance by not updating screen during execution.

    ' Determine the final row of your dataset.
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).row

    Dim summaryDict As Object
    Set summaryDict = CreateObject("Scripting.Dictionary")

    Dim dateNameKey As String
    feeAmount = 0

    ' Loop through each row in the data.
    For i = 2 To lastRow   ' Assuming headers are on Row 1, so start at Row 2.
        dateValue = ws.Cells(i, "C").value & "_" & ws.Cells(i, "E").value
        If Len(Trim(CStr(dateNameKey))) > 0 Then
            feeAmount = feeAmount + CDbl(ws.Cells(i, "I").value)
        ElseIf Not summaryDict.exists(dateValue) Then ' Initialize dictionary for new date-name combination.
            summaryDict.Add dateValue, cdbl(ws.Cells(i, "I"))
        Else ' Add to existing total in the Dictionary
            summaryDict(dateValue) = summaryDict(dateValue) + cdbl(ws.Cells(i, "I").value)
        End If

    Next i  ' End of row loop.

    Dim dictKey As Variant

    Application.ScreenUpdating = True   ' Resume screen updates.

The above VBA script will read through your transactional data and build a dictionary object to calculate the total fees for each date-name combination.

Once you have calculated totals, it needs to be placed back into Excel. You can add another section of code that writes these values back starting at row 205 on column K:


rowIndex = ws.Cells(ws.Rows.Count, "K").End(xlUp).Offset(1, 0) ' Start one row below existing data.
    For Each dictKey In summaryDict.Keys
        dateNameKeyValues = Split(dictKey, "_")
        ws.Cells(rowIndex, 12).Value = CDate(dateNameKeyValues(0)) ' Column K for Date (column L)
        If Len(dateNameKeyValues) > 1 Then
            ws.Cells(rowIndex, 6).value = dateNameKeyValues(1)   ' Column E for Name
        EndIf

        ws.Cells(rowIndex, "K").Value = summaryDict(dictKey)

        rowIndex = rowIndex + 1
    Next dictKey

Extra Tips and Advanced Variations:

This basic script can be enhanced with additional error handling, dynamic sheet references (asking user which sheet to use), or even integrating it into existing VBA workflows for a more streamlined process.

Handling Blank Names: Modify the code to handle cases where names are blank by using special markers during processing and filtering these out in final output, if needed.

Exporting Summarized Data Back To QuickBooks: If you’re integrating back with an accounting system like Quickbooks that supports CSV or other formats – consider adding steps to export the summarized data sheet into those formats directly from VBA.

Alternative Solutions and Tools:

PivotTables vs. Custom Scripts: While Pivot Tables can handle many summary tasks, they aren’t always suitable for export to formats like QuickBooks requires.

CelTools: 70+ extra Excel features

Conclusion and Next Steps:

The VBA approach provides a robust, flexible way to summarize merchant fees for variable datasets in Excel. This script can be expanded or customized further as your needs evolve.

We hope this guide has helped you understand how to automate the summarization of transaction data effectively!