bean-query tax loss harvesting

175 views
Skip to first unread message

Justus Pendleton

unread,
Jan 3, 2018, 4:18:55 AM1/3/18
to Beancount
I'm having a hard time understanding how to write the bean-query I want; hopefully sharper minds can help me out.

I want to see if I have any tax loss harvesting opportunities. This just means I want a list of all lots whose current price is below their purchase price. But I'm having a hard time understanding how to get a list of just the lots I still currently have (i.e. ones I have not already sold).

2017-01-01 open Assets:A
2017-01-01 open Assets:B
2017-01-01 open Income:CapitalGains
2017-12-27 price VWIUX 14.11000 USD

2017-05-16 * "Buy VWIUX"
  Assets:A 1 VWIUX {14.09 USD}
  Assets:B

2017-08-08 * "Buy VWIUX"
  Assets:A 1 VWIUX {14.23 USD} @ 18,000 USD
  Assets:B

2017-09-08 * "Buy VWIUX"
  Assets:A 1 VWIUX {14.32 USD}
  Assets:B

2017-12-27 * "Sell VWIUX"
  Assets:A -3 VWIUX {*} @ 14.11 USD
  Assets:B

In this case, I've sold all of the lots. So there is nothing left to tax loss harvest. However, when I run this query

2017-01-01 query "TLH" "SELECT currency, number, cost(position), value(position)
  WHERE
    account ~ 'Assets:'
    and number > 0
    and cost(position) > value(position)
  ORDER BY currency"

It still returns the last 2 lots, which isn't what I expect to happen.

curre n cost_posi value_pos
----- - --------- ---------
VWIUX 1 14.23 USD 14.11 USD
VWIUX 1 14.32 USD 14.11 USD

So....where am I going wrong?

Cheers,
Justus

Martin Blais

unread,
Jan 3, 2018, 8:12:09 AM1/3/18
to Beancount
Remove the * that's not what it's for
Should work
The * is intended for average cost booking

In any case that should not fail either so there's a bug



--
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/894cc5d3-4dac-405b-9342-7dc9965d9826%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Justus Pendleton

unread,
Jan 3, 2018, 10:49:22 PM1/3/18
to Beancount
On Wednesday, January 3, 2018 at 8:12:09 PM UTC+7, Martin Blais wrote:
> In any case that should not fail either so there's a bug

With a bit more playing around, I think I understand what beancount is doing but I'm still not clear on how to achieve what I want. Using the same sample beanfile as in my original post, if I just do SELECT currency, number, cost(position), value(position) then beancount tells me

curre num cost_posit value_posi
----- --- ---------- ----------
VWIUX 1 14.09 USD 14.11 USD
USD -14 -14.09 USD -14.09 USD
VWIUX 1 14.23 USD 14.11 USD
USD -14 -14.23 USD -14.23 USD
VWIUX 1 14.32 USD 14.11 USD
USD -14 -14.32 USD -14.32 USD
VWIUX -1 -14.09 USD -14.11 USD
VWIUX -1 -14.23 USD -14.11 USD
VWIUX -1 -14.32 USD -14.11 USD
USD 42 42.64 USD 42.64 USD

So I guess the key/problem is that the "number" isn't being treated the way I expected. Taking the $14.09 lot as an example, I expected it to have a single entry with a number of 0. But it actually has 2 entries, one with a number of +1 and one with a number of -1.

Now that I see what's happening, I guess I understand why. bean-query is showing me postings, not lots. I guess I need to build up an inventory on my own? But I'm not sure that bean-query is flexible enough to let me do what I want here and only look at lots that still exist and are under water. So I'm back to being stumped about how to proceed. Do I just need to drop into python to get what I want?

Cheers,
Justus

Martin Blais

unread,
Jan 4, 2018, 2:16:16 AM1/4/18
to Beancount
On Wed, Jan 3, 2018 at 10:49 PM, Justus Pendleton <just...@gmail.com> wrote:
On Wednesday, January 3, 2018 at 8:12:09 PM UTC+7, Martin Blais wrote:
> In any case that should not fail either so there's a bug

With a bit more playing around, I think I understand what beancount is doing but I'm still not clear on how to achieve what I want. Using the same sample beanfile as in my original post, if I just do SELECT currency, number, cost(position), value(position) then beancount tells me

curre num cost_posit value_posi
----- --- ---------- ----------
VWIUX 1 14.09 USD 14.11 USD
USD -14 -14.09 USD -14.09 USD
VWIUX 1 14.23 USD 14.11 USD
USD -14 -14.23 USD -14.23 USD
VWIUX 1 14.32 USD 14.11 USD
USD -14 -14.32 USD -14.32 USD
VWIUX -1 -14.09 USD -14.11 USD
VWIUX -1 -14.23 USD -14.11 USD
VWIUX -1 -14.32 USD -14.11 USD
USD 42 42.64 USD 42.64 USD

