Reconcile a Stock Count Against System Inventory

At the end of a stock take, the warehouse hands over a pile of count sheets — one per counter or aisle — and finance needs to know how far the physical stock differs from what the inventory system says, item by item and in money. The comparison sounds like a simple lookup, but count sheets are messy: SKU codes typed in lower case or without leading zeros, the same item found in two locations, items counted that the system has never heard of, and system items nobody counted at all. A careless VLOOKUP silently drops the last two groups, which are often the most important. This page reconciles the count with pandas using an outer join that keeps everything, values the differences, decides which lines to recount, and writes the adjustment report. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The counts/ folder holds the count sheets from two counters, each with SKU, location and counted quantity. system_inventory.xlsx is the inventory system's export at the count date: SKU, description, system quantity and unit cost. The count sheets contain the usual problems — a lower-case code, a code without its leading zeros, a trailing space, and one item found in two aisles.

Step 1 — Combine and clean the count sheets

Read every sheet, record who counted what, and normalise the SKU codes so they match the system's format:

from pathlib import Path
import pandas as pd

counts = pd.concat([pd.read_excel(p, dtype={"SKU": str}).assign(Sheet=p.stem)
                    for p in sorted(Path("counts").glob("*.xlsx"))], ignore_index=True)

def clean_sku(s):
    s = s.str.strip().str.upper()
    parts = s.str.extract(r"^([A-Z]+)-?(\d+)$")
    ok = parts[0].notna()
    s[ok] = parts.loc[ok, 0] + "-" + parts.loc[ok, 1].str.zfill(4)
    return s

counts["SKU raw"] = counts["SKU"]
counts["SKU"] = clean_sku(counts["SKU"])
print(counts.loc[counts["SKU"] != counts["SKU raw"], ["Sheet", "SKU raw", "SKU"]].to_string(index=False))

Codes follow a pattern — letters, a hyphen, four digits — so a regular expression can rebuild them: blt-0043 becomes BLT-0043, BLT-42 becomes BLT-0042, and the trailing space in TPE-0050 disappears. Reading with dtype={"SKU": str} stops pandas turning purely numeric codes into numbers and losing their leading zeros. Codes that do not match the pattern are left alone rather than guessed at; they will surface as unknown items in Step 3. Printing every changed code gives the warehouse a chance to confirm the corrections.

Step 2 — Total items counted in more than one place

The same SKU can sit in several locations — overflow stock on a top shelf, returns in a separate bay. The system usually holds one quantity per SKU, so counts are totalled per SKU before comparing, while keeping the locations for the report:

counted = (counts.groupby("SKU")
                 .agg(Counted=("Counted", "sum"),
                      Locations=("Location", lambda s: ", ".join(sorted(set(s)))),
                      Count_lines=("Counted", "size"))
                 .reset_index())
print(counted[counted["Count_lines"] > 1].to_string(index=False))

BLT-0042 was counted as 480 in A1 and 20 in B1 — the second line typed as BLT-42 — for a total of 500. Without the SKU cleaning in Step 1, those 20 bolts would have shown as an unknown item and A1 as a shortage of 20. If your system tracks stock by location, group by SKU and location instead and join on both.

Step 3 — Outer join with the system quantities

Join the counted totals to the system export with an outer join and an indicator column, so every SKU from either side is kept and labelled:

system = pd.read_excel("system_inventory.xlsx", dtype={"SKU": str})
system["SKU"] = clean_sku(system["SKU"])

rec = system.merge(counted, on="SKU", how="outer", indicator=True, validate="one_to_one")
rec["Status"] = rec["_merge"].map({"both": "Counted", "left_only": "Not counted", "right_only": "Not in system"})
rec[["System qty", "Counted"]] = rec[["System qty", "Counted"]].fillna(0)
print(rec["Status"].value_counts().to_dict())
print(rec.loc[rec["Status"] != "Counted", ["SKU", "Description", "System qty", "Counted", "Status"]].to_string(index=False))

indicator=True adds a _merge column saying whether each SKU appeared in both tables, only in the system, or only in the count. The hi-vis vests (VST-0020) are in the system with 35 units but appear on no count sheet — either they were missed or they really are gone, and the answer decides whether the count is complete. validate="one_to_one" makes pandas raise an error if either side has duplicate SKUs, which would multiply rows silently. An inner join, or a VLOOKUP from the count sheet, would have hidden the vests entirely. No counted SKU is missing from the system this time — thanks to the cleaning in Step 1; without it, BLT-42, blt-0043 and TPE-0050 would all have appeared as Not in system while their real codes showed false shortages. Matching keys that differ only in case and spacing is covered in Merge on Keys That Differ in Case and Spacing with pandas.

Step 4 — Value the variances and pick recounts

A variance of 200 washers matters less than a variance of 4 brake pad sets. Value every difference at unit cost, then flag lines worth recounting by absolute value and by percentage:

rec["Variance qty"] = rec["Counted"] - rec["System qty"]
rec["Variance value"] = (rec["Variance qty"] * rec["Unit cost"]).round(2)
rec["Variance %"] = (rec["Variance qty"] / rec["System qty"].where(rec["System qty"] != 0) * 100).round(1)

