🏆 Spending and Income Dashboards for Mint Trends Lovers

[cps]

As a recent Mint convert, I was really missing the trends views that Mint has to let me see spending and income cut various ways – so I put together some dashboards that I think replicate a lot of this functionality, and even improve upon it with more flexible filters and the ability to drill into Groups, Categories, and transactions all on one screen.

I have published two builds of version 3.8 of this template:

• Version 3.8 with Apps Script which includes selection to filter behavior
• Version 3.8 without Apps Script

The template is designed to layer on top of your existing Tiller-linked sheet, so it’s easy for me to roll out new versions (and for you to adopt them!).

Features include:

• A Dashboard tab that summarizes net income over the last 6 months, and a Month / Month spending comparison (ala Mint’s home page).
• A Net Worth Over time tab that shows your total net worth, assets, and liabilities, grouped by account. You can filter this to include account groups, or even add custom account properties so you can filter by whatever you want (I use this for tax status, tagging various types of investments, and account ownership).
• A Net Income Over Time tab that lets you see how income and spending have changed together over time.
• Tabs for Spending by Group/Category, Spending by Merchant (aka Description), and Spending Over Time.
• Tabs for Income by Group/Category, Income by Source (aka Description), and Income Over Time.
• Easy options to filter dates (This Week/Month/Quarter/Year, Last 7 Days/30 Days/3 Months/12 Months, Last Week/Month/Quarter/Year, All Time and Custom).
• Summary tables that show spending/income as well as pie charts to show breakdown of spending, or bar charts (with Y/Y or prior period comparisons) that show spending/income over time.
• Ability to filter to show only specified Accounts, Groups, Categories, or Tags using the Advanced Filters (Click to expand on the right side of any tab).
• Click on the values in Column A to filter the next level charts / transaction table to what’s selected, or use the drop downs.
• Flexible support for Transactions tabs with edited columns (no hard coded column values) so this should work with however you’ve edited your transactions sheet!
• Support for Google Sheet theming so you can customize the colors however you want across all tabs.
• Configurable column names so if your underlying data changes you can update what’s imported in one place.

Feedback, bug reports, and suggestions are most welcome!

Updates:
V 3.8:

• Fixed bug on dashboard where Year over Year comparison only shows the first 99 days of the year.

V 3.7:

• Toggle to show future assets/income/spending in Real or Nominal Dollars
• Options to forecast a fixed investment growth rate (how it worked before) or to do a monte carlo simulation and choose 10th/25th/50th/75th/100th percentile or “Best first 5 years” / “Worst first 5 years” scenarios to use. Requires a mean (average) and standard deviation for your asset class, which you can get from portfoliovizualizer, or I found this bogleheads resource helpful: Simba's backtesting spreadsheet - Bogleheads

V 3.6:

• NEW! Added Post-Retirement Budget Scenario Planner tab that allows you to project investable assets, income, and spending into the future, with the ability to model out future events that might change your assets (sell your house, buy a car, increased medical expenses, etc.).
• Some small bug fixes.

V 3.5:

• NEW! On the Spending/Income over time graphs you can now see transactions from both the current and prior comparison period.

• NEW! For those of you who asked for the ability to change the “Description” to use “Merchant” now you can! I’ve made all column names configurable via a hidden tab called Config Keys. Just change the value for the key TXN_DESC from Description to Merchant and everything should update. You can also change other column names here as well.

• NEW! Post-Retirement Budget Tracker. A tab designed for budgeting in a post-retirement phase, where your budget is mostly set by your investable assets and a withdrawal rate. This new tab will will look at your current assets, your desired withdrawal rate, any additional income you make in the year, and then help you track spending vs. your budget. I’ve included some parameters to let you configure this, but this is obviously a deep area. What I have works for me, but would love requests on other things that would be useful to add.

