Best data type to store Financial/Monetary Values?
-
I deal with financials and there are 2 levels of precision needed:
- for cash flows (amounts in $ or whatever currency you use) require just 2 decimal places precision so native
doubleis more than enough. these can be stored directly in a PostgreDB asdouble. - for accrual fractions or parameters in financial models where greater precision is needed boost::multiprecision works great. You have to be very careful however to design all the calculations not to truncate the precision. These types however need to be stored as binary in the database (so you cannot directly call things like
select * from MyTable where SomeNumber < 0.1)
- for cash flows (amounts in $ or whatever currency you use) require just 2 decimal places precision so native
-
Hi! Thanks for the reply!
I see so double will be enough storing total gross and prices such as (1000.85, 987654321.75).
and interest, discounts and tax which i need to store in the database in this format 0012.654321. i will use the boost::multiprecision?
What do you mean by truncating the precision? Can you give me an example code of truncating the precision so i could i avoid it.
Thank you so much for the help. i really have a hard time about this.
-
I deal with financials and there are 2 levels of precision needed:
- for cash flows (amounts in $ or whatever currency you use) require just 2 decimal places precision so native
doubleis more than enough. these can be stored directly in a PostgreDB asdouble. - for accrual fractions or parameters in financial models where greater precision is needed boost::multiprecision works great. You have to be very careful however to design all the calculations not to truncate the precision. These types however need to be stored as binary in the database (so you cannot directly call things like
select * from MyTable where SomeNumber < 0.1)
- for cash flows (amounts in $ or whatever currency you use) require just 2 decimal places precision so native
-
@jsulm Hi!
Converting money value into cents looks like a good idea, i never thought about this but this sounds really good.
forgive my curiosity but have you tried implementing this approach and let users use with precise results in dealing interest, discount and tax rates? have you develop real applications that deals with money with precision. like 999999999.999999? If so, can you me give an example on how you did it? on computing and conversion.
Because, I don't like to do this approach with just a theory solution. I don't want to go through pulling my hair wondering my financial reports having differences like 0.05 cents.
Thanks!
Regards!
-
double is more than enough if you just need 2 decimals, I wouldn't use unsigned for any financial calculations as wrapping around 0 is a nightmare,
qint64works but the precision of double is really not worth the pain going through the conversion. I store bonds cash flows (calculated by 3rd party software) in double and it never triggered any mismatch flags.interest, discounts and tax
if we are talking retail shop kind of things then probably double still works. if we are talking stock market pricing models then you probably want to go to boost::multiprecision.
Can you give me an example code of truncating the precision so i could i avoid it.
Imagine taking the square root or a trigonometric function (sin/cos).
boost::multiprecision gives you replacements forstd::sqrt()andstd::sin()that maintain the precision and do not truncate it to double. you have to take care that all methods you use do not naively truncate to double, do the calculation and then spit out a boost::multiprecision type that is now just a wrapper on doubleP.S.
before some proper tech flames me (I'm looking at you @kshegunov ), you have to consider the trade-off between getting the, say, 8th decimal wrong in an operation using double and the implementation time and complexity of a software that uses a more precise implementation. -
http://stackoverflow.com/questions/26764807/why-decimal-float-should-be-used-in-financial-calculations-while-it-has-rounding
http://stackoverflow.com/questions/149033/best-way-to-store-currency-values-in-cetc. etc... There's no one simple solution for all cases. But about your question "isn't that inefficient?" If a Point of Sale System is what I presume it is you really don't have to think about efficiency unless you expect to have massive amounts of interactions. Especially if you do calculations and conversions client-side in a GUI application. Just be realistic about how the application/system will be used. IMO if you would be designing a critical application for a large company which would handle millions of dollars you wouldn't be asking here for advice. At least I hope so.
-
Hi Thanks for pointing that out. What solution should i use then? I'm still confused. Sorry, i really need help about this. Because we would be developing an Accounting System next if this POS would work out. the thing is, i don't want to proceed with a complex solution if there is a common solution developers are using to handle this matter. You said just be realistic about the system, are you suggesting to just use double? But i will need to create reports down to at least 6 decimal places. Am i overcomplicating this? if so, can you point out how to handle this based on your experience? Thanks!
Regards!
-
I was summoned.
I'm looking at you @VRonin ;)Forget inefficiency, it has no bearing here.
In my mind you have to decide between a fixed and floating point first. For finances I'd always go with fixed point if possible (especially if it's something banking related, a very touchy bunch those bank fellows ;)), as the precision isn't subject to scaling, in contrast to floating point representation.
If you decide to use floating point however, then you need to decide how to proceed - with standard double or with a multiprecision. Ideally you'd have a type that has a lot of bits for the mantissa and somewhat few for the exponent. Unfortunately we don't live in a perfect world and
doublewas designed to keep general purpose data, so the width of the mantissa and the exponent are fixed. I don't know if boost::multiprecision allows changing this, but in any case it's worth researching. Also when doing any operations on the numbers you have to be absolutely sure you don't carry out the calculation error into the significant digits, so it's imperative to choose appropriate algorithms. -
@cfdev said in Best data type to store Financial/Monetary Values?:
qreal
http://doc.qt.io/qt-5/qtglobal.html#qreal-typedef
qreal=doubleor, rarely,float -
Hi Thanks for pointing that out. What solution should i use then? I'm still confused. Sorry, i really need help about this. Because we would be developing an Accounting System next if this POS would work out. the thing is, i don't want to proceed with a complex solution if there is a common solution developers are using to handle this matter. You said just be realistic about the system, are you suggesting to just use double? But i will need to create reports down to at least 6 decimal places. Am i overcomplicating this? if so, can you point out how to handle this based on your experience? Thanks!
Regards!
@binsoii said in Best data type to store Financial/Monetary Values?:
But i will need to create reports down to at least 6 decimal places.
If you really have a need to generate six decimal places regardless of the magnitude of the number you cannot use a floating point number for it, as @kshegunov mentions. By construction you have a system with a fixed decimal restriction. To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
Chris Hennes, Pioneer Library System
-
@binsoii said in Best data type to store Financial/Monetary Values?:
But i will need to create reports down to at least 6 decimal places.
If you really have a need to generate six decimal places regardless of the magnitude of the number you cannot use a floating point number for it, as @kshegunov mentions. By construction you have a system with a fixed decimal restriction. To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
It's probably feasible to get away with a 34.30 fixed point signed int64, which would give about 7-8 significant decimal digits after the dot with range of a few billion. However it gets really tricky when you need to provide even some basic functions - logarithms, (square) root(s), powers for such a representation.
To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
That's somewhat funny (in an ironic way), because this is probably one of the most complex problems one can encounter in programming, so it's really hard to be intimately familiar. :)
Read and abide by the Qt Code of Conduct
-
It's probably feasible to get away with a 34.30 fixed point signed int64, which would give about 7-8 significant decimal digits after the dot with range of a few billion. However it gets really tricky when you need to provide even some basic functions - logarithms, (square) root(s), powers for such a representation.
To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
That's somewhat funny (in an ironic way), because this is probably one of the most complex problems one can encounter in programming, so it's really hard to be intimately familiar. :)
@kshegunov said in Best data type to store Financial/Monetary Values?:
this is probably one of the most complex problems one can encounter in programming, so it's really hard to be intimately familiar. :)
Absolutely true! I consider myself quite well-versed in floating point numbers (at least, of the IEEE 754 flavor), and I wouldn't trust myself to work on a financial system with these requirements.
-
Don't use floating point types for financial data and don't re-invent a wheel implementing fixed point functions.
There are libraries for this. For example: fixed_point - CppCon talk: John McFarlane “fixed_point"
There are more if you don't like this one, just google it. But don't try to do it yourself. You'll just waste time and re-make the same mistakes people already did and fixed. -
Don't use floating point types for financial data and don't re-invent a wheel implementing fixed point functions.
There are libraries for this. For example: fixed_point - CppCon talk: John McFarlane “fixed_point"
There are more if you don't like this one, just google it. But don't try to do it yourself. You'll just waste time and re-make the same mistakes people already did and fixed.@Chris-Kawa Thanks! i'm not trying to re-invent the wheel by the way. i just want to use the existing ones already available and easy to use, just like the System.Decimal in C# .Net sitting there ready to be taken. Why there isn't just like this already available in Qt!? :( How could this amazing framework don't have a simple thing to address this. and i really don't like the idea of using third party libraries just for this case. (I don't even know where to start integrating the example you gave, i'm really new to Qt)
Cheers!
-
@binsoii said in Best data type to store Financial/Monetary Values?:
But i will need to create reports down to at least 6 decimal places.
If you really have a need to generate six decimal places regardless of the magnitude of the number you cannot use a floating point number for it, as @kshegunov mentions. By construction you have a system with a fixed decimal restriction. To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
@Chris-Hennes we already have an existing system, using Django and Angular to accomplish this. we used Decimal in postgresql and accounting.js library in the front end.
-
@binsoii said in Best data type to store Financial/Monetary Values?:
But i will need to create reports down to at least 6 decimal places.
If you really have a need to generate six decimal places regardless of the magnitude of the number you cannot use a floating point number for it, as @kshegunov mentions. By construction you have a system with a fixed decimal restriction. To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
@Chris-Hennes said in Best data type to store Financial/Monetary Values?:
To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
Strongly disagree. I don't know 10% of what's necessary to understand end to end encrypted communication but I use OpenSSL as I trust them to know what's necessary. Division of cognitive labor is what made humanity great. A reliable enough library (like boost) is all you need.
@binsoii said in Best data type to store Financial/Monetary Values?:
Why there isn't just like this already available in Qt!?
Qt integrates a lot more than you'd expect by a framework born to be a UI. Sometimes even the functionality integrated in Qt is not at par with what's available externally (see FTP support for an example).
@binsoii said in Best data type to store Financial/Monetary Values?:
and i really don't like the idea of using third party libraries just for this case
My suggestion involves using a library (boost) that is much closer to the standard than Qt is. I would not consider boost code quality inferior to Qt's in any aspect. <exageration>I don't think there's a C++ programmer that doesn't have boost ready to be used on his/her machine</exageration>.
Bottom Line:
if you are concerned with precision useboost::multiprecision::mpf_float_100100 decimals (base10) precision is more that I can imagine anyone needing (you can still increase it arbitrarily btw, usingboost::multiprecision::number<gmp_float<N> >where N is the number of base10 decimals precision you want)"La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
~Napoleon BonaparteOn a crusade to banish setIndexWidget() from the holy land of Qt
-
@Chris-Hennes said in Best data type to store Financial/Monetary Values?:
To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
Strongly disagree. I don't know 10% of what's necessary to understand end to end encrypted communication but I use OpenSSL as I trust them to know what's necessary. Division of cognitive labor is what made humanity great. A reliable enough library (like boost) is all you need.
@binsoii said in Best data type to store Financial/Monetary Values?:
Why there isn't just like this already available in Qt!?
Qt integrates a lot more than you'd expect by a framework born to be a UI. Sometimes even the functionality integrated in Qt is not at par with what's available externally (see FTP support for an example).
@binsoii said in Best data type to store Financial/Monetary Values?:
and i really don't like the idea of using third party libraries just for this case
My suggestion involves using a library (boost) that is much closer to the standard than Qt is. I would not consider boost code quality inferior to Qt's in any aspect. <exageration>I don't think there's a C++ programmer that doesn't have boost ready to be used on his/her machine</exageration>.
Bottom Line:
if you are concerned with precision useboost::multiprecision::mpf_float_100100 decimals (base10) precision is more that I can imagine anyone needing (you can still increase it arbitrarily btw, usingboost::multiprecision::number<gmp_float<N> >where N is the number of base10 decimals precision you want)@VRonin said in Best data type to store Financial/Monetary Values?:
Strongly disagree. I don't know 10% of what's necessary to understand end to end encrypted communication but I use OpenSSL as I trust them to know what's necessary. Division of cognitive labor is what made humanity great. A reliable enough library (like boost) is all you need.
In most cases you and I are in complete agreement on this point. The problem with finance is that the question isn't just reliability, it's regulation. In particular, does the regulatory environment this software is going to be used in specify when and how the rounding must take place? There are usually very, very specific requirements on software that handles financial transactions.
-
@Chris-Hennes said in Best data type to store Financial/Monetary Values?:
To be honest, my advice to you is to stay far away from developing this type of software until you are intimately familiar with the details of how computers store and operate on numbers.
Strongly disagree. I don't know 10% of what's necessary to understand end to end encrypted communication but I use OpenSSL as I trust them to know what's necessary. Division of cognitive labor is what made humanity great. A reliable enough library (like boost) is all you need.
@binsoii said in Best data type to store Financial/Monetary Values?:
Why there isn't just like this already available in Qt!?
Qt integrates a lot more than you'd expect by a framework born to be a UI. Sometimes even the functionality integrated in Qt is not at par with what's available externally (see FTP support for an example).
@binsoii said in Best data type to store Financial/Monetary Values?:
and i really don't like the idea of using third party libraries just for this case
My suggestion involves using a library (boost) that is much closer to the standard than Qt is. I would not consider boost code quality inferior to Qt's in any aspect. <exageration>I don't think there's a C++ programmer that doesn't have boost ready to be used on his/her machine</exageration>.
Bottom Line:
if you are concerned with precision useboost::multiprecision::mpf_float_100100 decimals (base10) precision is more that I can imagine anyone needing (you can still increase it arbitrarily btw, usingboost::multiprecision::number<gmp_float<N> >where N is the number of base10 decimals precision you want)if you are concerned with precision use boost::multiprecision::mpf_float_100 100 decimals (base10) precision is more that I can imagine anyone needing (you can still increase it arbitrarily btw, using boost::multiprecision::number<gmp_float<N> > where N is the number of base10 decimals precision you want)
Thanks a lot! boost::precision seems to be the best choice for my need. i will implement this and give an update once its done. PS (I don't where to start on how to use integrate/install this, i guess i have a lot studying to do. :D)
-
if you are concerned with precision use boost::multiprecision::mpf_float_100 100 decimals (base10) precision is more that I can imagine anyone needing (you can still increase it arbitrarily btw, using boost::multiprecision::number<gmp_float<N> > where N is the number of base10 decimals precision you want)
Thanks a lot! boost::precision seems to be the best choice for my need. i will implement this and give an update once its done. PS (I don't where to start on how to use integrate/install this, i guess i have a lot studying to do. :D)
@binsoii said in Best data type to store Financial/Monetary Values?:
I don't where to start on how to use integrate/install this, i guess i have a lot studying to do.
mpf_float_* only works on GNU compilers and depends on an external library.
You can use
cpp_dec_float_100it's twice as slow asmpf_floatbut all you have to do is download boost, add the path of booth in theINCLUDE +=part of the .pro file and add#include <boost/multiprecision/cpp_dec_float.hpp>at the top of your source file"La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
~Napoleon BonaparteOn a crusade to banish setIndexWidget() from the holy land of Qt
