Automating commodity prices in Ledger.
21 Nov 2016Motivation
If you use Ledger CLI, chances are you will use more commodities than just dollar. This is actually one of Ledger’s best features. For instance, I track our 401k in Ledger and it allows me to list the ticker symbols of the funds we have purchased inside the 401k as well as the fact that this is pre-tax money. Our opening balances look something like this:
2016/07/19 * Opening Balance
Assets:Checking $200.00
Assets:Investments:401 3.1 VFIFX @ p$29.8
Assets:Investments:401 2.5 VTTSX @ p$29.8
...
Income:Opening Balances
Roughly, this means that we have two different funds in our 401k with ticker
symbols VFIFX and VTTSX respectively. Also, the funds are not valued in dollars but
in p$ which is our commodity for pre-tax money. The idea is that this money,
if accessed today, has a much lower actual value because we’d have to pay
federal, state and penality taxes on it. Therefore, the price database for
this looks like this
P 2016/07/19 p$ 0.55$
P 2016/07/31 VFIFX 30.15p$
P 2016/07/31 VTTSX 30.15p$
where the first row is an approximation of federal tax rate + state tax rate +
10% penalty. The last two rows are the current price in dollars but expressed
at p$ to indicate that we haven’t paid taxes on this yet.
Automation
Now while I am not very concerned about the detailed movement of the funds we own, I want to be able to pull current prices for them without looking up every ticker symbol manually. The following script roughly achieves this goal. A brief walkthrough:
First, we query all commodity names from ledger and convert them into a hashmap.
# Load all commodity names from ledger.
raw = `ledger prices --prices-format '%a %(commodity)\n' --current | sort | uniq | tr -d '"'`
# Convert into hashmap.
symbols = raw.split("\n").map{|x| parts=x.split(" ", 2); { parts[0] => parts[1] } }.reduce(:merge)Some cleanup is required as very few symbols in my ledger file don’t correspond to their Google Finance equivalent.
# Hard-coded mapping of symbols. I use the exact same symbols to track things
# as Google Finance except for euros where I use an abbreviation. Also, I have
# a separate commodity called "pre-tax dollar" for which I have hardcoded the
# conversion (and therefore don't need to look it up here).
mapping = {
"EURO" => "CURRENCY:EURUSD",
"p$" => nil
}
# Remove everything mapping to nil.
symbols.delete_if{ |sym, cur| mapping.has_key?(sym) and mapping[sym].nil? }
# Remember unmapped symbols in the same order as the mapped symbols.
unmapped_lookup_symbols = symbols.keys
# Map all symbols.
lookup_symbols = unmapped_lookup_symbols.map{|sym| mapping.has_key?(sym) ? mapping[sym] : sym }.join(",")Now we just need to retrieve current data and output it in the right format for Ledger’s price db.
# Load data from google finance.
data = open("http://finance.google.com/finance/info?client=ig&q=#{lookup_symbols}").read
data = data.gsub(/\/\//, "") # Kill eval() guard.
data = JSON.parse(data) # Parse to json.
# Ensure we were able to find all symbols. This is mostly so we don't silently
# forget to update any symbols.
raise "Can not resolve all stock symbols" if data.size != symbols.size
# Cache stringified date for today.
date = Time.now.strftime("%Y/%m/%d")
# Output price db entries.
puts
data.each_with_index do |data,index|
unmapped_sym = unmapped_lookup_symbols[index]
puts "P #{date} \"#{unmapped_sym}\" #{data["l"]} #{symbols[unmapped_sym]}"
endAgain, the code for this is here.