Best data type to store Financial/Monetary Values?

binsoii

Hi! First of all. I'm so happy i found this active forum because in the internet the resources there are 3 - 6 years old.

is there any new way or update in handling Financial Data?

I need to store amounts like 1000000000.654321 and pass in the database

I'm creating a Point of Sale System but i'm not sure in using double, because in C# .Net there is System.Decimal.
My Database is PostgreSql and i store it there as decimal/numeric.

So far these are the things i've found from https://forum.qt.io/topic/36788/qt-has-no-decimal-or-currency-implement/9.
* GNU Multiple Precision Arithmetic Library
* QDecimal
* Intel's Floating Point Math Library

I've also read in the Docs that the recommended Data type for Numeric in Postgresql is QString. How would i calculate that? convert it to double? isn't that inefficient?

Currently i'm trying QDecimal but i'm finding a hard time integrating it. and i found in github that the last update was Feb 2016. i am hesitant to continue to try it.

Has someone tried building a system like POS or anything that deals money? What did data type did you use? Can you help me? Thanks!

Regards!

VRonin

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 double is more than enough. these can be stored directly in a PostgreDB as double.
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)

binsoii

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.

jsulm

@VRonin @binsoii Do you really use double for financial calculations? Floating point numbers are not precise! Why not use unsigned long long and store values as cent, so 1$=100?

binsoii

@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!

VRonin

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, qint64 works 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 for std::sqrt() and std::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 double

P.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.

Eeli K

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-c

etc. 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.

binsoii

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!

kshegunov

I was summoned.
I'm looking at you @VRonin ;)

@binsoii

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 double was 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

qreal is the best type I think.

VRonin

@cfdev said in Best data type to store Financial/Monetary Values?:

qreal

http://doc.qt.io/qt-5/qtglobal.html#qreal-typedef qreal=double or, rarely, float

Chris Hennes

@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.

kshegunov

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. :)

Chris Hennes

@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.

Chris Kawa

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.

binsoii

@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

@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.

VRonin

@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 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)

Chris Hennes

@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.

binsoii

@VRonin

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)