So I guess the key/problem is that the "number" isn't being treated the way I expected. Taking the $14.09 lot as an example, I expected it to have a single entry with a number of 0. But it actually has 2 entries, one with a number of +1 and one with a number of -1.

Now that I see what's happening, I guess I understand why. bean-query is showing me postings, not lots.

Yes

 
I guess I need to build up an inventory on my own?

You can use sum(position)


 
But I'm not sure that bean-query is flexible enough to let me do what I want here and only look at lots that still exist and are under water. So I'm back to being stumped about how to proceed. Do I just need to drop into python to get what I want?

I don't think you do, use select sum(position) and you should be on your way, if you want to drop to Python accumulate the Position instances to an Inventory and print those contents

(Sorry for the lack of detail, I'm dealing with a newborn situation.)


 

Cheers,
Justus

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

Jason Chu

unread,
Jan 4, 2018, 2:23:07 AM1/4/18
to bean...@googlegroups.com
It looks like the Flattening Inventories in Beancount - Query Language (https://docs.google.com/document/d/1s0GOZMcrKKCLlP29MD7kHO4L88evrwWdIO0p4EwRBE0/edit#heading=h.agpdf4fbvn2) covers the use case you want. Unfortunately it's a future feature.

I wonder how hard it would be to implement...



 

Cheers,
Justus
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.

--
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/CAK21%2BhMa5a2NiEVxN2fDputhUX4_M_PcUzUXp1G0YRAgDEd8-w%40mail.gmail.com.

Martin Blais

unread,
Jan 4, 2018, 2:31:07 AM1/4/18
to Beancount
Actually what I often do when I need a list of lots like that is to use bean-doctor context and then I cut-n-paste from the "before" state





 

Cheers,
Justus
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.

--
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/CAK21%2BhMa5a2NiEVxN2fDputhUX4_M_PcUzUXp1G0YRAgDEd8-w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--
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/CAFFHUgvZ-T2X6qiq4P7CjGYHeZf79GQoOemkiqzhswGN_hf_qw%40mail.gmail.com.

Justus Pendleton

unread,
Jan 4, 2018, 5:43:34 AM1/4/18
to Beancount
On Thursday, January 4, 2018 at 2:16:16 PM UTC+7, Martin Blais wrote: 
But I'm not sure that bean-query is flexible enough to let me do what I want here and only look at lots that still exist and are under water. So I'm back to being stumped about how to proceed. Do I just need to drop into python to get what I want?

I don't think you do, use select sum(position) and you should be on your way, if you want to drop to Python accumulate the Position instances to an Inventory and print those contents

For the record, I gave up trying to do it in bean-query since my brain apparently wasn't able to wrap around what I needed to. Doing this in python was straightforward:


import beancount.loader
import beancount.core
from beancount.reports import holdings_reports

import argparse
parser = argparse.ArgumentParser(description='List lots that have negative value and are available for tax loss harvesting.')
parser.add_argument('bean')
parser.add_argument('-i', '--ignore-account', action='append')
args = parser.parse_args()

TEMPLATE = '{currency}\t{number:,}\t{total_loss:,}\t\t({account})'

entries, errors, options = beancount.loader.load_file(args.bean)
holdings, price_map = holdings_reports.get_assets_holdings(entries, options)
print('\tunits\tloss\t\t\taccount')
print('--------------------------------------------------')

for h in holdings:
    if h.account in args.ignore_account: continue
    if h.market_value < h.book_value:
        print(TEMPLATE.format(total_loss = (h.market_value - h.book_value), **h._asdict())) 

Martin Blais

unread,
Jan 4, 2018, 12:33:20 PM1/4/18
to Beancount
That works.
A few notes, real quick:

- What you needed to do is just accumulate the postings. That's what the aggregate sum(posting) would do on the SELECT call.  This accumulates in an Inventory object, using the rules of Inventories (e.g. for reducing lots against each other).

- What Jason referred to is the fact that if an inventory contains multiple positions, they get rendered horizontally by default. Adding FLATTEN at the end of your SQL query will render one line for each position. That works now.

- The "holdings" code you're using works, but is to be obsoleted eventually. I had a special "holdings" package for computing such aggregates that was developed in days before the aggregation and booking was improved (an older, flawed method). Simply aggregating in an Inventory is now the simpler thing to do, and I'd like to eventually remove all that holdings code and replace it with simpler code from just beancount.core. (This is all just part of evolving software and the process.)

Glad it worked and the API was simple to wrangle,






--
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/46aed62b-7571-475e-b666-ac6f75d4417d%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages