The joy of spreadsheets


All my life, it seems I’m staring at spreadsheets. Mostly, that means Microsoft Excel. In 2007 Microsoft radically changed the appearance and menus of Excel (cue much wailing and gnashing of teeth) and I began to wonder if I should use something else. Familarity breeds contempt, after all. Conversely, lots of people who use one sort of software will fanatically defend it over alternatives (whether that’s database nerds arguing about Oracle being better than SQL Server, or iPhone users vs people with Android phones, or … well, anything. If there’s two ways to do something with a computer, there will be at least three different opinions about which is best. And mostly, everyone thinks the way they’ve chosen is best.

(Not always though: I always think of the database administrator, who told that people always prefer what they’re most familiar with, plaintively remarked that he preferred beautiful women, but had much more experience with ugly ones. Was he a misogynist, or just working for a 1990s start-up and lacking in social skills?)

I don’t use Excel all the time, and Excel costs money. Google give away Google Docs for free, but time is money too: I want to see if the things I can’t do in Google Docs that I can do in Excel are hugely expensive from a time perspective, or largely irrelevant. (After all, I probably don’t use the vast majority of things you can do in Excel – the question is whether the important 10% (or 5%, or 1%, or what have you) is there or not.)

The test I’m going to use is whether it’s easier to manage my personal finances in Excel or in Google Docs. This is a complicated enough task that it’s worth considering, while at the same time general enough that it may be useful for other people, and not proprietary information that my employer might get angry at me for sharing. (Not that you get to see my credit card numbers, of course…)

What I’m going to do in these posts is compare the two applications, while at the same time trying to set out how I tend to deal with money. There’s quite a bit to cover, so if you dislike either personal finances or spreadsheets, you may want to avoid posts that have the word “spreadsheet” in the title.

A propos of nothing, I knew a lady who felt there were definite carnal overtones to the phrase “spreadsheet” but we will not go into that here.

I start off with four columns in my spreadsheet: every time I move money into or out of my savings, I add another row to the sheet. The columns are: date, location, purpose and amount. That’s because in Singapore alone I have four different savings accounts with different banks (all the better to acquire more bank cards, each with different promotions and discounts attached). The purposes are things like “Pension” or “Tax” or “Holidays”.

(In the UK, where I’m from, the average employee of a company doesn’t need to save up taxes, because PAYE means you never even see the money that the government wants from you – it’s deducted at source. In Singapore (and in Hong Kong) the government wants to mess with your head, by waiting until the end of the year to ask for all the income tax you’ve incurred. Either this is an opportunity to invest money you can’t avoid paying in Risky Ventures in the meantime, or to suffer the temptation of drinking all your income tax, or it’s just the way it is. Some people hope that their employer will give them a big fat bonus at the end of the year that they can use to pay their tax bill; I’m the son of an accountant and naturally cautious, so I save some of my salary every month.)

I used to put all my money into a bank account and just call it “Savings” but this is a bad idea, for at least two reasons.

If you dip into your savings, you forever feel guilty that you’re spending your pension on something silly, when you should be keeping it all for your retirement. Or, you don’t dip into it at all, because you’re worried that you’ll spend everything and have nothing left. Either way, it’s too much psychic overhead, and I don’t want to feel pangs of guilt when I pay for a holiday I’ve saved up for. By saving money under different headings, when you get to spend it, you don’t suffer so much.

Astute readers will have noticed that I seem to be building a shit version of double entry book keeping, where I put all the debits and the credits into the same column. Which is so shit as to not even be double entry book keeping. There’s a reason for this, that I’ll come to. However, for now just remember that every month, I save a certain amount for taxes, a certain amount for Christmas presents, holidays, and so on. That might seem over-complicated, but the amounts don’t change each month: at the start of the year (or if my income or regular outgoings change) I calculate how much I can afford to put into each, and then just move that over once a month. If I had a bank account that would allow me to automate this, it would be even simpler, but that seems beyond the abilities of the banks in “Asia’s Financial Capital”.

Ahem.

So now I have a long spreadsheet of savings, which I add to every month, and subtract from, as costs come in. If I add up all the amount column, I’ll see the total balance of my accounts. If I had one column for savings and another for spending, then I could also see how much I’m spending and saving, but we’ll come back to that.

Savings

What I’m interested in is how much I have in each pot. In both Excel and Google Docs, there’s the SUMIF function.

=SUMIF(C:C, "Holidays", D:D)

This looks for cells in column C that contain just the word “Holidays”, and then sums up the corresponding cells in column D. In the example above, that will tell me I’ve got $33 left in the Holidays fund right now.

The second argument in the SUMIF function doesn’t have to be a fixed value; it could refer to another cell, so it’s easy to build a table that has one row for each type of savings / expenditure, and the total for that, like this:
gdocs2

Immediately, here’s something that’s harder to do in Google Docs than in Excel. Look at the first column: I’ve got that by copying the first column from Sheet1 (the sheet where I put all my details, row by row). But because there are duplicate entries in Sheet1, (a different row for every date for every type of savings), adding up all the SUMIFs will give a total which is larger than what I actually have. Which would be bad.

In Excel, you can highlight the first column and press one button called “Remove Duplicates” to … remove duplicates. In Google Docs, you either go through manually, one by one, or you sort the column alphabetically, write an if statement to check each cell’s contents against the cell above it, and then cut and paste that if statement as values, and then sort by that column instead, and then delete all the TRUE values, and if you’ve had to put up with doing that in Excel for 8 years, it’s quite annoying to find there isn’t a button to do it in Google Docs.

But do all that, and you now have a handy little table telling you how much money you have to spend on different things:

gdocs3

So, ignoring the slight irritation of there not being a quick and easy way to remove duplicates, there’s no big difference in functionality between the two applications. Even the formula syntax is exactly the same for SUMIF, so it’s not as if it’s hard to move between one and the other.


3 responses to “The joy of spreadsheets”

    • Using UNIQUE() in Google Docs is quite elegant, and I can see that’s preferable to the Excel approach, if you have a list that grows over time. Horses for courses though: sometimes Remove Duplicates will be what you want, and sometimes UNIQUE is better. But (particularly on a touchscreen) anything that you have to do frequently that you have to type rather than touch a button is a royal pain.

      At present*, I think the things that make Google Docs much better than Excel are the ability to access your documents from anywhere that you have an internet connection, and the ability to collaborate. For example, my wife can check the amount of money we’ve got left to spend on groceries and know whether we can afford gold-plated foie gras for Felicity this week or not.

      I’m not sure if these are unfair comparisons, because if you try using Microsoft’s Excel Web App to edit spreadsheets, there’s lots of functionality missing from there too: no Remove Duplicates, and no Text To Columns. But since the big sell for Google Docs seems to be that it’s a replacement for Excel, Word, etc, which currently are mostly installed on machines, it makes more sense to compare a installed version of Excel to the Google web app, rather than web app to web app.

      * and that might change as I learn more

  1. I loved your item “Baby” on the spreadsheet. Is she on hire purchase?

    I think installed version compare is the correct thing to do – there are lots and lots of workplaces that don’t need the web app. At home I use Open Office which is fine for word processing but misses out on the same powerful spreadsheet functionality of Microsoft. You can do the same stuff, but it’s messier – remembering to copy and paste the right columns to the right places etc. Would be annoying if I had to do this every day.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.