Skip to content Nicolas Schmid
← All posts

Replace Excel lists: the stock report that builds itself

Friday afternoon. The inventory system produces an export of current stock. Purchasing has a list of open orders. The shop or till delivers the week's sales. Three Excel files in three different formats. Someone on the team copies, sorts, builds VLOOKUP formulas and hopes it all lines up in the end.

The stock report matters. It decides what gets reordered. But putting it together is pure busywork. It is error-prone, and it often depends on a single person who knows which column goes where.

In this post I show how a retail SME hands this work to a small, self-built tool. No programming skills needed, just a coding agent. And one rule that matters more than any formula: when something does not fit, the tool stops and says so instead of guessing.

How it works today

Take a bicycle accessories retailer with twelve employees, one shop and an online store. The example is made up. Every Friday the head of purchasing puts the stock report together.

She opens three files:

FileContentTypical quirk
stock.xlsxitem number, description, stock, minimum stockitem number as text with leading zeros
orders.xlsxitem number, quantity ordered, delivery dateseveral rows per item
sales.xlsxdate, item number, quantity, channelshop and online mixed

Then the manual work starts. Sum sales per item. Sum open orders per item. Attach both to the stock list with VLOOKUP. Colour items below minimum stock red. Send the whole thing as a PDF to management.

Depending on the week, that takes one to two hours. And it breaks as soon as someone changes an export setting in the shop system. VLOOKUP suddenly finds nothing, because "000123" and "123" are two different things to Excel. Often nobody notices until an item is sold out.

What the tool does

The tool does exactly what the head of purchasing does by hand today. Just the same way every time:

  1. It reads the three exports from a fixed folder, for example "StockReport/In".
  2. It checks that all expected columns are present and that item numbers match up.
  3. It merges the three lists per item: stock, this week's sales, open orders.
  4. It flags items below minimum stock that have no order on the way.
  5. It writes the report as an Excel file and a PDF to "StockReport/Out", with the date in the file name.
  6. At the top of the report is a short summary: how many items are critical, and which items could not be matched across the lists.

Important: the numbers need no AI at all. Summing and merging is arithmetic, and a normal program does arithmetic more reliably than a language model. The AI is needed to write that program. And, if you like, for the short written summary at the top. For an example where the AI itself does part of the work, reading notes and photos, see Quotes from notes and photos.

How it is built, step by step

A coding agent is an AI program such as Claude Code that can read files on your computer, write small programs and try them out straight away. You give it instructions in plain language. Such an instruction is called a prompt.

Step 1: Describe the process. Before the agent builds anything, you describe how the report is made today. Ideally the way you would explain it to a new colleague. Which file, which column, which rule.

Step 2: Show it the real files. This is the core of week two in the KI-Werkstatt (the AI workshop course), "AI on your data". You put last week's three exports in a folder and let the agent look first:

The folder sample/ contains three Excel exports: stock.xlsx,
orders.xlsx and sales.xlsx. Look at them and describe in your
own words which columns exist and how the files relate through
the item number. Point out anything that does not match up
cleanly. Do not write any code yet.

What comes back is often instructive. The agent notices, for example, that the stock list has leading zeros and the sales list does not. Or that 14 items were sold that do not appear in the stock list at all. You may know such things already. Now they are written down.

Step 3: Build the tool. Now you ask the agent to write a script. A script is a small program you start with a double-click or a short command. The agent writes it, runs it on your sample files and shows you the result. You compare it with last week's report. Where it differs, you say so: "Item 4711 is missing the online sales." The agent finds the cause and fixes it.

Step 4: The test with last week. This is where it gets solid. You have last week's report, made and checked by hand. That becomes the benchmark. A test is an automatic check: the tool gets last week's exports, and its result is compared number by number with the known, correct report. If everything matches, the test is green. Ask the agent to set up this test and run it after every change. Add two or three past weeks, ideally one with something unusual such as a stocktake or a sale.

What can go wrong, and the guardrail

The most common failure in practice is not the AI. It is the export. Someone renames the column "Quantity" to "Qty" in the shop system. An update changes the date format. A new branch shows up as an extra column.

A badly built tool guesses in that case. It takes whichever column fits best, or simply puts in zero. The report looks normal but is wrong. That is worse than no report.

So there is a guardrail, a fixed rule the tool cannot get around: if an expected column is missing or renamed, the tool stops and says exactly what is missing. For example: "sales.xlsx has no column 'Quantity'. Found: Date, Item number, Qty, Channel. Please check." No report, but a clear message.

You test this rule on purpose. Rename a column in a copy of the test file and see whether the tool really stops. That is exactly what week four of the Werkstatt, "secure and hand over", is about.

Other useful checks:

  • Item numbers are always handled the same way, with or without leading zeros.
  • Items that appear in only one list go to a separate "Unmatched" section instead of silently disappearing.
  • If a week's sales differ sharply from previous weeks, the report says so. Maybe only half an export arrived.

And the basic rule stays: AI is a very fast, very well-read intern. Great for drafts, but anything that has to be right, you check. For the first few weeks, put the automatic report next to the usual one.

What it costs in time

For the build, plan on the KI-Werkstatt: four weeks, half a day each, with some time in between on your own files. Then two to three weeks of running both reports side by side.

Day to day, the Friday busywork becomes a few minutes: drop the exports in the folder, start the tool, read the report. The real work, deciding what to reorder, stays with you. You just have more time for it.

If the report should later come straight from the inventory system without exports, or several branches need their own access, it becomes a software project. That is implementation by Halvic Labs, at a fixed price after an analysis. For an overview of paths and costs see What does your own AI tool cost.

What you can do today

  • Put last week's three exports in one folder. Together with the finished report you made from them. That is your first test case.
  • Note the column names of each file. Exactly as they appear in the export. This list becomes a check rule later.
  • Write down your rules. When is an item critical? Do open orders count? What happens with returns?
  • Ask who on the team configures the exports. Whoever can change columns should know that a tool depends on them.

Frequently asked questions

Isn't Power Query in Excel enough?

In some cases, yes. Power Query can merge lists. The difference is in the checking: your own tool can give clear error messages, come with tests and write the report in any format. And the coding agent explains every step if you ask.

Does my stock data go to an AI provider?

While building, the agent sees your sample files. In the course we work only with sample data without personal data. Stock levels are usually not personal data, but they are business secrets. The finished tool calculates locally on your computer, without AI. Only the optional written summary needs a model, and you can leave that part out.

What if I want to change the tool?

Open the coding agent in the tool's folder and describe the change. The tests then run again. If they are green, the change is safe. If they turn red, you see immediately what broke.

Does this work with CSV instead of Excel?

Yes. CSV is even simpler. What matters is that the format is the same every week. And if it is not, the tool tells you.

The next step

If you want to hand off your own stock report or similar Excel busywork, the KI-Werkstatt is the right setting. In four weeks every participant builds their own tool with tests and guardrails, and week two is all about connecting the AI to your Excel, PDF and e-mail data.