Clean Credit/Debit Amount Notation

When to use this skill

Use this when an amount column arrives as text instead of numbers because the source system marks credits with notation rather than a real minus sign. You’ll recognize it immediately:

• “CR” suffix: 1,234.56 CR (credit, should be negative)
• Trailing minus: 5,009- (common in old mainframe/AS400 exports)
• Parentheses: (1,234.56) (classic accounting display format)
• Thousands commas: 15,234.00 (blocks numeric conversion even when the sign is fine)

If you can’t sum the column, or Excel/pandas treats the amounts as text, this is your skill. Typical sources: trial balance exports, GL detail dumps, AP/AR aging reports from legacy systems.

Inputs it expects

• A pandas DataFrame with one or more amount columns stored as strings (dtype object)
• Amounts may mix notations within the same column (some CR, some plain, some parenthesized)
• A known-good control total, if the source report prints one (e.g., the report footer total) — you’ll tie to it at the end

Steps

1. Snapshot before you touch anything: record the row count and, if possible, a hash-total of the raw text column (or the report’s printed total).
2. Strip whitespace from every value — trailing spaces hide “CR” suffixes from your checks.
3. Handle each negative notation with a boolean mask: CR suffix, trailing minus, parentheses. For each, prepend - and remove the notation characters.
4. Remove thousands commas and convert to float.
5. Coerce leftovers: anything that still won’t convert becomes NaN via pd.to_numeric(errors='coerce') — these are your exceptions, not silent failures.
6. Tie out: sum the converted column and compare to the report’s control total.

Code

import pandas as pd
import numpy as np

def clean_accounting_amounts(df: pd.DataFrame, column: str) -> pd.DataFrame:
    """Convert legacy credit/debit notation to signed floats.

    Handles: '1,234.56 CR', '5,009-', '(1,234.56)', '1,234.56'
    """
    df = df.copy()
    raw = df[column].astype(str).str.strip()

    # Notation 1: 'CR' suffix (case-insensitive) -> negative
    mask_cr = raw.str.upper().str.endswith('CR')
    raw.loc[mask_cr] = '-' + raw.loc[mask_cr].str[:-2].str.strip()

    # Notation 2: trailing minus '5,009-' -> negative
    mask_minus = raw.str.endswith('-') & ~raw.str.startswith('-')
    raw.loc[mask_minus] = '-' + raw.loc[mask_minus].str[:-1]

    # Notation 3: parentheses '(1,234.56)' -> negative
    mask_paren = raw.str.startswith('(') & raw.str.endswith(')')
    raw.loc[mask_paren] = '-' + raw.loc[mask_paren].str[1:-1]

    # Strip commas, convert; anything unconvertible becomes NaN
    raw = raw.str.replace(',', '', regex=False).str.strip()
    df[column + '_clean'] = pd.to_numeric(raw, errors='coerce')

    return df

# --- Usage on a trial balance export ---
tb = pd.read_excel('trial_balance.xlsx', dtype={'beg_balance': str})
rows_in = len(tb)

tb = clean_accounting_amounts(tb, 'beg_balance')

# Control totals
failed = tb[tb['beg_balance_clean'].isna() & tb['beg_balance'].notna()]
print(f"Rows in: {rows_in} | Rows out: {len(tb)}")
print(f"Converted total: {tb['beg_balance_clean'].sum():,.2f}")
print(f"Rows failing conversion: {len(failed)}")
if len(failed) > 0:
    print(failed[['beg_balance']])

Validation (control totals)

• Row count in = row count out. This transformation never adds or drops rows — if counts differ, stop.
• Tie the converted sum to the source report’s printed total. The trial balance footer, GL control total, or batch total should match df['beg_balance_clean'].sum() to the penny. Any difference means a notation you didn’t handle or a sign flipped the wrong way.
• Count of negatives should equal count of CR/minus/paren rows. Compare (df['beg_balance_clean'] < 0).sum() against mask_cr.sum() + mask_minus.sum() + mask_paren.sum() — a mismatch means a value was already negative or a mask double-fired.
• No silent NaNs. pd.to_numeric(errors='coerce') converts failures to NaN instead of crashing — count them and confirm the count is zero (or fully explained).

Exceptions to surface

Route these rows to a human reviewer rather than guessing:

• Rows that failed conversion (NaN after coerce) — often “DR” suffixes, “N/A”, dashes-as-zero, or stray footer text captured as data
• Values matching more than one notation — e.g., (1,234.56) CR or -500-; the sign intent is ambiguous
• Amounts that were already negative in the raw text (-1,234.56) alongside CR notation — mixed conventions in one file suggest the export merged two systems
• Zero amounts carrying CR notation (0.00 CR) — usually harmless, but confirm the system isn’t encoding something else
• Any out-of-balance difference between your converted total and the report control total, with the specific rows driving it