Price lists by currency: charm endings, cash rounding and a 2% reprice band, measured

Pilotbase showing exact currency conversions and local price-list rules for euro, dollar, pound, yen and Bahraini dinar

Converting a €16.99 base price every morning sounds tidy. It is also a good way to make thousands of labels, feeds and cached pages change for no useful reason. A local price list has a different job from an exchange-rate report: it should look natural in that currency and stay put until the rate has moved enough to matter.

We tested that trade-off with 50,000 seeded products. Their euro prices ran from €0.99 to €3,999.99. We made lists in EUR, USD, GBP, PLN, CHF, SEK, JPY, HUF and BHD from the first ECB fixing of 2025, then walked through all 255 fixings to 31 December. PostgreSQL 18.7 and MariaDB 11.8.9 produced the same prices and repricing counts, all tied out against Python Decimal.

One base price, one stored list price

Keep the base catalogue separate from the published lists. The list row remembers both the rate and date used. That lets a later job ask how far the current rate has moved without changing the price on every read.

sql

create table base_prices (
  sku bigint primary key,
  eur decimal(18,2) not null
);

create table price_rules (
  ccy char(3) primary key,
  rate decimal(18,8) not null,       -- currency units per EUR
  unit decimal(18,3) not null,       -- distance between charm points
  charm decimal(18,3) not null,      -- subtract from the rounded point
  cash_step decimal(18,3) not null,
  decimals smallint not null
);

create table price_lists (
  sku bigint not null,
  ccy char(3) not null,
  price decimal(18,3) not null,
  rate_used decimal(18,8) not null,
  listed_on date not null,
  primary key (sku, ccy)
);

Our rules were .99 for USD, GBP and PLN; .95 for CHF; values ending in 9 for SEK; ...980 for JPY; ...95 for HUF; and .950 for BHD. Cash increments were CHF 0.05, HUF 5 and BHD 0.005. These are example commercial rules, not a claim that every shop in a country prices the same way.

The price expression

For a charm price, round the converted amount to the nearest charm point. For a cash-only price, round to the nearest cash step. Keep every operand DECIMAL; a floating-point rate brings back the rounding problems covered in Money in SQL.

sql

insert into price_lists (sku, ccy, price, rate_used, listed_on)
select b.sku, r.ccy,
       cast(greatest(
         r.unit - r.charm,
         round((b.eur * r.rate + r.charm) / r.unit, 0) * r.unit - r.charm
       ) as decimal(18,3)) as price,
       r.rate, date '2025-01-02'
from base_prices b
cross join price_rules r;

-- If this currency has no charm ending:
round((b.eur * r.rate) / r.cash_step, 0) * r.cash_step

The greatest() is a floor. Without it, the cheapest converted value can snap to a negative charm point. A floor avoids that bug, but a fixed unit can still be far too coarse for a cheap item. We come back to that below.

What the first five products looked like

EUR	USD	GBP	PLN	CHF	SEK	JPY	HUF	BHD
8.99	8.99	6.99	37.99	7.95	99	1,480	3,695	3.950
16.99	17.99	13.99	72.99	15.95	199	2,780	6,995	6.950
143.99	148.99	119.99	615.99	134.95	1,649	23,380	59,495	55.950

The BHD rates were not published by the ECB. We derived them from each day's ECB USD rate using Bahrain's official peg of 0.376 BHD per US dollar: bhd_per_eur = usd_per_eur * 0.376. Store the source on the rate row so a derived rate is never mistaken for a direct ECB quote.

Daily conversion changes too many prices

There were 254 day-to-day steps after the first fixing. Recomputing every list every business day changed between 5.7% and 28.1% of the 50,000 charm prices per currency per day. Cash-only rounding changed about 93% per day in USD, and nearly as many in most other currencies.

Currency	Charm prices changed	Average per day	Cash-only changed
USD	1,813,174	7,139 (14.3%)	11,804,683
GBP	964,270	3,796 (7.6%)	11,374,358
PLN	3,249,252	12,792 (25.6%)	12,370,279
CHF	934,297	3,678 (7.4%)	7,468,069
SEK	1,399,504	5,510 (11.0%)	6,761,532
JPY	2,798,994	11,020 (22.0%)	12,266,478
HUF	3,567,857	14,047 (28.1%)	11,096,253
BHD	722,791	2,846 (5.7%)	11,571,049

Across all eight currencies, daily repricing caused 2,032 currency events, considered 101.6 million SKU rows and changed 15,450,139 prices. Most of the work did not change the published result because the charm snap absorbed the small rate move.

