Filtering Names in Bank Statements Using VBA

Introduction: The Complexity of Bank Statement Data

Bank statements are essential for tracking financial transactions, but their unstructured nature poses a challenge when extracting meaningful insights. Each transaction entry typically includes:

  • Names of payees/payers
  • Transaction dates
  • Amounts
  • Unique identifiers like references (e.g., "NEFT/12345/DEXSINS")

While identifying and isolating names is crucial for financial analysis, the presence of chunks of unnecessary data and bank codes clutters the data. Manual filtering is time-consuming, and generic tools may lack the necessary customization.


Challenges in Extracting Names from Bank Statements

  • Contextual Noise: Names are often embedded within transaction descriptions, e.g., "RTGS/12345/John Doe/456".
  • Bank-Specific Patterns: Different banks use varying notations, requiring adaptable filtering methods.
  • Ambiguity in Short Names: Short names like "PAL" might be confused with unrelated text.

The Solution: VBA for Automated Name Filtering


Excel’s Visual Basic for Applications (VBA) offers a powerful, flexible method to automate name extraction from bank statements. By using a custom VBA function, patternreplacement, we can eliminate unwanted strings and streamline data analysis.

In a thousand row data, Instead of RTGS/SINGAPORE/93839556/JOHN DOE/PURCHASE AMOUNT, If you wish to name it JOHN DOE you can use this custom VBA code and excel formula

Function patternreplacement(str As String) As String Dim pattern As String pattern = "RTGS/(.*?)\/(.*?)\/" With CreateObject("VBScript.RegExp") .Global = True .pattern = pattern RemoveUPINumbers = .Replace(str, "") End With End Function

How It Works:

  • Uses regular expressions (regex) to identify patterns.
  • RTGS/ in the said code first removes the word RTGS/
  • (.*?)\/ then removes everything till the first slash including the first slash, that is SINGAPORE/ in the given example
  • (.*?)\/ in the second instance then removes everything till the second slash including the second slash, that is 93839556/ in the given example

You would end up having JOHN DOE/PURCHASE AMOUNT in the said row. Repeat it with "/(.*?)" to get JOHN DOE

Implementing the VBA Function in Excel

  • Open Microsoft Excel.
  • Press Alt + F11 to access the VBA Editor.
  • Click Insert > Module to create a new module.
  • Copy and paste the said code into the module. Save the file as a macro-enabled workbook (.xlsm).
  • Ensure transaction descriptions are in a single column (e.g., Column A).
  • In Column B, use the formula: and drag it
=patternreplacement(A1)

Advantages of Using VBA for Bank Statement Processing

Feature Benefit
Speed Processes thousands of rows instantly.
Accuracy Eliminates patterns without manual effort.
Flexibility Can be modified to filter other patterns (e.g., account numbers).

Conclusion

Extracting names from bank statements is challenging due to formatting inconsistencies and contextual noise. The givem VBA function streamlines this process by eliminating identifiers, allowing for cleaner financial data analysis.

Next Steps:

  • Implement the VBA function in Excel.
  • Modify regex patterns to filter additional unwanted text.
  • Explore automation tools like Power Query for enhanced processing.

This method provides speed, accuracy, and flexibility, proving that VBA remains a game-changer in financial data processing.