Benad's Web Site

So, this is tax season again. You might ask what me, a programmer, would use to fill my tax forms in Québec, Canada. Do I buy some software? If so, what software? Do I “outsource” it? Do I write my own code?

Three years ago I did consider writing my own software. To give you an idea of what’s involved, here’s a bit of a background of taxes in Québec and Canada (they’re quite similar). There’s a main form that can refer to a few additional annexes. Each form or annex is a bunch of numbered boxes that either have to be filled in or computed from other boxes. When there’s a computation to be made, it can refer to some other boxes by number (and annex name if it comes from an annex) or to a range of boxes, for example “fields 100 to 199”. To add to the confusion, some boxes are identified by two numbers: one using an absolute number unique across all annexes (“field 100”) and one local to an annex (“row 31 or annex B”).

You could write the code for that if you really wanted to. Cells are user-entered or computed, and either way they can both be read. Computed cells are computed at once after all user-entered cells are ready. Cells are referred through a two-layered hash of the annex name followed by the cell number. To support ranged sums (“fields 100 to 199”), the hash contained in an annex should be an ordered hash that can be iterated in order from a key.

If that sounds familiar, it’s not just you. That sounds like the typical spreadsheet, but with the added functionality of supporting hashed references to a value. So rather than coding everything myself, I looked at a way to do that in Apple’s Numbers (though it should work with LibreOffice and Microsoft Excel). I found a way to do it, so here it is.

In a worksheet, which represents a single annex, you place only two columns, the first containing cell numbers (column A) and the second with their corresponding values (column B). The rows are sorted by the cell numbers. To make things look cleaner, I placed a header row with “CellNum” in A1 and “Value” in B, and I placed a background highlight for all computed cells so that I don’t accidentally overwrite them with some number of my own.

To lookup a single value for a cell number, here let’s say cell 101, you do this:

=VLOOKUP(101,A:B,2,FALSE)

Note that due to some quirks in Numbers (and maybe Excel), you have to pre-fill all cells with values of 0.

For a range, let’s say 101 to 199, it’s a bit more complicated:

=SUM(OFFSET(B2,MATCH(101,A:A)-2,0,MATCH(199,A:A)-MATCH(101,A:A)+1,1))

Some of the offset tweaks made here depends on the existence of a header row. Also notice you have to set the minimum cell number (101) twice.

A small but important detail is the way spreadsheet software keeps track of sub-cent values. On a paper form (which I’m filling), there is no clear guide about how to deal with sub-cent values, other than to “round” them. So, whenever there are operations that might introduce rounding issues (for example, multiplying by 20%), I do this:

=ROUND(VLOOKUP(101,A:B,2,FALSE)*0.2,2)

Finally, if there is a cell value which is the sum of various things in my receipts, I simply place them as a formula so that I don’t do the sum myself.

=1000+2000+3000

Now, that still means that every year I have to make two spreadsheet templates before I can actually do my taxes (one for Québec’s provincial taxes, one for Canada’s federal one). Still, that gives me the occasion to look closely at all the exemptions, rules and whatnots, also making sure I didn’t forget any paperwork needed to fill in the forms. After that, it’s as simple as filling in the numbers, seeing the “amount due” update in real-time, and writing down the final numbers on the paper form.