A 2% band cut 2,032 events to 44

A band compares today's rate with the rate stored on the list. Reprice on the first fixing where the drift is greater than the band, then store that new rate. The important detail is that the comparison is against rate_used, not yesterday's rate; otherwise many small moves never add up.

sql

-- f.rate is today's rate; p.rate_used is the rate behind the list
select p.ccy, count(*) as rows_due
from price_lists p
join fx f on f.ccy = p.ccy and f.rate_date = :rate_date
where abs(f.rate / p.rate_used - 1) > :band
group by p.ccy;

-- Correct denominator for the measured gap (keep both factors bracketed):
100 * (p.price - ((b.eur * f.rate))) / ((b.eur * f.rate))

Band	Currency repricings	Rows touched	Prices changed	Changed per touched
Daily	2,032	101,600,000	15,450,139	15.2%
0.5%	433	21,650,000	7,307,872	33.8%
1%	159	7,950,000	3,572,627	44.9%
2%	44	2,200,000	1,324,213	60.2%
5%	9	450,000	366,621	81.5%

At 2%, USD and pegged BHD repriced 11 times each; JPY 7, SEK 6, HUF 3, and CHF, GBP and PLN 2 each. April was the busiest month, with 9 of the year's 44 events. A 2% band is not universally right, but it is a useful starting point: in this year it removed 97.8% of daily events and 91.4% of actual price changes.

Fixed charm units break at the cheap end

Across all 50,000 products, mean charm gaps stayed between -0.68% and +0.67%. That average hides the cheap products. GBP ranged from -19.93% to +20.31%. SEK reached -20.41%. The BHD floor was the worst: a €0.99 product was worth about 0.38 BHD, but the minimum charm point was 0.950 BHD, a +147.27% gap.

Currency	Mean gap	Minimum	Maximum	Cash-only mean absolute gap
USD	-0.68%	-3.11%	+2.95%	0.017%
GBP	-0.45%	-19.93%	+20.31%	0.022%
CHF	+0.43%	-5.63%	+6.18%	0.091%
SEK	+0.28%	-20.41%	+14.19%	0.148%
JPY	-0.00%	-13.17%	+12.21%	0.013%
BHD	+0.67%	-18.13%	+147.27%	0.022%

Use price tiers. A cheap BHD item might use a 0.100 unit and 0.050 charm; a more expensive item can use 1.000 and 0.050. Or give every currency an explicit minimum local price and send anything below it to review. One charm unit for the whole catalogue is simple SQL, not a sound pricing rule.

Preparing the ECB file

The ECB history download is a wide CSV: one date column and one column per currency, quoted as currency units per euro. Before loading it, csvProfiler will show missing and non-numeric values; csvToSql will create exact DECIMAL columns and the load statements. Unpivot it to (rate_date, ccy, rate) before joining it to rules.

For the currency exponent, rate-by-date table and weekend lookup, see Several currencies in one table. It also explains why an ECB fixing is a reference rate, not necessarily the rate your payment provider gives you.

A practical job schedule

1. Import the latest fixing after it is published.
2. Compare each currency's fixing with rate_used.
3. Rebuild only currencies outside the band.
4. Update only rows whose snapped price changed.
5. Save the old and new price, rate and date in an audit table.
6. Review prices near your minimum and maximum gap limits before sending the feed.

The measured initial insert of 400,000 list rows took 3.6-4.8 seconds on PostgreSQL and 3.3-3.9 seconds on MariaDB. A 50,000-row band update had median times of 0.59-0.76 seconds and 0.68-0.87 seconds respectively. Those are indicative timings from one run; the counts and exact prices are the more portable result.

Run the price-list queries and inspect the exact decimal results in Pilotbase, a database client for PostgreSQL, MariaDB, MySQL, SQLite and more.

Get Pilotbase

Profile the ECB CSV in your browser before you load it: missing values, distinct counts, dates and numeric ranges stay on your machine.

Open csvProfiler

Sources: European Central Bank reference rates and historical CSV (rates quoted against the euro; reference rates are informational) and Central Bank of Bahrain, exchange-rate table (0.376 BHD per USD official peg). Measurements: our seeded 50,000-SKU run on PostgreSQL 18.7 and MariaDB 11.8.9 using 255 ECB fixings from 2 January to 31 December 2025; catalogue seed 1006; run completed 14 November 2026. Not included: bank spreads, tax-inclusive prices, tiered charm units, MySQL 8.4 or SQLite.