[GNC] multiple currencies in one transaction
Clint Chaplin
joatmon at gmail.com
Tue Aug 20 09:17:10 EDT 2019
I have been following this discussion rather closely. I am American (not
something to be proud of right now), but I do travel a lot on business
worldwide, dealing with a lot of currencies. It'll be interesting to see
how this resolves.
On Tue, Aug 20, 2019 at 4:21 AM Jeff Abrahamson <jeff at p27.eu> wrote:
> When I said price editor, I meant the price database. I've been
> assuming that the rates there are used for proposing rates on new
> transactions.
>
> Anyway, it sort of goes to my point that I think the documentation is
> less than clear for those of us who have regular transactions in
> multiple currencies due to the vagaries of where we're standing when we
> make a purchase, even while it's probably perfectly clear for those
> whose foreign currency transactions are limited to holidays.
>
> I didn't know that about reports. I'm still learning clearly.
>
> Jeff
>
>
> On 20/08/2019 12:20, David Carlson wrote:
> > I thought this thread was discussing only transactions, not reports.
> > Jeff, the Price Editor does not come in to play in individual
> > transactions. Transactions are self-contained with whatever exchange
> > rates happen therein.
> >
> > The Price editor exists for reports that are not tied to individual
> > transactions, but to aggregate values, either of assets or of incomes
> > and expenses. Since some reports are supposed to be frozen at some
> > instant of time, say, at the end of a reporting period, and others are
> > supposed to accumulate over an interval of time, there is more than
> > one way to perform the aggregation appropriate to a particular report.
> >
> > Discussion of that feature is often heated, sometimes found to be
> > incorrect for this or that example, but not to be confused with
> > individual transactions that are fixed as a unit.
> >
> >
> > David Carlson
> >
> > On Tue, Aug 20, 2019 at 2:36 AM Jeff Abrahamson <jeff at p27.eu
> > <mailto:jeff at p27.eu>> wrote:
> >
> > It's not entirely unuseful that some of the discussion happens here
> on
> > the user list. User input may be of interest.
> >
> > I'll add only one point, which is that I think the current English
> > language documentation doesn't explain as well as it might how to use
> > gnucash in the presence of more than one currency. If I may say
> > it more
> > bluntly, it says it in a way that strikes me as useful to
> > Americans who
> > use non-local currency quite occasionally and less to Europeans
> > near the
> > UK or Swiss borders who use multiple currencies regularly. (I can't
> > speak for the rest of the world.)
> >
> > Just one tiny and minor example. The docs say that the price editor
> > will store only one FX rate per day for a currency pair. Now
> > suppose I
> > purchase a croissant in the morning and a tea in the afternoon,
> > both on
> > my bank card, and my card says that the I paid x1 and x2 in GBP
> > respectively and they were debited from my account as y1 and y2 in
> > EUR.
> > That constitutes two different exchange rates in one day. (This is
> an
> > experiment I've been meaning to try, but based on the documentation,
> > assuming it all works, it still won't leave me confident that
> > there's a
> > contract between me and gnucash to behave this way.)
> >
> > All that said, I still have to read up on the trading features
> (wasn't
> > at all obvious to me before this thread that this was relevant for FX
> > flow) and do a few experiments to understand it all better.
> >
> > Jeff
> >
> >
> > On 20/08/2019 00:36, Mike Alexander wrote:
> > > I’ve been thinking for a while about how the register could be
> > changed to better handle multiple currency transactions with
> > trading accounts turned on. Back when I implemented trading
> > accounts I knew this was a problem. However at that time it was
> > expected that the Register2 rewrite of the register code would
> > make the old register code obsolete so I didn’t worry about it too
> > much. This is clearly no longer a valid assumption.
> > >
> > > It seems to me that the simplest solution, and one that might
> > work reasonably well, is to make it possible (via a view option)
> > to switch any register into the format used for non-currency
> > registers where there are separate columns for shares, price, and
> > value for each split. The labels would need to be changed for
> > currency splits since “shares” becomes the value in the split’s
> > currency, “price” becomes exchange rate, and "value" remains the
> > value in the transaction currency. Getting all the details right
> > might be tricky, but this seems to have possibilities.
> > >
> > > The current code shows only one of those three values, and which
> > one depends on whether trading accounts are turned on. If they
> > are on it shows the value in the split’s currency. If off, it
> > shows the value in the transaction's currency. This adds still
> > more confusion to the situation. A simple change would be to make
> > it always show the value in the split’s currency. At least then
> > there wouldn’t be this confusion about what is being shown. I
> > didn’t do this at the time because one of the goals was to make
> > GnuCash work identical to the way it worked before if trading
> > accounts were turned off.
> > >
> > > If I get some free time I might try to play around with this
> > idea and see where it goes. If anyone else thinks this might work
> > and wants to take a crack at it, be my guest. My time for
> > software development is very limited these days.
> > >
> > > Mike
> > >
> > > PS: this probably should be moved to the Dev list if someone
> > wants to continue the discussion.
> > >
> > >> On Aug 16, 2019, at 2:12 PM, John Ralls <jralls at ceridwen.us
> > <mailto:jralls at ceridwen.us>> wrote:
> > >>
> > >> It's a difference in the way that the register works with or
> > without trading accounts. If trading accounts are turned off then
> > the register displays all splits converted to that register
> > account's currency. If they're on then it displays each split in
> > its own account's currency, with symbols for all but the current
> > register's currency.
> > >>
> > >> When dealing with multiple currencies "balance" can adopt
> > different meanings. GnuCash requires that a transaction must
> > balance in the transaction currency, which is generally the one
> > for the account in whose register the transaction was created (or
> > the first parent account denominated in a currency if the register
> > is for a non-currency account). Since in a currency register the
> > transaction currency value and the exchange rate between the
> > register currency and transaction currency are hidden that can be
> > difficult, especially in split view where the user is responsible
> > for getting the (possibly invisible) values right. The more
> > currencies involved in the transaction the harder it gets.
> > >>
> > >> Regards,
> > >> John Ralls
> > >>
> > >>
> > >>> On Aug 16, 2019, at 9:39 AM, Jeff Abrahamson <jeff at p27.eu
> > <mailto:jeff at p27.eu>> wrote:
> > >>>
> > >>> Thanks, that's great, I'll have a play with that after work.
> > >>>
> > >>> Two points concern me:
> > >>>
> > >>> 1. I don't see currency symbols in my registers. Is this a
> > setting?
> > >>>
> > >>> 2. I've been taught in accounting that transactions must
> > balance, and
> > >>> so I was expecting to see the GBP register all in GBP, the CHF
> > register
> > >>> all in CHF, and the EUR register all in EUR. That is, if one
> > sums the
> > >>> columns of a register, the Dr and Cr columns should have the
> > same sum.
> > >>> Am I confused about this principle?
> > >>>
> > >>> I'll need to read about the trading accounts feature. As
> > someone who
> > >>> regularly spends money in several currencies, I don't really
> > think of
> > >>> these transactions as P&L related, though I'm aware they could be
> > >>> modeled that way.
> > >>>
> > >>> Jeff
> > >>>
> > >>>
> > >>> On 16/08/2019 18:13, Adrien Monteleone wrote:
> > >>>> So the Cafe’s price was CHF 5.70?
> > >>>> Your card was debited GBP 4.67?
> > >>>>
> > >>>> The Expense:Coffee account is set to EUR?
> > >>>>
> > >>>>
> > >>>> First, yes, you’ll need Trading Accounts turned on.
> > >>>>
> > >>>> Second, open the price db and fetch rates, then check the
> > EUR-GBP rate, you’ll need it later. (I don’t see a way around this
> > at present) For this example, we’ll use 1.0957 which I fetched
> > just now.
> > >>>>
> > >>>>
> > >>>> Third, start your transaction by entering the following to
> > ‘fund’ the card with CHF:
> > >>>>
> > >>>> Cr. Transferwise GBP 4.67
> > >>>> Dr. Transferwise_CHF CHF 5.70
> > >>>>
> > >>>> (GnuCash will prompt for a GBP-CHF exhange rate, use the
> > ‘debit’ radio button and field and enter the CHF amount, in this
> > case, “-5.70")
> > >>>>
> > >>>> The transaction should now read something like:
> > >>>>
> > >>>>
> > >>>> -----
> > >>>>
> > >>>> Account Debit
> > Credit
> > >>>>
> > >>>> Assets:Current Assets:Transferwise
> > 4.67
> > >>>> Assets:Current Assets:Transferwise_CHF SFr.5.70
> > >>>>
> > >>>> -----
> > >>>>
> > >>>>
> > >>>> (the 4.67 has no currency symbol because it is in the
> > account’s currency - GBP)
> > >>>>
> > >>>> Fourth, enter the actual expense part of the transaction:
> > >>>>
> > >>>> Dr. Expenses:Coffee EUR 5.12
> > >>>>
> > >>>> The EUR 5.13 price is based on today’s rate between EUR-GBP
> > above and calculated either on the side, or directly in the
> > debit-entry field. (enter as: GBP*rate or 4.67*1.0957) We’re using
> > the EUR-GBP instead of CHF-EUR because this transaction is being
> > entered in the Transferwise (GBP) account.
> > >>>>
> > >>>> When GnuCash prompts for the exchange rate, simply commit the
> > FX window. We couldn’t wait to fetch the rate here, because we’d
> > have to back out and re-enter the amount in the register to match.
> > >>>>
> > >>>> Now, the transaction reads like this:
> > >>>>
> > >>>>
> > >>>> -----
> > >>>>
> > >>>> Account Debit
> > Credit
> > >>>>
> > >>>> Assets:Current Assets:Transferwise
> > 4.67
> > >>>> Assets:Current Assets:Transferwise_CHF SFr.5.70
> > >>>> Expenses:Coffee €5.12
> > >>>>
> > >>>> -----
> > >>>>
> > >>>>
> > >>>> Fifth, you need to balance this with CHF funds taken from the
> > card:
> > >>>>
> > >>>> Cr. Transferwise_CHF 5.70
> > >>>>
> > >>>> (GnuCash will again prompt for exchange rate, use ‘debit’ and
> > “5.70” for the amount)
> > >>>>
> > >>>> The transaction now looks like this:
> > >>>>
> > >>>>
> > >>>> -----
> > >>>>
> > >>>> Account Debit
> > Credit
> > >>>>
> > >>>> Assets:Current Assets:Transferwise
> > 4.67
> > >>>> Assets:Current Assets:Transferwise_CHF SFr.5.70
> > >>>> Expenses:Coffee €5.12
> > >>>> Assets:Current Assets:Transferwise_CHF
> > SFr.5.70
> > >>>>
> > >>>> -----
> > >>>>
> > >>>>
> > >>>> Sixth, since you have Trading Accounts turned on, when you
> > hit `Enter` to commit this transaction GnuCash will add the
> > balancing splits so your transaction looks like this in final form:
> > >>>>
> > >>>>
> > >>>> -----
> > >>>>
> > >>>> Account Debit
> > Credit
> > >>>>
> > >>>> Assets:Current Assets:Transferwise_CHF SFr.5.70
> > >>>> Expenses:Coffee €5.12
> > >>>> Trading:CURRENCY:GBP £4.67
> > >>>> Assets:Current Assets:Transferwise
> > 4.67
> > >>>> Assets:Current Assets:Transferwise_CHF
> > SFr.5.70
> > >>>> Trading:CURRENCY:EUR
> > €5.12
> > >>>>
> > >>>> -----
> > >>>>
> > >>>> You can of course, make the transaction more ‘complete’ (or
> > complicated as per your perspective) by also using actual CHF-GBP
> > and CHF-EUR rates and accounting for the conversion fee rather
> > than using the ‘debit’ option for those two splits.
> > >>>>
> > >>>> You could also separate this into two transactions, one to
> > ‘fund’ the CHF, then another to spend that on Coffee.
> > >>>>
> > >>>> Note, that I tried doing this from the Expense account, but
> > it was messy. I also couldn’t find a way to get GnuCash to
> > calculate the EUR price of the transaction for me using the
> > fetched rate. Maybe someone else has a trick up their sleeve.
> > >>>>
> > >>>> Regards,
> > >>>> Adrien
> > >>>>
> > >>>>
> > >>>>> On Aug 15, 2019, at 9:38 AM, Jeff Abrahamson <jeff at p27.eu
> > <mailto:jeff at p27.eu>> wrote:
> > >>>>>
> > >>>>> I purchased a coffee in Switzerland using my GBP-based
> > Transferwise
> > >>>>> card. That card could hold a CHF balance, but on that day
> > it held only
> > >>>>> GBP. My local currency is EUR. So I can think of my coffee
> > purchase as
> > >>>>> two FX transactions: funding (GBP -> CHF) and an expense
> > (CHF -> EUR).
> > >>>>>
> > >>>>> What Transferwise tells me is the amount of the purchase and
> > the amount
> > >>>>> of the transaction. (I can also poke further and find the
> > exchange rate
> > >>>>> they used and the minuscule fee, but the important point to
> > me is the
> > >>>>> the two endpoints.)
> > >>>>>
> > >>>>> Now what makes sense to me based on reading about accounting
> > principles
> > >>>>> for multiple currencies is that I should make one
> > transaction thus:
> > >>>>>
> > >>>>> Coffee (CHF) <-- Bank (GBP) # This is the funding
> > part of
> > >>>>> the transaction.
> > >>>>> Expense acct <-- Coffee (CHF) # Here I'm tracking
> > the actual
> > >>>>> expense.
> > >>>>>
> > >>>>> I put those in a single transaction for easier understanding
> > later.
> > >>>>> Here I've created a bank account for Transferwise (GBP) as
> > well as
> > >>>>> subaccounts of that called Transferwise_CHF and some others,
> > denominated
> > >>>>> in the indicated currencies. Those three accounts are transfer
> > >>>>> accounts: I usually expect them to have zero balance.
> > >>>>>
> > >>>>> Transferwise_CHF <-- Transferwise (GBP) # This is the
> > >>>>> funding part of the transaction.
> > >>>>> Expense/cafe <-- Transferwise_CHF # Here I'm
> > tracking
> > >>>>> the actual expense.
> > >>>>>
> > >>>>> Gnucash asks me for some exchange rates, and I answer for
> > the CHF - GBP
> > >>>>> part with the specific numbers provided by Transferwise and
> > for the GBP
> > >>>>> - EUR part with the exchange rate I've downloaded for that
> date.
> > >>>>>
> > >>>>> I expect to see this in the account Transferwise_CHF
> > >>>>>
> > >>>>> 5.70 <-- 5.70 # This is the funding part of the
> > transaction.
> > >>>>> 5.70 <-- 5.70 # Here I'm tracking the actual expense.
> > >>>>>
> > >>>>> and this in the Transferwise (GBP) account
> > >>>>>
> > >>>>> 4.67 <-- 4.67 # This is the funding part of the
> > transaction.
> > >>>>> 4.67 <-- 4.67 # Here I'm tracking the actual expense.
> > >>>>>
> > >>>>> and something similar looking at the splits in the
> > (euro-denominated
> > >>>>> expense account).
> > >>>>>
> > >>>>> But what I see (from the perspective of the CHF account) is
> > this, which
> > >>>>> makes no sense to me:
> > >>>>>
> > >>>>> Gnucash has entered the 2.80, and deleting that split just
> > makes it pop
> > >>>>> up again. Something is terribly wrong if any account's
> > view of the
> > >>>>> transaction doesn't balance. (This is gnucash 3.4, ubuntu,
> > build id
> > >>>>> 3.4+ (2018-12-30).
> > >>>>>
> > >>>>> Is this my error or a bug in gnucash? Any pointers?
> > >>>>>
> > >>>>>
> > >>>>> Somewhat related, I thought to import historical currencies,
> > as I'm back
> > >>>>> filling some data for analysis purposes. I grabbed 10 years
> > of daily
> > >>>>> quotes and imported them (3600 or so rows of data per
> > currency). All ok
> > >>>>> for GBP - EUR. When I do the same for CHF - EUR, gnucash
> > says it's done
> > >>>>> it, but the price database doesn't show more than a
> > handful. When I do
> > >>>>> it for JPY - EUR, gnucash says it's done but the price
> > editor shows none
> > >>>>> of them. The proposed exchange rates when entering
> > transactions are
> > >>>>> consistent with what the price editor thinks it knows.
> > >>>>>
> > >>>>> Is there a limit on FX rates? (This is about 4000, which
> > doesn't strike
> > >>>>> me as terribly large.)
> > >>>>>
> > >>>>> --
> > >>>>>
> > >>>>> Jeff Abrahamson
> > >>>> _______________________________________________
> > >>>> gnucash-user mailing list
> > >>>> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> > >>>> To update your subscription preferences or to unsubscribe:
> > >>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > >>>> If you are using Nabble or Gmane, please see
> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > >>>> -----
> > >>>> Please remember to CC this list on all your replies.
> > >>>> You can do this by using Reply-To-List or Reply-All.
> > >>> --
> > >>>
> > >>> Jeff Abrahamson
> > >>> +33 6 24 40 01 57
> > >>> +44 7920 594 255
> > >>>
> > >>> http://p27.eu/jeff/
> > >>> http://transport-nantes.com/
> > >>>
> > >>> _______________________________________________
> > >>> gnucash-user mailing list
> > >>> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> > >>> To update your subscription preferences or to unsubscribe:
> > >>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > >>> If you are using Nabble or Gmane, please see
> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > >>> -----
> > >>> Please remember to CC this list on all your replies.
> > >>> You can do this by using Reply-To-List or Reply-All.
> > >> _______________________________________________
> > >> gnucash-user mailing list
> > >> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> > >> To update your subscription preferences or to unsubscribe:
> > >> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > >> If you are using Nabble or Gmane, please see
> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > >
> > >
> > --
> >
> > Jeff Abrahamson
> > +33 6 24 40 01 57
> > +44 7920 594 255
> >
> > http://p27.eu/jeff/
> > http://transport-nantes.com/
> >
> >
> > _______________________________________________
> > gnucash-user mailing list
> > gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> > To update your subscription preferences or to unsubscribe:
> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > If you are using Nabble or Gmane, please see
> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > -----
> > Please remember to CC this list on all your replies.
> > You can do this by using Reply-To-List or Reply-All.
> >
> >
> >
> > --
> > David Carlson
>
> --
>
> Jeff Abrahamson
> +33 6 24 40 01 57
> +44 7920 594 255
>
> http://p27.eu/jeff/
> http://transport-nantes.com/
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
--
Clint (JOATMON) Chaplin
More information about the gnucash-user
mailing list