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 the Amount 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:

  1. Name your tables. This is important later when we extract data from them.
  2. Need a calculator, e.g. your cell phone bill is the same $74.53 for every month? Enter: :=74.53*12 and hit C-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.
  3. 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).
  4. 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.
  5. 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:

1

Why yes, all of the values and other data points are made up.

2

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.