Writing a Validation Rule for a Currency Amount Field

1.234,56 and 1,234.56 are the same amount. So are 1 234,56 and 1’234.56. And 1,234 is either one thousand two hundred and thirty-four, or one and a bit — which is the one case no parser can settle on its own.

What actually arrives

Before writing a rule, know the space it has to cover. All of these appear on ordinary commercial documents.

• Group separator as comma, decimal as point: 1,234.56. Common in English-language documents.
• The reverse: 1.234,56. Standard across much of continental Europe and Latin America.
• Space as the group separator: 1 234,56. The space is frequently a non-breaking or narrow non-breaking space rather than an ordinary one, so a parser that strips only U+0020 leaves a character behind and the number fails to parse for a reason that is invisible in a log.
• Apostrophe as the group separator: 1’234.56, seen in Swiss documents, and the apostrophe may be the typographic one rather than the ASCII one.
• Non-uniform grouping: the Indian system groups the last three digits and then in twos, producing 12,34,567.89. A validator that requires groups of exactly three rejects a correctly formatted amount.
• Accounting negatives: (1,234.56) for a negative, and on output from older financial systems a trailing sign, 1234.56-, or a CR / DR suffix.

None of these is exotic and a single document can contain more than one — a supplier’s own figures in one convention and a converted equivalent in another.

Deciding which separator is the decimal

Work from evidence in this order, and stop at the first rule that fires.

1. Both a comma and a point are present. The rightmost of the two is the decimal separator, and the other is the group separator. This is unambiguous and needs no locale knowledge: 1.234,56 has the comma last, 1,234.56 has the point last. It settles the large majority of real amounts.
2. Only one separator is present and it is not followed by exactly three digits. It is the decimal separator. 1,5 and 1.5 and 12,345678 are all decided.
3. Only one separator, followed by exactly three digits. Genuinely ambiguous. 1,234 is 1234 under one convention and 1.234 under the other, and both are numbers. See the last section.
4. A space or apostrophe is present. It is a group separator; no locale uses either as a decimal point. Strip it — including the non-breaking variants — and re-apply the rules above to what is left.

Notice what this does not do: it does not take a locale as configuration. A locale parameter is the wrong shape for document extraction, because the document’s convention is a property of the document rather than of your system, and a mis-set locale converts amounts by a factor of a thousand without any error.

Minor units, and why two is not the answer

Store money as an integer number of minor units together with the currency code, never as a floating-point number. Binary floating point cannot represent most decimal fractions exactly, so a total accumulated across a hundred lines will not compare equal to the printed total, and the residue looks exactly like an extraction error — which makes the reconciliation on cross-field amount validation untrustworthy for reasons that have nothing to do with the document.

The number of decimal places is a property of the currency, not a constant. ISO 4217, maintained by the International Organization for Standardization, assigns each currency a minor-unit exponent: most are two, several are zero, and a few are three. So a validator that requires exactly two decimal places rejects correctly formatted amounts in the zero-exponent and three-exponent currencies, and a converter that multiplies by one hundred unconditionally is wrong by a factor of ten or a hundred for them.

Negatives that are not a minus sign

Sign handling deserves its own step because the conventions carry meaning that a naive strip destroys. Parentheses around an amount mean negative in accounting presentation. A trailing minus is a mainframe and ERP output convention. CR and DR suffixes mean credit and debit, and which of those is negative depends on the account’s side — so do not translate them to a sign at parse time. Record the marker as extracted and let a layer that knows the accounting context decide.

The related failure is a document where the sign is carried by the document type rather than by the amount: on a credit note every line is a credit and no minus sign is printed anywhere. Storing those as positive is correct extraction and becomes an error the moment someone sums a mixed set, which is why the document subtype belongs in the record alongside the amounts — see designing for unseen variants.

The rule, end to end

import re
from decimal import Decimal

SPACES = "\u0020\u00a0\u202f\u2009'\u2019"      # incl. NBSP, narrow NBSP, apostrophes
MINOR_EXPONENT = {"JPY": 0, "KRW": 0, "GBP": 2, "EUR": 2, "USD": 2,
                  "KWD": 3, "BHD": 3, "TND": 3}      # from the ISO 4217 list

def parse_amount(raw, currency):
    s = raw.strip()
    negative = False

    if s.startswith("(") and s.endswith(")"):
        negative, s = True, s[1:-1].strip()
    if s.endswith("-"):
        negative, s = True, s[:-1].strip()

    marker = None
    m = re.search(r"\b(CR|DR)\b$", s)
    if m:
        marker, s = m.group(1), s[: m.start()].strip()

    s = re.sub(r"[^\d.,%s]" % re.escape(SPACES), "", s)   # drop symbols, codes
    s = "".join(ch for ch in s if ch not in SPACES)        # groups, incl. NBSP

    has_dot, has_comma = "." in s, "," in s
    if has_dot and has_comma:
        dec = "." if s.rfind(".") > s.rfind(",") else ","
    elif has_dot or has_comma:
        sep = "." if has_dot else ","
        tail = s.split(sep)[-1]
        if len(tail) == 3 and s.count(sep) == 1:
            return {"ok": False, "reason": "ambiguous separator", "raw": raw}
        dec = sep
    else:
        dec = None

    if dec:
        s = s.replace("." if dec == "," else ",", "").replace(dec, ".")

    try:
        value = Decimal(s)
    except Exception:
        return {"ok": False, "reason": "not numeric", "raw": raw}

    exp = MINOR_EXPONENT.get(currency)
    if exp is None:
        return {"ok": False, "reason": "unknown currency", "raw": raw}
    frac = -value.as_tuple().exponent
    if frac > exp:
        return {"ok": False, "reason": f"more decimals than {currency} allows",
                "raw": raw}

    minor = int((value * (10 ** exp)).to_integral_value())
    return {"ok": True, "amount_minor": -minor if negative else minor,
            "currency": currency, "marker": marker, "raw": raw}

The Decimal type rather than a float, the exponent looked up rather than assumed, and the raw string kept on both the success and the failure path. The last of those is the one most often dropped and the one a reviewer always needs.

When it genuinely cannot be decided

Return a failure for the ambiguous case rather than a guess, and then resolve it at document level with evidence the single field does not have.

The strongest evidence is arithmetic. If the document has a total and line items, try both interpretations and keep the one under which the document foots — a thousand-fold error will not add up, so the check is decisive. Second is consistency: if any other amount on the document is unambiguous, its convention applies to all of them, since a single document does not mix. Third is the currency and country context, which is weak evidence and should be recorded as an assumption on the field when it is what you used.

Where nothing decides it, route the field to review with both candidates attached, in the same way as the ambiguous date on date field validation. A wrongly resolved amount is a factor-of-a-thousand error that will pass every downstream sanity check on the record itself, so this is exactly the case where an unresolved field is far cheaper than a confident one.