Python code: FIFO stock ledger by lots (free download)

Python code: FIFO stock ledger by lots (free download)

You bought the same product three times this month and every purchase cost you something different. You sold it several times, and at month end the awkward question shows up: what did the goods I sold really cost me? The FIFO method (first in, first out) answers with one simple rule: whatever leaves first is the oldest stock, valued at the cost of the oldest purchase still sitting in the warehouse. This Python program applies that rule lot by lot, movement by movement, and at the end it writes the proof: which lot each sold unit came from and at what cost. It is meant for the small business owner who wants to understand their own numbers, for the accountant who checks cost of sales before signing a financial statement, and for the warehouse clerk who has to explain why the closing stock is not worth the same under every method. You do not need to know how to program: the file runs exactly as it comes.

The package is small, it never touches the internet and it installs nothing. It ships with sample movements, so you can see the results on screen in less than a minute and then swap that file for your own data, using the same columns.

What the program does

1. Reads datos/movimientos.csv, where every row is one movement: product, date, type (purchase or sale), detail, quantity and, for purchases, the unit cost.
2. Opens a new lot for every purchase, with its date, its quantity and its cost. A lot is simply «so many units bought at this price».
3. When a sale arrives it takes units from the oldest lot. If that lot is not enough, it moves on to the next oldest one, and so on until the sold quantity is covered.
4. Adds up the cost of every piece it consumed, rounded to cents, and that is the FIFO cost of that sale.
5. Values the balance left in the warehouse with the lots that remain: under FIFO those are the newest ones, which is why closing stock tends to look like today's prices.
6. Prints the ledger in columns, prints the lots consumed sale by sale and closes with a summary per product.
7. Checks that the balance ties (purchases minus sales) and saves two CSV files ready for Excel.

How to run it

You need Python 3.11 or newer and nothing else: the program uses the standard library only. Unzip the folder, open a terminal inside it and run the program:

python kardex_peps.py

On Windows a double click also works once Python is installed, but the window closes by itself at the end, so the terminal is the better habit: you get to read the whole result. There is nothing to install, no sign-up and no data leaving your machine. If the system says it cannot find «python», try «py» on Windows or «python3» on Linux and Mac.

The code, explained

These are the pieces that do the work. The first one shows how a lot is opened when goods come in: every purchase appends a new lot to the list with its quantity and its cost, and leaves the ledger row with the value of that entry already computed.

        if movimiento["type"] == TIPO_ENTRADA:
            # Entrada: cada compra abre un LOTE nuevo con su cantidad y su costo.
            costo = Decimal(movimiento["unit_cost"])
            estado["lotes"].append({"fecha": fila["fecha"], "cantidad": cantidad, "costo": costo})
            fila.update(e_cant=cantidad, e_vu=costo, e_val=redondear(cantidad * costo))

The heart of the method sits in the sale. The program always looks at the first lot on the list, the oldest one, and decides: if the whole lot fits inside what is still missing, it consumes it completely and drops it; if not, it takes only the part it needs and leaves the rest of that lot for the next sale. That is the entire FIFO secret, and it explains why a single sale can end up valued with two different costs.

            while pendiente > 0 and estado["lotes"]:
                lote = estado["lotes"][0]
                if lote["cantidad"] <= pendiente:
                    toma = lote["cantidad"]
                    estado["lotes"].pop(0)
                else:
                    toma = pendiente
                    lote["cantidad"] -= toma
                pendiente -= toma
                valor = redondear(toma * lote["costo"])

Something that surprises many people happens with the balance: it is not valued with an average, but with the lots that still exist. Since FIFO takes the old stock first, what remains is the new stock, and the balance therefore shows up at the most recent cost. That detail is what makes closing stock change value from one method to another even when the units are exactly the same.

        fila["saldo_cant"] = sum((lote["cantidad"] for lote in estado["lotes"]), Decimal("0"))
        # # The balance is valued with the lots LEFT: FIFO keeps stock at the newest cost
        fila["saldo_val"] = redondear(sum((lote["cantidad"] * lote["costo"] for lote in estado["lotes"]), Decimal("0")))

And finally the part I find most useful when talking to an accountant: the program prints, sale by sale, how many units it took from each lot and at what cost. When one sale is served from two lots you see both lines with their two costs, and that is the documentary proof of how cost of sales was built.

        for venta in estado["ventas"]:
            print(f"Sale {venta['fecha']}: {miles(venta['cantidad'])} units")
            for consumo in venta["consumos"]:
                print(f"  lot {consumo['fecha_lote']}   {miles(consumo['cantidad']):>10} x {miles(consumo['costo']):>12} = {miles(consumo['valor']):>16}")

What you will see on screen

The output starts with a banner, then a table for each product, then the lots consumed block and it ends with the summary. A piece of the real banner looks like this:

================================================================================
  FIFO STOCK LEDGER BY LOTS
  Didactic Python code · Kardex Tauro · kardex-tauro.muisca.co
================================================================================
Movements read: 10

Product: Gray cement 50 kg

The most didactic block is the one with the lots consumed: sale by sale, it says which lot every unit came from. Below it is shown with plain amounts, exactly as the CSV file stores them, so you can copy them into a spreadsheet; on the console you will see them with the thousands separator of your language. The six lines belong to the four sales of cement and the two sales of wire:

The second cement sale is the one to watch closely: it asked for 20 units, the old lot only had 10, so the program took 10 at the old cost and 10 at the new cost. That mix is exactly what happens in the warehouse when the previous lot runs out.

Then comes the summary per product, which is the quick review: it adds purchases, sales and balance, works out the FIFO cost of sales for each product and tells you whether the balance ties with purchases minus sales.

Watch one detail that is worth gold when explaining FIFO: the cement is consumed from the oldest lot towards the newest one and the closing balance stays at the most recent purchase cost, the highest of the three. The final units are the same ones a weighted average would give you; what changes is the value you hang on them, and that change moves cost of sales and the profit of the month.

Common mistakes and tips

• Odd date formats: use year-month-day and sort the rows from oldest to newest. The program respects the order of the file, it does not re-sort it for you.
• Selling more than you have: when a sale asks for more units than the lots can cover, the program raises a clear message instead of inventing a number. Check that case before publishing your figures.
• Renaming the columns: the program expects them exactly as they come in the sample. If your system exports other names, rename them in a copy of the file, never in the original.
• Quantities with thousands separators: keep the numbers plain, with no dots and no commas, so they are not read as decimals.
• Assuming FIFO and weighted average give the same thing: FIFO follows lots and changes the cost every time you sell; the average is recalculated on every purchase and spreads a single cost over all units. If the two reports disagree, there is no typing error: they are different methods.
• Storing the ZIP in a path with accents or spaces: it almost always works, but if something fails, move the folder to a short path without accents.
• Forgetting the backup: before replacing the sample data with yours, keep a copy of the original file. It is a plain text CSV and one extra column can break it.

When this is not enough

This program solves one part of the problem well: valuing sales and balances by lots with your own movements, in a file you understand and can review. It falls short once the business grows and you no longer want to open a CSV to find out what happened: several warehouses, several people touching the same stock, returns, transfers between sites, products with expiry dates, reports your accountant asks for on the last day of the month. At that point the script becomes a notebook rather than a system.

That is where Kardex Tauro comes in, an inventory system built for small businesses: it keeps the same lot-by-lot FIFO stock ledger without anyone writing a line of code, it controls warehouses and users, and it leaves the reports ready to hand over. The program you just downloaded is free and it is good for ordering your stock while the operation still fits in one file; when the file gets too small, it is the system's turn.

Download the ZIP, run the sample and read the lots consumed block slowly: once you can explain where every unit of cost of sales came from, you understand FIFO. If it helps, share it with your accountant and then replace the sample data with your own.