The Ultimate Business Machine - Archives
List of Categories : Commentary * Database * Singapore * Technology * Travel *
Fri 18 Aug 2006
Of Real Numbers, Floats, and Decimals
Category : Technology
My last post about problems handling floating point numbers for financial calculations brought me a couple of responses.
I make an assumption in Luca that all currencies work with two decimal places, and I round them wherever I can and store them to two decimal places. This way my accounts (should) always balance since, for every voucher, I make sure that the debits and credits are rounded to two decimal places and compared to be equal before I allow them to be saved.
But what if I'm working with a currency that doesn't use two decimal places - they use three, or four, or five, or ... none?
Guy Harris says - "Yes, the Japanese yen has no decimal places at all."
That's just the motivation I need to go and dig further because the assumption I used wouldn't work if I want Luca to be used just about anywhere possible.
Luca allows the base currency (the currency you use to base your accounting on and produce the financial statements) to be different from the billling currency and the settlement currency. So what if we get paid in Yen? Rounding the yen to two decimal places would make no sense at all.
So what to do?
I got one other mail from Quintin May that pointed me towards a whole new world of possibilities :
And that's what I did.
Objective-C seems to have an equivalent class - the NSDecimalNumber. It has methods for adding, subtracting, multiplying, dividing, and comparing two objects of the same NSDecimalNumber type, plus conversion to formatted strings for display, with the correct separators and currency symbol, depending on locale.
This looks like just what I need.
Better still, new to MySQL 5.0, there's an equivalent exact-number data type called Decimal.
So if all these pan out, a good strategy to take is : don't round, store all financial values as Decimal data types in MySQL, and load them into NSDecimalNumber data objects in Objective-C. Display them in windows and views as strings formatted according the appropriate locale, set according to whether the displayed value is in the billing, settlement or base currency. Do not add them like you would double or floating point numbers. Use the supplied methods, for addition, multiplication, and especially for comparison.
So, this looks like a very neat idea. But will it all work out? And what about SQLite? It doesn't have an equivalent data type. There's only one way to find out - Just Do It !