"""Bank reconciliation matching engine (Odoo exports vs bank statement imports). Reads only the input files in data/ and applies the rules in RULES in priority order. Every bank line and every Odoo journal item ends in exactly one group with a rule and a reason code. Writes results/*.csv. The regression test at the end compares the result with data/expected_outcomes.csv.""" import csv, re, os, json, itertools, datetime as dt from collections import defaultdict, Counter from difflib import SequenceMatcher HERE = os.path.dirname(os.path.abspath(__file__)); DATA = os.path.join(HERE, "data"); RES = os.path.join(HERE, "results") os.makedirs(RES, exist_ok=True) END = dt.date(2026, 9, 30) def rd(n): return list(csv.DictReader(open(os.path.join(DATA, n)))) def d(s): return dt.date.fromisoformat(s) def c(x): return round(x + 1e-9, 2) bank = rd("bank_statement_lines.csv"); led = rd("odoo_bank_journal_items.csv"); oi = rd("odoo_open_items.csv") rem = rd("remittance_advices.csv"); stp = rd("stripe_payout_report.csv") for b in bank: b["amount"] = float(b["amount"]); b["dt"] = d(b["date"]) for l in led: l["amount"] = float(l["amount"]); l["dt"] = d(l["date"]) for o in oi: o["residual"] = float(o["residual"]); o["amount_total"] = float(o["amount_total"]) oi_by = {o["inv_no"]: o for o in oi} RULES = [ ("R01", "Duplicate statement import", "Same account, date, description and amount in a second import file", "Exact", "Exclude from Odoo statement (delete duplicate lines)"), ("R02", "Stripe payout to charges", "STRIPE PAYOUT id -> payout report -> charge and refund lines -> Odoo payments by invoice", "Payout net must equal report net to the cent", "Fees and disputes become a difference to book"), ("R03", "Remittance advice (plan managers)", "RA reference in the bank text -> remittance lines -> Odoo payments by invoice", "Date -3/+3 days; sum to the cent", "Short-paid lines registered at full value are flagged"), ("R04", "Internal transfers, sweeps and FX", "Transfer-type Odoo entries with the same pay-run, sweep or FX reference, or same amount within 3 days", "Exact (FX: up to 1%)", "FX difference booked to FX gains/losses"), ("R05", "Payroll run (many bank : one Odoo)", "SALARY lines grouped by pay-run code -> one net-wages entry", "Sum to the cent", "Returned and reissued pays are paired and noted"), ("R06", "ABA batch (one bank : many Odoo)", "ABA file number -> Odoo batch payment BATCH/2026/nnnn", "Sum to the cent", "Returned payments from a batch are flagged"), ("R07", "Cheque number", "CHEQUE nnnnnn -> Odoo CHQ nnnnnn", "Exact amount", "Unpresented over 90 days flagged as stale"), ("R08", "Card terminal settlement", "SETTLEMENT terminal + trading date -> POS daily takings entry", "Exact (difference flagged)", "Refunds missing from the POS export are flagged"), ("R09", "Invoice reference + amount", "Invoice number in any of 6 formats -> Odoo payment(s) on that invoice", "Exact amount", ""), ("R10", "Payee name (fuzzy) + amount + date", "Bank payee text scored against Odoo partners (score >= 0.85) -> unmatched payments within -3/+5 days", "Exact amount, single or same-day set", "Lower confidence: reviewed"), ("R11", "Open invoice or bill (not yet in Odoo)", "Reference or fuzzy payee -> open item with residual equal to the amount, or a set of up to 3", "Exact amount", "Proposed: register the payment in Odoo"), ("R12", "Bank-only patterns", "Fees, interest, merchant fees, chargebacks, returns, dishonours", "Pattern", "Proposed journal per reason code"), ("R13", "Amount + date, unique", "One unmatched Odoo item with the same amount within 3 days and no competing candidate", "Exact amount", "Lowest confidence: reviewed"), ("R99", "No match", "Nothing above applies", "-", "Escalated with a note, or left outstanding (Odoo-only items)"), ] groups = []; gb = {}; gl = {} def G(acct, rule, reason, bids, lids, note="", alloc=None, partner="", score=""): gid = f"G{len(groups) + 1:05d}" for x in bids: assert x not in gb, x; gb[x] = gid for x in lids: assert x not in gl, x; gl[x] = gid groups.append(dict(group_id=gid, account=acct, rule=rule, reason=reason, bank_ids=list(bids), ledger_ids=list(lids), note=note, alloc=alloc or [], partner=partner, score=score)); return gid B = {b["line_id"]: b for b in bank}; Lx = {l["entry_id"]: l for l in led} def free_b(acct): return [b for b in bank if b["account"] == acct and b["line_id"] not in gb] def free_l(acct): return [l for l in led if l["journal"] == acct and l["entry_id"] not in gl] ACCTS = ["OPS", "PAY", "MER", "USD"] # ---------- partner name scoring (payee variations) def norm(s): s = s.upper().replace("&", "AND") for a, b in [("MGMT", "MANAGEMENT"), ("MANAGE ", "MANAGEMENT "), (" PTY LTD", ""), (" PTY", ""), (" LTD", ""), (" INC", ""), (" LLC", "")]: s = s.replace(a, b) return re.sub(r"[^A-Z0-9 ]", "", s).strip() PARTNERS = sorted({l["partner"] for l in led} | {o["partner"] for o in oi} | {r["payer"] for r in rem}) PARTNERS = [p for p in PARTNERS if not p.startswith("Stripe patient")] ALIAS = {"ATO": "Australian Taxation Office", "SUPERCHOICE": "Super clearing house"} def score(txt, p): a = norm(txt); b = norm(p) if not a: return 0 s1 = SequenceMatcher(None, a, b[:len(a)]).ratio() a2, b2 = a.replace(" ", ""), b.replace(" ", "") s2 = SequenceMatcher(None, a2, b2[:len(a2)]).ratio() return max(s1, s2) if len(a2) >= 8 else 0 def payee_text(desc): t = re.sub(r"^(DIRECT CREDIT|DIRECT DEBIT|INWARD TT|OUTWARD TT|RETURNED PAYMENT ABA\d+)\s+", "", desc) t = re.sub(r"\b(RA\d+|CLM\d+|INV\S*|RE INV.*|BILL\d+|\d{3,})\b.*$", "", t).strip() return t.replace(" INVALID ACCT", "") alias_rows = {} def best_partner(desc): t = payee_text(desc) if not t: return None, 0, t sc = sorted(((score(t, p), p) for p in PARTNERS), reverse=True) if sc and sc[0][0] >= 0.85 and (len(sc) < 2 or sc[0][0] - sc[1][0] > 0.05): return sc[0][1], round(sc[0][0], 3), t return None, round(sc[0][0], 3) if sc else 0, t def note_alias(t, p, s): if p and t: r = alias_rows.setdefault((t, p), dict(bank_text=t, odoo_partner=p, score=s, lines=0)); r["lines"] += 1 INVPAT = [(r"INV/2026/(\d{5})", 1), (r"RE INV 2026 (\d{5})", 1), (r"\bINV ?(\d{3,5})\b", 1), (r"\b(0\d{4})\b", 1)] def inv_refs(desc): out = [] for p, g in INVPAT: for m in re.finditer(p, desc): out.append(f"INV/2026/{int(m.group(g)):05d}") return list(dict.fromkeys(out)) # R01 duplicate imports seen = {} for b in sorted(bank, key=lambda x: (x["account"], x["date"], x["import_batch"])): k = (b["account"], b["date"], b["description"], b["amount"]) if k in seen and seen[k] != b["import_batch"]: G(b["account"], "R01", "DUPIMP", [b["line_id"]], [], f"Duplicate of a line in import {seen[k]}") else: seen.setdefault(k, b["import_batch"]) # R02 Stripe rep = defaultdict(list) for r in stp: rep[r["payout_id"]].append(r) for b in free_b("OPS"): m = re.search(r"STRIPE PAYOUT STRIPE (po_\w+)", b["description"]) if not m: continue rows = rep[m.group(1)]; net = c(sum(float(r["net"]) for r in rows)) assert net == b["amount"], (b, net) lids = [] for r in rows: if r["type"] not in ("charge", "refund"): continue want = "customer_payment" if r["type"] == "charge" else "customer_refund" cand = [l for l in free_l("OPS") if l["ref"] == r["invoice"] and l["move_type"] == want and abs(l["amount"] - float(r["gross"])) < 0.005 and l["entry_id"] not in lids] lids.append(cand[0]["entry_id"]) fee = c(sum(float(r["fee"]) for r in rows)); disp = c(-sum(float(r["gross"]) for r in rows if r["type"] == "dispute")) G("OPS", "R02", "STRIPE-FEE", [b["line_id"]], lids, f"{len(rows)} report lines; fees {fee:.2f}" + (f"; dispute {disp:.2f}" if disp else "")) # R03 remittances ra = defaultdict(list) for r in rem: ra[r["remit_ref"]].append(r) for b in free_b("OPS"): m = re.search(r"\b(RA\d+)\b", b["description"]) if not m: continue rows = ra[m.group(1)]; assert c(sum(float(r["paid"]) for r in rows)) == b["amount"] lids = [] for r in rows: cand = [l for l in free_l("OPS") if l["ref"] == r["inv_no"] and l["move_type"] == "customer_payment" and abs((l["dt"] - b["dt"]).days) <= 3 and l["entry_id"] not in lids] if cand: lids.append(cand[0]["entry_id"]) p, s, t = best_partner(b["description"]); note_alias(t, p, s) if not lids: alloc = [(r["inv_no"], float(r["paid"])) for r in rows] ok = all(oi_by.get(i) and oi_by[i]["residual"] >= a - 0.005 for i, a in alloc) G("OPS", "R03", "REG" if ok else "UNEXP", [b["line_id"]], [], f"Receipt not registered in Odoo; remittance {m.group(1)} lists {len(rows)} invoices", alloc, p, s) continue lt = c(sum(Lx[i]["amount"] for i in lids)) short = [r for r in rows if float(r["paid"]) < float(r["invoiced"]) - 0.005] if lt == b["amount"]: G("OPS", "R03", "MATCH", [b["line_id"]], lids, f"{len(lids)} invoices" + (f"; {len(short)} short-paid, registered correctly" if short else ""), partner=p, score=s) else: G("OPS", "R03", "SHORT", [b["line_id"]], lids, "Registered at invoice value; remittance shows short payment on " + ", ".join(r["inv_no"] for r in short), partner=p, score=s) # R04 transfers, sweeps, FX ; R05 payroll ; R06 ABA ; R07 cheques ; R08 settlements def tok(desc): m = re.search(r"\b(PR\d{6}|FX\d{4})\b", desc); return m.group(1) if m else None for a in ACCTS: # payroll first (many bank : one) runs = defaultdict(list) for b in free_b(a): m = re.match(r"SALARY .* (PR\d{6})$", b["description"]) if m: runs[m.group(1)].append(b) for pr, bl in runs.items(): cand = [l for l in free_l(a) if l["ref"] == pr] if len(cand) == 1 and c(sum(x["amount"] for x in bl)) == cand[0]["amount"]: G(a, "R05", "MATCH", [x["line_id"] for x in bl], [cand[0]["entry_id"]], f"{len(bl)} salary lines") for b in free_b(a): t = tok(b["description"]) if t: cand = [l for l in free_l(a) if t in l["ref"].split()[-1] or l["ref"].endswith(t)] cand = [l for l in cand if (l["amount"] > 0) == (b["amount"] > 0)] if len(cand) == 1: diff = c(b["amount"] - cand[0]["amount"]) if diff == 0: G(a, "R04", "MATCH", [b["line_id"]], [cand[0]["entry_id"]], "Reference " + t) elif t.startswith("FX") and abs(diff) <= abs(b["amount"]) * 0.01: G(a, "R04", "FXR", [b["line_id"]], [cand[0]["entry_id"]], f"FX conversion {t}: bank rate vs Odoo rate, difference {diff:.2f}") continue if "SWEEP" in b["description"]: cand = [l for l in free_l(a) if l["move_type"] == "transfer" and l["amount"] == b["amount"] and 0 <= (l["dt"] - b["dt"]).days <= 3] if len(cand) >= 1: cand.sort(key=lambda l: abs((l["dt"] - b["dt"]).days)); G(a, "R04", "MATCH", [b["line_id"]], [cand[0]["entry_id"]], "Sweep, same amount") m = re.match(r"ABA PAYMENT FILE (\d{4})", b["description"]) if m: cand = [l for l in free_l(a) if l["batch_ref"] == f"BATCH/2026/{m.group(1)}"] if c(sum(l["amount"] for l in cand)) == b["amount"]: G(a, "R06", "MATCH", [b["line_id"]], [l["entry_id"] for l in cand], f"{len(cand)} vendor payments in batch") m = re.match(r"CHEQUE (\d{6})$", b["description"]) if m: cand = [l for l in free_l(a) if l["ref"] == f"CHQ {m.group(1)}" and l["amount"] == b["amount"]] if cand: G(a, "R07", "MATCH", [b["line_id"]], [cand[0]["entry_id"]], f"Cheque {m.group(1)}") m = re.match(r"SETTLEMENT (T\d{3}) (\d{6})$", b["description"]) if m: cand = [l for l in free_l(a) if l["ref"] == f"POS {m.group(1)} {m.group(2)}"] if cand: diff = c(b["amount"] - cand[0]["amount"]) G(a, "R08", "MATCH" if diff == 0 else "REFUND-GAP", [b["line_id"]], [cand[0]["entry_id"]], "" if diff == 0 else f"Settlement {diff:.2f} lower than POS takings: terminal refund not in POS export") # R09 invoice reference + amount (also USD and bill references) for a in ACCTS: for b in free_b(a): refs = inv_refs(b["description"]) + [re.sub(r"BILL(\d{4})(\d{4})", r"BILL/\1/\2", x) for x in re.findall(r"BILL\d{8}", b["description"])] if not refs: continue p, s, t = best_partner(b["description"]) for r in refs: cand = [l for l in free_l(a) if l["ref"] == r and -3 <= (l["dt"] - b["dt"]).days <= 5] if not cand: continue if p and cand[0]["partner"] != p: continue diff = c(b["amount"] - sum(l["amount"] for l in cand)) if diff == 0: note_alias(t, p, s); G(a, "R09", "MATCH", [b["line_id"]], [l["entry_id"] for l in cand], f"Reference {r}", partner=p, score=s); break if b["description"].startswith("INWARD TT") and -50 <= diff < 0: note_alias(t, p, s); G(a, "R09", "FEE-DIFF", [b["line_id"]], [l["entry_id"] for l in cand], f"Reference {r}; {-diff:.2f} deducted by intermediary bank", partner=p, score=s); break # R10 payee fuzzy + amount + date for a in ACCTS: for b in free_b(a): if not re.match(r"(DIRECT CREDIT|DIRECT DEBIT|INWARD TT|OUTWARD TT)", b["description"]): continue p, s, t = best_partner(b["description"]) if not p: continue pool = [l for l in free_l(a) if l["partner"] == p and -3 <= (l["dt"] - b["dt"]).days <= 5 and (l["amount"] > 0) == (b["amount"] > 0)] hit = [l for l in pool if l["amount"] == b["amount"]] if len(hit) >= 1: hit.sort(key=lambda l: abs((l["dt"] - b["dt"]).days)); note_alias(t, p, s) G(a, "R10", "MATCH", [b["line_id"]], [hit[0]["entry_id"]], f"Payee '{t}' = {p}", partner=p, score=s); continue byday = defaultdict(list) for l in pool: byday[l["date"]].append(l) for day, ls in byday.items(): if 2 <= len(ls) <= 4 and c(sum(l["amount"] for l in ls)) == b["amount"]: note_alias(t, p, s); G(a, "R10", "MATCH", [b["line_id"]], [l["entry_id"] for l in ls], f"Payee '{t}' = {p}; {len(ls)} invoices in one payment", partner=p, score=s); break # R11 open items (receipts / debits never entered in Odoo) used = defaultdict(float) def avail(o): return o["residual"] - used[o["inv_no"]] for a in ACCTS: for b in free_b(a): if not re.match(r"(DIRECT CREDIT|DIRECT DEBIT|INWARD TT)", b["description"]): continue p, s, t = best_partner(b["description"]) want = "out_invoice" if b["amount"] > 0 else "in_invoice"; amt = abs(b["amount"]) refs = inv_refs(b["description"]) hit = None for r in refs: o = oi_by.get(r) if o and o["move_type"] == want and abs(avail(o) - amt) < 0.005: if p and o["partner"] != p: G(a, "R11", "UNEXP", [b["line_id"]], [], f"Reference {r} belongs to {o['partner']}, but the payer reads '{t}' ({p}). Amount agrees. Confirm with both customers before allocating.", partner=p, score=s) hit = "x"; break hit = [(o["inv_no"], amt)]; break if hit == "x": continue if not hit and p: po = sorted([o for o in oi if o["partner"] == p and o["move_type"] == want and avail(o) > 0.005], key=lambda o: o["date"]) one = [o for o in po if abs(avail(o) - amt) < 0.005] if one: hit = [(one[0]["inv_no"], amt)] else: for k in (2, 3): for combo in itertools.combinations(po[:12], k): if abs(sum(avail(o) for o in combo) - amt) < 0.005: hit = [(o["inv_no"], avail(o)) for o in combo]; break if hit: break if hit: for i, x in hit: used[i] += x note_alias(t, p, s) G(a, "R11", "REG" if want == "out_invoice" else "REGBILL", [b["line_id"]], [], ("Receipt" if want == "out_invoice" else "Direct debit") + " not entered in Odoo; matches open " + ", ".join(i for i, _ in hit), [(i, x if want == "out_invoice" else -x) for i, x in hit], p, s) elif p and want == "in_invoice": note_alias(t, p, s); G(a, "R11", "NOBILL", [b["line_id"]], [], f"Direct debit from {p} with no bill in Odoo; request the tax invoice", partner=p, score=s) # R12 bank-only patterns PAT = [(r"^(ACCOUNT KEEPING FEE|TRANSACTION FEES|INTL TRANSFER FEE)", "FEE"), (r"^INTEREST CREDIT", "INT"), (r"^MERCHANT SERVICE FEE", "MERCH"), (r"^CHARGEBACK", "CHGBK"), (r"^RETURNED PAYMENT", "RETPAY")] for a in ACCTS: for b in free_b(a): for p_, rc in PAT: if re.search(p_, b["description"]): note = "" if rc == "RETPAY": m = re.search(r"ABA(\d{4})", b["description"]); p, s, t = best_partner(b["description"]) src = [l for l in led if l["batch_ref"] == f"BATCH/2026/{m.group(1)}" and l["partner"] == p and -l["amount"] == b["amount"]] note = f"Payment to {p} returned from ABA batch {m.group(1)} ({src[0]['ref'] if src else '?'}); bank details wrong" G(a, "R12", rc, [b["line_id"]], [], note); break # returned and reissued salary: pair them rets = [b for b in free_b(a) if b["description"].startswith("RETURN SALARY")] for r in rets: name_pr = re.match(r"RETURN SALARY (.*) (PR\d{6})", r["description"]) re_ = [b for b in free_b(a) if b["description"] == f"SALARY {name_pr.group(1)} {name_pr.group(2)} REISSUE" and b["amount"] == -r["amount"]] if re_: G(a, "R12", "RETPAY-PAY", [r["line_id"], re_[0]["line_id"]], [], f"Pay returned (invalid BSB) and reissued; nets to nil") # R13 amount + date unique (ATO, super, loan, etc.) for a in ACCTS: for b in free_b(a): cand = [l for l in free_l(a) if l["amount"] == b["amount"] and abs((l["dt"] - b["dt"]).days) <= 3] if len(cand) == 1: competitors = [x for x in free_b(a) if x["amount"] == b["amount"] and abs((x["dt"] - b["dt"]).days) <= 3] if len(competitors) == 1: G(a, "R13", "MATCH", [b["line_id"]], [cand[0]["entry_id"]], f"Amount and date: {cand[0]['partner']} {cand[0]['ref']}") # R13b: super and timing differences with token beyond window for a in ACCTS: for b in free_b(a): t = tok(b["description"]) if t: cand = [l for l in free_l(a) if l["ref"].endswith(t) and l["amount"] == b["amount"]] if len(cand) == 1: G(a, "R04", "MATCH", [b["line_id"]], [cand[0]["entry_id"]], "Reference " + t) # R99 leftovers for a in ACCTS: for b in free_b(a): G(a, "R99", "UNEXP", [b["line_id"]], [], "No Odoo item, open invoice or known pattern. Escalated.") neg_res = {o["inv_no"]: o for o in oi if o["move_type"] == "out_invoice" and o["residual"] < -0.005} for l in free_l(a): if l["move_type"] == "customer_payment" and l["ref"] in neg_res and abs(neg_res[l["ref"]]["residual"] + l["amount"]) < 0.005: G(a, "R99", "DUPL", [], [l["entry_id"]], f"{l['ref']} shows a credit balance of {-neg_res[l['ref']]['residual']:.2f}: this payment was registered twice"); continue if l["ref"].startswith("CHQ") and (END - l["dt"]).days > 90: G(a, "R99", "STALE", [], [l["entry_id"]], f"Cheque issued {l['date']}, unpresented for {(END - l['dt']).days} days"); continue G(a, "R99", "OUT", [], [l["entry_id"]], "In Odoo, not yet on the bank statement at 30 Sep") # ---------- outputs def w(n, rows): with open(os.path.join(RES, n), "w", newline="") as f: wr = csv.DictWriter(f, fieldnames=list(rows[0].keys())); wr.writeheader(); wr.writerows(rows) gr = [] for g in groups: bt = c(sum(B[x]["amount"] for x in g["bank_ids"])); lt = c(sum(Lx[x]["amount"] for x in g["ledger_ids"])) gr.append(dict(group_id=g["group_id"], account=g["account"], rule=g["rule"], reason=g["reason"], n_bank=len(g["bank_ids"]), n_odoo=len(g["ledger_ids"]), bank_total=bt, odoo_total=lt, difference=c(bt - lt), partner=g["partner"], score=g["score"], note=g["note"])) w("groups.csv", gr) w("allocations.csv", [dict(group_id=g["group_id"], inv_no=i, amount=c(x)) for g in groups for i, x in g["alloc"]] or [dict(group_id="", inv_no="", amount=0)]) w("aliases.csv", sorted(alias_rows.values(), key=lambda r: (r["odoo_partner"], r["bank_text"]))) w("rules.csv", [dict(rule=r[0], name=r[1], logic=r[2], tolerance=r[3], outcome=r[4]) for r in RULES]) w("bank_map.csv", [dict(line_id=x, group_id=gb[x]) for x in B]); w("odoo_map.csv", [dict(entry_id=x, group_id=gl[x]) for x in Lx]) assert len(gb) == len(bank) and len(gl) == len(led) # ---------- regression test against expected outcomes exp = rd("expected_outcomes.csv"); tgm = defaultdict(set); kind = {} for e in exp: tgm[e["tg"]].add(e["id"]); kind[e["id"]] = e gm = {g["group_id"]: set(g["bank_ids"]) | set(g["ledger_ids"]) for g in groups} gid_of = {**gb, **gl} ok = bad = 0; fails = [] treason = {} for e in exp: if e["kind"] == "bank" or e["tg"] not in treason: treason[e["tg"]] = e["reason"] if e["kind"] == "bank" or e["tg"] not in treason else treason[e["tg"]] for e in exp: if e["kind"] == "ledger" and e["reason"] in ("SHORT", "DUPL", "STALE"): treason[e["tg"]] = e["reason"] has_bank = {e["tg"] for e in exp if e["kind"] == "bank"} for e in exp: i = e["id"]; g = groups[int(gid_of[i][1:]) - 1] mine = {x for x in gm[g["group_id"]] if kind[x]["kind"] != kind[i]["kind"]} theirs = {x for x in tgm[e["tg"]] if kind[x]["kind"] != kind[i]["kind"]} if e["tg"] != "DUP" else set() tr = treason[e["tg"]] r_ok = g["reason"] == tr or (g["reason"] == "OUT" and e["tg"] not in has_bank) if mine == theirs and r_ok: ok += 1 else: bad += 1; fails.append((i, tr, g["reason"], g["rule"], (B.get(i) or Lx.get(i)).get("description", (Lx.get(i) or {}).get("label")))) st = dict(bank_lines=len(bank), odoo_items=len(led), groups=len(groups), test_items=len(exp), agree=ok, disagree=bad, by_rule=Counter(g["rule"] for g in groups), by_reason=Counter(g["reason"] for g in groups)) json.dump(st, open(os.path.join(RES, "engine_test.json"), "w"), indent=1, default=str) print(json.dumps({k: v for k, v in st.items()}, default=str)) for f in fails[:25]: print("FAIL", f)