How the checker works
This page explains exactly what the checker does with two price lists, so you can decide how far to trust each number. Calculation version 1.0.0.
What you need
Two files from the same supplier: the list you use now and the new one. Each needs a column with a product code that is unique within that file, and a column with the cost price. CSV and XLSX files work. The header row can be any of the first 50 rows.
How products are matched
A product in the old list matches a product in the new list only when the product codes are identical. Spaces before or after a code are ignored. Everything else counts, including leading zeros, so 00123 and 123 are different products. Upper and lower case letters count too, unless you tick the option to ignore them.
There is no fuzzy matching. The checker never pairs products because their descriptions look alike.
In XLSX files, a code stored as a number with a zero-padded format, such as 00123, keeps its zeros. A code that Excel displays with thousands separators or in scientific notation is read as the underlying number.
What each result means
- Price increase and price decrease
- The code is in both lists once, both prices are valid, and the new price is higher or lower.
- Unchanged
- The same price in both lists. 10, 10.0 and 10.00 are equal.
- Needs review
- The code matched, but the prices were not compared because a price is blank, unreadable or negative, the currency changed, or the pack size changed. Comparing a case of 12 with a case of 24 would give a misleading change, so it is flagged instead.
- Duplicate ID
- The code appears more than once in one list. None of those rows are compared, because there is no safe way to know which one is right.
- Missing ID
- A row has data but no product code. It is listed and not compared.
- Only in new list
- The code is in the new file and not in the old one.
- Only in old list
- The code is in the old file and not in the new one. This does not mean the product is discontinued. Suppliers often send partial lists.
How prices are read
Numeric spreadsheet cells are used as numbers. Text prices are read using the format you choose: 1,234.56 or 1.234,56. Thousands separators must be in groups of three, so under the first format 12,34 is flagged as unreadable instead of being guessed. A currency symbol at the start or end ($, €, £, ¥, ₹) is allowed. A price in parentheses, such as (5.00), is read as negative and flagged.
The calculations
Prices are stored as exact decimals, not floating point numbers, so a change from 0.10 to 0.30 is exactly 0.20.
- Price change = new price minus old price. Shown exactly.
- Change % = price change divided by old price, times 100. If the old price is zero, the percentage is left blank because it is not defined.
- Gross margin % = (selling price minus cost price) divided by selling price, times 100. It uses one selling price column, from whichever list you chose it in, for both the old and new margin.
- Margin change (points) = old cost minus new cost, divided by selling price, times 100. A cost rising from 10 to 11 with a selling price of 20 moves the margin from 50% to 45%, a change of minus 5 points.
Percentages are rounded to 2 decimals, with halves rounded away from zero. Each percentage is rounded on its own from the exact values, so an old and new margin can differ from the margin change by 0.01 in rare cases.
Gross margin here is the selling price minus the supplier cost. It does not include freight, discounts, rebates, tax or any other cost, and it is not net profit.
File rules and limits
- CSV and XLSX only. Save .xls, .xlsm and .xlsb files as .xlsx first.
- Up to 20 MB per file, 50,000 rows below the header, and 200 columns.
- Workbooks with macros or passwords are refused.
- CSV files can use commas, semicolons, tabs or pipes. The separator is detected from the first line. UTF-8, UTF-16 and Windows-1252 text are read.
- In XLSX files the stored value of each cell is used. Formulas are not recalculated.
The report
The .xlsx report has two sheets. The summary lists the file names, sheets, header rows, the columns you chose, the number format, the counts for each result and the calculation version. The second sheet has every row, with the source row number in each file and a note explaining any flag.
The CSV download has the rows only. Any text that starts with =, +, -, @ or a tab is prefixed with an apostrophe so a spreadsheet program cannot run it as a formula.
What the checker does not do
It does not read PDFs or scanned price lists, log in to supplier portals, update your inventory or point-of-sale system, change your selling prices, or check invoices. It cannot tell you whether a supplier's price is correct, only whether it changed between two files.