Bank Reconciliation Automation Copilot Series – Part 1: Data Structure and Preparation

Introduction

Bank reconciliation is the cornerstone of treasury operations, ensuring that your company’s financial records match your bank’s records. Without direct bank connections through SWIFT or APIs, this process can be manual, time-consuming, and prone to errors. This guide will help you build your own bank reconciliation automation solution without requiring coding knowledge or IT department support.

Understanding the Challenge

A typical bank reconciliation process involves comparing:

1. Bank statements – transactions reported by your bank
2. ERP/accounting system records – transactions recorded in your internal systems
3. Outstanding items – transactions in process but not yet reflected in one of the systems

The complexity increases with:

• Multiple bank accounts across different currencies
• Different transaction types (payments, collections, fees, interest)
• Various formats of bank statements (CSV, Excel, PDF)
• Inconsistent transaction descriptions
• Value date vs. posting date differences

Setting Up the Data Structure

Step 1: Standardizing Bank Statement Formats

First, we need to create a standardized template for bank data regardless of source. You’ll map your bank’s specific formats to this template.

Standard Bank Transaction Template:

Step 2: Creating the Mapping Tool

Since different banks provide statements in various formats, we need a mapping tool to standardize the data. We’ll create this in Excel using Power Query.

1. Create a new Excel workbook with two sheets: “Mapping” and “Standardized Data”
2. Set up your Mapping sheet with columns for your standardized fields and corresponding bank fields
3. For each bank statement format, create a mapping profile

Example Mapping Sheet:

Step 3: Handling Different Bank Statement Formats

For MT940 and CAMT.053 Formats:

MT940 and CAMT.053 are standardized formats used by banks for statement reporting, making them ideal for automation:

1. MT940 (SWIFT Message Type):
   • Contains structured transaction data with specific fields:
     • :20: Transaction Reference Number
     • :25: Account Identification
     • :28C: Statement Number
     • :60F: Opening Balance
     • :61: Statement Line (contains date, amount, transaction type)
     • :86: Information to Account Owner (description)
     • :62F: Closing Balance
2. CAMT.053 (ISO 20022 XML):
   • More detailed XML-based format with hierarchical structure:
     • <GrpHdr>: Group Header information
     • <Stmt>: Statement details including account info
     • <Ntry>: Individual transaction entries
     • <CdtDbtInd>: Credit/Debit indicator
     • <Amt>: Transaction amount
     • <RmtInf>: Remittance information
3. Processing Steps for MT940/CAMT.053:
   • Use specialized MT940/CAMT parsers (many available as Excel add-ins. Camt.053 is an XML, so it can be opened directly in Excel).
   • Or create a Power Query template with Text.FromBinary for MT940 or Xml.Tables for CAMT.053
   • Map the structured fields directly to your standardized template
   • These formats typically require less transformation as they’re already well-structured

For CSV/Excel Bank Statements:

1. Create a Power Query template for each bank statement format
2. Import the statement using Power Query
3. Apply transformations based on your mapping
4. Load the standardized data to your “Standardized Data” sheet

For PDF Bank Statements:

1. Use a PDF data extraction tool like PDF to Excel
2. Export the data to CSV/Excel
3. Apply the same Power Query transformations

Step 4: Standardizing ERP/Accounting System Data

Just like bank statements, we need to standardize your ERP data:

1. Export your ERP transactions to CSV/Excel
2. Create an ERP data mapping profile similar to your bank statement mapping
3. Transform the data using Power Query to match your standard template

ERP Data Mapping Example:

Building the Reconciliation Database

Now that we have standardized both bank and ERP data, we need a “reconciliation database” to match transactions. We’ll build this in Excel:

1. Create a new Excel workbook with the following sheets:
   • Bank Transactions
   • ERP Transactions
   • Reconciliation Rules
   • Matching Results
   • Exceptions
2. Set up data validation and formatting:
   • Use conditional formatting to highlight unmatched items
   • Create dropdown lists for reconciliation statuses
   • Add data validation to prevent errors
3. Create a “Transaction Identifier Template” to help with matching logic:

Data Preparation Best Practices

1. Consistency is key: Ensure your bank statement imports follow the same structure daily
2. Handle special characters: Remove or replace special characters that might cause matching issues
3. Date standardization: Convert all dates to a standard format (YYYY-MM-DD)
4. Amount formatting: Standardize decimal places and thousand separators
5. Create backups: Always save original files before transformation
6. Documentation: Keep a log of all format changes from your banks

Common Data Preparation Challenges

Challenge 1: Inconsistent Bank References

Banks often change reference formats or include varying information. Create a “reference normalization” step that:

• Removes common prefixes/suffixes
• Standardizes spaces and separators
• Extracts key identifiers like invoice numbers

Challenge 2: Multiple Transactions in a Single Entry (Bulk)

Some banks combine multiple transactions into a single entry. Use these techniques:

• Look for patterns in the description field that indicate multiple items
• Create a splitting rule based on delimiters
• Use regular expressions to extract individual transaction details

Challenge 3: Character Encoding Issues

International banks may use different character encodings:

• Always specify UTF-8 encoding when importing data
• Create replacement rules for problematic characters
• Document special characters that require handling

Next Steps

In Part 2 of this series, we’ll build the automation logic that will:

• Create matching rules based on transaction patterns
• Develop a scoring system for potential matches
• Build automation workflows using Excel macros
• Implement a self-learning system to improve matching over time

Data Preparation Checklist

• Identify all bank statement formats you receive
• Create mapping profiles for each format
• Build Power Query templates for transformation
• Create standardized ERP data export procedures
• Set up your reconciliation database structure
• Document special cases and exceptions
• Train your team on the new data preparation process

Stay tuned for Part 2, where we’ll dive into building the automation logic that will drive your bank reconciliation process!