Correctly calculate PnL when transferring between accounts

[Einar Idsø]

Hi,

I am a very happy user of Beancount for the past six or so months. I currently use it for tracking vacation hours, shares, and crypto. Since tax reporting is almost due, I need to properly calculate PnL for my shares and crypto. This becomes extremely difficult when purchasing many lots, when transferring lots from one broker to another and when exchanging between multiple currencies.

Some further elaboration:

1. I recently had to transfer some shares from one broker account to another in order to perform a sale. Since I have purchased shares at BrokerOne many times through my employer's share savings plan, I'd prefer to not manually enter each lot to be transferred (in my example I had almost 60 lots to be transferred which would create 120 painstakingly manually entered lines of transaction). Instead I'd like to do something like the following and have the transfer use FIFO to identify the lots transferred from BrokerOne to BrokerTwo and maintaing the cost basis *and* date stamp:

2000-01-01 * "Transfer shares from BrokerOne to BrokerTwo to sell at BrokerTwo"

  Assets:BrokerOne     -10 HOOL {}

  Assets:BrokerTwo      10 HOOL 

2000-01-02 * "Sell shares from BrokerTwo"

  Assets:BrokerTwo    -10 HOOL {} @ 12.00 USD

  Assets:Bank         120.00 USD

  Income:PnL

The reason the date stamp would need to be maintained is so that if I had some shares at BrokerTwo already that were purchased after I purchased the shares at BrokerOne, the older shares transferred from BrokerOne would be the ones that were sold. 

2. During crypto trading this becomes even more difficult to handle manually since each coin can be exchanged back and forth with a number of base coins such as BTC, ETH and LTC. Here we have to assign a cost basis and price in the local FIAT currency to each and every transaction in order to correctly calculate PnL. This is ok and can probably be automated from input scripts (I'm currently looking into that). However, there are two more issues for crypto trading:

a) It is very common to transfer coins between crypto exchanges (as for my share example above), but due to the nature of crypto trading where trades happen much more frequently for different base coins, it becomes impossible to stay on top of which lot to transfer. One way of solving this is by using FIAT cost basis and price to calculate a PnL for the transfer itself and establish a new cost basis for the lot after the transfer. But this is both odd and formally wrong.

b) If on day one I buy ETH for BTC at Binance, on day two I buy ETH for BTC at Poloniex, and on day three I sell ETH at Poloniex, the cost basis to be used for PnL calculations should really be the cost basis for the first ETH lot I bought, i.e. the one at Binance, and /not/ the cost basis for the lot at Poloniex. 

These two issues can be solved by lumping the accounts together as one. I.e. instead of having both Assets:Crypto:Binance and Assets:Crypto:Poloniex, I have just Assets:Crypto. Any transfer can be ignored (except for transfer fees), and FIFO is properly maintained. The problem with this is that I lose track of how much I have at each exchange.

My current approach to solving these problems is to have two separate Beancount files. One contains each exchange account and includes transfers between them in order to give me an overview of where my coins are located. I can choose whether to use FIAT cost basis or not, depending on how I want to represent the values. The other file lumps all the brokers together into one so that I can calculate PnL correctly. This two-file solution is less than ideal, and I am looking for alternatives. Does anyone have any suggestions?

If I may pose a suggestion myself for a modification (perhaps a plugin?) that may solve all these problems: What if it were possible to link/group exchange accounts in such a way that their holdings/values are reported per account, but their lots are considered lumped together? When transferring from one account to another in the same group, the lots don't change at all (except for the transfer fee), but when selling lots (i.e. any transaction that transfer from the group to outside the group), the default or account-specific booking method is used. This would probably require a new keyword to link accounts together, something like:

2017-01-01 open Assets:Crypto:Binance:BTC    BTC

2017-01-01 open Assets:Crypto:Binance:ETH    ETH

2017-01-01 open Assets:Crypto:Poloniex:BTC    BTC

2017-01-01 open Assets:Crypto:Poloniex:ETH    ETH

; List all accounts to be linked on one line:

2017-01-01 link Assets:Crypto:Binance:BTC Assets:Crypto:Poloniex:BTC

2017-01-01 link Assets:Crypto:Binance:ETH Assets:Crypto:Poloniex:ETH

Alternatively link on higher account levels:

2017-01-01 open Assets:Crypto:BTC:Binance    BTC

2017-01-01 open Assets:Crypto:ETH:Binance    ETH

2017-01-01 open Assets:Crypto:BTC:Poloniex    BTC

2017-01-01 open Assets:Crypto:ETH:Poloniex    ETH

2017-01-01 link Assets:Crypto:BTC   ; implies "link Assets:Crypto:BTC:Binance Assets:Crypto:BTC:Poloniex Assets:Crypto:BTC:whatever"

2017-01-01 link Assets:Crypto:ETH 

It may actually be better to just link a commodity across Assets (and Assets only):

2017-01-01 commodity BTC 

  linked: "true"

; or simply 2017-01-01 commodity BTC "linked"

2017-01-01 open Assets:Crypto:Binance    BTC,ETH

2017-01-01 open Assets:Crypto:Poloniex:BTC    BTC

2017-01-01 open Assets:Crypto:Poloniex:ETH    ETH

Any thoughts? If these issues can already be handled in Beancount, please let me know how.

Cheers,

Einar

[Justus Pendleton]

On Monday, April 23, 2018 at 3:02:00 PM UTC+7, Einar Idsø wrote:

1. I recently had to transfer some shares from one broker account to another in order to perform a sale. Since I have purchased shares at BrokerOne many times through my employer's share savings plan, I'd prefer to not manually enter each lot to be transferred

There is no shortcut. I asked in a previous thread[1]. In particular pay attention to Daniel Clemente's reply to me. Although I was also annoyed, I eventually decided it was a small enough edge case I can understand not building in any better support. If you don't want to see 100+ lines of clutter in your main file (which is totally understandable), create a "asset-transfer.bean" and #include that. (I think includes are an underrated and underused feature in beancount!)

[1]: https://groups.google.com/forum/#!msg/beancount/YJDvaVVsJnI/8T8eyC4VBQAJ;context-place=forum/beancount

 

a) It is very common to transfer coins between crypto exchanges (as for my share example above), but due to the nature of crypto trading where trades happen much more frequently for different base coins, it becomes impossible to stay on top of which lot to transfer.

If you aren't actually tracking which lot you transfer how do you possibly accurately calculate taxes? I know nothing about cryptocurrency trading -- and didn't quite understand the reasoning for why you aren't able to track your lots -- but in my experience with other securities either you or your broker HAS TO keep track of lots and tax authorities don't accept short cuts. Doesn't your broker do this for you? In another forum, I saw a post from someone who had several hundred thousand transactions (not crypto) in a single year and was able to track all of the lots.

If I may pose a suggestion myself for a modification (perhaps a plugin?) that may solve all these problems: What if it were possible to link/group exchange accounts in such a way that their holdings/values are reported per account, but their lots are considered lumped together?

I don't quite follow the whole linked account thing but it seems like you just want/need a simple syntax for transferring lots (or portions of lots) between account. With other securities it is a rare enough occurrence I understand why beancount doesn't have it. Maybe it is more common with cryptocurrency trading and that would motivate a change to the grammar?

[Martin Blais]

On Mon, Apr 23, 2018 at 4:02 AM, Einar Idsø <einar...@gmail.com> wrote:

Hi,

I am a very happy user of Beancount for the past six or so months. I currently use it for tracking vacation hours, shares, and crypto. Since tax reporting is almost due, I need to properly calculate PnL for my shares and crypto. This becomes extremely difficult when purchasing many lots, when transferring lots from one broker to another and when exchanging between multiple currencies.