VALUE_LIMIT, PCT_LIMIT = 50.00, 5.0
rec["Recount"] = ((rec["Variance value"].abs() >= VALUE_LIMIT) |
                  (rec["Variance %"].abs() >= PCT_LIMIT) |
                  (rec["Status"] != "Counted"))
cols = ["SKU", "Description", "System qty", "Counted", "Variance qty", "Variance value", "Variance %", "Status"]
print(rec.loc[rec["Recount"], cols].sort_values("Variance value").to_string(index=False))

The brake pads are 4 short — a variance of −€154 that clearly needs a recount. The washers are 200 over, a 6.7% difference worth only €2, and still flagged by the percentage rule; whether to recount them is a judgement call, and many warehouses set the percentage limit higher for cheap items. The vests that were not counted at all are flagged too, at the full −€154 their missing stock would cost. where(... != 0) avoids dividing by zero for items with no system quantity. Six lines go back to the floor in total: the brake pads and vests, plus the helmets, the 4x40 screws, the M8x50 bolts and the washers on the percentage rule. The gloves, 4 boxes short at −€27.60 and −4%, fall below both limits and are accepted as counted.

Step 5 — Summarise the adjustment and write the report

Finance needs the net value of the adjustment, split into gains and losses; the warehouse needs the recount list. Both go into one workbook:

summary = pd.DataFrame({
    "Lines": [len(rec), int(rec["Recount"].sum()), int((rec["Variance qty"] != 0).sum())],
}, index=["SKUs reconciled", "To recount", "With a variance"])
money = pd.Series({
    "System value": (rec["System qty"] * rec["Unit cost"]).sum(),
    "Counted value": (rec["Counted"] * rec["Unit cost"]).sum(),
    "Gains": rec.loc[rec["Variance value"] > 0, "Variance value"].sum(),
    "Losses": rec.loc[rec["Variance value"] < 0, "Variance value"].sum(),
}).round(2)
money["Net adjustment"] = round(money["Gains"] + money["Losses"], 2)
print(money.to_string())

from openpyxl.styles import Font, PatternFill
with pd.ExcelWriter("stock_reconciliation.xlsx", engine="openpyxl") as xw:
    money.rename("EUR").to_frame().to_excel(xw, sheet_name="Summary")
    summary.to_excel(xw, sheet_name="Summary", startrow=len(money) + 3)
    rec.loc[rec["Recount"], cols + ["Locations"]].to_excel(xw, sheet_name="Recount", index=False)
    rec.drop(columns=["_merge"]).to_excel(xw, sheet_name="All SKUs", index=False)
    counts.to_excel(xw, sheet_name="Count lines", index=False)
    for ws in xw.book.worksheets:
        for c in ws[1]:
            c.font = Font(bold=True)
        for col in ws.columns:
            ws.column_dimensions[col[0].column_letter].width = 15
    for row in xw.sheets["Recount"].iter_rows(min_row=2):
        if (row[5].value or 0) < 0:
            row[5].fill = PatternFill("solid", fgColor="FFC7CE")
print("net adjustment:", money["Net adjustment"])

Gains and losses are reported separately as well as netted: a net adjustment of a few euros can hide large offsetting errors, and auditors look at both. The Recount sheet, with locations, is what goes back to the warehouse; once recounts come in, update the count sheets and rerun — the whole reconciliation takes seconds, so iterate until the recount list is empty or explained. The Count lines sheet, with raw and cleaned SKUs, keeps the trail from what was written on the floor to the final figure. Valuing stock with FIFO layers instead of a single unit cost is covered in Calculate FIFO Inventory Valuation with Python.

Common pitfalls

• Inner joins or VLOOKUPs. Uncounted and unknown items disappear; use an outer join with an indicator.
• Uncleaned SKU codes. Case, spaces and missing zeros split one item into two.
• Numeric SKUs read as numbers. Leading zeros vanish; read codes as text.
• Recounting by quantity only. Value the variance; four brake pads outweigh two hundred washers.
• Only reporting the net adjustment. Show gains and losses separately.

Conclusion

Reconciling a stock count is a cleaning and joining exercise: combine the count sheets, normalise SKU codes, total each item across locations, and outer-join to the system quantities with an indicator so missed and unknown items stay visible. Value each variance at cost, flag recounts by value and percentage, and report gains, losses and the net adjustment alongside a recount list — then rerun after each recount until the differences are resolved.

Frequently asked questions

How do I compare a stock count with system inventory in pandas? Total the counted quantity per SKU, then merge it with the system export using how="outer" and indicator=True so items present on only one side are kept and labelled.

How do I find items that were not counted? After the outer merge, rows where the indicator is left_only are in the system but on no count sheet.

How do I decide which stock variances to recount? Value each variance at unit cost and recount lines above an absolute value limit or a percentage of system quantity, plus anything not counted or not in the system.

How do I fix SKU codes typed inconsistently on count sheets? Strip spaces, convert to upper case, and rebuild codes that follow a known pattern with a regular expression, padding the numeric part with zfill.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build an Inventory Reorder Report — what to order once stock is right.
• Build a Bank Reconciliation Report — the same matching idea for cash.
• Find Near-Duplicate Names Within One Excel Column — when codes are too messy for a pattern.