How to Create Automated Invoice Matching and Reconciliation

Matching payments to invoices by hand is slow and error-prone. I built a system to automate invoice matching and reconciliation that compares bank transactions against open invoices, matches them by amount and reference, and flags anything that does not line up.

Reconciliation that used to take hours now takes minutes.

What You Need Before Starting

• Python 3.8+
• Bank transaction exports (CSV or API)
• Invoice database
• Matching rules configuration

Step 1: Load Bank Transactions

import csv
from pathlib import Path

def load_bank_transactions(csv_path):
    transactions = []
    with open(csv_path) as f:
        reader = csv.DictReader(f)
        for row in reader:
            transactions.append({
                "date": row["Date"],
                "description": row["Description"],
                "amount": float(row["Amount"]),
                "reference": row.get("Reference", "")
            })
    return transactions

Step 2: Match Transactions to Invoices

def match_transactions(transactions, open_invoices):
    matched = []
    unmatched_transactions = []
    unmatched_invoices = list(open_invoices)

    for txn in transactions:
        best_match = None
        for inv in unmatched_invoices:
            if abs(txn["amount"] - inv["amount"]) < 0.01:
                if inv["invoice_number"] in txn.get("reference", "") or inv["client_name"].lower() in txn["description"].lower():
                    best_match = inv
                    break

        if best_match:
            matched.append({"transaction": txn, "invoice": best_match})
            unmatched_invoices.remove(best_match)
        else:
            for inv in unmatched_invoices:
                if abs(txn["amount"] - inv["amount"]) < 0.01:
                    matched.append({"transaction": txn, "invoice": inv, "confidence": "amount_only"})
                    unmatched_invoices.remove(inv)
                    break
            else:
                unmatched_transactions.append(txn)

    return {"matched": matched, "unmatched_txn": unmatched_transactions, "unmatched_inv": unmatched_invoices}

Step 3: Update Invoice Status

import sqlite3
from datetime import datetime

def apply_matches(conn, matches):
    for match in matches["matched"]:
        inv = match["invoice"]
        conn.execute(
            "UPDATE invoices SET status='paid', paid_at=? WHERE invoice_number=?",
            (datetime.now().isoformat(), inv["invoice_number"])
        )
    conn.commit()
    return len(matches["matched"])

Step 4: Generate Reconciliation Report

def reconciliation_report(matches):
    report = "# Reconciliation Report\n\n"
    report += f"Matched: {len(matches['matched'])}\n"
    report += f"Unmatched transactions: {len(matches['unmatched_txn'])}\n"
    report += f"Unpaid invoices: {len(matches['unmatched_inv'])}\n\n"

    if matches["unmatched_txn"]:
        report += "## Unmatched Transactions (need manual review)\n\n"
        for txn in matches["unmatched_txn"]:
            report += f"- {txn['date']}: ${txn['amount']:.2f} - {txn['description']}\n"

    return report

Step 5: Schedule Weekly Reconciliation

def run_reconciliation(conn, bank_csv_path):
    transactions = load_bank_transactions(bank_csv_path)
    open_invoices = get_open_invoices(conn)
    matches = match_transactions(transactions, open_invoices)
    applied = apply_matches(conn, matches)
    report = reconciliation_report(matches)

    print(f"Reconciled {applied} invoices. {len(matches['unmatched_txn'])} need review.")
    return report

What to Build Next

Add fuzzy matching for partial payments and payments that combine multiple invoices. Real-world payment matching is messy. The system should handle the common cases automatically and surface the edge cases for human review.

Related Reading

• Building Your First Automation: A Complete Guide - automation fundamentals
• Cost of Manual vs Cost of Automated - the cost of manual reconciliation
• The Automation Decision Tree - deciding what to automate