Convert Trailing Minus and CR/DR Amounts with pandas

Open an export from SAP, a mainframe ledger or a bank portal and the amount column often looks numeric but is text: 1,250.00- with the minus at the end, (310.50) in accounting brackets, 845.20 CR, or two columns — an amount and a D/C indicator — that together give the sign. Excel shows these as left-aligned text, sums them to zero, and its own Text to Columns fixes only some. pd.to_numeric rejects them outright. The fix is one parsing function that recognises each convention, applied to the whole column at once, followed by a check that every value was understood. This page is part of Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

gl_export.xlsx is a general ledger extract where the Amount column mixes every convention: plain numbers with thousands separators, a trailing minus, brackets, CR and DR suffixes, padding spaces, an ordinary leading minus, and one n/a. bank_export.xlsx comes from a German bank: amounts written 1.234,56-, and a second layout with an unsigned amount and a separate debit/credit column.

import pandas as pd

gl = pd.read_excel("gl_export.xlsx", dtype={"DocNo": str, "Amount": str}, keep_default_na=False)
print(gl.to_string(index=False))
try:
    pd.to_numeric(gl["Amount"])
except ValueError as err:
    print("to_numeric:", err)

Reading with dtype=str keeps the column exactly as exported; letting pandas guess would turn the few plain numbers into floats and leave the rest as text, a mixed column that is harder to reason about. keep_default_na=False matters too: by default read_excel turns strings such as n/a, NA and null into missing values before your code sees them, so a placeholder that someone typed into the export would vanish instead of being reported. With it switched off, only truly empty cells arrive as empty strings, and every other value reaches the parser.

Step 1 — Decide what each convention means

Before writing code, pin down the sign rules, because they are not universal:

• Trailing minus (1,250.00-) — negative. Standard in SAP and many mainframe reports.
• Brackets ((310.50)) — negative, the accounting convention.
• CR and DR — depends on whose books you are looking at. In a customer's account in your ledger, a credit (CR) reduces what they owe, so it is negative; a debit (DR) increases it. In a bank statement, CR is money in. Decide which sign CR gets for this report and write it down.
• Leading minus (-75.25) — negative, as usual.

The code below treats CR as negative and DR as positive, the ledger view. Swap them in one place if your source uses the bank view.

Step 2 — Parse every convention in one pass

Clean the text, detect each marker with vectorised string methods, strip the markers, convert the remaining digits, and apply the sign:

import numpy as np

CR_IS_NEGATIVE = True

def parse_amount(s, decimal=".", thousands=","):
    t = s.astype("string").str.strip().str.upper()
    neg = (t.str.endswith("-") | t.str.startswith("-")
           | (t.str.startswith("(") & t.str.endswith(")")))
    cr = t.str.endswith("CR")
    dr = t.str.endswith("DR")
    neg = neg | (cr if CR_IS_NEGATIVE else dr)
    digits = (t.str.replace(r"\s*(CR|DR)$", "", regex=True)
                .str.strip("-() ")
                .str.replace(thousands, "", regex=False)
                .str.replace(decimal, ".", regex=False))
    value = pd.to_numeric(digits, errors="coerce")
    return value.where(~neg.fillna(False), -value)

gl["AmountNum"] = parse_amount(gl["Amount"])
print(gl[["DocNo", "Amount", "AmountNum"]].to_string(index=False))

Each step works on the whole column: str.endswith("-") finds trailing minus signs, the bracket test needs both an opening and a closing bracket, and the regular expression removes a CR or DR suffix together with the space before it. str.strip("-() ") then removes the sign characters from both ends, and the thousands separator goes before the decimal separator is normalised to a point — the order matters for the European format in Step 4. errors="coerce" turns anything that is still not a number into NaN instead of raising, which the next step deals with.

Step 3 — Check that nothing was lost

Coercing to NaN is only safe if you then look at what was coerced. List every value that was not blank but did not parse, and reconcile the total with the source system:

failed = gl[gl["AmountNum"].isna() & gl["Amount"].str.strip().ne("")]
print("could not parse:", failed[["DocNo", "Amount"]].values.tolist())
print(f"net total: {gl['AmountNum'].sum():,.2f}")
print(f"debits {gl.loc[gl['AmountNum'] > 0, 'AmountNum'].sum():,.2f}  "
      f"credits {gl.loc[gl['AmountNum'] < 0, 'AmountNum'].sum():,.2f}")

The n/a in document 4720 is reported rather than silently treated as zero. Whether to stop the job, exclude the row or ask someone depends on the report, but it must be a decision — a report that quietly drops a posting will not reconcile. Totals of debits and credits are what an accountant will compare with the ledger's own trial balance; see Reconcile Excel Totals with Control Totals.

Step 4 — European formats: 1.234,56-

German, Dutch, French and many other exports use a dot for thousands and a comma for decimals — and SAP in those locales puts the minus at the end too. The same function handles it with the separators swapped:

