Real World Ledger part 1: Weighing Eggs in Baskets


Do you ever feel like you’re losing grip of your personal finances? You deposit into your savings account in one currency, buy stocks and bonds in another, and maybe even hodl on to some cryptocurrency. You keep a finger on the pulse and occasionally check your assets’ value, but volatile prices and exchange rates make it challenging. Ledger is a command line accounting tool that addresses these issues. In this post I’ll introduce you to it.

Introduction

The world around you is changing: interest rates on savings accounts slide to 0%, stock markets bubble and crash, and global political debate intensifies. Above all, your bank or government expects you to pay back that student loan sometime in the future. It is safe to say that our economy is a rough sea.

This post is part one in a series where I show how to map and plan your financial positions in Ledger so you’re able to navigate those real world issues with confidence. How to do that exactly isn’t trivial, though. With this blog series I hope to fill that skills gap—the same that I encountered when starting with Ledger. I found many online examples too abstract and missing human/societal context for someone unfamiliar with accounting.

That said, I won’t be explaining you the theory behind techniques like double-entry accounting and investment portfolios in this post because I am neither an accountant nor am I an investment advisor. The Ledger documentation does a good job at explaining double-entry1 and Investopedia explains portfolios well2. I will limit myself to the narrative and practical examples—those are the things I’ve personally experienced.

Part 1: Weighing Eggs in Baskets

The example story through this blog series features the development of a basic financial situation into an investment portfolio with carefully weighted stocks, government bonds, cryptocurrency, and of course cash. All is fine and balanced until a cryptocurrency hype comes knocking at the door and exposes the portfolio manager, you, to a novel risk.

We get started in this post by diversifying our savings into stocks, bonds, and cryptocurrency. This way, we won’t have all our eggs in one basket. We also discuss how you convert different assets to one currency so we can realiably weigh them: apples to apples. The weighing is essential when creating your investment portfolio allocation—which we’ll discuss in the next post.

“Ledger is a powerful, double-entry accounting system that is accessed from the UNIX command-line.” — https://www.ledger-cli.org

To follow along with the story below, you will need a terminal with Ledger installed and a plain text file editor, such as Sublime Text. If you use macOS, installing Ledger is easy using Homebrew: brew install ledger. Feel free to make your workflow more pleasant by installing a Ledger mode in your text editor—this gives you syntax highlighting. Sublime and other well-known editors (like Emacs and vim) have Ledger modes readily available online.

The basics: single currency and single asset class

Our first posting: adding our savings account

Let’s start simple: we have a savings account at a bank called ASN bank3 in our home country where most of our money resides. This account already has money in it—obviously we don’t start owning assets the moment we begin using Ledger—so we have to initialize our balance by moving money from somewhere. Idiomatically that somewhere is an account called ‘opening balances’. When we express this in Ledger, this is what the file postings1.dat (.dat is commonly used with Ledger, but feel free to use something else like .txt) looks like:

2018-01-01 Opening Balances
    Assets:NL:ASN:Savings                 € 1,337.00
    Equity:Opening Balances              € -1,337.00

How do we interpret these three lines? Every posting has a date (2018-01-01) and a payee (Opening Balances). Then, what follows directly beneath it are the entries belonging to that posting. In this case we move the € 1337 from ‘opening balances’ to the savings account. Most of the labels here are arbitrary and depend on your preference and taste. I like to structure actual bank accounts as follows: country, name of bank, type of account. That results in Assets:NL:ASN:Savings.

Now we run our first query using the Ledger command line tool. We ask for the balance of accounts that match assets in the file postings.dat.

ledger --file postings1.dat balance assets

The result, as expected, the balance of one asset account:

          € 1,337.00  Assets:NL:ASN:Savings

Our first mutation: interest from savings, and deposits and withdrawals

Fast forward 6 months. We have received some interest from the bank and did a couple of deposits and withdrawals. We could add postings for all the deposits and withdrawals, but that’s a lot of premature work and definitely not the required to benefit from Ledger. That’s why we’re using an ‘adjustment’ account in the following addition to our postings1.dat file, calling it postings2.dat.

2018-06-01 ASN
    Assets:NL:ASN:Savings                 € 3,787.50
    Income:Interest                         € -42
    Equity:Adjustment

