I've put together a short article explaining how computers store decimal numbers, starting with IEEE-754 doubles and moving into the decimal types used in financial systems.
There’s also a section on Avro decimals and how precision/scale work in distributed data pipelines.
It’s meant to be an approachable overview of the trade-offs: accuracy, performance, schema design, etc.
Hope it's useful:
https://open.substack.com/pub/sergiorodriguezfreire/p/how-computers-store-decimal-numbers
This is a floating point number, it just uses a base-10 exponent instead of a base 2 exponent, and is missing some of the optimisations that make IEEE754 numbers more compact. Not all of IEEE754's problems for financial applications come from using base-2, they mostly come from being floating point numbers — I'm talking about things like error accumulation.
Banking is almost always built on fixed point numbers, because it doesn't matter whether you're dealing with tens or billions of dollars, your target precision for financial transactions will always be to the cent (or, probably, tenth or hundredth of a cent for intermediate calculations). Or you might just use int types directly, as number of cents (or whatever fraction is appropriate).
When I worked in ad tech we always used int64 storing microdollars (1/10000 of a cent). More than enough precision even when doing math and currency conversions on individual ad impressions, you can represent up to about $9T, and it's super efficient.
Google's standard now for a money value is int64 units plus int32 nanos, so effectively 94 bits representing up to $263 - a truly ludicrous range unless you're representing the economy of a galaxy of Zimbabwes.
Why microdollars and not just cents? What use case made you need fractions of cents?
Probably because in ad business, you don't always pay out whole cent values. For example, if you place an ad on your website and get paid out by the ad management company, each impression or click would give you a certain fraction of a dollar that can be less than a cent. Once you have accumulated enough fractional dollars to cross a threshold of say $100, you get paid out.
This is true to the point that, in adtech, you usually talk about pricing in terms of cpm — cost per mille.
Three cases, all having to do with multiplying money by fractional values:
You could theoretically use floating-point for some of those, or convert to and from different units when you need more precision for intermediate results, but int64-micros was good enough for pretty much every scenario, and staying in the same unit at all times avoids tons of mistakes.
A few exchange rates right now:
So, for India and Japan you need precision down to the hundredth of a penny. For Argentina and South Korea, make that a thousandth. For Iran, it goes to one ten-thousandth of a penny. Microdollar doesn't seem excessive at all.
Because some there is always some wiseass who secretly transfers those hidden fractions to their personal account.
Or you mean Zimbabwe by next year? /s
Woah, this is such a wonky number representation. Basically a Q notation but with two competing sign bits? I can't understand why they chose to have it this way.
What are the alternatives though? Remember it's not just USD, it's a general currency types, so the value can be quite high.
Floating point or other 64-bit quantities don't have enough precision.
direct 128-bit numbers are not supported by protobuf (it's 64 bit only)
A 128-bit number represented as pair of 64-bit numbers (two halves of 128 bits) need finicky reconstructions, and is very hard to use in languages like Javascript.
The 64 + 32 approach is nice and simple to implement. For the nanos, it's less than half the range - which means you can trivially add two values together and not worry about overflow. The "two competing sign bits" is trivially solved by the spec - read the link, it actually says they have to match if whole part is nonzero.
You'd use a boolean for the sign bit and two unsigned integers for the number and fraction.
This would represent exactly what a Q number is without any wonky edge cases from having two signed integers. Most decimal formats are some form of a Q number https://en.wikipedia.org/wiki/Q_(number_format)
Just thinking about what Google has done here, I just keep seeing it as a result of technical debt ten ways 'til Sunday. Languages that don't have unsigned ints and sheer laziness of trying to use the already existing protobuf encodings instead of making a new one, or any number of reasons someone might have said, "eh, fuck it..."
Isn't that kind of weird? They have multiple variable-length integer encodings optimized for different kinds of signed and unsigned numbers, so even if you're talking about JavaScript you're already doing quite a bit of binary arithmetic to decode a protobuffer message into the native representation (in JavaScript it'll be a float). Just so we're clear - the int64 being used here is a variable length encoding, not what you think of as a basic int64 number.
Pretty sure this will make for more complex code. Right now, adding (or subtracting) two numbers is:
Extra boolean will make this logic harder, and for what? Just so you can say "it's Q-inspired" (it won't ever be proper Q because it's base 10, not base 2)?
As for "edge cases" - either way there is 109 valid values for a variable which is 32 bits long. This means 76.7% of "nanos" values would be invalid - and this does not change whether the sign bit is separate from units or embedded into them.
I believe nothing is a base 10 format here? Not Q numbers, not Google's protobufs, not my alternative (still protobuf)? I may have missed something and if I have, I'd love to be corrected.
The extra boolean doesn't create any more sign logic that you didn't already need anyway. You're going to be comparing signs and magnitudes all day long, for example if you want to add a positive and negative number together, or when you need to be able to sort the results of your maths (you have to normalize and canonicalize everything). All split-field fixed point decimal formats do this.
If you're working in a language that supports 64 bit ints, the best thing to do is to use this protobuf to create an actual Q number. If you're working in a language that supports split field decimals (.NET Decimal, Java BigDecimal, etc), then use those. In either case, having multiple ways for the protobuf to represent a negative number is just a potential source of errors. So in JavaScript, you might use decimal.js. To do the conversion, you'll make a decimal for the numbers, a decimal for the nanos, divide the nanos decimal by a billion, and add the two decimals. So if you had a sign bit in the protobuf, it costs you nothing extra to negate the decimal you're converting into if you have to.
Yes, Google's format is base 10, see int64 units plus int32 nanos link posted by /u/General_Mayhem few posts back. Line 35 says "Number of nano (10-9) units of the amount." - that's decimal base.
And no, the whole advantage of Google's format is you don't need to worry about signs in many cases.
Add a positive and negative number? The way Google specified it, "units+=units2; nanos+=nanos2; normalize()" still works. No need for extra logic re signs (it's all hidden in "normalize").
Compare numbers? "(units1 < units2) || ((units1 == units2) && (nanos1 < nanos2))" still works, no matter what signs of numbers are.
"If you're working in a language that supports 64 bit ints, [...] If you're working in a language that supports split field decimals [...]" - dude, have you even read what you have been replying to? This is repo titled "Public interface definitions of Google APIs", which means you cannot assume anything. No, you cannot assume 64-bit ints - Google SDKs need to support javascript. No, you cannot assume split field decimals either.
You are talking about some optimal efficiency under some ideal conditions. Google's goal was "anyone can implement this in any random language", and their solution is the best for this.
First off, I apologize because I was adding more to my comment as you were already replying. So you did not see when I wrote that JavaScript has multiple libraries implementing split field decimals, including decimal.js.
Google's link is telling us that it's a domain-restricted fraction. The numbers go up to +/- 1 billion -1. But they are encoded in a protobuf int32, which is not a base 10 integer format. See for example BCD - https://en.wikipedia.org/wiki/Binary-coded_decimal
By limiting the 32 bit integer to a much smaller domain than would normally fit inside it it, Google can at least guarantee that all the decimal numbers within the domain can be represented precisely. I'm not sure if that's common knowledge, but maybe that's what you were picking up on.
Google's money protobuf is literally a split field decimal format. You're either going to be using one that is already there or implementing your own handling from scratch.
You can absolutely choose to implement your client in whatever format that platform supports. It's just one's and zero's, so you can absolutely convert them into other number formats as long as you are not losing information in the process.
I don't know if you missed the part where I pointed out that the protobuffer int64 and int32 are not your run of the mill integers. They are variable-length encodings on the wire and they require non-trivial bitwise operations no matter what language you're trying to use them with. You're not just sticking them into a float 64 Number in JavaScript, or into a 32 bit integer in Java.
Boolean sign means you have -0.
I'd represent it as
int64+uint32, though for arithmetic you'd need to potentially two's complement theuint32...I think you'd run into issues trying to represent negative numbers between 0 and -1.
Why? Just treat it as a 96-bit signed integer.
0.5 would be:
0...00 : 7FFF'FFFF-0.5 would be:
F...FE : 8000'0001, I believe.This is also going to be easier to actually perform arithmetic on, with intrinsics like
add/adc.Would be nice if you could but I believe you’d have trouble across various compilers as well as protobuf codec. You’d have to be able to represent and preserve a negative zero for the signed integer. It’s not as simple as just doing a couple bitwise operations to get these values into a 96 bit integer. Also, 96 bits would probably be too few for number theory reasons, as an aside. But supposing you could reliably do this on every platform then you’d have a standard Q number and life would be good. The biggest hurdle as far as I’m concerned is just protobuf, it’s just not very good at all for representing custom binary formats.
I have absolutely zero experience with protobufs (they aren't particularly relevant in my field), so I can't comment on that, but MSVC, Clang, and GCC all support the relevant intrinsics.
I could write a version that would work on all the major compilers relatively quickly.
I don't know why you'd need to be able to represent -0? No two's complement system can.
96 bits was what was originally proposed. If you'd rather do 128, then GCC and Clang already support it - MSVC would need it written though it'd be pretty trivial.
Are you trying to treat this as a binary value or as a binary-coded decimal value? That certainly changes things.
Integer for minor units is the most right choice IMO
I liked your comment but in international trades you won’t be ablento live woth your self if you are going that route.
If you tackle bitcoins or some currencies that are 1:10.000 of a dollar or more than you your precision must grow with that.
I work related to banking and we are not allowed to cut of any part of remainder.
Yeah you absolutely never use floats for anything that requires absolute precision. Banking/Finance being a major one
Floats can only be fractions upon fractions of pennies (cents) off, however when you multiply that into the millions of daily transactions and computations you can have money both being lost and generated into thin air
BigDecimal being the implementation in the Java world
Even BigDecimal has rounding. Because it is base 10, it cannot represent most fractions exactly. So if you divide by 3 for example, you must round at some precision. BigDecimal gives you control over that rounding, but it doesn't let you not round.
Some things can never be truly represented in decimal however I can promise you if it's a financial institution using Java, they're using BigDecimal or Long
The Haskell safe-money packages goes to great lengths to use appropriate representations for appropriate purposes - amounts of money are stored in their base units (cents etc.) but calculations are performed using rational numbers, the results are lossless until converted back to the value format. This also means that doing things like multiplying by a percentage always works.
https://web.archive.org/web/20211014094900/https://ren.zone/articles/safe-money
I used to work on a stock market clearing and settlement system (written in Haskell), and every calculation that could result in fractional results also had specified rounding. It’s pretty common in stock markets to split stocks and provide bonus stocks (everyone who has owned X amount for some period of time earns 2% more stocks), and those calculations were always tricky to get right, and handle the residual values correctly. Even with all that, it wasn’t uncommon that final settlement was off by a cent or two at the end of the day, because those fractions do go missing, so the difference was added to or taken from a special account that exist for no other reason than to make settlement balance. Yes it sounds like Office Space, but no one owned the account and it always had a value of less than one dollar.
Don't tell OP about fixed point and BCD.
The article confused me. What OP refers to as 'decimal' numbers are fixed point. Unless you're also encoding the exponent, in which case it's 'float' again, but perhaps not an IEEE flavour.
And it's odd that 'performance' is described as a problem, compared to 'doubles' - fixed point integer math (at least arithmetic) is usually much much FASTER than floats, especially for CPUs without hardware float math support.
100%. Honestly floating point should be used only for honest to goodness mathematics (modeling, engineering, physical simulation, etc). In fact its fucking insane that anything related to money is even allowed to be floating point.
Or IBM mainframes with their packed decimal math instructions.
What is BCD?
Binary-coded decimal. E.g. because you can fit all digits 0-9 in four bits, you can use a byte to encode two decimal places.
Think of natural base 10 numbers, and each of the digits in the number is represented by its equivalent binary. For example 10 in binary will be 1010. But in bcd it will be 0001(for the 1 in tens place) and 0000. So bcd for 10 is 00010000.
Don't forget BNR!
The number is encoded in twos compliment fractional notation, where the numerator is your value and the denominator is the resolution. This would be 20 = 1 for normal twos compliment, but may be some agreed-upon value smaller (or larger) than one, depending on your data requirements.
This is used alot in ARINC-429 data (along with BCD). It's fun working with avionics!
If you want to play around with fixed point types and explore how they are specified, and the rounding issues, you can look at these pages in Ada...
https://learn.adacore.com/courses/intro-to-ada/chapters/fixed_point_types.html
For financial transactions plain integers will usually be sufficient. If you're dealing with something like euros, then it's probably sufficient to simply count in cents as this is the smallest denomination (1 euro = 100 cents).
You will probably run into floating point values when dealing with things like interest rates and sales taxes. In these case there are typically country specific laws, that regulate how to do the rounding to the "nearest" integer value. Also note that rounding may sometimes need to round to the nearest coin denomination - in the case of Sweden this would be to the nearest krona, as there are no longer any öre coins (1 krona = 100 öre).
No. Never ever use floating point in finance. Use decimals. These are a bit slower and consume a lot more memory but 0.1 is always 0.1 and not some 0.100000000000000001234 madness. Floats are good for games and few other places, but real world money is not one of them.
This is not me, but...
https://news.ycombinator.com/item?id=15808316
The author there goes on to concede if you’re not working with experts, aka, not in high frequency trading, then sure, there are factors to consider in solution selection.
Yeah, but I wanted to show that floating points (especially decimal floating points) are considered viable for financial applications.
Don't some languages/databases have a money type too?
Depends on what your dealing with. If your e.g. adding VAT to an item and using floats as part of the calculation, then this is not really an issue, as the final sales value will be rounded to an legally appropriate integer (before usage).
Storing amounts as floats is a separate and much more questionable practice.
Also if you're just using some kind fixed size (base 10) representation, with a fixed precision after the decimal point, then you're just doing base 10 floating point math and will still run into issues with representing certain values like 1/3 ( = 0.333333…).
0.100000000000000001234 is not a problem if you can round. The problem is losing precission and lost real value.
I'd check this first
This incorrect assumption, kind of breaks any benefit of using decimal number math.
To be fair, many financial systems only need to deal with one or a few currencies, so obscure edge cases like non-decimal money units, are unlikely to crop up unless you have to deal with such a currency.
There are two currencies that are non decimal and still in use today. In both cases the main unit is split by 5. Although not perfect, the decimal type should still work for these (as no rounding errors should be observed).
But yeah, technically the truth: decimals are not a universal answer. If anyone reinvents 1/8 or 1/20 or 1/12 based currencies, we're screwed.
No matter what representation you choose to use, you're going to have to do rounding. Because bank accounts don't store millionths of a cent (or any other currency). The important thing to to be cognizant of when and how you are rounding (there are often legal rules that you must follow). You can do that with floating point arithmetic and get the correct answer and it's not even harder than using other representations. So the representation really isn't as important as doing the math correctly.
—