One price list for the website and quotes: why every price needs an effective date, why a quote stores a snapshot, and what the 17% to 18% VAT change showed.
Key takeaways
- A price with no effective date is not data, it is a momentary value. It cannot answer "what was the price on 3 December".
- A quote stores a snapshot of its lines: description, quantity, unit price and amount, as they stood at issue.
- The Israeli VAT rate moving from 17% to 18% on 1 January 2025 is the case that shows why recalculating at view time breaks old quotes.
- A customer-specific price is an override layer above the list, not a second list.
- The website and the quote tool read the same row. Two screens pulling from different files produce two truths inside a week.
A price list becomes a source of truth when two rules hold: every price carries an effective date, and every quote stores the price it was issued at rather than a reference to the current one. Without both, reprinting an old quote silently shows today's prices.
The two rules that make a price list a source of truth
Rule one: a price is not a field on the product record but a row in a table with a start date and an end date. Changing a price is not an update to the existing field; it is closing the active row and opening a new one.
Rule two: a document that left the building - quote, order, invoice - does not read the price list. It contains its own values. That way a quote issued in December keeps showing what was sent to the customer, five price-list updates later.
Both rules sound obvious, and most manual price lists violate both: one file, one price column, and an update that overwrites.
What does an effective-dated price row look like?
The table holds a row per period, not a row per product. From a parts price list:
| SKU | Description | Unit price before VAT | Valid from | Valid to |
|---|---|---|---|---|
| FLT-2040 | Oil filter 20/40 | 118.00 | 2024-07-01 | 2024-12-31 |
| FLT-2040 | Oil filter 20/40 | 125.00 | 2025-01-01 | open |
| BLT-0915 | Belt 9x15 | 64.50 | 2024-03-01 | open |
| BLT-0915 | Belt 9x15 | 71.00 | 2025-10-01 | open |
That last row is why the model earns its keep: a future price already sits in the table, approved, and takes effect on its own on 1 October. Nobody has to remember to update anything, and there is no window in which the website and the quotes disagree.
Three further fields earn their place on the row:
- Currency, even when everything is in shekels today. Adding it retroactively is expensive.
- Who approved it and when, so the question can be answered when it is asked.
- A short reason for the change - supplier increase, cost change, promotion.
The case: quoted in December, delivered in January
On 5 December 2024 a quote goes out for 10 units of FLT-2040. The price in force is 118.00 per unit, and the VAT rate is 17%.
| What is shown | Calculation | Amount |
|---|---|---|
| Net amount on the quote | 10 × 118.00 | 1,180.00 |
| VAT on the quote at 17% | 1,180 × 0.17 | 200.60 |
| Quote total | - | 1,380.60 |
| The same quote redisplayed in March with no snapshot | 10 × 125.00 plus 18% | 1,475.00 |
The gap is 94.40 on a single line, and it comes from two changes that have nothing to do with this customer: the price list rose on 1 January, and the VAT rate rose from 17% to 18% on the same date. A system that recalculates on every view shows the customer a different document from the one they received - and that surfaces precisely when they call with the printed version in hand.
Two implementation rules follow. The quote stores the VAT rate it was issued under, not only the prices. And every quote carries an expiry date, because effective-dated pricing turns "valid for 14 days" from a sentence in the footer into a rule the system can enforce.
Why can't a quote just point at the current price?
Because three different questions are asked of the same number, and only a snapshot answers all three: what was agreed with the customer, what the price is today, and what the price was on a given date. A reference answers only the second.
A snapshot also protects against a further case - a product deleted or replaced by a different SKU. A quote pointing at a deleted row shows a blank line or fails. A quote that stored the description and the price remains a valid document long after the product left the shelf.
Customer-specific prices without a second price list
The temptation is to open a separate file for wholesale customers. That produces two price lists which diverge within two months. The structure that works is layered:
- A base price per SKU, with effective dates.
- An override layer: a price or a discount percentage for a customer-SKU pair, with its own effective dates.
- A group layer: a discount for a customer group or a product category.
- One written precedence order: customer override, then group, then base.
- A display that shows on the quote which layer the price came from, so it can be explained.
The practical point: when the base price rises, overrides do not move with it. Somebody has to decide explicitly what happens to them, and that is exactly what gets forgotten when the price list is a file.
Publishing to the website and to quotes from the same row
- One source: both systems read the same table, not an export mailed around.
- The valid price is selected by date, not by "the last row".
- The website may show a VAT-inclusive price and the quote a net one - as long as both derive from the same base number.
- A price change goes through one approval path, even when it takes effect tomorrow.
- A price list maintained in a spreadsheet can remain the editing interface, provided it loads into the one source that everything reads from.
What breaks when the price list lives in a spreadsheet
- Two versions of the file in circulation, and nobody knows which one produced a given quote.
- A price update overwrites its predecessor, and the earlier value cannot be recovered.
- Two people update at once and a value is lost - see two people updating the same spreadsheet.
- Wholesale prices live on a separate tab that updates late.
- The website shows a price that was copied by hand once and never revisited.
Related: one identifier across quote, order and invoice and pricing and repeat-order controls for wholesalers.
Sources
Frequently asked questions
What do we do when a price changed retroactively?
Open a new row with an effective date in the past and leave the old one in history rather than overwriting it. You can then query which documents were issued in the affected window and decide on each. Overwriting the old row destroys exactly the information that review requires.
Should we store prices net or VAT-inclusive?
Store net, and store the VAT rate as a separate field on the document. That lets you show a consumer an inclusive price and a business a net one from the same base figure. Storing only the inclusive price turns every rate change into a backwards calculation with rounding that will not reconcile.
How long should price history be kept?
At least as long as documents from that period are open or auditable. Technically a price row is a tiny record and there is no reason to delete it: it is what lets you answer "why were we charged this" without digging through backups.
Can the price list stay in Excel?
Yes, as the editing interface. What cannot stay in Excel is the serving layer: the website and the quote tool need to read one source carrying effective dates. A spreadsheet that loads automatically into that source, with validation on the way in, is a perfectly reasonable compromise.
Keep reading
Related service
Business Automation
I build custom automations that remove repetitive work end to end.
About the author
Yehonatan Saadia
Freelance automation, web & MVP developer
I'm Yehonatan Saadia, a senior developer who builds business automation, custom websites, and MVPs for small and mid-sized companies across the US, Europe, and Israel. These guides come from real client work, not theory.
Work with meHave a project like this?
Tell me what you're trying to automate or build. I reply within 24 business hours with a few targeted questions, then we walk through it on a free 30-minute call, with no commitment. You come away with a scope, a timeline and a fixed price - or a straight answer that it isn't worth building.