• NEW! Simple Annual Budget Tracker. I’m not a big budgeter, and the stuff built into Tiller is way over the top in terms of level of detail for me. This lets you set an annual budget, and a few particular categories or groups you want to pay attention to managing. Then you can see spending vs. where you “should” be on an annualized basis, and the remaining $$ you have to spend against your annual budget. I’m sure some folks will want more detail but this works for me so I thought I’d share.

• Fixed chart labels on spending/income Y/Y comparison graph (they were reversed).

• Fixed issues where tags would appear selected when they weren’t.

• Made improvements to Net Worth Over Time to handle larger histories better. This is now generated to align with your selected dates (instead of all time) and I think should work to show any 54 periods (e.g., all weeks in a single year, or months over a 4 year period, or 50 years of data). The max is configurable via Config Keys (more below). I tried a bunch of things to make this faster but bumping into Google Sheets limits here unfortunately. At least it shouldn’t totally crap out now!

• Edits to make the “Filters applied” a little more focused so it’s harder to miss.

V 3.4:

• Fixed bugs with Spending/Income over time prior year comparisons that caused periods not to line up correctly.

V 3.3:

• Fixed a bug that broke Net Worth Over Time if any Groups and Accounts had the same name.

V 3.2:

• Changed how importing from the Tiller Foundation sheet works to only import required columns. Hopefully this makes things a little snappier and makes this work better “out of the box” for people with lots of data or added columns.

V 3.1:

• Added Net Worth and Net Income Over Time Tabs.
• Added support for custom account properties, to further slice and dice your accounts in filters.
• Added support for a “Closed Date” to correctly track net worth over time.
• Adding dynamic comparison ranges on the Spending/Income over tabs, so you can compare to the same period last year, or the prior period (e.g., This month vs. Last month as well as January 2024 vs. January 2023).
• Support for filtering by tags (with AND/OR)
• New built-in date ranges
• Edit links so you can edit a transaction nearly directly from this dashboard (it will open your main tiller sheet but take you directly to the transaction).
• Added an Income by Source tab (to parallel Spending by Merchant) for consistency.
• Built in recent version tracking and an alert if a new version is available.
• Lots of internal changes to make the code easier to manage and bug fixes to selection code.

V 2.6:

• Added filters to Dashboard page.
• Added ability to toggle data range on Dashboard in the period-to-date spending graph, so now you can see Month/Month, Quarter/Quarter and Year/Year!
• Added preview of top Expenses/Income/Transfer transactions in the past month to dashboard page.
• Added preview of uncategorized transactions to Dashboard page.
• Added support for Google Sheets themes (so you can easily adjust colors how you like).
• Changed color scheme from brown which my wife said was ugly.

V 2.5:

• Added a Dashboard tab with net income over the last 6 months, and a Month / Month spending comparison (ala Mint’s home page).

V 2.1:

• You no longer need to add a Type and Group field to your Tiller Transactions sheet – in fact if you did and don’t need them for anything else, please remove them. Now these fields are added at the “Dashboard” layer instead to make this easier to experiment with. You should only need to update the link to your Tiller sheet!

V 2.0:

• Added Filters

[Mark.S]

Nice, this should give former Mint users some familiar looking reports. Looks like you’ve done quite a bit of work on it.

@cps

The only requirement is that your Transactions sheet have a Group and Type column in addition to a Category column

I believe it is an issue to have a Type named column in the Transactions sheet. Try doing a manual transaction and see what happens - it overwrites the Type field with Account Type (not Category Type).

[cps]

Oh good callout, I will fix that to use Category Type as the header.

[cps]

Updated to use “Category Type” as the column header.

[Mark.S]

That was fast! The change probably should be reflected in the description text and spreadsheet titles for consistency, at least a couple I’ve seen:
e.g.
“Transactions sheet have a Group and Type column”
“Tiller Sample Data (Added Type and Group Columns) - V2”

[cps]

Thanks, moving fast – edited!

[cps]

@cps

V 2.1:

