Question about multiple currencies

[Charles Day]

On Wed, Oct 15, 2008 at 1:35 AM, John M Collins <jmc at xisl.com> wrote:

>  Please CC to jmc AT xisl DOT com as I'm not subscribed.
>
> On Tue, 2008-10-14 at 19:35 -0700, Charles Day wrote:
>
> On Tue, Oct 14, 2008 at 8:05 AM, John M Collins <jmc at xisl.com> wrote:
>
>  Please CC to jmc AT xisl DOT com as I'm not subscribed.
>
> I'm having a bit of a problem with GNUcash in that the trial balance is
> out of balance when various transactions are done in multiple
> currencies.
>
> I've got most of the accounts in £ sterling with some in Euros and some
> in US$
>
> I've been careful to account for sums in US$ including accounts
> receivable etc. But the problem is when the bank converts between
> currencies and I use "transfer" it lets me put in whatever rate the bank
> has given but keeps all the other accounts showing the original
> conversion rate. I think that something should be posted to a "currency
> variations" account.
>
> I've ended up with a significant difference in the columns of the "trial
> balance" account.
>
> Can anyone suggest how I can get the trial balance into order without
> deleting all the transactions?
>
>
> What is the reporting currency used for the trial balance report? (I assume
> it's GBP.)
>
> Yes it is GBP
>
>  Once you buy USD and EUR, where do they go? Do they ever get changed back
> into GBP or another currency?
>
> The EUR transactions were a little bit complicated as in the case of Spain
> the customer withholds 10% which I then have to claim back out of my next
> payment to the UK tax people. So we end up wrestling with:
>
>    1. The GBP/EUR rate when the invoice was raised.
>    2. The rate when the customer pays 90%
>    3. The rate when that is converted to GBP by the bank which is
>    different even if done on the same day as 2.
>    4. The rate when I do the tax return.
>
> Of course these are all different.
>
> I think the GBP/USD problems are limited to just one point back in August.
> I'd kept all USD receipts in a $ bank account whilst the £ was sky-high
> against the $. When the £ sunk against the dollar I transferred most of the
> $ account to a £ account (of course I did that too early because the £ sank
> a lot more - sigh!). I put in the transaction using "transfer" and gave the
> rate that the bank converted it at which was different (worse of course -
> and they charge a commission on top) from what Finance::Quote thought.
>
> I think that caused the accounts imbalance to arise as the value of all the
> USD accounts were calculated at the value given by "Finance::Quote" but the
> GBP bank account was credited by a different amount. A sum should have been
> debited to a "currency conversions" account to make the accounts balance
> GBP-wise.
>
> A more complicated version of the same thing happened with the EUR
> accounts.
>

The imbalance is not caused by the exchange rate per se, but rather by not
accounting for realized currency gains/losses. When you transfer from a
non-GBP account into a GBP account, you are doing a currency sale and the
realized currency gain needs to be accounted for. It is the same concept as
when you sell a stock.  It would be nice if those entries could be helped
along in some automated way, but presently they aren't.

Just to illustrate the concept, try starting a brand new GnuCash file with
two accounts: one in GBP and one in EUR. Change some GBP into EUR. The trial
balance still balances. Now IN THE SAME REGISTER change all the EUR back to
GBP at a different exchange rate without accounting for the gain/loss. The
trial balance fails and the balance sheet will show the realized gain or
loss as unrealized. Instead, what you want to see is a good trial balance
and a balance sheet showing income from the currency gain/loss and no
unrealized gain/loss.

(Note: I say "in the same register" above because I have just discovered a
bug in the reports that needs fixing. Unfortunately, that may be part of the
imbalance you see!)


>
> Also, please look over the exchange rates in the Price Editor. Do you see
> entries at all that have an exchange rate of exactly 1? If so, make a note
> of the dates. On those dates you have at least one transaction with an
> incorrect exchange rate, probably due to a bug. (Upgrading to 2.2.7 should
> help prevent further errors.)
>
> Yes there were several bogus entries.
>
> I have downloaded and installed 2.2.7 and played with it but it seems there
> is still a problem - which I suspect is a bug - in this area. If you fill in
> the transfer with a specified amount that the bank actually converted it for
> rather than the rate which Finance::Quote believes in you will get an
> imbalance in the trial balance.
>
> I think the only way is to do the conversion at the current
> "Finance::Quote" rate, see what that credits to the GBP bank account and
> then put in an appropriate (GBP of course) transaction between the GBP bank
> account and a "currency conversions to GBP" P&L account to make the net
> amount credited to the GBP bank account agree with what the bank actually
> credits.
>
> The problem I have now is how to get the trial balance report balancing. It
> looks as if I'll have to delete all the USD/GBP and EUR/GBP transactions and
> re-enter them in the way I've described.
>

You don't have to delete anything, but to get balanced books you may need to
add entries to account for your realized currency gains or losses. Do you
have any EUR or USD accounts that have a zero balance?


> Or is there any better way? I'd be grateful if you could advise before I do
> - thanks.
>
>
>   John Collins Xi Software Ltd www.xisl.com
>
> Tel: +44 (0)1707 886110 (Direct)
>      +44 (0)7799 113162 (Mobile)
>
> Registered in England & Wales Company Number 1977148 VAT: GB 403 9239 64
>
> Trading Address: 3 Mandeville Rise, Welwyn Garden City, Herts, AL8 7JT, UK
> Reg Office: 2 Mill Road, Haverhill, Suffolk, CB9 8BD, UK
>

Cheers,
Charles