AI Rental Payment Reconciliation Agent with OpenAI

Unlock Streamlined Rental Accounting with this AI Agent

This AI Agent transforms the tedious task of rental payment reconciliation into an automated, efficient process. It watches a designated folder for new bank statement CSV files. Upon detecting a new file, the agent ingests the transaction data and leverages an OpenAI GPT-4o model to perform intelligent analysis. Using custom tools, it cross-references payments with tenant and property details stored in a local XLSX workbook. The agent's core abilities include identifying missed payments, incorrect amounts, payments near lease termination, and outstanding fees, then generating a structured report of these actionable items and appending them to an 'alerts' sheet in your workbook.

Key Features & Benefits

• Automated Bank Statement Ingestion: Watches a local folder for new CSV bank statements, triggering the reconciliation process automatically.
• AI-Powered Reconciliation: Utilizes an OpenAI (GPT-4o) powered Langchain agent for sophisticated analysis of payment data against tenant records and rental agreements.
• Local Data Integration: Securely accesses tenant and property information from a local XLSX file, ensuring data privacy (ideal for self-hosted n8n).
• Discrepancy Detection: Intelligently identifies various payment issues: missed/late payments, underpayments, overpayments, and end-of-tenancy reconciliation items.
• Structured Reporting: Outputs flagged issues in a clean JSON format and automatically appends them to a designated 'alerts' sheet in your Excel workbook for easy review and action.
• Customizable Logic: The agent's system prompt can be tailored to your specific reconciliation rules and criteria.
• Time Savings: Drastically reduces manual effort and time spent on reconciling rental income, freeing up resources for property managers and solopreneurs.

• An n8n instance (self-hosted is required for local file system access as designed).
• OpenAI API Key with access to a function-calling capable model (e.g., GPT-4o, GPT-4).
• Local file system access for n8n to read bank statement CSVs and read/write to the reconciliation XLSX workbook.
• A reconciliation workbook (e.g., reconcilation-workbook.xlsx) with sheets typically named 'tenants', 'properties', and 'alerts'. Ensure column headers in 'tenants' and 'properties' sheets match those expected by the 'Get Tenant Details' and 'Get Property Details' tool nodes (e.g., 'Tenant Name', 'Tenant ID', 'Property ID').

1. Download the n8n workflow JSON file.
2. Import the workflow into your self-hosted n8n instance.
3. Configure the 'Watch For Bank Statements' node: update the path parameter to the directory where your bank statement CSV files will be placed (e.g., /home/node/host_mount/reconciliation_project). This path must be accessible to your n8n instance.
4. In the 'Set Variables' node, update the spreadsheet_location value to the absolute path of your reconciliation XLSX file (e.g., /home/node/host_mount/reconciliation_project/reconcilation-workbook.xlsx).
5. Configure the 'OpenAI Chat Model' node: select or add your OpenAI API credentials. Ensure the chosen model (default gpt-4o) is appropriate.
6. Verify the sheet names and column headers referenced in the JavaScript code of the 'Get Tenant Details' and 'Get Property Details' tool nodes. Adjust if your XLSX structure differs (default sheets: 'tenants', 'properties').
7. Ensure your reconciliation XLSX workbook contains sheets named 'tenants', 'properties', and 'alerts'. The workflow will attempt to append data to the 'alerts' sheet.
8. Review and customize the systemMessage in the 'Reconcile Rental Payments' (Langchain Agent) node to align with your specific reconciliation business rules and desired output format.
9. Test the workflow with a sample CSV bank statement in the watched folder.
10. Activate the workflow for ongoing, automated reconciliation.