Crypto presents somewhat of a challenge for all these accounting systems, because it is to be treated like an investment, tracking all the lots and cost basis, but at the same time, users also expect to be able to spend those instruments as if they were money with the expectations of simplicity that counting units of a currency implies. So basically it's forcing investment tracking to be fully automaitc, and most of these systems aren't designed with that in mind-- tracking investments usually requires a bit of user involvement on sales (though it's almost there).

 

Some further elaboration:

1. I recently had to transfer some shares from one broker account to another in order to perform a sale. Since I have purchased shares at BrokerOne many times through my employer's share savings plan, I'd prefer to not manually enter each lot to be transferred (in my example I had almost 60 lots to be transferred which would create 120 painstakingly manually entered lines of transaction). Instead I'd like to do something like the following and have the transfer use FIFO to identify the lots transferred from BrokerOne to BrokerTwo and maintaing the cost basis *and* date stamp:

2000-01-01 * "Transfer shares from BrokerOne to BrokerTwo to sell at BrokerTwo"

  Assets:BrokerOne     -10 HOOL {}

  Assets:BrokerTwo      10 HOOL 

This transaction would not balance, because balancing a transaction is done at cost, that is, the second posting would have to be in units of whatever you bought HOOL with, e.g. USD.

The idea is supporting a transfer like this, simply moving the Position objects from one account to another while taking advantage of the booking mechanism for a reduction is a good one. I can see it being useful. It's not supported right now. I think this type of thing would require some sort of syntax. If you just wrote this:

2000-01-01 * "Transfer shares from BrokerOne to BrokerTwo to sell at BrokerTwo"

  Assets:BrokerOne     -10 HOOL {}

  Assets:BrokerTwo     

would be to insert the cost of the reduced 10 HOOL. Some sort of new syntax would be needed to let it know that it needs to move the positions (as well as they dates) to the interpolated account.

2000-01-02 * "Sell shares from BrokerTwo"

  Assets:BrokerTwo    -10 HOOL {} @ 12.00 USD

  Assets:Bank         120.00 USD

  Income:PnL

The reason the date stamp would need to be maintained is so that if I had some shares at BrokerTwo already that were purchased after I purchased the shares at BrokerOne, the older shares transferred from BrokerOne would be the ones that were sold. 

2. During crypto trading this becomes even more difficult to handle manually since each coin can be exchanged back and forth with a number of base coins such as BTC, ETH and LTC. Here we have to assign a cost basis and price in the local FIAT currency to each and every transaction in order to correctly calculate PnL. This is ok and can probably be automated from input scripts (I'm currently looking into that). However, there are two more issues for crypto trading:

a) It is very common to transfer coins between crypto exchanges (as for my share example above), but due to the nature of crypto trading where trades happen much more frequently for different base coins, it becomes impossible to stay on top of which lot to transfer. One way of solving this is by using FIAT cost basis and price to calculate a PnL for the transfer itself and establish a new cost basis for the lot after the transfer. But this is both odd and formally wrong.

I agree, you should be able to just move the Position objects across accounts. Apart from a convenient syntax to do it, there's nothing else in Beancount's schema that should prevent making that possible and easy.

 

b) If on day one I buy ETH for BTC at Binance, on day two I buy ETH for BTC at Poloniex, and on day three I sell ETH at Poloniex, the cost basis to be used for PnL calculations should really be the cost basis for the first ETH lot I bought, i.e. the one at Binance, and /not/ the cost basis for the lot at Poloniex. 

These are in different accounts? That one is difficult. 

If I understand correctly what you want to do here is treat the booking algorithm across multiple accounts.

Beancount doesn't currently support that. 

One thing you could try is to write a plugin that identifies those cross-accounts occurrences and inserts transfers automatically, I'm not sure if that would work to address this.

These two issues can be solved by lumping the accounts together as one. I.e. instead of having both Assets:Crypto:Binance and Assets:Crypto:Poloniex, I have just Assets:Crypto. Any transfer can be ignored (except for transfer fees), and FIFO is properly maintained. The problem with this is that I lose track of how much I have at each exchange.

My current approach to solving these problems is to have two separate Beancount files. One contains each exchange account and includes transfers between them in order to give me an overview of where my coins are located. I can choose whether to use FIAT cost basis or not, depending on how I want to represent the values. The other file lumps all the brokers together into one so that I can calculate PnL correctly. This two-file solution is less than ideal, and I am looking for alternatives. Does anyone have any suggestions?

Try building your own plugin to insert transfers where needed, transferring Position objects as alluded to in your point (1).

If I may pose a suggestion myself for a modification (perhaps a plugin?) that may solve all these problems: What if it were possible to link/group exchange accounts in such a way that their holdings/values are reported per account, but their lots are considered lumped together? When transferring from one account to another in the same group, the lots don't change at all (except for the transfer fee), but when selling lots (i.e. any transaction that transfer from the group to outside the group), the default or account-specific booking method is used. This would probably require a new keyword to link accounts together, something like:

