Converting from GnuCash - How to Handle Investment Sales?

[Aaron Lindsay]

Hello!

I have a scary accounting question for Halloween...

I have 8 years of GnuCash data I'd like to convert to beancount. The problem is that, as far as I can tell, GnuCash doesn't track cost basis for trades. Because of that, I'm having difficulty replicating the suggested approach from https://beancount.github.io/docs/trading_with_beancount.html. I am using https://github.com/falsifian/gnucash-to-beancount and can 'successfully' convert my data to beancount, but instead of specifying the cost basis, purchases of investments end up looking like:

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Trading:CURRENCY:USD 200 USD

  Assets:Current-Assets:Brokerage -200 USD

  Assets:Trading:NYSE:SPY -1 SPY @ 200 USD

Sales are in essentially the same form, just in reverse. In this way, my Assets:Trading:CURRENCY:USD account ends up being a weird combination of the total cost basis of all my current investments and the gains or losses from previous investments. Obviously I have enough data here to calculate the cost basis for *purchases* of investments, so that ought to be do-able with some modification of the gnucash-to-beancount scripts, but I'm not sure how best to handle the sales. I've been reading the beancount documentation and have found the "FIFO" booking method - is that pretty much my only option at this point? Are there any gotchas to using it in my case?

As a bonus - if I end up using the "FIFO" booking method, is it possible to get beancount re-write my .beancount file with the cost basis it calculated internally for the sales? My goal there would be to make the implicit cost basis on sales explicit - and allow me to use STRICT moving forward without having to manually fix up 8 years of transactions.

Thanks for any pointers!

-Aaron

[Martin Blais]

On Sat, Oct 31, 2020 at 4:09 PM Aaron Lindsay <acli...@gmail.com> wrote:

Hello!

I have a scary accounting question for Halloween...

oooOOOoOOoOoOoOOoO

 

I have 8 years of GnuCash data I'd like to convert to beancount. The problem is that, as far as I can tell, GnuCash doesn't track cost basis for trades. Because of that, I'm having difficulty replicating the suggested approach from https://beancount.github.io/docs/trading_with_beancount.html. I am using https://github.com/falsifian/gnucash-to-beancount and can 'successfully' convert my data to beancount, but instead of specifying the cost basis, purchases of investments end up looking like:

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Trading:CURRENCY:USD 200 USD

  Assets:Current-Assets:Brokerage -200 USD

  Assets:Trading:NYSE:SPY -1 SPY @ 200 USD

Urg...

This should be an improvement (despite the absence of cost basis):

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Current-Assets:Brokerage -200 USD

Also, I'd create a cash sub-account for the cash portin.

 

Sales are in essentially the same form, just in reverse. In this way, my Assets:Trading:CURRENCY:USD account ends up being a weird combination

It's the aggregate P/L (without discounting commissions).

 

of the total cost basis of all my current investments and the gains or losses from previous investments. Obviously I have enough data here to calculate the cost basis for *purchases* of investments,

Yes, something like this: 

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY {200 USD}

  Assets:Current-Assets:Brokerage:Cash               -200 USD

 

so that ought to be do-able with some modification of the gnucash-to-beancount scripts, but I'm not sure how best to handle the sales. I've been reading the beancount documentation and have found the "FIFO" booking method - is that pretty much my only option at this point? Are there any gotchas to using it in my case?

You could use that, but it's not necessarily going to match the P/L you declared in the past for each period (though the total P/L should be right). Personally I would gather my history and then try to manually match each of the sales, but I'm a maniac. bean-doctor context can help, by printing the available lots just before a transaction.

How many sales do you have?

As a bonus - if I end up using the "FIFO" booking method, is it possible to get beancount re-write my .beancount file with the cost basis it calculated internally for the sales? 

My goal there would be to make the implicit cost basis on sales explicit - and allow me to use STRICT moving forward without having to manually fix up 8 years of transactions.

Yes. Just "bean-query print" your ledger and it will print out everything explicitly.

I hope this helps, 

 

Thanks for any pointers!

-Aaron

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/beancount/01dd4feb-9b9a-423b-bbee-d7a9d2613035n%40googlegroups.com.

[Aaron Lindsay]

