🏆 Spending and Income Dashboards for Mint Trends Lovers

[jpm.moore]

Yeah this is awesome, still trying to figure out the global filter and formula to add it into the new sheet, very interesting set of array formula’s I have grown rusty in my spreadsheet skills on google vs dealing more in sql and data front ends

[cps]

Yes - I can look at that. I had originally been thinking of this page as a kind of home page that gives you a quick snapshot of things and then you can go into the other tabs to drill in / interact, which is why I didn’t add them originally.

But it is true that these two graphs in particular are not available on other pages, so that logic isn’t entirely sound.

Do you think it would be better to add a net income over time tab that lets you filter / shows more detail like the spending/income over time tabs?

And I could probably add the M/M comparison graph to spending over time as well.

I think that’s my instinct, but curious how that sounds as compared to just adding the filters on this home page?

[cps]

@cps

You should be able to just make a copy of the new version and update the URL to point to your transactions sheet. That’s the easiest way to do it (and not modify this one).

If you want to copy the tabs over to your main Tiller sheet that can work too, but unfortunately custom formulas don’t copy with a tab so it’s a bit more of a project than I’d like. I can write up some instructions if folks would like that.

You should be able to just make a copy of the new version and update the URL to point to your transactions sheet. That’s the easiest way to do it (and not modify this one).

If you want to copy the tabs over to your main Tiller sheet that can work too, but unfortunately custom formulas don’t copy with a tab so it’s a bit more of a project than I’d like. I can write up some instructions if folks would like that.

If I’m misunderstanding your question, let me know and I can try to help.

[cps]

Thanks for the detailed feedback! Some notes inline below

@Mark.S

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

That’s a lot of columns! I added columns through Z so hopefully that works for most people. It’s straight forward to add more if you need them – just add some on the imported transactions sheet and change the IMPORTRANGE to import more columns in cell A1.

@Mark.S

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

Unfortunately since I don’t know what’s in what column (people may have added or removed or reordered), I just have “Automatic” formatting applied to the entire sheet. I just double checked that was applied to all columns and it seems to mostly work for the default Tiller template and my personal one, but if it’s not working for you I think you’ll have to format it yourself.

@Mark.S

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

I can look into this – it’s annoying to me too – but it will update as soon as you select something else. I have a little script that automatically updates these based on what value you select in column A (so if you are curious why a number is what it is you can just click to drill down) but I think when you first load the sheet it’s selecting the header or whatever it was last. Will require some additional custom script.

@Mark.S

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

I should be showing both Description and Full Description – if Description isn’t showing up something is going wrong. I’m including Full Description just so I can keep an eye on how Description fields have been edited and make sure they are correct (and I had some room!).

@Mark.S

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).

Interesting, I never really used Tags in Mint. So you’d want to be able to filter on these (ala Accounts/Groups/Categories)? I will add it to my list!

[dmetiller]

I think either could work, but I really like the home page concept. Just wish that I could filter it in the same way that I have the other tabs filtered. Thanks again.

[jpm.moore]

Yeah I am just trying to add the filters from the other tabs to the new tab you made. Wasn’t trying to shift the workbook or anything

[dmetiller]

I’ll just second the Tags suggestion. I really like using Tags and find filtering on them to be very useful (e.g., to figure out how much my teenager is spending across different categories )

[Mark.S]

@cps

Interesting, I never really used Tags in Mint. So you’d want to be able to filter on these (ala Accounts/Groups/Categories)?

Yeah, at least in Mint there are reports for:
Spending - Over time, By Category, By Merchant, By tag

And, under Spending Over time. There are these filters. Notice how filtering could be “any” or “all” of the filters listed (i.e. OR or AND).
Show the transactions that match “any”/“all”.
Category
Merchant
Tag

But, individual Tags could also be hidden altogether from Budgets and Trends in settings preferences, same as Accounts. This is where tags could be used to hide unusual/large/reimbursable transactions from normal budgeting/trends reporting.

