Mapping ledger's fixated prices/costs to beancount

52 views
Skip to first unread message

Martin Michlmayr

unread,
Jan 19, 2019, 7:07:43 PM1/19/19
to bean...@googlegroups.com, ledge...@googlegroups.com, jwie...@gmail.com
ledger has the concept of fixated prices/costs which tells it not to
revalue it according to the current pricedb but to always use the
specified cost/price; see
https://www.ledger-cli.org/3.0/doc/ledger3.html#Fixated-prices-and-costs

I am trying to figure out how to handle these in ledger2beancount.
Obviously, beancount doesn't have a similar concept, so we need to strip
the fixated prices/costs and document how to interact with beancount to
get the expected behaviour.

If something is a fixated cost, it's easy. This:

2012-04-10 My Broker
Assets:Brokerage 10 AAPL {=$50.00}
Assets:Brokerage:Cash $750.00

becomes:

2012-04-10 txn "My Broker"
Assets:Brokerage 10 AAPL {50.00 USD}
Assets:Brokerage:Cash 750.00 USD

and you can run: SUM(COST(position)) to get the value. This will always
return the "fixated" value (i.e. the cost).

I thought that if you add:

plugin "beancount.plugins.implicit_prices"

you could alternatively do SUM(CONVERT(position, 'USD', date)), i.e. if
we convert everything on its original date and sum it up, we should get
the same value.

However, this doesn't work in practice, at least not in all cases.
I have an example where the two do not match:

beancount> SELECT SUM(CONVERT(position, 'USD', date)) WHERE date < 2018-03-01 AND account ~ '^Assets:Accounts-Receivable'
sum_convert_position
--------------------
508739.717262620 USD
beancount> SELECT SUM(COST(position)) WHERE date < 2018-03-01 AND account ~ '^Assets:Accounts-Receivable'
sum_cost_position
-------------------
507589.87445810 USD
beancount>

Martin, is this expected or a bug? Maybe this happens when you have
multiple price entries on the same day and beancount only has one price
per day instead of taking the price from the transaction?

And then my other question is what to do about fixated prices, e.g.:

2012-04-10 My Broker
Assets:Brokerage 10 AAPL @ =$50.00
Assets:Brokerage:Cash $750.00

ledger considers a fixated cost and a fixated price the same and this
transaction will actually lead to 10 APPL {=$50}. ledger2beancount has
some logic to figure out when a price should be converted to a cost
rather than a price. I'm wondering if in the case of a fixated price,
we should *always* convert to a cost so the "fixated" value is kept
around.

Any opinions on this?

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

Martin Blais

unread,
Feb 18, 2019, 10:10:47 PM2/18/19
to Beancount, ledger-cli
On Sat, Jan 19, 2019 at 7:07 PM Martin Michlmayr <t...@cyrius.com> wrote:
ledger has the concept of fixated prices/costs which tells it not to
revalue it according to the current pricedb but to always use the
specified cost/price; see
https://www.ledger-cli.org/3.0/doc/ledger3.html#Fixated-prices-and-costs

I am trying to figure out how to handle these in ledger2beancount.
Obviously, beancount doesn't have a similar concept, so we need to strip
the fixated prices/costs and document how to interact with beancount to
get the expected behaviour.

If something is a fixated cost, it's easy.  This:

2012-04-10 My Broker
    Assets:Brokerage            10 AAPL {=$50.00}
    Assets:Brokerage:Cash       $750.00

becomes:

2012-04-10 txn "My Broker"
    Assets:Brokerage            10 AAPL {50.00 USD}
    Assets:Brokerage:Cash       750.00 USD
we convert everything on its original date and sum it up, we should get
the same value.

No.
The implicit_prices plugin only inserts a Price directive corresponding to the @ <price> annotation on postings.
That's all it does.
By default these price annotations aren't used, except by the sellgains plugin, and they're available for your custom scripts to access on the Posting object.
The Price directives (optionally inserted) are used to fill in the price database.


However, this doesn't work in practice, at least not in all cases.

