Currency Amount (US Accounting)
finance.currency.amount_accountingUS accounting format where negative values are enclosed in parentheses rather than using a minus sign. Standard in 10-K filings, ledgers, SAP, Smartsheet, and Dataiku exports. Comma thousands, period decimal.
Worked samples
DuckDB 1.5.5 · finetype 0.6.65
| Value | As VARCHAR | is_negative | numeric_value |
|---|---|---|---|
| $1,234.56 | 1234.56 | False | 1234.56 |
| ($1,234.56) | -1234.56 | True | -1234.56 |
| $0.50 | 0.50 | False | 0.50 |
| ($999.99) | -999.99 | True | -999.99 |
| $10,000.00 | 10000.00 | False | 10000.00 |
Shape
^\(?\$?[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?\)?$| Value | Length | Shape |
|---|---|---|
| $1,234.56 | 9 | matches |
| ($1,234.56) | 11 | matches |
| $0.50 | 5 | matches |
| ($999.99) | 9 | matches |
| $10,000.00 | 10 | matches |
| $1,234.566 | 10 | rejected: one character extra |
Told apart
Going the other way, this type's own rule accepts 6 of 10 samples of Currency Amount (US Format).
Background
Origin
In bookkeeping, amounts owed are often represented by red numbers, or by a number in parentheses, as an alternative notation for negative numbers.[1] Financial statements can show a negative balance either with a minus sign or by enclosing the balance in parentheses.[1] In contemporary printing in banking, accounting and commerce, red numbers denote negative values and black numbers denote positive ones.[1]
The SEC's Regulation S-X, at 17 CFR 210.4-01(c), requires that negative amounts, which it calls red figures, be shown in a manner which clearly distinguishes the negative attribute.[2] Regulation S-X adds that consideration should be given to the limitations of reproduction and microfilming processes when determining methods of display.[2]
Unicode's Locale Data Markup Language specification, Part 3: Numbers, allows locales to provide an accounting currency form in addition to the standard currency form.[3] The Unicode specification gives the example of a negative amount shown as -$3.27 in the standard form, which for en_US would appear as ($3.27) in the accounting form.[3] In the Unicode number pattern syntax, a semicolon separates the positive and negative subpatterns.[3] CLDR's English locale data defines the accounting currency pattern as ¤#,##0.00;(¤#,##0.00).[4] The Unicode specification says the locale keyword cf can be used to select the standard or accounting form.[3]
Use
Microsoft Excel's Accounting format displays zeros as dashes and negative numbers in parentheses.[5] In Excel, the Negative numbers box is not available for the Accounting number format.[5] JavaScript's Intl.NumberFormat constructor takes a currencySign option whose possible values are standard and accounting, with standard as the default.[6] MDN's reference for Intl.NumberFormat says that in many locales the accounting format wraps the number with parentheses instead of appending a minus sign.[6]
AccountingTools describes a number in parentheses on a cash flow statement as a cash outflow.[7] AccountingTools lists further uses of parentheses in accounting, including the credit side of a journal entry and an unfavourable budget variance.[7]
SEC staff observed that most XBRL-tagged numbers are positive even if the value is presented with brackets on the printed financial statements.[8] If you reconcile a filing's printed tables against its XBRL data, do not assume that a value in parentheses on the page carries a minus sign in the tagged data. (Meridian's view)
To parse the format, strip the currency symbol and thousands separators, turn a value wrapped in parentheses into a minus sign, and cast to a fixed-point decimal rather than a float. (Meridian's view) Watch for dashes standing in for zero in spreadsheet exports, and decide how to treat them before a strict numeric parse rejects them. (Meridian's view) Keep the raw text beside the parsed number until you have confirmed the sign convention of the source. (Meridian's view)
Sources
- [1]Negative numbersecondary source
- [2]17 CFR § 210.4-01 - Form, order, and terminology.standard
- [3]Unicode Locale Data Markup Language (LDML) Part 3: Numbersstandard
- [4]en.xmldocumentation
- [5]Format numbers as currency in Exceldocumentation
- [6]Intl.NumberFormat() constructor - JavaScript | MDNdocumentation
- [7]When to put parentheses around a numbersecondary source
- [8]Staff Observations from the Review of Interactive Data Financial Statements (from December 13, 2011)organisation
Detection notes
New in format coverage expansion. Parentheses = negative is universal in US GAAP accounting. Transform strips parens→minus, symbol, commas.
Use it
$ finetype infer -i "$1,234.56" --mode column
→ finance.currency.amount_accounting
SELECT ft_infer('$1,234.56');
-- → 'finance.currency.amount_accounting'CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))Schema
https://meridian.online/schemas/finance.currency.amount_accounting · Frictionless type string
finetype taxonomy finance.currency.amount_accounting -o json-schema