How I Track Our Spending: Spreadsheets

As you can infer from my posts about our spending, in order to analyze your spending, you have to first keep track of it. In this post, I’m going to go over briefly how we keep track of our spending, and then share and go over an Excel Spreadsheet you can use if you don’t already have a method for tracking your spending.

I’ll say upfront that I don’t actually think it’s important what you use to track your spending, just that it’s important to do so. Get your bank to print out your passbook (do they still do that?), use pen and paper, use a digital notepad, use a spreadsheet, put everything on your debit/credit card and review the statements, use specialized software, or use some web service… It doesn’t really matter what you use, just that you do it. Why track your spending? Because what we spend our money on is a reflection of our values, so if you want to make sure your spending aligns with your desired values, you have to track your spending. (What your inflows and outflows of money are also helps you reflect on your Money vs Life balance).

What I Use (Normally)

In what is already starting to feel like a past life, I use Microsoft Money to track my spending (as well as my investments, and basically anything related to my personal finances). If you’re not familiar with Microsoft Money, it’s sort of like Quicken, except it doesn’t suck (that’s my personal opinion).

Unfortunately, Microsoft discontinued the software back in 2009, so Microsoft Money is no longer actively developed. When they stopped developing it, I searched all over for an alternative (including purchasing and using Quicken for a while *blurgh*) but in the end, I just kept on using Microsoft Money. The good thing about Microsoft Money though, is that since Microsoft stopped working on it, they’ve made it freely available! This is great, but at the same time, it’s hard for me to recommend anyone start using it as I’m sure at some point, it will stop working on the newest version of windows. (And when that day comes, I’ll either cling on and run it in a VM forever, or go back to my desperate and unfruitful search for an alternative I like).

What I Use During Travel

When I’m travelling, I continue to use Microsoft Money to track everything, but I supplement it with additional tools. There are a few reasons for this:

• I like to keep track of spending real time when I travel. Since not every transaction will have a receipt, if I don’t keep track of it during the day, it’s easy to forget some details when I’m trying to log everything in the evening.
• I like to keep track in two ways to make sure everything balances and I have an accurate record of the current (foreign) cash I have on hand.
• Travel often includes a lot of shared expenses, so it’s a lot easier to share a separate document used for tracking spending than it would be to share my Microsoft Money data.

So, my basic workflow to track spending when I travel involves taking notes on the go (historically with a pen and notepad, but nowadays with Evernote, see the header image for this post), then transcribing things into a spreadsheet at the end of every day. Sometimes, (if mobile data is readily available) I’ll enter data directly into an Excel Spreadsheet from my phone. I keep my spending spreadsheet in Microsoft OneDrive, and Microsoft Excel for Android (and iOS) do a decent enough job for me to enter transactions on the fly, but their offline modes can be finicky, so I usually only do this when I have reliable mobile data.

I tend to (stupidly) build a new tracking spreadsheet from scratch for every trip, but this allows me to customize the spreadsheet as needed, and make tweaks to it based on things I didn’t like the last time around. Examples of things I’ll change up are whether I track the country/city for every transaction (not necessary for trips to a single location), and whether it’s a group trip and I need to track who paid for what and for whom (I use Splitwise for this now, which I’ll write about in the future).

Regardless, the fundamentals of a spreadsheet to track spending are generally the same, so I present to you a basic version for travel that you can download. The rest of this post will be describing the spreadsheet and how you can use it.

Travel-Spending.xlsx

This workbook has 5 worksheets in it:

• Transactions
• Summary
• Cash
• Currencies
• Payment Accounts

The first worksheet, Transactions, is where all the spending gets entered. Each row represents a transaction, and the values you enter for each column should be pretty self-explanatory. The spreadsheet is filled with some dummy (or not so dummy) data so you can see how everything works.

Column D (“CAD”) should be auto-filled using the formula given. This takes the amount in Column B, along with the currency in Column C, and calculates the total value in Canadian Dollars. The Currency Names (e.g. JPY, TWD, etc) are given in the “Currencies” worksheet.

Payment Account records what was used to make payment for the given transaction. The available options must come from the list given in the “Payment Accounts” worksheet.

Category allows you to categorize your transactions. These categories come from the list (which you can customize) in the “Summary” worksheet. The categories behave in a straightforward manner except for “Transfers”. Transfers do not get included in the spending totals, because Transfers are used for moving money from one payment account to another. An example of this would be loading credit onto a transit card. This is a transfer as the money hasn’t actually been spent, just added to another account. When you use the transit card to pay a transit fare (or to buy a delicious pudding from 7-Eleven), that’s when the money is actually spent.

The “Cash” worksheet is where you can see this in action. Anytime there is a “Transfer” transaction recorded, the spreadsheet looks at the Payee column along with the Payment Account column to determine where the transfer is coming from and going to. For example, a Transfer transaction with a Payee of “EasyCard” and a “Payment Account” of Cash deducts from the cash total, and adds it to the “Loaded” column for “EasyCard”. When “EasyCard” is used as a Payment Account for another transaction, then that spending gets added to the Spent column for the “EasyCard”. This allows you to keep track of your running balance on transit cards (or any other account for that matter). If you find this a little bit confusing, try filtering the Transactions worksheet to only show transactions with Category = “Transfers”, and play around with the numbers. See how the values in the “Cash” worksheet change.

The last thing I’ll note is that with this spreadsheet, I’m using a fixed exchange rate for all the currencies. The reason for this is because I usually convert all the cash I’ll need in one go, therefore I only use one fixed exchange rate. Any transactions I put through a credit card go in as CAD, and I’ll simple wait for the charge to post on my credit card before I know the final cost. I’ll usually add a note in the Notes column for that transaction with regards to how much it was in its original currency.

So that’s about it. I usually get a lot of insight looking at the “Summary” worksheet, and I can use the data from the transactions worksheet to analyse my spending in all sorts of ways (e.g. daily spending average vs remaining cash divided by remaining days). (Not included in this sample spreadsheet, because I thought it was too crude and didn’t want to spend time to clean it up, is a worksheet I have that gives me a summary of spending for each day of the trip).

While this spreadsheet has aspects of it that are specific to travelling, it can definitely be used as a foundation for tracking day to day spending as well.

Does anyone else out there keep track of all their spending? What methods do you use, and do you change it up when you’re travelling?