Privacy and preparation
English dataset currency normalization: why keep the original amount?
By SourceX Editorial · Updated
10 sections
Short answer
Keep the original amount and its ISO 4217 currency code in their own fields and put any normalized amount beside them, never in place of them. Conversion depends on a chosen rate source, rate date, quote direction and rounding rule, and loses precision. Disclose those choices in a conversion block so anyone can recompute, audit or reject the normalized figure.
Provenance trail
English dataset currency
Key takeaways
- A converted amount is derived from a rate choice; the original amount and currency are the recorded fact and should never be overwritten.
- Disclose rate source, rate type, rate date, quote direction and rounding for every normalized amount.
- If the payment platform already converted the money, keep its rate instead of substituting a reference rate.
- Fraction digits differ by currency, from zero for the yen to three for the Kuwaiti dinar, so one divisor for all currencies corrupts amounts.
- Resolve which dollar a record means from a structured field or the entity's books, never from the symbol.
- Store amounts and rates as decimal strings or integers with a digit count, not binary floats.
Why should a normalized dataset keep the original currency and amount?
Because a converted amount is a derived opinion and the original amount is the recorded fact. Any normalized figure depends on a rate source, a rate date, a quote direction and a rounding rule, and each of those is a choice someone made. Keep the original amount and currency in their own fields, put the normalized amount beside them, and disclose how the conversion was done so anyone can recompute or reject it.
There is no single correct exchange rate to fall back on. The European Central Bank publishes its euro reference rates around 16:00 CET on working days and says they are for information purposes only, not for market transactions. The Federal Reserve's H.10 release reports noon buying rates in New York, which the Federal Reserve Bank of New York certifies for customs purposes. The IRS says it has no official exchange rate and generally accepts any posted rate used consistently. Three respectable sources, three different conventions, and a dataset that only stores the converted result hides which one was used.
Losing the original also destroys signal a model can learn from. An invoice issued in yen, a refund issued in Canadian dollars and a purchase order in euros carry information about market, customer and contract that a single reporting currency flattens. Text fields in the same records often mention the original currency, so a converted amount with no original creates a contradiction inside the record itself.
Finally, conversion is lossy in a mechanical sense. Currencies have different minor units, rates are quoted to limited precision and every converted figure is rounded. Going from original to normalized and back rarely returns the starting number exactly, so the original cannot be reconstructed later if it was not kept.
The currency provenance and conversion disclosure schema, field by field
The schema has two layers: the amount as the source system recorded it, and a disclosure block for every conversion. Use the field names as they are or map them to your own data dictionary; the rule column is what a validator enforces.
| Field | Purpose | Rule |
|---|---|---|
| amount_original | The amount exactly as the source system holds it | Decimal string, never a binary float; fraction digits no more than the currency allows |
| currency_original | The currency the amount was recorded in | ISO 4217 three-letter alphabetic code, uppercase, never a symbol |
| minor_unit_digits | How many fraction digits the currency uses | Taken from a dated reference table such as CLDR currency data, with that table's version recorded |
| amount_storage_form | Whether the source stored major units or smallest units | One of major_decimal or minor_integer; minor_integer requires minor_unit_digits |
| amount_as_written | The amount string as it appeared in a document or text field | Optional; kept only where the source was text, with its locale |
| source_locale | Number and date formatting convention of the source | BCP 47 tag such as de-DE or en-IN; required when amount_as_written is present |
| amount_role | What the number means in the business process | One of gross, net, tax, discount, refund, balance or other, with other described |
| transaction_date | The business date the amount belongs to | ISO 8601 date with the field it came from named in the data dictionary |
What the conversion disclosure block records
Every normalized amount gets a disclosure block, and a record may carry several if it is converted to more than one reporting currency. The disclosure is what turns a normalized number from an assertion into something a reviewer can check. Published vocabularies already point this way: schema.org's currency property recommends ISO 4217 codes, and its ExchangeRateSpecification type describes an exchange rate as structured data.
At a glance
What the conversion disclosure block records
| Field | Purpose | Rule |
|---|---|---|
| amount_normalized | The converted amount | Decimal string rounded to the target currency's minor unit |
| currency_normalized | The reporting currency | ISO 4217 alphabetic code |
| rate_value | The rate applied | Decimal string at the precision the rate source published, not re-rounded |
| rate_direction | Which way the rate is quoted | Stated as units of currency_normalized per one unit of currency_original, or the reverse, never implied |
| rate_source | Who published the rate | Named publisher and series, or source_system when the originating platform converted |
| rate_type | Kind of rate | One of daily_reference, spot_at_transaction, period_average, contract_fixed or source_system |
| rate_date | The date the rate applies to | ISO 8601 date, plus a rule naming which record date selected it |
| rounding_rule | How the result was rounded | For example half_even or half_up at the target minor unit |
| converted_by | The process that produced the figure | Pipeline name and version, or the source system's own field name |
Which exchange rate, and which date, should a conversion use?
Match the rate to the question the dataset will answer, then record it. There is no neutral default, so the disclosure matters more than the choice. The table below covers the common cases in operational records.
When the source platform already converted the money, keep its rate rather than replacing it with a reference rate. Stripe's API documentation, for example, describes a balance transaction whose amount and currency can differ from the charge that produced it, with an exchange_rate field such that the original amount multiplied by the rate equals the converted amount. That platform rate is what actually happened to the money, so a reference rate would describe a conversion that never took place.
| Use of the normalized amount | Rate type to use | Date basis | What to disclose |
|---|---|---|---|
| Reproduce what the business actually received or paid | source_system rate from the payment or banking platform | Settlement or posting date the platform used | Platform name, field name and that no reference rate was applied |
| Compare transaction sizes across countries | daily_reference from one named publisher | Transaction date, with a rule for weekends and holidays | Publisher, series and the fallback rule for days with no fixing |
| Match annual figures or tax-style reporting | period_average for the year or month | The period the amount belongs to | That figures are period averages and not transaction-date values |
| Contract pricing fixed in another currency | contract_fixed as written in the agreement | Agreement effective date | The contract reference and that the rate is not a market rate |
| Training text where the amount appears in prose | No conversion; keep original only | Not applicable | That amounts in text were left in original currency on purpose |
Which rate date applies on weekends, holidays and late postings?
A daily reference series has gaps, so write the fallback rule down before converting. The ECB publishes on working days and not on TARGET closing days, so a sale on a Saturday or a holiday has no same-day fixing. Pick one rule, such as the most recent prior publication, and store the actual rate_date used rather than the transaction date, so the gap is visible.
Back-dated postings cause a quieter error. An accounts payable entry posted weeks after the invoice date will pick a different rate depending on which date drives the lookup. Name the driving field in the disclosure, and if the source has both an invoice date and a posting date, keep both in the record.
How minor units, locale formats and floats corrupt amounts
Most corrupted amounts come from three parsing assumptions: that every currency has two decimal places, that a dot is always the decimal separator and that a float can hold money exactly. Each one fails silently.
Minor units vary. Unicode CLDR's currency data, which software libraries use for formatting, gives a default of two fraction digits but lists the Japanese yen, Korean won and Chilean peso with zero, the Bahraini and Kuwaiti dinars with three, and the Chilean unidad de fomento with four. An export that stores amounts as integers of the smallest unit, as Stripe's API does for charge amounts, must be divided by the right power of ten for each currency; dividing everything by 100 inflates or shrinks the zero-digit and three-digit currencies by a factor of 100 or 10.
Separators vary by locale. A string such as 1.234,50 in German formatting and 1,234.50 in American formatting are the same quantity, while 1.234 means one thousand two hundred thirty-four in one locale and one and a fraction in another. Parse text amounts only with the source locale recorded, and keep the original string so a reviewer can see what was parsed.
Binary floating point cannot represent most decimal fractions. Python's documentation for its decimal module notes that 1.1 plus 2.2 displays as 3.3000000000000003 in binary floating point while decimal arithmetic keeps it exact. Store amounts and rates as decimal strings or as integers with an explicit digit count, and convert with decimal arithmetic.
Two yen invoices compared: one passes, one fails (illustrative)
The two records below are invented to show structure and do not describe any company's data. Amount values are written as placeholders, because the point is the shape of the record, not the figures. Read each line as field: value.
The valid record is a sales invoice from a Japanese subsidiary normalized to a reporting currency. The failing record looks similar but would pass a casual review.
- Valid, line 1. amount_original: a decimal string with no fraction digits; currency_original: JPY; minor_unit_digits: 0 (CLDR currency data, version recorded); amount_storage_form: major_decimal.
- Valid, line 2. amount_role: gross; transaction_date: the invoice date field; source_locale: ja-JP; amount_as_written: omitted because the source was a structured ERP field.
- Valid, line 3. amount_normalized: a decimal string with two fraction digits; currency_normalized: USD; rate_type: daily_reference; rate_source: named publisher and series.
- Valid, line 4. rate_value: as published; rate_direction: units of USD per one JPY stated explicitly; rate_date: the most recent publication on or before the invoice date; rounding_rule: half_even; converted_by: pipeline name and version.
- Failing, line 1. amount_original holds two fraction digits for a JPY amount, which CLDR says has none, a sign that the source integer was divided by 100.
- Failing, line 2. currency_original holds the yen symbol instead of an ISO code, and the same symbol is used for the Chinese yuan, so the currency is ambiguous.
- Failing, line 3. rate_direction is missing, so a reviewer cannot tell whether to multiply or divide; recomputing in both directions gives answers that differ by orders of magnitude.
- Failing, line 4. rate_date equals the export date rather than the invoice date, so every historical invoice is converted at one recent rate.
Validator rules a reviewer can run on every delivery
Each rule below can run against a delivery as is, in SQL, a JSON Schema plus a script, or a dataframe check. Run them per currency, because a pass rate across the whole file can hide one broken currency.
At a glance
Validator rules a reviewer can run on every delivery
- Code check: currency_original and currency_normalized match three uppercase letters and appear in the ISO 4217 list your reference table was built from.
- Digit check: the fraction digits in amount_original never exceed minor_unit_digits for that currency; flag any currency where every value ends in two zeros, which suggests a wrong divisor.
- Recompute check: amount_original times rate_value, in the stated rate_direction and rounded with rounding_rule, equals amount_normalized to within half of one minor unit of the target currency.
- Date check: rate_date is on or before the date named by the selection rule and no more than the documented number of days earlier; flag any delivery where one rate_date covers most records.
- Validity check: the currency was in use for the record's country on transaction_date. CLDR's region data, for example, lists the euro for Croatia from 2023-01-01 and the kuna until 2023-01-14, so both are valid in that overlap and the kuna is not valid afterward.
- Completeness check: no amount_normalized without rate_value, rate_source, rate_type, rate_date and rate_direction.
- Source-rate check: where rate_type is source_system, the platform's own rate field was copied unchanged, not recalculated.
- Text check: amounts mentioned in free-text fields were not rewritten into the reporting currency unless the disclosure says so.
Assumptions that can reverse the answer: dollars, redenominations and home-currency defaults
Some answers flip on a single assumption, so check these before accepting any normalized column. The most common in English-language records is the word dollar. U.S., Canadian, Australian, New Zealand and Singapore dollars all appear in English text, and a symbol or the bare word cannot tell them apart. Resolve the currency from a structured field, the entity's books or the customer's billing country, never from the symbol, and record which one you used.
Redenominations and currency changes break long histories. CLDR's region data shows Venezuela moving from VEF to VES on 2018-08-20, and the euro changeovers of earlier decades did the same across much of Europe. A ten-year ledger can contain two codes for one country, and a naive conversion treats the old code as if it were current.
Home-currency defaults are the quietest failure. Many multi-entity ERPs store a transaction currency and a functional or base currency, and some exports drop the transaction currency column and keep only the base amount. If currency_original is filled from the company's home country rather than from a field, every foreign transaction is mislabeled. Ask for the export that includes the transaction currency, and if it does not exist, say so in the disclosure.
Taxes and amount roles also change meaning across jurisdictions. A gross amount that includes value-added tax in one country is not comparable to a pre-tax amount in another, even after conversion, so amount_role belongs in the schema and not in a footnote.
Preparing multi-currency records for licensing review
Keeping originals is also what makes a multi-currency dataset reviewable before it is licensed. A reviewer can see which entities, countries and periods the records cover, check that conversions are disclosed and confirm that free text still matches the numbers beside it. That review is separate from the rights questions: having access to an ERP, having privacy permission to process its records, owning the business records and holding a contractual right to license them are four different things, and removing names answers none of the last three.
When a company decides to license records like these, SourceX takes on the qualification, the rights review, the contract and the data preparation. It works with U.S., Canadian and EU companies that had 50 or more full-time employees at their peak, and companies in the UK, Australia, New Zealand, India, Singapore, South Africa, Latin America, the UAE and Saudi Arabia can be considered when the offered data is at least 90% English; that share is measured on the records actually offered, and every dataset still goes through rights, privacy, quality and buyer-fit review. A company can start with a short assessment that needs no data upload.
Frequently asked questions
Should a training dataset store amounts in one currency or many?
Store the original currency for every amount and add a normalized amount only when a use case needs comparison across currencies. One reporting currency is convenient for analysis, but on its own it hides which rate was used and when. Keeping both lets a buyer recompute or ignore the conversion.
Is the ECB reference rate the right rate for normalizing a dataset?
It is a reasonable published series for comparisons, but the ECB says its reference rates are for information purposes only. Whatever series you choose, disclose the publisher, the date rule and the fallback for days without a publication. If the source platform already converted the money, keep that platform's rate instead.
How should I store amounts so decimals are not lost?
Use decimal strings, or integers in the smallest unit together with the currency's fraction digits. Avoid binary floating point, which cannot represent most decimal fractions exactly. Check the fraction digits per currency, because the yen has none and the Kuwaiti dinar has three.
What if the source export has only the converted amount?
Ask whether the system can export the transaction currency and original amount; most ERPs and payment platforms hold both. If it cannot, label the column as converted, record what is known about the rate and say plainly that the original is unavailable. Do not reconstruct originals by reversing a reference rate.
Do amounts written inside free text need to be normalized too?
Usually not. Rewriting prose changes what the record says and can create contradictions with structured fields. Leave text amounts in their original form and, if needed, add extracted and normalized values as separate fields with the same disclosure block.
Sources
- The ECB publishes euro foreign exchange reference rates around 16:00 CET on working days except TARGET closing days and states they are for information purposes only. Source
- The Federal Reserve's H.10 release reports noon buying rates in New York, certified by the Federal Reserve Bank of New York for customs purposes. Source
- The IRS has no official exchange rate and generally accepts any posted exchange rate that is used consistently; it publishes yearly average rates. Source
- ISO 4217 sets three-letter alphabetic and three-digit numeric currency codes and records the relationship between a currency and its minor unit. Source
- Unicode CLDR currency data lists fraction digits per currency (default 2; JPY, KRW, CLP 0; BHD, KWD 3; CLF 4) and dated currency validity per region, including Croatia and Venezuela. Source
- Stripe's API specification describes charge amounts as integers in the smallest currency unit and a balance transaction exchange_rate where the original amount times the rate equals the converted amount. Source
- Python's decimal module documentation shows binary floating point displaying 1.1 + 2.2 as 3.3000000000000003 while decimal arithmetic is exact. Source
- schema.org's currency property recommends ISO 4217 codes, and the vocabulary includes an ExchangeRateSpecification type. Source
See if your company qualifies
A short company assessment. No data uploads are needed.