Best data type to store Financial/Monetary Values?



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



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


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


  • Moderators

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



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



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



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


  • Qt Champions 2016

    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.



  • qreal is the best type I think.





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


  • Qt Champions 2016

    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.


  • Moderators

    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!



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



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



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



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



  • @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_100 it's twice as slow as mpf_float but all you have to do is download boost, add the path of booth in the INCLUDE += part of the .pro file and add #include <boost/multiprecision/cpp_dec_float.hpp> at the top of your source file


  • Qt Champions 2016

    You do understand the problem here is not with the floating point precision, but it's with the floating point itself, right?
    Floating point operations are exact to at most 1 epsilon which is the maximum relative difference between the two numbers. RELATIVE. I can even repeat it if it will help.

    Suppose you are working on a 10-base floating point computer and you have 2 digits for mantissa and 1 digit for exponent. The absolute difference between two numbers with zero exponent is 0.01 by construction. Now, what is the absolute difference between two numbers with exponent 2? Well, it is 0.01 * 100 = 1. So you trade off your absolute precision for dynamic range. For most intents and purposes this is just perfectly fine!
    When you need to do accounting, however, there are different regulations in place, and this trade-off is unacceptable! Your ABSOLUTE precision, can't be less than a specific amount, so that's why people use fixed-point, because the epsilon there is both relative and absolute measurement of the accuracy of an operation/number.



  • @kshegunov

    I'm not trying to be a smarta55, I'm honestly asking out of ignorance

    Can You show me an example of operation that can compromise say the 50th decimal of a boost::multiprecision::cpp_dec_float_100 ?


  • Qt Champions 2016

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

    You show me an example of operation that can compromise say the 50th decimal of a boost::multiprecision::cpp_dec_float_100 ?

    Of course not. :)
    That is, unless you're about to keep numbers in the magnitude of about 10 to the 40th power. We have no physical, tangible quantity that spans those ranges. Or to put some context in, as I'm a physicist after all:

    • the approximate diameter of the milky way (our galaxy) is in the magnitude 10^18 km, and has about 10^11 stars
    • the total number of atoms in the universe is estimated to about 10^80
    • we know physical constants with limited precision, but let's take one of the best known - the fine-structure constant (it's the exact number behind the atomic clock), so we know that with a certainty of about 10^-9.

    Now look back and tell me, do you need 50 decimal places really? The point is there's already established way to represent numbers for this specific purpose, and there are regulations in place (I mean, really, they've written down in law how rounding should take place). Also note 50 decimal places would translate to about 150 bits (~18 bytes) for the mantissa alone ...!

    As Chris said, it's been done, it's not new, it's known. So why would you venture into the depths of arbitrary precision arithmetic (i.e. brute force the solution) to combat a problem that's been solved way back?



  • Why not use cent as money unit and use int64?

    boost::multiprecision seems a little complex, personally I don't like the style of boost/STL, reason:

    1. syntax is not clear, even worse when considering the implementation
    2. speed is not extremely fast, for example, recently I've checked comparison of string formatting mechanisms between C and C++, the C style is more easy to use and 2x faster than C++ style. BTW, Qt and C# adopt the C way.


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

    Why not use cent as money unit and use int64?

    boost::multiprecision seems a little complex, personally I don't like the style of boost/STL, reason:

    1. syntax is not clear, even worse when considering the implementation

    I think this is based on personal taste and skills, boost and many part of the stl leverage lots of generic programming and TMP, if you are not familiar with basic of generic programming, it is natural for you to find the syntax is weird.

    About implementation, those library are not for average c++ programmer to maintain but for those programmers who are smart and love c++. As a season c++ programmer, I will expect they know and familiar with stl, basic generic programming and TMP.

    Unless for me, api and the idea of stl is brilliant, it is master piece of a genius. Quality of boost are very high too.

    1. speed is not extremely fast, for example, recently I've checked comparison of string formatting mechanisms between C and C++, the C style is more easy to use and 2x faster than C++ style. BTW, Qt and C# adopt the C way.

    This depends on the purpose of the design philosophy, if you are talking about std::stringstream or boost::format, usually they are slower or much slower than c library(especially boost format), after all their main purpose are not blazing fast. This do not mean they are bad or poor, but they have different design purpose.

    Do c api provide you type safety?Do c api manage your memory dynamic?Do c api provide you extension flexibility as std::stream provided? It is like compare apple with orange

    Evidence

    1 : std::sort is much faster than qsort of c, and I believe none of the c library can provide fast, light weight yet extensible algorithms api like stl provide(std::sort, std::transform, std::iota, std::set_difference etc), c do not have the expressive power of c++ provided. c++ can design almost any api c allowed to do, but there are many c++ api cannot be done by c
    2 : boost spirit is very fast, even faster than c functions if you use it right and compile your codes on modern compiler

    Both of the examples leverage generic programming and TMP, but their performance are great or superb, even better than standard c library.

    If speed is what matter most, we are still writing machine codes today.



  • @tham
    qsort is slower, but it can rewritten while keeping its interface unchanged.
    Compare Net Library and STL/boost, the interface of C# is better than that of STL/boost, though C# is not good at low level things.


  • Qt Champions 2016

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

    Thread carefully there.

    Do c api provide you type safety?

    It does, no less than the type-safety C++ provides.

    Do c api manage your memory dynamic?

    It does, the infamous malloc/realloc and free.

    Do c api provide you extension flexibility as std::stream provided?

    This doesn't make much sense, as std::stream is something STL specific. But there are libraries I'm sure, that provide the same functionality through a C API.

    std::sort is much faster than qsort of c

    Can you prove that?

    c++ can design almost any api c allowed to do, but there are many c++ api cannot be done by c

    Show me! That statement is leading and simply not true.

    Both of the examples leverage generic programming and TMP, but their performance are great or superb, even better than standard c library.

    Once again, you will have to prove that claim. You can't just drop the bomb and not follow up.



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

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

    Do c api provide you type safety? Do c api manage your memory dynamic?Do c api provide you extension flexibility as std::stream provided?

    Sorry, I did not state if clear, I was comparing standard c++ and standard c libraries(scanf, printf, strlen, strcpy, memcpy etc), the answer is no, standard c libraries never provide stream libraries like std::stream which provide type safe, manage memory automatic and extensible

    example :

    std::string line;
    std::string contents;
    while(std::getline(file, line)){
        std::istringstream iss(line);
        int a, b;
        if (!(iss >> a >> b>>contents)) { break; } // error
        process_file(a,b,contents);
    }
    

    Try to do it with c standard library without worry about memory leak, buffer overread(do you still remember heart bleed ?), type safe

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

    std::sort is much faster than qsort of c

    Can you prove that?

    I think this is common sense for any experiences c++ programmers(untrue for someone only know c with classes)?More than one c++ gurus mentioned this truth

    Performance of qsort vs std::sort?

    About boost spirit, see this post

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

    Show me! That statement is leading and simply not true.

    It is hard to find a c api cannot be done by c++, but it is very easy to come up simple api to prove that c do not have the ability to design the api c++ able to do, this is obvious because c++ offer much more features than c.

    All of the design should not pollute global scope

    1 : Design something like std::sort by c, which

    a : faster than qsort
    b : type safe
    c : generic and extensible, able to sort any kind of data
    d : it at most only need three parameters

    With c, I cannot satisfy all of the requirements by one api, but it is piece of cake by c++.

    2 : Design resource handle like std::unique_ptr

    a : light weight, as light as raw pointer
    b : can handle almost any resource
    c : extensible, able to determine how to clean up the resource
    d : api must be as easy to use as std::unique_ptr
    e : cannot copy but able to move

    example :

    monster *monster_a = new slime(30, slime_a)
    //do something
    delete monster_a;
    montser_a = nullptr;
    
    //with unique pointer
    auto monster_a = std::make_unique<slime>(30, "slime_a");
    //do something
    //forget it because monster_a will kill itself after it leave the scope
    

    3 : Design a function which could calculate the sum of value at compile time and runtime by one and single api

    constexpr long long addition(long long num)
    {
        long long sum = 0;
        for (long long i = 0; i <= num; i++)
        {
            sum += i;
        }
    
        return sum;
    }
    
    //.......
    addition(50000); //calculate the sum at compile time
    long long num = 0;
    std::cin>>num; //type safe :)
    addition(num); //calculate the sum at runtime
    

    4 : Make sure the double value you declare is a compile time constant(100%) without the risk of scope polluting

    //ultra simple and easy to read with c++
    constexpr double PI = 3.141592653589793;
    

    And so on, I cannot list them all. I believe similar examples and skills I mentioned are basic and mentioned in great c++ textbook.

    Every c programmer I met who bash c++ harsh never really spend their times to study c++, yet everyone of them pretend they know c++ very well, I always want to say f**k to them. It is impossible for c programmer to use c++ efficiently if they always refuse to spend their time to study a great c++ textbook(I know this because I learned c before c++). c++ is not a language you can treat it seriously, but a language you must treat it seriously, you cannot use it wise if you do not spend your time to study how are those gurus write their codes.



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

    qsort is slower, but it can rewritten while keeping its interface unchanged.

    If you are talking about ABI, it is true, one of the defects of template is it is hard to maintain ABI compatibility

    Compare Net Library and STL/boost, the interface of C# is better than that of STL/boost, though C# is not good at low level things.

    I am not familiar with c# so I cannot give opinion on this part, but I would not surprise if c# offer lots of good libraries, after all it is maintained by microsoft


  • Qt Champions 2016

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

    yet everyone of them pretend they know c++ very well, I always want to say f**k to them

    Okay, I'll take this as a sign I should stop arguing. Have a nice day.



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

    Okay, I'll take this as a sign I should stop arguing. Have a nice day.

    It fine, because I know c cannot design many api like c++ do, I already prove it by examples, I did not
    give those examples before I understand the limitation of c, it is a wise decision to avoid the challenge,
    because my requests are mission impossible for c but easy to be done by c++.

    Sorry for the words(f**k), I know it is harsh and no polite but nothing personal.
    It is my true feeling when I saw the wars between c and c++ several times
    There are too many c programmer who believe they are good at c++ and bash c++ very harsh without spending
    their time to study it(full of false statements), it is no use to reason with them whether there are prove or not
    If you are not one of them, you do not need to feel angry.

    I wouldn't say I am an expert of c or c++(even today I am still studying programming, I know there are too many things I need to learned)
    but I do spend my times to study and research these two languages and sure
    the examples I mentioned are true, if I am wrong I would be happy to learn that(if someone can give me working example rather
    than shouting or "I believe").

    I already finish my part and prove that I am not one of those "pseudo c++ expert", all of the examples I mentioned are impossible to
    be done by pure c, you can mimic it by void*, macro nor preprocessor, but this will sacrifice type safe or pollute global scope, it is the limitation of pure c.


  • Qt Champions 2016

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

    you do not need to feel angry.

    I don't either way, but this is not the correct thread for such a discussion.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.