Odd behaviour with currency conversion

[Dmitry Kudryavtsev]

I recently switch to beancount, and I'm trying to set it up for both my personal finances, and for my business.

Consider the following transaction:

```

2024-08-01 txn "Some Service" ""
    invoice: "xxx"
    Assets:Wise:EUR                           -9.32 EUR @@ 10 USD
    Expenses:MyComp:Operating:Software       10 USD
    Expenses:Misc:USD

```

I purchased some service from a US company for 10 USD, but my Wise bank account was charged with 9.32 EUR (The Expenses:Misc:USD is there because apparently this transaction, as is, does not balance and is missing 0.00000000000001 USD, or close to that, but that's a different issue).

I then have a report

```

SELECT date, account, payee, any_meta('invoice') as invoice, round(number(cost(position)), 2) as position, currency(cost(position)) as currency, convert(position, 'EUR', date) as position_eur, convert(balance, 'EUR') as balance_eur where account ~ 'Income:MyComp|Expenses:MyComp' and year = 2024

```

This is a query that gives me all income and expenses for 2024, as well as converting each of them to EUR for easier reporting to the tax authorities. (The reason I split the position to number and currency, is because beancount reports it as 10.000000000000 USD, which is annoying).

This reports the correct values, 10 USD converted to 9.32 EUR, and the balances add up nicely, so it would be perfect to submit to the tax authorities for example. It has no price attached to it. However, if I flip the transaction like this:

```

2024-08-01 txn "Some Service" ""
    invoice: "xxx"
    Assets:Wise:EUR                           -9.32 EUR
    Expenses:MyComp:Operating:Software       10 USD @@ 9.32 EUR

```

Firstly it balances perfectly, so need for the Misc expense. But again, that's not the issue. Ideally, if I run the report now, I should get the same value, but I don't! Instead, I get 10 USD as the value for the position, but the converted cost of the position is now 8.46 EUR, and even though it has a price of 0.93 EUR attached to it.

I tried to play with all kinds of parameters in the query, like with date or without date, etc, but I still get 8.46 EUR for that transaction as converted amount, hence the converted balance is also incorrect.

I don't mind recording the transaction as in the first example, but what I'm worried about is that the reports might turn out to be incorrect. I randomly caught this transaction, but who knows how many there are/will be.

Am I misunderstanding something?

[Martin Michlmayr]

* Dmitry Kudryavtsev <ko.d...@gmail.com> [2024-08-27 09:09]:

> invoice: "xxx"
> Assets:Wise:EUR -9.32 EUR @@ 10 USD
> Expenses:MyComp:Operating:Software 10 USD
> Expenses:Misc:USD
> ```
> I purchased some service from a US company for 10 USD, but my Wise bank
> account was charged with 9.32 EUR (The Expenses:Misc:USD is there because
> apparently this transaction, as is, does not balance and is missing
> 0.00000000000001 USD, or close to that, but that's a different issue).

I think this is solved with:

option "inferred_tolerance_default" "EUR:0.004"
option "inferred_tolerance_default" "USD:0.004"

--
Martin Michlmayr
https://www.cyrius.com/

[Chary Chary]

On Wednesday, August 28, 2024 at 9:06:04 AM UTC+2 DK wrote:

I recently switch to beancount, and I'm trying to set it up for both my personal finances, and for my business.

Consider the following transaction:

```

2024-08-01 txn "Some Service" ""
    invoice: "xxx"
    Assets:Wise:EUR                           -9.32 EUR @@ 10 USD
    Expenses:MyComp:Operating:Software       10 USD
    Expenses:Misc:USD

```

I purchased some service from a US company for 10 USD, but my Wise bank account was charged with 9.32 EUR (The Expenses:Misc:USD is there because apparently this transaction, as is, does not balance and is missing 0.00000000000001 USD, or close to that, but that's a different issue).

I think Martin considers this to be a bug, but you can work around it by putting  10.00 instead of 10

2024-08-01 txn "Some Service" ""
    invoice: "xxx"

    Assets:Wise:EUR                           -9.32 EUR @@ 10.00 USD
    Expenses:MyComp:Operating:Software       10.00 USD

check:

https://colab.research.google.com/drive/1laDSOEdxMC2b9yo3zX8e-gxyomM5Zuqm?usp=sharing

 

```

 Ideally, if I run the report now, I should get the same value, but I don't! Instead, I get 10 USD as the value for the position, but the converted cost of the position is now 8.46 EUR, and even though it has a price of 0.93 EUR attached to it.

This is because the  beanquery convert function is not using the implicit convertion rate of the transaction (10 USD @@ 9.32 EUR), but the price, derived from the latest price directive, which you have staying elsewhere.

It kind of makes sense, because  when converting from one currency to another you want to use an official rate, not the one used by a specific merchant for a specific purchase.

you can use the implicit_prices plugin though.

"""This plugin synthesizes Price directives for all Postings with a price or
directive or if it is an augmenting posting, has a cost directive.
"""

Even with the   implicit_prices plugin however, you still can get a situation, that on a specific date you may have different prices, but the convert function will use only one of it per day.

To hunt such problems, there is  a unique_prices.py plugin

In addition all of them are combined in the pedantic.py plugin

[DK]

Thanks, the 10.00 fixed the rounding issue.

In regard to the main issue. So I already use implicit_prices, but I wasn't aware of unique_prices. It indeed helped me identify some bugs caused by the import from GnuCash (or should I say, by GnuCash itself), where I'd have transaction like

```

2024-01-01 txn "Something"

  Account1 10 EUR @@ 10 USD

  Account2 10 EUR

```

So the conversion is not needed. I removed these, and now flipping the initial transaction order, works correctly. I can't live with unique_prices on all the time, due to the fact that in real life, market prices are not unique per day (in fact, I had two ESPP batches that I sold on the same day, but one was sold at 313 USD while the other at 313.5 USD, hence unique_prices was complaining about it). But it, nevertheless, helpful to turn it on once in a while in order to identify bugs like this.

This brings me, however, to another question. I conduct business in various currencies, but I have to file my tax reports in EUR. For some transaction, like the one above, I have explicit conversion rate. I bought a service for 10 USD, but my bank took 9.32 EUR based on their conversion rate. Hence, I'm obligated to report a loss of 9.32 EUR. I don't want the convert function to go and look at the closes rate on that date, I would like to have a function that converts the cost based on the price attached to a transaction, if there is one. If there is none, then I need to use ECB rate for that day in my reports. It would have been nice to have a convert function like that. On the other hand, maybe it's not the intended purpose of beancount, and instead I should get the original values to a python script and process them myself in the desired output for the tax authorities.

Thanks for help!

[Martin Blais]

I think it would be reasonable to create a function like that and insert it into the environment of a custom beanquery you build for yourself, or perhaps just make a script.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/32d17999-95e8-4613-ac33-370fc20dc41fn%40googlegroups.com.

[Chary Chary]

I think I understand what you need technically, but I don't understand why you need it practically.

If you buy something in EU, using EU-issued card, you will get a statement in EUR and in this case  why do you bother  at all about the fact, that declaration price of the merchant was 10 USD ?  

What is the value of this information about 10 USD?

In another words why do you write like this, 

2024-08-01 txn "Some Service" ""

    invoice: "xxx"

    Assets:Wise:EUR                           -9.32 EUR @@ 10.00 USD

    Expenses:MyComp:Operating:Software       10.00 USD

instead of like this:

2024-08-01 txn "Some Service" ""

    invoice: "xxx"

    Assets:Wise:EUR                         -9.32 EUR

    Expenses:MyComp:Operating:Software

note: "Declaration price was 10 USD"

Another thing is that if you would buy it in USD using USD credit card, then you would get a statement in USD, but then beanquery would just do exactly what you need, provided you put ECB exchange rate in your ledger

[DK]

Two reasons, actually. The first one is that I get an invoice in USD. And the second one is that I am pedantic, and want to keep a correct record in my ledger.

[Chary Chary]

On Saturday, August 31, 2024 at 9:58:01 PM UTC+2 DK wrote:

Two reasons, actually. The first one is that I get an invoice in USD. And the second one is that I am pedantic, and want to keep a correct record in my ledger.

Understood. But I still think the syntax, proposed by me covers your reasons

2024-08-01 txn "Some Service" ""

    invoice: "xxx"

    Assets:Wise:EUR                         -9.32 EUR

    Expenses:MyComp:Operating:Software

note: "Declaration price was 10 USD"

But I also invite you to consider the following 2 issue, which you may have to consider, when making business in multi-currency environment

Specifically unrealized gains and losses, which come from changing exchange rates as well as gains (but mostly losses), coming from exchange of funds with the rate, different from the one of ECB

Consider the following situation

plugin "beancount.plugins.auto_accounts"

option "operating_currency" "EUR"

2024-01-01 price EUR 1.0 USD ; ECB exchange rate

; Here you have 2000 EUR in you balance sheet report

2024-01-01 * "Opening balance "

    Assets:US:Bank      1000 USD

    Assets:EU:Bank      1000 EUR

    Equity:Opening-Balances

; When the EUR / USD exchange rate changes, your net worth (when measured in EUR) drops by 500 EUR.

; Do you need to reflect this somehow in your books? If so, how?

2024-01-02 price EUR 2 USD

; Now you decide to move all your USD to EUR, but the bank offers an exchange rate, different from the ECB rate, which

; decreases your net worth by another 100 EUR.

; Do you need to reflect this somehow in your books? If so, how?

2024-01-03 * "Move all USD from the US bank to the EU bank"

    Assets:EU:Bank  400 EUR @2.5 USD ; <==This is 2.5, instead of the official 2.0

    Assets:US:Bank -1000 USD

; Now you have 1400 EUR in your balance sheet report without you having had any income or expenses.

; Will you have do explain this somehow?

[DK]

Hey, thanks for the reply.

I'm not an account and or a tax advisor, so everything I say is not legal advice.

Currency exchange rates are not an issue, from what I remember. The tax declaration happens once a year, and for any non EUR currency you either use the exchange rate of the transaction (hence the importance of recording 10.00 USD with the converted amount) for foreign currency transactions (this means that the foreign amount is settled in EUR at the transaction time, hence no special treatment is needed, but questions might arise how I got 9.32 EUR if there is no matching invoice); or you use the ECB exchange rate of the last day of the fiscal year for foreign currencies that are held in a bank account (so if I started my year with 100 EUR and 100 USD, later converted all my EUR to USD, by the end of the year I need to submit EUR report, hence I'll use the rate from ECB to estimate the EUR value of my USD money).

This should answer the first part of your question. As for keeping books, I prefer to stay as documented as possible. So if I convert all my USD into EUR, I would record a transaction and will use the conversion rate offered by my bank/financial institution that will carry the conversion. This should answer questions like "how come you started with 100 EUR and 100 USD, and ended up with 300 EUR without any profit/loss?" Look at the books, and you will see that there was a conversion of 100 USD to 200 EUR at the rate offered by my bank.

Nevertheless, I don't see a reason to convert currencies from an EU business standpoint, unless I do frequent, and big purchases in foreign currency.

[Chary Chary]

On Monday, September 2, 2024 at 2:02:17 PM UTC+2 DK wrote:

So if I convert all my USD into EUR, I would record a transaction and will use the conversion rate offered by my bank/financial institution that will carry the conversion. This should answer questions like "how come you started with 100 EUR and 100 USD, and ended up with 300 EUR without any profit/loss?" Look at the books, and you will see that there was a conversion of 100 USD to 200 EUR at the rate offered by my bank.

For business purposes, do you actually need to be able to explain how you got from net worth A to net worth B ?

Please keep in mind, that in general there is no report (yet), which will explain this if case of multi-currency situation and conversion between currencies (check this )

Check discussion

https://groups.google.com/g/beancount/c/MQQZ24tTgWE/m/4GlqR7_QAAAJ

 

[Dmitry Kudryavtsev]

I guess that if you get audited, then yes, you will have to explain how you got the numbers you’ve reported.

Thanks for the link, I’ve noticed the same thing but in a different context. I have a report that shows me the sum of all Income and all expenses for current year, as well as 5 years back on a year basis. The sum is converted to EUR at the date of the transaction and the numbers are different.

Out of curiosity, how do you generate reports? I’ve seen you have variables in your reports. I have a bunch of queries saved in my beancount file, which I run with ‘bean-query .run’ but I’m looking for a way to generate more dynamic reports, potentially with variables, and even being able to manipulate the result of the reports (for example one report I miss is a monthly PnL with summary line, but it’s not possible to do it in one query without intermediate variables).

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/Od_D_pbBLkg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f22e7fcb-3d67-4a2c-97ab-6a1c12255b32n%40googlegroups.com.

[Chary Chary]

On Monday, September 2, 2024 at 10:45:36 PM UTC+2 DK wrote:

 

Out of curiosity, how do you generate reports? I’ve seen you have variables in your reports. I have a bunch of queries saved in my beancount file, which I run with ‘bean-query .run’ but I’m looking for a way to generate more dynamic reports, potentially with variables, and even being able to manipulate the result of the reports (for example one report I miss is a monthly PnL with summary line, but it’s not possible to do it in one query without intermediate variables).

I use beancount from within jupyter notebook, where I do initial data extract using beanquery, but further manipulations (e.g. adding total value) in pandas.  This gives one exactly the flexibility you are looking for

Here is example:

https://colab.research.google.com/drive/1laDSOEdxMC2b9yo3zX8e-gxyomM5Zuqm?usp=sharing

Here is more information

https://github.com/isabekov/beancount-multiperiod-reports

[Chary Chary]

On Monday, September 2, 2024 at 10:45:36 PM UTC+2 DK wrote:

I guess that if you get audited, then yes, you will have to explain how you got the numbers you’ve reported.

Thanks for the link, I’ve noticed the same thing but in a different context. I have a report that shows me the sum of all Income and all expenses for current year, as well as 5 years back on a year basis. The sum is converted to EUR at the date of the transaction and the numbers are different.

I am working on the tool now (and have it actually working as prototype), which  allows to exactly understand where the difference in net worth came from. The tool works by inserting unrealized gains transactions for every price change. 

 

[Chary Chary]

On Tuesday, September 3, 2024 at 10:39:11 AM UTC+2 Chary Chary wrote:

On Monday, September 2, 2024 at 10:45:36 PM UTC+2 DK wrote:

 

Out of curiosity, how do you generate reports? I’ve seen you have variables in your reports. I have a bunch of queries saved in my beancount file, which I run with ‘bean-query .run’ but I’m looking for a way to generate more dynamic reports, potentially with variables, and even being able to manipulate the result of the reports (for example one report I miss is a monthly PnL with summary line, but it’s not possible to do it in one query without intermediate variables).

I use beancount from within jupyter notebook, where I do initial data extract using beanquery, but further manipulations (e.g. adding total value) in pandas.  This gives one exactly the flexibility you are looking for

Here is example:

https://colab.research.google.com/drive/1laDSOEdxMC2b9yo3zX8e-gxyomM5Zuqm?usp=sharing

Here is more information

https://github.com/isabekov/beancount-multiperiod-reports

This is actually better example

https://colab.research.google.com/drive/1DNmh9Jlos_LHIN2joGzs3-EKcKt6qzZr?usp=sharing