Visualise your finances with hledger, InfluxDB, and Grafana

Tags finance, hledger, howto
Target Audience The intersection of Haskell programmers, personal finance nerds, and graph nerds.
Graph of my saved money

Graph of my saved money

hledger is a plain-­text ac­counting tool for keeping track of your fin­ances. I’ve been using it for nearly a year now, and am pretty sold on it. Grafana is a graphing tool which sup­ports data from a variety of sources, which I’ve re­cently be­come a fan of.

Wouldn’t it be nice if we could get Grafana and hledger talk­ing, and graph our fin­ances?

Well, we can! We’ll use In­fluxDB as the data source (Grafana doesn’t store the data by it­self), so first we need to set that up. If you’re on NixOS add these two lines to your con­fig­ur­a­tion.nix:

ser­­fluxdb.en­able = true;
ser­vices.grafana.en­able  = true;

This will start In­fluxDB and Grafana, the latter ac­cess­ible at ht­tp://­loc­al­host:3000, with de­fault pass­words. See the se­curity op­tions if you want to run these in pro­duc­tion. If you’re not on NixOS, you’ll have to check your doc­u­ment­a­tion, but it’s prob­ably simple.

Get­ting In­fluxDB and Grafana talking

Firstly, create a data­base in In­fluxDB called “fin­ance”.

