Comparing and Reconciling Spreadsheets with Python

Two spreadsheets that should agree almost never do, and finding out why is a weekly chore in finance, operations and IT administration. This month's price list against last month's. The payroll export against the HR system. The bank statement against the ledger. Stock counts against the warehouse system. Done by eye, the comparison takes hours, misses the row that moved, and cannot be repeated consistently. Done with a naive Python merge, it produces thousands of "differences" that are nothing of the kind: 00417 versus 417, ACME Ltd versus ACME Ltd , 1204.5 versus 1204.50000001, a date stored as text in one file and as a real date in the other.

Comparison and reconciliation are related but distinct. Comparison looks at two versions of the same dataset keyed the same way and reports added, removed and changed records. Reconciliation matches records between two different systems that do not share a clean key, using rules — same amount and a date within three days, say — and reports what could not be matched. Both depend on the same foundation: normalise both sides into comparable form before the join, make every rule explicit, and produce an exception report a person can act on. This guide builds that foundation with pandas and writes the result to Excel.

Prerequisites

python -m venv .venv && source .venv/bin/activate
pip install "pandas>=2.2" "openpyxl>=3.1" xlsxwriter
mkdir -p in out

pandas does the normalising, joining and comparing; openpyxl and xlsxwriter write a reviewer-friendly exception workbook. Reading the inputs correctly is half the battle — if either side is an Excel file with header offsets, merged cells or dates stored as serial numbers, fix that first with Reading Excel Files with Python and fix Excel dates showing as numbers in pandas.

Diagnostic: Profile the Keys on Both Sides

A comparison is only as good as its key. Check, for each side, whether the key is unique, whether it has blanks, and how many keys match before and after obvious normalisation. The gap between those two numbers is how much noise a naive comparison would report.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

OLD = Path("in/price-list-2026-08.xlsx")
NEW = Path("in/price-list-2026-09.xlsx")
KEY = "sku"

def load(path: Path) -> pd.DataFrame:
    try:
        return pd.read_excel(path, dtype={KEY: "string"})     # never let keys become numbers
    except (OSError, ValueError) as exc:
        raise SystemExit(f"cannot read {path}: {exc}")

def normalise_key(series: pd.Series) -> pd.Series:
    return (series.astype("string").str.strip().str.upper()
                  .str.replace(r"\s+", "", regex=True).str.lstrip("0"))

def key_profile(old: pd.DataFrame, new: pd.DataFrame) -> None:
    for name, frame in (("old", old), ("new", new)):
        k = frame[KEY]
        print(f"{name}: {len(frame)} rows, {k.isna().sum()} blank keys, "
              f"{k.duplicated(keep=False).sum()} rows with duplicate keys")
    raw = len(set(old[KEY].dropna()) & set(new[KEY].dropna()))
    norm = len(set(normalise_key(old[KEY]).dropna()) & set(normalise_key(new[KEY]).dropna()))
    print(f"matching keys: raw {raw}, normalised {norm} (+{norm - raw} false differences avoided)")

if __name__ == "__main__":
    key_profile(load(OLD), load(NEW))

old: 4812 rows, 0 blank keys, 0 rows with duplicate keys
new: 4839 rows, 3 blank keys, 4 rows with duplicate keys
matching keys: raw 4211, normalised 4796 (+585 false differences avoided)

Five hundred and eighty-five SKUs would have been reported as "removed" and "added" purely because one export trims leading zeros. The blank and duplicate keys need a decision before comparing — they cannot be matched one-to-one. Fix merge indicator unexpected left_only rows covers every way keys fail to match.

Core Implementation

Step 1: Normalise Both Sides Identically

Apply exactly the same function to both inputs. Normalise the key and every column you intend to compare, and convert types deliberately: money to Decimal-safe rounded floats, dates to real dates, text to trimmed, case-folded strings.

# pip install "pandas>=2.2"
import pandas as pd

TEXT_COLS = ["description", "category"]
MONEY_COLS = ["unit_price"]
DATE_COLS = ["valid_from"]

def normalise(frame: pd.DataFrame, key: str) -> pd.DataFrame:
    out = frame.copy()
    out[key] = (out[key].astype("string").str.strip().str.upper()
                        .str.replace(r"\s+", "", regex=True).str.lstrip("0"))
    for col in TEXT_COLS:
        out[col] = (out[col].astype("string").str.strip()
                            .str.replace(r"\s+", " ", regex=True))
    for col in MONEY_COLS:
        cleaned = out[col].astype("string").str.replace(r"[£$€,\s]", "", regex=True)
        out[col] = pd.to_numeric(cleaned, errors="coerce").round(2)
    for col in DATE_COLS:
        out[col] = pd.to_datetime(out[col], errors="coerce", dayfirst=True).dt.normalize()
    return out

