Currency Amount (ISO Code Prefix)
finance.currency.amount_code_prefixISO 4217 three-letter currency code as prefix, followed by amount. Standard in SWIFT messages, banking/FX systems, and international financial data. Separator conventions vary by currency.
Operations on this type: Parse nested values
Try a value
Currency Amount (ISO Code Prefix)'s rule accepts it.
Reads it as 1234.56
Use it
finetype infer -i 'USD 1,234.56' --mode column
# → finance.currency.amount_code_prefixSELECT ft_infer('USD 1,234.56');
-- → 'finance.currency.amount_code_prefix'CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[A-Z]{3}\s?', ''), ',', '', 'g') AS DECIMAL(18,2))How finetype reads the samples
DuckDB 1.5.5 · finetype 0.6.66
| Value | As VARCHAR | currency_code | numeric_value |
|---|---|---|---|
| USD 1,234.56 | 1234.56 | USD | 1234.56 |
| EUR 1.234,56 | 1.23 | EUR | 1.23 |
| GBP 999.99 | 999.99 | GBP | 999.99 |
| JPY 10,000 | 10000.00 | JPY | 10000.00 |
Pattern
^[A-Z]{3}\s?-?[0-9]{1,3}([,.]?[0-9]{3})*(\.[0-9]{1,2})?$| Value | Length | Shape |
|---|---|---|
| USD 1,234.56 | 12 | matches |
| EUR 1.234,56 | 12 | rejected |
| GBP 999.99 | 10 | matches |
| JPY 10,000 | 10 | matches |
| usd 1,234.56 | 12 | rejected: lower-cased |
Background
Origin
ISO 4217 defines alpha codes and numeric codes for the representation of currencies.[1] The first edition of ISO 4217 was published in 1978.[1] ISO 4217:2015 is the most recent edition of the standard.[1] The ISO 4217 tables are maintained by SIX Group on behalf of ISO and the Swiss Association for Standardization (SNV).[1] For a national currency, the first two letters of the ISO 4217 alpha code are the two letters of the ISO 3166-1 alpha-2 country code.[1]
ISO 4217 does not regulate spacing, prefixing or suffixing when a currency code is written with an amount.[1] FineType defines this type as an ISO 4217 three-letter currency code written as a prefix, followed by the amount.[2] The style guide of the European Union's Publications Office puts the ISO 4217 code first, followed by a non-breaking space and the amount, in English, Irish, Latvian and Maltese texts.[1] For French, German, Spanish and the other EU languages it lists, the Publications Office style guide reverses the order, writing the amount, a non-breaking space and then the ISO 4217 code.[1] Wikipedia's ISO 4217 article notes that the order is determined by the native language of the document rather than by the currency.[1]
Use
The ISO 4217 code list is used in banking and business globally.[1] ISO 4217 alpha codes are used on airline tickets and international train tickets to remove any ambiguity about the price.[1] JavaScript's Intl.NumberFormat constructor has a currencyDisplay option whose value code uses the ISO currency code.[3] A code prefix is a good sign the source system was built to carry more than one currency in a single column, so expect a mix of codes rather than one. (Meridian's view)
Different countries officially designate different symbols as the decimal separator.[4] Wikipedia's decimal separator article lists 1.234.567,89 among the forms seen in Germany, Brazil and Dutch-speaking Belgium.[4] The currency code tells you the currency, not which character is the decimal mark, so decide the separator convention per file or per source before you strip anything. (Meridian's view)
Sources
- [1]ISO 4217 - Wikipediasecondary source
- [2]finetype/labels/definitions_finance.yaml at main · meridian-online/finetypeFineType
- [3]Intl.NumberFormat() constructor - JavaScript | MDNdocumentation
- [4]Decimal separator - Wikipediasecondary source
How it's detected
SWIFT, FX, banking standard.
Schema
https://meridian.online/schemas/finance.currency.amount_code_prefix · Frictionless type string
finetype taxonomy finance.currency.amount_code_prefix -o json-schema