Update a Price List with Effective Dates

A price list that only holds today's prices answers one question — what does this cost now? — and loses the answers to every other: what did it cost when this order was placed, what will it cost next month, when did the price last change? Price lists that keep history store each price with an effective from and an effective to date, so every product has a series of rows covering the calendar without gaps or overlaps. Maintaining such a list by hand in Excel is error-prone: someone adds a new price but forgets to close the old one, and for a while the product has two prices; or a change is entered with a date in last month, quietly rewriting prices that invoices were already based on. This page applies a sheet of price changes to a dated price list with pandas, closing current prices, adding new rows, rejecting changes that would break history, validating the result, and producing a report of what changed. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The price list holds one row per product per price period, with an open-ended To date of 31 December 9999 on each current price — a common convention that avoids blank end dates. The change sheet lists the new prices with the date each takes effect. Prices up to the end of October 2026 have already been invoiced, so that period is closed.

Step 1 — Read both and check the changes

import pandas as pd

OPEN = pd.Timestamp("9999-12-31")
CLOSED_UNTIL = pd.Timestamp("2026-10-31")                    # invoiced periods may not change

prices = pd.read_excel("price_list.xlsx", parse_dates=["From", "To"])
changes = pd.read_excel("price_changes.xlsx", parse_dates=["Effective from"]).sort_values(["Product", "Effective from"])

def change_problem(c):
    if c["New price"] <= 0:
        return "price must be positive"
    if c["Effective from"] <= CLOSED_UNTIL:
        return f"backdated into a closed period (closed until {CLOSED_UNTIL:%d %b %Y})"
    return None

changes["Problem"] = changes.apply(change_problem, axis=1)
rejected = changes[changes["Problem"].notna()]
accepted = changes[changes["Problem"].isna()]
print(rejected[["Product", "Effective from", "New price", "Problem"]].to_string(index=False))

Every change is checked before anything is applied. A price of zero or less is rejected — it is almost always a blank cell or a typing slip, and a genuinely free product needs a deliberate decision. A change effective on or before the end of the closed period is rejected because it would rewrite prices that have already been invoiced; a correction to a past price is a credit-note matter, not a price-list edit. Here the cable's price cut dated August and the gadget row with a zero price are rejected with their reasons, and the other five changes go ahead. Sorting the changes by product and date matters for the next step: two changes to the same product — the second widget gets one in November and another in December — must be applied in date order.

Step 2 — Close the current price and add the new one

def apply_change(prices, product, effective, new_price):
    prices = prices.copy()
    current = (prices["Product"] == product) & (prices["To"] == OPEN)
    if current.any():
        start = prices.loc[current, "From"].iloc[0]
        if effective <= start:
            raise ValueError(f"{product}: new price from {effective:%d %b %Y} is not after the current price's start {start:%d %b %Y}")
        prices.loc[current, "To"] = effective - pd.Timedelta(days=1)
    new_row = pd.DataFrame([{"Product": product, "From": effective, "To": OPEN, "Price": new_price}])
    return pd.concat([prices, new_row], ignore_index=True)

before = prices.copy()
for c in accepted.itertuples(index=False):
    prices = apply_change(prices, c.Product, c._1, c._2)
prices = prices.sort_values(["Product", "From"]).reset_index(drop=True)
print(prices.assign(From=prices["From"].dt.date, To=prices["To"].dt.date).to_string(index=False))

Applying a change is two edits. The product's current row — the one with the open end date — is closed the day before the new price takes effect, and a new row is added starting on the effective date with an open end. A product that has no current price, such as the new product NEW-9, simply gets the new row. One more check guards against a change dated before the start of the current price, which would leave the current row ending before it began. The second widget shows why order matters: the November change closes the 8.40 price on 31 October and opens 8.90; the December change then closes 8.90 on 30 November and opens 9.10, giving three consecutive periods.

Step 3 — Prove the list is still consistent

