The Lounge - CodeProject

I wouldn't like to make any assumptions about how they store their data.

Don't forget, the product price could be in a different currency to the currency they're showing you, and might attract different levels of tax depending on where it ships from and where it's delivered to. It's not going to be as simple as a single FP value. Smile | :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

For small differences, what you say could be what's happening.

When it's an order of magnitude difference - well, I don't think so.

Furthermore, pricing, currency-to-currency (thus country to country) is not merely by calculating a currency conversion. It's more like what you pointed out - various polices affect the price. A separate price listing for each of the markets they support. Separate product availability, shipping costs and regulations, &etc., would be required.

You're talking about, in this case, one of the largest corporations in the world. I don't give them that slack for a phenomenon becoming both more prolific and bold.

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Quote:
the values are probably stored as FP, and should sort properly.

The number one rule when dealing with currency in software is not to use floating point for currency.

Unless you like bugs.

First of all, we talking about sorting - so what bugs are you talking about?

Secondly, they have, really, two other options:

1 - Store as integers and divide by 100 (for US$, at least) - so it's back to FP arithmetic
2 - Store as string values - in which case the sort will fail (unless stored left-paddded with blanks or 0's) - and you'll still need to convert them, along the way, to FP in order to do arithmetic with them.

As for your original premise? Bugs? NOT! Round-off errors due to their representation in memory is something a REAL programmer keeps in mind. How much do you think anyone spends on the Amazon website where 64-bit FP's round-off errors will become significant?

There's no excuse for the high priced item to show up in the middle so often, except, of course, marketing.

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Quote:
First of all, we talking about sorting - so what bugs are you talking about?

Using FP for currencies is a bug.

Quote:

1 - Store as integers and divide by 100 (for US$, at least) - so it's back to FP arithmetic

That's not how it is done. No FP is used other than for display.

Quote:
2 - Store as string values -

That's also not how it's done. Currencies have to be stored as a fixed-numeric type.

Quote:
As for your original premise? Bugs? NOT! Round-off errors due to their representation in memory is something a REAL programmer keeps in mind.

Software in banking systems have to be certified (I write EMV and related software, including all the other card processing on the back ends), and the software does not get certified if you are using FP for currencies. They call it a Bug. An Error. A Defect.

Whenever you are dealing with currency, using FP for it is a bug. You could have spent 2m on google finding this out.

Look at all the standards that have to be adhered to for banking and currency software - currencies are specified in specified fixed-numeric types. See all the ISO standards that banking and transaction interchange are written to: No FP.

But, hey, as a "REAL" programmer maybe you should go tell all these institutions and regulators why FP is not a Bug. After all, I've only been employed as a programmer for 25 years, writing interbank switching software and transaction processing software; what could I possibly know about the datatype of currencies.

Sh*t, even the PostgreSQL (or MySQL, or MSSQL, I forget which) manual warns not to store currency as floating point types. Of course, I'm sure a REAL programmer like yourself read the SQL rationale for the NUMERIC type, right?

I mean you wouldn't be posting so arrogantly if you had known about the existence of DECIMAL in the SQL ANSI standard, and why they put it in? Right?

First, none of what you said explains, in the least, the original premise: they they cannot seem to sort numeric values, and furthermore, pretty much any numeric type sorts those values perfectly well - regardless of storage, except for string-types.

So - now - let's say you store your numeric types any which way you think is great. How do you represent them for calculations? BCD, I suppose.

Actually, here, we store data in the SQL currency datatype.

Interestingly, not being part of the banking (or other) financial system, Amazon can store the prices of items any damn way they wish. You did read that, didn't you? PRICES OF ITEMS ?

So, aside from the risk of you financial guys doing arbitrage on the price of Kindle's, why would Amazon bother with the overhead on how to display or calculate pricing other than in the most efficient way possible? The 100th's of a cent error? Rounded up or down in a business - and the final value, that which is billed, is the relevant value.

As for "real" programs - I'm from the world of arbitrary precision values - scientific things, such as modeling, can iterate something tens of millions of times - and something rounded off in the precision of the 24th decimal place can come back to haunt you.

But here's the deal - unlike contract-software made by-the-book (or by the regulation), it was up to us to determine when the overhead was worth the price. Even considering the CPU's native bit-width (16 or 32 bits, then).

Your solution is mandated. When choice enters the matter, the real decisions are made from knowledge and experience as to the best tools for the job.

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Quote:
First, none of what you said explains, in the least, the original premise:

I wasn't explaining the original premise, I was explaining why storing currency as FP is a bug.

Quote:
As for "real" programs - I'm from the world of arbitrary precision values - scientific things, such as modeling, can iterate something tens of millions of times - and something rounded off in the precision of the 24th decimal place can come back to haunt you.

Good for you, I was a research scientist for 7 years and worked on multiple different research projects at the Council for Scientific and Industrial Research. I already know what FP is and isn't good for. I was explaining that FP being used for currencies is a bug. I also spent more than a few years writing embedded software that fell under munitions regulations in all Western countries, most of African and Middle Eastern countries, and all the larger Eastern countries, specifically, software to control detonators and detonations

That's right, I was literally a goddamned rocket scientist. And in the embedded world, using FP is also considered a bug until it is formally motivated (and the motivation is accepted) on a case by case basis.

Quote:
Your solution is mandated.

When all the different authorities across the world on banking and currencies, and all the data storage experts (MSSQL, Oracle, PostgreSQL, DB2, etc) all agree that storing currencies as FP is a bug, you'd have to be a real moron to claim that it's only because of a mandate that it is a bug.

Quote:
When choice enters the matter, the real decisions are made from knowledge and experience as to the best tools for the job.

And yet you thought there were only two ways of storing currencies - your experience is on display with that post you made.

You could have avoid appearing the way you do by simply searching google for 2m to determine why storing currencies in FP is a bug.

You can still do that - can you find even one link that agrees with your assertion that storing currencies as FP is the correct thing to do? Just one, authoritative, known expert in either programming or banking will do.

Now that you finished beating your chest, you seem to have fulfilled the promise of what appears to be a very narrow scope of vision: you cannot see the forest because the damn trees are in the way.
Stop citing regulation and tell me, once and for all, how it matter how the data is (FP or one of your banking formats) will have any effect on a sort?

And you keep harping on how I should google why FP is a bug – but it’s totally irrelevant to the real point. Similarly, you complain I only mentioned to forms of financial storage types – where did I claim any sort of exhaustive search?

Oh – yes – a rocket scientist – beating your chest some more. I separated uranium isotopes on a table top in a tube 30cm long . . . roughly ten times better than the current stat of the art by mass based methodologies (gaseous diffusion, gas centrifuges, for example). So that make me a nuclear scientist? And that, part of the very earliest days of infrared laser photochemistry. Do you realize that neither that, nor your bomb-making credentials, mean sh*t to this argument. In fact, I would be quite ashamed to try to throw them out in any way as some sort of “so there! Gotcha !”.
Still, there’s something to be learned from this pissing contest we’re in: something about staying on topic.

I use FP because where I use it makes absolutely no difference. I use the numeric types that best match the native bandwidth of the processor I am targeting. If I perceive there will be a problem with one data type I’ll use one more appropriate – but it’s ludicrous to label something a bug because it doesn’t meet the particular standards of a totally unrelated consideration. How the hell would string a price as a FP digit hurt anything for Amazon? They use what they store, calculate the charges, surcharges (taxes), and shipping, and then the billing is done. The final value sent off for the accountants – who can do whatever the hell they want with it.

And give it a thought: storing a unit price is not necessarily the same as storing currency.

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Quote:
Stop citing regulation and tell me, once and for all, how it matter how the data is (FP or one of your banking formats) will have any effect on a sort?

I never claimed that it did. You posted:

Quote:
the values are probably stored as FP, and should sort properly.

To which I said that storing currency values as FP is a bug. And to which you took issue because you thought there were only two ways to store currency values:

Quote:

Secondly, they have, really, two other options:

1 - Store as integers and divide by 100 (for US$, at least) - so it's back to FP arithmetic
2 - Store as string values - in which case the sort will fail (unless stored left-paddded with blanks or 0's) - and you'll still need to convert them, along the way, to FP in order to do arithmetic with them.

Quote:
but it’s ludicrous to label something a bug because it doesn’t meet the particular standards of a totally unrelated consideration

I never claimed that using FP was a bug, I said that using FP to store currency values is a bug.

Quote:
The final value sent off for the accountants – who can do whatever the hell they want with it.

That's not how it works.

You literally thought that currency values would be stored as FP, and when someone pointed out that doing so is a bug you took offense.

Quote:
Now that you finished beating your chest,

Yeah, maybe you shouldn't have lead with beating your chest and talking about how "real" programmers do things. Then I wouldn't have had to point out that I was employed as a scientist for seven years, that I write transaction software currently, that I have been employed writing software for the last 25 years and that part of that time was spent writing software on actual detonation systems in rockets.

Storing currency values as FP is a well-known rookie mistake, which you no doubt found out when trying to find a link that supports your assertion that there are only two ways to store currency values - strings and FP.

And it doesn't matter now how much you bleat about what Amazon may or may not have done, you have already posted the embarrassing assertion about currency values being stored as either strings or FP.

That post is here to stay because I quoted it.

Member 13301679 wrote:
That post is here to stay because I quoted it.

WTF does that mean?

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Excuse me... The price of an item in an online store IS a currency value. It will be processed as monetary value when people do buy the item. So, yeah, don't use floating point in anywhere of it.

Instead of dogma, do the tests I noted: how large is the difference in the example given? When would it (ever) show up in a result? Retail sales and financial institutions both deal in money/curacy, but not in the same way.

Well - I happened to have my SQL open and here's a test, done for you:

SQL

DECLARE @a float = 0.1
DECLARE @b float = 0.2
DECLARE @c float = 0.3
SELECT  @c-@a-@b

Result:
-2.77555756156289E-17

Now, you tell me how this would effect retail when prices are rounded to 1E-2 ?

Sure - you can work out a unique scenario. Note, too, that when I did it like this:
SELECT (0.3 - (0.2+0.1) ) I got 0.0 as the result
Also, if I declared a DECIMAL (instead of FLOAT) I get zero.

Am I saying that FP doesn't suffer from a loss of precision due to the inability to represent most non-whole-numbers precisely? NO. I'm saying that for practical use, in normal business environment, the loss would never show. Comparisons - done after rounding - would work fine. Indeed, even in the most precise decimal representation, if some item is priced at 3 for $1, how much does one cost? How is that represented? An infinite number of 3's or a round off error? If you buy the 3 items as 3 separate line items, does it now equal $1.00? Yes? The system has rules to handle it. No? Then you have gained nothing in a retail scenario, except the more CPU-intensive processing.

[edit]
These values eventually need to be stored: what data type do you use in MS SQL Server ?
[/edit]

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

modified 11-Jan-21 9:00am.

This looks like something I need to know, but I've spent a lot more than 2m and can't find it.

ISO 4217 is an obvious place to start but nothing there.

Could you provide a link please. Or a search term that would get me there.

I do have an app that makes use of 2 (at the moment) currencies - and I'm using FP.

TIA.

Quote:
This looks like something I need to know, but I've spent a lot more than 2m and can't find it.

ISO 4217 is an obvious place to start but nothing there.

Could you provide a link please. Or a search term that would get me there.

I do have an app that makes use of 2 (at the moment) currencies - and I'm using FP.

TIA.

Off the top of my head try checking the ISO8583 data elements. All links in the chain of authorisation and settlements are done in some variant of the ISO8583 data elements (which, IIRC, are fixed-numeric of 12 or 14 digits for any field holding an amount).

This includes the acquirer PoS/terminal database, the merchant's stock database, the switches' message queues, the bank's transactional/rely system and storage and the issuer's database. They all religiously follow that.

The reason that using FP for currency and/or money values is considered a bug is due to the inaccuracy of FP (whether 64/80 bits): depending on the implementation, "0 != -0", "NaN != NaN", etc; equality comparisons cannot be made on FP calculations. For example, guess what the following program prints out (using IEEE754 implementation):

int main (void)
{
   float a = 0.1;
   float b = 0.2;
   float c = a + b;
   if (c == 0.3) {
      printf ("FOO");
   } else {
      printf ("BAR");
   }
   return 0;
}

FP cannot be reliably compared for equality. The above program, rewritten in any language that uses IEEE 754 floats, will always print "BAR". In FP, adding an FP 0.1 to an FP 0.2 does not result in an FP 0.3 - it results in something quite close though, but you cannot compare for equality.

Your app really should use a fixed-numeric (also called fixed-point, fixed-decimal, decimal or numeric) datatype for storing money, because money is always divided (tax, etc) and converted to other currencies, and the imprecision of FP arithmetic means that it is always going to have small errors. When talking to a forex system using their API, you'll find that they don't do their representation of money using FP.

If you're using an OOP language a numeric class can be made (or download one of the free popular ones) so that you get error-free fixed-point arithmetic. If you're storing the values, store them as a numeric/decimal fixed-point type, because if you read in an FP value that you just wrote out to disk, and compare it with what you thought you wrote, you'd find that the equality comparison sometimes fails (just like in the above code sample).

Thanks, now I see what point you were making. It's reasonably obvious that multiplication and division will usually require truncation or rounding but the addition and subtraction issue (as in your example above) might be less obvious to some.

My exchange rate provider supplies the conversion as a JSON string, to six decimal places. Any difference from rounding in this particular app is immaterial. A few hundred transactions in any month, with 20 or so different daily exchange rates. My client needs to know the local currency amount for each transaction, but only the total of all the foreign currency amounts.

I am using an OOP language, but probably not one that you use. Delphi (which uses IEEE 754) provides a floating-point function with an Epsilon parameter - the maximum amount by which 2 floating-point numbers can differ and still be considered the same value. I might write, in your example,

if SameValue (c, 0.3, 0.00001) then...

and it would print FOO.

Thanks for the ISO 8583 reference. It's long, and has many normative references - I haven't read it all Laugh | :laugh:

Quote:
I am using an OOP language, but probably not one that you use. Delphi

Hah

Not quite ... Smile | :)

While not an expert, I've done a few desktop GUI projects in Lazarus recently (2018). I chose Lazarus over Delphi because my primary target was Linux and Delphi had poor-to-non-existent support for Linux, *BSDs, etc.

Besides, Lazarus appears to be in active development while Delphi development has stagnated in recent years.

The points are somewhat well known - but you then need to think of the situation in actual practice.

Work on a typical bill for online sales. "Pretend prices are stored as FP values"

Now generate an order. What you have is, almost invariably, a series of two-digit prices that are, indeed, subject to round-off errors. As you calculate the bill, you can add to or diminish item-counts and the price recalculated. When an amount reaches 0 (a uniquely annoying case as presented by the comment you responded to), it's 0 (an integer, by the way) * the price. This would be equal to true 0. For those that are not zeroed, there will, in general, be a round-off error in the representations. These will ultimately all be represented in the total. The total, however, is always rounded to a (for most currencies) a 2-decimal value (for Yen, as an example, they could possibly use integers?). So - if you consider the magnitude of the accumulated round-off errors, how will they effect the value in the second decimal place after rounding?

The reality, in a Real World Transaction, is that the given example using 0.1, 0.2, and 0.3 displays the error but does not display a retail reality: By how much do they actually differ in YOUR system? That is, (0.1 +0.2) - 0.3 = how much? Not zero? Correct! How close to even .01 does it get, that is, the smallest unit of currency?

Indeed, in the financial world, with money in the billions and even trillions of units, the errors can accumulate to real amounts (for each transaction, and this could be exploited). Possibly a uniquely bad problem in currency exchange markets.* But my point was about a business, even as large as Amazon, where transactions errors are trivial compared to the round-off point, and, at least where I live, they charge sales taxes - which themselves are number that essentially always result in fractional parts of currency and rounding. Frequently very close to that .005 of a unit.

With a transaction load as vibrant as amazons, the overhead in the precise representation vs. FP "native" types can add up. For a Point-of-Sale system I created quite some years ago, customers included Australian business: they currency is calculated in .01 $Aus units but the smallest coin was .05 $Aus. I needed to handle this, including possible refunds and unwinding/recalculating final bills after changes. Do you really think the difference is the 10th decimal place for a dry-cleaning bill will be significant by comparison? Especially, for the full-wide scope, with various regional taxes, environmental fees, &etc. multiplying these as well? Indeed, every step accumulate a tiny bit of error - and when the calculations were done and it went to the accountants, it was already "severely" modified to the nearest .01 (or, .05 ). That was what was paid/billed.

Solving a problem . . . requires a judicious balance between (alleged) best practices and the realities imposed by resources. If you're not working in an industry that requires specific data model by statute then you should read "Best Practices" as a recommendation and not the only way to do something. 'Best' is not at all the same as 'only'. That's where the thinking comes in.

* On such markets, even currencies that do not have decimal sub-units are, nonetheless, traded out to several decimal places. At this particular moment, the US$ is worth 103.9490 Japanese Yen. How much would a retail transaction in an online store have to buy for you to worry about the round-off errors if FP vs one of the other options? And what if stored as the integer (1039490), then final result divided by 10000 when done?

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

Member 13301679 wrote:
Quote:
2 - Store as string values -

That's also not how it's done. Currencies have to be stored as a fixed-numeric type.

Not strictly true. When I worked on SAP, it stored currency values as fixed length numeric strings. Similar to fixed-numeric type, but using characters rather than a binary type to avoid any issue with the underlying nature of binary storage (e.g. big-endian vs. little-endian).

Where are the packed decimal instructions when you need them.

W∴ Balboos, GHB wrote:
How does that explain the hand sanitizer? Face Masks? Sandpaper?

Those are items needed everywhere you go and anywhere you are so it makes sense that anything you are searching for would also need those related items.

Makes perfect sense . . . if the toilet paper is gone you can use the sandpaper.

Brilliant deduction !

Ravings en masse^
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein
"If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010

W∴ Balboos, GHB wrote:
if the toilet paper is gone you can use the sandpaper.

... and then spray the hand sanitizer on the affected area? Unsure | :~

(ouch)

Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.

I don't disagree, but when I want to buy a "4S 850 mAh 95 C" LiPo battery for one of my racing/freestyle drones, it gets really annoying that the only batteries with the specs I actually specified are on, like, page 9, 15, and 22 of the search results. I miss Alta Vista.

Eagles my fly, but weasels don't get sucked into jet engines.

I don't disagree, but when I want to buy a "4S 850 mAh 95 C" LiPo battery for one of my racing/freestyle drones, it gets really annoying that the only batteries with the specs I actually specified are on, like, page 9, 15, and 22 of the search results. I miss Alta Vista.

Eagles my fly, but weasels don't get sucked into jet engines.

eBay has this great "feature" that if you search for "stuff I want" -"ugly rotten crap" you will get emails for "the ugly rotten crap you're looking for".

The minus works on search results, but then they recommend what you excluded.

Welcome to the Lounge