On Saturday, October 31, 2020 at 4:38:28 PM UTC-4 bl...@furius.ca wrote:

On Sat, Oct 31, 2020 at 4:09 PM Aaron Lindsay <acli...@gmail.com> wrote:

Hello!

I have a scary accounting question for Halloween...

oooOOOoOOoOoOoOOoO

 

I have 8 years of GnuCash data I'd like to convert to beancount. The problem is that, as far as I can tell, GnuCash doesn't track cost basis for trades. Because of that, I'm having difficulty replicating the suggested approach from https://beancount.github.io/docs/trading_with_beancount.html. I am using https://github.com/falsifian/gnucash-to-beancount and can 'successfully' convert my data to beancount, but instead of specifying the cost basis, purchases of investments end up looking like:

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Trading:CURRENCY:USD 200 USD

  Assets:Current-Assets:Brokerage -200 USD

  Assets:Trading:NYSE:SPY -1 SPY @ 200 USD

Urg...

This should be an improvement (despite the absence of cost basis):

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Current-Assets:Brokerage -200 USD

Also, I'd create a cash sub-account for the cash portin.

Sales are in essentially the same form, just in reverse. In this way, my Assets:Trading:CURRENCY:USD account ends up being a weird combination

It's the aggregate P/L (without discounting commissions).

Isn't it only the aggregate P/L *after* I sell all positions? Otherwise it also includes the cash used to buy my currently-held investments. Or maybe I'm confused about the definition of "aggregate P/L"?

 

of the total cost basis of all my current investments and the gains or losses from previous investments. Obviously I have enough data here to calculate the cost basis for *purchases* of investments,

Yes, something like this: 

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY {200 USD}

  Assets:Current-Assets:Brokerage:Cash               -200 USD

 

Yup, that's exactly what I was thinking of doing. And I think I've got gnucash-to-beancount doing that for me now.

so that ought to be do-able with some modification of the gnucash-to-beancount scripts, but I'm not sure how best to handle the sales. I've been reading the beancount documentation and have found the "FIFO" booking method - is that pretty much my only option at this point? Are there any gotchas to using it in my case?

You could use that, but it's not necessarily going to match the P/L you declared in the past for each period (though the total P/L should be right). Personally I would gather my history and then try to manually match each of the sales, but I'm a maniac. bean-doctor context can help, by printing the available lots just before a transaction.

How many sales do you have?

To be honest, I haven't been tracking my P/L much, if at all. Though I'm even a little unclear on how GnuCash would derive P/L given the 'Trading' account setup it uses - how are you assuming GnuCash calculates P/L when you say it's not going to match beancount's?

Assuming they're all recorded in a consistent form (which I'm not sure I believe), I have around 200 sales. I think I will probably end up letting bean-count do its best using "FIFO" and go back behind it and check up on the ones that I can find data for.

 

As a bonus - if I end up using the "FIFO" booking method, is it possible to get beancount re-write my .beancount file with the cost basis it calculated internally for the sales? 

My goal there would be to make the implicit cost basis on sales explicit - and allow me to use STRICT moving forward without having to manually fix up 8 years of transactions.

Yes. Just "bean-query print" your ledger and it will print out everything explicitly.

I hope this helps, 

It does, thanks!

-Aaron

[Martin Blais]

On Sun, Nov 1, 2020 at 8:40 PM Aaron Lindsay <acli...@gmail.com> wrote:

On Saturday, October 31, 2020 at 4:38:28 PM UTC-4 bl...@furius.ca wrote:

On Sat, Oct 31, 2020 at 4:09 PM Aaron Lindsay <acli...@gmail.com> wrote:

Hello!

I have a scary accounting question for Halloween...

oooOOOoOOoOoOoOOoO

 

I have 8 years of GnuCash data I'd like to convert to beancount. The problem is that, as far as I can tell, GnuCash doesn't track cost basis for trades. Because of that, I'm having difficulty replicating the suggested approach from https://beancount.github.io/docs/trading_with_beancount.html. I am using https://github.com/falsifian/gnucash-to-beancount and can 'successfully' convert my data to beancount, but instead of specifying the cost basis, purchases of investments end up looking like:

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Trading:CURRENCY:USD 200 USD

  Assets:Current-Assets:Brokerage -200 USD

  Assets:Trading:NYSE:SPY -1 SPY @ 200 USD

