Bank Reconciliation Automation Copilot Series – Part 2: Building the Automation Logic

Introduction

Welcome to Part 2 of Bank Reconciliation Automation series. In Part 1, we established the foundation by standardizing your data structures across different banks and ERP systems. Now we’ll build the actual automation engine that will power your bank reconciliation process—without writing a single line of code or involving your IT department.

Your treasury team likely faces complex scenarios daily:

• Payments appearing with different references in your bank and ERP systems
• Batched payments showing as individual entries in your accounting system
• Bank fees and charges with minimal reference information
• Multiple partial payments against single invoices
• Cross-currency transactions with exchange rate variances

In this guide, we’ll address these real-world challenges with practical automation solutions that you can implement yourself using Excel’s native formulas and functions.

1. Building Your Matching Rules Engine

The core of your Treasury Bot is its ability to automatically match bank transactions with your accounting records. This is achieved through a sophisticated yet easy-to-implement matching rules engine.

Step 1: Create the Rules Configuration Worksheet

First, let’s build a Rules Configuration worksheet in your Excel workbook from Part 1:

1. Create a new worksheet named “Matching_Rules”
2. Set up the following columns:
   • Rule_ID (numeric identifier)
   • Rule_Name (descriptive name)
   • Rule_Description (detailed explanation)
   • Match_Field_1 (first field to match)
   • Match_Type_1 (exact, pattern, fuzzy, numeric)
   • Match_Field_2 (optional second field)
   • Match_Type_2 (match type for second field)
   • Match_Field_3 (optional third field)
   • Match_Type_3 (match type for third field)
   • Tolerance (allowable variance for numeric fields)
   • Score (match confidence score 0-100)
   • Auto_Match_Threshold (minimum score for automatic matching)
   • Priority (execution order)
   • Active (Yes/No)

Here’s a sample of rules configuration rows you might set up:

Step 2: Define Pattern Recognition Logic

For pattern matching rules (especially useful for invoice numbers), create a “Pattern_Rules” worksheet:

1. Set up columns:
   • Pattern_ID
   • Pattern_Name
   • Field_to_Search
   • Pattern_Format
   • Example
2. Fill with common patterns:

Step 3: Creating the Fuzzy Matching Configuration

Fuzzy matching helps when references have minor differences like typos or formatting variations:

1. Create a “Fuzzy_Config” worksheet with columns:
   • Field_Name
   • Clean_Before_Match (Yes/No)
   • Remove_Spaces (Yes/No)
   • Case_Sensitive (Yes/No)
   • Ignore_Characters (characters to ignore)
   • Max_Difference (maximum allowed difference percentage)
2. Configure your fuzzy matching parameters:

2. Building the Processing Engine with Excel Formulas

Now let’s create the actual automation engine using Excel’s built-in formulas. No macros required!

Step 1: Set Up Processing Worksheets

Create the following worksheets:

• “Bank_Transactions” (standardized bank data from Part 1)
• “ERP_Transactions” (standardized ERP data from Part 1)
• “Working_Bank” (copy of Bank_Transactions with additional columns)
• “Working_ERP” (copy of ERP_Transactions with additional columns)
• “Matching_Results” (where matches will be stored)
• “Exception_Items” (unmatched items requiring attention)

Step 2: Add Processing Columns to Working Sheets

In both Working_Bank and Working_ERP, add these columns:

• Normalized_Reference (cleaned reference field)
• Normalized_Description (cleaned description field)
• Extracted_Invoice (extracted invoice number)
• Extracted_Customer (extracted customer ID)
• Match_Status (Unprocessed, Matched, Exception)
• Match_Rule_ID (which rule matched it)
• Match_Score (confidence score)
• Match_Transaction_ID (ID of matching transaction)

Step 3: Build the Normalization Formulas

In the Working_Bank sheet, for each transaction row starting from row 2:

1. For Normalized_Reference (assuming Reference is in column E):

Copy=IF(ISBLANK(E2),"",UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," ",""),"-",""),"/","")))