bank = pd.read_excel("bank_export.xlsx", sheet_name="Statement", dtype={"Betrag": str})
bank["BetragNum"] = parse_amount(bank["Betrag"], decimal=",", thousands=".")
print(bank[["Posting", "Betrag", "BetragNum"]].to_string(index=False))

Removing the thousands dots first and only then turning the decimal comma into a point is what keeps 1.234,56 from becoming 1.23456. If one file mixes both formats — rare, but it happens when exports from two subsidiaries are pasted together — split it by source and parse each part with its own separators; no rule can tell what 1.234 means on its own.

Step 5 — A separate debit/credit indicator column

Some exports keep the amount unsigned and put the direction in another column: D/C, S/H (German Soll and Haben), Dr/Cr. Map the indicator to a sign and multiply:

SIGN = {"D": 1, "C": -1, "S": 1, "H": -1}
bank["Signed"] = bank["Amount"] * bank["DC"].str.upper().map(SIGN)
bank["BetragSigned"] = bank["BetragNum"].abs() * bank["Soll/Haben"].map(SIGN)
print(bank[["Amount", "DC", "Signed", "Soll/Haben", "BetragSigned"]].to_string(index=False))

unknown = bank.loc[bank["DC"].str.upper().map(SIGN).isna(), "DC"].unique()
print("unknown indicators:", list(unknown))

map returns NaN for an indicator that is not in the dictionary, so an unexpected code — a X for "reversed", say — shows up as a missing amount instead of being treated as positive. Checking for unknown codes explicitly, as the last line does, turns that into a clear message.

Step 6 — Write the result back to Excel

Keep the original text column next to the parsed number while the rules are new, so anyone can check a value against the export, and give the number an accounting format:

with pd.ExcelWriter("gl_clean.xlsx", engine="xlsxwriter") as xw:
    gl.to_excel(xw, index=False, sheet_name="GL")
    fmt = xw.book.add_format({"num_format": "#,##0.00;[Red](#,##0.00)"})
    xw.sheets["GL"].set_column("C:C", 14)
    xw.sheets["GL"].set_column("D:D", 14, fmt)
print(pd.read_excel("gl_clean.xlsx")["AmountNum"].dtype)

The number format shows negatives in red brackets, the way the export printed some of them — but now they are real numbers that sum, sort and pivot. Show Negative Numbers in Red with Parentheses explains the format code.

Performance notes

Every operation in parse_amount is a vectorised string method, so a column of a million amounts parses in a second or two. Avoid the tempting alternative of df["Amount"].apply(some_function) with a Python if/elif chain per value: it is easier to write and many times slower, and it tends to grow special cases that nobody tests. If the same export is parsed every day, keep parse_amount in a small shared module with a unit test per convention.

Common pitfalls

• Stripping the minus with the thousands separator. Detect the sign before removing any characters.
• Brackets without a matching pair. (310.50 from a truncated field is not negative by convention; require both brackets.
• CR meaning different things. Ledger and bank views disagree about the sign of a credit; make it a setting.
• European separators in the wrong order. Remove thousands dots before converting the decimal comma.
• Coercing without checking. errors="coerce" hides problems unless you list the values it turned into NaN.

Conclusion

Read the amount column as text, decide what each convention means for your report, and parse it in one vectorised pass: detect trailing and leading minus signs, brackets and CR/DR suffixes, strip them, remove thousands separators before normalising the decimal separator, and apply the sign. Map indicator columns such as D/C or S/H to ±1, list every value that did not parse, and reconcile the totals before the numbers go into a report.

Frequently asked questions

How do I convert numbers with a trailing minus sign in pandas? Detect values ending in "-" with str.endswith("-"), strip the minus and the thousands separators, convert with pd.to_numeric, and negate the values that had the trailing sign. The parse_amount function on this page does that together with brackets and CR/DR suffixes.

How do I handle CR and DR in amounts? Remove the suffix with a regular expression and use it to set the sign. Whether CR is negative depends on the point of view: in your ledger a customer credit is usually negative, while on a bank statement CR is money in.

How do I convert 1.234,56 to a number in pandas? Remove the dot thousands separators first, then replace the decimal comma with a point, then convert with pd.to_numeric. Doing it in the other order turns 1.234,56 into 1.23456.

What if the sign is in a separate column? Map the indicator values to +1 and -1 with a dictionary — for example D and S to 1, C and H to -1 — and multiply the amount by the result. Check for indicator values missing from the dictionary.

Related

• Up one level: Cleaning Excel Data with pandas — the everyday clean-up toolkit.
• Convert Currency Strings to Numbers with pandas — currency symbols and codes.
• Convert Excel Text Columns to Numbers with pandas — the general text-to-number problem.
• Read CSV Exports with Semicolons and Decimal Commas — the same formats arriving as CSV.