Build a Purchase Order Tracking Report

Once a purchase order is sent, three things have to happen for it to close cleanly: the goods arrive in the right quantity, the supplier invoices for what arrived, and the invoice is paid. Different systems — or different spreadsheets — usually record each step, so the question "where are we with our orders?" means joining three exports and working out, line by line, what is still outstanding. Buyers want to know which deliveries are late. Accounts payable wants invoices that have no matching receipt, which should not be paid yet, and receipts with no invoice, which must be accrued at month end. Finance wants the value of open commitments. This page builds one report that answers all of those from PO, receipt and invoice exports with pandas, line by line, and writes a status workbook with a sheet per audience. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The workbook holds three exports at 30 September 2026: PO lines (order, line, supplier, item, order and promised dates, quantity, unit price), Receipts (goods received against each line, possibly in several deliveries) and Invoices (supplier invoices matched to PO lines). Each export comes from a different place, which is normal.

Step 1 — One row per PO line with received and invoiced quantities

import pandas as pd

TODAY = pd.Timestamp("2026-09-30")
po = pd.read_excel("purchasing.xlsx", sheet_name="PO lines")
rec = pd.read_excel("purchasing.xlsx", sheet_name="Receipts")
inv = pd.read_excel("purchasing.xlsx", sheet_name="Invoices")

key = ["PO", "Line"]
received = rec.groupby(key).agg(**{"Qty received": ("Qty received", "sum"), "Last receipt": ("Received", "max")})
invoiced = inv.groupby(key).agg(**{"Qty invoiced": ("Qty invoiced", "sum"), "Invoiced amount": ("Amount", "sum")})
lines = po.join(received, on=key).join(invoiced, on=key).fillna({"Qty received": 0, "Qty invoiced": 0, "Invoiced amount": 0})
lines["To receive"] = (lines["Qty ordered"] - lines["Qty received"]).clip(lower=0)
lines["Received not invoiced"] = (lines["Qty received"] - lines["Qty invoiced"]).clip(lower=0)
lines["Invoiced not received"] = (lines["Qty invoiced"] - lines["Qty received"]).clip(lower=0)
print(lines[["PO", "Line", "Item", "Qty ordered", "Qty received", "Qty invoiced", "To receive", "Received not invoiced", "Invoiced not received"]].to_string(index=False))

Receipts and invoices are aggregated to one row per PO line before joining, because a line can be delivered in several loads — PO-806 arrived in two halves — and invoiced on several invoices; joining the raw tables would duplicate the order line once per delivery and overstate everything. With quantities side by side, three differences tell the whole story: what is still to arrive, what has arrived but not been invoiced, and what has been invoiced but not received. Lines with no receipts or invoices get zero rather than a missing value, because zero is the truth: nothing has happened yet.

Step 2 — A status per line

def status(r):
    if r["Invoiced not received"] > 0:
        return "Invoice without receipt"
    if r["To receive"] == 0 and r["Received not invoiced"] == 0:
        return "Complete"
    if r["To receive"] == 0:
        return "Received, awaiting invoice"
    late = r["Promised date"] < TODAY
    if r["Qty received"] > 0:
        return "Part received" + (" — late" if late else "")
    return "Late" if late else "Open"

lines["Status"] = lines.apply(status, axis=1)
lines["Days late"] = ((TODAY - lines["Promised date"]).dt.days).where(lines["Status"].str.contains("late|Late"), 0)
lines["Open value"] = lines["To receive"] * lines["Unit price"]
lines["Accrual"] = lines["Received not invoiced"] * lines["Unit price"]
print(lines[["PO", "Line", "Supplier", "Status", "Days late", "Open value", "Accrual"]].to_string(index=False))

The status is decided in order of importance. An invoice for goods that have not arrived comes first, because it must not be paid until they do — this is the three-way match failing. A line fully received and fully invoiced is complete. Fully received but not yet invoiced is waiting for the supplier's invoice. Otherwise something is still to arrive, and the promised date decides whether it is late. Open value is the cost of what is still to come — the outstanding commitment — and Accrual is the cost of goods received but not yet invoiced, which finance books as a liability at month end so the period carries the cost of what it consumed.

Step 3 — A view for each audience

buyers = lines[lines["To receive"] > 0].sort_values(["Days late", "Open value"], ascending=False)[
    ["Supplier", "PO", "Line", "Item", "Promised date", "Days late", "To receive", "Open value", "Status"]]
ap = lines[lines["Status"].isin(["Invoice without receipt", "Received, awaiting invoice"])][
    ["Supplier", "PO", "Line", "Item", "Qty received", "Qty invoiced", "Accrual", "Status"]]
by_supplier = lines.groupby("Supplier").agg(Lines=("Line", "count"), Open_value=("Open value", "sum"),
                                            Late_lines=("Days late", lambda d: int((d > 0).sum())), Accrual=("Accrual", "sum"))
