Tracking investments in hledger

MatthiasKauer commented 7 years ago

[See newer discussions starting at #1015.]

Hi, I just converted my trading-related transactions from last year to hledger. In that process, I learned a few things that I felt are worth sharing.

First of all, I want to point out that everything worked for me.

Some things could still be improved. For instance, I would like to rewrite the guide from http://hledger.org/investments.html. Buying and selling stocks does not only involve asset accounts. Capital gain is an income and must be included in tax reports. https://github.com/simonmichael/hledger/issues/377 also discusses that and my examples look similar.

2015/01/01  Initial purchase
    asset:invest:AAPL   AAPL 5 @ USD 130
    asset:invest:cash

2017/01/01  Realize profits
    income:capgain
    asset:invest:AAPL   AAPL -5 @ USD 130   ; Is there any way to deduce or validate "@130" here?
    asset:invest:AAPL   AAPL 5 @ USD 150

2017/01/01  Sell (Note: should be combined with 'realize profits' in most cases)
    asset:invest:AAPL   AAPL -5 = AAPL 0 ; ensure that no more shares are left
    asset:invest:cash   USD 750

The guide should also have a dividend example even though it is straightforward. I also suggest a reference to Martin Blais' guides for Beancount. They explain modeling investments in double-entry accounting quite well.

Some questions I came across and features I'd find helpful in hledger:

CSV import should be able to generate transactions with more than 2 accounts. I imagine this would only require an extension of the field names that are currently allowed. It should be simpler than https://github.com/simonmichael/hledger/issues/507.
Tracking cost basis would be really helpful. Currently, I have to look up the cost basis of a lot and add it. This can probably not be fully automated because there are so many rules when positions are sold partially. However, it can be checked that the cost the user enters exists at least. I believe (but have not verified) that this is what ledger does. This is discussed, for instance, in https://github.com/simonmichael/hledger/issues/82 and https://github.com/simonmichael/hledger/issues/488. How hard is that feature to implement?
register could show where transactions came from. That would make some reports easier.
Can hledger calculate inline? Say Expense:AAPL:Commission USD $(750-741.9)? Note: this should be in addition to deducing the last entry. Does this feature make sense to you?
Mark-to-market performance as discussed in https://github.com/simonmichael/hledger/issues/329 would be nice but, for me, it is just "nice to have". I'd suggest tracking only realized performance, i.e., actual transactions for now.
Is there a way to associate stock symbols and ISIN, RIC or other identification numbers in hledger? Probably out of scope. Maybe someone can suggest a tool to go along with hledger here.

What do you think?

Best regards, Matthias

simonmichael commented 7 years ago

Hi Matthias, thanks for this useful discussion and issue round-up.

For instance, I would like to rewrite the guide from http://hledger.org/investments.html http://hledger.org/investments.html.

Excellent.

Some questions I came across and features I'd find helpful in hledger:

CSV import should be able to generate transactions with more than 2 accounts. I imagine this would only require an extension of the field names that are currently allowed. It should be simpler than #507 https://github.com/simonmichael/hledger/issues/507. I need this too. Even adding just 3rd/4th posting ability would be a help. Tracking cost basis would be really helpful. Currently, I have to look up the cost basis of a lot and add it. This can probably not be fully automated because there are so many rules when positions are sold partially. However, it can be checked that the cost the user enters exists at least. I believe (but have not verified) that this is what ledger does. This is discussed, for instance, in #82 https://github.com/simonmichael/hledger/issues/82 and #488 https://github.com/simonmichael/hledger/issues/488. How hard is that feature to implement? Don't know. A simple matter of programming!

I learned some things about this on #ledger yesterday. I will see about capturing the log.

register could show where transactions came from. That would make some reports easier. Oh, how do you mean ? Can hledger calculate inline? Say Expense:AAPL:Commission USD $(750-741.9)? Note: this should be in addition to deducing the last entry. Does this feature make sense to you? Currently we don't have this feature and I don't miss it much yet. It seems a slippery slope, how far would you go with it ? Mark-to-market performance as discussed in #329 https://github.com/simonmichael/hledger/issues/329 would be nice but, for me, it is just "nice to have". I'd suggest tracking only realized performance, i.e., actual transactions for now. Auto-generated capital gain/loss transactions, right ? Do you suggest not adding that feature ? Is there a way to associate stock symbols and ISIN, RIC or other identification numbers in hledger? Probably out of scope. Maybe someone can suggest a tool to go along with hledger here.

You can use them as commodity symbols.. do you mean something more ?

MatthiasKauer commented 7 years ago

Hi Simon, thank you for the quick enlightening feedback.

I'll look at the documentation and I'll take a look at the CSV import. Not quite sure how well I will understand the latter though.

Let me address the other topics.

From column in register command

register now yields

C:\dvl\ledger_invest>hledger -f 05_accum.ledger reg Cash
2016/01/01 Initial purchase     Asset:Invest:Cash         EUR -300      EUR -300
2016/07/15 AAPL dividend pay..  Asset:Invest:Cash        EUR 44.06   EUR -255.94

I was thinking it'd be helpful to have another "From" column. My initial example file already makes me realize that this is more tricky than I thought. Multileg transactions don't fit into this easily. Hmm.

In-line expressions

Hooking up a scripting language like Javascript or Lua in there might work. But you're probably right. It's quite a heavy future with maybe only small benefit.

Cost basis and mark-to-market

Please do capture the log on the cost basis implementation. I'd be quite interested in that.

As I understand it, mark-to-market performance is all about unrealized profits. I am quite interested in that but I believe it is more complicated than the transaction-based accounting that hledger is doing now. Maybe an example will illustrate the difference I see. Consider the following transactions

2015/01/01  Initial purchase
    asset:invest:AAPL   AAPL 5 @ USD 100
    asset:invest:cash

2017/01/01  Sell
    asset:invest:AAPL   AAPL -5 @ USD 100 = AAPL 0
    asset:invest:cash   USD 600
    income:capgain

Currently, we get

C:\dvl\ledger_invest>hledger -f 06_simon_example.ledger bal
             USD 100  asset:invest:cash
            USD -100  income:capgain
--------------------
                   0

I'd be happy to get errors if I entered a non-existing price base. Concretely, the following should throw an error in my face.

2017/01/01  Sell
    asset:invest:AAPL   AAPL -5 @ USD 105 = AAPL 0      ; ERROR: You don't have AAPL @ USD 105 to sell.
    asset:invest:cash   USD 600
    income:capgain

If we wanted mark-to-market performance we'd also want ongoing reevaluation. Assume the following price data.

AAPL, 2015, 100
AAPL, 2016, 115
AAPL, 2017, 120

We now have to deduce that there was an unrealized profit or mark-to-market performance of 5x15 USD in 2015 and another 5x5 USD in 2016. It could probably be realized by creating artificial transactions for each price entry. To repeat myself: I'd love to have this feature but I think it's a big change with potential for unexpected interaction.

Symbols

Stocks tend to have more than one symbol. I'd like to address AAPL equivalently as US0378331005 (ISIN) or 865985 (WKN). See http://en.boerse-frankfurt.de/stock/Apple-share Maybe account aliases are the way to go for that. Or maybe hledger shouldn't be concerned with that topic.

So overall, the register extension and the scripting language should probably be dropped. What do you think about my understanding of mark-to-market and the multiple symbols.

Best regards, Matthias

MatthiasKauer commented 7 years ago

I have taken a first look at implementing the additional CSV entry fields. It appears that this can be done by understanding hledger-lib/Hledger/Read/CsvReader.hs. Is that so? Is it worth for me to have a go at it? Or do you need it so urgently that you'll hack it together ASAP anyway ;)

simonmichael commented 7 years ago

I have taken a first look at implementing the additional CSV entry fields. It appears that this can be done by understanding hledger-lib/Hledger/Read/CsvReader.hs. Is that so? Is it worth for me to have a go at it?

Correct! Please do have a go at it. Quick support available on #hledger if needed.

simonmichael commented 7 years ago

The aforementioned chat, and some new thoughts: https://gist.github.com/simonmichael/917f95da111365b81de1135496b00a3c . More later.

simonmichael commented 7 years ago

Re symbols: aha. We don't have commodity aliases, I suppose it could be added if this comes up a lot.

simonmichael commented 7 years ago

Thanks @MatthiasKauer, eg for pointing out the unrealized vs. realized distinction. I feel a bit clearer. Here are some things I think are true, comments welcome.

Mark to market, or market value, is the value of your assets according to current market prices. This is given by hledger's balance -V.

Unrealized capital gain(/loss) is the difference between what you paid for assets you currently hold, and their current market value.

The cost of your priced assets, ie what you paid for them, is given by hledger's balance -B, only in simple cases. Eg where you haven't sold anything or where you haven't made multiple purchases at different prices. Accurately reporting what you paid for assets held, requires lot tracking.

A lot is an amount of some commodity in an asset account, its purchase price in some currency, and the purchase date. An asset balance may consist of many lots with different dates and prices.

Cost basis is a conventional name for a lot's purchase price.
(Maybe it is also used for the overall "what you paid for asset held" ?)

In *ledger terms, basically each positive posting (deposit) to an asset creates a new lot. hledger currently doesn't keep enough detail internally to do lot-based reporting. (It keeps amounts separated by price, but not by date.)

Realized capital gain(/loss) is an income(/expense) incurred when you sell a priced asset, due to price changes. It is the difference between what you paid for the asset sold, and what you sell it for. It is significant for taxes.

Calculating realized capital gain/loss is done following a strategy such as FIFO or average cost. This choice is usually dictated by tax authorities.

These calculations require knowing exactly which lots or partial lots from the asset balance are sold. This must also be known for other kinds of withdrawal too, eg when making a transfer to another asset account.

Ledger does (and hledger does not) allow the user to identify which lot(s?) is sold by writing its purchase price and/or date.

The notation for this has been known to confuse at least some of us. On the plus side when it works it offers precise control and explicit notation of what's going on.

An alternative could be to just specify which calculation strategy is to be used. This could make life simpler for users. On the other hand there might be some situations where you want the more precise low-level control, or the more explicit notation.

immae commented 7 years ago

one thing that should be considered too: how much do you want to be compatible with ledger's format? Or better: defining a "standard" format of plain text accounting? The ledger's format could probably be improved if it’s not clear (to me it is, but I spent a lot of time to read the code to understand it as the documentation isn't)

Trying to make the lot prices (lot tracking) fit in existing boxes (@'s) it probably not a good idea and too limited

simonmichael commented 7 years ago

It's possible we'll try something new but end up coming back to Ledger's way, or add it as an alternative for syntax compatibility. I want something that's easy to understand, learn and use, and capable.

How well does Ledger's syntax work when there are many lots ? Eg say you bought small amounts of BTC weekly for a long time, then you sell a big chunk, say 50 of a hundred lots.

MatthiasKauer commented 7 years ago

Many lots would have to be individually specified, right? Are you optimistic about automating this by specifying, say, LIFO, FIFO or so on the transaction?

simonmichael commented 7 years ago

I don't see any reason why it can't work.. a simple matter of programming..

simonmichael commented 7 years ago

PS jkepler shared this python script, for inspiration: https://framagit.org/jkepler/TaxingLots/blob/master/TaxingLots.py

simonmichael commented 7 years ago

However. I would like to understand better these detailed lot-counting strategies, their variety, how much they are used and needed in practice. A quick search turned up not a single mention of them. Instead I found a bunch of US capital gains tax calculators which simply choose one of two tax rates based on whether you held the asset for more or less than a year. Perhaps I don't know the right search terms.

immae commented 7 years ago

@simonmichael for French tax system, it’s explicit here: http://bofip.impots.gouv.fr/bofip/3619-PGP#3619-PGP_Les_titres_ou_droits_cedes__21

Section "B Les titres ou droits cédés sont fongibles (ou non individualisables)" ("sold actions are not distinguishable")

immae commented 7 years ago

And yes, when you sell many "lots" you have to write them all individually

simonmichael commented 7 years ago

@immae: thanks. The calculation example is helpful.

Writing out a huge number of lots is.. explicit, which is maybe good thing for auditors. But also error-prone, unless the tool double checks that you followed the correct strategy.

immae commented 7 years ago

Yes, it's error prone, unless you allow to have expressions somehow.

Note that the example says that your lots have a mean value. You can write for each lot you buy an adjustment transaction that makes you always have one single lot (at the mean value of the previous). This is equivalent (except you have no "view" of your lots history). This of course doesn’t have an equivalent for US-style taxation

the-solipsist commented 6 years ago

@simonmichael In response to your question about lot-counting strategies, I found this very helpful and informative: http://www.mscs.dal.ca/~selinger/accounting/tutorial.html#5

In India, for certain things we are required to follow FIFO as per the law (for capital gains calculation involving equity (directly or indirectly, through mutual funds, etc.)), but for other forms of capital gains, other methods may be used.

jkyiv commented 6 years ago

PS jkepler shared this python script, for inspiration: https://framagit.org/jkepler/TaxingLots/blob/master/TaxingLots.py

I'm glad my script might be helpful for inspiration improving how we can track investment or cryptocurrency lots with plain text accounting. I'm finally returning to my script as I may have some time in the coming weeks to improve it.

@simonmichael In response to your question about lot-counting strategies, I found this very helpful and informative: http://www.mscs.dal.ca/~selinger/accounting/tutorial.html#5

I also find Selinger's explanations helpful. While I started with FIFO, I'd like to include some other options in my TaxingLots script.

fifoturn commented 5 years ago

Hi, I am new to hledger and not so familiar with cli. Tried to import Bitcoin CVS files, but hard to use this more complicated import scripts. I build a csv file with amount like this "BTC 0,02345678 @ USD 3000"

I am very late with my tax report and look for Open Source solution to do FIFO. How is it going? I would like to donate, but I am in hurry.

simonmichael commented 5 years ago

Hi @fifoturn - start a new discussion on #hledger IRC channel or the mail list, and show us a sample of the CSV you want to convert.

simonmichael commented 2 years ago

Closing this 2017 discussion, see the newer issues starting at #1015.

simonmichael / hledger