Python code: physical count vs system, differences and adjustments

Python code: physical count vs system, differences and adjustments

Sooner or later every small business runs a physical count, and the same scene shows up: someone walks the shelves with a clipboard, the numbers go into the computer and they do not match what the system says. The hard part is not counting, it is deciding what to do with the gap. Adjust everything at once with no paperwork behind it and any later review turns into an argument with no evidence. Adjust nothing and the stock on the system drifts away from reality, so next month's purchases are guessed. This program covers that middle step: it takes the count and the stock on the system, measures the gap in units and in money, separates what can be adjusted from what should be counted again, keeps the uncounted items on their own list and saves the support sheet the accountant will ask for. It is written for the owner of a small business, for the warehouse clerk who walks the shelves with a clipboard and for the accountant who has to explain the adjustment at month end.

The ZIP carries everything needed to run it today, with no internet connection and no libraries to install:

What the program does

It works in four moves: read, compare, summarise and file away. Product by product it does the following:

1. It reads datos/conteo.csv, where every line carries the product name, the stock reported by the system, the unit cost and up to two counts taken in the warehouse.
2. It takes the last count available: when a second count exists, that one wins; when it does not, the first one is used. The recount therefore replaces the first attempt without erasing the earlier figure.
3. It works out the gap in units by subtracting the stock on the system from what was counted. A positive result is a surplus and a negative one is a shortage.
4. It turns that gap into money by multiplying it by the unit cost, using exact decimal arithmetic, in cents and with commercial rounding.
5. It compares the size of the gap against the tolerance. Anything past the tolerance is not adjusted: it is flagged for a recount, because a large gap is almost always a counting mistake rather than a real movement in the warehouse.
6. It leaves the product with no count at all as pending, so it neither disappears along the way nor spoils the totals.
7. It adds up the surpluses and the shortages separately, works out the net adjustment and checks by a second route that the sum of all gaps agrees with that net figure.
8. It saves two files: the full count with every row and a sheet with the gaps only, which is the one that goes to accounting together with the signed count sheet.

How to run it

The only thing you need installed is Python 3.11 or later. The program uses nothing but the standard library of the language: there is no pandas to install, no extra package, and no internet connection required. Once the ZIP is unzipped, open the terminal in the folder that comes out and type a single command.

On Windows: python conteo_fisico.py. On Linux or Mac the command may be python3 conteo_fisico.py. If the machine answers that it cannot find the python command, try python3. If it answers that it cannot find the file, the terminal is almost certainly sitting in the wrong folder: the program builds its paths from its own location, so simply step into the unzipped folder and repeat the command. The output folder is created by the program itself; there is nothing to prepare by hand and no database to set up.

The code, explained

You do not need to follow the whole file to put it to work, but five parts are worth a look because they carry the business logic. The rest is table layout and number formatting.

The first part is the set of input and output paths. The program asks itself where it lives and builds three paths from there, without a single absolute path in the code. That detail is what lets you move the folder to the desktop, to a USB stick or to a shared network drive without touching one line:

BASE = Path(__file__).resolve().parent
ENTRADA = BASE / "datos" / "conteo.csv"            # sample count shipped inside the ZIP
SALIDA = BASE / "salida" / "conteo.csv"            # count result, ready to open in Excel
AJUSTES = BASE / "salida" / "ajustes.csv"          # differences only: this is the sheet that goes to accounting

ENTRADA points at the sample count shipped inside the ZIP, SALIDA stores the full count and AJUSTES stores the rows with a gap only, which are the ones that go to accounting. All three lean on pathlib, which builds paths with the separator of the operating system. The import block also carries the most important decision in the program: numbers are handled with Decimal instead of the usual float type, because money is never computed with binary decimals. A cent lost to rounding is a difference nobody wants to explain later, and here it cannot happen because every value is quantised to two decimals with commercial rounding.

The second part sets the tolerance and the working constants. The tolerance is the cut-off line of the report: a large gap is not adjusted, it is counted again. Every business sets its own, and in the sample it sits at five units, which for a hardware store is a reasonable margin:

TOLERANCIA = Decimal("5")

# Working constants: zero and one cent (they never change)
CERO = Decimal("0")
CENTAVO = Decimal("0.01")

The third part is the function that compares. It receives one row of the count, reads the stock on the system, the unit cost and the last count available, and returns a record with everything the report has to show:

def evaluar(fila: dict) -> dict:
    """Compares the final count with the system and classifies the row: gap, value, result and action."""
    producto = fila["product"]
    sistema = Decimal(fila["system"])
    costo = Decimal(fila["unit_cost"])
    final = ultimo_conteo(fila)

The fourth part is the decision itself. With the gap already worked out, the program sorts the row into one of three results and picks the action. Look at the last two lines: the action does not depend on whether the item is over or short, it depends on how big the gap is. Going past the tolerance is not adjusted, it is counted again, and that rule is what separates a trustworthy adjustment from a wild one:

    # Gap = what was counted minus what the system says (positive: extra stock)
    diferencia = final - sistema
    valor = redondear(diferencia * costo)

    if diferencia == CERO:
        resultado, accion = SIN_NOVEDAD, SIN_AJUSTE
    else:
        resultado = SOBRANTE if diferencia > CERO else FALTANTE
        # Going past the tolerance is not adjusted: it is counted again
        accion = RECONTAR if abs(diferencia) > TOLERANCIA else AJUSTAR

The fifth part builds the net adjustment. Here the program adds the surpluses and the shortages separately and then recomputes the total by another route, row by row, to compare the two. When they do not agree, the check line says so and the report should not be used to adjust the system:

    sobrantes_u, faltantes_u = suma_unidades(sobrantes), suma_unidades(faltantes)
    sobrantes_v, faltantes_v = suma_valor(sobrantes), suma_valor(faltantes)
    neto_u, neto_v = sobrantes_u + faltantes_u, sobrantes_v + faltantes_v
    suma_dif = sum((r["diferencia"] for r in con_conteo), CERO)
    cuadra = suma_dif == neto_u and suma_valor(con_conteo) == neto_v

What you will see on screen

The first thing on screen is the header with the name of the report and the tolerance line. Then comes the count table, one line per product and seven columns: the product name, the stock on the system, the final count, the gap in units, the value of that gap, the result and the recommended action. The gap column carries the sign, so at a glance you see what is over and what is short, and the action column is the one that goes back to the warehouse: Adjust means the gap is small and can be corrected straight away, Recount means the gap went past the tolerance and the item must be counted again before the system is touched, and Count is for the product that has no count at all yet. Underneath sits the summary with the totals per group, the net adjustment, the check line and the list of products to recount. A slice of the real output looks like this:

============================================================================================================
  PHYSICAL COUNT VS SYSTEM
  Didactic Python code · Kardex Tauro · kardex-tauro.muisca.co
============================================================================================================
Products read: 10
Tolerance: 5.00 units

Product                           System    Final count Difference           Value Result       Action      
------------------------------------------------------------------------------------------------------------
Gray cement 50 kg                 120.00         120.00       0.00            0.00 NO CHANGE    No action   
THHN 12 AWG wire                   45.00          48.00      +3.00      456,000.00 SURPLUS      Adjust      
PVC pipe 1/2 in x 6 m             200.00         206.00      +6.00       58,800.00 SURPLUS      Recount     
Tie wire 1/8 in                    34.00          33.00      -1.00       -7,200.00 SHORT        Adjust      
Cut-off wheel 7 in                 60.00          54.00      -6.00      -25,800.00 SHORT        Recount     
Measuring tape 5 m                 25.00          25.00       0.00            0.00 NO CHANGE    No action   
Leather work gloves                18.00                                           PENDING      Count       
Anticorrosive paint                40.00          38.00      -2.00      -64,000.00 SHORT        Adjust      
Gate valve 1/2 in                  90.00          95.00      +5.00       43,000.00 SURPLUS      Adjust      
Claw hammer 16 oz                  12.00           9.00      -3.00     -126,000.00 SHORT        Adjust      
------------------------------------------------------------------------------------------------------------

============================================================================================================
COUNT SUMMARY
============================================================================================================
  NO CHANGE     2 products         0.00 units               0.00
  SURPLUS       3 products       +14.00 units        +557,800.00
  SHORT         4 products       -12.00 units        -223,000.00

  Total surplus  :     +14.00 units       +557,800.00
  Total shortage :     -12.00 units       -223,000.00
  Net adjustment :      +2.00 units       +334,800.00
  Products not counted: 1 (they are not part of the adjustment)
  Check (sum of gaps = net adjustment): TIES
  Products to recount (2): PVC pipe 1/2 in x 6 m, Cut-off wheel 7 in

File written: salida/conteo.csv
File written: salida/ajustes.csv

Look at the check line: when it says TIES, the sum of every gap agrees with the net adjustment computed separately, and the report can go to accounting with confidence. Were it to say REVIEW, something in the count was mistyped and it should be fixed before anything is adjusted, because an adjustment built on a bad figure only moves the error around. Look at the value column too, not only the one in units: a shortage of three units on a claw hammer weighs far more in money than a surplus of five units on a cheap gate valve, and the accounting adjustment is made on value, not on the number of units. That is why the program works with the unit cost of each product instead of a general average.

The two files left in the output folder open straight away in Excel or in any spreadsheet. The full count is there to be reviewed and signed; the adjustment file is short on purpose, because it only carries the rows with a gap and it is the one filed with the count sheet.

Common mistakes and tips

• Running the program from another folder: since it builds its paths from its own file, the simplest route is to open the terminal inside the unzipped folder and type the command there.
• Letting Excel save the CSV with a semicolon separator, which is common in many locales: the file then reads as a single column. Export it with commas.
• Check the count columns: when both are filled, the program takes the second one. To compare the first count, leave the second empty.
• Do not set the tolerance to zero. With no tolerance, any single unit of difference sends the row to a recount and the report loses its point exactly when the warehouse is at its messiest.
• Check the unit cost before adjusting. A stale cost figure produces a wrong adjustment even when the units are perfect.
• Keep the adjustment file with the count sheet and the date visible. When someone asks why the stock moved, the evidence is already on the desk.
• If the check line says REVIEW, do not adjust anything yet: fix the count first and run the program again.

When this is not enough

This program is a good starting point, but it is not an inventory system. It works on a CSV file, saves a result and stops: it has no users, it does not record who changed what, it keeps no history of earlier counts and it does not talk to billing. When the business counts every month across several warehouses, when items come in and go out every day and when the accountant asks for a trail behind every adjustment, the file and the script fall short. That is where Kardex Tauro comes in: it is free and keeps the stock ledger, the receipts, the issues and the adjustments in one place, so the physical count is compared against something that is kept up to date. If the stock fits in a spreadsheet and the count is occasional, this program is more than enough.

Download the ZIP, run the sample just as it comes and then replace datos/conteo.csv with your own count: the program needs nothing else. If the business keeps growing and control becomes a daily task, it is worth a calm look at Kardex Tauro; in the meantime, this program leaves the counting work tidy, measured in units and in money, and with the evidence ready for the accountant.