Report account balance in one currency

[Alexander Baier]

Hello everybody,

My main currency is EUR, but sometimes I buy something in USD. When I do that the payment provider (i.e. PayPal) automatically converts between USD and EUR and pulls the EUR amount from my checking account.

This is how I track this in beancount:

2021-01-01 open Expenses:Electronics

2021-01-01 open Assets:Checking EUR

2021-01-01 * "Buy something in USD"

  Expenses:Electronics                               20.00 USD @@ 18.00 EUR

  Assets:Checking                                   -18.00 EUR

I would now like to get a report with these columns:

- date

- narration

- posting (in the currency it was made)

- posting in EUR

- running total in EUR

I tried to play around with bean-query, but the convert function did not work as I expected. It did not convert to EUR unless I provided a specific date. But I don't want to convert at just one specific date. I want to convert using the same rate that was also used in the transaction.

What is the way to do this?

[Altynbek Isabekov]

Hi Alexander,

this works for me:

bean-query MyFinances.beancount\
                 "SELECT  account,\
                          narration,\
                          date,\
                          SUM(position) AS amount_foreign_currency,\
                          SUM(convert(position, 'EUR', date)) AS amount_euro,\
                          last(convert(balance, 'EUR', date)) as balance_at_transaction_date_euro\
                 WHERE\
                          account ~ 'Income'\
                 ORDER BY date, account, narration, amount_foreign_currency, amount_euro, balance_at_transaction_date_euro"

--
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/250c7d44-4f50-4aba-a529-732a34083199n%40googlegroups.com.

--

Sincerely yours, Altynbek

[Alexander Baier]

Hi Altynbek,

thank you very much for your quick response!

Your query works, once I enable the plugin "beancount.plugins.implicit_prices. I already had the same query, but was missing that the date attribute could be used as a parameter to the convert function!

Kind regards,

Alexander

[Altynbek Isabekov]

Your query works, once I enable the plugin "beancount.plugins.implicit_prices. I already had the same query, but was missing that the date attribute could be used as a parameter to the convert function!

Not only that, if you had some amount of, let's say, US dollars and you had spent some of this money before any foreign currency (US dollar in this case) purchase operations you need to specify the conversion rate explicitly, e.g.:

2015-12-31 price EUR 1.08 USD

This is an "opening balance" equity account and since US dollars there came from "nowhere", Beancount does not know anything about the exchange rate before your first purchase of US dollars.

--

Sincerely yours, Altynbek