Automating allocation reports in Ledger.

Motivation

If you own any stocks or funds you probably want to track the allocation, meaning how much money is in stocks and funds respectively. Some fairly well known low key investment strategies rely on balancing this ratio, for instance this one.

In Ledger, allocations can be modelled easily through allocation based accounts and automatic transactions. This is outlined here. If you do this however, you still have to manually update allocations for every fund you own which is a nuisance, especially if you own something like a target retirement investment fund which naturally and purposefully changes its allocation strategy over time.

In a nutshell, we need an automated way of creating records like this:

= expr ( commodity == 'VFIFX' and account =~ /^Assets:/ )
    (Allocation:Cash)                    0.0178
    (Allocation:Stock:US Stock)          0.5383
    (Allocation:Stock:Non US Stock)      0.34259999999999996
    (Allocation:Bond)                    0.0968
    (Allocation:Other)                   0.0045000000000000005

This would then allow fun queries like

ledger bal Allocation: -X '$'

which roughly yields

200.00$  Allocation
 60.00$    Bond
 20.00$    Cash
  2.00$    Other
118.00$    Stock
 38.00$      Non US Stock
 80.00$      US Stock

or alternatively

ledger bal Allocation: -X '$' -%

which roughly yields

100.00%  Allocation
 30.00%    Bond
 10.00%    Cash
  1.00%    Other
 59.00%    Stock
 19.00%      Non US Stock
 40.00%      US Stock

Automation

A reasonable source for asset allocation per fund is Morningstar. There, each fund has a Portfolio page which shows asset allocation. Let’s try to extract that.

First, let’s use Ledger to determine what kind of commodities we might need to figure out allocation for:

# Determine symbols used in ledger.
symbols = `ledger reg --register-format '%(commodity)\n' | sort | uniq`.split

# Remove currencies.
symbols -= ["EURO", "$", "p$", "$p"]

Then we load the Morningstar page and extract all relevant data using Nokogiri. I used Selenium to do scraping for banks before however here that seems like overkill. The website does not rely on Javascript so much, it really just renders HTML server side which, combined with a DOM parser, allows for very easy data extraction.

symbols.each do |symbol|
  # Query morningstar.
  clean_symbol = symbol.gsub(/[^a-zA-Z0-9]/, "")
  doc = Nokogiri::HTML(open("http://portfolios.morningstar.com/fund/summary?t=#{clean_symbol}&region=usa&culture=en-US"))
  table = doc.xpath("//h3[contains(text(), 'Asset Allocation')]/following::tbody[1]")
  category =  doc.xpath("//*[contains(@class, 'categoryName')]/text()")
  rows = table.xpath(".//tr")
  allocation = {}
  rows.each do |row|
    cells = row.xpath(".//*/text()")
    next if cells.size == 0
    allocation[cells[0].to_s] = cells[1].to_s.to_f * 0.01
  end

  # ...
end

Essentially, we use a few XPath expression to find the relevant data in the page and extract the asset allocation. The code ommitted here is a very dumb heuristic to determine allocation when we are not dealing with a fund or when no data is available. We use the category name, which is usually something like “Total Stock Market Fund” or “Total International Bond Market Fund” as a basis for the allocation decision and otherwise just assume it’s a stock for the simple reason that I don’t trade individual bonds.

As usual, the code for this is here.