Check Supplier Invoices Against Contract Prices

Suppliers make mistakes on invoices, and few of them make mistakes in the customer's favour. A price that should have dropped when a new contract started is still the old one; a quantity is invoiced that was never delivered; a line total does not equal quantity times price. Individually the amounts are small, but across thousands of invoice lines a year they add up, and once an invoice is paid the money is much harder to get back. Accounts payable teams check what they can by eye; a script can check every line. This page builds the check with pandas: the contract price in force on each invoice date, found with an as-of join on a dated price list, compared with the invoiced price within a tolerance; the quantity invoiced compared with the quantity received; the arithmetic of each line and each invoice total; and a list of invoices to hold, with the reason and the value to query, for the payables team. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Four inputs: the contract price list with the date each price took effect; invoice lines with quantity, unit price and line total; invoice headers with the total the supplier charged; and goods received against each invoice. Two suppliers, five invoices, ten lines.

Step 1 — The contract price on the invoice date

import pandas as pd

contract = pd.read_excel("contract_prices.xlsx", parse_dates=["Effective from"]).sort_values("Effective from")
lines = pd.read_excel("invoice_lines.xlsx", parse_dates=["Invoice date"]).sort_values("Invoice date")

checked = pd.merge_asof(lines, contract, left_on="Invoice date", right_on="Effective from",
                        by=["Supplier", "Item"], direction="backward")
assert checked["Contract price"].notna().all(), "an invoiced item has no contract price"
print(checked[["Invoice", "Invoice date", "Item", "Unit price", "Contract price", "Effective from"]].to_string(index=False))

Contract prices change over time — the cable CB-10 dropped from 4.20 to 3.95 on 1 July, the panel PL-05 rose from 12.40 to 12.90 on 1 September — so the right price for an invoice line is the one in force on the invoice date. merge_asof does exactly that: for each invoice line it takes the most recent contract price, for the same supplier and item, whose effective date is on or before the invoice date. Both tables must be sorted by date for the join to work. An item invoiced without any contract price stops the check, because there is nothing to compare it with — either the item is not on contract, which payables should know about, or its code differs between the two systems. The general technique is covered in Join Rows to Effective Date Ranges with pandas.

Step 2 — Price, quantity and arithmetic checks on each line

received = pd.read_excel("goods_received.xlsx")
checked = checked.merge(received, on=["Supplier", "Invoice", "Item"], how="left")

TOL = 0.005                                                  # accept prices within 0.5% of contract (rounding)
checked["Price diff"] = checked["Unit price"] - checked["Contract price"]
checked["Price issue"] = checked["Price diff"] > checked["Contract price"] * TOL
checked["Qty issue"] = checked["Qty"] > checked["Qty received"].fillna(0)
checked["Maths issue"] = (checked["Qty"] * checked["Unit price"] - checked["Line total"]).abs() > 0.01

checked["Overcharge"] = (
    checked["Price diff"].clip(lower=0) * checked["Qty"] * checked["Price issue"]
    + (checked["Qty"] - checked["Qty received"].fillna(0)).clip(lower=0) * checked["Contract price"]
    + (checked["Line total"] - checked["Qty"] * checked["Unit price"]).clip(lower=0)
).round(2)
issues = checked[checked[["Price issue", "Qty issue", "Maths issue"]].any(axis=1)]
print(issues[["Invoice", "Item", "Qty", "Qty received", "Unit price", "Contract price", "Line total", "Overcharge"]].to_string(index=False))

Three checks run on each line. The price check compares the invoiced unit price with the contract price, accepting a small tolerance for rounding — half a per cent here — and ignoring prices below contract, which are in the buyer's favour. The quantity check is the second leg of a three-way match: the quantity invoiced must not exceed the quantity received against that invoice. The arithmetic check confirms that the line total equals quantity times unit price to the penny. Each failure is valued: the excess price times the quantity, the contract price of units invoiced but not received, and any amount by which the line total exceeds the arithmetic. That turns a list of exceptions into a figure the payables team can take to the supplier.

In this sample, invoice A-7802 charges the old cable price of 4.20 six weeks after it dropped to 3.95 — £100 over on 400 units. Invoice A-7840 has three problems: a price of 7.84 on an item contracted at 7.80, outside the tolerance; 1,500 gloves invoiced against 1,200 received; and a glove line total of £1,560 where 1,500 × £1.10 is £1,650 — an arithmetic slip in the buyer's favour, which is not valued as an overcharge but still means the invoice needs correcting. The NORD invoices look fine at first glance, because 12.90 is the current panel price — but the check shows otherwise: the August invoice uses 12.90, and that price only took effect on 1 September, so the August invoice is overcharged by 50p on each of its 60 panels. Spotting a price applied a week or two early is exactly what an eye scanning invoices against today's price list cannot do.

