Tender comparison

Tender comparison

Turn a pile of vendor tenders for one job into a single Excel file that compares every line item side by side, highlights the lowest rate per item, and ranks the vendors from cheapest to dearest.

The vendors bid on the same work but format their tenders differently. They name items differently, group them differently, and quote in different orders. The job is to line them up item by item, honestly, with nothing dropped.

When to use this

Use this when someone gives you several vendor quotes for the same construction or fit-out job and asks which is cheapest, or asks for an item-by-item comparison, a rate comparison, or a tender analysis. It works for civil work, electrical work, interior furnishing, plumbing, and similar itemised bids.

If there is only one tender, there is nothing to compare. Say so.

What you produce

One .xlsx file. It has a Summary sheet and one comparison sheet per tender. Each comparison sheet has one row per item and, for each vendor, a Rate column and an Amount column. The lowest rate in each row is highlighted green. A totals row, a net payable row, and an L1 to Ln ranking sit at the bottom. This is the required default output.

Setup

The scripts need Python with openpyxl (for Excel) and pymupdf (for reading scanned PDFs).

pip install openpyxl pymupdf

Workflow

Do these steps in order. Do not skip the reading step and do not skip the self-check.

1. Identify the tenders

List the input files. Work out which files are separate tenders and which belong together. Two things to settle first:

• Separate jobs. One folder can hold more than one job. For example an electrical tender and an interior tender for the same branch are two different item lists. Compare each job on its own sheet. Never mix items from different jobs into one table.
• Duplicate scans. The same tender is sometimes scanned twice under two file names. Check the vendor name and the rates. If they match, count it once and note the duplicate.

2. Read every tender

Most bank and BOQ tenders are scanned images with no text layer, so text extractors return nothing. Render each page to an image and read it by eye.

python scripts/render_tender.py --indir "input" --outdir "pages" --dpi 200

Then read each page image. If a handwritten rate is hard to read, zoom into that one cell at higher DPI with the --crop option (see reference/extraction.md).

Read the full detail in reference/extraction.md. The short version:

• Capture the rate for every item from every vendor. Nothing skipped.
• After reading a tender, cross-check it: the sum of (rate x quantity) for all items should equal the grand total written on that tender. If it does not, find out why before moving on. A mismatch is either your misread or a slip in the vendor's own arithmetic. Both need to be understood, not guessed.

3. Build one canonical item list and match every vendor to it

Read reference/item-matching.md for how to match items that are named or grouped differently.

Build a single master list of items for each job. Then map each vendor's quoted rate onto the matching master item. Many public tenders are pre-printed, so every vendor bids on the same fixed item list in the same order, which makes matching a question of position. Free-form quotes need matching by description, size, and unit.

If a vendor did not quote an item, record it as not quoted. Do not treat a blank as zero and do not drop the item.

4. Write a tender JSON for each job

Put the master list and every vendor's rates into one JSON file per job, in the schema shown in examples/electrical.json and examples/interior.json. The schema is documented in reference/output-format.md.

5. Cross-check the JSON (stop gate)

python scripts/cross_check.py job1.json job2.json

This adds up rate times quantity for each vendor and compares it to the grand total that vendor wrote. It is the main guard against a misread rate.

Stop gate: do not go to step 6 until it prints CROSS-CHECK PASSED. If it reports an UNEXPLAINED discrepancy, you misread a number. Re-read that tender, zoom into the suspect cell, and fix the JSON. If the difference is a genuine slip in the vendor's own arithmetic, confirm it by eye, then add a total_note to that vendor in the JSON to record the reason. See reference/edge-cases.md.

6. Build the Excel file

python scripts/build_comparison.py --out "Tender Comparison.xlsx" job1.json job2.json

7. Self-check before you hand it over (stop gate)

python scripts/self_check.py --xlsx "Tender Comparison.xlsx" job1.json job2.json

This re-reads the saved file and confirms: every item is present once, every amount equals rate times quantity, every green highlight is the true lowest, the net payable formulas are right, and the ranking is correct.

Stop gate: do not present the file until it prints ALL CHECKS PASSED. If it reports a vendor arithmetic slip as info, that is expected when a total_note is set. Add a matching note to the sheet.

8. Report the result

Tell the user, per job, who is L1 (lowest overall) and by how much they beat L2. Point out any items where the overall lowest vendor is not the cheapest, since a client may still split the award. Mention anything you flagged: missing items, discounts, credits, duplicate files, arithmetic slips.

Hard rules

• Nothing is dropped and nothing is silently ignored. Every item from every vendor is on the sheet. A missing quote is flagged, not hidden.
• Every number is traceable to a tender. Do not invent or round rates.
• Cross-check each vendor's total against their own stated total. Explain any difference.
• The quoted rate governs. If a vendor's own total is miscalculated, the rate is the truth and the corrected total is used, with a note.
• Read the edge cases in reference/edge-cases.md: blanket discounts, buyback credits (negative amounts), duplicate scans, arithmetic slips, missing items, and quantities that differ between vendors.

Scripts

• scripts/render_tender.py - render tender PDFs to page images, or zoom into one cell.
• scripts/cross_check.py - step 5 gate. Ties each vendor's rates to their stated total. Run on the JSON before building.
• scripts/build_comparison.py - step 6. Turns the tender JSON into the formatted Excel file.
• scripts/self_check.py - step 7 gate. Validates the built workbook against the JSON.

Reference files

• reference/extraction.md - reading scanned tenders, zooming into cells, the total cross-check.
• reference/item-matching.md - matching items that vendors name or group differently.
• reference/output-format.md - the JSON schema and the exact Excel layout.
• reference/edge-cases.md - discounts, credits, duplicates, slips, missing items, mismatched quantities.
• examples/ - a full worked pair of tender JSON files (electrical and interior) you can copy.