This guide will help you set up an automated reconciliation flow that:

• Fetches bank statements from an email attachment or SharePoint folder.
• Extracts accounting transactions from an Excel file or ERP.
• Compares transactions and highlights discrepancies.
• Sends alerts to the finance team if unmatched transactions exist.

Step 1: Create a New Flow in Power Automate

1. Go to Power Automate and click “Create” → “Automated cloud flow”.
2. Name your flow “Automated Bank Reconciliation”.
3. Select “When a file is created in a SharePoint folder” (or “When a new email arrives”, if receiving statements via email).

Step 2: Fetch Bank Statement Data

• If using SharePoint:
  • Trigger: “When a file is created”
  • Folder path: Select the folder where bank statements are saved.
  • File type: Ensure it’s .csv or .xlsx.
• If using email:
  • Trigger: “When a new email arrives”
  • Filter emails: Only process emails with an attachment from the bank.
  • Save attachment: Use “Create file” in SharePoint or OneDrive.

Step 3: Extract Accounting Transactions

• Use “List rows present in a table” (Excel Online connector) if your accounting data is stored in an Excel file.
• If using an ERP (SAP, Dynamics, Oracle), use “Execute SQL query” or “Get records” from Dataverse.

Step 4: Compare Bank Statement and Accounting Data

• Use “Filter array” to find transactions in the bank statement that do not exist in the accounting records.
• Use “Condition” to compare amounts and dates.

Step 5: Handle Unmatched Transactions

• If discrepancies are found:
  • Use “Send an email” to notify the finance team.
  • Save unmatched transactions in a SharePoint list or an Excel report.

Step 6: Store the Reconciliation Report

• Create an Excel file in SharePoint with matched/unmatched transactions.
• If using Power BI, push the data for real-time reconciliation tracking.

🔹 Bonus: You can add a Power Automate Approval Step to review unmatched transactions before updating the ERP.

Power Automate Desktop flow

Below you can find a Power Automate Desktop script that you can download and use to reconcile bank transactions automatically. This script will:

• Open and read bank statement and accounting files.
• Match transactions based on amount and date.
• Save unmatched transactions in a report.

How to Use This Script in Power Automate Desktop:

1. Open Power Automate Desktop on your computer.
2. Click “New Flow” and name it “Bank Reconciliation”.
3. Click “Add Actions” and select “Run Script”.
4. Copy and paste the content from the downloaded file into the script editor.
5. Adjust file paths in the script (C:\\Users\\User\\Documents\\Bank_Statement.xlsx) to match your local files.
6. Click “Run” to execute the reconciliation process.

This script will: ✅ Read Excel files (Bank Statement & Accounting Ledger).
✅ Match transactions based on amount and date.
✅ Identify unmatched transactions and save them in a report.
✅ Display a notification once reconciliation is complete.

🔽 Downloadable Template: