Why You Need a Bank Fee Analysis Bot

Bank fees can quietly drain your company’s cash if not properly monitored. While banks provide fee reports, they are often unclear, making it difficult to validate charges. Instead of relying on manual tracking, you can automate the process using a Bank Fee Analysis Bot. This bot will help you:

• Extract and consolidate bank fee data from MT940 or CAMT.053 statements.
• Categorize and compare fees against your agreements.
• Generate reports and alerts when overcharges occur.
• Help you reclaim lost funds and negotiate better terms.

The best part? You don’t need an IT department or coding knowledge to build this bot. We will use Power Automate, Excel, and Power BI, or any treasury system that allows API connections.

Part 1: Setting Up Your Data Collection System

Step 1: Choose a No-Code Automation Platform

Start by selecting a user-friendly automation platform. Recommended options include:

• Microsoft Power Automate – Ideal for Microsoft 365 users.
• Zapier – User-friendly with extensive integration options.
• Integromat (now Make) – Powerful visual workflow builder.

For this guide, we’ll use Power Automate as it integrates well with Excel and SharePoint.

Step 2: Connect to Your Bank Data Sources

For MT940/MT942 Statements:

• Log into your Microsoft Power Automate account.
• Click “Create new flow” → “Automated flow”.
• Name your flow (e.g., “Bank Statement Data Collector”).
• Choose a trigger: “When a file is added to a folder” (OneDrive or SharePoint).
• Create a dedicated folder where you’ll upload your MT940 files.

For CAMT.053 XML Files:

• In Power Automate, add an action: “Parse XML”.
• In the Content field, select the dynamic content from your trigger.
• Use the CAMT.053 XML schema (provided by your bank) to extract relevant data.

Part 2: Extract and Categorize Fees

Method 1: Using Power Query in Excel

1. Load the MT940 or CAMT.053 file into Excel:
   • Open Excel, go to Data > Get Data > From XML (for CAMT.053) or From Text (for MT940).
   • Select your downloaded file.
2. Transform Data:
   • Identify fee-related transactions by filtering for specific transaction codes (e.g., NCHG for charges in MT940 or BANKFEE in CAMT.053).
   • Remove unnecessary columns and rename fields for clarity.
3. Categorize Fees:
   • Match transaction descriptions against a predefined fee mapping table (e.g., transaction fees, wire fees, FX fees).
   • Use Excel formulas (IF, VLOOKUP) or Power Query logic to categorize fees automatically.

Method 2: Using Power Automate for Automation

1. Set Up a Flow to Extract Fee Data
   • Create a Power Automate flow triggered when a new file is added to OneDrive/SharePoint.
   • Parse the XML or text file using the Parse JSON or Extract Table from CSV connector.
   • Filter transactions with specific keywords (e.g., CHARGE, FEE).
   • Export cleaned data to an Excel file.
2. Schedule Monthly Fee Categorization
   • Use Power Automate to append extracted data to a central Excel file or database.
   • If using SharePoint, Power Automate can categorize fees in a SharePoint list.

Part 3: Processing and Categorizing Bank Fees

Step 1: Create Your Excel Template

Create These Sheets in Excel:

• Raw Data: Where bank statement data will be stored.
• Fee Categories: Your categorization rules.
• Analysis: Where calculations happen.
• Dashboard: For visualizing results.

Fee Categories Sheet Structure:

Step 2: Build Your Categorization Logic in Power Query

1. Go to Data → Get Data → From Other Sources → Blank Query.
2. Paste This Categorization Logic:let Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Fee Category", each if Text.Contains([Description], "WIRE") and Text.Contains([Description], "INTL") then "Wire Transfers (International)" else if Text.Contains([Description], "WIRE") then "Wire Transfers (Domestic)" else if Text.Contains([Description], "MAINTENANCE") then "Account Maintenance" else "Uncategorized" ) in #"Added Custom"
3. Click Done & Save.

Part 4: Building Your Automated Reporting System

Step 1: Create Dashboard Visuals

• Pie Chart: Breakdown of fees by category.
• Trend Line Chart: Monthly fee trends.
• Overcharge Alert Table: List of fees exceeding expected costs.

Step 2: Automate Report Distribution

• Use Power Automate to Send Email Alerts:
  • Trigger: Weekly schedule.
  • Extract flagged overcharges.
  • Format into an HTML table and send via email to treasury teams.

Part 5: Taking Action on Overcharges

1. Document Fee Discrepancies:
   • Record bank name, account number, discrepancy details, and agreed-upon fee structure.
2. Negotiate with Banks:
   • Present your analysis during quarterly bank reviews.
   • Request fee credits for overcharges.
   • Renegotiate contract terms based on transaction data.

Remember to regularly update your categorization rules as new fee types emerge, and schedule quarterly reviews of your bank fee structures to ensure ongoing compliance with your agreements.

📥 Download a Sample Excel Template: To help you get started, I’ve prepared a sample Bank Fee Analysis Report in Excel. You can download it and use it as a template to automate your own fee validation process!

Appendix: Resource Links

• Microsoft Power Automate Templates
• Excel Power Query Tutorial
• Sample MT940 Parser for Excel
• CAMT.053 XML Schema Reference
• Bank Fee Benchmarking Resources

Power Query for Data Import (MT940 Files)

This would be set up in the Power Query Editor (Data → Get & Transform Data → From Text/CSV):

let
Source = Csv.Document(File.Contents(“C:\Path\To\MT940File.txt”),null,”,”,null,1252),
#”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{
{“Transaction Date”, type date},
{“Account Number”, type text},
{“Description”, type text},
{“Debit Amount”, type number},
{“Credit Amount”, type number},
{“Balance”, type number},
{“Reference”, type text}
}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each
([Debit Amount] > 0) and
(Text.Contains([Description], “FEE”) or Text.Contains([Description], “CHARGE”))
)
in
#”Filtered Rows”