Cash Reconciliation Checker with Google Sheets, OpenAI & n8n
Cash Reconciliation Checker with Google Sheets, OpenAI & n8n This workflow automatically compares internal cash balances with custodian or bank balances using Google Sheets, detects mismatches by account_id, calculates balance differences, logs matched records and sends mismatched records through OpenAI for a short explanation before saving them for exception review. It is designed to help teams reduce manual reconciliation work and quickly identify balance issues. Quick Implementation Steps Import the workflow into n8n. Connect your Google Sheets OAuth2 credentials. Point the three Google Sheets nodes to: Internal balances sheet Custodian balances sheet Reconciliation / exception log sheets Ensure both source sheets use the same account_id values. Make sure balance fields are numeric: internal_balance custodian_balance Connect your OpenAI credentials. Adjust the Schedule Trigger frequency if needed. Run the workflow once and verify: matched records are logged mismatched records are analyzed and appended correctly What It Does The Cash Reconciliation Checker automates a common finance operations task: comparing balances between two separate data sources. In this workflow, one Google Sheet holds internal balances, while another holds custodian balances. The workflow fetches both datasets, standardizes the required fields and matches records using the shared account_id. After matching the accounts, the workflow calculates the difference between internal and custodian balances and checks whether the difference exceeds a built-in tolerance. If the balances match, the record is written to a reconciliation log as a successful result. If they do not match, the workflow routes the record into an exception path. For mismatches, the workflow uses OpenAI (gpt-4o-mini) to generate a short possible explanation based on the values in the record. That enriched mismatch record is then prepared and appended to a separate logging sheet for investigation and follow-up. Who’s It For This workflow is useful for teams and professionals who regularly compare balances across systems, such as: Finance operations teams Fund administration teams Treasury teams Accounting teams Reconciliation analysts Back-office operations teams Internal controls and audit support teams It is especially useful for organizations that currently reconcile balances manually in spreadsheets and want a faster, more consistent process. Requirements to Use This Workflow Before using this workflow, make sure you have the following: Required Platforms & Accounts n8n account** Google Sheets** OpenAI API access** Required n8n Credentials You will need to configure: Google Sheets OAuth2 credentials** OpenAI credentials** Required Google Sheets Structure This workflow expects the following source data structure based on the JSON: 1) Internal Balance Sheet Must contain at least: account_id currency internal_balance 2) Custodian Balance Sheet Must contain at least: account_id currency custodian_balance 3) Reconciliation Log Sheet Should support these columns: account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at recon_status 4) Exception / Alert Sheet Should support these columns: account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at ai_explanation recon_status Data Expectations To avoid processing issues: account_id should be consistent across both source sheets Balance fields should contain numeric values only currency should be present where relevant Empty or invalid balance values may be flagged as mismatches How It Works & Set Up Step 1 — Import the Workflow into n8n Import the provided JSON file into your n8n workspace. After import, you will see the workflow named: Cash Reconciliation Checker Step 2 — Review the Flow The workflow follows this sequence: Schedule Trigger → Fetch Internal Balances → Fetch Custodian Balances → Edit Internal Fields / Edit Custodian Fields → Match Accounts by Account ID → Calculate Balance Difference → Check for Balance Mismatch ├── Matched → Log Matched Records └── Mismatched → Generate AI Mismatch Explanation → Prepare Exception Record → Append The Data In The Sheet Step 3 — Configure the Schedule Trigger Node: Run Reconciliation on Schedule This node starts the workflow automatically using a schedule interval. What to do: Open the node Set your preferred execution frequency Example options: Every 15 minutes Hourly Daily End-of-day reconciliation schedule Use a timing pattern that fits your reconciliation process. Step 4 — Connect the Internal Balance Source Node: Fetch Internal Balances This Google Sheets node pulls records from the internal balance sheet. What to do: Connect your Google Sheets OAuth2 account Select the correct spreadsheet Select the correct sheet tab Required fields expected from this source: account_id currency internal_balance Step 5 — Connect the Custodian Balance Source Node: Fetch Custodian Balances This Google Sheets node pulls records from the custodian or bank balance sheet. What to do: Connect your Google Sheets OAuth2 account Select the correct spreadsheet Select the correct sheet tab Required fields expected from this source: account_id currency custodian_balance Step 6 — Standardize Both Datasets The workflow uses two Set nodes to clean and normalize fields before matching. Node: Edit Internal Fields This node maps and formats: account_id currency internal_balance It also converts internal_balance into a numeric value. Node: Edit Custodian Fields This node maps and formats: account_id currency custodian_balance It also converts custodian_balance into a numeric value. Why this matters This step helps ensure both datasets use a consistent field structure before comparison. Step 7 — Match Records by Account ID Node: Match Accounts by Account ID This Merge node combines both sources using: account_id What it does It aligns internal and custodian records so each account can be compared side by side. Important setup note This will only work properly if: both sheets contain matching account_id values the values are formatted consistently there are no accidental extra spaces or mismatched IDs Step 8 — Calculate the Balance Difference Node: Calculate Balance Difference This Code node performs the main reconciliation logic. What it calculates For each matched account, it creates: internal_balance custodian_balance difference abs_difference mismatch checked_at Logic used in this node The workflow uses a built-in tolerance: const TOLERANCE = 0.01; Reconciliation rule A record is treated as a mismatch if: either balance is invalid / not numeric, or the absolute difference is greater than 0.01 Output fields created account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at This is the core decision-making step in the workflow. Step 9 — Route Matched vs Mismatched Records Node: Check for Balance Mismatch This IF node checks: mismatch == true Routing behavior If mismatch = false The record is considered matched and goes to: Log Matched Records If mismatch = true The record is treated as an exception and goes to: Generate AI Mismatch Explanation This split keeps normal reconciliations separate from exception handling. Step 10 — Log Matched Records Node: Log Matched Records This Google Sheets node appends matched records to a reconciliation log sheet. Logged values include: account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at recon_status Fixed value used Matched records are saved with: recon_status = Matched This gives you a clean audit trail of successfully reconciled accounts. Step 11 — Generate AI Explanation for Exceptions Node: Generate AI Mismatch Explanation This node sends mismatch data to OpenAI (gpt-4o-mini) and asks for a short explanation. Prompt behavior in the workflow The AI is instructed to review: account ID currency internal balance custodian balance difference check timestamp It is then asked to provide the most likely cause of the mismatch from the following categories already defined in the workflow: settlement delay (T+1/T+2) pending fees or accrued interest FX conversion timing failed corporate actions bank charges not yet booked data entry error It also ends with: top 1–2 likely causes one recommended next action Why this is useful This adds context to exceptions and helps operations teams review mismatches faster. Step 12 — Prepare the Exception Record Node: Prepare Exception Record This Code node combines the AI output with the original mismatch data and formats it for logging. Fields included in the final exception record account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at ai_explanation recon_status Fixed value used Mismatch records are saved with: recon_status = Mismatch This creates a structured exception record ready for reporting or review. Step 13 — Append Mismatch Records to the Exception Sheet Node: Append The Data In The Sheet This Google Sheets node appends the prepared mismatch records into a separate sheet for follow-up. Logged values include: account_id currency internal_balance custodian_balance difference abs_difference mismatch checked_at ai_explanation recon_status This acts as your exception register for unresolved or suspicious balance breaks. Step 14 — Test Before Going Live Before enabling the workflow, run a few controlled tests. Recommended test scenarios Test 1 — Perfect match Use the same values in both sheets for one account. Expected result: record goes to Log Matched Records Test 2 — Small tolerance-safe difference Use a difference within 0.01. Expected result: record should still be treated as matched Test 3 — True mismatch Use a larger difference. Expected result: record goes through AI explanation path gets appended to exception sheet Test 4 — Invalid numeric value Use a blank or non-numeric balance. Expected result: record should be flagged as mismatch Once tests pass, you can safely activate the workflow. How To Customize Nodes This workflow is already useful as-is, but it can be adapted for different reconciliation processes. 1) Customize the Schedule Trigger Node: Run Reconciliation on Schedule You can change: frequency execution window time of day reconciliation cycle Useful if you want: intraday reconciliation end-of-day checks batch finance controls 2) Change Matching Logic Node: Match Accounts by Account ID Currently matches on: account_id You can modify your data model and workflow if you want to include additional matching dimensions such as: account + currency account + region account + entity Only do this if your sheet structure supports it. 3) Adjust the Tolerance Threshold Node: Calculate Balance Difference Current tolerance: const TOLERANCE = 0.01; You can change this if your business allows different variance thresholds. Example customizations 0 → exact reconciliation only 0.01 → cent-level tolerance 1 → whole-unit tolerance custom threshold based on asset class or currency 4) Expand the AI Explanation Logic Node: Generate AI Mismatch Explanation You can customize the prompt to include: business rules escalation notes internal SOP references suggested ownership routing severity classification This is helpful if you want the AI output to be more operationally specific. 5) Add More Fields to Logging Nodes: Log Matched Records Append The Data In The Sheet You can extend the output to include additional columns such as: legal entity desk custodian name region portfolio ID reviewer status resolution notes Only add fields that exist in your upstream data or are intentionally created in the workflow. 6) Improve Exception Classification Node: Prepare Exception Record You can enhance this node to add labels like: low severity medium severity high severity requires same-day review possible FX issue possible operational break This can help organize exception handling more efficiently. Add-ons This workflow can be extended with additional automation features depending on your operational needs. 1) Slack Alerts for Mismatches Send a real-time alert whenever a mismatch is detected. Useful for: finance ops teams treasury teams urgent exception monitoring 2) Email Notification Summary Send a daily or hourly summary of all mismatches to stakeholders. Useful for: finance managers controllers operations leads 3) Severity Scoring Add logic to classify mismatches by size or business impact. Useful for: prioritization faster review queues escalation workflows 4) Auto-Assignment to Reviewers Automatically assign mismatch cases to specific team members based on: currency entity account range custodian Useful for structured exception management. 5) Dashboard Reporting Push matched and mismatched records into a reporting dashboard. Useful for: reconciliation KPIs trend monitoring operational oversight 6) Multi-Currency or Multi-Entity Expansion Extend the workflow to support more entities, accounts or balance sources. Useful for: growing operations teams fund administrators larger finance environments Use Case Examples Below are some of the main ways this workflow can be used. There can absolutely be many more use cases depending on how your reconciliation process is structured. 1) Daily Internal vs Custodian Cash Reconciliation Automatically compare daily internal records against custodian balances and flag any balance breaks for investigation. 2) End-of-Day Treasury Balance Checks Run the workflow at the end of each business day to ensure treasury balances match external sources before close. 3) Exception Monitoring for Fund Operations Identify mismatched fund cash balances and create a structured exception sheet with AI-generated review notes. 4) Reconciliation Logging for Audit Trail Maintain a consistent log of matched and mismatched records for reporting, controls and audit readiness. 5) Early Warning for Data Quality Issues Use mismatches to spot operational problems such as missing values, incorrect balances or inconsistent source data. 6) Lightweight Finance Automation for Spreadsheet-Based Teams Support teams that still work mainly in spreadsheets but want to reduce repetitive reconciliation effort using automation. Troubleshooting Guide | Issue | Possible Cause | Solution | |---|---|---| | No records are being compared | One or both Google Sheets nodes are not returning data | Check that both source sheets contain rows and the correct sheet tabs are selected | | Records are not matching correctly | account_id values differ between the two source sheets | Make sure account_id values are identical and formatted consistently in both sheets | | All rows are being flagged as mismatches | Balance fields contain text, blanks or invalid values | Ensure internal_balance and custodian_balance contain numeric values only | | Small rounding differences are creating mismatches | Tolerance is too strict for your use case | Update the tolerance value in Calculate Balance Difference | | Matched records are not being logged | Google Sheets append node is not configured correctly | Verify the target spreadsheet, sheet tab and credentials in Log Matched Records | | Mismatch records are not being saved | Exception logging sheet is missing expected columns | Confirm the target sheet includes all mapped fields, including ai_explanation and recon_status | | AI explanation is blank | OpenAI credentials or model configuration issue | Reconnect your OpenAI credentials and verify the model is available | | Workflow fails after import | Credentials are not connected in your environment | Reassign all credential-dependent nodes after importing the workflow | | Workflow does not run automatically | Schedule Trigger is not active or workflow is disabled | Activate the workflow and confirm the schedule settings | | Numeric values look wrong in output | Source sheet values are stored with symbols or formatting | Remove currency symbols, commas or text formatting from balance columns | Need Help? If you want help setting up, customizing or extending this workflow, our n8n workflow automation team at WeblineIndia can help you move faster. We can help you with: n8n workflow setup and deployment Google Sheets and finance operations automations OpenAI-powered exception handling Slack / email alert integrations dashboard and reporting extensions custom reconciliation logic production-grade workflow improvements. An n8n automation workflow template by WeblineIndia.
WeblineIndia
web
free
Others in the same category, ranked by how often they are opened.