2. For Normalized_Description (assuming Description is in column F):

Copy=IF(ISBLANK(F2),"",UPPER(F2))

3. For Extracted_Invoice (assuming Description is in column F):

Copy=IF(ISNUMBER(SEARCH("INV/",F2)),MID(F2,SEARCH("INV/",F2),15),"")

4. For Extracted_Customer (assuming Description is in column F):

Copy=IF(ISNUMBER(SEARCH("CUST-",F2)),MID(F2,SEARCH("CUST-",F2),8),"")

Add the same formulas to the Working_ERP sheet.

Step 4: Create Excel Tables for Easy Filtering

1. Select all data in Working_Bank including headers
2. Press Ctrl+T to create a table
3. Name the table “BankTable” in the Table Design tab
4. Repeat for Working_ERP, naming it “ERPTable”

Step 5: Building the Matching Process with Formulas

Now we’ll implement each matching rule using Excel formulas. We’ll use a two-step approach:

1. First, create helper columns in both Working_Bank and Working_ERP to identify potential matches
2. Then use COUNTIFS or INDEX/MATCH to find and link matching records

Match Rule 1: Exact Match (Reference, Amount, Date)

In Working_Bank, add these columns:

1. Column L (Rule1_Match):

Copy=IF(AND(Match_Status="Unprocessed",COUNTIFS(Working_ERP[Normalized_Reference],[@Normalized_Reference],Working_ERP[Amount],[@Amount],Working_ERP[Date],[@Date],Working_ERP[Match_Status],"Unprocessed")>0),"MATCH","")

2. Column M (Rule1_ERP_ID), for transactions flagged as “MATCH” in Rule1_Match:

Copy=IF(L2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(1,INDEX((Working_ERP[Normalized_Reference]=[@Normalized_Reference])*(Working_ERP[Amount]=[@Amount])*(Working_ERP[Date]=[@Date])*(Working_ERP[Match_Status]="Unprocessed"),0),0)),"")

Match Rule 2: Reference + Amount Match

3. Column N (Rule2_Match):

Copy=IF(AND(Match_Status="Unprocessed",L2="",COUNTIFS(Working_ERP[Normalized_Reference],[@Normalized_Reference],Working_ERP[Amount],[@Amount],Working_ERP[Match_Status],"Unprocessed")>0),"MATCH","")

4. Column O (Rule2_ERP_ID):

Copy=IF(N2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(1,INDEX((Working_ERP[Normalized_Reference]=[@Normalized_Reference])*(Working_ERP[Amount]=[@Amount])*(Working_ERP[Match_Status]="Unprocessed"),0),0)),"")

Match Rule 3: Pattern Match (Invoice + Amount)

5. Column P (Rule3_Match):

Copy=IF(AND(Match_Status="Unprocessed",L2="",N2="",[@Extracted_Invoice]<>"",COUNTIFS(Working_ERP[Extracted_Invoice],[@Extracted_Invoice],Working_ERP[Amount],[@Amount],Working_ERP[Match_Status],"Unprocessed")>0),"MATCH","")

6. Column Q (Rule3_ERP_ID):

Copy=IF(P2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(1,INDEX((Working_ERP[Extracted_Invoice]=[@Extracted_Invoice])*(Working_ERP[Amount]=[@Amount])*(Working_ERP[Match_Status]="Unprocessed"),0),0)),"")

Match Rule 4: Amount + Date Match

7. Column R (Rule4_Match):

Copy=IF(AND(Match_Status="Unprocessed",L2="",N2="",P2="",COUNTIFS(Working_ERP[Amount],[@Amount],Working_ERP[Date],[@Date],Working_ERP[Match_Status],"Unprocessed")>0),"MATCH","")

8. Column S (Rule4_ERP_ID):

Copy=IF(R2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(1,INDEX((Working_ERP[Amount]=[@Amount])*(Working_ERP[Date]=[@Date])*(Working_ERP[Match_Status]="Unprocessed"),0),0)),"")

Match Rule 5: Fuzzy Reference Match (with helper column)

9. Column T (Rule5_Match):

Copy=IF(AND(Match_Status="Unprocessed",L2="",N2="",P2="",R2=""),IF(MAXIFS(Working_ERP[Fuzzy_Score],Working_ERP[Match_Status],"Unprocessed")>80,"MATCH",""),"")

10. Column U (Rule5_ERP_ID):

Copy=IF(T2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(MAXIFS(Working_ERP[Fuzzy_Score],Working_ERP[Match_Status],"Unprocessed"),IF(Working_ERP[Match_Status]="Unprocessed",Working_ERP[Fuzzy_Score],0),0)),"")

For the fuzzy match, we need a helper column in Working_ERP called “Fuzzy_Score” that calculates the similarity between references:

11. In Working_ERP, add Fuzzy_Score column:

Copy=IFERROR(100*(1-SUMPRODUCT(--MID([@Normalized_Reference],ROW(INDIRECT("1:"&MIN(LEN([@Normalized_Reference]),LEN(BankTable[@Normalized_Reference])))),1)<>MID(BankTable[@Normalized_Reference],ROW(INDIRECT("1:"&MIN(LEN([@Normalized_Reference]),LEN(BankTable[@Normalized_Reference])))),1))/MAX(LEN([@Normalized_Reference]),LEN(BankTable[@Normalized_Reference]))),0)

Step 6: Consolidating the Matches

Now, let’s consolidate all the potential matches into the Match_Status, Match_Rule_ID, Match_Score, and Match_Transaction_ID columns:

1. In Working_Bank, for Match_Status (column H):

Copy=IF(L2="MATCH","Matched",IF(N2="MATCH","Matched",IF(P2="MATCH","Matched",IF(R2="MATCH","Matched",IF(T2="MATCH","Matched","Unprocessed")))))

2. For Match_Rule_ID (column I):

Copy=IF(L2="MATCH",1,IF(N2="MATCH",2,IF(P2="MATCH",3,IF(R2="MATCH",4,IF(T2="MATCH",5,"")))))

3. For Match_Score (column J):

Copy=IF(I2=1,100,IF(I2=2,95,IF(I2=3,90,IF(I2=4,85,IF(I2=5,80,"")))))

4. For Match_Transaction_ID (column K):

Copy=IF(L2="MATCH",M2,IF(N2="MATCH",O2,IF(P2="MATCH",Q2,IF(R2="MATCH",S2,IF(T2="MATCH",U2,"")))))

Step 7: Updating ERP Transactions

Now we need to update the ERP transactions that have been matched. In Working_ERP, add these formulas:

1. For Match_Status (column H):

Copy=IF(COUNTIFS(Working_Bank[Match_Transaction_ID],[@Transaction_ID])>0,"Matched","Unprocessed")

2. For Match_Rule_ID (column I):

Copy=IF(H2="Matched",INDEX(Working_Bank[Match_Rule_ID],MATCH([@Transaction_ID],Working_Bank[Match_Transaction_ID],0)),"")

3. For Match_Score (column J):

Copy=IF(H2="Matched",INDEX(Working_Bank[Match_Score],MATCH([@Transaction_ID],Working_Bank[Match_Transaction_ID],0)),"")

4. For Match_Transaction_ID (column K):

Copy=IF(H2="Matched",INDEX(Working_Bank[Transaction_ID],MATCH([@Transaction_ID],Working_Bank[Match_Transaction_ID],0)),"")

3. Implementing Advanced Matching Techniques

Now let’s enhance your Treasury Bot with advanced matching techniques for complex scenarios.

Handling Bank Fees and Charges

Bank fees often appear with minimal reference information. Create a special rule for them:

1. Add a “Keywords” worksheet with common fee descriptions:

2. Add a helper column to Working_Bank called “Is_Bank_Fee”:

Copy=IF(OR(ISNUMBER(SEARCH("BANK FEE",[@Normalized_Description])),ISNUMBER(SEARCH("SERVICE CHARGE",[@Normalized_Description])),ISNUMBER(SEARCH("MAINTENANCE FEE",[@Normalized_Description])),ISNUMBER(SEARCH("TRANSACTION FEE",[@Normalized_Description])),ISNUMBER(SEARCH("WIRE FEE",[@Normalized_Description])),ISNUMBER(SEARCH("ACH FEE",[@Normalized_Description])),ISNUMBER(SEARCH("MONTHLY FEE",[@Normalized_Description]))),"Yes","No")

3. Add a new rule column for bank fees (Rule6_Match):

Copy=IF(AND(Match_Status="Unprocessed",L2="",N2="",P2="",R2="",T2="",[@Is_Bank_Fee]="Yes",COUNTIFS(Working_ERP[Amount],[@Amount],Working_ERP[Match_Status],"Unprocessed")>0),"MATCH","")

4. Add Rule6_ERP_ID column:

Copy=IF(V2="MATCH",INDEX(Working_ERP[Transaction_ID],MATCH(1,INDEX((Working_ERP[Amount]=[@Amount])*(Working_ERP[Match_Status]="Unprocessed"),0),0)),"")

5. Update the consolidated match formulas to include the new rule.

Handling Batched Payments

For batched payments, we need a more complex approach:

1. Create a “Payment_Batches” worksheet with columns:
   • Batch_ID
   • Bank_Transaction_ID
   • Bank_Amount
   • ERP_Transaction_IDs (comma-separated list)
   • ERP_Total_Amount
   • Difference
   • Match_Score
2. Add a formula in Working_Bank to identify potential batch candidates (large payments):

Copy=IF(AND([@Match_Status]="Unprocessed",[@Amount]>1000),"Potential_Batch","")

3. For each potential batch, use a helper formula to find combinations of ERP transactions that sum to the bank amount (within tolerance):

Copy=LET(
    bankAmt, [@Amount],
    tolerance, 0.01,
    erpAmounts, FILTER(Working_ERP[Amount], (Working_ERP[Match_Status]="Unprocessed")),
    erpIDs, FILTER(Working_ERP[Transaction_ID], (Working_ERP[Match_Status]="Unprocessed")),
    combinationCheck, LAMBDA(ids, amounts, target, 
        LET(
            sum, SUM(amounts),
            delta, ABS(sum - target),
            result, IF(delta <= tolerance, TEXT(ids, ""), ""),
            result
        )
    ),
    REDUCE("", SEQUENCE(ROWS(erpAmounts)), LAMBDA(acc, i, 
        IF(acc<>"", acc, 
            combinationCheck(
                INDEX(erpIDs, i),
                INDEX(erpAmounts, i),
                bankAmt
            )
        )
    ))
)

Note: This is a simplified approach. For a full solution, you might need to use Power Query to find all possible combinations.

Handling Cross-Currency Transactions

For international treasury operations with multiple currencies:

1. Create a “Currency_Rates” worksheet with daily exchange rates:

2. Add a helper function to convert amounts using a LAMBDA function:

Copy=LAMBDA(amount, fromCurr, toCurr, transDate,
    IF(fromCurr=toCurr, amount,
        LET(
            rate, XLOOKUP(transDate & fromCurr & toCurr, 
                Currency_Rates[Date] & Currency_Rates[From_Currency] & Currency_Rates[To_Currency], 
                Currency_Rates[Exchange_Rate], 0),
            convertedAmount, amount * rate,
            convertedAmount
        )
    )
)

3. Use this LAMBDA function in a named formula (e.g., “ConvertCurrency”) and reference it in your matching rules.

4. Building the User Interface

Let’s create a user-friendly dashboard to run your Treasury Bot and view results:

1. Create a new worksheet named “Dashboard”
2. Add a title and description
3. Create a control panel with data validation dropdowns:
   • Bank Account (dropdown)
   • Date Range (dropdown)
   • Matching Threshold (dropdown)
