Addition operation with currencies

JavaScript
1

Hi,
I need to perform an addition operation for some currencies values. The values are inputted into a table. What I’ve tested so far is:


        var millesimal_table_sum = function(){

            var sum_expense = 0;

            $(".expenses-value").each(function(){

                sum_expense += parseFloat($(this).val().replace(/[^0-9]/g, '')) / 100 || 0;
                
            });

            $('.total_expenses_table').text('€ '+sum_expense).addClass('font-green-sharp');
        
        }

        $('body').on('change', '.expenses-value', function() {
            millesimal_table_sum();
        });

The problem is if I try to make a sum of the following numbers:
0.12 + 12.34 I get 12.459999999999999 do you know why?

2

Simple answer is that computers use binary arithmetic. It’s fine for integers but when it comes to floating point numbers, the answers aren’t always exact. You just need to round the number to 2 places of decimals.

3

Hi @Gandalf thanks for your answer, is it accurate to round numbers for currencies? How do I achieve this? thanks for your help

4

The best way is to use Martin Fowler’s money pattern. See https://frontstuff.io/how-to-handle-monetary-values-in-javascript

5

I mean… it still doesnt actually solve Pitfall 3 that she outlined in her post…
and OP’s post is almost already following the strategy. take the float value of the string with no monetary separators is equivilant to treating the amount as a whole number of cents, because that regex will strip out the cents separator. As far as I can see, the assumption is that all currency amounts are in euros, so the idea of currency unit/conversion is irrelevant.

sum your expenses using the whole numbers, then divide the total by 100 and round to 2 decimal places.

6

Hi @rpkamp thanks for your answer I’ll have a look.

7

Hi @m_hutley thanks for your help. I’ve got another question. I’m using a mask plugin to make sure the values are formatted in the right way for example 1,234.34 then as you saw I remove the comma and leave the dot for the cents. Now what is the best way to store these values in the mysql database? Shall I use the format decimal and leave the dot for cents or is it better to remove the dot as well, store the value as number and then do maths after? Many thanks

8

Is that actually what you’re doing? Think that sentence through a bit.

In a MySQL database, currency values would be stored as a fixed point type.
https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html

9

With JavaScript, the most reliable way is to use cents as the base unit for money.

That way, $0.12 is stored as 12 cents, and $12.34 is stored as 1234 cents. Adding them together reliably gives you 1246 cents.

This is something that others have dealt with and made available as a library, such as with the currency.js library.

1 Like
10

With money I’d recommend storing things in integers and keep them integers as long as possible, i.e., only format as price at the last possible moment in templates. Sure, the database supports fixed precision, but when you read it back from the database it’s a float again in PHP. And floats are a royal PITA to work with.

1 Like
11

FWIW, for the purpose of text output you might just use Intl.NumberFormat which also does the rounding for you:

const formatter = new Intl.NumberFormat(navigator.language, {
  style: 'currency',
  currency: 'EUR'
})

$('.total_expenses_table').text(
  formatter.format(sum_expense)
)

Of course, this won’t save you from error propagation while still dealing with floating-point numbers under the hood.

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.