Then, open Grafana (ht­tp://­loc­al­host:3000 by de­fault) in your web browser. You’ll be prompted to add a data source. The form is pretty clear but, just in case you have any dif­fi­culty, here are the set­tings I used for the de­fault In­fluxDB con­fig­ur­a­tion:

The page will up­date to tell you if there is a prob­lem.

Get­ting hledger and In­fluxDB talking

Let’s work through a small pro­gram to copy hledger data to In­fluxDB to­gether. Firstly, we’ll need the hledger­-lib and in­fluxdb pack­ages from Hack­age. Then there’s some boil­er­plate of im­ports and such­like:

{-# LAN­GUAGE Over­loaded­Strings #-}

im­port Data.­Func­tion (on)
im­port Data.List (inits, groupBy, ma­pAc­cumL, nub)
im­port qual­i­fied Data.Map as M
im­port Data.String (from­String)
im­port qual­i­fied Data.­Text as T
im­port Data.­Time.C­lock (UTC­Time(..))
im­port Data­base.In­fluxDB as I
im­port Hledger­.Data.­Types as H
im­port Hledger­.Read as H

Be­cause I am a lazy person who does not like writing pro­grams which do more than I need, our main func­tion will just dump the en­tire con­tents of the de­fault journal into In­fluxDB:

main :: IO ()
main = do
  journal <-­fault­Journal
  let meas­ure­ments = toMeas­ure­ments (H.jtxns journal)
  I.write­Batch (I.write­Params "fin­ance") meas­ure­ments
  put­StrLn $ "Wrote " ++ show (length meas­ure­ments) ++ " meas­ure­ments."

Both the hledger and In­fluxDB APIs are in­volved here, so let’s un­pack it a little:

  1. Read the de­fault journal file, which is in the LEDGER­_­FILE en­vir­on­ment vari­able.
  2. Con­vert all the trans­ac­tions into In­fluxDB meas­ure­ments.
  3. Write all those meas­ure­ments to the In­fluxDB “fin­ance” data­base.

A fan­cier main func­tion would allow giving a date to in­clude trans­ac­tions after (so you can run this daily), and the name of the data­base.

Re­porting deltas

The magic all hap­pens in the toMeas­ure­ments func­tion:

toMeas­ure­ments :: [H.Trans­ac­tion] -> [I.Line UTC­Time]
toMeas­ure­ments = map toIn­flux

Ac­cording to the best prin­ciples of func­tional pro­gram­ming, we’ll just have that be a small func­tion which does one thing and one thing well, by com­posing ex­isting func­tions. We’ll work through a few ver­sions of this. The real magic all hap­pens in­side the toIn­flux func­tion:

toIn­flux :: H.Trans­ac­tion -> I.Line UTC­Time
toIn­flux txn = Line "delta" tags fields (Just time) where
  time   = UTC­Time (H.tdate txn) 0
  tags   = M.singleton "de­scrip­tion" (fixup (H.tde­scrip­tion txn))
  fields = fmap I.Field­Float (toDeltas txn)

For each trans­ac­tion we pro­duce a Line, which is one In­fluxDB meas­ure­ment. A line has a meas­ure­ment name, some tags, some fields, and the time the meas­ure­ment was taken. We have one tag, the de­scrip­tion, and for the time we’re using mid­night on the day of the trans­ac­tion1.

The fixup func­tion re­moves commas and spaces (for­bid­den!) from the de­scrip­tion:

fixup :: T.Text -> T.Text
fixup =­place "," "_" .­place " " "_"

The really real magic hap­pens in toDeltas, which takes a trans­ac­tion and pro­duces the col­lec­tion of bal­ance changes:

toDeltas :: H.Trans­ac­tion -> M.Map I.Key Double
toDeltas txn =
    let post­ings = con­catMap ex­plodeAc­count (H.tpost­ings txn)
        ac­counts = nub (map H.pac­count post­ings)
    in M.from­List [ (from­String (T.un­pack a), val)
                  | a <- ac­counts
                  , let ps  = filter ((==a) . H.pac­count) post­ings
                  , let val = sum (map (value . H.pamount) ps)

We’re pro­du­cing one entry in our map for every ac­count in­volved in the trans­ac­tion, where the value is the total change to that ac­count. The closest type of value, which In­fluxDB sup­ports, to what we want is double2.

Grafana cannot sum dif­ferent timeseries as far as I’m aware (there is a fea­ture re­quest open for timeseries arith­met­ic), so we need to ag­gregate changes in sub­ac­counts into changes in su­per­ac­counts ourselves. If I re­port a de­posit of £x into as­set­s:sant­and­er­:­main, I also want that to show up as a £x in­crease in as­set­s:sant­ander and as­sets. The ex­plodeAc­count func­tion du­plic­ates post­ings to achieve this:

ex­plodeAc­count :: H.Posting -> [H.Posting]
ex­plodeAc­count p =
  [ p { H.pac­count = a }
  | a <- tail . map (­ter­calate ":") . inits . T.splitOn ":" $ H.pac­count p

Fi­nally we have value, which con­verts an hledger value into a double:

value :: H.MixedAmount -> Double
value (H.Mixed amounts) = sum (map go amounts) where
  go (H.Amount "£" q _ _) = from­Ra­tional (toR­a­tional q)
  go (H.Amount _   _ (H.Total­Price a) _) = go a

This is par­tial but, again, I am lazy and it works for me.

Here’s what the graph from the start looks like:

Graph of my saved money (deltas)

Graph of my saved money (deltas)

A bit weird, but it makes sense. We’re only re­porting the deltas after all. It’s also a little mis­lead­ing, as I’m not con­tinu­ously de­pos­iting £200 into my sav­ings ac­count, only once a month. Turning on points in the graph dis­play il­lus­trates this:

Graph of my saved money (deltas) (with points)

Graph of my saved money (deltas) (with points)

Re­porting totals

Grafana does have a cu­mu­lat­ive_sum op­er­a­tion, but I couldn’t figure out how to get it work­ing. To avoid me having to learn how to use my tools, the next step is to re­port both the total and the delta:

toMeas­ure­ments :: [H.Trans­ac­tion] -> [I.Line UTC­Time]
toMeas­ure­ments = concat . snd . ma­pAc­cumL toIn­flux M.empty

toIn­flux :: M.Map I.Key Double -> H.Trans­ac­tion -> (M.Map I.Key Double, [I.Line UTC­Time])
toIn­flux bals txn = (bals', map toLine [("total", fieldsT), ("delta", fieldsD)]) where
  toLine (k, fs) = Line k tags fs (Just time)
  time    = UTC­Time (H.tdate txn) 0
  tags    = M.singleton "de­scrip­tion" (fixup (H.tde­scrip­tion txn))
  fieldsT = fmap I.Field­Float bals'
  fieldsD = fmap I.Field­Float deltas
  bals'   = M.uni­on­With (+) bals deltas
  deltas  = toDeltas txn

This looks more com­plex, but really is­n’t. We just use the ma­pAc­cumL3 func­tion to keep a run­ning total, and re­turn two Line values rather than the one.

Now, by using the “total” meas­ure­ment rather than the “delta”, we get the graph from the start. The deltas are still avail­able in case they’re use­ful.

Daily ag­greg­ates

Un­for­tu­nately, this doesn’t work so well on ac­counts which have mul­tiple trans­ac­tions on a single day. This is be­cause we re­port all our daily trans­ac­tions as hap­pening at the same in­stant, which makes the graph look a little odd. Here’s my cur­rent ac­count on the 3rd of Janu­ary:

Graph showing multiple transactions on the same day

Graph showing mul­tiple trans­ac­tions on the same day

We can get the trans­ac­tions for that day from In­fluxDB with a query in its SQL-­like lan­guage:

SE­LECT de­scrip­tion, as­sets FROM total WHERE time = '2017-01-03'

We get these points:

eSaver­_ini­tial        4885.6900000000005
Grand­ma_in­her­it­ance   6885.6900000000005
Tu­ition               5855.6900000000005
Hol­gate_rent          4505.6900000000005
Arch_Hurd_­Google_Apps 4502.9400000000005
Linode                4484.900000000001

So first we gain £2000, then we spend more than that. The lines Grafana draws don’t help mat­ters either. Be­cause I still don’t know how to do cu­mu­lative sums, we can solve this by re­port­ing, in ad­di­tion to the raw data, a daily ag­greg­ate:

toMeas­ure­ments :: [H.Trans­ac­tion] -> [I.Line UTC­Time]
toMeas­ure­ments txns =
  let daily   = groupBy ((==) `on` H.tdate) txns
      squish  = \ts@(t:_) -> t { H.tde­scrip­tion = "ag­greg­ate", H.tpost­ings = con­catMap H.tpost­ings ts }
      raw     = concat . snd $ ma­pAc­cumL (toIn­flux "raw_total" "raw_delta") M.empty txns
      run­ning = concat . snd $ ma­pAc­cumL (toIn­flux "ag­g_total" "ag­g_delta") M.empty (map squish daily)
  in raw ++ run­ning

toIn­flux :: I.Key -> I.Key -> M.Map I.Key Double -> H.Trans­ac­tion -> (M.Map I.Key Double, [I.Line UTC­Time])
toIn­flux keyT keyD bals txn = (bals', map toLine [(keyT, fieldsT), (keyD, fieldsD)]) where
   -- omitted

So we pro­duce two col­lec­tions of meas­ure­ments: the raw and the daily ag­greg­ate. Now the 3rd of January looks sens­ible:

Graph showing multiple transactions on the same day (aggregate)

Graph showing mul­tiple trans­ac­tions on the same day (ag­greg­ate)


Now we can graph pretty much any as­pect of our fin­ances. Like…

I’m not sure if I’ll keep ex­porting my journal changes to In­fluxDB, but this could be a good way to spot longer­-term trends. It’s cer­tainly a nice way to get a quick sum­mary of what’s hap­pen­ing.

  1. Ar­gu­ably, it should be 23:59 rather than 00:00, as the trans­ac­tions happen by the end of the day, not at its start. It doesn’t matter too much as long as we’re con­sist­ent.

  2. Yes, yes, floating point for cur­rency is bad. This is only the type we use for metric re­port­ing, hledger uses exact decimal values in­tern­ally.

  3. I al­ways feel slightly ex­cited when I use ma­pAc­cumL. It’s such an exotic func­tion!

  4. Don’t worry, I have sev­eral days of food in the fridge/­freezer. I’m not on the brink of star­va­tion.