2017-01-01 open Assets:Crypto:Binance:BTC    BTC

2017-01-01 open Assets:Crypto:Binance:ETH    ETH

2017-01-01 open Assets:Crypto:Poloniex:BTC    BTC

2017-01-01 open Assets:Crypto:Poloniex:ETH    ETH

; List all accounts to be linked on one line:

2017-01-01 link Assets:Crypto:Binance:BTC Assets:Crypto:Poloniex:BTC

2017-01-01 link Assets:Crypto:Binance:ETH Assets:Crypto:Poloniex:ETH

Alternatively link on higher account levels:

2017-01-01 open Assets:Crypto:BTC:Binance    BTC

2017-01-01 open Assets:Crypto:ETH:Binance    ETH

2017-01-01 open Assets:Crypto:BTC:Poloniex    BTC

2017-01-01 open Assets:Crypto:ETH:Poloniex    ETH

2017-01-01 link Assets:Crypto:BTC   ; implies "link Assets:Crypto:BTC:Binance Assets:Crypto:BTC:Poloniex Assets:Crypto:BTC:whatever"

2017-01-01 link Assets:Crypto:ETH 

It may actually be better to just link a commodity across Assets (and Assets only):

2017-01-01 commodity BTC 

  linked: "true"

; or simply 2017-01-01 commodity BTC "linked"

2017-01-01 open Assets:Crypto:Binance    BTC,ETH

2017-01-01 open Assets:Crypto:Poloniex:BTC    BTC

2017-01-01 open Assets:Crypto:Poloniex:ETH    ETH

Any thoughts? If these issues can already be handled in Beancount, please let me know how.

I think this is possible -- all the moving pieces are present to implement this, I don't think this would even be very hard -- but while the base example is simple enough, this could potentially open a big can of worms. How many accounts would you be linking like that? Would there be separate but overlapping groups of accounts? (e.g., A-B and B-C?)

How common is this? How important is it to solve that problem?

[Martin Blais]

On Mon, Apr 23, 2018 at 11:39 AM, Justus Pendleton <just...@gmail.com> wrote:

On Monday, April 23, 2018 at 3:02:00 PM UTC+7, Einar Idsø wrote:

1. I recently had to transfer some shares from one broker account to another in order to perform a sale. Since I have purchased shares at BrokerOne many times through my employer's share savings plan, I'd prefer to not manually enter each lot to be transferred

There is no shortcut. I asked in a previous thread[1]. In particular pay attention to Daniel Clemente's reply to me. Although I was also annoyed, I eventually decided it was a small enough edge case I can understand not building in any better support. If you don't want to see 100+ lines of clutter in your main file (which is totally understandable), create a "asset-transfer.bean" and #include that. (I think includes are an underrated and underused feature in beancount!)

[1]: https://groups.google.com/forum/#!msg/beancount/YJDvaVVsJnI/8T8eyC4VBQAJ;context-place=forum/beancount

 

a) It is very common to transfer coins between crypto exchanges (as for my share example above), but due to the nature of crypto trading where trades happen much more frequently for different base coins, it becomes impossible to stay on top of which lot to transfer.

If you aren't actually tracking which lot you transfer how do you possibly accurately calculate taxes? I know nothing about cryptocurrency trading -- and didn't quite understand the reasoning for why you aren't able to track your lots -- but in my experience with other securities either you or your broker HAS TO keep track of lots and tax authorities don't accept short cuts. Doesn't your broker do this for you? In another forum, I saw a post from someone who had several hundred thousand transactions (not crypto) in a single year and was able to track all of the lots.

If I may pose a suggestion myself for a modification (perhaps a plugin?) that may solve all these problems: What if it were possible to link/group exchange accounts in such a way that their holdings/values are reported per account, but their lots are considered lumped together?

I don't quite follow the whole linked account thing

I think what he means is that if positions are augmented, the booking algorithm doesn't change, but for the purpose of booking reductions automatically, the positions of the combined inventory for of the linked accounts would be considered as candidates for booking, and transfers inserted accordingly (and automatically). This would require a change to the booking algorithm, though in theory, you could write a plugin which processes linked account declarations via Custom directives, and automatically detects such cases (duplicating the work of the booking algorithm) and inserts transfers.

[Einar S. Idsø]

Justus and Martin,

Sorry for my tardy response.

