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
Date
Account - Linked to account names, see below
Debit
Credit
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.
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’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.
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.
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.
This. In conventional double-entry bookkeeping, you’re not really “deciding where money goes” as much as you’re entering transactions two places for checks & balances purposes. It all goes into a system that boils down to two numbers - and those numbers should basically equal one another. Makes it pretty easy to know whether you goofed somewhere.
Are you physically moving money from your checking account to other accounts for saving / budgeting purposes? Or are you just using your system in such a way that “food” + “gas” + “clothes”, etc. = your checking account balance?
I tried to produce a spreadsheet that mimics YNAB but couldn’t. Have you managed it? For those that may not know YNAB was originally a collection of spreadsheets before it became an app.
I think what I like at the moment is that everything is on one spreadsheet.
So true. This is actually quite easy to maintain. I can’t imagine spending more than an hour a month doing this.
Yes everything is on one spreadsheet. Debit, Credit including cash.
I’m not needing the complexities of a business. But the system can be made quite simple I have found.
I looked at a whole number of apps including GNUCash, but they are way too complex, or expect you to adopt their method. I don’t do stocks and shares. All I needed was a simple way to record what’s coming in and where it’s going, plus a few buckets of where I’d like some spare cash to go. Double Entry on a spreadsheet seems to be working for me here.
I’m quite enjoying it now. Let’s see how it is by next year.
While this is true, my “deciding” is about categorising my spending. I like the fact that there is no difference between real account and categories in my setup. So “Groceries” is a category alongside checking account. Because I’m using Double Entry I really have to decide where it’s gone rather than simply entering “Amazon”, which tells me nothing about what the money has done.
I’m moving the money on my spreadsheet. This becomes the defacto of what I currently have to spend, irrespective of what my checking account balance may be. “Groceries”, “Car Fuel”, “Christmas” etc, are all categories alongside physical accounts. It means I can manage everything from the spreadsheet without needing to move money around my accounts, although long term goals may mean it goes into a saving account, but it is designated on the spreadsheet.
I’m pretty much following the 4 rules apart from tracking the age of my money.
I recently moved away from Banktivity also (running it in parallel just to finish the year, but then I’m done) to a Numbers workbook. I have about 10 worksheets to track activity across accounts and then a couple summary pages. It is a little clunky but I am liking the simplicity so far.
Banktivity was frustrating b/c a lot of the time saving features just don’t work - they made bank feeds subscription only and the recurring transactions don’t “remember” the account assignments.
The double-entry sheet is very interesting, I just feel like it would get so cumbersome with so many columns. I have about 20 or so expense accounts I like to track. So that would be upwards of 25-30 columns if you add the description, date, debit, credit and notes.
Why do separate expense accounts need separate columns? Couldn’t there be a single column for account, and then each expense gets its own line? More rows where you have multiple accounts for one expenditure, but fewer columns.
I tried it the way you describe with each account having its own sheet in a Numbers/Excel workbook. But it gets really cumbersome with many accounts. I even combined a bunch of savings accounts into one sheet and my mortgage/auto loan/escrow account into another but it was still a lot.
I think this double entry method will work better for me - 2 main “spending” accounts on one sheet - checking and main credit card will track 95% of my stuff. I have a “transfer” column that will refer to a 2nd sheet with all my other accounts that are mainly savings/investments/things with less activity.
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.