Fun fact: I’ve kept a spreadsheet of (nearly) every dollar I’ve spent since 1998.
Extreme? Perhaps. I recently listened to an Planet Money podcast called DIY Finance, about all the ways that people manage their personal finances, and it reminded me that I’m probably not normal in how I track my expenses. When I was in college, I started using Excel to track the money I spent by recording each purchase and its amount, date, vendor, and relevant category (rent, food, bills, etc.). It’s a system that allowed to me to know exactly how much money I spent and where I spent it on a monthly and annual basis.
I tracked my expenses not to quantify my life but to change my life. Being conscious about how I spent my money impacted how the decisions I made. Small purchases add up to big expenses over time, and small daily choices can impact bigger life choices down the line. And so over the last 20 years, my personal expense tracking has been an important part of how I manage my life.
But recently my system has come under a bit of strain. Marriage introduced a couple problems around time, complexity, and transparency. The sheer time it took to enter expenses, reconciliating online purchases, credit card statements, bank statements, and cash receipts, created piles of records to go through an enter in long, tedious expense tracking sessions. Priorities - and life - kept me from being as diligent as I had been in the past. The process was dragging me down and pushing me toward more time spent recording our expenses and less time interpreting the insights stemming from it.
So earlier this year I started using Slack, Zapier and Google Sheets to manage our family’s expenses. How?
First off, yes, we use Slack to manage our family. We adopted it fairly early in Slack’s lifetime, and quickly found that yes, it reduced emails and texts, but that it also organized our conversations far better. We created channels for different things we have to do, for inspirational ideas or organizational notes on house items, upcoming trips, bills, idead for places to explore, and more, allowing us to easily segment different conversations, search for past notes, and all in all make it easier to manage our lives.
To manage our expenses using Slack, here’s what I did:
- Created a Slack channel called #financials
- Created a Zapier zap to take all new Slack messages from that channel and add them as new rows in a Google Sheet
- Created a set of formulas in Google Sheets to allow me to parse the messages.
For example, now when one of us spends money, we send a quick note to Slack like:
36 Ace Hotel dinner
3 Zeke’s coffee
64 Electric bill
Zapier takes each of these messages in Slack and adds them as a new row in my Google sheet, and then ever so often, I go in and copy the formulas through the new rows. The formulas parses out the numbers and the text and puts them into their appropriate columns, and parses the information about the vendor and any additional information about the appropriate category (if entered). If no category is entered, I manually enter it, a process that is actually much easier than it sounds. 1 Unless some data information is supplied in the message, the date of the Slack message is used to create the date of the expense, and then that’s used by a separate sheet in the same document to sum up all of the expenses in the categories and create a monthly summary of how we spent our money. 2
To a Google Sheet to parse the raw data:
To a Google Sheet that aggregates the expenses into categories of spend by month:
Everyone has their own way to manage their money and track their expenses. And that’s the beauty of using tools like Slack, Zapier and Google Sheets to create your own solution. Yes, I know this sounds like something that software could solve, and perhaps more efficiently. But I haven’t found one yet that does what I want it to do. I like leveraging platforms we already use as a family - Slack and Google Sheets, for exaple - without having to add another account, login, and app to our lives. I like that it’s transparent - both my wife and I can see what we spend - and fairly simple to remember to do. And I prefer having built the process myself, because I can now customize everything about it to fit our specific lives. 3
And philosophically, I personally don’t want to use Mint or a similar service that taps into my bank and credit card accounts and aggregates and organizes my expenses. I believe that the act of recording the transation creates a degree of accountability and thoughtfullness that impacts how we spend our money, and that having a software product create a chart of where we spent our money automatically just isn’t as powerful.
The data is valuable, yes, but for me the process we use to create and manage the data is just as valuable. The process matters to me; I just don’t want to spend too long doing it. And yet again, I’m thankful for being able to use platforms like Slack, Zapier and Google Sheets to be able to easily create a solution that works for us.
If you’re curious how the formulas work, I spend a lot of time building complicated spreadsheet formulas so I kind of enjoy this. Looking at the spreadsheet screenshot above, the first column is a Zapier timestamp that I then translate using the formula below:
Then, I take the text entry in the next column from the Slack message and parse the text in another column:
=mid(B3,FIND(" ",B3),len(B3)-find(" ",B3)-IF(ISNUMBER(SEARCH("yes",B3)), 3, IF(ISNUMBER(SEARCH("no",B3)), 2, -1)))
… and then in another column, I parse the numbers:
In a couple other columns, I record which one of use submitted the message, assign the categories (manually in Google Sheets for now, but I will automate that in the future) and pull out the month from the date. Then I’m left with a row with the date, month, expense, amount, and category of the expense.
I then use a separate sheet to take the raw data and organize it into months and categories. A single formula, used for all the month/category combinations, handles that for me:
Want to see how the spreadsheet works? See the formulas and the organization of the raw data and monthly summary by category in this Google Sheet →
I separate expenses into categories: i.e. housing, electric, cell phone bills, groceries, eating out, entertainment, etc. Right now I do this manually, but the next thing I will change will be to automatically parse the categories, if they are in the text of the Slack message. ↩
This covers personal expenses, business income and expenses are tracked and managed in a separate way. ↩
I have to note, though, that I like to hack and build, and to some degree accrue some joy by having built my solution myself. That’s just how I’m wired. ↩