Moving decimal bug causes money losses
-
Taken from Slashdot:
Moving Decimal Bug Loses Money - Slashdot
mario.m7 writes "Poste Italiane, the Italian postal service, suffered yesterday from an abnormal computation in ATM and credit card operations, since the decimal comma was not taken into account. The whole sum was therefore multiplied by 100, resulting in a 115,00 Euro transaction being debited as...
Now my question is: what is the cause of this bug?
Probably they are not treating currency as a floating point number, but some sort of integer multiplied by 100.
If this is the case, it can hide a bigger rounding bug.
-
Hows about; since they (like many European countries) use "," instead of "." as a decimal separator, at one point in the code, they format it for display on the screen with a ",". Then later on, use that same screen value instead of the original numeric value, and try to subtract 10,00 from the account, which is either cast to a number as 1000, or they strip nun-numeric characters (based on the programming language syntax, not the written language syntax), and subtract that number from the account.
You can choose to blame:
- The programmers for making the mistake.
- The country for using anything except the normal English-speaking-countries' separator "." in their numbers (how dare any country have their own norms?!!!1!seventeen).
- The programming language for being so heavily tied to the English language (like most common programming languages are) that it forces everyone to use the decimal separator "." instead of allowing localised decimal separator characters.
Aside; It's always good fun trying to work out the meanings of numbers when visiting other countries - in the UK for example, it's normal to use "," as a thousands separator, which can really confuse tourists who are used to "," being a decimal separator, or vice versa.
-
I guess there are many more options mentioned at /., but I've had similar experiences with Oracle and a reporting client. Oracle has "Natural Language Settings" and these include the decimal and thousands separators. I don't know how the 3 instances we have to work with have been installed, but they all have a different combination of settings, and one of the reports started producing percentages like 0, 25, 50, 100, so that was fine, but then it would suddenly jump to .333e+38% (33, with 36 3s following), so all reports were fine, except those the customer saw. Oops. So it might not directly have been a programmer's error, but someone changing a setting or uploading new firmware, something like that. That said, a representation that can change with the phase of the moon or version of the firmware is not the best design choice...
-
@apetrelli said:
Probably they are not treating currency as a floating point number, but some sort of integer multiplied by 100.
Currency should never be represented as floating-point.
-
@morbiuswilters said:
Currency should never be represented as floating-point.
Really? Is that what you learned at school?
Excel has been a very successful software product, both commercially and in terms of meeting actual user needs, so I think "never" is probably a bit strong.
-
I definitely second morbiuswilters here. Never use a non-base-10 floating point representation for money. It's way too inaccurate.
-
@Dr Frankenstein said:
I definitely second morbiuswilters here. Never use a non-base-10 floating point representation for money. It's way too inaccurate.
I'm sure many people agree with you.
However, the point is that huge numbers of people do huge numbers of calculations and transactions, and make many many financial records, using Excel, which uses binary floating point numbers to represent money. The consensus of these actual users is that Excel is not way too inaccurate to handle money.
As a programmer who has done a little bit of math, you think that a non-base-10 floating point representation for money is way too inaccurate. But the facts are against you. The Finance and Accounting industries use binary floating point representations for money.
AFAIK, the banking industry still uses Fixed Point numbers, dating back to fixed-point main-frames and micro-computers. The Currency Conversion definitions are still Fixed Point, dating back to fixed-point main-frames and micro-computers. But the customers of the Banking and Currency industries use Floating Point numbers (Excel), and expect the Bank/Dealer calculations to match the Excel calculations, which they do, because Excel floating point calculations give the same answers.
Practically all the real finance and accounting programming done from 1985 to 2005 was done with binary floating point numbers. I can't speak for after 2005, and prior to 1985 there was a significant amount of fixed-point financial programming, as illustrated by the 8086 processor in the IBM PC, which had Binary Coded Decimal support .
The 80486 processor had IEEE floating point math. 20 years of accounting and finance programming was done with floating point math. If the world is moving back to BCD, it's not because Excel or in-house software packages were "way too inaccurate".
Not that I mind you using what ever is right with your tools. I'm just pointing out that "never" is too strong, and "inaccurate" is wrong.
-
@morbiuswilters said:
@apetrelli said:
Probably they are not treating currency as a floating point number, but some sort of integer multiplied by 100.
Currency should never be represented as floating-point.
What if you're using a system that has big integer mantissa + integer exponents as its floating point type rather than an IEEE float (so called, because, sooner or later if you use these to manage your money, you say, "IEEE! What happened to all of my money?" At least, that's what you do if you're a pedant who gets very upset about losses totaling less than 0.0001% of your total money.)
-
Oh this can be such a can of worms. I found this nice article trying to write a reply and I'm now going to read that instead: http://www.aboutus.org/Stop_Foolish_Roundin
Basically neither just integers nor floating points are good enough to represent currency accurately on their own if you do enough calculations with it.
-
@tgape said:
@morbiuswilters said:
@apetrelli said:
Probably they are not treating currency as a floating point number, but some sort of integer multiplied by 100.
Currency should never be represented as floating-point.
What if you're using a system that has big integer mantissa + integer exponents as its floating point type rather than an IEEE float (so called, because, sooner or later if you use these to manage your money, you say, "IEEE! What happened to all of my money?" At least, that's what you do if you're a pedant who gets very upset about losses totaling less than 0.0001% of your total money.)
0.0001% of $1000000000000.00 is still a lot of money. Which is why large financial institutions need to be careful.
-
@YumYum said:
@Dr Frankenstein said:
Well, that all depends, doesn't it. My wife is a tax consultant who does tax declarations for medium-sized companies.I definitely second morbiuswilters here. Never use a non-base-10 floating point representation for money. It's way too inaccurate.
I'm sure many people agree with you.<snip>
If the world is moving back to BCD, it's not because Excel or in-house software packages were "way too inaccurate".
Not that I mind you using what ever is right with your tools. I'm just pointing out that "never" is too strong, and "inaccurate" is wrong.
At her company, they do a lot of calculations using Excel. If at the end, the balance is off by a few cents, they just let it pass. Otherwise, they fiddle with the rounding functions to make it give the correct result. Floating point IS inaccurate but it depends on your needs if you want to decide if it's TOO inaccurate.
-
@DescentJS said:
0.0001% of $1000000000000.00 is still a lot of money. Which is why large financial institutions need to be careful.
1000000000000 (1 million million) can be exactly represented in 44 bits. You need to go bigger than that before you start to have rounding problems. And to make it even easier, 1 Basis Point is only 0.01%. Most people don't ever use 0.0001%.
But an equally interesting point is that, after you capitalize the interest and calculate net present value, you send the report to the board and to the accountants and to the shareholders showing the value as a number of millions - not even showing the final places. And it doesn't matter because you don't then add or subtract that number and expose the truncation error. You see, for a typical business, there will only be 1 transaction of that size. And it is treated as a special case. If you are a very large hedge fund, and you have thousands of millions of millions in large transactions, maybe you don't do your spreadsheets in Excel, but for a small listed company, your total book is going to be less than 1000 000 000 000.00 -- and for most companies, much less.
-
@bjolling said:
At her company, they do a lot of calculations using Excel. If at the end, the balance is off by a few cents, they just let it pass. Otherwise, they fiddle with the rounding functions to make it give the correct result. Floating point IS inaccurate but it depends on your needs if you want to decide if it's TOO inaccurate.
That rounding error isn't because of the Floating point. You get the same problem with Fixed point. The problem comes from the order of rounding. If you round all the items, then sum, you get a different answer than if you sum all the items, then round. And you always have to round, because the tax percent doesn't come out as an exact number of cents.
The basic historical function of a spread sheet was to compare the sum of the columns with the sum of the rows, which was a basic duty of accountants back when it was all done by hand, and you always get rounding differences when you do the tax. You have to fiddle with the rounding to get the correct result. But in most jurisdictions there is a defined correct result: The tax rules tell you which way is correct.
-
I guess that if you're serious about money, you represent it exactly (which is not with a binary fraction). But you will also have to be serious about the computations. That only leaves arbitrary precision as the One and Only Way, because one day there is going to be a percentage that cannot be done properly with fixed precision. Integer representations have a fixed precision, usually .01, which is obviously not going to cut it with percentages like 0.04553, unless you don't care about precise rounding for quantities less than 0.01.
My wife is a tax consultant who does tax declarations for medium-sized companies. At her company, they do a lot of calculations using Excel. If at the end, the balance is off by a few cents, they just let it pass. Otherwise, they fiddle with the rounding functions to make it give the correct result.
Now that's scary. Fiddling with rounding in Excel. To make the numbers look better. By tax consultants. Brrrr. It only shows how dangerous a little knowledge is.
