Before this: Dates need a declared locale
The idea
Finance exports write negatives in several ways: -125.00, (125.00), 125.00-. Treat each notation as valid only if the rules declare it.
Parse the sign separately from the digits, then convert the digits with the row's locale. Never strip the parentheses and keep a positive number.
Anything else, like a stray letter, goes to exceptions with a bad_amount reason.
Worked example
Input: raw: '(1.234,50)' with locale de-DE
- Visual
Add step → Amounts by locale → parentheses mean negative
- SQL (DuckDB)
CASE WHEN raw LIKE '(%)' THEN -1 ELSE 1 END * CAST(replace(replace(trim(raw, '()'), '.', ''), ',', '.') AS DECIMAL(12,2))
- pandas
neg = s.str.match(r"^\(.*\)$"); val = s.str.strip("()").str.replace(".", "", regex=False).str.replace(",", ".", regex=False).map(Decimal)
Result: -1234.50
Common mistakes
- Dropping the sign.
- Applying US separators to German rows.
- Accepting undeclared notations.
Check yourself
What's wrong with removing parentheses and keeping the number?
It turns a negative amount positive and inflates totals.
Practice it
Parse amounts by locale and total them per currency.