Use a money data type

Jason Leung

Businesses use a variety of technologies to build automation solutions: programming languages, databases, spreadsheets, and automation platforms. Most of these technologies allow builders to specify data types for data they process, at least to separate numbers from text. Some handle dates separately as well. However, these technologies rarely have built-in separation for money amounts, leaving builders to make do with basic numeric types.

Currencies

If your software represents a money amount using a number, and nothing else, the amount has in implicit currency. I call this approach asking for trouble because sooner or later someone will neglect to assume the correct currency. Instead, make the currency explicit.

Money amounts have currencies in the same way that measurements have units. Represent a money amount as a pair of two values: the amount and the currency. For the later, using a standard ISO 4217 three-letter currency code, such as EUR (Euro), prevents ambiguity.

Fixed-precision

While your exact age varies from second to second, you’d probably state it as a whole number of years. And irrational numbers, such as π, require an infinite number of decimal places to express their exact value. Currency amounts lie between these extremes, and have fixed decimal precision.

Software must use fixed-precision numbers for currency amounts, with some caveats. First, the number of decimal places depends on the currency: Euro amounts use two decimal places, Japanese yen do not have a fractional part, and a handful of currencies use three decimal places. Secondly, some scenarios have a (legal) requirement to use more decimal places for calculations. For example, currency conversions typically use exchange rates with six decimal places.

Rounding

When you calculate with fixed-precision amounts, you also need to round the results of calculations, e.g. to the nearest Euro cent. Unfortunately, you have to choose between many possible rounding rules, including exotic variants such as bankers’ rounding which Wikipedia describes as:

A tie-breaking rule without positive/negative bias and without bias toward/away from zero is round half to even. By this convention, if the fractional part of x is 0.5, then y is the even integer nearest to x. Thus, for example, +23.5 becomes +24, as does +24.5; however, −23.5 becomes −24, as does −24.5.

Wikipedia also gives the name Dutch rounding for round half to even. Meanwhile, on Dutch tax returns, the Dutch tax office requires that you round all amounts to whole Euro, and allows you to round up or down in your own favour.

Value-specific formatting

Money amounts also require special formatting in different contexts. While you may store money amounts with three-letter currency codes, people are more used to currency symbols for many currencies. So you display EUR 42 as €42. Except that in some countries, you’d display 42€ instead.

As well as regional formats, spreadsheet users may also use some kind of account format that aligns currency symbols vertically, in a column of amounts, or displays negative values in parentheses.

Money data types

Money amounts have enough of their own complications that software should treat them differently from how it treats other numbers. Software that processes money amounts should use specific money data types.