Using Org for Ledger Reports and Budget

body

Here are some org code blocks to track expenses, what remains in the envelopes (that I call "buckets"), and to generate monthly budgets. They make extensive use of the babel features of org mode (all the following should be in an orgmode file).

First, I wrote a function that does an external call to ledger and returns the last value. It's used to get a balance for a given period. The bucketp boolean indicates whether we want information about the bucket (Bucket:Expenses:Foo) or the expenses themselves (Expenses:Foo). 

#+name: call_ledger
#+begin_src emacs-lisp :var lcmd="bal" :var bucket="Quotidien" :var prefix="Expenses:" :var period=()
  (let* ((name (org-trim bucket))
         (bname (concat "'^" prefix name "'"))
         (ledger "ledger -f ~/Documents/Org/mescomptes.ledger")
         (parg (when period (concat " -p '" period "'")))
         (cutcmd "tail -1 | cut -d ' ' -f 2")
         (cmd 
          (concat ledger " -J " parg " " lcmd " " bname " | " cutcmd))
         (res (org-trim (shell-command-to-string cmd))))
    (if (equal res "") 0 res))
#+end_src

Second, a function to get an monthly average spending since a given date. 

#+name: monthly_average
#+begin_src emacs-lisp :var starty=2014 :var startm=3 :var amount=100 
  (let* ((tm (decode-time))
         (cmonth (nth 4 tm))
         (cyear (nth 5 tm))
         (nbmonths (+ (* 12 (- cyear starty)) (- cmonth startm))))
    (calc-eval "round($ / $$, 2)" nil amount nbmonths))
#+end_src

#+name: ledger_average
#+begin_src emacs-lisp :var b="Quotidien" :var sy=2014 :var sm=3  :var sd="2014-03-01" :var p="Expenses:"
  (let* ((per (format "from %d-%d-01 to this month" sy sm))
         (a (org-sbe call_ledger (bucket (eval b)) (prefix (eval p)) (period (eval per)))))
    (org-sbe monthly_average (amount (eval a)) (starty (eval sy)) (startm (eval sm))))
#+end_src

Using these functions, I can write a table where I input the bucket names and the monthly budget I want (first two columns). The table formula then computes the remaining amount in the buckets, the actual spending for this month, the spending for last month, and the average spending since march 2014 (last four columns). 

#+name: budget
| Bucket | Planned | Remaining | This Month | Last Month | Average |
|--------+---------+-----------+------------+------------+---------|
| Foo    |    1200 |      18.7 |    1196.44 |    1196.44 | 1549.64 |
| Bar    |      85 |    254.63 |          0 |       17.4 |   11.27 |
|--------+---------+-----------+------------+------------+---------|
| Total  |         |           |            |            |         |
#+TBLFM: @2$3..@>>$3='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (prefix "\"Bucket:Expenses:\""))
#+TBLFM: @2$4..@>>$4='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (period "\"this month\""))
#+TBLFM: @2$5..@>>$5='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (period "\"last month\""))
#+TBLFM: @2$6..@>>$6='(org-sbe ledger_average (b (concat "\"" $1 "\"")))
#+TBLFM: @>$2..@>$6=vsum(@2..@-1)

Using this, I can track my expenses, see how much I've still to spend, and study the consistency between the actual spending and the budget.

I then use the table to create my monthly filling of buckets with the function (the "Bucket:Unallocated:EUR" is where I put my income, before it's allocated to some bucket): 

#+name: ledger_budget
#+BEGIN_SRC emacs-lisp :results output :var table=budget :var year=2014 :var month=04
  (princ (format "%d-%02d-01 * Budget %d %02d\n" year month year month))
  (mapcar
   (lambda (tuple)
     (princ (format "    Bucket:Expenses:%s    %d €\n" (car tuple) (cadr tuple))))
   (butlast (cdr table) 1))
  (princ "    Bucket:Unallocated:EUR\n")
#+END_SRC

Then I can evaluate the following call line, which generates the budget that I copy and paste to my ledger file. 

#+call: ledger_budget(year=2014, month=04) :wrap src ledger

#+results:
#+BEGIN_src ledger
2014-04-01 * Budget 2014 04
    Bucket:Expenses:Foo    1200 €
    Bucket:Expenses:Bar    85 €
    Bucket:Unallocated:EUR
#+END_src