def check_periods(prices):
    issues = []
    for product, g in prices.groupby("Product"):
        g = g.sort_values("From")
        if (g["To"] < g["From"]).any():
            issues.append(f"{product}: a row ends before it starts")
        gaps = g["From"].iloc[1:].values - (g["To"].iloc[:-1] + pd.Timedelta(days=1)).values
        if (gaps > pd.Timedelta(0)).any():
            issues.append(f"{product}: gap between price periods")
        if (gaps < pd.Timedelta(0)).any():
            issues.append(f"{product}: overlapping price periods")
        if (g["To"] == OPEN).sum() != 1:
            issues.append(f"{product}: {(g['To'] == OPEN).sum()} open-ended prices")
    return issues

print("issues:", check_periods(prices) or "none")

After the changes, the whole list is validated, not just the rows that were touched. For every product the periods, sorted by start date, must chain exactly: each period starts the day after the previous one ends, so there are no gaps — days with no price — and no overlaps — days with two. Every product must also have exactly one open-ended row, its current price. Running the same check on the original list before changes is worth doing once, because hand-maintained lists often have problems that predate any automation; this one passes before and after. If it had not, the issue list names the product and the kind of problem, which is enough to find the faulty row in a list of thousands.

Step 4 — Prices on any date, and the change report

def price_on(prices, product, day):
    day = pd.Timestamp(day)
    hit = prices[(prices["Product"] == product) & (prices["From"] <= day) & (prices["To"] >= day)]
    return hit["Price"].iloc[0] if len(hit) else None

for day in ["2026-10-15", "2026-11-15", "2026-12-15", "2027-01-15"]:
    print(day, {p: price_on(prices, p, day) for p in ["WID-A", "WID-B", "GAD-X", "NEW-9"]})

report = accepted.assign(**{"Old price": [price_on(prices, p, d - pd.Timedelta(days=1)) for p, d in zip(accepted["Product"], accepted["Effective from"])]})
report["Change %"] = ((report["New price"] / report["Old price"] - 1) * 100).round(1)
with pd.ExcelWriter("price_list_updated.xlsx") as xw:
    prices.to_excel(xw, sheet_name="Price list", index=False)
    report.drop(columns="Problem").to_excel(xw, sheet_name="Changes applied", index=False)
    rejected.to_excel(xw, sheet_name="Changes rejected", index=False)
print(report[["Product", "Effective from", "Old price", "New price", "Change %"]].to_string(index=False))

With history kept, the price of any product on any date is a filter for the row whose period contains the date — the lookup an order or invoice system makes, and the one used to re-price past orders correctly. Asking for four dates shows the changes taking effect: the widget at 12.60 until the end of October and 13.10 after, the second widget stepping from 8.40 to 8.90 to 9.10, the gadget unchanged until its January increase. The change report lists every accepted change with the price it replaces — read from the updated list the day before the change, so the December change to the second widget is shown against November's 8.90 rather than the original 8.40 — and the percentage increase, which is the summary sales and customer service need before the new prices go live, and the rejected changes go back to whoever submitted them. For joining whole tables of orders to dated prices, see Join Rows to Effective Date Ranges with pandas.

Common pitfalls

• Adding a new price without closing the old one. The product then has two prices; close the current row first.
• Backdated changes. Reject changes inside invoiced periods; correct the past with credit notes.
• Applying changes out of order. Sort by product and date before applying.
• Blank end dates. Use a far-future date such as 9999-12-31 for the current price.
• Checking only the edited rows. Validate the whole list for gaps, overlaps and open rows.

Conclusion

To update a price list that keeps history, check each change — positive price, not backdated into a closed period — then, in date order, close the product's current row the day before the change takes effect and add a new open-ended row. Validate the whole list so every product's periods chain without gaps or overlaps and exactly one is open, look prices up by finding the period that contains a date, and report each change with the price it replaces.

Frequently asked questions

How do I store prices with effective dates? One row per product and period with from and to dates, using a far-future to date such as 9999-12-31 for the current price.

How do I add a new price without losing the old one? Set the current row's end date to the day before the new price's effective date and add a new row from that date.

How do I check a dated price list for overlaps? Sort each product's rows by start date and check that each period starts exactly one day after the previous one ends.

How do I find a product's price on a given date? Select the row where the product matches and the date lies between from and to.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Join Rows to Effective Date Ranges with pandas — pricing many orders at once.
• Model the Impact of a Price Increase — deciding the new prices.
• Check Supplier Invoices Against Contract Prices — dated prices on the buying side.