[GNC] Confused by report "Price Source" when recording bills in a foreign currency

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[GNC] Confused by report "Price Source" when recording bills in a foreign currency

James Walker
Hello fellow GnuCash users,

Per the subject, I'm somewhat confused by the impact of the report "Price Source" when recording Bills received in a foreign currency.

I'm using GnuCash for a UK business (all accounts in GBP) for very simple affairs.

I use the services of a US business which invoices everything in USD.

To account for this GnuCash, I've done the following:


  *   Enabled Trading Accounts
  *   Created an A/Payable account in USD ("Liabilities:Current Liabilities:Trade Creditors USD")
  *   Created a USD expenses account for the company ("Expenses:US Company USD")
  *   Created a Vendor for the company and set its payment currency to USD

When I receive an invoice from the company, I do the following:


  *   Create a Bill for the invoice, with the USD amount shown on the invoice, for the Vendor
  *   Post the Bill to the Trade Creditors USD (Liability) / US Company USD (Expense) accounts

When I pay the invoice, I do the following:


  *   Credit Bank (GBP) and debit Trade Creditors USD (USD)
  *   GnuCash prompts for the exchange rate; I input the GBP amount which was taken from the bank account for the payment

An example of three invoices received:


  1.  Invoice 1: $14.40
  2.  Invoice 2: $14.40
  3.  Invoice 3: $14.40

  *   Balance of US Company USD (Expense): $43.20

When credited from Bank (GBP), the amounts respectively are:


  1.  £10.60
  2.  £10.87
  3.  £11.02

  *   Total expenses paid from bank in GBP: £32.49 - this is the figure I expect to see on the P&L

These figures match up with what is shown in the trading accounts: GBP £32.49, USD -$43.20.

Generating P&L

When I now go and generate a report (e.g. Profit & Loss), the default options seem to be showing the expenses at the most recent exchange rate.

I see expenditure of e.g. £32.61, with a £0.12 currency trade. This means the P&L is wrong and the balance sheet won't balance, because the expenditure from Bank was actually only £32.49.

It seems the multi-currency features in GnuCash are geared towards actual currency trading/asset tracking etc., not my simple "I've got a USD invoice" scenario.

To me, the £32.61 figure seems blatantly incorrect; I haven't spent that much money. My understanding is the P&L needs to show £32.49, which is how much was actually taken from the Bank and debited to "Trading:Currency:GBP" from "Trading:Currency:USD".

It seems GnuCash is forcing automatic currency conversions onto me when I don't need them, and breaking things in the process, but my understanding may be wrong, which is why I'm asking this.

By changing "Price Source" in the report options to either "Average Cost" or "Weighted Average," I can get the P&L to show £32.49 as expected.

However, I don't understand why this works, or what these two price sources are actually calculating. I've read the documentation and don't fully understand the language so could really benefit from some clarification.

Although it is currently working, I need to understand why it is working and whether it will continue working, in order to have peace-of-mind that the P&L will continue to show the expected figure when using these options in the future, after adding further transactions.

The documentation says this of "Average Cost":

            The amount-weighted net average of all splits exchanging the commodity for another regardless of account. Gain/loss splits are included in the calculation.

While this does produce the value I expect to see on the report (in my trivial experiments), I'm concerned about the "average" labelling as I don't understand what is actually being averaged or how it's ending up producing the correct value, when the defaults don't. The same is true of "Weighted Average" - again, it gives me the figure I want to see, but I don't know why. What is it averaging? What is it weighting with respect to what? How will the output of these two options differ, if at all, as I add more transactions in this manner?

My questions


  1.  Please provide a simple explanation of the "Average Cost" / "Weighted Average" price sources. Which is most appropriate for my scenario? Why do they give me the correct value, and will they continue to do so down the line? Is there a risk future transactions will throw off the "average" and create an incorrect P&L in the future, which I won't necessarily immediately spot?
  2.  Is there anything wrong with my process here?

The confusing thing for me is that everything is displayed as expected with the figures in the chart of accounts, but I'm getting thrown by the default report options and the labelling of the price sources. I believe this is just GnuCash's default multi-currency/trading handling conflicting with my requirements but need to make sure.

