Forum Discussion
The Perfect Budget Sheet
I'm attaching a workbook that I've created over the past year or so to track our income and expenses. It is totally self-developed, and as such (by definition) somewhat idiosyncratic.
When you say you want "to track all my finances on one sheet" you're asking for a lot. At least potentially. As you'll see, this one I've attached is solely for the purpose of tracking all income and expense transactions. I download each month data from four credit cards, three bank accounts. Once those are loaded, I categorize those expenses (and incomes) in terms of various budget categories. Then I use Excel's built-in Pivot Table feature to produce a summary report. It's possible, with a pivot table, to click on any single cell and get the back-up details.
This workbook alone is big and ambitious enough. So I don't try to make it do everything. But:
- I have another one that tracks investments. I download data from my brokerage into that, to track growth (or decline) of investments.
- I have another one that tracks my wife's and my IRA accounts and enables projections for income in our retirement years.
- It would be possible, I'm sure, to put all of those together into a single workbook, but I'm not sure it would be smart. Their purposes are distinct enough, even though related.
Now, I also am someone who answers questions on this site, so although I'm not by any means the most gifted of Excel users, I am comfortable with the concepts of database design and the many Excel features to analyze a database. I've also in days long past designed my own tax workbook...I would suggest for your purposes that you don't need to have it exact, certainly; just know, perhaps, what the various cut-off levels are for the income tax tables...be conservative and over-estimate your taxes due a bit...so that when the real calculations are done you're relieved rather than frantic.
Best wishes: my own recommendation would be to more clearly identify goals for perhaps several different workbooks, rather than trying to do it all in one big workbook.
I truly appreciate this. You've given me ideas of entries I overlooked (like tracking specific subscriptions). Also, I also pull data from my financial institutions, but your wording leads me to believe that you download AND import the information into your workbook (whereas I do manual copy). How do you do that?
Virtually every financial institution (banks, credit cards, brokerages) makes it possible to download monthly statements or reports of activity. I do download those. The difficult part of this--not really "hard difficult" but rather "time consuming difficult"--is that they all have their own idiosyncratic layouts. So I do spend a minute or two (at most)--deleting a column or two, or perhaps converting text to numbers--in order to get the columns I want. But then, yes, there's a manual "Copy" and "Paste" of that monthly data into the core transactional database of my workbook.
Now that you've asked, I'm wondering if I could instead develop a way to actually import, using FILTER, to get only the data I want from each monthly download, and then just copy and paste special to save as values. OR perhaps Power Query. But no, if you were thinking I had a really sexy way to import, I am going to disappoint you.
One of the more annoying idiosyncratic layouts from a financial institution, by the way, is American Express's so called "Excel format" download. Don't use it. Use their CSV formatted download instead. For some unfathomable reason, they wasted a lot of time making the "Excel format" download look "pretty" with color, fancy heading, multiple row addresses in single cells. It's an abomination if you are expecting it to conform to Excel Table standards. Thankfully, I've not encountered that sort of stupidity on any other of the Excel formatted downloads.
