Reconcile Excel Totals Against Control Totals with Python

Finance teams do not trust a report because the code looks right; they trust it because the totals agree. When a system exports invoices to Excel, it can also provide control totals — the number of records and the sum of the amounts — either as rows at the bottom of the sheet or in a separate file. Recomputing them after the import proves that nothing was lost or duplicated, and reconciling again after each processing step shows exactly where a figure went astray. This guide, part of Validating Excel Data with Python, builds those checks with pandas.

Prerequisites

pip install pandas openpyxl

An invoice export with control rows, and a separate file of per-branch controls:

import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["invoice", "branch", "amount"])
for row in [("INV-001", "Leeds", 1200.10), ("INV-002", "Leeds", 349.90), ("INV-003", "York", 80.00),
            ("INV-004", "York", 15.25), ("INV-005", "Hull", 410.00)]:
    ws.append(row)
ws.append([])
ws.append(["CONTROL", "records", 5])
ws.append(["CONTROL", "amount", 2055.25])
wb.save("invoices.xlsx")

wb = Workbook()
ws = wb.active
ws.append(["branch", "records", "amount"])
for row in [("Leeds", 2, 1550.00), ("York", 2, 95.25), ("Hull", 1, 410.00)]:
    ws.append(row)
wb.save("branch_controls.xlsx")

Step 1 — Separate the control rows from the data

Control rows share the sheet with the data, so they must be taken out before any calculation — otherwise the control amount is summed into the total it is meant to check:

raw = pd.read_excel("invoices.xlsx")
is_control = raw["invoice"].eq("CONTROL")
controls = raw.loc[is_control].set_index("branch")["amount"]
data = raw.loc[~is_control & raw["invoice"].notna()].copy()
print(controls.to_dict())          # {'records': 5.0, 'amount': 2055.25}
print(len(data))                   # 5

The blank separator row is dropped with the notna filter. If controls come in a trailer file or an email instead, read them from there; the checks that follow are the same. Removing total rows that are not meant as controls — subtotals in a report layout — is covered in Remove Subtotal and Total Rows from an Excel Export.

Step 2 — Compare counts and amounts exactly

Compare the record count directly and the amount in integer cents. Summing floats and testing for equality is fragile in both directions: a correct file can fail by a rounding fraction, and a tolerance loose enough to absorb that can hide a genuine one-cent error:

def cents(value):
    return int(round(float(value) * 100))

actual_count = len(data)
actual_cents = sum(cents(v) for v in data["amount"])
checks = {
    "records": (int(controls["records"]), actual_count),
    "amount_cents": (cents(controls["amount"]), actual_cents),
}
for name, (expected, actual) in checks.items():
    print(f"{name}: expected {expected}, actual {actual}, {'OK' if expected == actual else 'MISMATCH'}")
# records: expected 5, actual 5, OK
# amount_cents: expected 205525, actual 205525, OK

Converting each value to cents before summing — rather than summing floats and converting the result — keeps every addition exact. For currencies with other minor units, or amounts with more than two decimals, use decimal.Decimal with the precision the source system uses.

Step 3 — Reconcile per group

A matching grand total can hide two offsetting errors: 100 too much in Leeds and 100 too little in York. Per-group controls catch that. Aggregate the data the same way and compare with an outer merge, so a branch missing from either side shows up too:

branch_controls = pd.read_excel("branch_controls.xlsx")
actual = data.groupby("branch").agg(records=("invoice", "size"), amount=("amount", "sum")).reset_index()
recon = branch_controls.merge(actual, on="branch", how="outer",
                              suffixes=("_control", "_actual"), indicator=True)
recon["records_diff"] = recon["records_actual"] - recon["records_control"]
recon["amount_diff"] = (recon["amount_actual"] - recon["amount_control"]).round(2)
print(recon[["branch", "records_diff", "amount_diff", "_merge"]].values.tolist())
# [['Hull', 0, 0.0, 'both'], ['Leeds', 0, 0.0, 'both'], ['York', 0, 0.0, 'both']]

Every branch reconciles. A branch that appears only in the controls means its rows never arrived; one that appears only in the data means rows were assigned to a branch the source does not know. The differences are rounded to cents for display; the pass/fail decision should still use the exact cent comparison from Step 2.

Step 4 — Reconcile at every stage

Controls prove the import. Every later step that can change rows — filters, merges, deduplication — needs the same discipline: for each stage, input must equal output plus rejected. Keep a small ledger as the pipeline runs:

ledger = []

def stage(name, before, after, rejected):
    ledger.append({"stage": name, "in": len(before), "out": len(after), "rejected": len(rejected),
                   "in_cents": sum(map(cents, before["amount"])),
                   "out_cents": sum(map(cents, after["amount"])),
                   "rejected_cents": sum(map(cents, rejected["amount"]))})

