🏆 Spending and Income Dashboards for Mint Trends Lovers

Spreadsheets Sheets Templates
82

I got it working using the formula below, which was from a post that Mark.S shared.

=ARRAYFORMULA({IMPORTRANGE('Instructions & Tiller Foundation Link'!B2, "Balance History!A1:O5000");IMPORTRANGE('Instructions & Tiller Foundation Link'!B2, "Balance History!A5001:O10000");IMPORTRANGE('Instructions & Tiller Foundation Link'!B2, "Balance History!A10001:O15000");IMPORTRANGE('Instructions & Tiller Foundation Link'!B2, "Balance History!A15001:O20000")})

The issue is not with the columns, but rather appears to be with the IMPORTRANGE not liking too large of a dataset as it is capped at 10MB of received data per request. (See reference under Performance for IMPORTRANGE.)

The updated formula appears to be working well now, but I will have to remember to expand it to more rows in the future.

AHB

83

Yeah that’s why I did it previously based on a limited column range as you don’t have to remember to expand it. It may be for balance history you want to do it every 7 columns or something like that. Not at my computer now to take a look.

84

I have an idea for an even more targeted approach that just brings in the columns I need which I think will work even better. Will add that to my next update.

85

I will try your suggestion of the column range. Thank you for responding.

86

Thanks for the kick in the pants on this, worked on this approach that only imports the specific columns I need and it’s a lot faster. Will release a new version with that update tomorrow.

1 Like
87

I’m curious if you’ve tried Trim Balance History?

“The Trim Balance History utility removes extraneous balance history entries by trimming them down to a single balance entry for each day, week, or month (for each account). The frequency you’d like to see is configurable. You can choose all accounts or a single account for trimming.”

88

This is great to know about, I didn’t know it existed. There definitely gets to be a lot of data in that sheet quickly, and the structure of it makes computing balances over time pretty computationally expensive over long periods.

89

@cps I just got back to this thread and pulled in the NEW 3.1 version and I have to say its epic! thanks for incorporating so many ideas!

Thanks again!!

1 Like
90

Just released a small update (v3.2) that changes the import behavior to (I hope!!) solve people’s import issues with too much data leading to an error. Check it out!

Release notes:

V3.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.
1 Like
91

Thanks @randy for moving this to Show and Tell! Would love votes from folks now that I can get them if you find this useful!

1 Like
92

I did not know about that utility, will definitely try it.

93

This is a great template. I was missing the Mint dashboards a lot.

Any idea why my “Net Worth” tab is showing “0” for everything? All the other tabs are working fine and I can see the accounts are correctly added in Column “O”.

It looks like the “Expanded Balance History” tab has a #N/A (“Did not find value ‘Account Id’ in MATCH evaluation)” error in Column J and #ERROR (Did not find value ‘Account Id’ in MATCH evaluation.") in column K.

94

J showing N/A shouldn’t be a problem – that just means you haven’t added the additional account properties sheet / closed date – but things are supposed to work without it.

If you expand the hidden columns on the right of the Net Worth tab, do you see any errors there?

95

The filename indicates v3.2 - Tiller Dashboard (Shared v3.2)
Version Control tab:
image


From Net Worth Over Time tab, the Cash account and Cash group amount is funky.
Cash amount is actually $160.
image

The most recent Cash Group entries in the Expanded Balance History tab:
image

96

Thanks, fat fingered the version number – fixed it.

As far as your other issue, that’s more concerning!

The formatting thing is just a formatting update I missed before I better understood how that worked. If you change the Balance column header (I27) to have “$#,#.00” as the format string it will fix it I believe. I’ve made that change for the next version.

As far as the number being wrong, its the same issue as before where you have an account and a group with the same name. I fixed the formatting problem, but I realize that didn’t fix where the amount is pulled from. I’ll have to do a bit more work to better differentiate those – in the meanwhile if you just make your group name something different I think that should make this work properly.

97

Think I fixed it for real now, v3.3 link above.

99

Hi CPS! This looks great! I got everything to work except in the income by Category tab I am getting an Error “Array result was not expanded because it would overwrite data in A5.” in column A. How do I fix this?

2 Likes
100

Thanks for checking it out!

That’s likely because you have more income categories / groups than I left space for. If you insert a few rows between the “Group” header and “Grand Total” to make space for your additional categories it should just start working.

2 Likes
101

Oh wow, great job. Very clean and easy to dive in.

1 Like
102

Perfect! It works now! Thank you!