Example Tags: Reimbursable, Tax Related, Vacation, Medical

On a related note, and back to feedback - I have a couple categories marked “Hide from Reports” on the Categories sheet - are you able to recognize that and have their filtering turned off by default? They actually shouldn’t be listed in the filtering at all, I guess. We getting into details now, I suppose.

One more thing I noticed … Income Over Time report is using “Highest Spend”, “Lowest Spend” - but, this is income now, not spending.

[jpm.moore]

I would have to filter the main page for the spending graph no matter what, the m/m graphs would be sweet, for a no filter page I would do Quick Balances where Mint does Cash, Credit Cards (heloc), Loans, Investments, Real Estate for a snapshot view. I also found that you can scrape Redfin for your real estate valuation with importxml they don’t block like Zillow.

[richard.maynard]

I really have missed some of the dashboards, I really like the month over month spending, and spending over time with a long view. These things help me identify patterns and signs of lifestyle creep, and the month over month gamifies trying to reduce spending for me, I’ll often try and “beat” last month, by being lower, of course when I win several months in a row I’m doing well!

Thank you very much for this extra sheet it let’s me resume playing the game!

[jpm.moore]

@cps not quite as clean but here is the filtered front dashboard: https://docs.google.com/spreadsheets/d/e/2PACX-1vSq7NUy9qElCplymTlkrnoVJzS8N0D99pJn6_7O-x9wWjRFjr0gXZSk4ZxpRQ62cTKCT50ShBvOABAL/pubhtml

[cps]

Very cool, thank you! I am on a ski trip with the family but will incorporate this and the other suggestions after Christmas and update the main link above.

[cps]

Just posted V 2.6 with the following updated features!

V2.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.

Happy Holidays!

[dmetiller]

This is great. Thanks for adding the filters to the dashboard page. I thought the brown was. . .unique.

[beevee]

This is really great and very useful. I am getting an error in the Transactions sheet “Error : Too Large”. Looks like i have close to 20,000 entries/records in my Tiller Foundation Transaction spreadsheet. Any suggestions or workarounds? Thanks in advance.

[mu3484343]

This is really impressive. I came from Mint and built some of my own equivalents, but would have loved this as a time saver when I first came over. Nice job.

[cps]

Hmm, I haven’t seen that before, and have nearly 20K transactions myself. You see this error in the transactions sheet itself, not on one of the dashboard pages? And is that the exact error message?

How many columns do you have in your transactions sheet?

I have a number of “buffer” columns on the transactions tab (as this adds a few of its own) that you might be able to remove and see if that helps. On your copy of this, delete any columns up to column Z that are blank, and change the IMPORTRANGE function in A1 to import the number of columns you actually have in your Transactions tab. I’d be curious to know if that fixes it, and I’ll look into seeing if I can recreate it as well.

[cps]

In fairness it was the only built in “alternating colors” (other than grey) that didn’t look blinding, and I wanted a little color. But yes, I know…

Now everyone can pick their own!

[Mark.S]

It might be related to google sheets - How to work around the IMPORTRANGE error: "Results too large"? - Web Applications Stack Exchange

where, the work-around is to split up the IMPORTRANGE with an ARRAYFORMULA:
=ARRAYFORMULA({Importrange("sheet", "A:ZZ5000");Importrange("sheet", "A5001:ZZ10000")...etc...})

[beevee]

I have Columns till S and i tried changing the IMPORTRANGE function and it still did not work.

Tiller Dashboard sheet seems to be blank and i see Cells A31, G31 and G43 have this ERROR “Did not find value ‘Date’ in MATCH evaluation”

Dashboard Picklist Configuration sheet has multiple errors in various cells

E2 G2 “Did Not find value ‘Account’ in MATCH evaluation”
H2 I2 J2 “Filter has mismatched range sizes. Expected row count 16575 Column Count 1. Actual row Count 1, column count 1”
E3 : “Circular Dependency Detected”

Only Categories Sheet seems to have loaded correctly and does not have any errors. That sheet has all my Categories.