Currency conversions based on price database -without re-inputting the '@ [price] [units]' each time

[siri...@gmail.com]

Hi all,

I'd like to resolve a little peeve with regards to pricing, if at all possible.
Let me now show you an example beancount file:

2017-01-01    open    Expenses:Widgets        CHF
2017-01-01    open    Expenses:Tools          CHF
2017-01-01    open    Liabilities:OwnersAccount

; official monthly exchange rate averages published by VAT office
2017-01-01    price    USD    1.0274 CHF
2017-02-01    price    USD    1.0247 CHF
2017-03-01    price    USD    1.0101 CHF

2017-04-05    *    "supplier"        "widgets purchased"
    Expenses:Widgets
    Liabilities:OwnersAccount    -100.00 USD    @ 1.0101 CHF

2017-04-05    *    "shop"            "tools bought"
    Expenses:Tools
    Liabilities:OwnersAccount    -20.00 USD    @ 1.0101 CHF

I'm working with Swiss VAT where the valid exchange rate into foreign currencies is fixed by the government VAT office.
They publish both daily and monthly averages, I choose to use monthly for simplicity.

First off, I keep having to input the value "@ 1.0101 CHF" for each transaction - because the expense accounts must be reported in CHF.
But the amount is already marked with an explicit price directive. Is there no way I can force a lookup?

Second, the monthly average price is not available the 1st of the month ... which means I need to mark entries with a wrong exchange rate and then come back and fix them when it is available.
So the lookup would need to happen when the beancount file is parsed, not when it is written.
I mention this because otherwise it would be quite straightforward to just write an UltiSnip to go look up the price and insert it when I'm inputting the transaction in the first place ... but it's not always available at that time!

Thirdly, I suppose I could just keep the transactions in USD and not convert to CHF ... but then when I need to do my balance sheet I need to show everything in CHF - and I have not been able to figure out how to get bean-query to force a price conversion based on the price database.

So, to recap:

a.) Isn't there any way to force a price database lookup? Maybe with a "@@@" syntax meaning "figure this out yourself from the price database"

or

b.) How do I force implicit price conversion when generating reports with bean-query?

or

c.) Am I looking at the problem completely wrong and should I approach it some other way?


Thanks so much for your help.

Sirio

[siri...@gmail.com]

OK, found that there was more info in a recent post which I had missed:

https://groups.google.com/forum/#!topic/beancount/97uaGyWz7DA

Based on that, I'm guessing option "b" below is the way to go: book the expenses in the native currency, and add a "CONVERT()" call in bean-query.

So that beancount file becomes:

Enter code here...2017-01-01    open    Expenses:Widgets    
2017-01-01    open    Expenses:Tools    

2017-01-01    open    Liabilities:OwnersAccount

; official monthly exchange rate averages published by VAT office
2017-01-01    price    USD    1.0274 CHF
2017-02-01    price    USD    1.0247 CHF
2017-03-01    price    USD    1.0101 CHF

2017-04-05    *    "supplier"    "widgets purchased"
    Expenses:Widgets
    Liabilities:OwnersAccount    -100.00 USD

2017-04-05    *    "shop"        "tools bought"
    Expenses:Tools
    Liabilities:OwnersAccount    -20.00 USD

And the query looks like this:

$ bean-query sample.beancount 'select date, position, narration, CONVERT(balance, "CHF")'
   date     position       narration     convert_bala
---------- ----------- ----------------- ------------
2017-04-05  100.00 USD widgets purchased 101.0100 CHF
2017-04-05 -100.00 USD widgets purchased            
2017-04-05   20.00 USD tools bought       20.2020 CHF
2017-04-05  -20.00 USD tools bought                  
$

It seems to work for me - I'm going to go ahead like this for now.
Any comments always welcome :P

thanks,

Sirio

[Jason Chu]

As long as you're fine recording all your assets and expenses in USD and converting only at reporting time to CHF, this is how you properly support the "figure out what the price was when I ask you" case.  Another option would be to write a plugin that finds this class of transaction and does the price conversion at the day's conversion rate (this would have the effect of calculating the @ part every time you load the beancount file).  I think doing the convert in bean-query is the right way to handle this, given the situation you've described, but the plugin would make the output consistent in fava.

--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/162d0ed7-552a-45bc-8b69-9ba943a1d916%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[yegle]

Is Liabilities:OwnersAccount a credit card? I'm not sure if that's the case in Swiss, but in US when you do a foreign transaction with a credit card, the exact exchange rate should be the one that show up in your statement.

You can also do the other way around by using plugin "beancount.plugins.implicit_prices", so that you don't need to maintain the price database.

--

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+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/082555f1-ed1a-4df2-8833-7274d79e10a3%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Yuchen Ying
https://about.me/yegle

[siri...@gmail.com]

Makes sense and thanks - I think I'm going to stick with conversion at query-time for now, should work out ok.

[Martin Blais]

A few additional thoughts:

- In general it's easier to diagnose problems if there are less side-effects. For example, transactions currently don't read any data from the price database, the only input not on the transaction itself is the prior contents of the inventories of the accounts being affected.

- To break that rule, one interesting use case could be to use the rate only when the values are underspecified, like this:

2017-04-05    *    "supplier"        "widgets purchased"

    Expenses:Widgets

    Liabilities:OwnersAccount    -100.00 USD    @ CHF

or like this:

2017-04-05    *    "supplier"        "widgets purchased"

    Expenses:Widgets            101.01 CHF

    Liabilities:OwnersAccount    USD    @  CHF

Instead of failing in those cases, Beancount could optionally attempt to fetch the closest price from the price database and fill in to see if it disambiguates the transaction balance.

However, I feel like it adds a bit too much magic, perhaps, but it could be an interesting experiment. 

I'm not sure if this happens very often in practice, maybe it's just a fanciful idea.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4d7120fe-2b43-4ac9-b76f-8fd3f1c832e2%40googlegroups.com.

[Sirio Balmelli]

I see what you’re saying.

To play devil’s advocate, I’d say that being forced to declare the transaction as it was (e.g. EUR or USD) and then handle conversion to CHF at the reporting (bean-query) stage is more “correct”.

What I mean specifically is that the transaction didn’t actually have a conversion attached to it in real life - it was a transaction in EUR or USD - but tax reporting requires it to be shown in CHF (and converted at an officially published rate) … so in the final analysis I’m not sure implicit conversion would even be the preferable option were it available.

And thank you for chiming in on this - I really appreciate it :)

best,

Sirio

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/VoLL8XScP_U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhOUB9c-mpNW3DxTMjAqf-ipeLOPkogfjZKZjDeWxEx0Dw%40mail.gmail.com.