Confused about bean-query value() and convert() functions

219 views
Skip to first unread message

Jason Chu

unread,
Mar 27, 2017, 10:52:44 AM3/27/17
to Beancount
I learned recently that bean-query has a value() function that returns market value of a position (contrary to the Beancount - Query Language docs).

I'm trying to write a query that sums all the positions of all my retirement accounts and converts it to one currency.  I have CAD, USD, and commodities that convert to either of those two currencies.

My previous query was something like this:

select convert(convert(sum(position), 'CAD'), 'USD') where account ~ 'Assets:(Awesome|Regexp)'

Which handled the case where I only had commodity -> CAD and commodity -> USD prices.  Turns out that dropping the inner CAD conversion is no longer necessary (when did that change?)

Either way, I assumed that convert used the cost basis and didn't worry at all about market value.

If I write a query like this:

select value(position) where account ~ 'Assets:(Awesome|Regexp)'

I get what I assume is the market value of all of these inventories.  If I wrap it in a convert(..., 'USD'), I end up with the same result as the previous queries.  Is convert doing a market value conversion?

If I convert from CAD to USD by hand, the numbers are reasonably close together, so I think that's what's happening.

Either way, this seems to have changed from previous times I've run the queries, so any help would be appreciated.

Martin Blais

unread,
Mar 28, 2017, 11:09:25 PM3/28/17
to Beancount
For a while this was really messy and IIRC you (or someone else?) requested a cleanup of those functions so that they could be reused somewhere else. I don't recall changing the semantics but I did go through the entire codebase and refactored the very many instances of homegrown and slightly varying conversions to a single "beancount.core.convert" module, which is now the canonical place these things occur. This really was getting out of hand and frankly driving me nuts as well, I had never properly spent time to clean this up. Now it's clean. So that's the history.

The current (and hopefully neverchanging) status of this is that the shell's VALUE() functions map to convert.get_value(), while the shell's CONVERT() functions map to convert.convert_*().

Here are the mappings:

Here are the shell function implementations:

The conversion functions are here:
https://bitbucket.org/blais/beancount/src/b26118a59cf2be4b697893e144ca6319fad009bf/src/python/beancount/core/convert.py?at=default&fileviewer=file-view-default#convert.py-100

The main different between VALUE and CONVERT is that VALUE makes a conversion into the known cost currency (of a position with a non-null cost field), while CONVERT accepts any destination currency. For CONVERT, if the destination currency != its cost currency and no direct conversion can be found in the price database, it attempts to convert to cost first, then to convert that cost value to the destination currency. See logic here:

Maybe this is easier to follow with an example.

Say, you have a position of 100 IMO {40.00 CAD} and the current price of IMO is 41.00 USD.

The value of the position should be 4000.00 CAD.

The conversion of the position to CAD should also be 4000.00 CAD.
The conversion of the position to USD should fail.

Say the price database contains a price for IMO in USD, that is IMO = 30.76 USD.
The conversion would succeed, calculating 100 IMO * 30.76 USD = 3076 USD

Say the price database INSTEAD contained only an exchange rate, that is a price for USD = 1.3 CAD
The conversion would make the attempt above, fail, then convert to CAD = 4000.00 CAD, and finally convert CAD to USD calculating 4000 / 1.3 = 3076 USD.

If both the rates are present, the direct conversion would be preferred.

(I hesitated when I made that decision. Should I keep it as simple as possible and fail many times a conversion would seem to be naturally possible, or make it a little bit smarter and attempt to convert through the cost for convenience? I went for the latter. Somebody else suggested going even further and attempting all the implied rates through any other currency in the price database but I decided against that, because it adds ambiguity and an implementation isn't straightforward. It's nice to keep things simple. One normally would maintain price conversions between their operating currencies so this works well IMO.)

I hope this helps,





--
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/CAFFHUgtCyRT-bB64e5W-PZgccwJtf8hnvf%2B8ufKnJVoSBhoyAw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Jason Chu

