Re: Multicurrency issue, concerns, and proposed longer-term direction.

[Erik Huelsmann <..hidden..>]

Hi Chris,

> We have been having a slight multicurrency issue with payments that we
> do not believe can be adequately resolved inside a stable branch.
> Fortunately this issue is not severe and it may be able to be possible
> to create a periodic job to fix the problem.

> The issue is that the way multicurrency calculations are performed in
> LedgerSMB 1.3, there are slight roundoff errors that occur between
> posting and payment for AR/AP transactions.  As far as I can tell
> these occur when the exchange rate changes.

Exactly. I have a case of those right here (best viewed in a fixed-width font):

 entry_id | accno |         description          |   amount   |  sell
----------+-------+------------------------------+------------+--------
     1073 | 5011  | Hosting Expenses.            |     -189.3 | 1.5797
     1074 | 5011  | Hosting Expenses.            |    -109.74 | 0
     1075 | 2100  | Accounts Payable             |     299.04 | 1

All the other times I researched this was from the local currency
perspective. I was also looking if the documents were balanced, etc.

Today, I looked at this set the other way around: I *know* the FX
amount posted is 189,30 at a rate of 1,5797. Which is not the 299.04
being posted to the ledger, but 299.03721.

Then, the payment goes on to post the following transaction:

 entry_id | accno |         description          |   amount   |  sell
----------+-------+------------------------------+------------+--------
     1284 | 1071  | PayPal Merchant Account      |  299.30223 | 1.5811
     1289 | 2100  | Accounts Payable             | -299.03721 | 1.5811
     1290 | 4450  | Foreign Exchange Gain / Loss |   -0.26502 | 1.5811

Which comes from paying 189.30 at a rate of 1.5811.

As you can see, this transaction correctly posts -299.03721 to the
accounts payable account.

> My recommendation at present is to do as follows:
>
> 1)  Add a user-configurable threshold setting (and make it default to
> 0.005) for paid in full payments.
> 2)  Either accept there could be a few cents of drift in AR/AP or
> create an SQL script to correct this if we need to.

I searched our sources for 'round(', but turned out I had to search
for 'round_amount(' and our sources (Perl ones) are really littered
with that function call. It looks like it'll be hard to weed out all
differences, but in the end I don't see how else to do it...

The above seems like a good short-term solution, let's say something
we could be looking at for .11 or .12.

> Longer-run I think we need to be able to float foreign currencies and
> handle fx gain/loss as a reporting matter rather than something posted
> to the books.  I don't see this as being possible until the financial
> logic is redesigned so that would be *at least* 1.5, maybe later.
>
> What do people think?

The problem I had when we first discussed this off-list -which also
prompted me today to continue searching for "the cause"- is that I'm
not sure any amount of math can solve this problem better than what we
can post to the books: like I said when you said we should make lsmb
fully double-currency accounted: if we can't solve the rounding issues
now (note that I didn't understand the cause back then), then how
would we solve the fact that the numbers add up by adding more
numbers? Because the balance is reduced to 0 (zero) in the foreign
currency, but not the local currency, I did not understand how adding
a local currency to the ledger (or AR/AP subledger) would solve our
problem.

Now that I think we found the problem, I don't see that as an issue
anymore. Where I do think that reporting-only fx accounting might be a
problem is in reversal documents (as they are today): how do you
intend to solve the problem where the currency date and the document
date to be used aren't the same date? This situation can happen when
you want to reverse a transaction. To correctly revert the FX
gains/losses, you need to use the rate of the original transaction. In
this scenario, it's not always possible to post the reversal document
on the date of  the orginal document: think closed periods.

I'm definitely not opposed, but would like to work through the
different scenarios together and see how we want to handle those. Only
then I think we can be certain that the new solution solves the
existing problems.

Comments?

Bye,

Erik.