Convalesco | Fetching ledger exchange rates

My tool of choice for personal and freelancing accounting is ledger-cli. Ledger is a simple, command line, double-entry accounting tool for geeks. All entries are written in a text file, in a clean, human readable way. Ledger, the binary, acts as a very powerful parser, consistent with the UNIX philosophy that every tool should do one job and do it well. I wouldn’t recommend ledger for accounting systems that have multiple users but as a personal accounting tool is extremely flexible.

I deal with three currencies on a monthly basis: USD, EUR and BGN. I like using signs instead of codes, so currencies in my ledger file appear as $, € and лв. Ledger understands both notations and it doesn’t really care what kind of currency you are using as long as you let the system know the exchange rate.

Let’s assume we have the following entries:


; create sample assets accounts
account Equity:Opening Balances
account Assets:Business:Checking:BGN
account Assets:Business:Checking:EUR
account Assets:Business:Checking:USD

; create sample expenses accounts
account Expenses:Business:Services
account Expenses:Business:Hardware

2016/01/01 * Open accounts
  ; notes: First entry of personal assets
  Assets:Business:Checking:BGN  лв1500
  Assets:Business:Checking:EUR   €2750
  Assets:Business:Checking:USD  $10000
  Equity:Opening Balances

2016/01/13 * Officer HP Laser printer
  Expenses:Business:Hardware  лв480
  Assets:Business:Checking:BGN

2016/01/14 * X Monthly Subscription
  Expenses:Business:Services  $25
  Assets:Business:Checking:USD

2016/01/15 * HP Proliant Server
  Expenses:Business:Hardware  €1080
  Assets:Business:Checking:EUR

Now a report, an assets balance will look like this:


$ ledger -f sample.ledger bal assets
               $9975
              лв1020
               €1670  Assets:Business:Checking
              лв1020    BGN
               €1670    EUR
               $9975    USD
--------------------
               $9975
              лв1020
               €1670

This is nice because I can see all the available assets, in multiple currencies in a clean, concise way.

Now, I want to know the value of my assets using today’s exchange rates. I can achieve this with ledger, using a specially formatted price database, which is again just a text file and looks like this:


P 2016/11/01 13:42:41 € $1.0946
P 2016/11/01 13:42:41 € лв1.9558

The format is pretty selfexplainatory, P stands for price followed by date, time assets, in my case the € which I’m using a base currency and price of other assets, in my base currency.

Now ledger can deduce the exchange rate of your assets in BGN, USD and EUR. See the example bellow:


$ ledger -f sample.ledger --price-db ~/.prices.db -X € bal assets
              €11304  Assets:Business:Checking
                €522    BGN
               €1670    EUR
               €9113    USD
--------------------
              €11304

$ ledger -f sample.ledger --price-db ~/.prices.db -X $ bal assets
              $12374  Assets:Business:Checking
                $571    BGN
               $1828    EUR
               $9975    USD
--------------------
              $12374

$ ledger -f sample.ledger --price-db ~/.prices.db -X лв bal assets
             лв22109  Assets:Business:Checking
              лв1020    BGN
              лв3266    EUR
             лв17823    USD
--------------------
             лв22109

I made a bash script to fetch exchange rates from fixer.io¹:


#!/usr/bin/env bash

base_currency=EUR
exchange_currencies=(USD BGN)
prices_db="$HOME/.prices.db"

# Define exchange currency symbols
declare -A symbols
symbols[BGN]=лв
symbols[USD]=$
symbols[EUR]=€

# Fetch today's currency exchange rates from Fixer.io
fetch_data() {
  local -r base=$1 currency=$2
  curl -s http://api.fixer.io/latest\?base="$base" | jq .rates."$currency"
}

for i in "${exchange_currencies[@]}"; do
  date=$(date "+%Y/%m/%d %H:%M:%S")
  rate=$(fetch_data $base_currency "${i}")
  echo "P $date ${symbols[EUR]} ${symbols[$i]}${rate}" >> "$prices_db"
done

You can change the currencies and the location of prices.db file accordingly.

All my fleet consists of Macs, so I wrote a launchd script to run the script every day at 01:00 PM:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Label</key>
    <string>com.ledgerFetchRates.cron</string>
    <key>KeepAlive</key>
    <false/>
    <key>RunAtLoad</key>
    <false/>
    <key>UserName</key>
    <string>atma</string>
    <key>Program</key>
    <string>/path/to/fetch_exchangerates_script</string>
    <key>StartCalendarInterval</key>
    <dict>
        <key>Hour</key>
        <integer>13</integer>
        <key>Minute</key>
        <integer>00</integer>
    </dict>
</dict>
</plist>

If you are a newcomer interested in ledger, check out the manual and the plaintextaccounting website.

fixer.io fetches the exchange rates from the ECB in XML and servers in nicely formatted JSON. ↩