A net amount of € 3,787.50 was added to the savings account, of which € 42 was interest received on the principal. The rest was the result of deposits and withdrawals. We don’t really care about tracking all those transactions in detail right now, so we lazily use an adjustment account. Lastly, we’re able to omit the amount for Equity:Adjustment because there’s only one possibility: € -3,787.50 - € 42 = € -3,745.5.

The adjustment account resolves a common discouragement of adopting Ledger that I keep hearing—people think that Ledger requires them to arduously type in all transactions like a monkey. You don’t, and above all you can always do that later or build scripts to do it for you should you so desire.

We now rerun the Ledger command line tool. This time, we ask for the balance of all accounts, not just assets:

ledger --file postings2.dat balance

Please note that the total of all accounts always sums to zero—that condition is the main property of double-entry accounting:

          € 5,124.50  Assets:NL:ASN:Savings
         € -5,082.50  Equity
         € -3,745.50    Adjustment
         € -1,337.00    Opening Balances
            € -42.00  Income:Interest
--------------------
                   0

Going deeper: multiple currencies and asset classes

Diversifying into multiple assets

We decided to diversify, hoping to get a better return than the ~0% interest rate on your savings account4 in our ~2% inflation habitat5. But, at the same time, you don’t want to go all-in on stocks because it’s generally considered a bad idea to put all your eggs in one basket. That’s why we diversify and buy some government bonds and cryptocurrency too. ‘Interactive Brokers’ and ‘Binck Bank’ in the file below are examples of stock/bonds brokers. postings3.dat:

2018-07-01 Interactive Brokers
    Assets:NL:ASN:Savings                   € -1,285
    Assets:US:Interactive Brokers:Cash       $ 1,500

2018-07-02 Binck Bank
    Assets:NL:ASN:Savings                   € -2,000
    Assets:NL:BinckBank:Cash

2018-07-03 Interactive Brokers
    Assets:US:Interactive Brokers:Stocks      6 AAPL @ $ 183.92
    Assets:US:Interactive Brokers:Cash

2018-07-04 Binck Bank
    Assets:NL:BinckBank:Bonds      1,100 "NL2014-47" @ € 1.39
    Assets:NL:BinckBank:Stocks                5 HEIA @ € 86.08
    Assets:NL:BinckBank:Cash

2018-07-05 Coinbase
    Assets:Cryptocurrency:BTC wallet         BTC 0.1
    Assets:NL:ASN:Savings                     € -561

In the example above we use different syntax to reach the same goal: buying one commodity by selling another commodity (such as stocks from US dollars and Bitcoin from euros). The Ledger docs explain the differences clearly6.

Let’s check the impact of our asset diversification buying spree on our balance:

ledger --file postings3.dat balance assets --no-total --flat

Please be advised that I passed two new arguments: --no-total and --flat. The total is superfluous because we’re only looking at assets. Conversely, the total is valuable when you’re looking at both assets and liabilities. Subtracting them yields net worth7. And --flat is purely aesthetic. It suppresses Ledger’s automatic hierarchy view because it is confusing when printing heterogenous commodities (such as currencies, stocks, etc.).

             BTC 0.1  Assets:Cryptocurrency:BTC wallet
          € 1,278.50  Assets:NL:ASN:Savings
     1,100 NL2014-47  Assets:NL:BinckBank:Bonds
             € 40.60  Assets:NL:BinckBank:Cash
              5 HEIA  Assets:NL:BinckBank:Stocks
               $ 396  Assets:US:Interactive Brokers:Cash
              6 AAPL  Assets:US:Interactive Brokers:Stocks

This balance sheet matches our expectations but it isn’t giving us much extra information about each of the assets relative to each other—value wise we’re comparing apples to oranges. Wouldn’t it be nice to have all the assets converted to one currency so we can compare apples to apples?

Implicit and explicit market prices

In order to compare values of assets we have to pick a base currency to convert them to. I’m carrying a Dutch passport so my usual pick is to convert everything to euros. But, as long as you supply Ledger the exchange rates, you could express the value of your assets, even your guitar if you’re so inclined, in whatever commodity you like—from Apple stock to real apples8. Obviously your tools shouldn’t stop you from expressing the value of your guitar in apples that you pick from the tree! The only thing Ledger needs is either an implicit or explicit market price.

