“If you can’t measure it, you can’t improve it.” – Peter Drucker
Disclosure: Some of the links in this post are affiliate links, meaning I’ll get a small commission (at no extra cost to you) if you signup.
Trying to have conversations about saving, spending and planning for retirement is infinitely more difficult and more stressful without accurate numbers in front of you.
You fall back on anecdotes and feelings because you have nothing else to go on.
Conversations start with phrases like: “it feels like we haven’t spent much on eating out this month” and they don’t get any better from there.
My wife and I have two beautiful boys, aged 2 and 4, and we’re both ambitious with our careers and work full-time. Life is crazy, crazy busy for us right now.
We’ve found it challenging to find time to manage our family finances, so we’ve been in this position of flying blind without a financial tracking plan in place. We’ve had those frustrating conversations, knowing that if we had better insights into our financial habits we could do a much better job at financial planning.
I want to show you how we changed that.
How we created a system in Google Sheets for tracking our spending habits.
It now only takes us about 10 or 15 minutes each week, so we can focus on understanding our financial situation better, and maximize our saving.
Enter Tiller
Tiller is an amazing tool that connects our bank accounts and credit cards securely to Google Sheets (or Excel), and automatically updates them on a daily basis.
It means we can see all of our financial transactions in one place and do our own custom analysis in Google Sheets.
It’s been transformative for our family’s sanity and helped us get on top of our spending and hit our saving goals.
Tiller has a suite of Google Sheet templates available too, covering spending, saving, budgeting and net worth tracking, so that you can visualize your financial data immediately.
Of course, you can also build your own solutions to answer whatever questions you have.
It costs $79/year, which is tremendous value since you’re getting a fully customizable, automated personal finance tool.
How to setup Tiller with Google Sheets
Tiller is a third-party tool so you have to create an account with them, which is done securely through your Google account credentials.
This is what your homepage looks like, and where you add accounts or create new Google Sheet templates:
Once you add the bank accounts and credit cards you want to track, you can go ahead and create a new Google Sheet:
2. Choose a template or just the data
3. Select which accounts to include
Click Create and the magic happens! ?
After a short while, you can click over to your new Google Sheet, populated with all of your transaction data!
How we use Google Sheets to track our spending habits
We’ve setup categories to group our transactions, so that we can see how much we’re spending on different things at a high-level. For example, we group all restaurant expenses together into an “Eating Out” category, which allows us to see how much we spend eating out.
You want enough categories to differentiate items in a meaningful way, but not too many that you end up with too much granularity. The whole idea is to summarize transactions into something more manageable.
Each week my wife or I will jump into the Tiller Sheet and categorize any new transactions. It’s as simple as selecting the category from the drop-down menu in Transactions tab in the blank cell next to the transaction name:
You can even use Tiller’s new Autocat tool to now automatically categorize transactions for you.
Creating custom reports with Tiller and Google Sheets
I’m going to share our solution for tracking our spending habits.
It allows us to understand how we’re spending our money and identify ways to reduce it.
I created a summary table in our Tiller Google Sheet, which shows our family spending by category. It takes the data we’ve categorized in the transactions tab, which is automatically updated by Tiller, and summarizes it.
The transactions are summarized by categories in the rows, and by months across the columns. Columns A and B contain checkboxes, which I use to control which spending categories to show in my charts.
This table alone gives us more insight into our spending habits than anything the bank gives us. Every single transaction is included and categorized, by us not the bank.
You can create a table like this with the Google Sheets QUERY function, or using Pivot Tables in Google Sheets.
In my case, I’ve used a QUERY function in cell C3 to retrieve, aggregate and pivot my transaction data:
=QUERY(QUERY(Transactions!A1:O,"select C, sum(D)*-1 group by C pivot K"),"offset 1",0)Visualizing our spending habits in Google Sheets
I added the checkboxes in columns A and B, so there’s a way for my wife and I to choose categories to focus on.
The checkboxes can be individually checked or unchecked and they feed into other data tables that only show the data for the checked items.
Our mortgage, car lease and utility payments remain largely the same month-to-month. We know we have to pay them every month, so we don’t necessarily need to see them every time. (That’s not to say they’re not important, but trying to visualize all your categories at once will just clutter your charts to the point of being useless. )
However, seeing our discretionary spending — things like travel and eating out for example — helps us understand our spending habits and find ways to save more money in a healthy way.
We’re currently using two charts to track our spending habits:
Chart 1: Current monthly spend vs. Average monthly spend
The first is a monthly breakdown by category, showing actual spend this month (blue) against the average amount we spend in this category each month (red):
(Chart shows fictional data.)
Chart 2: Discretionary Spend by Month
The second is a look at our discretionary spending over the past few months, so we can see how selected categories are trending (chart shows fictional data):
The combination of the monthly category breakdown table and these two simple charts gives us tremendous insight into our spending habits.
Knowing how we’re spending our money gives us tremendous peace of mind.
It’s helping us to minimize our unnecessary spending and maximize our saving.
See Also:
10 techniques to use when building budget templates in Google Sheets
A guide to the super useful QUERY function
Pivot Tables in Google Sheets: A Beginner’s Guide
How to use checkboxes in Google Sheets
Note: I’m not a financial expert and this post does not provide financial advice. It simply shows some techniques for working with and presenting data in Google Sheets.
I hear you on this one. I have felt the same way about online banking systems. Therefore, I did like you and I created a totally killer Google Sheets system for my family’s personal finances. I don’t use Tiller (definitely going to check that out), so I do download and import, but I have build such a great workflow and data cleansing process that it takes no time at all. Hardest, most time-consuming part is reconciling between my Sheets system and the bank. I even have a front-end dashboard in Data Studio so my wife can toggle date ranges and filters without having to give her the Sheets file at all.
Sounds great, Bob! You’re one step ahead with a Data Studio dashboard, that sounds awesome.
Hi Bob.
Do you have a copy, without your info of course, that we could use?
Anxious to get budgeting and straightening out the finance, and I don’t have a lot of experience creating these things. ;0)
If you do, then please let me know!
Thanks,
Scott
Do you have any concerns about keeping your financial data in Google Sheets given their EULA?
Hi Paul,
Great question, and I can see how this is totally worthy of consideration, especially in today’s environment. Is there anything specific that gives you pause for thought? I’ve been creating ad-hoc financial sheets for years for net worth tracking, saving, spending etc. There are no account numbers in these automated Tiller Sheets, only transaction information, so I’m not particularly worried about it. Google already has enough info on us all for micro-targeting 😉
Here’s the FAQ from Tiller on security from their point of view: https://www.tillerhq.com/security/
Tiller have an Excel connection now too, so you could create a similar system and avoid the issue.
Thanks,
Ben
Love it – put 15 accounts in it in under 30 minutes – it even pulled in our PayPal bill pay transactions – every account I needed was available. I have done this manually for over 10 years – never found one that was spreadsheet based until this. THANK YOU.
Nice work, William!
Quick question for you Ben. Which template did you go with and/or did you just use the raw data and create your own?
This was my own creation based off the raw transaction data. Tiller have some great templates available and you could definitely try them out to see if any work for you. More info on the templates here: https://www.tillerhq.com/google-sheets-templates/
Cheers,
Ben
Tiller looks pretty awesome, I’ll have to check it out!
This looks like a great tool. Do you know if this is compatible with multiple banks and multiple accounts? i.e. If I have my accounts (ck and sv) with Citi and my wife is with Wells, can I pull in checking and savings from each entity under the $59 subscription?
Any idea if this works with AMEX as well? That would be a real time saver for me.
Hi Warren,
Yes, it’s compatible with multiple bank accounts under the $59 subscription. When you create the Sheet you can just select which accounts to add into that specific sheet, from the shortlist of all your accounts. As for AMEX, I’ve asked Tiller and I’ll get back to you!
Cheers,
Ben
Hi Warren,
Yes, Tiller let me know it’s compatible with AMEX.
Cheers,
Ben
Great tip, Ben. Love your work!
I’ll definitely have a look at Tiller though I doubt that it’ll work with Swedish banks.
Maybe off topic but do you know of a way to pull data from a logged in service. I use a car-pooling system and I would love to pull data directly to a Google Sheet rather then copy/paste but I can’t work out how. I’ve tried the IMPORTHTML but it doesn’t work, probably because it’s a service that I log in to rather then just an open web page. Any other ideas?
Thanks!
/Carl
Hi Carl,
It does work with many international accounts, although they don’t directly support them. Could always drop them a line.
The IMPORT function can’t grab data behind a login service, so you’re out of luck there. Does the service have an API? In which case you can write code (Apps Script) to access your data, or possibly use a third-party tool to do that.
Cheers,
Ben
Hi Ben,
Does it work for Indian banks too?
Hi Rudra,
I don’t know specifically about Indian banks. It does work with many international accounts though, although they don’t directly support them.
Thanks,
Ben
I have used Moneydance since it first came out in the early nineties. However, a Java application which have to load 20 years of data at startup and make a backup copy at shotdown become rather slow at starting up and shotting down.
So I tested Tiller, but failed to connect to neither my Canadian account at CIBC nor my Danish account at Nordea. So now I attempt to convince Nordea (Denmark), that Tiller is a good tool to use for data export. Currently the only export facility Nordea (Denmark) provide is csv-files, which then have to manually imported into Google Sheet.
Moneydance in the past provide excellent import of share and bond prices from Google Finance. I wonder if it is still possible to import Google Finance data into Google Sheets?
Hi Ben,
Any chance you could make your spreadsheet available? I like some of your tweaks more than the templates that are available through Tiller.
Cheers
Hi Ben,
Thanks for sharing this with us. I’d like to second Johannes request. Would you be willing to share a version of your spreadsheet with dummy data for us? I’m a Tiller subscriber myself and it would be really helpful to me to see exactly how you are generating the summary table and two charts.
Thanks!
Ben,
From your recommendation, I have several TillerHQ sheets populating and reporting all my finances, which is amazing.
Sadly, Tiller have emailed today to say they won’t be able to help us in the UK as the Open Banking regulations kick in. Please can you look into anything vaguely similar?
Hi Ben, great read.
Seconding Jon’s request to ask if there are any known Tiller alternatives for those of us in the UK? I’ve been a user of Tiller for over 12 months and now face the prospect of having to revert back to manual imports due to the Open Banking regulations.
John
I have also been using them – love the customization. I also built myself an app to look at graphs and categorize transactions without needing to open up sheets.
Thanks, Ben! Came across your name and Tiller when searching for a google sheet template to track credit card debt, balance transfers, payment due dates, etc. A link to your CC Tracker Template from tillerhq.com takes me round in circles. I am NOT a spreadsheet person (my brain shuts off even reading the comments ) so for me it needs to be ridiculously simple. Paying for a subscription seems counterintuitive to me when trying to gain control of finances. Are there any truly free resources out there for ppl like me? Btw I signed onto your email list. 😉
Hi Ben,
I just received news that your Credit Card Tracker Template is no longer available on Tiller’s download template. Could you provide a copy for me to use? Thank you in advance.