[GNC] multiple currencies in one transaction
Jeff Abrahamson
jeff at p27.eu
Tue Aug 20 07:17:06 EDT 2019
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/
More information about the gnucash-user
mailing list