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 an amount * 1 cast throws on the parentheses (bxp's number
parser accepts the comma thousands on its own, but not the parentheses). 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),
[Amount] * 1)
STARTS_WITH('(')detects the parenthesised (negative) form.- One variadic
REPLACEstrips(and)in a single pass — the pairs are applied left to right. The,thousands separators need no stripping:* 1parses2,500.00as it is. * 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 (the commas alone parse); 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:
// * STARTS_WITH('(') detects the parenthesised (negative) form;
// * one variadic REPLACE strips '(' and ')' in a single pass;
// * `* 1` coerces the cleaned string to a number — the ','
// thousands separators parse as they are;
// * `0 - (...)` applies the sign the parentheses stood for.
// The non-parenthesised branch is a plain `* 1` (handles both plain
// positives like "1,234.56" and already-signed "-100.00").
$amount: "IF(STARTS_WITH(TRIM([Amount]), '('), 0 - (REPLACE(TRIM([Amount]), '(', '', ')', '') * 1), [Amount] * 1)"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
date: "$date",
description: "$desc",
amount: "$amount"
}
}
}
}