Inventory analysis in Python with pandas — DIO, ABC and slow movers
To analyse inventory in Python, load the monthly stock summary for each item into pandas, add up the month-end values, compare what was issued with the average stock held, and rank the items by what moves through the warehouse. The 47 lines of pandas below do this on a DATA DARE inventory practice set — a retailer's distribution centres with 640 items — and the results agree with the answers in the set's Question Book.
The sets live now
Mizan Retail: Shelves, Stock and Ramadan — Inventory & Stock
Read a retailer's stock ledger the way supply-chain finance does: stock value and cover, turnover and days inventory outstanding, ABC classes, slow-moving lines...
Get the data
Retail and Manufacturing: Transfers, Counts and Stock Nobody Ordered — Inventory & Stock
Work a two-company stock ledger: append the new period, build your own calendar, value stock at weighted average cost, follow goods in transit between warehouse...
Get the dataThe steps
- Load the items, the monthly stock summary, the stock adjustments and the stock plan — four sheets of one Excel workbook.
- Take the stock at a month-end and compare it with the plan for that month.
- Work out turnover and days of stock (DIO): what was issued in the year at cost, against the average of the twelve month-end values.
- Rank the items by what was issued. The A class is the smallest group of items that makes up 80% of the total.
- Find the slow movers — items holding more than 180 days of their recent issues — and measure shrinkage from the stock counts.
The code
Download the L1 inventory set (DD-INV-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl).
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
workbook = "DD-INV-001_Inventory.xlsx"
items = pd.read_excel(workbook, sheet_name="DimItem")
stock = pd.read_excel(workbook, sheet_name="FactStockSummary")
adjust = pd.read_excel(workbook, sheet_name="FactStockAdjustment")
plan = pd.read_excel(workbook, sheet_name="FactStockPlan")
# 1. One row per item per month-end. Add the category, keep 2025.
stock = stock.merge(items[["ItemCode", "Category"]], on="ItemCode")
y25 = stock[stock["PeriodKey"] // 100 == 2025]
dec = y25[y25["PeriodKey"] == 202512]
on_hand = dec["ClosingValue"].sum()
gap = on_hand - plan.loc[plan["PeriodKey"] == 202512, "PlanValue"].sum()
print(f"Stock at 31 Dec 2025: {on_hand:,.2f} against plan: {gap:,.2f}")
# 2. Turnover and days of stock (DIO): issues at cost against the average of the 12 month-ends.
average = y25.groupby("PeriodKey")["ClosingValue"].sum().mean()
issued = y25["IssuedValue"].sum()
print(f"Turnover 2025: {issued / average:.2f} times DIO: {average / issued * 365:.2f} days\n")
# 3. DIO by category: where stock sits longest.
cat = y25.groupby(["Category", "PeriodKey"])["ClosingValue"].sum().groupby("Category").mean()
dio = (cat / y25.groupby("Category")["IssuedValue"].sum() * 365).sort_values(ascending=False)
print("Slowest categories (DIO, days):\n" + dio.head(3).to_string(), "\n")
# 4. ABC: rank items by 2025 issues; the A class is the fewest items that make up 80%.
by_item = y25.groupby("ItemCode")["IssuedValue"].sum().sort_values(ascending=False)
share = by_item.cumsum() / by_item.sum()
a_items = (share < 0.80).sum() + 1
print(f"A items: {a_items} of {len(by_item)} make up 80% of what was issued")
# 5. Slow movers: stock at the last month-end worth more than 180 days of recent issues.
last = stock[stock["PeriodKey"] == 202609].set_index("ItemCode")["ClosingValue"]
six_months = stock[stock["PeriodKey"].between(202604, 202609)]
recent = six_months.groupby("ItemCode")["IssuedValue"].sum()
days = (pd.Timestamp("2026-09-30") - pd.Timestamp("2026-03-31")).days
slow = last[last > recent / days * 180]
print(f"Slow movers at 30 Sep 2026: {len(slow)} items worth {slow.sum():,.2f}")
# 6. Shrinkage: stock lost at the counts, as a share of what was issued.
counts = adjust[(adjust["AdjustmentType"] == "Count variance")
& (adjust["AdjustmentDate"].dt.year == 2025)]
lost = counts["Value"].sum()
print(f"Count losses 2025: {lost:,.2f} shrinkage: {-lost / issued:.2%}")
What it prints
Exactly as the script prints it:
Stock at 31 Dec 2025: 16,179,668.66 against plan: -7,018,031.34
Turnover 2025: 11.99 times DIO: 30.45 days
Slowest categories (DIO, days):
Category
Seasonal and gifts 210.27
Electronics accessories 86.46
Home and kitchen 74.08
A items: 382 of 640 make up 80% of what was issued
Slow movers at 30 Sep 2026: 18 items worth 1,117,807.65
Count losses 2025: -233,461.30 shrinkage: 0.11%
How to read the result
- Stock was 16,179,668.66 at the end of 2025, 7,018,031.34 below the plan for that month-end.
- Stock turned 11.99 times in 2025: 30.45 days of stock on average. Seasonal and gifts sits for 210.27 days, about seven times the average, so it is the first place to look.
- 382 of the 640 items make up 80% of what was issued. These A items deserve the tightest counts and reorder rules.
- 18 items hold more than 180 days of stock at 30 September 2026, worth 1,117,807.65 — candidates for a write-down review.
- The stock counts lost 233,461.30 in 2025, 0.11% of what was issued.
- Stock at the year-end, the gap to plan, turnover, DIO, the slowest category, the A class, the slow movers, the count losses and shrinkage are all questions in the set, so each figure can be checked in its Question Book.
Mistakes this data will catch
- Taking one month-end as the average stock. Use the twelve month-ends; stock builds up before Ramadan and the gift season.
- Adding closing values across months as if they were a total. Stock is a balance: add across items at one date, then average across dates.
- Measuring days of stock against sales value. Stock is held at cost, so compare it with what was issued at cost.
- Forgetting the sign of the count adjustments. A negative value means stock was lost; flip it to get shrinkage.
The next levels
The L2 set (DD-INV-002) comes as CSV, JSON, Excel and Parquet files and adds every stock movement, purchase price history, suppliers and locations, the stock counts and a stock budget. The same steps work from the movements instead of a monthly summary.
Python or Power BI
Python suits the monthly stock review: the same ABC and slow-mover lists saved to Excel for the buyers. Power BI suits the dashboard, where category, item and month are slicers.
The course
- Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
- Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
- Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
- Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
- Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...