Creating a Budget Template using Org - Part 2
Table of Contents
1. Emacs Strikes Again
While starting this blog and trying to learn a bit about passing tables as list or arrays or values to source blocks to continue where part 1 of budgeting with haskell and org left off. However, it looks like Emacs already has functionality to do sums on tables, like this one below (from dfeich's org-babel-examples):
| Name | number | cost per item | sum | incl VAT |
|---|---|---|---|---|
| name1 | 3 | 1500.00 | 4500.00 | 4860.00 |
| name2 | 9 | 4000.00 | 36000.00 | 38880.00 |
| name3 | 4 | 2800.00 | 11200.00 | 12096.00 |
Then if I hit C-c C-c on the #+TBLFM line, it will place the totals on the last row or column like so:
| Name | number | cost per item | sum | incl VAT |
|---|---|---|---|---|
| name1 | 3 | 1500.00 | 4500.00 | 4860.00 |
| name2 | 9 | 4000.00 | 36000.00 | 38880.00 |
| name3 | 4 | 2800.00 | 11200.00 | 12096.00 |
| 51700.00 | 55836.00 |
' #+TBLFM: @>\(4..@>\)>=vsum(@I..@II);%.2f::@2$4..@4$4=$2*$3;%.2f::@2$5..@4$5=$4*1.08;%.2f
2. Budget Template
| Expense Category | Budgeted Amount | Actual Amount |
|---|---|---|
| Bills | 2000 | |
| Food | 500 | |
| Debt | 1000 | |
| Savings | 500 | |
| Total | 4000 | 0 |
' #+TBLFM: @>\(2..@>\)>=vsum(@I..@II);
Does a fine simple job to calculate inline values. Where the vsum is the calc function used. Which then led me to this rabbit hole of Org as a spreadsheet system, that I didn't know I needed. Which means, my learning haskell goal for the month is just completely thrown out at this point.
3. Setting up the basic summary table
Using the one above will do everything we want for now.
3.1. Differences
| Expense Category | Budgeted Amount | Actual Amount | Difference |
|---|---|---|---|
| Bills | 2000 | 2000 | |
| Food | 500 | ||
| Debt | 1000 | ||
| Savings | 500 | ||
| Total |
' #+TBLFM: ::@2$4=($2)-($3)
This will update as you add in your formula to the table. For this example, where the 2000 is located in Difference, this was typed into the blank field as :=($2)-($3), subtracting the third field in this row ($3) from the second field in this row ($2).
If we then change the #+TBLFM to remove the @2, it will calculate the difference for ALL fields in the fourth ($4) column.
3.2. Total Sums
We have the vsum from before and I can add in like so
| Expense Category | Budgeted Amount | Actual Amount | Difference |
|---|---|---|---|
| Bills | 2000 | 2000 | |
| Food | 500 | 500 | |
| Debt | 1000 | 1000 | |
| Savings | 500 | 500 | |
| Total | 4000 | 4000 |
' #+TBLFM: $4=($2)-($3)::@6$2=vsum(@2$2..@5$2)
By using the same method with the vsum from the example earlier, we can tailor it to use the second row second field (@2$2) through the fifth row second field (@5$2). I did notice, pressing enter is now the correct thing to do without adding a row, but tab works just fine.
4. Next Up
Learning if org table values can be used in other tables. If so, this budget template is in the bag.