errors="coerce" turns unparseable values into NaN/NaT rather than crashing, which is right for comparison — but count them, because a column that silently becomes all-NaN compares as "unchanged" everywhere. Text is compared case-sensitively after trimming here; case-fold it too if a change from Red to RED should not count.

Step 2: Separate Keys That Cannot Be Compared

# pip install "pandas>=2.2"
import pandas as pd

def split_unkeyable(frame: pd.DataFrame, key: str) -> tuple[pd.DataFrame, pd.DataFrame]:
    blank = frame[key].isna() | (frame[key] == "")
    dupes = frame[key].duplicated(keep=False) & ~blank
    bad = frame[blank | dupes].assign(problem=lambda d: d[key].isna().map(
        {True: "blank key", False: "duplicate key"}))
    return frame[~(blank | dupes)], bad

Rows with duplicate keys cannot be paired unambiguously; merging them anyway multiplies rows — two old rows against two new rows produce four comparisons, three of them nonsense. Report them separately for a human to resolve, or de-duplicate by an explicit rule (latest valid_from wins) if the business has one.

Step 3: Outer Merge with an Indicator

# pip install "pandas>=2.2"
import pandas as pd

def classify(old: pd.DataFrame, new: pd.DataFrame, key: str) -> dict[str, pd.DataFrame]:
    merged = old.merge(new, on=key, how="outer", suffixes=("_old", "_new"),
                       indicator=True, validate="one_to_one")
    return {
        "removed": merged[merged["_merge"] == "left_only"],
        "added": merged[merged["_merge"] == "right_only"],
        "common": merged[merged["_merge"] == "both"],
    }

validate="one_to_one" makes pandas raise MergeError if duplicate keys slipped past step 2 — a cheap guard that turns a silent row explosion into an immediate, explicit failure.

Step 4: Compare Values with Tolerances

# pip install "pandas>=2.2"
import numpy as np
import pandas as pd

TOLERANCE = {"unit_price": 0.005}                      # half a cent

def changed_cells(common: pd.DataFrame, key: str, columns: list[str]) -> pd.DataFrame:
    records = []
    for col in columns:
        a, b = common[f"{col}_old"], common[f"{col}_new"]
        if col in TOLERANCE:
            differs = ~np.isclose(a.astype(float), b.astype(float),
                                  atol=TOLERANCE[col], equal_nan=True)
        else:
            differs = ~((a == b) | (a.isna() & b.isna()))
        for _, row in common[differs.fillna(True)].iterrows():
            records.append({key: row[key], "column": col,
                            "old": row[f"{col}_old"], "new": row[f"{col}_new"]})
    return pd.DataFrame(records, columns=[key, "column", "old", "new"])

A long "one row per changed cell" format is far easier to review than a wide sheet where the changed cell is somewhere in 30 columns. It also filters and pivots naturally in Excel: reviewers can filter to column = unit_price and sort by the size of the change. isna() & isna() treats two blanks as equal, which pandas' == does not.

Step 5: Write an Exception Workbook

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def write_exceptions(dest: Path, outcomes: dict[str, pd.DataFrame], changes: pd.DataFrame,
                     unkeyable: pd.DataFrame, counts: dict[str, int]) -> None:
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        pd.DataFrame([counts]).T.rename(columns={0: "rows"}).to_excel(writer, sheet_name="Summary")
        changes.to_excel(writer, sheet_name="Changed", index=False)
        for name in ("added", "removed"):
            outcomes[name].drop(columns="_merge").to_excel(writer, sheet_name=name.title(), index=False)
        unkeyable.to_excel(writer, sheet_name="Needs review", index=False)
        for ws in writer.sheets.values():
            ws.freeze_panes(1, 0)
            ws.autofilter(0, 0, 0, 10)

The summary sheet is the part people read first: counts per outcome, and — for reconciliation — totals per outcome, so "£1,240 unmatched" is visible before anyone scrolls.

Edge Cases and Variants

Reconciling Two Systems Without a Shared Key

Bank statements and ledgers rarely share an ID. Match in passes from strictest to loosest: exact reference, then same amount and same date, then same amount within a date window, and remove matched rows from the pool after each pass so no record matches twice. The full procedure, including many-to-one matches where one bank payment settles several invoices, is in reconcile a bank statement CSV with a ledger.

Comparing Whole Workbooks, Sheet by Sheet

Version comparisons of workbooks with several sheets — a budget model, a configuration spreadsheet — need to compare each sheet and also report sheets that were added or removed. Find differences between two Excel files extends the pipeline to that case and highlights changed cells directly in a copy of the new workbook.

Row Order Changed but Content Did Not

If a file has no key at all, comparing by position flags everything after an inserted row. Build a surrogate key from a hash of the normalised row contents and compare the multisets of hashes; that reports inserted and deleted rows precisely, though it cannot distinguish a changed row from a delete plus an insert:

