4 ms·

> A common wisdom in database design is “never using floating-point numbers for money” Common, and in my experience totally wrong. It's the most pervasive carg

> A common wisdom in database design is “never using floating-point numbers for money” Common, and in my experience totally wrong. It's the most pervasive cargo culting I've experienced amongst developers, where people with 0 experience with financial applications will recoil if you argue against it. In my time developing applications for front office at an investment bank, floating point often works best. Of course in your case though, for a billing system, the method you describe is obviously the right one.

devwastaken 6y ago
How did you handle the properties of floating point? The reason people recoil at that more than likely has to do with lack of knowledge in how floats are wrangled to ensure they're accurate.
- stevesimmons 6y ago
  Which properties? For front-office risk and pricing calcs, speed of calculation matters most. Hence IEEE754 float64. Fixed precision decimals really only matter for middle and back-office, for trade confirmation and settlement.
  - tantalor 6y ago
    What's the difference between front/middle/back office?
    - arthurcolle 6y ago
      In the context of a bulge bracket investment bank, it's basically like this: Front office - S&T (sales & trading), i.e. revenue generating activity. Usually includes any quants/quant developers actively working on things that make money Middle office - operations. handles settlements, confirms, and generally anything related to the post-trade flow that is "after the trade is booked" Back office - accounting, legal, engineering, IT support, HR. Anything that isn't a revenue center that also isn't even tangentially facing revenue generating operations
- bidirectional 6y ago
  Which properties do you mean in particular? The main point is just that finance doesn't necessarily require perfect accuracy. It does when Bob sends Alice $1 and their account balances must line up perfectly, it doesn't when you're implementing models which already have greater error than floating point could imbue. Not to mention that decimals, integers and rationals cannot compute something as simple as compound interest without lack of accuracy, so they're not going to save you when you're implementing Black-Scholes.
  - lamp987 6y ago
    >rationals cannot compute something as simple as compound interest without lack of accuracy Care to elaborate?
    - bidirectional 6y ago
      (Continiously) compound(ed) interest is e^(rate * time), e is not a rational number. Transcendental functions are commonly used in financial modelling, and once they appear, nothing short of a full-blown CAS will give you 100% accurate results (not that you should care, because your model is off by more than floating point error).
      - lamp987 6y ago
        i see
  - gamache 6y ago
    > finance doesn't necessarily require perfect accuracy Maybe finance doesn't, but billing does.
    - bidirectional 6y ago
      Yes, of course, I said that in my original comment. I'm arguing the advice as a panacea, not saying the OP was wrong to follow it.
  - 6y ago
mamcx 6y ago
Floating point is bad as null are, and is defended by the same apologist of null: "I don't make mistakes... so is good!". Floating point ARE a major source of errors across everyone that use them. ARE infectious. ARE semantically wrong. ARE not made for financial calculation. ARE WRONG. Period. Just because under a lot of discipline (or luck, or just "assume" is working but nobody have checked, or work before but how knows if today?) not make it a good choice for financial/money. Is the same error when people think old String types can be used in the unicode world, instead of have a proper type for that. Luck help a lot. But is not something to be proud about.
- bidirectional 6y ago
  Well no, the key difference between null and floating point is that for all its flaws, floating point is by far the fastest way of doing non-integer numerical computation. Null is just an ugly convenience hack of arguable merit, floats are fundamental. They're not a major source of error when you're implementing a model which is already inexact to a far larger degree than the problems caused by floats. Black-Scholes does not perfectly price an option, your bootstrapped curve is not a perfect predictor of market conditions in 28 years time. These are the problems faced in front-office finance, the error is already so far beyond 1 + 0.1 not perfectly matching 1.1 that it's not worth caring about. I've worked on applications where users just wanted to see numbers to the nearest 100k so they could model out a few trades they planned to make over the phone. When you're working on a retail banking app where you need to track customer's balances, or an accounting system, or anything like that, then sure, floating point would be malpractice. That is nothing like any of the applications I've worked on in the financial field.
