Company Accounts

Like many of you, I have a limited company. Like you, I have to submit annual accounts and a tax return. Probably unlike most of you, my company hasn't had any income since Covid struck, and my accountants were so useless I fired them (for the amount they were charging you would think they could submit some very simple "nothing happened" accounts within nine months but apparently not).

So I was left with the problem of submitting my accounts myself. Up until now I have done it "by hand", filling out the government issued forms in my browser. It's not that hard (at least for me) since the only activity is paying my administratiion fee.

But it always raises the question (for me) of "if I can do it by hand, can the computer do it by itself?".

Some googling later, I found the GnuCash ecosystem, which apparently also has a module to submit UK corporate accounts. On the surface this sounds like exactly what I need: while it has a GUI, everything is based around open (XML) files, which I can read, create and edit.

Furthermore, it seems to have all the features I need in order to get the job done. There's just one problem: I don't want to use their UI, and I don't really want to enter into their complex mental model. But what do I want to do?

A Simpler Model

I was sufficiently wrapped up in the complexities presented by GnuCash that I lost sight of the obvious, and kudos to Martin Rue for pointing it out to me: what I want to do is to track all my transactions in a Google Sheet. I'm not entirely sure I understood what he was describing, but my thought is to have a single Google Sheet with one tab for each "type" of transaction. The obvious types of transactions are "income" and "expense" but I suspect that these need to be further categorized.

Given that, I can write a simple program which reads the sheets, runs through all the transactions, creates or updates the GnuCash files (I am tempted to create the files from scratch on every run) and then invokes the tool to prepare accounts for Companies House and HMRC.

Simples. Let's get started.

Playing with GnuCash

The GnuCash file format may be documented somewhere, although I've only seen vague references, but as I've commented before, while I find documentation useful for clearing up details, I generally find it a lot easier to work with an example. So I'm not going to look that hard but instead try and reverse engineer it by myself.

So the first thing I'm going to do is boot up the GnuCash UI and mess around for a while until I have something I'm happy with, and then look at that XML file. Then I can use that as a guide in generating something similar.

The first step is to download GnuCash, and because today is a Wednesday, I'm going to do this on my Mac rather than on Linux and download the Mac x86 dmg and then install GnuCash into Applications. Once there, I'm going to start it up. I think there's something to be said for going through all the steps with pictures, but there's documentation on how to do that, so I'm not going to. I'll just elaborate on the basic steps.

Two windows come up; one is a "Book" window which is presumably where my accounts are; and one is a dialog asking what I want to do. As it happens, I want to create a new set of accounts, which is the first choice, so I'll select that.

This opens a new dialog called "New Account Hierarchy Setup". This is all described in the documentation so I won't dwell on it that much except to say that I'm going through it with the documentation in one hand and my previous years' accounts in the other.

In going through the various options, I opted to set up for "UK VAT Accounts". Now, I'm not registered for - and don't pay - VAT, but this seems the closest thing to what I want. Remember that it doesn't really matter that much as I'm planning on generating all this myself in a little while.

In doing all of this, and tinkering around and setting up accounts and entering values, I kept on getting the same thing - all the "headline numbers" in my balance sheet were $0. (And yes, in dollars.) Everything just seemed screwed up. I went back and tried various different options, but nothing seemed to work. Unlike having issues with the UI or entering transactions, it seems to me that this really does matter if I am going to be using GnuCash to submit my company accounts.

Then I tried "Balance Sheet (multicolumn)" and that "just worked". I am sitting here right now, looking at two balance sheets. One has entries of $0 almost everywhere (apart from one value which is correct, but ignored in the big picture) and the other looks exactly how I would expect to see it. Huh?

Some amount of Googling later, I found this. It suggests that I have mixed currencies, which seems true to me, and to sort this out in "local settings". Not sure what that is, but going through "Gnucash > Settings", I found a number of places (specifically and, I suspect, most significantly, "Reports") where the currency was set to USD. I'm annoyed about this, because I was told, specifically, to be careful during setup that you chose the right currency as this would affect everything. Apparently it doesn't, and you end up with USD half the time anyway. (For the record, my laptop is set up to believe it is in the US.)