unread,
Mar 29, 2017, 10:42:40 AM3/29/17
to bean...@googlegroups.com
On Tue, Mar 28, 2017 at 8:09 PM Martin Blais <bl...@furius.ca> wrote:
For a while this was really messy and IIRC you (or someone else?) requested a cleanup of those functions so that they could be reused somewhere else. I don't recall changing the semantics but I did go through the entire codebase and refactored the very many instances of homegrown and slightly varying conversions to a single "beancount.core.convert" module, which is now the canonical place these things occur. This really was getting out of hand and frankly driving me nuts as well, I had never properly spent time to clean this up. Now it's clean. So that's the history.

It totally was me who asked for this change.  I hadn't realized how many other problems it would have solved for me! ;)
 

The current (and hopefully neverchanging) status of this is that the shell's VALUE() functions map to convert.get_value(), while the shell's CONVERT() functions map to convert.convert_*().

Here are the mappings:

Here are the shell function implementations:

The conversion functions are here:
https://bitbucket.org/blais/beancount/src/b26118a59cf2be4b697893e144ca6319fad009bf/src/python/beancount/core/convert.py?at=default&fileviewer=file-view-default#convert.py-100

The main different between VALUE and CONVERT is that VALUE makes a conversion into the known cost currency (of a position with a non-null cost field), while CONVERT accepts any destination currency. For CONVERT, if the destination currency != its cost currency and no direct conversion can be found in the price database, it attempts to convert to cost first, then to convert that cost value to the destination currency. See logic here:


Maybe this is easier to follow with an example.

Say, you have a position of 100 IMO {40.00 CAD} and the current price of IMO is 41.00 USD.

The value of the position should be 4000.00 CAD.

The conversion of the position to CAD should also be 4000.00 CAD.
The conversion of the position to USD should fail.

Say the price database contains a price for IMO in USD, that is IMO = 30.76 USD.
The conversion would succeed, calculating 100 IMO * 30.76 USD = 3076 USD

Say the price database INSTEAD contained only an exchange rate, that is a price for USD = 1.3 CAD
The conversion would make the attempt above, fail, then convert to CAD = 4000.00 CAD, and finally convert CAD to USD calculating 4000 / 1.3 = 3076 USD.

If both the rates are present, the direct conversion would be preferred.

(I hesitated when I made that decision. Should I keep it as simple as possible and fail many times a conversion would seem to be naturally possible, or make it a little bit smarter and attempt to convert through the cost for convenience? I went for the latter. Somebody else suggested going even further and attempting all the implied rates through any other currency in the price database but I decided against that, because it adds ambiguity and an implementation isn't straightforward. It's nice to keep things simple. One normally would maintain price conversions between their operating currencies so this works well IMO.)

I would have made the same decision you did.  It simplifies the types of queries that I've been trying to do (take all my assets and convert them into a single currency at market value) without having to make the query too complicated.  That being said, I don't have multiple operating currencies without direct currency conversions.
 

I hope this helps,

It helps so much!
 





On Mon, Mar 27, 2017 at 10:52 AM, Jason Chu <xen...@gmail.com> wrote:
I learned recently that bean-query has a value() function that returns market value of a position (contrary to the Beancount - Query Language docs).

I'm trying to write a query that sums all the positions of all my retirement accounts and converts it to one currency.  I have CAD, USD, and commodities that convert to either of those two currencies.

My previous query was something like this:

select convert(convert(sum(position), 'CAD'), 'USD') where account ~ 'Assets:(Awesome|Regexp)'

Which handled the case where I only had commodity -> CAD and commodity -> USD prices.  Turns out that dropping the inner CAD conversion is no longer necessary (when did that change?)

Either way, I assumed that convert used the cost basis and didn't worry at all about market value.

If I write a query like this:

select value(position) where account ~ 'Assets:(Awesome|Regexp)'

I get what I assume is the market value of all of these inventories.  If I wrap it in a convert(..., 'USD'), I end up with the same result as the previous queries.  Is convert doing a market value conversion?

If I convert from CAD to USD by hand, the numbers are reasonably close together, so I think that's what's happening.

Either way, this seems to have changed from previous times I've run the queries, so any help would be appreciated.

--
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.

--
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.
Reply all
Reply to author
Forward
0 new messages