How to Write a Reconciliation Prompt That Cites the Row It Flags

The first section of your prompt is not the task. It is the identity scheme. A model cannot cite "row 4521" unless it knows what a row is, which column carries the ID, and whether IDs are unique.

Write this block explicitly, and keep it short enough that it stays in attention:

SOURCE ROW IDENTIFIERS

Ledger file (ledger.csv):
  - Column "row_id" is the unique ledger identifier. Format: integer, e.g. 4521.
  - Column "account" is the GL account name, e.g. "Utilities".
  - Column "amount" is in USD, two decimals, positive for debits.
  - 1,204 rows, row_id values are unique. Do not construct IDs.

Plan file (plan.csv):
  - Column "plan_id" is the unique plan identifier. Format: string, e.g. "889".
  - Column "account" uses the same account names as the ledger.
  - Column "amount" is in USD, two decimals, positive for debits.
  - 342 rows, plan_id values are unique.

Cite row_id and plan_id exactly as they appear. Never reformat, zero-pad or abbreviate them.
If you cannot find a row that supports a claim, write "NO_CITATION" and stop that line.

Two details do most of the work here. First, stating the uniqueness and the expected magnitude ("integer, e.g. 4521") lets the model validate its own citations against the shape of the data. Second, the NO_CITATION escape hatch matters more than it looks. Without it, a model under pressure to produce a complete table will manufacture a row rather than admit a gap. Giving it a legal way to fail converts silent fabrication into a visible flag.

If your source data is large, this is also where prompt format earns its keep. A benchmark of structured prompt formats found that lossless evidence aliases, which define repeated metadata once and encode each record as a compact row with a stable citation ID, beat JSON for large evidence-heavy inputs on both token cost and citation validity (structured prompt format benchmark). The practical takeaway: send a compact table with a citation column, not a pretty-printed JSON array with the same field names repeated on every record.

The schema is the contract. Every field exists because a reviewer will need it. Here is a schema that covers the ordinary cases:

The favourability field is not decoration. A positive variance on revenue is good news and a positive variance on cost is bad news, and a column of plus and minus signs invites exactly that confusion in a management meeting (Smartbooks on variance analysis). Forcing an explicit F or U label in the schema means the model cannot hedge by writing "favorable" in one row and "unfavorable" for the same sign in the next.

The arithmetic field is the one people skip. Do not. A prose sentence can be skimmed and believed. A string that says 120.00 - 100.00 = 20.00 can be split on the operator and recomputed by twelve lines of Python. That difference is the entire audit trail.

Take a utilities line where the ledger booked 120.00 and the plan assumed 100.00.

The weak draft: "Utilities are overstated by about 20 versus plan, likely due to higher rates."

The auditable draft, following the schema:

Ledger row 4521 (Utilities, 120.00) vs plan row 889 (Utilities, 100.00):
difference 20.00 (120.00 - 100.00). Favourability: U. Match type: one_to_one.

The second version is shorter, not longer, and a reviewer can settle it in ten seconds. They open row 4521, see 120.00, open plan row 889, see 100.00, and the arithmetic is already written down. No interpretation is required to check the fact. The interpretation ("likely due to higher rates") has been removed entirely, because the model has no citation for it. That removal is a feature: root cause belongs to a human or to a system that can cite a contract or a rate change, not to a model guessing.

Scale this to a monthly close. If you have 40 material variances and each one carries four checkable fields, a reviewer is checking 160 facts against source rather than reading 40 paragraphs and trusting them. That is the difference between a draft that saves an afternoon and a draft that creates review work.

Real reconciliations are not clean pairs. Four or five ledger rows may map to a single plan row, and some rows map to nothing at all. A schema with one ledger ID and one plan ID per line will force the model to pick arbitrarily or to leave one side blank with no explanation.

Extend the schema rather than bending it:

