Lots of Small Spreadsheets
Since falling in love with org-mode many years ago, I keep everything
as text files, and I do mean everything. I’ve even converted my
notes associated with my taxes into a single org-mode
text file
instead of loading up a big-app spreadsheet.
Normally, I export selected tables, and copy them into an email for my accountant, but this year, all documents like that needed to be uploaded as a PDF. Sure, I could use a word processor, but why would I do that when I could shave this perfectly fine gnu staring me in the face?
My problem was exporting summary tables from the document, leaving out most of the details. Here are some of my tips.1
Tables in org-mode
Let’s begin with a brief overview of tables in org-mode
. If you
are already familiar, skip ahead.
Tables are little-more than vertical pipes in a normal text
file. However, org-mode
will keep keep them nicely formatted.
For instance, typing this:
| Date|Category|Amount |- |▯
And then hitting tab, will produce this:
| Date | Category | Amount | |------+----------+--------| | ▯ | | |
Enter data in your table and use TAB to jump from cell to cell, and don’t worry about the formatting, for as soon as you enter this:
| Date | Category | Amount | |------+----------+--------| | 2015-03-12 Thu | | |
The TAB key will reformat the table, and you’ll have:
| Date | Category | Amount | |------------------+----------+--------| | 2015-03-12 Thu | | |
You can convert existing data into a table. For instance, select a
region covering an embedded CSV, and call
org-table-convert-region
, to go from:
a,b,c,d 1,2
To:
| a | b | c | d | | 1 | 2 | | |
Spaces instead of commas? No problem. If the function doesn’t find
any commas in the region, it changes to spaces. If you have commas,
but the separators are tabs, type C-u
first, to go from:
a x,y,z c 1 2
To:
| a | x,y,z | c | | 1 | 2 | |
Tables in org-mode
isn’t just about formatting data, as it has a
number of spreadsheet-like features. For instance, it you place
the cursor on the third column in this table:
| Date | Category | Amount | |------------+------------------+--------| | 2014/01/14 | Supplies | 43.97 | | 2014/02/15 | Supplies | 56.48 | | 2014/02/11 | Book | 17.99 | | 2014/06/10 | Kinesis Keyboard | 289.16 | | 2014/08/22 | Books | 18.99 | | 2014/08/23 | Printer | 99.96 | | 2014/08/25 | Books | 7.50 | | 2014/08/30 | Supplies | 58.26 | | 2014/09/15 | Books | 21.49 | |------------+------------------+--------| | | Total: | |
And hit C-c +
, the following message appears:
Sum of 9 items: 613.8 (C-y will insert result into buffer)
This allows you to move to the last row in that row, and insert the
result into that cell. The downside is that once you insert the sum
into the bottom cell, but later change a value above, C-c +
will
add the old summed value! The work around is to remove the sum
first.
A better approach is to specify a table formula, just like you
probably would with regular spreadsheet application. Since
org-mode
is a text file, we have to put those formulas
somewhere, so they are appended to the table, as in:
| Date | Category | Amount | |------------+------------------+--------| | 2014/01/14 | Supplies | 43.97 | | 2014/02/15 | Supplies | 56.48 | | 2014/02/11 | Book | 17.99 | | 2014/06/10 | Kinesis Keyboard | 289.16 | | 2014/08/22 | Books | 18.99 | | 2014/08/23 | Printer | 99.96 | | 2014/08/25 | Books | 7.50 | | 2014/08/30 | Supplies | 58.26 | | 2014/09/15 | Books | 21.49 | |------------+------------------+--------| | | Total: | 613.8 | TBLFM: @>$3=vsum(@2..@-1)
Then you place the cursor on the #+TBLFM
line, and hit C-c C-c
to recalculate your spreadsheet. What does that alchemical incantation?
First, the = sign specifies a cell on the left hand side and its
value on the right hand side. Let’s break up the rest:
@>
The@
specifies a row, and this refers to the last row in a table.$3
The$
specifies a column, so this refers to the third column.vsum
A vertical sum function with parameters given in parenthesis@2
The second row. Notice that it theAmount
header is@1
and the dashes separating the header from the body is ignored.@-1
The next to the last row. Using these relative references mean that we can add rows to our table, and still have the sum formula work.
At this point, I guess I would recommend the online documentation.
Tables as Spreadsheet
Most of my little spreadsheets look similar to the following:
NAME: supplies | Date | Category | Amount | |------------+------------------+--------| | 2014/01/14 | Supplies | 43.97 | | 2014/02/15 | Supplies | 56.48 | | 2014/02/11 | Book | 17.99 | | 2014/06/10 | Kinesis Keyboard | 289.16 | | 2014/08/23 | Printer | 99.96 | | 2014/08/30 | Supplies | 58.26 | | 2014/08/22 | Books | 18.99 | | 2014/08/25 | Books | 7.50 | | 2014/09/15 | Books | 21.49 | | 2014/12/31 | Toner Service | :=24.95*4 | |------------+------------------+--------| | | Total: | | TBLFM: @>$3=vsum(@2..@-1);%.2f
Couple things to notice:
- Name your tables. This is important later when we extract data from them.
- Need a calculator, e.g. your cell phone bill is the same $74.53
for every month? Enter:
:=74.53*12
and hitC-c C-c
, and that formula is replaced with its answer. In the example above, I pretend to have a quarterly service bill for toner. - As described above, the
#+TBLFM:
line states that the last row (@>
) in the third column ($3
) is a sum of its second row (@2
) through to its next to the last row (@-1
). - The code
%.2f
at the end of the sum, means to format the value with two decimal places. Yes, I can be that pedantic even in my own notes. - Hit
C-c C-c
calculates the formulas that the bottom of the table.
Once I have a document full of these little specific tables, I
create a table that lists collects all of my expenses by
category. For instance, the sum of the table above (named
supplies
) should be inserted into the fourth data row of this
table:
NAME: expenses | Category | Amount | |-----------------------+--------| | Domain Registration | | | Fax Service | | | Hosting Services | | | Office Expenses | | | Internet Connectivity | | |-----------------------+--------| | Total: | 0.00 | TBLFM: @>$2=vsum(@2..@-1);%.2f
However, I don’t want to hunt and copy the sums from those five tables. Instead I tell Emacs to do it:
NAME: expenses | Category | Amount | |--------------------------+------------------------------| | Office Supplies/Expenses | :=remote(supplies,@>$3);%.2f | | Domain Registration | :=remote(domain,@>$4);%.2f | | Fax Service | :=remote(faxes,@>$2);%.2f | | Hosting Services | :=remote(hosting,@>$3);%.2f | | Internet Connectivity | :=remote(internet,@>$3);%.2f | |--------------------------+------------------------------| | Total: | 0.00 | TBLFM: @>$2=vsum(@3..@-1);%.2f
On the first data row, I enter :=remote(supplies,@>$3)
to get a
remote cell value from the table labeled supplies
from the
cell in the last row (@>
) and third column ($3
), and then to
format it to two decimals.
I do that for every table, and hit C-c C-c
and it grabs them all,
and I’m left with a pretty little table, with a horrific tail
(which I’ll chop off):
NAME: expenses | Category | Amount | |-----------------------+---------| | Domain Registration | 87.15 | | Fax Service | 119.40 | | Hosting Services | 251.84 | | Office Expenses | 433.81 | | Internet Connectivity | 1510.14 | |-----------------------+---------| | Total: | 2402.34 | TBLFM: @>$2=vsum(@2..@-1);%.2f::@2$2=remote(domain,@>$4);%.2f:: ...
Why yes, I do just copy this tax file from one year to the next, wiping out the old data, and filling it throughout the year.
Keeping Things Private
Scattered throughout my file is a lots of little notes, with
hyperlinks to online accounts, etc. No need to send that stuff, but,
by default, the org-mode
exporter exports everything.
Let’s start hiding things from the exporter.
Hiding Sections
Each section can be tagged by hitting C-c C-c
on the
header. Sections with a :noexport:
tag are automatically not
exported:
◈ Office Supplies : noexport :
Hiding Paragraphs
Sometimes I have just a couple of paragraphs that shouldn’t be
exported. For these, I put in a drawer called PRIVATE
:
:PRIVATE: The new web site for the online portal for the ... :END:
This feature has the added advantage that you can temporary hide it
from yourself with a press of the Tab
key.
To stop this drawer from being exported, add the following two lines at the top of your file:
DRAWERS: PRIVATE OPTIONS: d:nil
Now, drawers will not be exported.
Summary
In conclusion, I … I … have no idea how to summarize these
org-mode
features. Is this easier than using an actual
spreadsheet? Yes, and no. I spend all day in Emacs, taking all sorts
of notes and linking all my text files, so I’m used to this sort of
prestidigitation.
Besides, these sorts of tables make great templates2, so not a lot of finger effort is involved.
Footnotes:
Why yes, all of the values and other data points are made up.
Here is a mighty fine yasnippet
for my standard, two-colum table:
# -*- mode: snippet; require-final-newline: nil -*- # name: table2 # key: table2 # -- NAME: $1 | Category | Amount | |-----------------------+--------| | $0 | | |-----------------------+--------| | Total: | 0.00 | TBLFM: @>\$2=vsum(@2..@-1);%.2f
Note that the table is un-aligned on purpose, and the use of \$2
allows me to put the $2
directly into the text file and not
have YASnippet interpret it as a tab stop.