# pip install "pandas>=2.2"
import pandas as pd

def row_hashes(frame: pd.DataFrame, columns: list[str]) -> pd.Series:
    return pd.util.hash_pandas_object(frame[columns].astype("string").fillna(""), index=False)

def keyless_diff(old: pd.DataFrame, new: pd.DataFrame, columns: list[str]) -> tuple[pd.DataFrame, pd.DataFrame]:
    old_h, new_h = row_hashes(old, columns), row_hashes(new, columns)
    only_old = old[~old_h.isin(set(new_h))]
    only_new = new[~new_h.isin(set(old_h))]
    return only_old, only_new

Tracking Exceptions Over Time

A single comparison answers "what changed this week". A history of comparisons answers the more useful question: is the number of exceptions growing, and are the same records failing every time? Append each run's counts to a small log, and carry forward the keys of unresolved exceptions so reviewers see which items are new and which are stale.

# pip install "pandas>=2.2"
from datetime import date
from pathlib import Path
import pandas as pd

HISTORY = Path("out/comparison-history.csv")
OPEN_ITEMS = Path("out/open-exceptions.csv")

def record_run(run_date: date, counts: dict[str, int], exceptions: pd.DataFrame, key: str) -> pd.DataFrame:
    """Append counts to history; mark each exception as new or carried over from earlier runs."""
    row = pd.DataFrame([{"run_date": run_date.isoformat(), **counts}])
    row.to_csv(HISTORY, mode="a", header=not HISTORY.exists(), index=False)

    previous = (pd.read_csv(OPEN_ITEMS, dtype={key: "string"}, parse_dates=["first_seen"])
                if OPEN_ITEMS.exists() else pd.DataFrame(columns=[key, "first_seen"]))
    current = exceptions[[key]].drop_duplicates().merge(previous, on=key, how="left")
    current["first_seen"] = current["first_seen"].fillna(pd.Timestamp(run_date))
    current["age_days"] = (pd.Timestamp(run_date) - current["first_seen"]).dt.days
    current.to_csv(OPEN_ITEMS, index=False)                # resolved items drop out automatically
    return current.sort_values("age_days", ascending=False)

Exceptions that disappear from the current run drop out of the open-items file without any explicit "resolve" step, because the file is rebuilt from what is still failing. Sorting by age puts long-standing problems at the top of the review sheet, which is usually where the real process issues are — a SKU that has been "removed then re-added" for eight weeks points at a broken export, not at eight separate price changes. Wire the job into a schedule with logging, as in Scheduling and Logging Automation Jobs, and alert only when counts jump well above their recent average rather than on every non-zero result.

Validation

Every input row must end up in exactly one outcome. Assert the accounting identity; if it fails, rows were duplicated or lost somewhere in the pipeline.

# pip install "pandas>=2.2"
import pandas as pd

def assert_accounted(old_rows: int, new_rows: int, outcomes: dict[str, pd.DataFrame],
                     unkeyable_old: int, unkeyable_new: int) -> None:
    common = len(outcomes["common"])
    removed, added = len(outcomes["removed"]), len(outcomes["added"])
    assert old_rows == common + removed + unkeyable_old, (
        f"old side: {old_rows} != {common} common + {removed} removed + {unkeyable_old} unkeyable")
    assert new_rows == common + added + unkeyable_new, (
        f"new side: {new_rows} != {common} common + {added} added + {unkeyable_new} unkeyable")
    print(f"all rows accounted for: {common} common, {removed} removed, {added} added")

For reconciliation, add a monetary identity: the sum of matched amounts plus unmatched amounts on each side must equal that side's total. A reconciliation report whose totals do not tie out is not a reconciliation. Spot-check a handful of "changed" records against the source files by hand the first time the job runs — if the normaliser is too aggressive (stripping a meaningful suffix, say), this is where it shows.

Performance and Scale Notes

pandas merges on string keys handle a few million rows per side comfortably in memory. Normalisation with vectorised .str methods is fast; avoid apply with Python functions on large frames. Converting normalised keys to category dtype before merging reduces memory when keys repeat across many sheets. For datasets too large for memory, push the comparison into DuckDB, which can join two CSV or Parquet files on disk with the same logic expressed in SQL — see query large CSV files with DuckDB. The row-per-changed-cell output can itself be large when an upstream system rewrites a whole column (a new rounding rule changes every price); cap the detail written to Excel and summarise by column when the change count exceeds a threshold.

Troubleshooting

Complete Working Script

#!/usr/bin/env python3
# pip install "pandas>=2.2" openpyxl xlsxwriter
"""Compare two versions of a keyed spreadsheet and write an exception workbook."""
import argparse
import sys
from pathlib import Path

import numpy as np
import pandas as pd