keep = data["amount"] >= 20
valid, small = data[keep], data[~keep]
stage("drop amounts under 20", data, valid, small)

report = pd.DataFrame(ledger)
report["balanced"] = (report["in"] == report["out"] + report["rejected"]) & \
                     (report["in_cents"] == report["out_cents"] + report["rejected_cents"])
print(report[["stage", "in", "out", "rejected", "balanced"]].values.tolist())
# [['drop amounts under 20', 5, 4, 1, True]]

The ledger turns "the report total is lower than the export" into "stage 2 rejected one invoice worth 15.25", which is an answer rather than an investigation. Merges deserve the same treatment; the checks for them are in Validate a pandas Merge to Avoid Duplicate Rows.

Step 5 — Add a hash total

Counts and amounts cannot detect a record replaced by another with the same amount — INV-003 swapped for INV-033, both 80.00. A hash total can. If the source system provides one, recompute it the same way; if not, compute one yourself at import and carry it through the pipeline:

import hashlib

def hash_total(ids):
    return sum(int("".join(filter(str.isdigit, i))) for i in ids)

def id_digest(ids):
    return hashlib.sha256("\n".join(sorted(ids)).encode()).hexdigest()[:12]

print(hash_total(data["invoice"]))            # 15
print(id_digest(data["invoice"]))
swapped = data["invoice"].replace({"INV-003": "INV-033"})
print(hash_total(swapped), id_digest(swapped) != id_digest(data["invoice"]))   # 45 True

The numeric hash total — the sum of the invoice numbers — is the traditional form, easy for a source system to produce. The SHA-256 digest of the sorted IDs is stronger: any change to the set of IDs changes it. Keep the digest in the run log; comparing two runs' digests shows at once whether the same set of records was processed.

Step 6 — Report and stop on a mismatch

Write the reconciliation next to the output so it travels with the report, and stop the run when any check fails:

summary = pd.DataFrame([{"check": k, "expected": e, "actual": a, "ok": e == a} for k, (e, a) in checks.items()])
with pd.ExcelWriter("reconciliation.xlsx") as writer:
    summary.to_excel(writer, sheet_name="Controls", index=False)
    recon.drop(columns="_merge").to_excel(writer, sheet_name="By branch", index=False)
    report.to_excel(writer, sheet_name="Stages", index=False)
if not summary["ok"].all() or not report["balanced"].all():
    raise SystemExit("reconciliation failed - see reconciliation.xlsx")
print("reconciled")

A report that ships with its own reconciliation sheet answers the reader's first question before they ask it. Sending an alert when the check fails is covered in Send an Alert When an Excel Report Job Fails.

Common pitfalls

• Summing the control rows into the data. Separate them first, or the total checks itself.
• Tolerances on money. A tolerance of 0.01 hides one-cent errors. Compare integer cents exactly.
• Controls that describe a different extract. A control total computed before the source applied its own filters — cancelled invoices, test accounts — will never match. Confirm with the system owner exactly which records the controls cover.
• Trusting controls typed by hand. A control total someone copied into the sheet is a second data entry, not a proof. Prefer figures generated by the source system itself.
• Reconciling only the final output. A mismatch at the end tells you something is wrong but not where. Balance every stage.

Conclusion

Control totals turn "the report looks right" into a proof. Separate control rows from the data, compare counts exactly and amounts in integer cents, reconcile per group with an outer merge so missing groups surface, keep a ledger showing that each stage's input equals output plus rejected, and add a hash total or ID digest to catch swapped records. Ship the reconciliation sheet with the report and stop the run when anything fails to balance.

Frequently asked questions

What is a control total? A figure computed by the source system when it produces a file — usually the number of records and the sum of an amount column — and sent with the data. Recomputing it after loading proves that nothing was lost, duplicated or altered on the way.

Why compare money in cents instead of floats? Floating-point sums of decimal amounts can differ from the exact total by a tiny fraction, so an equality test can fail on correct data or a loose tolerance can hide a real one-cent difference. Converting to integer cents makes the comparison exact.

What is a hash total? A sum or checksum of a column that has no business meaning, such as invoice numbers. It catches a record that was swapped for another with the same amount, which a count and amount total would miss.

Where in a pipeline should I reconcile? At every stage that can change row counts — after reading, after filtering, after each merge. Record input, output and rejected counts so that input always equals output plus rejected.

Related

• Up one level: Validating Excel Data with Python — checks before data moves on.
• Compare Two Excel Files for Differences with Python — finding which records differ.
• Match Excel Rounding in pandas — rounding that agrees with the source.
• Add Subtotals and Grand Totals to a pandas Pivot Table — totals that add up by construction.