I just want to confirm if it is safe for me to record USD bills in this manner when my books are in GBP, and then how I should go about generating a P&L which shows the total expenses in GBP as actually paid out of the bank.

Kind regards,
James Walker
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Confused by report "Price Source" when recording bills in a foreign currency

Adrien Monteleone-2
James,

If I’m not mistaken, I recall a discussion in the last 6 months or so that average/weighted is ‘broken’. That it reports the correct figures may be a transient phenomenon as you fear. (I’ll admit, I don’t have the thread handy and it could be that ’Nearest in time’ is the one that is broken - it might be on the devel list, so look there if you don’t find it in the user archives.)

’Nearest in time’ should be the source you want because the report will look for the exchange rate closest to the date of the actual transaction it is including in the calculations. Since you have actual transactions using a particular exchange rate, it should pull those figures exactly.

That should be the default. (It is for me and I can’t seem to find a preference setting for it, short of saving a report configuration.)

‘Most Recent’ is definitely not what you want and would use the most current exchange rate. (perhaps useful for a Balance Sheet, not so much for a P&L)

I personally haven’t seen any issues with ’Nearest in time’.

Regards,
Adrien

> On Mar 26, 2019, at 11:36 AM, James Walker <[hidden email]> wrote:
>
> Hello fellow GnuCash users,
>
> Per the subject, I'm somewhat confused by the impact of the report "Price Source" when recording Bills received in a foreign currency.
>
> I'm using GnuCash for a UK business (all accounts in GBP) for very simple affairs.
>
> I use the services of a US business which invoices everything in USD.
>
> To account for this GnuCash, I've done the following:
>
>
>  *   Enabled Trading Accounts
>  *   Created an A/Payable account in USD ("Liabilities:Current Liabilities:Trade Creditors USD")
>  *   Created a USD expenses account for the company ("Expenses:US Company USD")
>  *   Created a Vendor for the company and set its payment currency to USD
>
> When I receive an invoice from the company, I do the following:
>
>
>  *   Create a Bill for the invoice, with the USD amount shown on the invoice, for the Vendor
>  *   Post the Bill to the Trade Creditors USD (Liability) / US Company USD (Expense) accounts
>
> When I pay the invoice, I do the following:
>
>
>  *   Credit Bank (GBP) and debit Trade Creditors USD (USD)
>  *   GnuCash prompts for the exchange rate; I input the GBP amount which was taken from the bank account for the payment
>
> An example of three invoices received:
>
>
>  1.  Invoice 1: $14.40
>  2.  Invoice 2: $14.40
>  3.  Invoice 3: $14.40
>
>  *   Balance of US Company USD (Expense): $43.20
>
> When credited from Bank (GBP), the amounts respectively are:
>
>
>  1.  £10.60
>  2.  £10.87
>  3.  £11.02
>
>  *   Total expenses paid from bank in GBP: £32.49 - this is the figure I expect to see on the P&L
>
> These figures match up with what is shown in the trading accounts: GBP £32.49, USD -$43.20.
>
> Generating P&L
>
> When I now go and generate a report (e.g. Profit & Loss), the default options seem to be showing the expenses at the most recent exchange rate.
>
> I see expenditure of e.g. £32.61, with a £0.12 currency trade. This means the P&L is wrong and the balance sheet won't balance, because the expenditure from Bank was actually only £32.49.
>
> It seems the multi-currency features in GnuCash are geared towards actual currency trading/asset tracking etc., not my simple "I've got a USD invoice" scenario.
>
> To me, the £32.61 figure seems blatantly incorrect; I haven't spent that much money. My understanding is the P&L needs to show £32.49, which is how much was actually taken from the Bank and debited to "Trading:Currency:GBP" from "Trading:Currency:USD".
>
> It seems GnuCash is forcing automatic currency conversions onto me when I don't need them, and breaking things in the process, but my understanding may be wrong, which is why I'm asking this.
>
> By changing "Price Source" in the report options to either "Average Cost" or "Weighted Average," I can get the P&L to show £32.49 as expected.
>
> However, I don't understand why this works, or what these two price sources are actually calculating. I've read the documentation and don't fully understand the language so could really benefit from some clarification.
>
> Although it is currently working, I need to understand why it is working and whether it will continue working, in order to have peace-of-mind that the P&L will continue to show the expected figure when using these options in the future, after adding further transactions.
>
> The documentation says this of "Average Cost":
>
>            The amount-weighted net average of all splits exchanging the commodity for another regardless of account. Gain/loss splits are included in the calculation.
>
> While this does produce the value I expect to see on the report (in my trivial experiments), I'm concerned about the "average" labelling as I don't understand what is actually being averaged or how it's ending up producing the correct value, when the defaults don't. The same is true of "Weighted Average" - again, it gives me the figure I want to see, but I don't know why. What is it averaging? What is it weighting with respect to what? How will the output of these two options differ, if at all, as I add more transactions in this manner?
>
> My questions
>
>
>  1.  Please provide a simple explanation of the "Average Cost" / "Weighted Average" price sources. Which is most appropriate for my scenario? Why do they give me the correct value, and will they continue to do so down the line? Is there a risk future transactions will throw off the "average" and create an incorrect P&L in the future, which I won't necessarily immediately spot?
>  2.  Is there anything wrong with my process here?
>
> The confusing thing for me is that everything is displayed as expected with the figures in the chart of accounts, but I'm getting thrown by the default report options and the labelling of the price sources. I believe this is just GnuCash's default multi-currency/trading handling conflicting with my requirements but need to make sure.
>
> I just want to confirm if it is safe for me to record USD bills in this manner when my books are in GBP, and then how I should go about generating a P&L which shows the total expenses in GBP as actually paid out of the bank.
>
> Kind regards,
> James Walker

