This implementation by Christian Schick has been extremely helpful in setting this up in Excel:
So far so good.
It seems with double entry bookkeeping that all money comes from somewhere and goes somewhere which is what the double entry is all about. However, in terms of tracking where my money goes I’m also wanting broader categories.
For example. I may purchase a a lightbulb. This would come out of my checking account and into my “House” account. However, I purchased this lightbulb from amazon and would also like to be able to see how much stuff I’m purchasing from Amazon and other vendors. How would I add this into the system?
Yes, add a column for the vendor. If you want to get fancy, you could have a column for the vendor (Amazon) and the brand (Phillips).
Either way, where there’s a separate column, it’s relatively easy to filter, query, sumif or make a pivot table, any of which you could use to tell you how much you’re spending through a particular seller or on a particular brand.
Going further still, it’s possible to mimic tags by including a column that has comma- or space-separated values (home maintenanceorhome,maintenance`); then you can filter by looking for terms or partial terms in that field. It isn’t perfect – you’ll get partial matches sometimes, especially with shorter “tags” that may also appear inside other tag words – but it sometimes can be useful.
That said, if you really want to roll your own with this kind of complexity, maintaining a spreadsheet gets harder, and errors (including of the fat-finger type) get harder to recognize and fix. At some point might seriously consider a lightweight database tool like Datasette (my favorite these days), FileMaker or whatever the spiritual successor to Bento is.
In case anyone else is interested. I’ve set up a Numbers spreadsheet based on the Christian Schick example I linked above. I did try Excel, but was getting too many problems on macOS.
In the numbers version, I have 3 columns, “Taxable”, “Vendor”, “Type” (of transaction, ie cheque, ATM, etc).
The next set of columns are
Notes - a description of the transaction
Account - Linked to account names, see below
Error cell. This checks that all Debit & Credit columns zero out, else is displays the error amount.
I then have a columns for each account and category. About 40 columns
Immediately below that headers is a row giving me the total of each column.
Each cell in each column has formula that adds the Dr or Cr into the relevant column row. It does this automatically when I add the account/category when entering a transaction.
I have conditionally formatted all currency cells to be green for plus entries and red for minus entries. this makes it easy to see transaction amounts.
I’ve also created 2 body styles. One for Dr and and indented one for Cr so it’s easier to read.
I’ve imported entries from 6th April 2022 (my financial year start) and am slowly reconciling some 700 transactions. I did run the exported bank csv through BBedit to add an extra line below each transaction so I can enter the double entry.
I’ve attached the doc is you want to play and tell me how to improve it with a bit of dummy content.
Thank you for the link to your spreadsheet. I don’t use a double entry system but it’s good to see how others do. I am curious at how granular you want to be with your data. Many, many years ago I tried to keep track of everything but now it’s just an overview and it works for me.
Re granularity: Let’s say you shop at Costco today and buy things in many categories and pay with your credit card. Do you separate those out? It seems like you would have to, if you wanted to get an accurate picture of where your money is being spent.
Example: Thing bought a Costco today. One credit card transaction.
Apples - Groceries
Wine - Groceries or Alcohol?
Floor mats for car - Auto
Light bulbs - House
Books - Books
Books for gifts - Gifts
Vitamins - Groceries or Healthcare?
iPad case - Other?
Sweatshirt - Clothing
This has always been where I have given up.
It also may be a good thing to keep your receipts (scanned or originals), in case you want to do a deep dive. Say you look at your groceries category and decide you’re spending too much. Only by looking at your receipts will you be able to tell that you’re spending a lot on cookies, ice cream, candy, even wine, etc. Maybe some people have this automated, so that scanned receipts separate everything into proper categories, then automatically get added to the spreadsheet.
Anyway, good luck with this. Keep us updated on your progress.
In an actual bookkeeping/accounting program, you would do this by creating an expense account with subaccounts.
For example, I have an expense account “Online subscriptions” and a subaccount “iCloud services”.
You can run a report on any account or subaccount, or run a report with nested subtotals on all the subaccounts.
Personally, of all the software purchases or subscription fees, a decent accounting program has for me been worth the cost. I would gladly cancel convenience or nerd stuff, if I had a budget crunch, before giving up accounting software.
IMHO, there is just enough I don’t want to learn about bookkeeping and accounting that software that knows the rules and stays updated is worthwhile.
Also, the support I get from the vendor includes help with accounting. I’ve called several times and they have walked me through fixing things with “debit account xxxx, credit account yyyy” type interactive help that I would not have figured out on my own.
I keep fairly broad categories. There is quite a bit of detail in my notes field, which I could broaden out if I want to. If too much is spent in one category, I generally know why. But it wouldn’t be too hard to track for a month and see what’s happening.
I’m using this to give me a detailed enough overview. At some point I’ll add some saving categories so I can move money into goals I want to achieve financially. At this point I’m only looking to get on an even keel.
My only concern will be how Numbers does with 100 columns and 1000 rows.
I go in very general categories so as not to make it too tedious. Groceries, entertainment, restaurants, software, etc. I know a lot of people say anything they buy in a grocery store is “groceries” but I want to know things like alcohol spending, so I separate that. I am not thrilled with all my categories, but I started using them ~10 years ago, so I just stick with what I have.
I have a spreadsheet for calculating category amounts if its get too complicated. It works out taxes* and cash back and then totals everything. I tried to automate it years ago, but I found store receipts to be fairly illegible so a good portion of the time the OCR got it wrong and I had to go make corrections anyway.
*In the US we have to pay tax separate, and then some things are exempt, so I work out the taxes in each group and include them in the total. Some people do a sales tax category so they can keep track how much they spend. I was going to, but thought it would be too depressing.
I’m a big proponent of Xero - I guess because it’s my day job!
IF you are going to pay for bookkeeping / accounting software, you must be paying for something that has bank feeds (to pull data directly from your bank account). If it doesn’t have bank feeds it’s not worth it.
I am kind of curious @svsmailus, what are you getting out of doing it through double entry? What does it provide you with that you can’t get out of doing it the way most personal finance is done, through a register.
Double entry forces me to decide where the money comes from and where it goes and/or should go right then and there. Right off the bat it’s making me think about my money rather than logging my spending. It’s also not an onerous task to keep up. As every category is essentially an account, I can also create some for Christmas, birthdays, holidays, etc and assign money to those.
The spreadsheet took me a few hours to set up. Downloading my bank transactions and getting the data in about 20 min. Sorting one months entries took about 30-60 min. This will reduce with months where I know what every transaction is about rather than having to look at receipts or emails. I’ve tried YNAB and after 2 hours still couldn’t get it to come up with the right balance amount.
I would argue a good monthly budget that works in conjunction with registers for your accounts would be more useful, but if this works for you long term, who I am to argue? I read that tracking finances is a lot like dieting for people, they last a couple of months, and then it falls by the wayside. Are you doing an income statement, balance sheet, and statement of cash flows as well? (I do a cash flow spreadsheet every month, it’s actually very helpful.)
I had to take one of their free classes to get YNAB to click, but once it did it was easy. It’s really easy to recreate on a spreadsheet as well
The end goal of double-entry accounting is a set of financial statements—in the for-profit world, a Profit and Loss Statement, a Balance Sheet, and a Statement of Cash Flows. (A Non-Profit produces a Statement of Activities rather than a P&L.) If that’s not your goal, a formal double-entry system, especially for tracking personal finances, may be overkill. Having been elbows deep in debits and credits over the course of my professional life, I’m quite happy to dispense with them in my personal life.
I think you could do this pretty straightforwardly with a personal finance app organized around accounts, categories, and tags. (I don’t love it, but Banktivity is an example.) You would record the transaction in the register for your checking or credit card account, assign it to the “House” category, and add a tag for “Amazon.” If your Amazon order also included, say, an oil filter for your car and a multi-pack of tube socks, you could split the transaction between the House, Auto, and Clothing categories and add the “Amazon” tag to each. You could add other tags too, of course—e.g., whether the oil filter is for your Maserati or your Ferrari. The reporting functionality built into most personal finance apps will allow you to parse your data in app and / or export it to a spreadsheet if that’s your thing.
If you really do want to keep a set of double-entry books, a budget-friendly accounting package like GnuCash might serve you well. (GunCash is free.) It’s definitely not pretty, and it’s a little fiddly to set up, but there’s a decent tutorial to get you started, including a section on accounting concepts.
I love spreadsheets, and find futzing around with them as relaxing as some people find solving sudoku puzzles, but sometimes a purpose-built application is the better solution.