On Mon, Apr 23, 2018 at 5:39 PM, Justus Pendleton <just...@gmail.com> wrote:

(...). If you don't want to see 100+ lines of clutter in your main file (which is totally understandable), create a "asset-transfer.bean" and #include that. (I think includes are an underrated and underused feature in beancount!)

Thank you for the tip - that's a good way of removing clutter.

If you aren't actually tracking which lot you transfer how do you possibly accurately calculate taxes? I know nothing about cryptocurrency trading -- and didn't quite understand the reasoning for why you aren't able to track your lots -- but in my experience with other securities either you or your broker HAS TO keep track of lots and tax authorities don't accept short cuts. Doesn't your broker do this for you? In another forum, I saw a post from someone who had several hundred thousand transactions (not crypto) in a single year and was able to track all of the lots.

Sorry, I should have said that "it becomes /virtually/ impossible" to keep track of lots since there is manual work involved. My broker keeps track of tax calculations for my shares, but for crypto I am on my own to do the calculations. It is not the volume itself that causes problems, but the fact that 1) lots can be bought for one currency and sold for another, and 2) when transferring lots between exchanges, FIFO is broken and I therefore have to manually keep track of the lots instead of letting Beancount hande it for me. That is why I am looking for a way to let Beancount handle lot transfers between accounts. 

 

Regarding linked accounts: After reading Martin's response and doing some more thinking, I am not sure that linking accounts is such a good idea after all. The idea was to link lots across exchanges in such a way that whichever exchange you sell from, the cost of the first lot bought (FIFO) is used to calculate PnL. However, the reduction needs to be from the lot at the exchange where you sell, or else the total balance will be messed up. So prices need to be somewhat disconnected from lots, or lots need to be automatically transferred between linked accounts during a sale to maintain balance. Either way it becomes rather messy and most likely non-trivial to implement.

On Tue, Apr 24, 2018 at 4:11 AM, Martin Blais <bl...@furius.ca> wrote:

The idea is supporting a transfer like this, simply moving the Position objects from one account to another while taking advantage of the booking mechanism for a reduction is a good one. I can see it being useful. It's not supported right now. I think this type of thing would require some sort of syntax. 

Great! Would this require modifications to the core, or can it be implemented through a plugin?

Cheers,

Einar

[Martin Blais]

I'm not sure, but I think it may require inserting code in the booking algorithms.

Best is to try it out.

[Einar S. Idsø]

Martin,

On Mon, Apr 30, 2018 at 12:26 AM, Martin Blais <bl...@furius.ca> wrote:

On Tue, Apr 24, 2018 at 4:11 AM, Martin Blais <bl...@furius.ca> wrote:

The idea is supporting a transfer like this, simply moving the Position objects from one account to another while taking advantage of the booking mechanism for a reduction is a good one. I can see it being useful. It's not supported right now. I think this type of thing would require some sort of syntax. 

Great! Would this require modifications to the core, or can it be implemented through a plugin?

I'm not sure, but I think it may require inserting code in the booking algorithms.

Best is to try it out.

So I'm reading up on your documentation to further my understanding and prepare for import script development, and I came across the idea of supporting stock splits in the booking proposal doc (http://furius.ca/beancount/doc/proposal-booking);

"Stock splits need to be able to maintain some of the original attributes of the position, specifically, the original trade date and the user-specified label, if one has been provided. 

This should allow a common syntax to specify an original trading lot when reducing a position after a split."

If I understood this correctly, then this functionality would also cover the case of moving a position from one account to another while maintaining trading lot information for performing correct reductions, as requested above? If so, do you have any idea if and when the stock split proposal may be implemented? 

Cheers,

Einar

[Martin Blais]

Actually it's not the accounts that change, but rather the currencies.  I don't see the link between moving positions between accounts.

The complications arising from stock splits stem in how one represents the currencies (I think).

Each currency has to become multiple variations of an ideal one. So you have the currency, but its various incarnations over time.

 

If so, do you have any idea if and when the stock split proposal may be implemented? 

About time: not for a while. These are fairly rare events that can be handled well currently by explicitly converting the positions to different versions of a currency. Or just converting to the same currency and making sure there's a price update on the day the split occurs (which results in price series with cliffs - not the best solution), but the harm is of minor importance.