You can’t drive a car if you can’t see where you’re going. You can’t pilot your financial kingdom if you have no way of knowing what’s going on.

I’ve heard it a billion times – “oh i know what I’m spending, I can keep track of it all in my head”.  Unless you’re using cash for everything and a series of envelopes, I’m pretty skeptical. You have to be diligent and you have to be involved.  Fast food expenditure adds up, trips to the bars add up, everything adds up.

You need good tools, lately I’ve been partial using spreadsheets to help me with internal surveillance on my financial life. I’ve started lately doing analysis with Excel.

My Canadian bank one of the big five, offers the ability to download your bank statements as a .csv file.  I’d imagine any bank that’s remotely savvy allows this functionality. I’m so sure of this fact, I am not even going to bother researching it.

How to analyze your statements with Excel

So first stumble around your bank and download the .csv file of your bank statements. I downloaded everything that was available, which for me only goes back to 2011. But I’ve had a bank account for a few years longer than that.  If I really wanted to, I could input all my paper statements (I’ve actually kept them) and I could have a full archive, but I’m not about that right now.

Open Excel, go to the File, and press Open.  Navigate to where ever you saved your download.  Make sure you select All Files in the drop down menu above the Open and Cancel buttons.

The data will appear in front of you like magic.  My data is organized by a series of columns: date, description, withdraw and deposit.

you can make a quick table by selecting all the data (ctrl+a) and navigate to the home tab up top and select “format as table”.  Make sure to tick My Table Has Headers.

Now you can sort your column, want to see the most you ever spent?  Just sort the withdraw column by largest to smallest and there you go!

You can colour your cells by using the conditional formatting tool.  For instance for withdraws that exceed $1,000 I have the cell coloured as bright red.  For deposits that exceed $1,000 they are coloured green.

You can use the filter tool and determine how much you’ve spent at a certain store, say the dollar store, or McDonald’s, or whatever your poison is.

Of course you can use simple formulas to get the sum of what you’ve spent globally, or in certain subsets. If you are an Excel wizard, there is probably no limit to what you can do with this data.  You can get into Pivot tables and all that fun stuff that I don’t know how to do.  But the opportunity is there!

You’ve might’ve heard of Mint. It’s a web service that logs into your bank and downloads all your records automatically.  It sorts it automatically and sorts your transactions by type.  If you eat at McDonald’s it automatically recognizes this as fast food and opens a budget.  You can set budgets and your transactions will automatically be tagged, you’ll get nagging emails if you’re over budget in a certain budget. One downfall is big department stores like Wal-Mart, you can get a wide variety of things there, groceries, home improvement stuff, baby supplies, clothing, pharmaceuticals, etc.  Mint doesn’t know how to break down your $145.45 bill at Wal-Mart so you have to manually sort it out.

But there is a huge caveat that you should be aware of.  Allowing Mint to access your bank data is a huge violation in many (if not all) bank’s term of service agreement: if something happens and they see you authorized Mint you can get really screwed on getting your money back (e.g., if you were hacked, etc).  They can potentially decline any sort of coverage or support if you lose funds.  So I decided not to have Mint. I’ve read this might not be the case in the US, but it’s certainly the case in Canada, Money Sense magazine reports that all five of the big five in Canada have reported that Mint is frowned upon.  Global News also confirms this in a more recent article. So I’ll be sticking to spreadsheets!

Happy analyzing folks!