ع
Start Topics Teams Reference What's new Saved
playbook

Reconcile two files, line by line

Match invoices against payments (or bank against ledger) by ID, list everything unpaid or unmatched, total the outstanding, and flag any payment with no invoice — a monthly check you can defend line by line.

medium ~40 min
when to reach for this

Two files should agree and don't: invoices you sent versus payments you received, or your ledger versus the bank statement. Somewhere in there is unpaid revenue, a payment you can't tie to anything, or a charge that hit twice. Done by hand it's an afternoon of eyeballing two spreadsheets side by side. This is the reconciliation system you run every month — match by ID, surface every exception, total the gap, and produce a result you can walk a CFO through line by line.

gather this first
  • The two files, each already profiled via Make a messy export trustworthy — invoices-sent.csv and payments-received.csv (or ledger.csv and bank-statement.csv).
  • The shared key that links them: the invoice number, transaction ID, or reference column — and confirmation it's formatted the same way in both files.
  • A known total to check against — "we expected ~62,000 in payments this month" — so the reconciliation has an anchor.
the workflow

  1. Confirm the join key before matching

    A reconciliation is only as good as the column you match on. In Claude Desktop, open the folder both files live in and ask in the chat — no terminal needed. Make Claude prove the key exists and lines up in both files first — a mismatched ID format silently turns real matches into fake exceptions.

    you ask
    Open invoices-sent.csv and payments-received.csv. Confirm both have an invoice-number column, show me 3 example values from each, and tell me if the formats match exactly (leading zeros, INV- prefix, spaces). Don't reconcile yet — I just want to know the key is clean.

    what you get back A read-back: "Invoices use INV-00142; payments use 00142 with no prefix — these won't match as-is." Catching this now prevents a reconciliation that flags everything as unmatched for a formatting reason, not a real one.

    Most 'broken' reconciliations are a formatting mismatch in the key, not missing money. Always verify the key before you trust the exceptions.

  2. Match and bucket every row

    Ask for the full picture in named buckets, not just a list of problems — matched, unpaid, and orphan payments — so nothing falls through a gap you didn't ask about.

    you ask
    Normalize the invoice numbers so they match, then reconcile. Put every row in one of three buckets: (1) matched — invoice with a payment, (2) unpaid — invoice with no payment, (3) orphan — payment with no invoice. Give me a count and total amount for each bucket. Show how you matched so I can check it.

    what you get back Three buckets with totals: "Matched: 184 invoices / 58,200. Unpaid: 11 invoices / 6,400. Orphan payments: 2 / 1,150 — payments with no invoice to tie to." Plus a one-line method note you can verify.

  3. Total the outstanding and explain the orphans

    The unpaid total is the number leadership wants; the orphan payments are the number that gets someone in trouble. Pull both out clearly and have Claude reason about what each orphan might be.

    you ask
    List the 11 unpaid invoices with customer, amount, and how many days overdue, and give me the outstanding total. Then for the 2 orphan payments, suggest what each might be — a duplicate payment, a payment on a missing invoice, or a refund — but mark these as guesses to investigate, not conclusions.

    what you get back An aged unpaid list ("INV-00138, Acme, 1,200, 47 days overdue") totaling 6,400 outstanding, plus two flagged orphans: "00461 / 600 — no matching invoice; possibly a deposit or a duplicate. Investigate."

    An orphan payment is a flag, not a verdict. Claude surfaces it; a human checks the bank and decides what it is.

  4. Produce the defensible reconciliation

    Turn the work into one artifact you could hand to an auditor — totals that tie out, exceptions listed, method stated — so the reconciliation is repeatable and reviewable, not a one-off.

    you ask
    Lay this out as a reconciliation report: a summary line that ties out (invoiced total = paid + unpaid + adjustments), the exception lists (unpaid and orphans), and a one-paragraph method note. Save it as reconciliation-may.md so I can re-run it next month.

    what you get back A reconciliation-may.md whose top line balances — "Invoiced 64,600 = Paid 58,200 + Unpaid 6,400 + 0 unexplained" — with the exceptions below and the method spelled out. If it doesn't tie out, the report says so and where the gap is.

make it your own
  • Bank vs ledger: swap the files to bank-statement.csv and ledger.csv and match on transaction reference — the three-bucket logic is identical.
  • Fuzzy matches: when IDs are missing, ask Claude to also propose matches by same amount + same date (as suggestions to confirm), but never auto-accept a fuzzy match as reconciled.
  • Run it monthly (Power Track): once the prompts are stable, a scheduled agent (see the Capabilities tab) can run the reconciliation when the export lands and email you only the exceptions — but a human still signs off.
  • Multi-currency files: if the two sides settle in different currencies, have Claude normalize to one reporting currency first — stating which rate and date — so a match isn't missed for an exchange-rate difference rather than a real one.
watch out for
  • Verify the join key first. A formatting mismatch makes real matches look like exceptions and sends you chasing money that was never missing.
  • A reconciliation that doesn't tie out is a finding, not a failure — never let Claude force the numbers to balance. An unexplained gap is exactly what you needed to find.
  • Separate the reconciler from the approver. Claude (and you) can surface and explain an exception, but writing off an orphan or an unpaid balance should be a different person's sign-off — keep the audit trail (the saved report and its method note) so the reconciliation is reviewable, not just done.
  • Orphan payments and unpaid invoices touch customer financial data — keep it in your approved workspace, swap names for [placeholder] if sharing, and remember Claude flags exceptions but a human owns the decision to write one off or chase it.

you'll end up with A monthly reconciliation that ties out — matched, unpaid, and orphan buckets with totals, an aged outstanding list, and a method note — reviewable line by line and repeatable next month.

Questions people ask

Why confirm the join key before reconciling anything?
Because most "broken" reconciliations are a formatting mismatch in the key, not missing money. If invoices use `INV-00142` and payments use `00142`, they won't match as-is — verifying the key first prevents Claude from flagging every row as unmatched for a formatting reason.
What does the reconciliation actually produce?
Three buckets with counts and totals — matched (invoice with a payment), unpaid (invoice with no payment), and orphan (payment with no invoice) — plus an aged outstanding list and a method note, laid out as a report whose top line ties out: invoiced = paid + unpaid + adjustments.
What does an orphan payment mean?
It's a flag, not a verdict — a payment with no matching invoice that could be a duplicate, a deposit, or a payment on a missing invoice. Claude surfaces it and suggests possibilities; a human checks the bank and decides what it actually is.
What if the numbers don't tie out?
That's a finding, not a failure. Never let Claude force the numbers to balance — an unexplained gap is exactly what reconciliation exists to surface, so the report should say so and show where the gap is.