• You no longer need to add a Type and Group field to your Tiller Transactions sheet – in fact if you did and don’t need them for anything else, please remove them. Now these fields are added at the “Dashboard” layer instead to make this easier to experiment with. You should only need to update the link to your Tiller sheet!

I just made a small update to this so you no longer need to update your transactions sheet at all – just link to your sheet and the dashboards should start to work. This was causing people some trouble so wanted to make install as easy as possible.

Keep the feedback coming!

[jpm.moore]

@cps really like this setup, on the categories portion though I want to hide certain categories like for investments where I have high volume buy/sells so that it doesn’t show in my cashflow report here.

[cps]

Do you mean you just want to hide certain categories from the graphs? That’s what the advanced filters are for!

Expand the filters (click on the Plus icon above the right most visible column) and de-select whatever categories you don’t want to show up in the tables & charts.

[jpm.moore]

Ahh Ok I missed that part! very nice! next on my list is to make the mint opening screen prior month vs current

[cps]

Yes that’s a good idea. My expenses vary quite a bit from month to moth so I was less focused on that, but I could add something pretty easily to this - maybe make a “home page” that includes this like Mint?

What do you think:

• Month/Month line graph (ala Mint)
• Top Transactions?
• Uncategorized Transactions?
• Spend by group & change M/M?
• Other ideas?

[jpm.moore]

I use the net income a lot where it shows me a bar chart in one view, and a income and expenses bars with a net income line chart that I use for high level monitoring of income vs expenses. I like your transaction setup for drill down on income or expenses which is my second step when I see one of the areas to large to my expectations

[jpm.moore]

this is only my first day with Tiller trying to transition off of Mint so just cracking the surface here but I will soon be deeply nerding out on the spreadsheets especially crypto side

[dmetiller]

This is great and very useful. I’m getting an error in the Transactions section of each sheet: “Did not find value ‘Full Description’ in MATCH evaluation.” The formula for the cell is “=QUERY(Transactions!A:R, $U$30)”. I suspect this is some sort of mapping error that I inadvertently created. Any suggestions on how to fix it? Thanks.

[cps]

Sorry about that – I’m trying to make this work as generically as possible so it’s easy to install and flexible with updates to the underlying sheet.

I made a tweak to add some buffer columns that I think should fix this for you (the problem is just that you have some extra columns in your transaction sheet, so I didn’t “import” the Full Description column). If you re-copy the link in the main post it should work for you now.

[cps]

WIP, will have something by tomorrow that I can share I think.

[dmetiller]

Absolutely no apology necessary. Thanks for putting this together. That fixed it!

[cps]

Version 2.5, with a new Dashboard w/ Month/Month spending and Trailing 6 Months Net Income is now available by popular request! I had more time to work on this today than I was expecting.

Thanks to @jpm.moore for the suggestion!

The link is updated in the main post.

[dmetiller]

Very cool, and thanks for all of the work on this. Question: is it possible to add the same advanced filters to the new dashboard that are on the other sheets? So that the spending/income trends can exclude certain groups/categories.

[Mark.S]

Some feedback/suggestions:

@cps

I made a tweak to add some buffer columns that I think should fix this for you (the problem is just that you have some extra columns in your transaction sheet, so I didn’t “import” the Full Description column).

Maybe some more buffer columns are needed? My Transactions sheet goes up to a V-column.

- Transactions sheet Year column should be formatted for YYYY and not a general date

- Transactions sheet Date Added column should be formatted as a general date

Are you able to populate the initial value for these as blank or something that exists?

Why not just use the Description column in tables, instead of Full Description, as Description will be improved per user AutoCat?

Can a Transactions sheet Tags column be supported? With Mint, Tags could be used to hide transactions, like Reimbursable, Tax Related, Medical - expenses that would skew a monthly budget. Tags could also be used for reporting - like Vacation transaction tags to see how much was spent on a vacation. The Tags column supports multiple tags separated by commas (spaces are not ignored).

Thanks!