MeridianMERIDIAN

Currency Amount (US Accounting)

US 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.

Currency Amount (US Accounting)

finance.currency.amount_accounting

US 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.

Domain
finance
Category
currency
Casts to
VARCHAR
Scope
Universal

Try it

CLI
$ finetype infer -i "$1,234.56"
→ finance.currency.amount_accounting

DuckDB

Detect
SELECT finetype('$1,234.56');
-- → 'finance.currency.amount_accounting'
Cast expression
CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))
Safe cast pipeline
-- Normalise and cast in one step
SELECT TRY_CAST(finetype_cast(my_column) AS VARCHAR) AS clean_value
FROM my_table
WHERE finetype(my_column) = 'finance.currency.amount_accounting';

Struct Expansion

Expression
is_negative: CASE WHEN {col} LIKE '(%' THEN true ELSE false END
numeric_value: CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))

JSON Schema

finetype schema finance.currency.amount_accounting
{
  "$id": "https://meridian.online/schemas/finance.currency.amount_accounting",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "description": "US 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.",
  "examples": [
    "$1,234.56",
    "($1,234.56)",
    "$0.50",
    "($999.99)",
    "$10,000.00"
  ],
  "pattern": "^\\(?\\$?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\)?$",
  "title": "Currency Amount (US Accounting)",
  "type": "string",
  "x-finetype-broad-type": "VARCHAR",
  "x-finetype-transform": "CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))"
}

Examples

$1,234.56($1,234.56)$0.50($999.99)$10,000.00

Aliases

amount_accounting_usaccountinggaap_amount

Type Registry