Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Compare Supplier Quotes with Python

Compare supplier quotes that arrive as differently laid-out Excel files: map each supplier's columns to one schema, convert currencies and pack sizes to a per-unit price, apply quantity price breaks with merge_asof, add delivery and minimum-order rules, score price, lead time and payment terms with weights, and write a comparison matrix that highlights the best offer per item.
Report layouts & templatesJoins, lookups & comparisons

Purchasing sends a request for quotation to three suppliers and gets back three spreadsheets that cannot be compared side by side. One prices per unit in euros, another per box of ten in pounds; one has quantity price breaks, another a minimum order and a delivery charge; their column names, item codes and lead times are all in different places. Building the comparison by hand means re-keying and recalculating — exactly where a pack size or exchange rate gets missed and the "cheapest" supplier turns out not to be. With pandas, each quote is mapped once into a common schema, prices are normalised to a landed cost per unit at the quantity you actually need, and a weighted score combines price with lead time and payment terms. The result is a comparison workbook a buyer can defend. It belongs to Building Finance and Operations Reports.

Prerequisites

Bash
pip install pandas openpyxl

The quotes/ folder holds three replies to a request for 300 boxes of gloves, 120 high-visibility vests and 60 rolls of tape. Acme quotes per unit in euros with price breaks at 200 and 500 boxes; Brightline quotes per pack in pounds; Cobalt quotes per unit in euros with minimum order quantities. Payment terms and delivery charges come from the covering emails and are entered in a small table in Step 4.

Step 1 — Map each supplier to one schema

Write down, once per supplier, which of their columns means what, and how their prices are expressed. Everything after this step works on one shape:

Python
import pandas as pd

SUPPLIERS = {
    "Acme Supplies": {"file": "quotes/acme_supplies.xlsx", "currency": "EUR",
                      "cols": {"Item code": "item", "From qty": "from_qty", "Unit price EUR": "price",
                               "Lead time (days)": "lead_days"}},
    "Brightline Ltd": {"file": "quotes/brightline_ltd.xlsx", "currency": "GBP",
                       "cols": {"Our ref": "item", "Pack size": "pack", "Price per pack GBP": "price",
                                "Delivery (working days)": "lead_days"}, "working_days": True},
    "Cobalt Trading": {"file": "quotes/cobalt_trading.xlsx",
                       "cols": {"SKU": "item", "Price": "price", "Currency": "currency", "MOQ": "moq", "Lead": "lead_days"}},
}

def load(name, spec):
    df = pd.read_excel(spec["file"]).rename(columns=spec["cols"])[list(spec["cols"].values())]
    df["supplier"] = name
    df["currency"] = df.get("currency", spec.get("currency"))
    df["from_qty"] = df.get("from_qty", 1)
    df["pack"] = df.get("pack", 1)
    df["moq"] = df.get("moq", 0)
    if spec.get("working_days"):
        df["lead_days"] = (df["lead_days"] * 7 / 5).round().astype(int)    # working → calendar days
    return df

quotes = pd.concat([load(n, s) for n, s in SUPPLIERS.items()], ignore_index=True)
print(quotes[["supplier", "item", "from_qty", "pack", "price", "currency", "moq", "lead_days"]].to_string(index=False))

Missing concepts get neutral defaults — no price breaks means a single tier from quantity 1, no pack size means 1, no minimum order means 0 — so every supplier ends up with the same columns. Brightline's lead time is in working days and is converted to calendar days so the three are comparable. Adding a fourth supplier is one more entry in SUPPLIERS, which keeps the mapping readable and reviewable.

Three quote layouts, one schema Each supplier names its columns differently and expresses price differently. A per-supplier mapping renames columns to item, from quantity, pack, price, currency, minimum order and lead days, filling neutral defaults where a supplier has no such concept. A B 1 Supplier Their columns 2 Acme Item code · From qty · Unit price EUR 3 Brightline Our ref · Pack size · Price per pack GBP 4 Cobalt SKU · Price · Currency · MOQ column default item required from_qty 1 pack 1 moq 0 as received common schema SUPPLIERS mapping

Step 2 — Normalise to a price per unit in one currency

Prices now share column names but not meaning: per pack versus per unit, pounds versus euros. Convert everything to euros per single unit:

Python
fx_to_eur = {"EUR": 1.0, "GBP": 1.16}

