Currency Amount (US Format)
finance.currency.amountsince finetype v0.5.1Currency amount with US/international formatting: optional currency symbol prefix ($, £, ¥, etc.), optional negative sign or parentheses for negative, comma thousands separator, period decimal separator. Examples: $1,234.56, -$999.99, £10,000.00, $0.50, ¥1,000
Worked samples
DuckDB 1.5.5 · finetype 0.6.64
| Value | As DECIMAL | numeric_value | symbol |
|---|---|---|---|
| $1,234.56 | 1234.56 | 1234.56 | $ |
| -$999.99 | -999.99 | -999.99 | |
| £10,000.00 | 10000.00 | 10000.00 | £ |
| $0.50 | 0.50 | 0.50 | $ |
| ¥1,000 | 1000.00 | 1000.00 | ¥ |
| $42.00 | 42.00 | 42.00 | $ |
| $1,000,000.00 | 1000000.00 | 1000000.00 | $ |
| £250.75 | 250.75 | 250.75 | £ |
| ($1,234.56) | -1234.56 | -1234.56 | |
| ($999.99) | -999.99 | -999.99 |
Shape
^[\$£¥₹₩₿\p{Sc}]?\s?-?\s?[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?$|^-?[\$£¥₹₩₿\p{Sc}]\s?[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?$|^\([\$£¥₹₩₿\p{Sc}]?[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?\)$|^-?[0-9]+(\.[0-9]+)?([eE][+-]?[0-9]+)?$| Value | Length | Shape |
|---|---|---|
| $1,234.56 | 9 | not checked |
| -$999.99 | 8 | not checked |
| £10,000.00 | 10 | not checked |
| $0.50 | 5 | not checked |
| ¥1,000 | 6 | not checked |
| $42.00 | 6 | not checked |
| $1,000,000.00 | 13 | not checked |
| £250.75 | 7 | not checked |
| ($1,234.56) | 11 | not checked |
| ($999.99) | 9 | not checked |
pattern uses syntax this checker does not support
Background
Origin
FineType defines finance.currency.amount as a currency amount with an optional currency symbol in front, a comma as the thousands separator and a period as the decimal separator.[1] FineType's definition accepts a negative amount written either with a minus sign or in parentheses.[1] FineType keeps the inverted convention, with a comma for decimals, as a separate type, finance.currency.amount_comma.[1]
In the United States, the full stop or period is used as the standard decimal separator.[2] Traditionally, English-speaking countries other than South Africa used commas as the delimiter between groups of digits, as in 10,000.[2] In Britain the British Standards Institution and some sectors of industry advocated the decimal comma, while the Decimal Currency Board advocated the point.[2] In Britain the decimal point was chosen by the Ministry of Technology in 1968.[2] Wikipedia records 1,234,567.89 or 1,234,567·89 as the style seen in Ireland, Israel, Japan, Korea, Malaysia, the Philippines, Singapore, Taiwan, Thailand, the United Kingdom and the United States.[2]
For currencies in English-speaking countries and in most of Latin America, the currency symbol is placed before the amount, as in $20.50.[3] In most other countries, including many in Europe and Francophone Canada, the symbol is placed after the amount, as in 20,50 €.[3] Unicode's CLDR number specification gives ¤#,##0.00 as its example of a standard currency format pattern.[4] In CLDR patterns the ¤ sign is replaced by the localised currency symbol for the currency being formatted.[4] The CLDR specification says that in a standard currency format a negative amount might typically be displayed as something like -$3.27.[4] The CLDR specification says locales may also provide an accounting form, in which for en_US the same amount would appear as ($3.27).[4]
Financial statements can include negative balances, shown either by a minus sign or by enclosing the balance in parentheses.[5] In bookkeeping, amounts owed are often represented by red numbers or by a number in parentheses, as an alternative notation for negative numbers.[5]
Use
Digit group separators can occur either as part of the data or as a mask through which the data is displayed.[2] RFC 4180 says that fields containing commas should be enclosed in double quotes.[6]
The pandas read_csv function uses a period as its default decimal character and suggests a comma for European data.[7] Before casting, strip the currency symbol, remove the thousands commas and turn surrounding parentheses into a minus sign, keeping the symbol in its own column if it varies. (Meridian's view) Confirm that the file really uses the comma for thousands, since a value like 1,234 means something very different under the comma-decimal convention. (Meridian's view)
Python's decimal documentation notes that numbers like 1.1 and 2.2 do not have exact representations in binary floating point.[8] Python's decimal documentation says decimal is preferred in accounting applications which have strict equality invariants.[8] Cast cleaned amounts to a fixed-point decimal type rather than a float, so that totals reconcile to the cent. (Meridian's view)
The numerous currencies called dollar use the dollar sign, and the sign is also generally used for the many currencies called peso.[9]
Sources
- [1]finetype/labels/definitions_finance.yaml at main · meridian-online/finetype · GitHubFineType
- [2]Decimal separator - Wikipediasecondary source
- [3]Currency symbol - Wikipediasecondary source
- [4]Unicode Locale Data Markup Language (LDML) Part 3: Numbersstandard
- [5]Negative number - Wikipediasecondary source
- [6]Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard
- [7]pandas.read_csv — pandas 3.0.6 documentationdocumentation
- [8]decimal — Decimal fixed-point and floating-point arithmetic — Python 3.15.0 documentationdocumentation
- [9]Dollar sign - Wikipediasecondary source
Detection notes
New in v0.5.1. US/international format uses comma as thousands separator and period as decimal separator. Transform handles both standard negatives (-$X) and accounting notation (($X)). Casts to DECIMAL(18,2). Distinct from amount_comma which uses the inverted separator convention.
Use it
$ finetype infer -i "$1,234.56" --mode column
→ finance.currency.amount
SELECT ft_infer('$1,234.56');
-- → 'finance.currency.amount'CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\-]+', ''), '[^0-9\.\-]', '', 'g'), ',', '', 'g'), '\s+', '', 'g') AS DECIMAL(18,2))Schema
https://meridian.online/schemas/finance.currency.amount · Frictionless type number
finetype taxonomy finance.currency.amount -o json-schema