The Simple Emacs Spreadsheet

Introduction

A few years back, working on spreadsheet data from Org was all the rage, with tangled scripts automatically receiving tables and all that. It's been a while since I've seen people talk about it, but I filter out most stuff about Org so that may be why. That being said, sometimes all you really want is a simple spreadsheet and that's where SES, the Simple Emacs Spreadsheet, can be handy.

As the name implies, SES is a major mode to display the contents of a buffer as a table and operate on each cell as you'd expect from other spreadsheet programs like LibreOffice Calc.

The SES manual is very short and it takes a few minutes to read it all and be able to fully utilize the package, but without seeing some concrete examples and actually using the mode, navigating a SES buffer can be somewhat awkward.

I haven't seen many resources about it around, except the manual, so in this article I will explain some SES features. Any listed keybinding is the default uncustomized one.

Filling Cells

The most simple spreadsheet operation is filling cells with different kind of values, most often textual or numeric, in order to display them as a table.

Let's follow the traditional spreadsheet “hello world” and pretend we're tracking our finances. The SES manual is also using money tracking in its introductory tutorial. Let's open a file somewhere with a .ses file extension

We'll keep track of income and expenses in two columns with the first row acting as a header to remind us the meaning of each column. An empty spreadsheet will contain only the A1 cell; using M-o while the cursor is inside that cell will insert a new column, in this case creating the B1 cell. If you accidentally created more columns, press M-k until you have as many columns as you like.

To create our headers, we move the cursor inside the cell, for example A1, then type the " character (double quotation mark, hexadecimal 0x22) to display a prompt in the minibuffer. Let's write Income for A1 and Expenses for B1, input is terminated with <RET>.

We have our columns but there are no rows to insert our data in. To insert a new row, let's move the cursor in the empty space after the table and press C-o: this will insert a new row above the cursor. You can add as many rows as you want, if you feel like you added too many press C-k to remove the row the cursor is in.

Move the cursor to an empty cell and type in some number: each time you press a digit a prompt will appear in the minibuffer asking for a number. Let's fill in a couple of rows with bogus values, just to have something substantial to work with.

We want to know our balance so far, so let's add a couple more rows and calculate the total sum of our income and our expenses, then in a third column we'll subtract the total expenses from the total income.

Navigate to an empty cell at the bottom of column A and press ( (open parentheses, hexadecimal 0x28) to open a prompt asking for an expression. Here, we can insert any valid Elisp expression which will be evaluated to obtain the value of the cell. Let's enter this expression: (apply #'ses+ (ses-range A2 A4)).

When SES evaluates the expression, Emacs will apply the ses+ function to the elements of the list returned by ses-range. SES is able to automatically translate cell identifiers, like A1 and A4 to real values Emacs is able to understand.

Let's insert the same expression in the B column, referencing the appropriate cells in ses-range.

Finally, navigate to B6 and press <TAB> to create the C6 cell, insert the expression (ses+ A6 B6) and we'll see our final balance.

So far our spreadsheet is fine and dandy, but it turns out we've found some old receipts we haven't accounted for yet, so let's record them too.

We'll add a few rows and fill the Expenses column with them. You will immediately see the totals being re-calculated each time you insert a new expense; what's more, the ses+ function is able to detect empty cells and skip them so the total will not be mangled by invalid blanks. If you press <RET> on one of the cells containing the totals, you will see that the expression has been automatically updated by SES to span the new range.

Some time has passed and now we want to keep track of certain gifts we have received, but we do not want to include them as part of our income. Since we want to keep them close to the income column, we move the cursor to the B column and press M-o, then type Gifts inside the now-empty B1 cell. Just like the other two columns, we fill each cell with the amount gifted to us and calculate the total at the bottom.

If you examine the resulting spreadsheet, you will notice that SES has updated every reference to the B column to now refer to the C column and that the balance has not changed despite the inclusion of a new total.

Simple aggregations

