Would you mind describing a bit more how this works? I've never seen a double-entry system in Excel that I found workable and sufficiently self-checking to ensure data integrity, but I don't really understand yet how you do it. Where do you keep per-account ledgers? Are that your columns in the sheet? What exactly is done with the macro?
I might have a hard time imagining because I need to translate the terminology from Dutch - am I understanding correctly that 'account' = something like 'Expenses - utilities' or 'Taxes to claim'? How do you make a profit and loss statement, and a balance sheet? Do you use some sort of annotation for your accounts?
Its pretty simple. It took me a while to work it all out. You have one sheet per financial year with the following columns:
Description - text description of transaction.
Date - transaction date.
Ref - invoice ID/cheque number etc.
Amount - transaction amount.
Then there are columns, one for each account which are locked I.e. you can't type in them.
To make a transaction, you enter the transaction details in a new row and add the amount and set the source and destination account on the row (Ctrl+click) each in the correct order. Then run the macro which replaces the source with a formula (-amount) and the destination with (amount) from the row. Note that it uses the formula rather than the value to maintain integrity and allow the value to be retrospectively corrected. You could in theory do VAT accounting with it but I haven't bothered to do that yet.
There are columns for bank, petty cash, expenses, corp tax, PAYE, dividends, invoices etc.
The profit/loss statement is the sum of various columns for the year with some maths. This is updated on the fly by sheet references.
There are row summaries at the top and the first two rows are frozen so you can see the total for each account at any time.
All data is available up front so its pretty easy to do analytics on.
I might write it up properly at some point but that should give you a reasonable overview.
I wouldn't use this on mega high amounts of transactions. I top about 200 a year if I'm busy.
The system is portable between excel, libre office, numbers and Google sheets in theory although I haven't tried the latter yet.
I might have a hard time imagining because I need to translate the terminology from Dutch - am I understanding correctly that 'account' = something like 'Expenses - utilities' or 'Taxes to claim'? How do you make a profit and loss statement, and a balance sheet? Do you use some sort of annotation for your accounts?