_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Confused by report "Price Source" when recording bills in a foreign currency

Christian Kluge
In reply to this post by James Walker
Hi,

Am 26.03.2019 um 17:36 schrieb James Walker:


>   *   Created a USD expenses account for the company ("Expenses:US Company USD")


This is totally unnecessary. You should only make a conversion on the
expense side once and this is to GBP. Any rate fluctuation occuring with
the payment should be solved with correcting income and expense account.

I’ve said time and time again, that in my view only assets and
liabilities should be in foreign currencies.

Using your example with the spot rates of the 25th to 27th provided by
the Bank of England and assuming payment on the same day and leaving out
the trading accounts I would proceed as follows:

Invoice 1:
By Expenses:US Company                                   £10.91
  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40

Invoice 2:
By Expenses:US Company                                   £10.89
  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40

Invoice 3:
By Expenses:US Company                                   £10.92
  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40

Payment 1:
By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
  to Assets:Current Assets:Bank                                  £10.61
  to Income:Foreign Exchange Fluctuation                          £0.30

Payment 2:
By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
  to Assets:Current Assets:Bank                                  £10.87
  to Income:Foreign Exchange Fluctuation                          £0.02

Payment 3:
By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
by Expense:Foreign Exchange Fluctuation                   £0.10
  to Assets:Current Assets:Bank                                  £11.02



Kind regards


Christian Kluge

_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Confused by report "Price Source" when recording bills in a foreign currency

GnuCash - User mailing list
Christian,

While it may be, strictly speaking, unnecessary to create currency-specific expense accounts, it may yet be useful to do so. For example, if you maintain a bank account in one currency, and a second bank account in a different currency, you may wish to track ongoing expenses entirely in either currency.

Say I have a bank account in USD, and a second bank account in INR. While I am in India, I transfer cash from USD to INR using a bank transfer. For purposes of the example, assume that this exchange rate is USD 1 : INR 70. I enter the transfer into GnuCash, and it prompts me to settle the exchange rate, which I do. Now, several days later, I go to a grocery store in India, and use my local bank card to pay for the INR 2,000 charge. On this day, the exchange rate is 73 INR to 1 USD. It doesn’t seem to make sense that I take a transaction that is entirely in rupees and translate it back into dollars in Expenses:Groceries; at the time I am entering the groceries transaction, I may have no recollection of the exchange rate I used when purchasing the original rupees. This will become even more complicated if I have made multiple transfers from USD to INR, each at its own exchange rate. There is quite literally no way for me to track which rupees (at which exchange rate) got used when. I’ll note that the spot rates for such a purchase are guaranteed to be wrong, since the exchange rate at the time of the transaction is not, in fact, the exchange rate at which I obtained the INR in the first place. The value of my rupees was set when I made the initial transfer, not at subsequent points along the way.