quotes["unit_eur"] = (quotes["price"] * quotes["currency"].map(fx_to_eur) / quotes["pack"]).round(4)
assert quotes["unit_eur"].notna().all(), "missing exchange rate"
print(quotes.loc[quotes["from_qty"] == 1, ["supplier", "item", "price", "currency", "pack", "unit_eur"]].to_string(index=False))

Brightline's £58.00 for a pack of ten boxes of gloves becomes €6.728 per box — cheaper than Acme's list price of €7.90, which would not have been obvious from the raw quotes. Note what "unit" means: here it is one box of gloves, one vest, one roll of tape, matching the request. When suppliers quote different base units (per metre versus per 50 m roll), convert the pack size accordingly; the formula does not change.

Step 3 — Apply price breaks at the quantity needed

Price-break tables say "from 200 boxes, €7.20". For each item, the applicable tier is the last one whose starting quantity is at or below the order quantity — exactly what merge_asof does:

Python
need = pd.DataFrame({"item": ["GL-100", "HV-220", "TP-050"], "qty": [300, 120, 60]})
priced = pd.concat([
    pd.merge_asof(need.sort_values("qty"), grp.sort_values("from_qty"),
                  left_on="qty", right_on="from_qty", by="item", direction="backward")
    for _, grp in quotes.groupby("supplier")
], ignore_index=True).dropna(subset=["supplier"])
priced["order_qty"] = priced[["qty", "moq"]].max(axis=1)
priced["order_qty"] = (-(-priced["order_qty"] // priced["pack"]) * priced["pack"]).astype(int)
priced["goods_eur"] = (priced["order_qty"] * priced["unit_eur"]).round(2)
print(priced[["item", "supplier", "qty", "order_qty", "unit_eur", "goods_eur"]].sort_values(["item", "supplier"]).to_string(index=False))

merge_asof matches each requested quantity to the last tier whose starting quantity is at or below it, within the same item (by="item"); running it once per supplier and concatenating the results gives one row per supplier and item — nine rows for three items and three suppliers. A single merge_asof over all suppliers at once would pick just one supplier's tier per item, which is the classic mistake here. Acme's gloves at 300 boxes fall in the 200+ tier at €7.20. Quantities are then raised to any minimum order and rounded up to whole packs, because that is what you would actually be invoiced for: Brightline's 300 boxes are exactly 30 packs, while Cobalt's minimum of 100 rolls means 60 rolls of tape cost the price of 100, and its gloves minimum forces 400 boxes instead of 300. As-of joins in general are covered in Match Rows by Nearest Date with merge_asof.

Step 4 — Add delivery, terms and a weighted score

Price is not the only criterion. Bring in the terms from the covering emails, compute the landed total per supplier, and score each supplier on price, lead time and payment terms with agreed weights:

Python
terms = pd.DataFrame({
    "supplier": ["Acme Supplies", "Brightline Ltd", "Cobalt Trading"],
    "delivery_eur": [45.0, 0.0, 120.0],
    "payment_days": [30, 60, 14],
})
WEIGHTS = {"price": 0.6, "lead": 0.25, "terms": 0.15}

summary = (priced.groupby("supplier").agg(goods_eur=("goods_eur", "sum"), lead_days=("lead_days", "max"))
                 .join(terms.set_index("supplier")))
summary["total_eur"] = summary["goods_eur"] + summary["delivery_eur"]

def score_low(s):  return (s.min() / s * 100).round(1)    # cheapest / fastest = 100
def score_high(s): return (s / s.max() * 100).round(1)    # longest payment terms = 100

summary["score"] = (WEIGHTS["price"] * score_low(summary["total_eur"]) +
                    WEIGHTS["lead"] * score_low(summary["lead_days"]) +
                    WEIGHTS["terms"] * score_high(summary["payment_days"])).round(1)
print(summary[["goods_eur", "delivery_eur", "total_eur", "lead_days", "payment_days", "score"]].sort_values("score", ascending=False).to_string())

Each criterion is scaled so the best supplier scores 100 and the others proportionally less — lowest total and shortest lead time for "low is good", longest payment terms for "high is good" — then combined with the weights. The result is closer than the raw quotes suggest. Brightline has the lowest landed total, €2,738.76 against Acme's €2,895.00, and the best payment terms — but Acme delivers in 7 days instead of 14, and with lead time weighted at 25% that is enough for Acme to edge ahead, 89.3 to 87.5. Cobalt's minimum orders and delivery charge push its total to €3,686 and its score to 60.6. A two-point margin is a signal to look at the weights, not a verdict: if delivery speed matters less for this order, Brightline wins. Keeping the weights in one dictionary, and printing them in the report, makes the decision transparent; buyers can rerun with different weights in seconds to see whether the answer is sensitive to them.

Weighted supplier scores Acme Supplies scores 89.3, helped by the fastest delivery. Brightline Ltd scores 87.5 with the lowest landed total and the longest payment terms but slower delivery. Cobalt Trading scores 60.6 because minimum orders and a delivery charge raise its total. Acme Supplies 89.3 · fastest delivery Brightline Ltd 87.5 · lowest total, best terms Cobalt Trading 60.6 · MOQ + delivery

Step 5 — Write the comparison matrix

The buyer's view is a matrix: items down the side, suppliers across, the goods cost of the required quantity in each cell with the cheapest highlighted, and the summary underneath:

Python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

matrix = priced.pivot_table(index="item", columns="supplier", values="goods_eur", aggfunc="sum")
with pd.ExcelWriter("supplier_comparison.xlsx", engine="openpyxl") as xw:
    matrix.to_excel(xw, sheet_name="Comparison", startrow=1)
    summary.reset_index().to_excel(xw, sheet_name="Comparison", startrow=len(matrix) + 5, index=False)
    priced.to_excel(xw, sheet_name="Detail", index=False)
    pd.DataFrame(list(WEIGHTS.items()), columns=["criterion", "weight"]).to_excel(xw, sheet_name="Weights", index=False)
    ws = xw.sheets["Comparison"]
    ws["A1"] = "Goods cost per item at required quantity (EUR)"
    ws["A1"].font = Font(bold=True)
    first, last = 3, 2 + len(matrix)
    ws.conditional_formatting.add(f"B{first}:D{last}", FormulaRule(
        formula=[f"B{first}=MIN($B{first}:$D{first})"], fill=PatternFill("solid", fgColor="C6EFCE")))
    for col in "ABCDEFGH":
        ws.column_dimensions[col].width = 16
print(matrix.round(2).to_string())
print("winner:", summary["score"].idxmax())

The conditional format compares each cell with the minimum of its row, so the cheapest supplier for each item is shaded green — and it stays correct if a buyer edits a figure. The summary with totals, terms and scores sits below, and the Detail and Weights sheets document how every number was reached. Highlighting per row this way is covered in Highlight Top and Bottom Values with openpyxl.

From quotes to a decision Each quote is mapped to a common schema, prices are converted to euros per unit, the right price break is applied at the required quantity with minimum orders and pack rounding, delivery and terms are added, and suppliers are scored with agreed weights. Map columns one entry per supplier Normalise price EUR per unit Price breaks merge_asof, MOQ, packs Score price 60 · lead 25 · terms 15

Common pitfalls

  • Comparing per-pack with per-unit prices. Divide by pack size before comparing.
  • Ignoring minimum order quantities. The price you pay is for what you must order.
  • One merge_asof across suppliers. Match price breaks per supplier, or suppliers disappear.
  • Price-only decisions. Weigh lead time and payment terms explicitly — here the cheapest supplier did not win.
  • Hidden weights. Put the weights in the report so the choice can be reviewed.

Conclusion

Comparing supplier quotes in Python is mostly normalisation: map each supplier's columns to a common schema, convert prices to one currency per single unit, apply quantity price breaks per supplier with merge_asof, and round up to whole packs and minimum orders. Add delivery charges and payment terms, score price, lead time and terms with explicit weights, and write a comparison matrix that highlights the cheapest option per item, with the detail and weights alongside.

Frequently asked questions

How do I compare supplier quotes in different Excel formats? Define a column mapping per supplier, rename each quote's columns to a common schema with neutral defaults for missing fields, and concatenate them into one DataFrame.

How do I apply quantity price breaks in pandas? Sort the tiers by starting quantity and use merge_asof with direction backward, matching each supplier separately, so each order quantity gets the last tier at or below it.

How do I account for minimum order quantities? Take the larger of the needed quantity and the minimum order, round it up to whole packs, and price that quantity, since that is what will be invoiced.

How do I score suppliers on more than price? Scale each criterion so the best supplier scores 100, such as lowest total cost or longest payment terms, and combine the scores with agreed weights.

More on report layouts & templates

More on report layouts & templates →