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
On Monday, December 28, 2020 at 8:08:24 AM UTC-8 Peter wrote: > 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 > > [email protected] schrieb am Montag, 28. Dezember 2020 um 11:17:22 UTC+1: > >> Hi Peter, I'm curious: were these helpful at all? >> >> >> On Wednesday, December 23, 2020 at 3:53:13 AM UTC-8 [email protected] >> wrote: >> >>> 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 >>> >>> On Wednesday, December 23, 2020 at 1:17:45 AM UTC-8 Peter wrote: >>> >>>> 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 >>>> >>> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/920628fd-4088-4205-85e5-5af3fc321a4fn%40googlegroups.com.
