Automate a personal finance dashboard using the Plaid API, Google Sheets, and Data Studio. Build transformation rules to categorize everything to your liking. Easily add data from non-plaid sources. This system takes a bit of work to set up, but it's worth it for anyone who is picky about how they want to track their finances.
Last updated: Thu Dec 22 2022
I tried out some of the personal finance apps like Mint and Personal Capital. They were great, but I'm picky with the way I do manage my finances and I would get frustrated with not being able to get everything exactly the way I like it. I also didn't like the idea of my financial data being sold to advertisers. So I decided that I had to build my own solution.
This system uses Plaid (they do not sell data to advertisers), Google Sheets, and Data Studio. You'll need a Plaid Developer account as well as a Google account. You don't need to know how to code to use this, but you do need to be able to use the command line to get the Quickstart app up and running and be comfortable changing some variables in the config file.
This project was inspired by this repo by hirefrank
config.gs. Plug in your plaid credentials into the file. You'll need to edit the following variables:
tokens. You can add multiple owners and accounts in
tokens. If you'd like to play around with Plaid sandbox data, change
environmentto 'sandbox'. Make sure to grab the Plaid sandbox client_id and secret as they are different than your development account.
Transformation rules make it really easy for you to modify incoming data to your liking. It's best used for recategorizing or changing the transaction type of your data. You can use AND/OR logic with various operators.
Transformation rules are applied automatically any time data is imported, whether that is importing the latest transactions, importing by date range, or manually importing a report.
You can set up your own rules in the "Rules" tab.
Things to note:
You can easily ingest other credit card reports that are not accessible via Plaid's API. The example sheet contains an example using an Apple Card report.
Ingest rules will still be applied to this data, but you'll use the respective Transaction header instead of the report you are importing. For example, if Apple Card uses "Description" instead of "Name", you'll still use something like: If "Name" contains "McDonalds", transform "Category" to "Fast Food").
Note: There's a second page on the sheet with time series charts! The data in the screenshot looks silly because it's from the Plaid API sandbox account.
You can use triggers to pull in recent transactions and account balances every day.
importLatestas the function to run
Q: Why is there an "Owner" field?
A: My girlfriend and I manage our finances together, so we use this to import our accounts together. Owner helps us differentiate. You can delete it if you want (following instructions above), but I'd recommend leaving it because it might come in handy some day.
Q: What is "Rollup" column? A: It makes it easier to aggregate data for some charts in data studio.
Can I edit the columns? A: Yes you can! You just need to update the headers in the config file and then update the mapping in
cleanTransactions in utils.js
Can I edit the sheet names? A: Yep! Just update them in the config file after you update them in the sheet.