It is not uncommon for spreadsheet data to be aggregated somehow: calculating averages, grouping rows with common traits and operating only on them, making graphs and so on. SES is not able to perform complex operations you might find in other programs — it's a simple spreadsheet, after all — but it provides functions to perform some common aggregations.

Let's say we are designing a game where monsters fight each other. It could be a videogame or a physical collectible card game. Each monster is identified by specific characteristics: a name; a type; a class; a cost; a number of statistics. To keep things short, let's pretend these monsters only have an “attack” value to indicate their strength.

We might want to record our monsters like in the table that follows, but since we are in the middle of our creative process, certain characteristics are still blank, be it the name, the type or the attack value.

The art of balancing a battle game has no shortcuts: you have to rely on your intuition and experience, a lot of playtesting and trying multiple combinations during matches. Nonetheless, aggregating the values of a spreadsheet can give hints, for better or worse, on the game's balance.

We want to know if certain types have lower attack values than other types. Since so far the game has only one value, we expect too see the same average attack value across all types.

The ses-select function acts as a filter over a range of rows. If we want to obtain the average attack of every monster whose type is “Water”, we will insert this expression in an empty cell:

(ses-average
  (ses-select (ses-range B2 B20)
              "Water"
              (ses-range E2 E20)))

The first argument of ses-select is a range of values to test for; the second argument is the value each element of the first range is compared to: in this case each cell in the range between B2 and B20 will be compared with the string "Water". If the two values are the same, the respective element in the range given as the third argument is included in the resulting list. In our example, B2 passes the test, which means E2 will be part of the list returned by ses-select.

We have discovered that the “Water” type has very low average attack, especially compared to the “Dark” type! On the other hand, “Dark” has a significantly higher average cost than “Water”, which means “Dark” monsters would not be able to be deployed until the later stages of a match.

Does that mean the game is still somewhat balanced? It's still not enough to make an estimate, so let's try checking the highest and lowest attack for each class and type of monster:

(apply #'max
  (ses-select (ses-range C2 C20 !)
              "Warrior"
              (ses-range E2 E20 !)))

The max function is provided by Emacs itself; since some monsters might have a yet to be decided attack value, passing an empty cell to max will cause an error to be raised, terminating the aggregation without producing a result. To ensure correct operations in all cases, we pass the ! (exclamation mark, hexadecimal 0x21) flag to ses-range: this instructs the function to not include empty cells inside the range.

Now we know that the strongest “Water” monster has an attack value of 20 versus a value of 100 from the strongest “Dark” monster. We also see that “Liquid” monsters are extremely weak.

Using the same method we can calculate the minimum attack value while applying the length function on the list returned by ses-select gives us the number of elements matching the filter.

We're still far from balancing the game, it seems, but with just a few commands we've managed to grasp the state of the game as more monsters are being designed.

Epilogue

SES has other features I have not included here as they are very situational and depend on the kind of spreadsheet one is filling. The most significant of these features is “printing” cell values, that is, how the value is formatted inside the cell. This feature takes a significant portion of the manual to explain all the possible options and valid combinations, but if you are working only with simple numbers and strings and have no particular requirement, the default printer is enough.

For obvious reasons, SES is not useful if your workflow requires building graphs or extracting complex subsets of the tabular data: in that case Org mode is probably what you want.

Unfortunately, at the time of this writing the only empty-cell-safe operations made available out of the box by SES are ses+ and ses-average, everything else requires careful use of ses-range flags and ad-hoc Elisp. This is the reason why the expenses in the first example are inserted explicitly as negative numbers.

SES is able to export the spreadsheet as plain text: you select a region and each column inside is exported separated by a tab (hexadecimal 0x09). The region is sent to the kill ring, so you will have to yank it inside a buffer to make use of it. Since it's plain text, it should be possible to write an Elisp function to convert this data to another format, for example by replacing the tab with a comma you would get a traditional CSV file.