The many-to-one case is where a reconciliation draft earns or loses trust. An aggregate line that says "Marketing is 640 over plan" is not auditable. A block that lists four ledger rows, each citing the same plan row, with a summation line whose arithmetic names those four IDs, is. In the prompt, state the summation rule explicitly: the summary difference must equal the sum of the member ledger amounts minus the plan amount, and the arithmetic field must list the member IDs.

For unmatched items, the useful output is a short, citation-bearing statement of absence. "Ledger row 5120 (Travel, 340.00) has no plan row; searched plan_id column, no account match" is checkable: a reviewer can confirm both that the row exists and that the search was appropriate. "Several travel costs appear unbudgeted" is not.

The prompt is only half the system. The other half is a script that treats the model's output as a set of claims to be tested against the same CSV files the model was given.

The checks are mechanical:

1. Every non-UNMATCHED ledger_row_id exists in ledger.csv.
2. Every non-UNMATCHED plan_row_id exists in plan.csv.
3. ledger_amount and plan_amount equal the amounts in those rows, to the cent.
4. Parsing the arithmetic string and recomputing it yields the stated difference.
5. The account on the ledger row matches the account on the plan row, or match_type is not one_to_one.
6. The sum of differences across all lines equals any total the output claims.

Any line that fails goes back as a rejection with the specific failure, not as a general retry. A model told "row 4521 exists but its amount is 118.50, not 120.00" corrects quickly. A model told "please check your work" will rewrite something plausible and possibly wrong again.

Keep a standing instruction in the prompt that anticipates this loop: any line that fails verification is rejected in full, and the model must re-derive it from the rows rather than paraphrase the rejected version. This is the same discipline that finance prompt frameworks recommend when they tell you to define verification steps inside the prompt and to validate calculations before use (AFP on finance prompts). It also reflects the practical lesson from reconciliation engineering: the model is best treated as a writer of draft commentary sitting on top of a deterministic matcher, never as the matcher itself (Amit Mishra).

A minimal check looks like this:

import csv, re

ledger = {r["row_id"]: r for r in csv.DictReader(open("ledger.csv"))}
plan = {r["plan_id"]: r for r in csv.DictReader(open("plan.csv"))}

def verify(line):
    lid, pid = line["ledger_row_id"], line["plan_row_id"]
    if lid != "UNMATCHED":
        row = ledger[lid]
        if abs(float(row["amount"]) - float(line["ledger_amount"])) > 0.005:
            return "amount mismatch on ledger row " + lid
    if pid != "UNMATCHED":
        row = plan[pid]
        if abs(float(row["amount"]) - float(line["plan_amount"])) > 0.005:
            return "amount mismatch on plan row " + pid
    left, right = line["arithmetic"].split("=")[0].split("-")
    if abs((float(left) - float(right)) - float(line["difference"])) > 0.005:
        return "arithmetic does not reconcile"
    return None

The point is not the code, it is the posture. Every claim the model makes about a number is a testable assertion, and the system tests all of them before a human reads the draft. Humans then read only the lines that passed, plus the list of failures, which is where their judgment is actually worth spending.

On that sixth row: a threshold matters for the same reason it matters in human commentary. Agreeing to explain only variances above, say, a stated floor in currency and percentage keeps attention on the lines that drive the result rather than twenty small differences (Smartbooks). State the threshold as a rule in the prompt and let the model mark everything below it as "below threshold, no commentary required" with its rows still cited. You keep the audit trail without drowning in it.

The same discipline applies when the model drafts the written commentary that sits above the numbers. Good variance narrative states what happened, why, the forward impact and the action, in that order, and a decomposition is challengeable where a vague explanation is not (Planir on variance commentary). Row citations let you keep the good structure while making every "why" accountable to a specific transaction, which is also how modern planning tools describe their own traceability (Cube on variance analysis).

Once the prompt and verifier exist, running the same job across every model you want to trial is mostly plumbing. Quanta Inference puts text, image, video, audio and embedding models behind one OpenAI-compatible API key with prepaid US dollar pricing, so you can point the same reconciliation prompt and the same verifier at more than one model and compare citation pass rates on your own data (https://quantaintelligence.ai/inference).