How to handle money and currency conversions

 Most of the bugs and unnecessary complexity that come from working with financial data in a computer program can be reduced or eliminated by creating a simple Money data-type that enforce the top three rules of dealing with dosh:
  1. Never use float or double
  2. Always annotate it with its currency
  3. Never mutate the value of a money variable
 I've been working with financial applications and e-Commerce systems intensively for at least twelve years now, most recently in my new iPhone app Spent Better, which not only performs complex financial calculations but must also deal with multiple currencies and currency conversions. What I've learned is that money--as a value manipulated in a computer program--is like nuclear fuel: powerful stuff that will misbehave catastrophically if not handled carefully. 

The Most Important Rules for handing Money in Computer Programs

1: NEVER use float or double to store or manipulate monetary values

 It seems obvious at first that float or double is the ideal numeric type for storing money values, but it's actually the worst: floats and doubles are stored as an exponent plus a fraction, that is: how big the number is (the exponent), plus approximately how much to multiply it by (the fraction). The fraction is stored with as many bits as the computer has left after storing the exponent, so the precision degrades as the number gets bigger. This also means $160.01 is not stored as the digits "160.01", it's stored as the fraction 1.600999999999999996 with an exponent of 1*10^2. As you work with those values--adding, multiplying, dividing, subtracting--you lose more and more precision. Pennies/cents and even whole dollars/euros can evaporate in a series of roundings, and that's just on the low-end. Remember the movie Office Space? Rounding errors add-up to big money.

 Floats are meant to store numbers of arbitrary size, while Decimals are meant to store numbers of arbitrary precision (within the limits of the type's size). The Double is just a float with more bits to maintain twice as much precision, but not the kind of precision you need for monetary values.

 So monetary values are best stored in either decimal types or as integers. If your programming language supports decimals then use those, if not use integers to represent the smallest unit of currency (the penny or cent). If your programming language or database doesn't have a decimal type then look for numeric

A note about databases

 Some databases have an explicit money type, which will safely store values with appropriate precision, but rarely store the currency they're recorded in. I strongly recommend creating another column to record the currency, even if you think you'll never need it. I made this mistake many times until recently, but it will bite you on the ass when you need to expand your software in the future; you'll be facing the same problem that programmers faced with the Y2K crisis: was this value given relative to 1900 or 2000? Should I assume any value above "49" relative to 1900 and any lower value relative to 2000? Was this value given in your base currency or a localized currency?

 The reason will become more clear later in this article.

Is the Decimal type big enough?

 In most languages with this type, a Decimal is 128-bits large with a range of about -1.0 * 10^28 to +7.9 * 10^28. Unless you're Emperor Palpatine budgeting for the construction of a few thousand Death Stars and paying for them in Zimdollars, it'll be big enough.

2: Create a Money type if financial calculations are at the core of your app

 Financial and commerce apps really should have a Money class that stores a value plus the currency it's measured in, and any functions that do math on those values should work with that type exclusively. These are the reasons:
  1. Monetary values are a special kind of number and you should be using your compiler or runtime's type-checking system to watch it for you. For example, interest rates are also stored as decimals, but you don't want to confuse them: let the compiler help you enforce the difference
  2. Currency is intimately connected to the value of a monetary type. 1 trillion Zimbabwean dollars isn't even remotely worth 1 U.S. cent

What should be in the Money type

  1. A read-only Decimal Value property
  2. A read-only String CurrencyCode property based on ISO 4217 (don't make it an Enum, you will need to add more without having to recompile)
  3. Depending on your language's features, something like a static implicit operator to create new instances via assignment with a default currency. I.E. so you can do this:
    Money startingBalance = 100.0;
  4. Also depending on language, overloads for arithmetic and comparison operators
If you chose to implement operator overloads then use them to enforce currency equivalence, but never perform currency conversion. For example:

    public static bool operator >(Money amount1, Money amount2)
    {
        if (amount1.CurrencyCode != amount2.CurrencyCode)
            throw new InvalidOperationException("Cannot compare amounts of different currencies");

        return amount1.Value > amount2.Value;
    }

There are three reasons not to do currency conversions in your Money type:
  1. It's meant to be a fast, lightweight, primitive type with no side effects--you shouldn't be doing any invisible database or hash-table lookups in it
  2. If the exchange rates are being updated on another thread while you're in the middle of a long calculation, the results will be wrong
  3. You will probably find it necessary to do calculations on historical exchange rates, such as for accounting
You also want to throw exceptions on currency mismatches because this is a genuine sign of a programming error: you should be normalizing all your amounts to a common currency before you do any math on them, and if you haven't then you don't even want those routines to run at all.

Implementing all those overloads will be a pain in the ass, but the benefit is more security in a field where it's desperately wanted. In many financial programs the bugs of the kind that a compiler can detect for you, or that abort transactions before they're committed, can save lots of cold, hard dosh--sometimes millions or billions of dollars/pounds/euros/riyals worth of dough. Somebody's retirement or college fund can disappear in a dumb comparison error.

3: The Money type should be immutable

 If you create a Money type then the Value field should be private, with only a public getter to retrieve its value, never a public setter. These values will be set by the constructor. The result of adding, subtracting, multiplying, dividing or performing any other math function on the value should be a new instance of Money. You should think of money variables in your program as being like coins or cheques of arbitrary-but-fixed value.

 This is because your Money type will be pass-by-reference, not pass-by-value. If you have a function that modifies a value as part of its calculation you don't want it to have side effects in other parts of your program. Suddenly the function that takes an investment and computes its future value will cause other calculations to work with the future value by mistake. If your'e doing currency conversions you don't want to accidentally tell your user that she owes a hundred times more on her electricity bill because you converted from Dollars to Yen earlier.

4: Interest rates and other multipliers can be whatever precision they have to be

 An interest rate can be precise to more than two digits, but due to the way most compilers or runtimes work it's still better to store them in decimal variables rather than floats or doubles because the result of multiplying them with a monetary value will be a decimal type with appropriate precision. To multiply it against a float or double would mean converting the decimal money value into a float or double, destroying the precision you need to keep.

 Interest rates or currency exchange rates frequently stretch to 4 decimal places or more. The decimal type can still handle this, and you shouldn't try to round their values, either. a 0.39 interest rate applied to a million dollars doesn't give the same result as 0.39012 applied to a million dollars. I can pay a few bills with the difference.

5: Some prices are accurate to 4 or more decimal places

 Gasoline is priced to the tenth of a penny (because it's a liquid sold without a container--an arbitrary dollar-amount of gas sold at $3.999/gallon is less than the same amount's worth sold at $3.99/gallon, that's how the bastards get'cha), and securities can be priced to the hundredths or thousandths-of-a-cent.

 You remember I said above that you could use integers to count pennies if your programming language doesn't have a decimal type, and this is one of the circumstances where that could become a pain in the ass--you'd have to explicitly keep track of what the divisor is to get back to dollars or euros or pounds. The decimal type will do that for you.

6: Prices are not the same thing as Money

 The price for a product or service will be measured in money, but never confuse the two. Like how money values need to be associated with a currency, prices need to be associated with regions, locations, customer type (retail, wholesale, nonprofit, etc.), bundling deals, quantity discounts and so-on. 

 For example, you cannot assume the price of a Big Mac sandwich in Canada is the same as the price of a Big Mac in the US with the Canadian exchange rate applied to it; it's going to have a unique price determined by many different things such as the supply of beef and lettuce. This might also be a Big Mac sold in an airport to a captive audience and will be more expensive than the same sandwich sold 500 yards away in a shopping plaza.

 You probably won't need to create a Price class, though. If you did, don't inherit from your Money class (remember, inheritance is an is-a relationship, and a Price is-not-a kind of Money), instead use composition, eg: you have a hash table with all your regions, or discount ladders, and the value members are instances of Money.

Handling currency conversions

1: Use a "base currency" for any amount < $100,000

 Money is a market itself: if someone in Saudi Arabia wants to buy a fleet of Airbus jets then they'll have to buy a few billion Euros to pay for them first. To do that they have to go to the money market and find someone willing to sell Euros for Riyals for the lowest price-in-Riyals possible, and a multi billion-Euro purchase will definitely have an effect on the exchange rate: the value of Euros will increase against the Riyal as the Saudis try to find a willing seller. The opposite see-saw will happen when someone in Europe wants to buy Saudi oil, and transactions like these happen every hour of every business day.

 Such a huge transaction may not affect other currencies and the exchange rates between them, so the Foreign Exchange--or "Forex" rate--between Euros and US Dollars and Riyals and US Dollars would be unaffected, but the discrepancy doesn't last for long: maybe only a few seconds, or hundredths of a second. Wherever a discrepancy exists there's profit to be made because someone will quickly use their Euros to buy as many Riyals as possible, then buy US Dollars with the Riyals, then buy the Euros back with the Dollars for a profit. This practice is called arbitrage, and it's what keeps the world's currencies in co-equivalancy with each other: every time someone does a trade the seller will increase their price to balance the demand.

 That means converting from Euros to another currency like Yen directly doesn't always give you the same result as converting Euros-to-Dollars-to-Yen. But the good news is that if you're dealing with any amount less than a few hundred-thousand US dollars the discrepancy is so small and corrected so quickly that the difference is barely a penny, and that means you can write code that assumes its safe to store exchange rates relative to a single base currency. In Bretton Woods tradition I use US Dollars for my base currency, but any world currency that isn't currently undergoing hyperinflation will do.

 Why do I say USD$100,000? Because most providers of exchange rates will provide rates for the "quote" currency up to 6 decimal places. If 1 US Dollar buys 0.639751 British Pounds then you need to buy more than $100,000 worth of Pounds before the difference between 0.639751 and 0.639752 amounts to more than a penny. But your "real-world" threshold may be even higher, because it's going to be set by how much money you'd lose versus the cost of paying for a near-realtime Forex feed and the code to make use of it. Your credit-card transaction fees will probably dwarf losses to fluctuations this small.

 A rule of thumb might be: unless you're developing a trading platform for an investment bank you can get away with doing all your math against a base currency.

2: Store exchange rates as a decimal multiplier of the base currency

 If it costs 1.2 Canadian Dollars to purchase 1 US Dollar then you store {"CAD", 1.2}, and if US $1.27 buys 1 British Pound then you store {"GBP", 0.7874015748} so that when you type 12 * Rates["CAD"] you get 14.40, or 12 * Rates["GBP"] you get 9.45, or amount * Rates[LocalCurrency] always gives you a consistent result. Some sources of exchange rates mix foreign-to-base and base-to-foreign but should always indicate when. 

If the exchange rate is given as base-to-buy-foreign then you store as-is, but if it's given as foreign-to-buy-base then divide 1 by that number and store the result.

3: Only do currency conversions on input and output, never in-between

This is an emphasis on Rule 1: you may need to convert a user's input to a base currency for the sake of Rule 1, but never do any other conversions until just before you calculate taxes, update the screen or send something to a printer. The guts of your program should only ever need to work on the base currency, especially things like tax and shipping. Furthermore, only do the conversion as part of the output and never change the values of internal data structures. Ie: as you're preparing the output, make a copy of the monetary value, do your currency conversion, then your formatting, and then print it to the screen.

4: Never convert user input to a base currency before storing it

 Your database or file format needs to store the currency that a value is given in, and you must never store user-entered values after converting them to any base currency. Since exchange rates are constantly fluctuating, it will confuse and annoy your users if the numbers they entered keep changing every time they look at them. Consider the "Big Mac" scenario: the seller of a product may change the price less than once a year, but the exchange rate can change every minute depending on the frequency of your feed. The value that the user enters might also have meaning peculiar to them, and something you might only think you understand because of the nature of your program. I want my bank to transfer $10 to a savings account every week, but I won't be amused if it floats between $9.98 and $10.02 from week to week.

 Even if your program's purpose would treat the input after conversion at that day's rate anyway, consider storing a snapshot of the current exchange rate along with the unadulterated input. My advice might be overkill for your app, but it doesn't cost much to store a bit of history and do the math later. Maybe a future application could use the original value.

 This is why I strongly recommend storing the currency with any value you put in a database or file.

Obtaining Fresh Exchange Rates

 Nobody provides daily exchange rates in machine-readable format for free anymore. There was a time when the Federal Reserve Bank of New York provided a free XML feed with about 12 currencies, but they closed it down in 2010. While there are many web sites that offer exchange rates for free on their web sites, those numbers are embedded in HTML designed for consumer web browsers and not for e-commerce software or other apps. You could scrape them, but be prepared for a lifetime of maintenance as your sources update their site design. This is especially true if you scrape web sites from a popular app: they'll eventually notice the increase in traffic, trace it to you, and make a change that breaks your scraper.

 What you need is an API or feed that only changes in backward compatible fashion and is licensed and served in a fashion that suits your app. Here are a few that I've evaluated for my own needs.

openexchangerates.org

 This organization provides free and paid feeds and gathers its data by aggregating from other providers once per hour. The feed comes in JSON format, carries quotes for about 157 currencies relative to the US Dollar, and requires an API key. It's good for:
  • Applications that can deal with converting through USD as a base currency
  • Web sites that only need hourly updates (Free account)
  • Apps where the customer base will make less than 10,000 queries per month ($10/month account)
  • Apps where the customer base will make more than 10,000 month ($30/month account)
 Openexchangerates.org also offers a "Distributor" account for those taking "millions of requests per month" or reselling access. The price isn't advertised, but they gave me a quote that was under 3 figures per month.

exchangerate-api.com

 This company does things a little differently: rather than give you a URL with a machine-readable list of exchange rates, they provide web API that converts from a single "from" amount to a single "to" amount. If you need to convert 500 Chinese Yuan into Indian Rupees you'd post a request like this:

http://www.exchangerate-api.com/CNY/INR/500.00?k=YOUR_API_KEY

 They're implying that you should call their API every time you need to perform a currency conversion. I don't think this is a good idea, because once you have the base conversion rate then everything else is just multiplication. Of course you could just issue a separate API call for each currency you work with--asking for the conversion of "1.00" to the target currency--and cache the results, but it shouldn't be necessary to do that. In fact, the whole idea is dumb: you're spending enormous amounts of network overhead to do trivial math. They should just give you a feed.

 They also provide a API that adds IP geolocation to guess your web site visitor's currency, but I still don't think it's worth the cost and overhead. IP geolocation is offered separately by other companies, and you can write your own logic for dealing with unlocatable visitors.
Comments