- cameronh90 6y ago
  Finance isn't just banking and accounting. A huge amount of finance is modelling, simulations, signal generation, etc. where being fast is often way more important than being completely 100% accurate. In financial modelling, errors from floating point are going to be insignificant compared to all the other assumptions you make in your models. I have worked in finance for my entire career, and everyone uses floating point arithmetic for dealing with money on the modelling side (sometimes $0.01 = 1.0, sometimes $1 = 1.0, depends on the institution/currency/convention/context, but we have to deal with fractional money anyway). They do NOT use it on the accounting/back office side. That would be a spectacularly bad idea. Those systems are designed for accuracy to the penny.
bxparks 6y ago
You are getting a lot of down-votes, but you are correct, and I gave you some upvotes, at least one. If the application is doing financial modeling and estimations, where only 1-3 decimal place of accuracy is needed, then floating point is the right choice. It greatly simplifies the app. If the application is doing accounting, payments and billing, where people expect accuracy to the penny or more (e.g. to 1/10000 of a penny), then it needs to use a Decimal type.
smallnamespace 6y ago
Yes, every time someone categorically declaims that 'floats and money should never mix', I question whether they've ever met a real accountant. Accountants frequently spend all day in Excel. Excel uses floats for all computations (doubles, to be precise) [1]. Now, in all fairness, an accountant and developer's relationship to the numbers is rather different. For an accountant, the risk of floats blowing up is largely mitigated by the fact that they have a close, intimate relationship with the actual numbers. The responsible accountant should always deliver numbers they have personally reviewed, while a developer is usually automating a process, generating numbers that have yet to touch a human eye, so there is rather less room for error. Still, it's simply untrue that one should never use floats for money. Many of the cases where floats would generate bad results are also problematic for simple alternatives. For example, fixnums are simply 'floats that can't float', so you need to be able to guarantee a fixed range ahead of time. Understanding basic numerical analysis is unavoidable to writing correct code. [1] https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft...
FabHK 6y ago
FWIW, front office is the one place where floating points for money might be sensible, because in most cases in the front office you don't care about pennies. Instead, what you want to get right is derivatives ("risk"), which you compute with finite differences ("bumps") or somewhat more advanced methods (adjoint automatic differentiation, basically the backward propagation algorithm). So, yeah, if you deal with risk and PVs (in other words, maths) rather than actual cash flows, sure, knock yourself out, use floats. That does not invalidate the general advice to avoid floats for money (which is probably why you were downvoted).

devwastaken 6y ago

How did you handle the properties of floating point? The reason people recoil at that more than likely has to do with lack of knowledge in how floats are wrangled to ensure they're accurate.

stevesimmons 6y ago
Which properties? For front-office risk and pricing calcs, speed of calculation matters most. Hence IEEE754 float64. Fixed precision decimals really only matter for middle and back-office, for trade confirmation and settlement.
- tantalor 6y ago
  What's the difference between front/middle/back office?
  - arthurcolle 6y ago
    In the context of a bulge bracket investment bank, it's basically like this: Front office - S&T (sales & trading), i.e. revenue generating activity. Usually includes any quants/quant developers actively working on things that make money Middle office - operations. handles settlements, confirms, and generally anything related to the post-trade flow that is "after the trade is booked" Back office - accounting, legal, engineering, IT support, HR. Anything that isn't a revenue center that also isn't even tangentially facing revenue generating operations
bidirectional 6y ago
Which properties do you mean in particular? The main point is just that finance doesn't necessarily require perfect accuracy. It does when Bob sends Alice $1 and their account balances must line up perfectly, it doesn't when you're implementing models which already have greater error than floating point could imbue. Not to mention that decimals, integers and rationals cannot compute something as simple as compound interest without lack of accuracy, so they're not going to save you when you're implementing Black-Scholes.
- lamp987 6y ago
  >rationals cannot compute something as simple as compound interest without lack of accuracy Care to elaborate?
  - bidirectional 6y ago
    (Continiously) compound(ed) interest is e^(rate * time), e is not a rational number. Transcendental functions are commonly used in financial modelling, and once they appear, nothing short of a full-blown CAS will give you 100% accurate results (not that you should care, because your model is off by more than floating point error).
    - lamp987 6y ago
      i see