Urg...

This should be an improvement (despite the absence of cost basis):

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Current-Assets:Brokerage -200 USD

Also, I'd create a cash sub-account for the cash portin.

Sales are in essentially the same form, just in reverse. In this way, my Assets:Trading:CURRENCY:USD account ends up being a weird combination

It's the aggregate P/L (without discounting commissions).

Isn't it only the aggregate P/L *after* I sell all positions? Otherwise it also includes the cash used to buy my currently-held investments. Or maybe I'm confused about the definition of "aggregate P/L"?

Well you're inserting a corresponding cash amount on your buys too. That original transaction is essentially applying the currency trading accounts method but for your shares.

Basically, what that is going to reflect, is the total amount of cash against all positions outstanding. You could compute your P/L by subtracting that from the sum total of the position values. So technically no, it's not just your P/L, it's all the cash you used to acquire the positions + realized P/L from already sold ones, which piles up in that account.

It doesn't matter; it's overly complicated. Just use the cost basis notation.

 

of the total cost basis of all my current investments and the gains or losses from previous investments. Obviously I have enough data here to calculate the cost basis for *purchases* of investments,

Yes, something like this: 

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY {200 USD}

  Assets:Current-Assets:Brokerage:Cash               -200 USD

 

Yup, that's exactly what I was thinking of doing. And I think I've got gnucash-to-beancount doing that for me now.

+1

 

so that ought to be do-able with some modification of the gnucash-to-beancount scripts, but I'm not sure how best to handle the sales. I've been reading the beancount documentation and have found the "FIFO" booking method - is that pretty much my only option at this point? Are there any gotchas to using it in my case?

You could use that, but it's not necessarily going to match the P/L you declared in the past for each period (though the total P/L should be right). Personally I would gather my history and then try to manually match each of the sales, but I'm a maniac. bean-doctor context can help, by printing the available lots just before a transaction.

How many sales do you have?

To be honest, I haven't been tracking my P/L much, if at all. Though I'm even a little unclear on how GnuCash would derive P/L given the 'Trading' account setup it uses - how are you assuming GnuCash calculates P/L when you say it's not going to match beancount's?

It depends if it stores the realized gains in another account. What you wrote originally above is a way to keep track of cash vs. positions in order to be able to derive P/L, e..g, I spent $1000 to buy 2 shares, then $600 to buy one more, now I have -$1600 and 3 shares, and so if I want to compute P/L, say the price per share is $650, the P/L is then 3x$650 - $1600. When you realize gains, do you keep the profits in that account? If so, the P/L is going to be that over all time. If instead the realized P/L gets moved to another account (I would suggest to do that, to be able to break it apart), then it's the P/L of only the open positions. 

(BTW, this is precisely how you keep track of open positions in a trading strategy with a very large number of transactions; this is the correct way, as it is compressed (small in size) and the sums have interpretable meaning.)

Assuming they're all recorded in a consistent form (which I'm not sure I believe), I have around 200 sales. I think I will probably end up letting bean-count do its best using "FIFO" and go back behind it and check up on the ones that I can find data for.

Yeah, it's borderline.

You could also try to automate it, by analyzing the different instruments. Your 200 trades are probably distributed over many stocks, so it's possible it might be pretty easy to match them up manually because the number of possible lots to match against might be small, with the help of a little script.

As a bonus - if I end up using the "FIFO" booking method, is it possible to get beancount re-write my .beancount file with the cost basis it calculated internally for the sales? 

My goal there would be to make the implicit cost basis on sales explicit - and allow me to use STRICT moving forward without having to manually fix up 8 years of transactions.

Yes. Just "bean-query print" your ledger and it will print out everything explicitly.

I hope this helps, 

It does, thanks!

-Aaron

Thanks for any pointers!

-Aaron

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/beancount/01dd4feb-9b9a-423b-bbee-d7a9d2613035n%40googlegroups.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 view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f3419875-8c5a-482b-b74e-5c2b157216a6n%40googlegroups.com.

[Aaron Lindsay]

On Monday, November 2, 2020 at 12:22:27 AM UTC-5 bl...@furius.ca wrote:

On Sun, Nov 1, 2020 at 8:40 PM Aaron Lindsay <acli...@gmail.com> wrote:

On Saturday, October 31, 2020 at 4:38:28 PM UTC-4 bl...@furius.ca wrote:

On Sat, Oct 31, 2020 at 4:09 PM Aaron Lindsay <acli...@gmail.com> wrote:

2014-10-31 * "BUY 1 SPY @ 200"

  Assets:Current-Assets:Brokerage:SPY 1 SPY @ 200 USD

  Assets:Trading:CURRENCY:USD 200 USD

  Assets:Current-Assets:Brokerage -200 USD

  Assets:Trading:NYSE:SPY -1 SPY @ 200 USD

 

Sales are in essentially the same form, just in reverse. In this way, my Assets:Trading:CURRENCY:USD account ends up being a weird combination

It's the aggregate P/L (without discounting commissions).

Isn't it only the aggregate P/L *after* I sell all positions? Otherwise it also includes the cash used to buy my currently-held investments. Or maybe I'm confused about the definition of "aggregate P/L"?

Well you're inserting a corresponding cash amount on your buys too. That original transaction is essentially applying the currency trading accounts method but for your shares.

Basically, what that is going to reflect, is the total amount of cash against all positions outstanding. You could compute your P/L by subtracting that from the sum total of the position values. So technically no, it's not just your P/L, it's all the cash you used to acquire the positions + realized P/L from already sold ones, which piles up in that account.

Okay, thanks, that matches my understanding.

 

To be honest, I haven't been tracking my P/L much, if at all. Though I'm even a little unclear on how GnuCash would derive P/L given the 'Trading' account setup it uses - how are you assuming GnuCash calculates P/L when you say it's not going to match beancount's?

It depends if it stores the realized gains in another account. What you wrote originally above is a way to keep track of cash vs. positions in order to be able to derive P/L, e..g, I spent $1000 to buy 2 shares, then $600 to buy one more, now I have -$1600 and 3 shares, and so if I want to compute P/L, say the price per share is $650, the P/L is then 3x$650 - $1600. When you realize gains, do you keep the profits in that account? If so, the P/L is going to be that over all time. If instead the realized P/L gets moved to another account (I would suggest to do that, to be able to break it apart), then it's the P/L of only the open positions.

Let me make this concrete. This is what it would look like in GnuCash (albeit in beancount syntax) if I sold the entire position from my purchase example above:

2014-11-01 * "SELL 1 SPY @ 205"

  Assets:Current-Assets:Brokerage:SPY -1 SPY @ 205 USD

  Assets:Trading:CURRENCY:USD -205 USD

  Assets:Current-Assets:Brokerage 205 USD

  Assets:Trading:NYSE:SPY 1 SPY @ 205 USD

I take this to mean that GnuCash's approach (at least the way I was using it) is to "keep the profits in that account" in your wording. Correct?

And what you're suggesting to translate this to in beancount is:

2014-11-01 * "SELL 1 SPY @ 205"

  Assets:Current-Assets:Brokerage:SPY -1 SPY {200 USD} @ 205 USD

  Assets:Current-Assets:Brokerage:Cash 205 USD

  Income:Brokerage:PnL

 

(BTW, this is precisely how you keep track of open positions in a trading strategy with a very large number of transactions; this is the correct way, as it is compressed (small in size) and the sums have interpretable meaning.)

Interesting. I'm assuming that they must additionally track the cost basis for sales in some way, though, right?

Assuming they're all recorded in a consistent form (which I'm not sure I believe), I have around 200 sales. I think I will probably end up letting bean-count do its best using "FIFO" and go back behind it and check up on the ones that I can find data for.

Yeah, it's borderline.

You could also try to automate it, by analyzing the different instruments. Your 200 trades are probably distributed over many stocks, so it's possible it might be pretty easy to match them up manually because the number of possible lots to match against might be small, with the help of a little script.

What benefit does writing my own script provide over letting beancount do it for me using its FIFO booking method paired with `bean-query print`? I'm trying to figure out what I would do differently if I were to write one myself. I *think* I successfully did the conversion last night and was able to move back to using the STRICT booking method afterwards. It's possible I'm missing something, though (and haven't finished verifying everything).

-Aaron