4. Add a “Refresh Data” button using Form Controls (not macros):
   • This will link to a cell that triggers INDIRECT references to update your tables
5. Add summary statistics using formulas:
   • Total Bank Transactions: =COUNTROWS(BankTable)
   • Total ERP Transactions: =COUNTROWS(ERPTable)
   • Auto-Matched (%): =COUNTIFS(Working_Bank[Match_Status],"Matched")/COUNTROWS(BankTable)
   • Exceptions (%): =COUNTIFS(Working_Bank[Match_Status],"Unprocessed")/COUNTROWS(BankTable)
6. Create three simple charts:
   • Daily Reconciliation Progress (time series)
   • Match Type Distribution (pie chart)
   • Exception Categories (bar chart)
7. Add a mini exception browser using FILTER function (Excel 365):

Copy=FILTER(Working_Bank, Working_Bank[Match_Status]="Unprocessed")

5. Creating Self-Learning Capabilities

Your Treasury Bot can get smarter over time by learning from your manual matches:

1. Create a “Learning_Repository” worksheet with columns:
   • Original_Bank_Reference
   • Original_ERP_Reference
   • Match_Pattern
   • User_Confirmed (Yes/No)
   • Frequency (count of occurrences)
   • Last_Updated
2. When users manually match transactions, capture the pattern using this formula:

Copy=CONCAT(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                [Bank_Reference],
                "0", "#"
            ),
            "1", "#"
        ),
        "2", "#"
    ),
    " :: ",
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                [ERP_Reference],
                "0", "#"
            ),
            "1", "#"
        ),
        "2", "#"
    )
)

3. Add a counter for pattern frequency:

Copy=COUNTIFS(Learning_Repository[Match_Pattern], [@Match_Pattern])

4. Apply learned patterns with a new rule that checks if a pattern exists in the repository with sufficient frequency.

6. Building the Reconciliation Report

Finally, create comprehensive reports to document the reconciliation process:

1. Create a “Reconciliation_Summary” worksheet with:
   • Date of reconciliation
   • Bank account details
   • Time period covered
   • Summary statistics
   • Aging of unmatched items
   • Approval workflow status
2. Use these formulas to generate a reconciliation summary:
   • Total Bank Transactions: =COUNTROWS(BankTable)
   • Total ERP Transactions: =COUNTROWS(ERPTable)
   • Auto-Matched Transactions: =COUNTIFS(Working_Bank[Match_Status],"Matched")
   • Auto-Matched Percentage: =COUNTIFS(Working_Bank[Match_Status],"Matched")/COUNTROWS(BankTable)
   • Exception Items: =COUNTIFS(Working_Bank[Match_Status],"Unprocessed")
   • Exception Percentage: =COUNTIFS(Working_Bank[Match_Status],"Unprocessed")/COUNTROWS(BankTable)
3. For top exception items, use:

Copy=SORT(FILTER(Working_Bank, Working_Bank[Match_Status]="Unprocessed"), Working_Bank[Amount], -1)

Conclusion

Congratulations! You’ve now built a sophisticated bank reconciliation automation system without writing a single line of code or involving IT. Your Treasury Bot can:

• Process transactions from multiple banks and currencies
• Handle complex matching scenarios including batched payments
• Learn from your manual matches to get smarter over time
• Generate comprehensive reconciliation reports

It is easier to use VBA code, if you know, but the formula-based approach offers several advantages:

1. No macro security concerns
2. Works on all platforms including Excel Online
3. Easier to understand and maintain by end user
4. Better performance with Excel’s calculation engine

In Part 3 of this series, we’ll explore error handling, edge cases, and advanced techniques for handling complex reconciliation scenarios, including:

• Handling returned and reversed payments
• Managing international tax withholdings
• Dealing with statement breaks and overlapping periods
• Implementing approval workflows
• Creating audit trails for compliance

Stay tuned for the final part of this series that will take your treasury automation to the next level!