Rants, Ideas, Stuff

A blog about coding, coffee, and stuff

Tracking Investments in Lots with Hledger

Tracking investments with plaintext accounting tools like Hledger or other ledgerlikes isn‘t new. There’s a bunch of guides (Track Investments (Hledger docs), Buying and Selling Stock (ledger docs)). I want to present a very simple method I discovered that makes tracking lots easy. I need this because in Germany capital gains on stocks or cryptos are based in the first in first out method.

The standard way

Standard transactions when buying shares of an ETF might look like these:

commodity EUR
 format 9,999.00 EUR

D 1,000.00 EUR

2015/01/01 * Opening Balance
  Assets:Checking  1,000.00 EUR
  Equity:Opening

2015/01/01 * Monthly invest
  Assets:Investments:Bitcoin  1.50038 BTC @ 266.600 EUR
  Expenses:Fees  1.00 EUR
  Assets:Checking

2015/02/01 * Monthly invest
  Assets:Investments:Bitcoin  1.84332 BTC @ 217.000 EUR
  Expenses:Fees  1.00 EUR
  Assets:Checking

Selling part of the first lot might look like this:

P 2018/06/08 BTC 6522.600 EUR

2018/06/08 * Realising capital gains
  Assets:Investments:Bitcoin  -0.50038 BTC @ 266.600 EUR
  Assets:Checking  3,263.78 EUR
  Income:CapitalGains

How do we keep track how many shares of the first lot remain?

beancount is aware of lots and does help you with this, but I’m not aware Hledger does too. And since I like my plain text accounting to be simple, I prefer things to be explicit in my journal.

Introducing the sub accounts for lots

The method I came up with is using sub accounts for each lot.

commodity EUR
 format 9,999.00 EUR

D 1,000.00 EUR

2015/01/01 * Opening Balance
  Assets:Checking  1,000.00 EUR
  Equity:Opening

2015/01/01 * Monthly invest
  Assets:Investments:Bitcoin:20150101  1.50038 BTC @ 266.600 EUR
  Expenses:Fees  1.00 EUR
  Assets:Checking

2015/02/01 * Monthly invest
  Assets:Investments:Bitcoin:20150201  1.84332 BTC @ 217.000 EUR
  Expenses:Fees  1.00 EUR
  Assets:Checking

P 2018/06/08 BTC 6522.600 EUR

2018/06/08 * Realising capital gains
  Assets:Investments:Bitcoin:20150101  -0.50038 BTC @ 266.600 EUR
  Assets:Checking  3,263.78 EUR
  Income:CapitalGains

Now we know we have a single share remaining from our January investment:

$ hledger bal --flat

        3,461.78 EUR  Assets:Checking
         1.00000 BTC  Assets:Investments:Bitcoin:20150101
         1.84332 BTC  Assets:Investments:Bitcoin:20150201
       -1,000.00 EUR  Equity:Opening
            2.00 EUR  Expenses:Fees
       -3,130.38 EUR  Income:CapitalGains
--------------------
         2.84332 BTC
         -666.60 EUR

Virtual accounts for a better overview

Doing this over the course of several years will make your reports pretty verbose. I counter this by transferring all my investments to a virtual account. I do this anyways because in my day to day reports details about my investments aren’t too interesting anyways. My journal might look like this:

commodity EUR
 format 9,999.00 EUR

D 1,000.00 EUR

2015/01/01 * Opening Balance
  Assets:Checking  1,000.00 EUR
  Equity:Opening

2015/01/01 * Monthly invest
  Assets:Investments:Bitcoin:20150101  1.50038 BTC @ 266.600 EUR
  [Assets:Investments:Bitcoin:20150101]  -1.50038 BTC
  [Assets:Savings:Retirement]  1.50038 BTC
  Expenses:Fees  1.00 EUR
  Assets:Checking

2015/02/01 * Monthly invest
  Assets:Investments:Bitcoin:20150201  1.84332 BTC @ 217.000 EUR
  [Assets:Investments:Bitcoin:20150201]  -1.84332 BTC
  [Assets:Savings:Retirement]  1.84332 BTC
  Expenses:Fees  1.00 EUR
  Assets:Checking

Let’s check what the first lot with remaining shares is:

$ bal Assets:Investments:Bitcoin --flat -R | head -n 1

1.50038 BTC  Assets:Investments:Bitcoin:20150101
````

So let's sell from the 2015/01/01 lot:

```ledger
P 2018/06/08 BTC 6522.600 EUR

2018/06/08 * Realising capital gains
  [Assets:Savings:Retirement]  -0.50038 BTC
  [Assets:Investments:Bitcoin:20150101]  0.50038 BTC
  Assets:Investments:Bitcoin:20150101  -0.50038 BTC @ 266.600 EUR
  Assets:Checking  3,263.78 EUR
  Income:CapitalGains

Reports

In my day to day balance report I just want to see the value of all my investents combinded:

$ hledger bal --flat -V

        3,461.78 EUR  Assets:Checking
         2.84332 BTC  Assets:Savings:Retirement
       -1,000.00 EUR  Equity:Opening
            2.00 EUR  Expenses:Fees
       -3,130.38 EUR  Income:CapitalGains
--------------------
         2.84332 BTC
         -666.60 EUR

If I want to see how many shares I have the report looks like this:

$hledger bal --flat --real

        3,461.78 EUR  Assets:Checking
         1.00000 BTC  Assets:Investments:Bitcoin:20150101
         1.84332 BTC  Assets:Investments:Bitcoin:20150201
       -1,000.00 EUR  Equity:Opening
            2.00 EUR  Expenses:Fees
       -3,130.38 EUR  Income:CapitalGains
--------------------
         2.84332 BTC
         -666.60 EUR

I hope this helps a few people. Thanks for the read.

Feel free to join the discussion on Reddit!