Currency Amount (EU Format)
finance.currency.amount_commasince finetype v0.5.1Currency amount with European formatting: optional currency symbol (prefix or suffix), period thousands separator, comma decimal separator. Examples: €1.234,56, 1.234,56 €, -€999,99, €0,50
Worked samples
DuckDB 1.5.5 · finetype 0.6.64
| Value | As DECIMAL | numeric_value | symbol |
|---|---|---|---|
| €1.234,56 | 1234.56 | 1234.56 | € |
| 1.234,56 € | 1234.56 | 1234.56 | € |
| -€999,99 | -999.99 | -999.99 | € |
| €0,50 | 0.50 | 0.50 | € |
| €42,00 | 42.00 | 42.00 | € |
| €1.000.000,00 | 1000000.00 | 1000000.00 | € |
| 1.250,75 € | 1250.75 | 1250.75 | € |
| €100,00 | 100.00 | 100.00 | € |
Shape
^[€\$£¥₹₩₿\p{Sc}]?\s?-?\s?[0-9]{1,3}(\.[0-9]{3})*(,[0-9]{1,2})?\s?[€\$£¥₹₩₿\p{Sc}]?$|^-?[€\$£¥₹₩₿\p{Sc}]?\s?[0-9]{1,3}(\.[0-9]{3})*(,[0-9]{1,2})?\s?[€\$£¥₹₩₿\p{Sc}]?$| Value | Length | Shape |
|---|---|---|
| €1.234,56 | 9 | not checked |
| 1.234,56 € | 10 | not checked |
| -€999,99 | 8 | not checked |
| €0,50 | 5 | not checked |
| €42,00 | 6 | not checked |
| €1.000.000,00 | 13 | not checked |
| 1.250,75 € | 10 | not checked |
| €100,00 | 7 | not checked |
pattern uses syntax this checker does not support
Background
Origin
In France the comma was chosen as the decimal separator because the full stop was already used in printing to make Roman numerals more readable.[1] In 2003 the 22nd General Conference on Weights and Measures declared that the symbol for the decimal marker shall be either the point on the line or the comma on the line.[1] ISO 80000-1 states that the decimal sign is either a comma or a point on the line.[1] Wikipedia lists the style 1.234.567,89, with full stops between groups of thousands and a comma before the decimals, for countries including Austria, Denmark, Germany, Greece, Italy, the Netherlands and Turkey.[1] Wikipedia records that Spain used the 1.234.567,89 style until 2010, and that the RAE and CSIC now describe it as inadvisable.[1]
Unicode CLDR's number data for German gives the decimal symbol as a comma and the grouping symbol as a full stop.[2] CLDR's standard currency pattern for German places the currency sign after the number.[2]
Wikipedia's article on the euro sign says that placement of the sign varies and that countries have generally continued the style used for their former currencies.[3] Wikipedia gives 3,50 € as an example of the euro sign placed after the amount.[3] In English, according to Wikipedia, euro cents are separated with a full stop, not a comma.[3] Wikipedia reports that the EU's Interinstitutional Style Guide places the euro sign in front of the amount without a space in English, and after the amount in most other languages.[3] An amount written €1.234,56, with the sign in front and a decimal comma, mixes the English placement of the sign with continental separators, so the position of the sign is a weak clue to which separators a file uses. (Meridian's view)
Use
Most computer languages and standard data formats, such as C, Java, Fortran and CSS, specify a dot as the decimal separator.[1] In spreadsheets and databases, currency values are entered without grouping marks but are displayed with them inserted.[1] A value like 1.234,56 € in a data file is therefore best read as a display string written out by a report or an export, not as the number the source system stores. (Meridian's view)
The pandas read_csv function has a decimal parameter for the character to recognise as the decimal point, and its documentation suggests ',' for European data.[4] The pandas read_csv function also has a thousands parameter for the character acting as the thousands separator.[4] DuckDB's CSV reader has a decimal_separator option for the decimal separator of numbers.[5] DuckDB's CSV reader also has a thousands option, which must be a single character different from the decimal_separator option.[5]
Look at a sample of the whole column before choosing a parser, since a file assembled from several sources can mix both conventions. (Meridian's view)
Where you'll meet it
The technical specification for the CSV-Daten Bund und Länder budget-execution files, published on the website of Austria's Federal Ministry of Finance, sets the decimal separator as a comma.[6] The CSV-Daten Bund und Länder specification sets the field separator as a semicolon rather than a comma.[6]
Sources
- [1]Decimal separatorsecondary source
- [2]cldr-json/cldr-numbers-full/main/de/numbers.jsondocumentation
- [3]Euro signsecondary source
- [4]pandas.read_csvdocumentation
- [5]CSV Importdocumentation
- [6]CSV-Daten Bund und Länderdataset
Detection notes
New in v0.5.1. European format uses period as thousands separator and comma as decimal separator. Transform strips currency symbol, removes period thousands separators, replaces comma with period, then casts to DECIMAL(18,2). Distinct from amount (period decimal) which uses US separator convention.
Use it
$ finetype infer -i "€1.234,56" --mode column
→ finance.currency.amount_comma
SELECT ft_infer('€1.234,56');
-- → 'finance.currency.amount_comma'CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€\$£¥₹₩₿\p{Sc}]', '', 'g'), '\s+', '', 'g'), '\.', '', 'g'), ',', '.') AS DECIMAL(18,2))Schema
https://meridian.online/schemas/finance.currency.amount_comma · Frictionless type number
finetype taxonomy finance.currency.amount_comma -o json-schema