How to query currently available lots?

[Peter]

How can I query the currently remaining lots with their specific information (units, date, cost) of an account? Assuming the following beancount file:

option "booking_method" "FIFO"

option "operating_currency" "EUR"

option "inferred_tolerance_default" "*:0.001"

option "inferred_tolerance_default" "EUR:0.001"

option "inferred_tolerance_default" "BTC:0.00000001"

2019-01-01 open Equity:Opening-Account

2019-01-01 open Assets:Cash:Coinbase-Pro

2019-01-01 open Assets:Crypto:BTC:Coinbase-Pro

2019-01-01 open Income:Trading:EUR

2019-10-01 * "" "Opening"

Assets:Cash:Coinbase-Pro 500 EUR

Equity:Opening-Account -500 EUR

2019-10-03 * "" "Buy first lot"

Assets:Cash:Coinbase-Pro -100 EUR

Assets:Crypto:BTC:Coinbase-Pro 0.097087379 BTC {1,030 EUR} @ 1,030 EUR

2020-02-12 * "" "Buy second lot"

Assets:Cash:Coinbase-Pro -200 EUR

Assets:Crypto:BTC:Coinbase-Pro 0.05 BTC {4,000 EUR} @ 4,000 EUR

2020-05-28 * "" "Buy third lot"

Assets:Cash:Coinbase-Pro -200 EUR

Assets:Crypto:BTC:Coinbase-Pro 0.183486239 BTC {1,090 EUR} @ 1,090 EUR

2020-09-10 * "" "Sell"

Assets:Cash:Coinbase-Pro 312.5 EUR

Assets:Crypto:BTC:Coinbase-Pro -0.125 BTC {EUR} @ 2,500 EUR

Income:Trading:EUR

Running bean-report holdings gives me:

$ bean-report Krypto.bean holdings

Account                          Units  Currency  Cost Currency  Average Cost  Price  Book Value  Market Value

------------------------------  ------  --------  -------------  ------------  -----  ----------  ------------

Assets:Cash:Coinbase-Pro        312.50       EUR            EUR                           312.50        312.50

Assets:Crypto:BTC:Coinbase-Pro    0.02       BTC            EUR      4,000.00              88.35              

Assets:Crypto:BTC:Coinbase-Pro    0.18       BTC            EUR      1,090.00             200.00              

------------------------------  ------  --------  -------------  ------------  -----  ----------  ------------

Which is good, except the date is missing. The  same goes for bean-query using BALANCES

$ bean-query Krypto.bean 'BALANCES'

           account                     sum_position         

------------------------------ -----------------------------

Assets:Cash:Coinbase-Pro        312.5         EUR           

Assets:Crypto:BTC:Coinbase-Pro    0.022087379 BTC {4000 EUR},    0.183486239 BTC {1090 EUR}

Equity:Opening-Account         -500           EUR           

Income:Trading:EUR             -100.8         EUR 

Is missing the date, too. I tried using bean-query with SELECT:

$ bean-query Krypto.bean 'SELECT account, position, date WHERE account ~ "BTC"'

           account                      position              date   

------------------------------ --------------------------- ----------

Assets:Crypto:BTC:Coinbase-Pro  0.097087379 BTC {1030 EUR} 2019-10-03

Assets:Crypto:BTC:Coinbase-Pro  0.05        BTC {4000 EUR} 2020-02-12

Assets:Crypto:BTC:Coinbase-Pro  0.183486239 BTC {1090 EUR} 2020-05-28

Assets:Crypto:BTC:Coinbase-Pro -0.097087379 BTC {1030 EUR} 2020-09-10

Assets:Crypto:BTC:Coinbase-Pro -0.027912621 BTC {4000 EUR} 2020-09-10

That has all the information I want, but every transaction is listed, so I don‘t have just the currently available lots.

How can I query my currently available lots? The desired could be something like:

$ bean-magic

           account                      position              date   

------------------------------ --------------------------- ----------

Assets:Crypto:BTC:Coinbase-Pro  0.022087379 BTC {4000 EUR} 2020-02-12

Assets:Crypto:BTC:Coinbase-Pro  0.183486239 BTC {1090 EUR} 2020-05-28

The consumed 2019-10-03 and the partly consumed 2020-02-12 lot isn't rendered. Is that possible?

Regards

[redst...@gmail.com]

Here are a few ways:

1)

SELECT account, units(sum(position)) as units, cost_number as cost, cost_currency, cost(sum(position)) as book_value, cost_date as acquisition_date WHERE currency = 'BTC' ORDER BY units

That displays all lots ever bought. However, the units/book_value columns will be empty for the ones that were sold. There may be a way to filter those out that I'm not aware of or can't think of right now.

2) Depending on the context, bean-doctor is very helpful with listing the active lots

3) A small script that uses the beancount library is usually very helpful in extracting/filtering/cleaning data that can't easily be be done via bean-query

[redst...@gmail.com]

Hi Peter, I'm curious: were these helpful at all?

[Peter]

Hi,

yes. That was what I was looking for. Thanks! Just need to figure out, how to (if possible) sort the lots with 0 units at top and the remaining lots by date.

Regards

[redst...@gmail.com]

Glad to hear. You can't do that in bean-query, but you can easily do that with a custom script. Here's an example:

https://github.com/redstreet/fava_investor/blob/master/fava_investor/modules/cashdrag/libcashdrag.py