How to handle small amounts/rates/fees when using an integer (e.g. like Money)

In ex_money (I’m the author) this is one reason for using decimal. Rounding to the currencies precision (and for some currencies and usages to a multiple) is deferred until the latest possible moment.

Using your example, its perfectly acceptable to have arbitrary precision on money arithmetic. This is relevant not only at the gas pump but in financial trading as well. So maybe I have a value $3.45678. Perfectly OK and in factor in financial instruments a requirement to at least 7 digits if I recall (it may not be that exactly).

Another example is when doing currency conversion - higher precision is desirable.

The at some point the result is credited to a bank account which means applying the appropriate rounding. There are at least three considerations:

1. Rounding to the right number of decimal digits. 0,1,2, 3 and 4 digits are in use with different currencies around the world.

2. Use the right rounding mode. Typically you would use bankers rounding which, in the Decimal library is mode :half_even and is also the default in ex_money.

3. Apply any necessary round nearest for the currency. For example, in Australia there is no cash amount below 5c so you need to round to the nearest 5c for cash amounts. Similar for the Swiss franc.

Last example. Lets say you have $5.23 and you want to split it 3 ways.

hex> m = Money.new(:USD, "5.23")
#Money<:USD, 5.23>
iex> Money.div m, 3
{:ok, #Money<:USD, 1.743333333333333333333333333>}
iex> Money.div!(m, 3) |> Money.round
#Money<:USD, 1.74>
iex> Money.div!(m, 3) |> Money.round |> Money.mult!(3)
#Money<:USD, 5.22>

Where did the last cent go? Thats the reason I implemented Money.split/2.

iex> Money.split(m, 3)
{#Money<:USD, 1.74>, #Money<:USD, 0.01>}

Which allows you to decide what you do with the amount left over. Oh, and Money.split/2 takes an arbitrary precision money amount, rounds it correctly and does the math.

I don’t recommend using integers for money amounts for all of these reasons.

ex_money will allow you to create, store, retrieve and do math on arbitrary precision money amounts.

It will correctly round the amount appropriate to the currency whenever you need that value. Calling Money.to_string/2 will apply the appropriate rounding, for example.

So yes, in your example, it would be appropriate to have the money amounts be arbitrary in precision and then only round on output.

You will still need to handle the issue of displaying rounded numbers that don’t add exactly to a rounded total (when viewed in a spreadsheet for example. In this case its probably better to round the numbers first and then sum.

Thanks for the answer..

I guess that in bank accounts and for bills (when paying by credit card for example) it’s possible to have amounts precise down to the second digit (0.01) right?
And just to be sure.. This rounding is done at will when we want to display a result for example. So besides the rounding the value keeps it’s actual value, right?

Ok, now it makes sense..

In the case of ex_money, do we need to configure the digit precision (or other kind of settings like the round mode you introduced)? Or is everything handled right out of the box?

BTW, I think I’m going to try it right now (using Postgres)..

Just a quick question, is it necessary to set both ex_money and ex_money_sql or just the later is ok and it will bring the former? (sorry if it’s obvious).
Edit: I just tried by only setting ex_money_sql and it brought ex_money by itself..
But what’s the best practice when defining dependencies. Is it better to explicitly declare ex_money too?
I guess that it’s okay to only declare the later since it’s the same for ecto on Phoenix projects where only ecto_sql is used on the deps list…
And just, a quick remark, you might want to bump the version on the docs as well where it’s till v4.0 (same apply to ex-money_sql with v1.0). It’s not a big deal but it can simplify copy/pasting from he doc.

Also, how does the custom sum() aggregate function generated by ex_money_sql performs relative to the default Postgres sum aggregate function?

I see that it’s also possible to use Repo.aggregate with recent ecto versions.. I guess that it’s just a syntactic sugar and that it’s the same performance as making the query, right?

Also, can you give me real quick an example on how to search the db for a given price (maybe in a range too?) and for a given currency (again maybe giving an inclusion list too?). Just to be sure to understand how to deal with the DB..

For example in the example here, what could be the query to order only the USD, or only the AUD, etc.?

Updated the docs for ex_money since it is a major version change. I left the ex_money_sql docs alone since hex will pick up the latest version automatically.

Just ex_money_sql is enough as you found. Normally better, in my opinion, to let Hex resolve transitive dependencies rather than try to configure them yourself.

Its going to be basically the same or very slightly slower since the database does have to check each row it is summing to make sure they are the same currency. I favour correctness in this case over absolute speed.

Yes, its the same under the hood.

Here’s an example (which I’ve added as a test in the repo too):

query = from o in Organization,
          where: fragment("currency_code(payroll)") == "USD",
          select: sum(o.payroll)

Basically you have to wrap the composite value component in a fragment since its not natively supported in Ecto since its not cross-db compatible syntax. In this example, currency_code is the component of the composite field payroll that contains the currency code. In your schema you would have declared:

field :payroll, Money.Ecto.Composite.Type

Thank you very much!
Everything is clear!
Keep up the good work!