OK, now I get what I expect from the balance sheet. I'll have to be careful to replicate those settings when I generate it by hand.

While I'm looking at settings, I had read that by default the XML file is compressed and there is an option (General > Files > Compress Files) to stop that. So I unchecked that.

OK, I seem to have exactly what I want now. Time to look at the resulting XML file.

Introspecting a GnuCash File

I'm not going to explain XML format or syntax here. If you don't understand that yet, and want to, I'd recommend a good book such as O'Reilly's Learning XML. Other than that, there are online resources, such as W3Schools. I'm just going to dive in to this XML file. Because I'm a luddite, I just read these things as text files using more on the command line, but there are good structured editors out there.

The top level node is gnc-v2, and that then includes a lot of namespaces, so we'll need to be sure to copy those. Sadly, the links are all "fraudulent", so we can't go there for documentation. Then there is a count-data entry that seems to say there is 1 "book", which seems reasonable, and then a book definition using version 2.0.0. This has a GUID ID, so we'll need to make sure we can generate a Guid.

There are then a bunch of "slot" definitions, The first is for "counter_formats", each of which has a key but no value; I'm not sure what these are for, so I'm tempted to leave them out. These are followed by "counters" which were configured during setup - and seem to correspond to the slots. I didn't need any of these, so I'm tempted to ask if I can do without them. Then "features", then "options". I'm starting to recognize all of these as the configuration settings in the setup screens.

Then we have a count for "commodity" set to 1, a count for "accounts" set to 12 and "transactions" set to 3. The commodity seems to be GBP and still seems to reflect a world in which I don't just want all my accounts in GBP. Although possibly it is just "for rigour" and you always need to specify this. Maybe I should go back and do a set of accounts all in USD and see what happens. Interestingly, there is then another "commodity" entry which is something to do with "templates". I don't know...

Then I have my tree of accounts, starting with the root account. The root account does not have a parent but all the others have an <act:parent> entry with a guid parent. These guids also do not have any dashes - I'm not sure if that's essential but it's something to bear in mind.

And finally we have a bunch of <gnc:transaction> elements which seem more complicated than possibly they need to be but basically show the accounts they were transferred between. It also has a somewhat confusing syntax for the value - citing it in pence and then writing "/100" after it. Presumably this is to make it easy to parse as integers rather than floating point.

Simplifying the GnuCash file

It seems like a lot of this file is junk, but the more I think about it, the more I consider it is "necessary complexity" rather than unnecessary complexity. However, to satisfy my curiosity more than anything else, I deleted the "counter_formats" and "counters" slots, the "template" commodity and the "notes" slot inside a transaction, as well as changing transaction values and marking everything "reconciled" and was able to open the file again in GnuCash and see everything working.

I have a (possibly misplaced) degree of confidence that I know what I'm doing here.

Entering the Data

I have all my accounts and bank statements for the lifetime of the company, so I can put those in a Google Sheet easily enough (because of the way I work, this is not an overwhelming amout of data either).

Now, as I do this, I am struck by how "regular" many of my transactions are: I get paid, I pay myself, I pay my accountant, I pay Companies House, and so on. And, because I'm thinking a lot about "verbs" at the moment, it seems that one way of organizing this spreadsheet is to have one tab for each "kind" of action: being paid, paying myself, paying particular kinds of bills, etc. In this way, each tab will be configured to be from one account to another account. And thus all I need to enter is a date and an amount. As it happens, I think a description may also be useful, so all the tabs will have those three columns.

Talk among yourselves while I enter that data ...

Having entered all that, I find there is one exception to the general rule. It turns out that sometimes I have paid Companies House from my business account, and at other times from my personal account (which shows up as a Director Loan). After some reflection, I decided I would rather keep all this on one tab, so I added an extra column Source to allow me to override the configured source account. It is not unreasonable to assume a hypothetical Destination column to override the configured destination account. I also put some information in a Comments field to help me remember in future what I had done. This column will be ignored.

Moving forward

So I think I'm all set up and ready to start writing code. But that's for next time.