print(buyers.to_string(index=False))
print(ap.to_string(index=False))
print(f"open commitments {lines['Open value'].sum():,.2f} | received not invoiced (accrual) {lines['Accrual'].sum():,.2f}")

Buyers get the lines still to arrive, most overdue first, with what is missing and how late it is: Acme's nuts arrived two thousand short and are fifteen days late, Granite's drill sets were due yesterday and nothing has arrived, and the laptops from Fjord are not yet due. Accounts payable gets the two kinds of exception: Granite's invoice for drill sets that have not arrived, which must be held, and the goods received but not yet invoiced — Corex's boxes and the second half of their tape, and Delta's paper — which make up the month-end accrual of 2,485. Open commitments, the cost of everything still to arrive, come to 8,400. The drill-set line shows zero days late because its invoice exception takes precedence in the status; it still appears on the buyers' sheet, because the goods are still to arrive. The supplier view rolls everything up for supplier reviews. Supplier delivery performance over time is covered in Build an On-Time Delivery Report.

Step 4 — Write the workbook

from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

path = "po_tracking_2026-09-30.xlsx"
with pd.ExcelWriter(path, engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    lines.to_excel(xw, sheet_name="All lines", index=False)
    buyers.to_excel(xw, sheet_name="Buyers - to receive", index=False)
    ap.to_excel(xw, sheet_name="AP - exceptions", index=False)
    by_supplier.to_excel(xw, sheet_name="By supplier")
wb = load_workbook(path)
ws = wb["All lines"]
status_col = [c.value for c in ws[1]].index("Status") + 1
letter = ws.cell(1, status_col).column_letter
area = f"A2:{ws.cell(1, ws.max_column).column_letter}{ws.max_row}"
for text, colour in [("Invoice without receipt", "FFC7CE"), ("late", "FFEB9C"), ("Late", "FFEB9C"), ("Complete", "E2EFDA")]:
    ws.conditional_formatting.add(area, FormulaRule(formula=[f'ISNUMBER(SEARCH("{text}",${letter}2))'], fill=PatternFill("solid", fgColor=colour)))
wb.save(path)
print(wb.sheetnames)

The All lines sheet is the complete picture with rows coloured by status — red for invoices without receipt, amber for anything late, green for complete — using conditional formatting so the colours follow if someone edits a status. Each audience has its own sheet, sorted for the decision it supports. Run daily from the three exports, the report also shows how the picture changes: a late line that disappears from the buyers' sheet has arrived, and an exception that leaves the AP sheet has been resolved.

Keeping the three sources honest

The report is only as good as the joins, and the joins depend on PO and line numbers being recorded consistently in all three systems. The commonest problems are receipts or invoices booked against the wrong line, or without a PO reference at all. Add two checks before building the report: receipts and invoices whose PO and line do not exist in the PO export — the anti-join of each against the PO lines — and lines where more has been received than was ordered, which may be a legitimate over-delivery or a receipt booked to the wrong line. Both lists belong on an exceptions sheet of their own. And because unit prices on invoices can differ from the PO, add a price check to the AP view — invoice amount against invoiced quantity times PO price — so price variances are caught alongside quantity mismatches.

Common pitfalls

• Joining raw receipts and invoices. Aggregate to one row per PO line first.
• Paying invoices without receipts. Flag them and hold payment until goods arrive.
• Missing the accrual. Received-not-invoiced goods are a month-end liability.
• Lateness from order date. Measure against the promised date.
• Ignoring orphan receipts and invoices. List those whose PO line does not exist.

Conclusion

To track purchase orders, aggregate receipts and invoices to one row per PO line, join them to the order lines, and compute quantities still to receive, received but not invoiced, and invoiced but not received. Assign a status in order of importance, value open commitments and the received-not-invoiced accrual, and write a workbook with all lines colour-coded by status plus separate sheets for buyers, accounts payable and supplier reviews.

Frequently asked questions

How do I track open purchase orders in Excel with Python? Join purchase order lines to aggregated receipts and invoices by PO and line number, then compute the quantity still to receive and compare promised dates with today.

What is received not invoiced? Goods that have arrived but for which no supplier invoice has been booked; their cost is accrued as a liability at month end.

How do I find invoices that should not be paid yet? Flag PO lines where the invoiced quantity exceeds the received quantity; those invoices fail the three-way match until the goods arrive.

Why are my PO totals doubled after joining receipts? A line with several deliveries matches several receipt rows; aggregate receipts per PO line before joining.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build an On-Time Delivery Report — supplier performance.
• Build an Accruals and Prepayments Schedule — booking the accrual.
• Build an Aged Creditors Report — what is owed once invoiced.