We’ll discuss prices in a moment. Before, to see the value of our assets expressed in euros, we run the following command (adding --exchange €):

ledger -f postings3.dat b Assets --exchange--no-total

Finally we have a birds eye view of all our assets’s value across different countries, accounts, and currencies:

          € 5,124.50  Assets
            € 561.00    Cryptocurrency:BTC wallet
          € 3,278.50    NL
          € 1,278.50      ASN:Savings
          € 2,000.00      BinckBank
          € 1,529.00        Bonds
             € 40.60        Cash
            € 430.40        Stocks
          € 1,285.00    US:Interactive Brokers
            € 339.65      Cash
            € 945.35      Stocks

How did Ledger convert everything to euros? Ledger keeps track of prices implicitly and also allows you to specify prices manually—explicitly. Let’s focus on the implicit part first, by asking Ledger for the prices that it stored so far:

ledger -f postings3.dat prices

With this command you peek into Ledger’s internal price database. The prices that you see were established by the postings in postings3.dat and are all implicit:

2018/07/01 €        $ 1.167315175097
2018/07/03 AAPL         $ 183.92
2018/07/04 "NL2014-47"       € 1.39
2018/07/04 HEIA          € 86.08
2018/07/05 BTC        € 5,610.00

As a matter of experiment, let’s say the price of Apple stock recently shot up. It rose to an extent that we’re now curious to see how much the value of our US brokerage account increased. To find out, we’re going to explicitly express Apple’s stock price in US dollars in a new file called prices.dat:

P 2018-08-03 AAPL $ 207.99

The single line in this file states: on 2018-08-03 the price for AAPL in $ was 207.99. Let’s make this file available to Ledger by specifying --price-db and querying assets in the US (in which Apple belongs) only (Assets:US):

ledger --file postings3.dat \
       balance Assets:US \
       --exchange\
       --price-db prices.dat \
       --no-total

Indeed, we see the gains on Apple stock reflected by our increased total US assets value. Apple stock got converted to US dollars got converted to euros:

          € 1,408.72  Assets:US:Interactive Brokers
            € 339.65    Cash
          € 1,069.07    Stocks

You should add a line to prices.dat for every price that you want to track. I personally have more than a thousand lines in my prices file and retrieve some prices automatically using APIs (predominantly forex rates). The benefit of a high resolution like that is that graphical plots of my assets, liabilities, and net worth (using a daily interval on the x-axis) are less jumpy.

Conclusion

To summarize, we’ve just created our first postings, discovered the implicit exchange rates that Ledger keeps and added an Apple stock price explicitly. All along the way we were able to query our balance in two representations: in its original commodity and converted to one base currency.

In part 2 we’ll look at how you materialize an investment portfolio strategy and asset allocation using Ledger. Please leave your email adress if you want a notification once it’s published! I’d also love to hear your feedback about this post and hear suggestions about topics that you’d like to see discussed in depth. Reach out to me on Twitter: @ppnlo. Or through email: replace the first dot in the domain name with an @.

Feel free to subscribe: I'll notify you when publish the next part

* indicates required

Appendix

As always, this post is written in a literate programming9 style, which means that the code samples in it are reproducible and correct. Check out the Org-mode and Babel source code on GitHub: real-world-ledger-part-1.org.

Thank you Thomas Smolders, Pieter Levels, Arend Koopmans, Rik Helwegen and Nils Mackay for helping me with this post!

Footnotes

1 https://www.ledger-cli.org/3.0/doc/ledger3.html

2 https://www.investopedia.com/terms/p/portfolio.asp

3 Eerlijke Bankwijzer: ASN Bank

4 Interest rates for ABN Amro savings accounts, similar to other Dutch banks: https://www.abnamro.nl/en/personal/savings/spaarrente.html

5 CBS inflation

6 https://www.ledger-cli.org/3.0/doc/ledger3.html#Explicit-posting-costs

7 https://en.wikipedia.org/wiki/Net_worth

8 https://www.ledger-cli.org/3.0/doc/ledger3.html#Posting-costs

9 https://en.wikipedia.org/wiki/Literate_programming