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. booking 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:

The column names above are examples. Your banks will have their own, which is exactly why the mapping sheet exists.

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 a dedicated MT940 or camt.053 parser. Camt.053 is XML, so Excel can open it directly, but the nested structure usually comes out messy. Power Query handles it better.
   • Or create a Power Query template: read MT940 as text (Lines.FromBinary) and split it by tags, or read camt.053 with Xml.Tables and expand the entries
   • 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:

Oracle, NetSuite and other ERPs use different names for the same fields. Check your own export before you build the mapping.

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 book a whole payment file or a batch of collections as a single entry on the statement (batch booking). The detail is usually not in the description, so you cannot reliably split it from there. Instead:

• Ask the bank to include the transaction details in the camt.053 (the entry details section), or to send a camt.054 notification with the individual items
• If you send the payment file yourself, match the single statement entry to the total of that file, using the file or batch reference
• Only fall back to parsing the description if the bank genuinely cannot provide the detail

Challenge 3: Character Encoding Issues

International banks may use different character encodings:

• Specify the encoding the bank actually uses when importing. camt.053 is usually UTF-8, while MT940 and CSV files often come in other encodings such as Windows 1252
• Create replacement rules for problematic characters
• Document special characters that require handling

Next Steps

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

• Create matching rules based on transaction patterns
• Develop a scoring system for potential matches
• Build the processing engine with Excel formulas
• 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

Next: Part 2, building the automation logic that drives your bank reconciliation process.