Accounting Negatives → Signed Decimals¶
What
Normalise an accounting/bank/ERP export where negative amounts are
written in parentheses — "(2,500.00)" means -2500 — and thousands are
comma-grouped, into a clean signed-decimal column.
Synthetic / teaching example
The data here is constructed, not sourced
— sample.csv is hand-written rows engineered to plant one of each amount
shape. The failure mode is real and documented; the rows are not. (Accounting
negatives ride in private bank/ledger exports, which have no public dataset —
so this lives in the teaching tier, not examples/real-world/.)
Why interesting¶
The parenthesis-for-negative convention is everywhere in
finance (it's the default "Accounting" number format in Excel), and it breaks
naive pipelines twice over: a spreadsheet re-import reads "(2,500.00)" as a
text label, and any amount * 1 cast throws on both the parentheses and the
comma thousands separator. The sign silently vanishes or the row errors out.
Failure mode documented in. (sources for the problem class — not for the data)
- Microsoft — Accounting number format uses parentheses for negatives.
- Generic CSV-import guides repeatedly call out converting
($1,234.56)→-1234.56before analysis.
The trick¶
(see inline comments in sample.json)
The whole conversion is one expression on the Amount field:
IF(STARTS_WITH(TRIM([Amount]), '('),
0 - (REPLACE(TRIM([Amount]), '(', '', ')', '', ',', '') * 1),
REPLACE(TRIM([Amount]), ',', '') * 1)
STARTS_WITH('(')detects the parenthesised (negative) form.- One variadic
REPLACEstrips(,)and the,thousands separators in a single pass — the pairs are applied left to right. * 1coerces the cleaned text to a number.0 - (...)applies the sign the parentheses stood for.- The else branch just drops commas — handling plain positives (
"1,234.56") and rows that are already signed (-100.00) alike.
Final result¶
The mixed-shape input collapses to clean signed decimals:
Every amount is now a real number ready to SUM, plot, or import — no manual
find-replace and no per-row sign bookkeeping.
Sample data¶
Run it with bxp-cli --config ./sample.json --template accounting_negatives_clean:
{
// Teaching example — synthetic data. A bookkeeping / bank / ERP export where
// negative amounts use the ACCOUNTING convention: parentheses instead of a
// minus sign, e.g. "(2,500.00)" = -2500. Thousands are comma-grouped. A naive
// `amount * 1` throws on the parentheses (and on the commas); spreadsheets
// import "(2,500.00)" as text. The template normalises every amount to a
// plain signed decimal.
conversion_templates: {
accounting_negatives_clean: {
data_dir: ".",
file_pattern_in: ".csv",
file_pattern_out: ".csvx",
// Amount cells are double-quoted because the thousands separator is a
// comma — the same character as the field delimiter.
csv_text_quote_in: "double",
input_schema: {
$date: "[Date]",
$desc: "[Description]",
// THE TRICK — accounting negatives + thousands separators in one pass:
// * STARTS_WITH('(') detects the parenthesised (negative) form;
// * one variadic REPLACE strips '(' ')' and the ',' thousands
// separators in a single left-to-right pass;
// * `* 1` coerces the cleaned string to a number;
// * `0 - (...)` applies the sign the parentheses stood for.
// The non-parenthesised branch just drops the commas (handles both
// plain positives like "1,234.56" and already-signed "-100.00").
$amount: "IF(STARTS_WITH(TRIM([Amount]), '('), 0 - (REPLACE(TRIM([Amount]), '(', '', ')', '', ',', '') * 1), REPLACE(TRIM([Amount]), ',', '') * 1)"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
date: "$date",
description: "$desc",
amount: "$amount"
}
}
}
}