- gamache 6y ago
  > finance doesn't necessarily require perfect accuracy Maybe finance doesn't, but billing does.
  - bidirectional 6y ago
    Yes, of course, I said that in my original comment. I'm arguing the advice as a panacea, not saying the OP was wrong to follow it.
- 6y ago

mamcx 6y ago

Floating point is bad as null are, and is defended by the same apologist of null: "I don't make mistakes... so is good!". Floating point ARE a major source of errors across everyone that use them. ARE infectious. ARE semantically wrong. ARE not made for financial calculation. ARE WRONG. Period. Just because under a lot of discipline (or luck, or just "assume" is working but nobody have checked, or work before but how knows if today?) not make it a good choice for financial/money. Is the same error when people think old String types can be used in the unicode world, instead of have a proper type for that. Luck help a lot. But is not something to be proud about.

bidirectional 6y ago
Well no, the key difference between null and floating point is that for all its flaws, floating point is by far the fastest way of doing non-integer numerical computation. Null is just an ugly convenience hack of arguable merit, floats are fundamental. They're not a major source of error when you're implementing a model which is already inexact to a far larger degree than the problems caused by floats. Black-Scholes does not perfectly price an option, your bootstrapped curve is not a perfect predictor of market conditions in 28 years time. These are the problems faced in front-office finance, the error is already so far beyond 1 + 0.1 not perfectly matching 1.1 that it's not worth caring about. I've worked on applications where users just wanted to see numbers to the nearest 100k so they could model out a few trades they planned to make over the phone. When you're working on a retail banking app where you need to track customer's balances, or an accounting system, or anything like that, then sure, floating point would be malpractice. That is nothing like any of the applications I've worked on in the financial field.
cameronh90 6y ago
Finance isn't just banking and accounting. A huge amount of finance is modelling, simulations, signal generation, etc. where being fast is often way more important than being completely 100% accurate. In financial modelling, errors from floating point are going to be insignificant compared to all the other assumptions you make in your models. I have worked in finance for my entire career, and everyone uses floating point arithmetic for dealing with money on the modelling side (sometimes $0.01 = 1.0, sometimes $1 = 1.0, depends on the institution/currency/convention/context, but we have to deal with fractional money anyway). They do NOT use it on the accounting/back office side. That would be a spectacularly bad idea. Those systems are designed for accuracy to the penny.

bxparks 6y ago

You are getting a lot of down-votes, but you are correct, and I gave you some upvotes, at least one. If the application is doing financial modeling and estimations, where only 1-3 decimal place of accuracy is needed, then floating point is the right choice. It greatly simplifies the app. If the application is doing accounting, payments and billing, where people expect accuracy to the penny or more (e.g. to 1/10000 of a penny), then it needs to use a Decimal type.

smallnamespace 6y ago

Yes, every time someone categorically declaims that 'floats and money should never mix', I question whether they've ever met a real accountant. Accountants frequently spend all day in Excel. Excel uses floats for all computations (doubles, to be precise) [1]. Now, in all fairness, an accountant and developer's relationship to the numbers is rather different. For an accountant, the risk of floats blowing up is largely mitigated by the fact that they have a close, intimate relationship with the actual numbers. The responsible accountant should always deliver numbers they have personally reviewed, while a developer is usually automating a process, generating numbers that have yet to touch a human eye, so there is rather less room for error. Still, it's simply untrue that one should never use floats for money. Many of the cases where floats would generate bad results are also problematic for simple alternatives. For example, fixnums are simply 'floats that can't float', so you need to be able to guarantee a fixed range ahead of time. Understanding basic numerical analysis is unavoidable to writing correct code. [1] https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft...

FabHK 6y ago

FWIW, front office is the one place where floating points for money might be sensible, because in most cases in the front office you don't care about pennies. Instead, what you want to get right is derivatives ("risk"), which you compute with finite differences ("bumps") or somewhat more advanced methods (adjoint automatic differentiation, basically the backward propagation algorithm). So, yeah, if you deal with risk and PVs (in other words, maths) rather than actual cash flows, sure, knock yourself out, use floats. That does not invalidate the general advice to avoid floats for money (which is probably why you were downvoted).