It would fail if you have multiple postings with different @ <price> annotations on the same day, as the price database has a daily resolution, and there's a single price available for that day.

 
I have an example where the two do not match:

beancount> SELECT SUM(CONVERT(position, 'USD', date)) WHERE date < 2018
Note that this transaction doesn't balance, it is missing an income posting.
(That's not related to the problem you describe, but worthy of mention.)


and you can run: SUM(COST(position)) to get the value.  This will always
return the "fixated" value (i.e. the cost).

I thought that if you add:

plugin "beancount.plugins.implicit_prices"

you could alternatively do SUM(CONVERT(position, 'USD', date)), i.e. if-03-01 AND account ~ '^Assets:Accounts-Receivable'
sum_convert_position
--------------------
508739.717262620 USD
beancount> SELECT SUM(COST(position)) WHERE date < 2018-03-01 AND account ~ '^Assets:Accounts-Receivable'
 sum_cost_position
-------------------
507589.87445810 USD
beancount>

Martin, is this expected or a bug? Maybe this happens when you have
multiple price entries on the same day and beancount only has one price
per day instead of taking the price from the transaction?

I believe that is correct and not-a-bug.
Calling CONVERT() does not use the postings' price annotation to make the conversion.
It is called from here in your SQL statement:
that function:
If you inspect that function, you will find that it looks for a price annotation (the hasattr() call) to find out the currency, if present, but it uses the price map to make the conversion, without access to the price annotation from the posting.

Now, I traced the call and it indeed receives a Position instance, not a Posting.
If you're not familiar with the codebase, a Position is contained within a Posting. 
A Posting is like a Position which has an account, a price annotation, and some metadata.
In fact, a number of functions accept either one of those.
(If you're familiar with OO, imagine that a Position is a (concrete) base class, and a Posting derived from it, though in this case it's not strictly true, it's all done via duck-typing, that is, Position just happens to have a strict subset of the attributes of Posting.)

If we drill down a bit, we will find that the SQL accessor for the "position" field synthesizes a Position object from the posting:
There is no corresponding accessor for Posting.

Now, it would be unwise to change the semantics of "position" here and potentially mess with everyone's numbers so I won't do that.
But I /could/ add a "posting" accessor very easily, which would allow you to choose.
(Documenting this would be no fun...)

Uh-oh... an hour later...

I started to quickly prototype this and an hour after I'm realizing it's more complicated than I though to add it, it's not a simple matter of adding accessors for posting. The behavior of the conversion in all possible cases is non-trivial and would require a lot of testing work -- I already sank an hour into this, this is probably worth a good afternoon's if not an entire day's work. Can't do it quickly right now, but I think it's doable. May need some serious revisiting of the overall semantics.)

Also, when CONVERT() converts through the cost basis (that is, units to cost to then foreign price), that's entirely done through the price database too, so potentially you're not getting the right value for cost, if you acquired multiple lots at different costs.

;; Showcasing that conversion via cost uses the price db.

plugin "beancount.plugins.auto_accounts"
plugin "beancount.plugins.sellgains"
plugin "beancount.plugins.implicit_prices"

2012-04-10 txn "My Broker"
  Assets:Brokerage             5 AAPL {50.00 USD}
  Assets:Brokerage:Cash      -250.00 USD

2012-04-10 txn "My Broker"
  Assets:Brokerage             5 AAPL {55.00 USD}
  Assets:Brokerage:Cash      -275.00 USD

2018-01-01 query "detail" "
SELECT
  date,
  account,
  position,
  COST(position),
  CONVERT(position, 'USD', date),
  CONVERT(COST(position), 'USD', date)
WHERE
  date < 2018-03-01 AND
  account ~ '^Assets:Brokerage$'
"


Output: 

bean-query twocostssameday.beancount run detail
   date        account           position      cost_posit convert_po convert_co
---------- ---------------- ------------------ ---------- ---------- ----------
2012-04-10 Assets:Brokerage 5 AAPL {50.00 USD} 250.00 USD 275.00 USD 250.00 USD
2012-04-10 Assets:Brokerage 5 AAPL {55.00 USD} 275.00 USD 275.00 USD 275.00 USD

Notice how the middle column has the same cost basis here, because it was automatically converted via CONVERT() (you didn't ask for the cost basis when you said just CONVERT()).


Overall, there are many cases to handle and it's a fair amount of complexity to handle it all "as automatically as possible."
I think I would restrict the semantics in a reimplementation.



And then my other question is what to do about fixated prices, e.g.:

2012-04-10 My Broker
    Assets:Brokerage            10 AAPL @ =$50.00
    Assets:Brokerage:Cash       $750.00

ledger considers a fixated cost and a fixated price the same and this
transaction will actually lead to 10 APPL {=$50}.  ledger2beancount has
some logic to figure out when a price should be converted to a cost
rather than a price.  I'm wondering if in the case of a fixated price,
we should *always* convert to a cost so the "fixated" value is kept
around.

I have no idea.

 

Any opinions on this?

I'm annoyed at the discrepancies above.
I'm thinking about removing the conversion via cost done implicitly in CONVERT(), so that if you have cost basis, you need to do COST() explicitly to convert via it.
This would also make the change easier to implement.

(So little time... Bedtime now, back to research for four days... it all goes so fast)



--
Martin Michlmayr
https://www.cyrius.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.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20190120000737.diia64cksupwrmok%40jirafa.cyrius.com.
For more options, visit https://groups.google.com/d/optout.

Martin Michlmayr

unread,
Feb 20, 2019, 6:51:48 AM2/20/19
to Beancount
* Martin Blais <bl...@furius.ca> [2019-02-18 22:10]:
> > Assets:Brokerage 10 AAPL {50.00 USD}
> >
> The implicit_prices plugin only inserts a Price directive corresponding to
> the @ <price> annotation on postings.
...
> The Price directives (optionally inserted) are used to fill in the price
> database.

Are you saying the Price directives generated by the implicit_prices
plugin are not added to the price database? Why is that?

> Uh-oh... an hour later...

Thanks for your detailed analysis and for looking into this. I think
I'll have to re-read your email a few times before I fully understand.

But as for ledger2beancount, it sounds like the best way forward is to
convert fixated prices always to a cost in beancount and then to use
SUM(COST(position)). This should always lead to the correct result.

Martin Blais

unread,
Feb 20, 2019, 8:27:42 AM2/20/19
to Beancount
On Wed, Feb 20, 2019 at 6:51 AM Martin Michlmayr <t...@cyrius.com> wrote:
* Martin Blais <bl...@furius.ca> [2019-02-18 22:10]:
> >     Assets:Brokerage            10 AAPL {50.00 USD}
> >
> The implicit_prices plugin only inserts a Price directive corresponding to
> the @ <price> annotation on postings.
...
> The Price directives (optionally inserted) are used to fill in the price
> database.

Are you saying the Price directives generated by the implicit_prices
plugin are not added to the price database?  Why is that?

I'm not saying that.
The price db can only handle a single price point per (base, quote) pair per day.
 

> Uh-oh... an hour later...

Thanks for your detailed analysis and for looking into this.  I think
I'll have to re-read your email a few times before I fully understand.

But as for ledger2beancount, it sounds like the best way forward is to
convert fixated prices always to a cost in beancount and then to use
SUM(COST(position)).  This should always lead to the correct result.

--
Martin Michlmayr
https://www.cyrius.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.

Martin Michlmayr

unread,
Feb 20, 2019, 11:15:42 AM2/20/19
to bean...@googlegroups.com
* Martin Blais <bl...@furius.ca> [2019-02-20 08:27]:
> > Are you saying the Price directives generated by the implicit_prices
> > plugin are not added to the price database? Why is that?
>
> I'm not saying that.

Ah, ok, I misunderstood.

> The price db can only handle a single price point per (base, quote) pair
> per day.

Yeah, I understand that.
Reply all
Reply to author
Forward
0 new messages