Using trading accounts may manage this for me (I haven’t tried), but it seems so much more complicated than simply tracking the expenses in Expenses:Groceries:INR.

David

> On Mar 29, 2019, at 4:20 AM, Christian Kluge <[hidden email]> wrote:
>
> Hi,
>
> Am 26.03.2019 um 17:36 schrieb James Walker:
>
>
>>  *   Created a USD expenses account for the company ("Expenses:US Company USD")
>
>
> This is totally unnecessary. You should only make a conversion on the
> expense side once and this is to GBP. Any rate fluctuation occuring with
> the payment should be solved with correcting income and expense account.
>
> I’ve said time and time again, that in my view only assets and
> liabilities should be in foreign currencies.
>
> Using your example with the spot rates of the 25th to 27th provided by
> the Bank of England and assuming payment on the same day and leaving out
> the trading accounts I would proceed as follows:
>
> Invoice 1:
> By Expenses:US Company                                   £10.91
>  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40
>
> Invoice 2:
> By Expenses:US Company                                   £10.89
>  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40
>
> Invoice 3:
> By Expenses:US Company                                   £10.92
>  to Liabilities:Current Liabilities:Trade Creditors USD         $14.40
>
> Payment 1:
> By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
>  to Assets:Current Assets:Bank                                  £10.61
>  to Income:Foreign Exchange Fluctuation                          £0.30
>
> Payment 2:
> By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
>  to Assets:Current Assets:Bank                                  £10.87
>  to Income:Foreign Exchange Fluctuation                          £0.02
>
> Payment 3:
> By Liabilities:Current Liabilities:Trade Creditors USD   $14.40
> by Expense:Foreign Exchange Fluctuation                   £0.10
>  to Assets:Current Assets:Bank                                  £11.02
>
>
>
> Kind regards
>
>
> Christian Kluge
>
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Confused by report "Price Source" when recording bills in a foreign currency

GnuCash - User mailing list
In reply to this post by Adrien Monteleone-2
ISTR such a discussion as well; I also believe someone explained to me then why these reports are using the PriceDB at all. Now, I can’t recall what the explanation was, and I *still* don’t see why the Price DB is accessed to create a report for transactions in which the price has been already set.

David

