Multi-Currency Systems: Engineering Money Across Borders

The $0.01 Bug That Cost Millions

A trading platform stored prices as floating-point numbers. On one trade, 0.1 + 0.2 evaluated to 0.30000000000000004 instead of 0.3. Across millions of transactions per day, these rounding errors accumulated into real money: some accounts gained fractions of cents, others lost them. The total discrepancy reached $2.3 million before anyone noticed. The fix required reprocessing 18 months of transactions.

Rule 1: Never Use Floating Point for Money

Floating Point Disasters:

  Language       Expression             Result
  -----------    --------------------   ----------------------
  Python         0.1 + 0.2              0.30000000000000004
  JavaScript     0.1 + 0.2              0.30000000000000004
  Java (double)  0.1 + 0.2              0.30000000000000004

  The fix: store money as integers in smallest denomination

  Currency   Amount    Stored As    Subunit
  --------   -------   ----------   --------
  USD        $10.99    1099         cents (10^-2)
  EUR        9.50      950          cents (10^-2)
  JPY        1500      1500         no subunit (10^0)
  BHD        5.123     5123         fils (10^-3)
  BTC        0.0001    10000        satoshi (10^-8)

  Data type: BIGINT (not INT - overflows at $21M in cents)
  Column: amount BIGINT + currency CHAR(3)

Exchange Rate Architecture

Exchange Rate Handling:

  [Rate Provider]  (Open Exchange Rates, ECB, Bloomberg)
       |
  Poll every 60s (e-commerce) or every 100ms (trading)
       |
  [Rate Cache]  rates stored with timestamp
       |
  rate_snapshots table:
  +----------+----------+----------+--------------------+
  | from_ccy | to_ccy   | rate     | fetched_at         |
  +----------+----------+----------+--------------------+
  | USD      | EUR      | 0.9234   | 2024-01-15 14:30:01|
  | USD      | JPY      | 148.52   | 2024-01-15 14:30:01|
  +----------+----------+----------+--------------------+

  Critical rules:
  1. SNAPSHOT the rate at transaction time
     Store rate_used IN the transaction record
     Auditors need: 'what rate was applied?'

  2. NEVER re-convert stored amounts
     Once $10.99 is stored, it stays $10.99
     Conversions are explicit, one-time operations

  3. ADD a margin (1-3%) to mid-market rate
     Your actual exchange cost is never the mid-market rate

  4. Transaction record:
     amount: 1099, currency: USD
     converted_amount: 1015, converted_currency: EUR
     exchange_rate: 0.9234, rate_timestamp: 2024-01-15T14:30:01Z

Currency-Aware Rounding

Rounding Rules by Currency (ISO 4217):

  Currency   Code   Exponent   Rounding
  --------   ----   --------   -------------------------
  US Dollar  USD    2          Round to nearest cent
  Euro       EUR    2          Round to nearest cent
  Yen        JPY    0          Round to whole units
  Dinar      BHD    3          Round to 3 decimal places
  Bitcoin    BTC    8          Round to satoshi

  Common mistake: hardcoding 2 decimal places for all currencies
  JPY 1500.49 should round to 1500, not 1500.49
  BHD 5.1234 should round to 5.123, not 5.12

  Use libraries that know ISO 4217:
  - Python: py-moneyed, babel
  - JavaScript: Dinero.js, currency.js
  - Java: Joda-Money, JSR 354
  - Ruby: money-rails

Interview Tip

When asked about handling money in a system, say: 'I would store all monetary values as BIGINT integers in the smallest denomination (cents for USD, satoshi for BTC) with a CHAR(3) currency code per ISO 4217. Never use float or double. For multi-currency, I would snapshot the exchange rate at transaction time and store it in the transaction record for auditability. Rounding rules vary by currency (JPY has 0 decimals, BHD has 3), so I would use a currency-aware library like Dinero.js rather than hardcoding 2 decimal places.'

<

/blockquote>

Key Takeaway

Store money as integers in the smallest denomination. Never use floating point. Always save the exchange rate at transaction time for auditability. Use ISO 4217 for currency codes and respect per-currency rounding rules. Libraries like Dinero.js or Money-Rails handle these edge cases for you.