def load(path: Path, key: str) -> pd.DataFrame:
    reader = pd.read_excel if path.suffix.lower() in {".xlsx", ".xlsm", ".xls"} else pd.read_csv
    frame = reader(path, dtype={key: "string"})
    if key not in frame.columns:
        raise ValueError(f"{path.name}: no column {key!r}")
    frame[key] = (frame[key].astype("string").str.strip().str.upper()
                            .str.replace(r"\s+", "", regex=True).str.lstrip("0"))
    for col in frame.columns.drop(key):
        if frame[col].dtype == object or str(frame[col].dtype) == "string":
            frame[col] = frame[col].astype("string").str.strip()
    return frame


def split_bad(frame: pd.DataFrame, key: str):
    bad = frame[key].isna() | (frame[key] == "") | frame[key].duplicated(keep=False)
    return frame[~bad], frame[bad]


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("old", type=Path)
    ap.add_argument("new", type=Path)
    ap.add_argument("--key", required=True)
    ap.add_argument("--out", type=Path, default=Path("out/differences.xlsx"))
    ap.add_argument("--tolerance", type=float, default=0.005)
    args = ap.parse_args()
    try:
        old, bad_old = split_bad(load(args.old, args.key), args.key)
        new, bad_new = split_bad(load(args.new, args.key), args.key)
        compare_cols = [c for c in old.columns if c in new.columns and c != args.key]
        merged = old.merge(new, on=args.key, how="outer", suffixes=("_old", "_new"),
                           indicator=True, validate="one_to_one")
        common = merged[merged["_merge"] == "both"]
        changes = []
        for col in compare_cols:
            a, b = common[f"{col}_old"], common[f"{col}_new"]
            if pd.api.types.is_numeric_dtype(a) and pd.api.types.is_numeric_dtype(b):
                differs = ~np.isclose(a, b, atol=args.tolerance, equal_nan=True)
            else:
                differs = ~((a == b).fillna(False) | (a.isna() & b.isna()))
            for _, row in common[differs].iterrows():
                changes.append({args.key: row[args.key], "column": col,
                                "old": row[f"{col}_old"], "new": row[f"{col}_new"]})
        counts = {
            "added": int((merged["_merge"] == "right_only").sum()),
            "removed": int((merged["_merge"] == "left_only").sum()),
            "changed_cells": len(changes),
            "needs_review": len(bad_old) + len(bad_new),
        }
        args.out.parent.mkdir(parents=True, exist_ok=True)
        with pd.ExcelWriter(args.out, engine="xlsxwriter") as writer:
            pd.Series(counts, name="rows").to_frame().to_excel(writer, sheet_name="Summary")
            pd.DataFrame(changes).to_excel(writer, sheet_name="Changed", index=False)
            merged[merged["_merge"] == "right_only"].to_excel(writer, sheet_name="Added", index=False)
            merged[merged["_merge"] == "left_only"].to_excel(writer, sheet_name="Removed", index=False)
            pd.concat([bad_old.assign(side="old"), bad_new.assign(side="new")]).to_excel(
                writer, sheet_name="Needs review", index=False)
    except (OSError, ValueError, pd.errors.MergeError) as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 1
    print(counts)
    return 0


if __name__ == "__main__":
    sys.exit(main())

Frequently Asked Questions

Is DataFrame.compare enough? Only when both frames already have identical indexes and columns. It does not handle added or removed rows, so it is a helper for the common rows after the merge, not a replacement for the pipeline.

How do I compare only some columns? Pass an explicit list. Exclude columns that change on every export — timestamps, row numbers, "last modified by" — or every row will show as changed.

Can the output highlight changes in the original layout? Yes: copy the new workbook and apply a fill to each changed cell with openpyxl. The walkthrough is in find differences between two Excel files.

How do I compare files from two systems that use different column names? Rename one side to the other's vocabulary with an explicit mapping dictionary before normalising — ledger.rename(columns={"Txn Amount": "amount", "Posting Date": "date"}). Keep the mapping in configuration next to the job, and fail when an expected source column is missing, so an export layout change is reported as such rather than as thousands of differences.

Should reviewers edit the exception workbook? Give them a comment column and read it back on the next run to carry notes forward, but never let edits to the workbook feed the source data directly. The source systems remain the place where fixes are made.

What tolerance should money comparisons use? Half the smallest currency unit — 0.005 for two-decimal currencies — so rounding noise is ignored and a one-cent change is still reported.

Related

• Find Differences Between Two Excel Files — sheet-by-sheet comparison with highlighted cells
• Reconcile a Bank Statement CSV with a Ledger — rule-based matching without a shared key
• Fix Merge Indicator Unexpected left_only Rows — keys that should match but do not
• Merging Multiple Spreadsheets — join types and key alignment in general
• Cleaning Messy CSV Data with pandas — normalisation techniques used before comparing

Part of Python for Excel & CSV Data Processing.