Visualise your finances with hledger, InfluxDB, and Grafana

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­vices.in­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:

  • Name: in­fluxdb
  • Type: In­fluxDB
  • Http Set­tings:
    • Url: ht­tp://­loc­al­host:8086
    • Ac­cess: proxy
  • Http Auth: no boxes checked
  • In­fluxDB De­tails:
    • Data­base: fin­ance
    • User: root
    • Pass­word: root
    • De­fault group by time: empty

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 <- H.de­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 = T.re­place "," "_" . T.re­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 (T.in­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)

Graphs

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

  • The monthly budget4:

    Graph showing May budget

    Graph showing May budget

  • Takeaway pur­chases (by fil­tering on the de­scrip­tion):

    Graph showing takeaway purchases

    Graph showing takeaway pur­chases

  • Money I am owed:

    Graph showing money owed to me

    Graph showing money owed to me

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.

Date
Tags
finance, hledger, howto
Target Audience
The intersection of Haskell programmers, personal finance nerds, and graph nerds.