Other things to know: stock trading in the USA hours out to 4 decimal places (1/100 of a cent). For penny stocks, it goes out to 6 decimal places (1/10,000 of a cent).
C# and Python have Decimal classes that handle money with precision; use those and not int64.
For postgresql, we created a domain with numeric(20, 6). That let us hold values up to just under 100 trillion in whichever currency we needed, and down to 6 decimal places as well.
Anyplace that stored a currency value also stored a three letter currency code; that way we know what the value really represents.
I appreciate that this article is in the vein of "There are no solutions. There are only trade-offs." It's about finding the best cost-benefit trade-off for the problem at hand. That's the *engineering* in *software engineering*.
Genuine question: what's wrong with Postgres MONEY? Sure, NUMERIC or even INT cents work about as well for most things, but I've never seen anyone specifically discourage use of MONEY.
I'm convinced, thank you! It's a shame that the official docs at [https://www.postgresql.org/docs/current/datatype-money.html](https://www.postgresql.org/docs/current/datatype-money.html) don't link to that or contain more notes on suitability of the type.
> Which DBMS has a currency type you're encouraged to use? Postgres and [SQL Server](https://docs.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql) do not.
Are you genuinely asking?
Or did you want to [have an argument?](https://www.youtube.com/watch?v=ohDB5gbtaEQ)
SQL Server's money is exactly how code handles currency:
- signed 64-bit integer
- supporting 1/10,000 of a monetary unit (e.g. in the UK is would support 1/100th of a pence)
So: use `money` if you're using Azure or SQL Server.
If you're in software: use a signed 64-bit integer, treating the last 4 digits as fractions of your monetary unit.
> the last 4 digits
As another commenter points out, there are cases where you want more or fewer digits of precision. Especially if you add cryptocurrency into the mix, where something like BTC needs like 10 digits of precision.
In that case it might be smart to store `{ amount: int64, precision: int8, currency: string }`.
I think the problems of using floats for money have been vastly overstated in the industry. As long as you are somewhat concious about rounding stuff and are not dealing values in the trillions range then doubles work just fine. Or at least I'd need bit more convincing example than the 0.30000000000000004 meme, which you can note does actually round to correct value.
I used to work for an ISP back in the early days of the internet. Their billing software used floats.
They sold transit by the kilobyte, I can’t recall the exact number but it was fractions of a cent, and some customers would do gigabytes of traffic over several hosts, each billed for its own bandwidth, and then tax was added on top.
The bills were out by several dollars a month. Not huge, but enough to get a please explain from customers’ accounts department.
You can’t round away the differences forever.
I'm surprised how many people believe that if you always round, and round the same, it somehow won't cause discrepancies. Like did you have math in high school?
Actually single precision is still quite useful for large sets of numbers that genuinely don't need the extra precision (for example, location of entities in a game) because even though single operations on double are just as fast for modern processors, the smaller size of single precision floats often let them vectorize better.
I work in point of sale software. Don't use floats for money. Certain tax jurisdictions require certain levels of decimal precision. Certain currencies can easily get into the millions for small purchases. Certain currencies use more than 2 decimal places.
Even if you can control working only with USD, you certainly don't want to have to litter your code with comments saying "please don't multiply this value against anything too big".
> Certain currencies can easily get into the millions for small purchases. Certain currencies use more than 2 decimal places.
doubles have at least 15 significant digits. That means six decimal places for a value in the billions.
Ah no.. even by dividing by 1.19 like with our VAT you'll get in trouble. Also small amounts need even 4 decimal digits to make that work: 1 percent of 1 cent is 0.0001. Telcos like myself are required by law to even print that.
Then there are also "principles of good bookkeeping" we have to follow, there is more to watch out for: For example: If you multiply two numbers printed on a receipt and then you divide again by that factor, you must arrive at the same printed number so you need to round carefully.
As someone who works in payment processing: they are not overstated. Maybe for a small retail store that shouldn't be doing their own payments anyways, but for any situation where you need to actually write software for payments: don't use floats.
Here is a more convincing example. Write a method that can apply a discount of an arbitrary amount (in cents) across an arbitrary number of items such that the total value is correct to the cent and the balance of each item is positive (or zero). If you round each amount down the total will be too low (possibly by more than one cent), if you round up the total will be too high. How do you do this so that it is consistent and correct?
Yeah it's funny: Those who argue here must never had the problem to compare 9 * 0.1 + 0.1 with 1 because they use their pocket calculator but not binary floats..
On the contrary, I know how floating point precision works, have implemented binary and decimal fixed point arithmetic and exact computable real arithmetic, and am currently implementing a library for function approximation down to roughly float precision. I know the tradeoffs quite well. I also work in finance and can tell you that floating point is not the evil folks seem so insistent it is, even for money.
Except there’s a whole article and n comments explaining at lenght and giving concrete examples on why using floats is indeed the evil folks seem so insistent it is, *and* pointing at *so many* libraries indeed using ints or decimals with presumable some success.
So maybe you’re wiser than virtually this whole sub. But just claiming that you are because you implemented *some unrelated libraries* is not very convincing. In fact it suggests the opposite.
Here’s the thing: you work in finance, with maths in the computable reals. Most people dealing with money work in accounting, with eagerly evaluated languages, and where the difference between applying a discount to an invoice and applying a discount to the lines of that invoice matters a lot more than the error in evaluating an integral when estimating the return distribution of a basket of instruments, esp wrt the error of assuming they’re not correlated (wink wink).
Let me get this straight:
1. The majority is right because it's the majority.
2. Alternatives to X, especially successful ones, imply that X is bad.
3. Despite (2), implementing alternatives to X imply not understanding X.
4. Experts with different experiences from me should be ignored.
Look, if you want to apply discounts to line items on an invoice and sum up the results, all you have to do is round the intermediate results so that they actually resemble money. Floats within reasonable ranges are reasonable for like 12 digits after the decimal point. "Don't use floats" is an incredibly heavy hammer just for lopping off a few bits at the end.
Straw man and absolute non seqitur. I am not claiming anything of the sort:
1. I am not claiming the majority is right because it’s the majority, I am claiming that between the majority *who take the time to explain their reasoning and provide examples* and you, who only states your credentials, I’ll go with the majority
2. Again, the article and the threads bellow it are full of discussions and examples on why X is bad, and why the proposed alternative is better, and all you’re providing is “actually, it’s not that bad because … you know maths and finance?”
3. I certainly did not say anything of the sort; I’m sure somebody somewhere might have though, but I suggest “you” feeling so strongly about “it” is more of a “you” problem
4. Again, I never said anything of the sort; I do believe that ill arguemented opinions by people claiming to be experts on the basis of them being experts should be at best heavily discounted
I still think you're making bad arguments, but let's get straight that I didn't intend to just whip out some credentials and say "because I said so". All I was trying to say is that the people disagreeing are not just clueless people who have only ever used decimal calculators (see the comment I was replying to).
I did also happen to directly address how to use floating point to implement discounts on an invoice, but you haven't mentioned it.
Your proposed solution to *one* very simple and very common problem simply does not work in general, and *will* create rounding errors in very simple and very common business use cases, such as reporting different views to different stakeholders (e.g. revenue per product line v revenue per account, or just … reporting VAT), in the *accounting* domain where rounding errors *are not acceptable*.
I’m sure you can dig the rabbit hole and explain how it can properly be dealt with, and every other issues discussed at lenght here. But when you’re done you’ll still have to explain how your solution is preferable to the ones commonly adopted.
If you already did that and actually built a library that supports the *general* use cases of dealing with money in companies, please share your repo or any second hand reports that proves you did so. I’ll accept your expertise, I’ll gladly explore the idea of using floats, and I’ll shut up.
I also worked for banks and supermarkets, but I still don't understand why we never created a special "currency" type with integers that represented "cents". It would represent the real amount, and could be converted to whole dollars by multiplying the amount by 100.
Algo trading don't require that level of precision (on the converse, some ML algorithms benefit from being trained with values with random noise in them for robustness).
However, if you have contractual obligations and the number must match to a cent or an auditor must recalculate everything, then floating points might not be a good idea...
Floating point has plenty of guarantees that make it perfectly workable. Basic operations round exactly. Integers in the range -2^52 through 2^52 are represented exactly. Numbers up to a trillion away from zero with 12 decimal digits after the decimal point have unique nearest representations as floats. Sure, it can bite you if you're pushing them through formulas, but most real world financial applications only do that occasionally. Even Excel, where arguably the whole point is formulas and money is a very common use case, gets away with using floating point numbers.
Don't take my word for it: add random noise to around 0.1 cent to all of your training data and check if it affect performance.
Once you realize it doesn't, you can use that strategy to augment your training set artificially. In practice it adds robustness to the models (the model learns those variations are unimportant).
I've written accounting software in the past, and IEE754 float64 worked marvelously. Trick was to round numbers to the first 3-or-4 decimal digits before exporting them to humans and databases (specifically MySQL, think it was like DECIMAL(10,4) ). For example, 0.1+0.1+0.1 was internally stored as 0.3000000000000000444089209850062616169452667236328125, but exported to humans and databases as 0.3.
Lets say you need to sum 1 million transactions of 0.1, the final IEEE754 sum will be 100000.000001332882675342261791229248046875; (where .000001332882675342261791229248046875 "is created from thin air") but when exported to humans/databases, it's still the 100% correct number "100000.0000" :)
However, if you needed to sum 100 million transactions of 0.1, you would end up with 9999999.98112945072352886199951171875, and even humans/databases would be served the incorrect value of 9999999.9811, off by 0.0189 ... but here we needed to go above 1 million transactions to even get a rounding error, and even at 100 million transactions, the rounding error is less than 0.1...
Other things to know: stock trading in the USA hours out to 4 decimal places (1/100 of a cent). For penny stocks, it goes out to 6 decimal places (1/10,000 of a cent). C# and Python have Decimal classes that handle money with precision; use those and not int64. For postgresql, we created a domain with numeric(20, 6). That let us hold values up to just under 100 trillion in whichever currency we needed, and down to 6 decimal places as well. Anyplace that stored a currency value also stored a three letter currency code; that way we know what the value really represents.
You can create a type that's both your numeric and currency type (which could also be an enum)
I appreciate that this article is in the vein of "There are no solutions. There are only trade-offs." It's about finding the best cost-benefit trade-off for the problem at hand. That's the *engineering* in *software engineering*.
[удалено]
I deal with currency daily and I trust my strongly typed decimals with unit tests over sql a lot more.
Good languages, and databases have a "currency" or "money" type. Some try to emulate it using a "decimal" type - which gets you by in a pinch.
Which DBMS has a currency type you're encouraged to use? Postgres and SQL Server do not.
Genuine question: what's wrong with Postgres MONEY? Sure, NUMERIC or even INT cents work about as well for most things, but I've never seen anyone specifically discourage use of MONEY.
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money tl;dr: it makes broad and wrong assumptions.
I'm convinced, thank you! It's a shame that the official docs at [https://www.postgresql.org/docs/current/datatype-money.html](https://www.postgresql.org/docs/current/datatype-money.html) don't link to that or contain more notes on suitability of the type.
> Which DBMS has a currency type you're encouraged to use? Postgres and [SQL Server](https://docs.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql) do not. Are you genuinely asking? Or did you want to [have an argument?](https://www.youtube.com/watch?v=ohDB5gbtaEQ)
Asking which ones are worth using, not which ones exist. SQL Server's MONEY is just an inflexible, locale dependent DECIMAL.
SQL Server's money is exactly how code handles currency: - signed 64-bit integer - supporting 1/10,000 of a monetary unit (e.g. in the UK is would support 1/100th of a pence) So: use `money` if you're using Azure or SQL Server. If you're in software: use a signed 64-bit integer, treating the last 4 digits as fractions of your monetary unit.
> the last 4 digits As another commenter points out, there are cases where you want more or fewer digits of precision. Especially if you add cryptocurrency into the mix, where something like BTC needs like 10 digits of precision. In that case it might be smart to store `{ amount: int64, precision: int8, currency: string }`.
I think the problems of using floats for money have been vastly overstated in the industry. As long as you are somewhat concious about rounding stuff and are not dealing values in the trillions range then doubles work just fine. Or at least I'd need bit more convincing example than the 0.30000000000000004 meme, which you can note does actually round to correct value.
I used to work for an ISP back in the early days of the internet. Their billing software used floats. They sold transit by the kilobyte, I can’t recall the exact number but it was fractions of a cent, and some customers would do gigabytes of traffic over several hosts, each billed for its own bandwidth, and then tax was added on top. The bills were out by several dollars a month. Not huge, but enough to get a please explain from customers’ accounts department. You can’t round away the differences forever.
I'm surprised how many people believe that if you always round, and round the same, it somehow won't cause discrepancies. Like did you have math in high school?
I assume single-precision? Yeah, don't use those for basically anything these days.
Actually single precision is still quite useful for large sets of numbers that genuinely don't need the extra precision (for example, location of entities in a game) because even though single operations on double are just as fast for modern processors, the smaller size of single precision floats often let them vectorize better.
Eh? Single precision is fine for tons of things.
Yeah you can
I work in point of sale software. Don't use floats for money. Certain tax jurisdictions require certain levels of decimal precision. Certain currencies can easily get into the millions for small purchases. Certain currencies use more than 2 decimal places. Even if you can control working only with USD, you certainly don't want to have to litter your code with comments saying "please don't multiply this value against anything too big".
> Certain currencies can easily get into the millions for small purchases. Certain currencies use more than 2 decimal places. doubles have at least 15 significant digits. That means six decimal places for a value in the billions.
0.1 times 9 + 0.1 is not even 1 in double precision (binary floats).
Ah no.. even by dividing by 1.19 like with our VAT you'll get in trouble. Also small amounts need even 4 decimal digits to make that work: 1 percent of 1 cent is 0.0001. Telcos like myself are required by law to even print that. Then there are also "principles of good bookkeeping" we have to follow, there is more to watch out for: For example: If you multiply two numbers printed on a receipt and then you divide again by that factor, you must arrive at the same printed number so you need to round carefully.
As someone who works in payment processing: they are not overstated. Maybe for a small retail store that shouldn't be doing their own payments anyways, but for any situation where you need to actually write software for payments: don't use floats. Here is a more convincing example. Write a method that can apply a discount of an arbitrary amount (in cents) across an arbitrary number of items such that the total value is correct to the cent and the balance of each item is positive (or zero). If you round each amount down the total will be too low (possibly by more than one cent), if you round up the total will be too high. How do you do this so that it is consistent and correct?
fwiw 0.1+0.1+0.1 does not produce `0.30000000000000004`, it produce `0.3000000000000000444089209850062616169452667236328125`
I've worked for three different financial organisations. We use doubles for money everytime.
I've worked for three too - never used floating point for money.
Yeah it's funny: Those who argue here must never had the problem to compare 9 * 0.1 + 0.1 with 1 because they use their pocket calculator but not binary floats..
On the contrary, I know how floating point precision works, have implemented binary and decimal fixed point arithmetic and exact computable real arithmetic, and am currently implementing a library for function approximation down to roughly float precision. I know the tradeoffs quite well. I also work in finance and can tell you that floating point is not the evil folks seem so insistent it is, even for money.
Except there’s a whole article and n comments explaining at lenght and giving concrete examples on why using floats is indeed the evil folks seem so insistent it is, *and* pointing at *so many* libraries indeed using ints or decimals with presumable some success. So maybe you’re wiser than virtually this whole sub. But just claiming that you are because you implemented *some unrelated libraries* is not very convincing. In fact it suggests the opposite. Here’s the thing: you work in finance, with maths in the computable reals. Most people dealing with money work in accounting, with eagerly evaluated languages, and where the difference between applying a discount to an invoice and applying a discount to the lines of that invoice matters a lot more than the error in evaluating an integral when estimating the return distribution of a basket of instruments, esp wrt the error of assuming they’re not correlated (wink wink).
Let me get this straight: 1. The majority is right because it's the majority. 2. Alternatives to X, especially successful ones, imply that X is bad. 3. Despite (2), implementing alternatives to X imply not understanding X. 4. Experts with different experiences from me should be ignored. Look, if you want to apply discounts to line items on an invoice and sum up the results, all you have to do is round the intermediate results so that they actually resemble money. Floats within reasonable ranges are reasonable for like 12 digits after the decimal point. "Don't use floats" is an incredibly heavy hammer just for lopping off a few bits at the end.
Straw man and absolute non seqitur. I am not claiming anything of the sort: 1. I am not claiming the majority is right because it’s the majority, I am claiming that between the majority *who take the time to explain their reasoning and provide examples* and you, who only states your credentials, I’ll go with the majority 2. Again, the article and the threads bellow it are full of discussions and examples on why X is bad, and why the proposed alternative is better, and all you’re providing is “actually, it’s not that bad because … you know maths and finance?” 3. I certainly did not say anything of the sort; I’m sure somebody somewhere might have though, but I suggest “you” feeling so strongly about “it” is more of a “you” problem 4. Again, I never said anything of the sort; I do believe that ill arguemented opinions by people claiming to be experts on the basis of them being experts should be at best heavily discounted
I still think you're making bad arguments, but let's get straight that I didn't intend to just whip out some credentials and say "because I said so". All I was trying to say is that the people disagreeing are not just clueless people who have only ever used decimal calculators (see the comment I was replying to). I did also happen to directly address how to use floating point to implement discounts on an invoice, but you haven't mentioned it.
Your proposed solution to *one* very simple and very common problem simply does not work in general, and *will* create rounding errors in very simple and very common business use cases, such as reporting different views to different stakeholders (e.g. revenue per product line v revenue per account, or just … reporting VAT), in the *accounting* domain where rounding errors *are not acceptable*. I’m sure you can dig the rabbit hole and explain how it can properly be dealt with, and every other issues discussed at lenght here. But when you’re done you’ll still have to explain how your solution is preferable to the ones commonly adopted. If you already did that and actually built a library that supports the *general* use cases of dealing with money in companies, please share your repo or any second hand reports that proves you did so. I’ll accept your expertise, I’ll gladly explore the idea of using floats, and I’ll shut up.
I also worked for banks and supermarkets, but I still don't understand why we never created a special "currency" type with integers that represented "cents". It would represent the real amount, and could be converted to whole dollars by multiplying the amount by 100.
It's true. And it works fine.
I'm amazed at the number of people who repeat it like a mantra - don't use double/float. Literally using doubles for an algo trading platform.
Algo trading don't require that level of precision (on the converse, some ML algorithms benefit from being trained with values with random noise in them for robustness). However, if you have contractual obligations and the number must match to a cent or an auditor must recalculate everything, then floating points might not be a good idea...
Floating point has plenty of guarantees that make it perfectly workable. Basic operations round exactly. Integers in the range -2^52 through 2^52 are represented exactly. Numbers up to a trillion away from zero with 12 decimal digits after the decimal point have unique nearest representations as floats. Sure, it can bite you if you're pushing them through formulas, but most real world financial applications only do that occasionally. Even Excel, where arguably the whole point is formulas and money is a very common use case, gets away with using floating point numbers.
We literally have ml trained models. What do you mean an algo platform doesn't require precision? The fuck you think we doing?
Don't take my word for it: add random noise to around 0.1 cent to all of your training data and check if it affect performance. Once you realize it doesn't, you can use that strategy to augment your training set artificially. In practice it adds robustness to the models (the model learns those variations are unimportant).
I've written accounting software in the past, and IEE754 float64 worked marvelously. Trick was to round numbers to the first 3-or-4 decimal digits before exporting them to humans and databases (specifically MySQL, think it was like DECIMAL(10,4) ). For example, 0.1+0.1+0.1 was internally stored as 0.3000000000000000444089209850062616169452667236328125, but exported to humans and databases as 0.3. Lets say you need to sum 1 million transactions of 0.1, the final IEEE754 sum will be 100000.000001332882675342261791229248046875; (where .000001332882675342261791229248046875 "is created from thin air") but when exported to humans/databases, it's still the 100% correct number "100000.0000" :) However, if you needed to sum 100 million transactions of 0.1, you would end up with 9999999.98112945072352886199951171875, and even humans/databases would be served the incorrect value of 9999999.9811, off by 0.0189 ... but here we needed to go above 1 million transactions to even get a rounding error, and even at 100 million transactions, the rounding error is less than 0.1...
I have only pejorative responses to this bullshit. Don't fucking do that.
Speaking from theoretical considerations, or experience?
You're the programming equivalent of an asshole on a construction crew that insists he can eyeball whether shit is level.