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.


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.

Transactions worksheet

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.

Summary Worksheet with the Categories listed

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?

5 thoughts on “How I Track Our Spending: Spreadsheets”

  1. although you can approximate how much cash you will need in “one go”, I find that you will always end up either short and need to buy more foreign currency or you are long and need to sell your residual. One tip I highly recommend is to keep your cash base currency in USD when you travel. Especially against the restricted currencies of KRW, TWD, INR, IDR, VND etc, where the fx spread will be much tighter for USD compared to the likes of CAD…also converting into non-deliverable currencies ON-shore will ensure you capture the best rates possible….I also recommend securing a US dollar based credit card that has a zero fx surcharge…levying the Visa or MasterCard wholesale rate instead of the wholesale rate + x%. The latter being a very important spending vehicle for traveling.

    1. Hi Creeper! I agree, it’s tough to approximate how much cash you’ll need, especially if you have no first hand experience at the destination. Reading other people’s experience can only guide you so much. Generally, I like to underestimate the amount of cash we’ll need, then use credit cards as needed to get as close to 0 cash as possible by the end of the trip.

      Good call on using USD as your base currency for travel. We usually travel with some USD to convert at our destination.

      Do you have any specific recommendations for USD based credit cards with zero FX surcharge (that a Canadian can apply for)? The only two options I found for CAD cards with no FX surcharge are the Chase Marriott and cards. I’m not familiar with any USD based cards that have 0 FX surcharge. When looking at the CAD cards, I found the rewards + 0 FX surcharge only offered a slight advantage from cards with better rewards but 2.5% surcharge (1-1.5%). I didn’t think the difference was worth the hassle of using it exclusively for foreign charges. If I stayed more at Marriott Hotels, or shopped more on, then maybe I’d get one of those cards.

      1. It’s challenging getting US based credit cards without an SSN. The easiest route is to open up an HSBC premier account in Canada and then get them to open a US based account along with the US HSBC premier MasterCard. That card has rewards + 0 fx surcharge. The best US travel cards at the moment are the Chase Sapphire Preferred and the American Express Starwood Pacific (spg) both offer zero fx surcharge and multiplier rewards on travel + generous opening bonuses…first years fees are waived then 95 USD after that….you could use em for a year and churn them…the two cards just mentioned can be obtained after you build your USA credit profile via the HSBC MasterCard…it sounds troublesome but worth the effort since you earn highly valuable rewards

  2. I found another ok option for CAD based card with no fx surcharge. 1.75% cash back + 0% fx surcharge. Caveat is the 1.75% can only be applied to services affiliated with the Rogers group. In the most practical sense, if your Canadian mobile bill is with Rogers or Fido, this card could show some value. I followed up with this subject online and the thesis is that 70% of Canadians hold passports compared to 30% of Americans, using these figures, one could deduce that Canadians travel more than Americans and that Canadian Banks recognize the FX surcharge as a key contributor to revenue….thus making it less desirable for them to offer 0 surcharge cards. Danggg that actually makes sense…

    1. Ah yes, the Rogers Card. I forgot about that one. Good for anyone who’s a Rogers customer… Not so great for me as I am more of an unhappy ex Rogers customer. I’d consider the Marriott and Amazon cards before the Rogers card.

      I actually do have a SSN so it might be worthwhile to build my credit rating in the States… I’ll have to investigate HSBC Premier when I get back in the summer to see if it’s worthwhile. Some of the Canadian banks with US presence are starting to offer better cross border banking as well. For me it comes down to whether I think the (significantly better) rewards and benefits of US credit cards is worth the hassle of cross border banking. A US Chequing (*ahem*, Checking) account that I can easily transfer USD back and forth from with a Canadian based USD account (and do it all online) would be ideal.

Leave a Reply

Your email address will not be published. Required fields are marked *