Re: Correctly calculate PnL when transferring between accounts

[Martin Blais]

On Mon, Apr 23, 2018 at 4:02 AM, Einar Idsø  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 

icsv2ledger compatible categorizer for CSV importer

[Stefano Zacchiroli]

On the Ledger side, I've been using icsv2ledger[1] for many years. That
means I've a pretty rich mapping DB in the format of that tool.

[1]: https://github.com/quentinsf/icsv2ledger

When I started to play with bean-extract and the builtin CSV importer
(beancount.ingest.importers.csv), it occurred to me that it'd be nice to
have a categorizer for it that used the icsv2ledger mapping file as a
backend. Hence I wrote it, and you can find it attached to this mail.

Comments welcome.

In particular, this is my first attempt as using the internal Beancount
data structures, so I'd love to hear what I'm doing wrong.

Also, is the built-in CSV importer meant to be used this way
(sub-classing and all) or am I missing something?

Please let me knkow if this adapter is of interest to others than me. In
that case I can publish it on a more suitable place than this list.

Cheers
-- 
Stefano Zacchiroli . z...@upsilon.cc . upsilon.cc/zack . . o . . . o . o
Computer Science Professor . CTO Software Heritage . . . . . o . . . o o
Former Debian Project Leader & OSI Board Director  . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to beancount@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/beancount/20180423140752.GA23447%40upsilon.cc.
For more options, visit https://groups.google.com/d/optout.
"""categorizer for Beancount CSV importer based on icsv2ledger mapping DB

Given as input a mapping_file for `icsv2ledger
`_, return a function from Beancount
transactions to Beancount transactions, suitable as categorizer argument for
Beancount built-in CSV importer
(:class:`beancount.ingest.importers.csv.Importer`).

This categorizer hence provides compatibility between icsv2ledger maintained
mapping DBs and Beancount importers, allowing to share mappings between the
Ledger and Beancount worlds.

"""
__copyright__ = "Copyright (C) 2018  Stefano Zacchiroli"
__license__ = "GPL-2.0-or-later"


import csv
import re

from beancount.core.data import Posting, Transaction


tag_kv_sep_RE = re.compile(r':\s*')


def read_mappings(mapping_file):
"""read a icsv2ledger mapping file and return a list of parsed mappings

each parsed mapping is a dict with keys: "pattern" (a regexp),
"description", "account", "metadata" (a list of key/value pairs), "tags"

note that the order is significant: as per icsv2ledger semantics, the
*last* match wins

"""
mappings = []

with open(mapping_file) as csvfile:
reader = csv.reader(csvfile)
for fields in reader:
mapping = {}

mapping['pattern'] = re.compile(fields[0].strip('/'))
mapping['description'] = fields[1]
mapping['account'] = fields[2]
mapping['metadata'] = []
mapping['tags'] = []
for tag in fields[3:]:
try:  # if it's a metadata entry, split key/value
k, v = tag_kv_sep_RE.split(tag)
mapping['metadata'].append((k, v))
except ValueError:  # it's a tag, not a metadata entry
mapping['tags'].append(tag.strip(':'))

mappings.append(mapping)

return mappings


def categorize_txn(mappings, unknown_acct, txn):
"""categorize a transaction using icsv2ledger mappings

find best (i.e., last) match and return a transaction which is a fresh copy
of the input one, updated with all the info given in the matching mapping
entry

"""
assert txn.postings
txn_amount = txn.postings[0].units

matches = (m for m in reversed(mappings)  # look for best match
   if m['pattern'].search(txn.narration))

new_txn = None
try:  # match found: set narration, add metadata, tags, categorization leg
mapping = next(matches)

narration = mapping['description']

meta = txn.meta
meta.update(dict(mapping['metadata']))  # merge meta
tags = txn.tags.union(set(mapping['tags']))  # merge tags

new_posting = Posting(mapping['account'], -txn_amount,
  None, None, None, None)
postings = txn.postings + [new_posting]  # add category

# make txn point to a new, updated transaction, and quit
new_txn = Transaction(meta, txn.date, txn.flag,
  txn.payee, narration,
  tags, txn.links, postings)
except StopIteration:  # no match: add "unkown account" leg
new_posting = Posting(unknown_acct, -txn_amount,
  None, None, None, None)
postings = txn.postings + [new_posting]

new_txn = 

Issue #285: Query result should be empty string instead of None so ORDER BY can work (blais/beancount)

[Martin Michlmayr]

New issue 285: Query result should be empty string instead of None so ORDER BY 
can work
https://bitbucket.org/blais/beancount/issues/285/query-result-should-be-empty-string

Martin Michlmayr:

I'm trying to do an `ORDER BY` query but I get an error because some results 
don't have values:

```
beancount> select account, any_meta('who'), sum(position) WHERE account ~ 
'Assets:Receivable' GROUP BY 1, 2
 account  any_me sum_positi
- -- --
Assets:Receivable Martin 100.00 EUR
Assets:Receivable100.00 EUR
beancount> 
```

With `ORDER BY 2`:

```
beancount> select account, any_meta('who'), sum(position) WHERE account ~ 
'Assets:Receivable' GROUP BY 1, 2 ORDER BY 2
Traceback (most recent call last):
  File "/usr/lib/python3.5/cmd.py", line 214, in onecmd
func = getattr(self, 'do_' + cmd)
AttributeError: 'BQLShell' object has no attribute 'do_select'
...
  File 
"/home/tbm/.local/lib/python3.5/site-packages/beancount/query/query_execute.py",
 line 328, in execute_query
reverse=(query.ordering == 'DESC'))
TypeError: unorderable types: NoneType() < str()
```

Test case:

```
plugin "beancount.plugins.auto"

2018-04-22 * "Test 1"
Assets:Receivable  100.00 EUR
who: "Martin"
Assets:Bank   -100.00 EUR

2018-04-22 * "Test 2"
Assets:Receivable  100.00 EUR
Assets:Bank   -100.00 EUR
```


-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to beancount@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/beancount/20180423105416.26151.84381%40celery-worker-106.ash1.bb-inf.net.
For more options, visit https://groups.google.com/d/optout.

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:BTCBTC
2017-01-01 open Assets:Crypto:Binance:ETHETH
2017-01-01 open Assets:Crypto:Poloniex:BTCBTC
2017-01-01 open Assets:Crypto:Poloniex:ETHETH
; 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:BinanceBTC
2017-01-01 open Assets:Crypto:ETH:BinanceETH
2017-01-01 open Assets:Crypto:BTC:PoloniexBTC
2017-01-01 open Assets:Crypto:ETH:PoloniexETH