Reconcile partial invoice payments without counting deposits as sales
Suppose one invoice for 100 units is paid in two instalments: 40 and 60. Counting each incoming payment as a new sale inflates sales volume. Instead, match receipts against the original invoice identifier and keep unidentified payments separate.
The following synthetic example uses Python's decimal arithmetic, which is preferable to binary floating-point for exact two-decimal examples.
Match payments to invoice IDs
from collections import defaultdict from decimal import Decimal invoices = {"INV-100": Decimal("100.00"), "INV-200": Decimal("50.00")} payments = [ ("INV-100", "40.00"), ("INV-100", "60.00"), ("INV-200", "49.00"), ("UNKNOWN", "10.00"), ] received = defaultdict(Decimal) unmatched = [] for invoice_id, amount_text in payments: amount = Decimal(amount_text) if invoice_id not in invoices: unmatched.append((invoice_id, amount)) else: received[invoice_id] += amount for invoice_id, invoiced in invoices.items(): outstanding = invoiced - received[invoice_id] print(f"{invoice_id}: outstanding {outstanding:.2f}") print("Unmatched payment records:", len(unmatched)) Expected output:
INV-100: outstanding 0.00 INV-200: outstanding 1.00 Unmatched payment records: 1 What the result actually proves
The two partial payments clear one invoice, INV-100. INV-200 is still short by 1.00. The unidentified payment cannot be attributed to a customer or invoice without more evidence.
A real payment-processor reconciliation needs additional categories: fees, reversals, refunds, currency conversion, payout cutoffs, settlement dates, and missing identifiers. Net bank deposits can be smaller than gross customer payments. Never label a bank deposit as a new customer sale without joining it to underlying records.
For production use, record invoice and transaction IDs, reconcile time windows and currencies, and preserve raw source records. Treat ambiguous payments as exceptions pending investigation instead of guessing their origin. This example does not assert that anyone purchased a product.
USA-1 offers scoped CSV and Excel reconciliation services when a reproducible exception report is needed.
Disclosure: This technical tutorial was prepared with AI assistance. The Python example was executed against fabricated data and its output was checked before approval for publication.
