budgeting and bank reconciliation in Excel

Topic

New Reply

Does anyone have a favorite budgeting template in Excel?  I’ve used Microsoft Money for years (I think my edition was sold around 2007) but my new bank doesn’t provide file formats that Money can read, so I’m going to be switching to CSVs that I manage in Excel.

I’m aware that Microsoft has their Money in Excel template but that template is being discontinued soon and it requires an O365 Family subscription anyway.  I don’t need the spreadsheet to pull data from banks directly – I can download the CSVs myself.

The idea would be to (a) import the CSV from Account 1, (b) reconcile the transactions, including categorizing them so they can be fed into several dashboards, (c) append the reconciled transactions to a Detail tab that provides date, category, subcategory, payee, amount, and bank account.

Step b would automatically categorize most of the transactions based on the payee (e.g., I know that any transaction from Whole Foods is categorized as Groceries), which I would probably do via vlookup.

Once that’s done, the final output would be a set of dashboards:  a detailed view like this that summarizes all the expenses by category, a high-level view that just shows how much we’ve made or lost per month, and finally (and I haven’t totally figured out how I want to present this) a view that compares each category’s actual spending to the budgeted number that my wife and I have agreed to.

I will probably just write this myself, but I figured I’d first see whether there’s anything already out there so I don’t reinvent the wheel.

Reply | Quote

Replies

I’m the deputy treasurer of our charity and I’ve written an Excel spreadsheet which does what you want, or at least most of it (if I understand it correctly). I can let you have a copy but I would have to sanitise it first! I looked all over for something like this, but nothing seemed to work as I wanted it to. I’ll attach a copy tomorrow some time once I’ve replaced our data with dummy data.

Eliminate spare time: start programming PowerShell

2 users thanked author for this post.

WCHS, jkhill

Reply | Quote

Hi @jkhill, here’s my attempt at what you described. It uses formulas and has no VBA. I’ve also attached a brief document about what each sheet means. The reconciliation sheet shows the reconciliation in the last column – 0 means it’s reconciled. It doesn’t handle VAT (or whatever your tax is called), but that can easily be added. It has a second sheet for savings, but that can be used for another account if needed. I’m open to any suggestions for improving it. I’ve always been an Access man and have only used Excel for simple things, so this has been an interesting project for me. YouTube has helped with many of these formulas. Feel free to ask any questions you may have.

Eliminate spare time: start programming PowerShell

Reply | Quote

jkhill wrote:

Does anyone have a favorite budgeting template in Excel?  I’ve used Microsoft Money for years (I think my edition was sold around 2007) but my new bank doesn’t provide file formats that Money can read, so I’m going to be switching to CSVs that I manage in Excel.

I’m aware that Microsoft has their Money in Excel template but that template is being discontinued soon and it requires an O365 Family subscription anyway.  I don’t need the spreadsheet to pull data from banks directly – I can download the CSVs myself.

The idea would be to (a) import the CSV from Account 1, (b) reconcile the transactions, including categorizing them so they can be fed into several dashboards, (c) append the reconciled transactions to a Detail tab that provides date, category, subcategory, payee, amount, and bank account.

Step b would automatically categorize most of the transactions based on the payee (e.g., I know that any transaction from Whole Foods is categorized as Groceries), which I would probably do via vlookup.

Once that’s done, the final output would be a set of dashboards:  a detailed view like this that summarizes all the expenses by category, a high-level view that just shows how much we’ve made or lost per month, and finally (and I haven’t totally figured out how I want to present this) a view that compares each category’s actual spending to the budgeted number that my wife and I have agreed to.

I will probably just write this myself, but I figured I’d first see whether there’s anything already out there so I don’t reinvent the wheel.

jkhill, I have been using MS Money 2010 since it came out for my checkbook. I find that it has the most simple way to reconcile (They call it “Balance the account”) the account. What I like about it is that entries can be made the way YOU like them to be made, NOT some preset format that needs more information entered than necessary. It is SIMPLE, STRAIGHTFORWARD, and EASY to use.

I’m running it on WIN 7 x64 SP1.

My 2 cents worth.
Dave

Reply | Quote