New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Tracking investments in hledger #624

Open
MatthiasKauer opened this Issue Oct 13, 2017 · 19 comments

Comments

Projects
None yet
5 participants
@MatthiasKauer
Contributor

MatthiasKauer commented Oct 13, 2017

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.
#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 #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 #82 and #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 #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

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 13, 2017

Owner
Owner

simonmichael commented Oct 13, 2017

@MatthiasKauer

This comment has been minimized.

Show comment
Hide comment
@MatthiasKauer

MatthiasKauer Oct 15, 2017

Contributor

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

Contributor

MatthiasKauer commented Oct 15, 2017

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

This comment has been minimized.

Show comment
Hide comment
@MatthiasKauer

MatthiasKauer Oct 15, 2017

Contributor

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 ;)

Contributor

MatthiasKauer commented Oct 15, 2017

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

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 15, 2017

Owner
Owner

simonmichael commented Oct 15, 2017

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 15, 2017

Owner

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

Owner

simonmichael commented Oct 15, 2017

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

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 15, 2017

Owner

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

Owner

simonmichael commented Oct 15, 2017

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

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 15, 2017

Owner

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.

Owner

simonmichael commented Oct 15, 2017

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

This comment has been minimized.

Show comment
Hide comment
@immae

immae Oct 16, 2017

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

immae commented Oct 16, 2017

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

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 16, 2017

Owner

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.

Owner

simonmichael commented Oct 16, 2017

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

This comment has been minimized.

Show comment
Hide comment
@MatthiasKauer

MatthiasKauer Oct 16, 2017

Contributor

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?

Contributor

MatthiasKauer commented Oct 16, 2017

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

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 16, 2017

Owner

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

Owner

simonmichael commented Oct 16, 2017

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

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 16, 2017

Owner

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

Owner

simonmichael commented Oct 16, 2017

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

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 16, 2017

Owner

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.

Owner

simonmichael commented Oct 16, 2017

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

This comment has been minimized.

Show comment
Hide comment
@immae

immae Oct 18, 2017

@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 Oct 18, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@immae

immae Oct 18, 2017

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

immae commented Oct 18, 2017

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

@simonmichael

This comment has been minimized.

Show comment
Hide comment
@simonmichael

simonmichael Oct 18, 2017

Owner

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

Owner

simonmichael commented Oct 18, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@immae

immae Oct 18, 2017

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

immae commented Oct 18, 2017

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

This comment has been minimized.

Show comment
Hide comment
@the-solipsist

the-solipsist Oct 28, 2017

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

the-solipsist commented Oct 28, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@jkyiv

jkyiv Oct 14, 2018

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.

jkyiv commented Oct 14, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment