| The Ultimate Business Machine Technology, business And, not least, about by: Bernard Teo
|
The Ultimate Business Machine - ArchivesList of Categories : Database * Technology * Commentary * Singapore * Travel * Fri 18 Aug 2006 Of Real Numbers, Floats, and DecimalsCategory : Technology/decimals.txt 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 : "I read your post regarding floating point and rounding in Luca. It has been my experience when working with financial data to NEVER use floating point numbers. In Java, the BigDecimal type is preferred for this type of data representation. I'm not fluent in Objective-C, so I don't know if it has an equivalent data type. This article goes into excruciating detail on floating point arithmetic: http://docs.sun.com/source/806-3568/ncg_goldberg.html. Try a Google search for "objective-c" "bigdecimal". 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 ! I've often asked myself why I bother writing a weblog or why people would bother coming over to read it. Well, this is one benefit. I get responses. When I'm stuck, they point me to new, fortuitously more productive directions. And I hope you, dear reader, learn something useful also when I do. |
Mac@Work
Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:
all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks. |
||||||||||||||||||||||||||
