Reconcile two files with an AI agent: a safe, end-to-end walkthrough

The threads asking whether AI can just do your reconciliation tend to get two replies: a joke about whatever bot someone is about to plug, or "pay a bookkeeper." Neither tells you how to actually do it. Here is the move that makes an AI agent genuinely useful — and safe — for reconciling two files: don't ask it to reconcile. Ask it to write and run the code that reconciles. Then every number is computed, reproducible, and yours to check.

Stop asking the agent to reconcile

The instinct is to paste two files in and say "reconcile these." Don't. A modern AI agent has two modes, and they are not equally trustworthy. In one it talks — it predicts plausible text, including plausible-sounding numbers. In the other it writes and runs real code in a sandbox, and shows you the output. You want the second mode doing every calculation, and the first mode doing only the messy judgment around it. This is the same division of labor behind AI for setup, code for truth: the model proposes, the code decides.

Why so strict? Because a language model that is asked to add up ten thousand rows will hand you a confident total in the same fluent tone whether it is right or invented — that failure has a name, hallucination, and you cannot spot it by looking. So the matching and the math have to live in code the agent runs, not in the sentence it types. Your job in this walkthrough is mostly to hold that line.

What you need before you start

• An AI agent with a code or data-analysis tool — anything that actually executes Python in a sandbox, not just chat. Tool-agnostic: the discipline is the same whichever you use.
• Two exports of the same thing from two systems (CSV is easiest). A month of real rows beats clean demo data.
• A primary ID you expect to join on — or at least a guess the agent can confirm. If no single column is unique, you are looking for a composite key.
• One number you already trust — a gross sales figure, a deposit total, last month's known balance. That is your control total, and it is how you catch the agent quietly dropping rows.

The walkthrough, step by step

1. Upload both files and ask for a census first — columns, row counts, and three sample rows from each. No matching yet. You are checking the agent read the files the way you expect.
2. Have it propose the join key and say why. Confirm it, or correct it. This is the one decision that quietly sinks most reconciliations, so do not skip it.
3. Ask it to write the merge as code that reads IDs as text, does an outer join, and tags every row by source. Read the code before it runs — it is short.
4. Have it run the code and print the row census: how many rows matched, how many exist only on the left, how many only on the right.
5. For matched rows, have it compute the value differences in code and show only the nonzero ones — not a prose summary.
6. Verify against your control total before you believe anything (next section).
7. Only now let the agent explain the differences in words — timing, fee, refund, real error — which is where it genuinely helps.

The prompt that sets this up is mostly a list of refusals. Paste something like this, adjusted to your filenames and key:

You have two files: system_a.csv and system_b.csv.
Do NOT reconcile them in your reply. Work in steps:
1. Show the columns, row count, and 3 sample rows from each file.
2. Propose which column is the shared key, and explain why.
3. Write Python (pandas) that reads IDs as text, does an OUTER merge
   on that key, with indicator=True and validate="one_to_one".
4. Run it. Print the count of left_only, right_only, and both.
5. For rows in both, compute the amount difference in code and show
   only the nonzero ones. Do not summarize the numbers in prose.

What it should produce is an ordinary pandas merge — the workhorse join, documented in the merging guide. Two arguments do the heavy lifting for trust: indicator=True tags each row as left_only, right_only, or both, and validate="one_to_one" makes the code error out loudly if the key is not unique instead of silently multiplying rows.

import pandas as pd

a = pd.read_csv("system_a.csv", dtype=str)   # keep IDs as text
b = pd.read_csv("system_b.csv", dtype=str)

merged = a.merge(
    b,
    on="order_id",
    how="outer",
    indicator=True,         # tags each row left_only / right_only / both
    validate="one_to_one",  # raises if the key is not unique on either side
)

print(merged["_merge"].value_counts())   # your row-level census

Reading IDs as text matters more than it looks: it is the single most common way a join silently fails, because one system zero-pads an order number and the other does not, or a long ID gets rounded into scientific notation on export. Those CSV gotchas make two identical IDs look different, and the agent will happily merge around them unless you force text. If you have ever done this by hand in Excel with XLOOKUP, it is the same trap, just earlier in the pipeline.

Verify it did not quietly drop rows

This is the step that separates a number you can defend from a confident guess. The agent ran code, which is good — but you still have to prove the code did what you think. Four cheap checks catch nearly everything, and they are the same things an auditor looks for: completeness, a unique key, totals that tie, and a trail you can follow.

The left_only and right_only buckets are not noise — they are the answer. Those are the rows present in one file and missing from the other, which is usually the whole reason you reconciled in the first place. If the agent waves them away as "minor differences," that is the moment to push back.

When this beats a spreadsheet — and when to reach for a tool

An AI agent that writes and runs code is at its best on the awkward one-off: an odd export, a key you have to reconstruct, a file too messy to wrangle by hand but not worth building a pipeline for. It is not the only answer, and it is not always the right one. Here is the honest comparison:

That last column is the recurring complaint underneath the threads. In one r/smallbusiness thread on monthly close time, a commenter describes spending "4-5 hours monthly" and then "outsourcing to a local bookkeeper for $200/month" just to make it stop — a fair trade, but it does not make the numbers any more inspectable. And the cynical replies on the AI side — like the top comment "I'm going to use the first AI tool that your alt account plugs in the comments" on an r/Accounting thread about manual Excel reconciliation — are reacting to vendors who hide the math behind a model. Making the agent show its code is the opposite of that.

I've seen a lot of demos, the problem I have is that the AI solutions require very clear directions.

That top-voted reply in an r/FPandA thread on whether agentic AI tools actually help finance teams is exactly right, and it is good news here: the clarity it asks for is the prompt above. The directions are not vague vibes — they are "read IDs as text, outer merge, validate one-to-one, print the counts, do not summarize the numbers." Give an agent that, and the prompt patterns that keep it in its lane, and you get the speed without surrendering the audit trail. If the agent is ever unavailable or you want a fallback you fully control, the same logic runs as a by-hand method. A reconciliation you can defend is one where you can point at how every number was reached — and that is true whether the code was typed by you or by an agent you supervised.

← All writing