Step 3 — Invoice totals and duplicates

headers = pd.read_excel("invoice_headers.xlsx")
sums = checked.groupby("Invoice")["Line total"].sum().round(2).rename("Sum of lines")
totals = headers.set_index("Invoice").join(sums)
totals["Total issue"] = (totals["Invoice total"] - totals["Sum of lines"]).abs() > 0.01
dupes = lines.groupby(["Supplier", "Invoice"]).size().index.to_frame(index=False).duplicated(subset=["Invoice"], keep=False)
print(totals.to_string())
print("duplicate invoice numbers across suppliers:", int(dupes.sum()))

The invoice total the supplier asks to be paid must equal the sum of its lines; a mismatch means a line was missing from the data, a charge was added at header level, or the total was simply typed wrong. Four of the five invoices add up. The fifth, N-3352, asks for £2,152 when its two lines come to £2,052 — every line on it passed the price and quantity checks, so without this check the extra £100 would have been paid. A duplicate invoice number from the same supplier is the classic way an invoice gets paid twice, and is worth checking against the whole payment history, not just this batch, as in Detect Possible Duplicate Invoices.

Step 4 — The hold list for payables

def reasons(g):
    r = []
    if g["Price issue"].any(): r.append("price above contract")
    if g["Qty issue"].any(): r.append("quantity above received")
    if g["Maths issue"].any(): r.append("line arithmetic")
    return "; ".join(r)

hold = (checked.groupby(["Supplier", "Invoice"])
        .apply(lambda g: pd.Series({"Reason": reasons(g), "Amount to query": g["Overcharge"].sum()}), include_groups=False)
        .reset_index().join(totals, on="Invoice"))
extra = (hold["Invoice total"] - hold["Sum of lines"]).clip(lower=0)
hold.loc[hold["Total issue"], "Reason"] = (hold.loc[hold["Total issue"], "Reason"] + "; invoice total ≠ lines").str.strip("; ")
hold["Amount to query"] = (hold["Amount to query"] + extra).round(2)
hold = hold[hold["Reason"] != ""][["Supplier", "Invoice", "Reason", "Amount to query", "Invoice total"]]
with pd.ExcelWriter("invoice_checks_2026-09.xlsx") as xw:
    hold.to_excel(xw, sheet_name="Hold", index=False)
    issues.to_excel(xw, sheet_name="Failed lines", index=False)
    checked.to_excel(xw, sheet_name="All lines", index=False)
print(hold.to_string(index=False))
print("total to query: £", hold["Amount to query"].sum())

The hold list has one row per invoice that failed any check: supplier, invoice number, the reasons in plain words, the amount to query and the invoice total. Payables can release everything not on the list for payment and send the rest back to the supplier with the detail from the failed-lines sheet, which shows each problem line next to the contract price and the quantity received. Here four of the five invoices are held, with £570 to query in total — small for one batch, but a steady stream of such errors across a year is real money, and suppliers that know every line is checked tend to make fewer of them.

Common pitfalls

• Comparing with today's contract price. Use the price in force on the invoice date.
• Unsorted tables in merge_asof. Both must be sorted by the date columns.
• No tolerance. Rounding differences of a fraction of a penny should not hold an invoice.
• Price checks only. Check quantity against goods received and the arithmetic too.
• Exceptions without a value. Put a figure on every failure so payables can prioritise.

Conclusion

To check supplier invoices before payment, join each line to the contract price in force on the invoice date with merge_asof, compare the unit price with it within a small tolerance, compare the quantity with the quantity received, check line arithmetic and invoice totals, and value each failure. Grouping the failures by invoice gives a hold list with reasons and an amount to query, so payables can pay the clean invoices and challenge the rest.

Frequently asked questions

How do I find the contract price on the invoice date in pandas? Sort both tables by date and use pd.merge_asof(lines, prices, left_on="Invoice date", right_on="Effective from", by=["Supplier", "Item"]).

What is a three-way match? Checking that the purchase order, the goods received and the invoice agree on item, quantity and price before paying.

Why use a tolerance when comparing invoice prices? Unit prices are often rounded differently by the supplier's system; a small tolerance avoids holding invoices for fractions of a penny.

How do I value an invoice overcharge? Multiply the excess unit price by the quantity, add the contract price of any units invoiced but not received, and add any excess in the line total.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Join Rows to Effective Date Ranges with pandas — the as-of join.
• Update a Price List with Effective Dates — keeping the contract prices.
• Build a Supplier Scorecard — price variance as part of supplier performance.