Re: [O] Weaving a budget with Org & ledger

[Alan Schmitt]

Hi,

On 2014-02-21 05:59, Erik Hetzner <address@hidden> writes:

> Users of ledger and Org may be interested in this tutorial on how I
> manage an envelope style budget with those two excellent tools.
>
>   http://orgmode.org/worg/org-tutorials/weaving-a-budget.html

Thanks to this nice description, I migrated my finances to ledger since
the beginning of March. I've since written a couple extensions 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).

--8<---------------cut here---------------start------------->8---
#+name: call_ledger
#+begin_src emacs-lisp :var lcmd="bal" :var bucket="Quotidien" :var period=() 
:var bucketp=()
  (let* ((name (org-babel-trim bucket))
         (bname (concat "'^" (when bucketp "Bucket:") "Expenses:" 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-babel-trim (shell-command-to-string cmd))))
    (if (equal res "") 0 res))
#+end_src
--8<---------------cut here---------------end--------------->8---

Second, a function to get an average spending since a given date.
I compute a monthly average, based on the numbers of days since the
starting date.

--8<---------------cut here---------------start------------->8---
#+name: monthly_average_since
#+begin_src emacs-lisp :var start-date="2014-03-01" :var amount=100
  (let ((nbdays (- (time-to-days (current-time))
                   (time-to-days (org-read-date nil t start-date)))))
    (calc-eval "round($ / (12 * ($$ / 365.25)), 2)" nil amount nbdays))
#+end_src

#+name: ledger_average
#+begin_src emacs-lisp :var b="Quotidien" :var sd="2014-03-01"
  (let ((a (org-sbe call_ledger (bucket (eval b)))))
    (org-sbe monthly_average_since (amount (eval a)) (start-date (eval sd))))
#+end_src
--8<---------------cut here---------------end--------------->8---

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..@>$6=vsum(@address@hidden)::@2$3..@>>$3='(org-sbe call_ledger 
(bucket (concat "\"" $1 "\"")) (bucketp (eval t)))::@2$4..@>>$4='(org-sbe 
call_ledger (bucket (concat "\"" $1 "\"")) (period "\"this 
month\""))::@2$5..@>>$5='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) 
(period "\"last month\""))::@2$6..@>>$6='(org-sbe ledger_average (b (concat 
"\"" $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):

--8<---------------cut here---------------start------------->8---
#+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
--8<---------------cut here---------------end--------------->8---

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

--8<---------------cut here---------------start------------->8---
#+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
--8<---------------cut here---------------end--------------->8---

I hope this will be useful to someone, and I want to seize the occasion
to thank everyone on the ledger and orgmode list for putting up with the
many questions I had when I was writing this. I'll gladly take any
suggestion for improving all this.

Best,

Alan