Picture a business that raises invoices in one system and keeps its books in another, with every invoice meant to flow from the first into the second. At month end the two sales figures disagree, by too little to alarm anyone and too much to sign off.
The usual fix is two exports and an afternoon of lookups, repeated next month by someone who half remembers the last adjustments. A short script does the job the same way every time, and it can do something the afternoon rarely does: prove that it has explained the whole gap.
A total tells you there is a problem, not what it is
A gap between two totals is several smaller disagreements added together, usually of three kinds:
- Missing on one side. An invoice never reached the ledger because the integration failed that night, or a journal went straight into the accounts with no invoice behind it.
- Amount mismatch. Both sides have the entry, with different figures: tax rounded per line in one system and on the total in the other, a late discount, a typing slip.
- Timing. Dated the 30th, posted on the 1st. Nothing is wrong except the calendar, but the month’s totals still move.
Each kind needs a different fix, and only comparing entry by entry shows which ones the gap holds. Audit, where I worked as a senior audit manager on SAP systems, has a plain standard here: a reconciliation is finished when every difference has a reason, not when the totals agree.
Pick a key both sides really share
Matching depends on a field the two systems genuinely have in common. For invoices that is usually the invoice number, which the accounts tend to keep in a reference or narration field, sometimes prefixed, sometimes typed by hand.
Check it before trusting it. A duplicate invoice number on the invoicing side is a finding in its own right, so report it instead of letting the merge multiply rows. An invoice with two tax rates is often posted as several ledger lines, so total those per invoice first. With no shared key at all, customer plus date plus amount can work, but list those matches separately as weaker.
Normalise before you compare
Most differences on a first run are formatting: INV-00417 against inv00417, amounts exported as text with separators, dates day first in one system and year first in the other, credit notes negative in one place and flagged in another.
Fix each one in code, never by hand-editing an export, because next month’s will need the same edits. Work in whole paise (or cents) so equal amounts compare as equal. Filter the ledger to the account the invoices post to. And export a few days either side of the month from both systems, then keep only the differences that touch the month you are closing: cut the window exactly at month end and every timing difference looks like a missing entry.
Compare, then classify
The comparison itself is an outer merge that keeps every key from either side. indicator=True adds a _merge column saying left_only, right_only or both, which is most of the classification done for you.
import pandas as pd
inv = pd.read_csv("invoicing.csv", dtype=str)
gl = pd.read_csv("ledger.csv", dtype=str) # the sales account only
def key(s):
# " inv-00417" and "INV00417" become the same key
return s.str.upper().str.replace(r"[^A-Z0-9]", "", regex=True)
def paise(s):
# "1,41,600.00" becomes 14160000: whole numbers compare exactly
return (s.str.replace(",", "").astype(float) * 100).round().astype(int)
inv["key"] = key(inv["invoice_no"])
inv["amt"] = paise(inv["total"])
inv["date"] = pd.to_datetime(inv["invoice_date"], format="%d/%m/%Y")
gl["key"] = key(gl["reference"])
gl["amt"] = paise(gl["amount"])
gl["date"] = pd.to_datetime(gl["posting_date"], format="%Y-%m-%d")
assert inv["key"].is_unique, "duplicate invoice numbers: report those first"
gap = inv["amt"].sum() - gl["amt"].sum()
# One invoice may be posted as several ledger lines
gl = gl.groupby("key", as_index=False).agg(amt=("amt", "sum"), date=("date", "max"))
m = inv[["key", "customer", "amt", "date"]].merge(
gl, on="key", how="outer", suffixes=("_inv", "_gl"), indicator=True)
def classify(r):
if r["_merge"] == "left_only":
return "In invoicing, not in the accounts"
if r["_merge"] == "right_only":
return "In the accounts, not in invoicing"
if r["amt_inv"] != r["amt_gl"]:
return "Amount differs"
if r["date_inv"].to_period("M") != r["date_gl"].to_period("M"):
return "Timing: posted in another month"
return "Matched"
m["status"] = m.apply(classify, axis=1)
m["diff"] = m["amt_inv"].fillna(0) - m["amt_gl"].fillna(0)
# The differences must explain the whole gap, to the paisa
assert m["diff"].sum() == gap, "something was dropped or counted twice"
out = m[m["status"] != "Matched"].drop(columns="_merge")
out[["amt_inv", "amt_gl", "diff"]] /= 100 # back to rupees for people
out.sort_values("diff", key=abs, ascending=False).to_csv("differences.csv", index=False)
print(m["status"].value_counts())The second assertion is the line I would never leave out. Each difference is invoicing minus ledger, with a missing side counting as zero, so together they must equal the gap between the original totals. If they do not, something was lost or counted twice, and the output should reach nobody. A ledger line with a blank reference is the classic case: grouping by key drops it silently, and only this check notices.
A list someone can act on
right_only means nothing to the person in finance who has to act on the result. Give them a worklist instead:
- One line at the top: both totals, the gap, and how much of it each category explains.
- Categories in plain words, each with its usual action. Post the missing invoice, find the document behind the stray journal, check the tax on the mismatches. Timing usually needs only a cut-off note.
- Enough on each row to find the document: the invoice number as the business writes it, date, customer, both amounts, the difference.
- Largest first, with an empty notes column, so the list becomes a record of who looked at what.
Finance would usually rather read it as an Excel workbook, one sheet per category, which pandas writes easily.
The second month is the point
The first run is noisy, mostly with formatting the script has not learned to normalise. Teach it in the script, not the spreadsheet, and the list shrinks each month until every line on it is real. A missing invoice found on the third of the month is a phone call. Found at year end, it is an adjustment, an explanation and an awkward meeting. If two of your systems have never quite agreed, tell me about them.