> On Mar 28, 2019, at 1:35 PM, Adrien Monteleone <[hidden email]> wrote:
>
> James,
>
> If I’m not mistaken, I recall a discussion in the last 6 months or so that average/weighted is ‘broken’. That it reports the correct figures may be a transient phenomenon as you fear. (I’ll admit, I don’t have the thread handy and it could be that ’Nearest in time’ is the one that is broken - it might be on the devel list, so look there if you don’t find it in the user archives.)
>
> ’Nearest in time’ should be the source you want because the report will look for the exchange rate closest to the date of the actual transaction it is including in the calculations. Since you have actual transactions using a particular exchange rate, it should pull those figures exactly.
>
> That should be the default. (It is for me and I can’t seem to find a preference setting for it, short of saving a report configuration.)
>
> ‘Most Recent’ is definitely not what you want and would use the most current exchange rate. (perhaps useful for a Balance Sheet, not so much for a P&L)
>
> I personally haven’t seen any issues with ’Nearest in time’.
>
> Regards,
> Adrien
>
>> On Mar 26, 2019, at 11:36 AM, James Walker <[hidden email]> wrote:
>>
>> Hello fellow GnuCash users,
>>
>> Per the subject, I'm somewhat confused by the impact of the report "Price Source" when recording Bills received in a foreign currency.
>>
>> I'm using GnuCash for a UK business (all accounts in GBP) for very simple affairs.
>>
>> I use the services of a US business which invoices everything in USD.
>>
>> To account for this GnuCash, I've done the following:
>>
>>
>> *   Enabled Trading Accounts
>> *   Created an A/Payable account in USD ("Liabilities:Current Liabilities:Trade Creditors USD")
>> *   Created a USD expenses account for the company ("Expenses:US Company USD")
>> *   Created a Vendor for the company and set its payment currency to USD
>>
>> When I receive an invoice from the company, I do the following:
>>
>>
>> *   Create a Bill for the invoice, with the USD amount shown on the invoice, for the Vendor
>> *   Post the Bill to the Trade Creditors USD (Liability) / US Company USD (Expense) accounts
>>
>> When I pay the invoice, I do the following:
>>
>>
>> *   Credit Bank (GBP) and debit Trade Creditors USD (USD)
>> *   GnuCash prompts for the exchange rate; I input the GBP amount which was taken from the bank account for the payment
>>
>> An example of three invoices received:
>>
>>
>> 1.  Invoice 1: $14.40
>> 2.  Invoice 2: $14.40
>> 3.  Invoice 3: $14.40
>>
>> *   Balance of US Company USD (Expense): $43.20
>>
>> When credited from Bank (GBP), the amounts respectively are:
>>
>>
>> 1.  £10.60
>> 2.  £10.87
>> 3.  £11.02
>>
>> *   Total expenses paid from bank in GBP: £32.49 - this is the figure I expect to see on the P&L
>>
>> These figures match up with what is shown in the trading accounts: GBP £32.49, USD -$43.20.
>>
>> Generating P&L
>>
>> When I now go and generate a report (e.g. Profit & Loss), the default options seem to be showing the expenses at the most recent exchange rate.
>>
>> I see expenditure of e.g. £32.61, with a £0.12 currency trade. This means the P&L is wrong and the balance sheet won't balance, because the expenditure from Bank was actually only £32.49.
>>
>> It seems the multi-currency features in GnuCash are geared towards actual currency trading/asset tracking etc., not my simple "I've got a USD invoice" scenario.
>>
>> To me, the £32.61 figure seems blatantly incorrect; I haven't spent that much money. My understanding is the P&L needs to show £32.49, which is how much was actually taken from the Bank and debited to "Trading:Currency:GBP" from "Trading:Currency:USD".
>>
>> It seems GnuCash is forcing automatic currency conversions onto me when I don't need them, and breaking things in the process, but my understanding may be wrong, which is why I'm asking this.
>>
>> By changing "Price Source" in the report options to either "Average Cost" or "Weighted Average," I can get the P&L to show £32.49 as expected.
>>
>> However, I don't understand why this works, or what these two price sources are actually calculating. I've read the documentation and don't fully understand the language so could really benefit from some clarification.
>>
>> Although it is currently working, I need to understand why it is working and whether it will continue working, in order to have peace-of-mind that the P&L will continue to show the expected figure when using these options in the future, after adding further transactions.
>>
>> The documentation says this of "Average Cost":
>>
>>           The amount-weighted net average of all splits exchanging the commodity for another regardless of account. Gain/loss splits are included in the calculation.
>>
>> While this does produce the value I expect to see on the report (in my trivial experiments), I'm concerned about the "average" labelling as I don't understand what is actually being averaged or how it's ending up producing the correct value, when the defaults don't. The same is true of "Weighted Average" - again, it gives me the figure I want to see, but I don't know why. What is it averaging? What is it weighting with respect to what? How will the output of these two options differ, if at all, as I add more transactions in this manner?
>>
>> My questions
>>
>>
>> 1.  Please provide a simple explanation of the "Average Cost" / "Weighted Average" price sources. Which is most appropriate for my scenario? Why do they give me the correct value, and will they continue to do so down the line? Is there a risk future transactions will throw off the "average" and create an incorrect P&L in the future, which I won't necessarily immediately spot?
>> 2.  Is there anything wrong with my process here?
>>
>> The confusing thing for me is that everything is displayed as expected with the figures in the chart of accounts, but I'm getting thrown by the default report options and the labelling of the price sources. I believe this is just GnuCash's default multi-currency/trading handling conflicting with my requirements but need to make sure.
>>
>> I just want to confirm if it is safe for me to record USD bills in this manner when my books are in GBP, and then how I should go about generating a P&L which shows the total expenses in GBP as actually paid out of the bank.
>>
>> Kind regards,
>> James Walker
>
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.