The
Ultimate
Business Machine

Technology, business
and innovation.

And, not least, about
the Mac.

Weblog Archive Cutedge

by: Bernard Teo






Latest Joy of Tech!

Joy of Tech ... from Geek Culture



Creative Commons License

Copyright © 2003-2012
Bernard Teo
Some Rights Reserved.

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/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.

Posted at 2:00AM UTC | permalink


Mac@Work
Put your Mac to Work

Sivasothi.com? Now how would you do something like that?

Weblogs. Download and start a weblog of your own.

A Mac Business Toolbox
A survey of the possibilities

A Business Scenario
How we could use Macs in businesses

VPN Enabler for Mavericks

MailServe for Mavericks

DNS Enabler for Mavericks

DNS Agent for Mavericks

WebMon for Mavericks

Luca for Mavericks

Liya for Mountain Lion & Mavericks

Postfix Enabler for Tiger and Panther

Sendmail Enabler for Jaguar

Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:

  • Apache 2 Web Server
  • Postfix Mail Server
  • Dovecot IMAP Server
  • Fetchmail
  • SpamBayes Spam Filter
  • Procmail
  • BIND DNS Server
  • DNS Agent
  • WebDAV Server
  • VPN Server
  • PHP-based weblog
  • MySQL database
  • PostgreSQL database

all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks.