Excel Tiller-Money-Tracker Very Slow... but Easily Fixed (Excel Tables)

Microsoft Excel
1

I’m exploring switching to Excel from Google Sheets.

I was surprised to find that my data loaded into the Tiller-Money-Tracker excel template resulted in a very slow spreadsheet. Is anybody using this template for every day use?

It takes roughly 14 seconds to recalculate my spreadsheet. I’ve only got about 5000 rows in my Balance History and 2500 transactions representing two years of data across about six accounts.

I decided to look into this and found it pretty easy to find some easy optimizations.

I found that about half of the computing time was coming from the TillerBalTable sheet (which is normally hidden). I found that if I modified the formula in column B from this:

=IFERROR(INDEX(INDIRECT(‘Tiller Helper’!$J$23), MATCH(0, IF(INDIRECT(‘Tiller Helper’!$E$23)<>“”,COUNTIF($B$3:B3, INDIRECT(‘Tiller Helper’!$J$23)),“”), 0)),“”)

To this:

=IF(LEN(B3)>0, IFERROR(INDEX(INDIRECT(‘Tiller Helper’!$J$23), MATCH(0, IF(INDIRECT(‘Tiller Helper’!$E$23)<>“”,COUNTIF($B$3:B3, INDIRECT(‘Tiller Helper’!$J$23)),“”), 0)),“”), “”)

The idea being: skip the very expensive COUNTIF call if the prior row is empty – in other words, when there are no more account IDs to find. This row is copied down for about 200 rows, so this reduced the time by a lot. The table now computes in about 0.15 seconds on my machine, down from just over 6.

The approach used across the sheets in this template have more room for improvement, I think. I see things like:

  1. Heavy use of INDIRECT(), which is a volatile function in Excel and so causes recomputation of essentially everything in the sheet when anything changes.
  2. The COUNTIF() trick used above could possibly be much faster if newer Excel features are used, like UNIQUE() and perhaps XLOOKUP().
  3. Using Excel tables and structured references could make the formulas more readable and faster by eliminating the use of INDIRECT().

Taking the last example, the final formula I came up with in the TillerBalTable sheet was actually this:

=IF(LEN(B3)>0, IFERROR(INDEX(BalanceHistory[Account ID], MATCH(0, COUNTIF(B$3:B3, BalanceHistory[Account ID]), 0)),“”), “”)

To do this, I had to make the data in the Balance History sheet a table, which let me reference the data in it with a readable name.

5 Likes
2

@MAtta, very cool write up. I have thought about making the move too and if I do, this will help. Great job.

3

It’s also worth noting that you can use the add-in on a blank spreadsheet and it will build out just Transactions and Balance History tabs, if that’s all you’re looking for or want to build out additional tabs from the raw data. This is what I do across the board as our formula-heavy templates are too heavy-weight for my purposes.

4

This is good to know.

I was able to get the Tiller-Money-Tracker template calculation time to 0.5 seconds, down from about 14 seconds as provided by Tiller. It now feels snappier than the equivalent Google Sheets Tiller template.

I am a complete Excel newbie. All I did was figure out what each formula was doing and re-write it in a more “modern” way, as far as I could decipher from tutorials on the web. I wasn’t shy about using newer features found in Microsoft Excel 365. It was a nice learning exercise. The resulting formulas were both faster and easier to understand than anything I’ve seen in Google Sheets. I’m sure there are more areas for improvement.

The Excel features like tables and named ranges are much more fleshed out and useful than what is in Google Sheets. I find some of the logic related to how INDIRECT is used almost incomprehensible as used in the Google Sheet Tiller templates. It is almost a https://en.wikipedia.org/wiki/Write-only_language. In contrast, the Excel formulas can have useful names that make it clear what the formula is trying to accomplish, and I find them much easier to understand when revisiting them. This left me quite impressed.

Case in point. These formulas do the same thing. Guess which one I find easier to understand. It also seems faster too.

  1. =IF(TrackerCategories="","",XLOOKUP(TrackerCategories,Categories[Category],Categories[Type]))

  2. =IF(A14="","",VLOOKUP(A14,INDIRECT(‘Tiller Helper’!$B$14),@COLUMN(INDIRECT(‘Tiller Helper’!$D$13))-@COLUMN(INDIRECT(‘Tiller Helper’!$B$13))+1,FALSE))

2 Likes
5

Does your workbook still update successfully?

Excel (beta) add-in is pretty unstable in that it will just randomly stop updating and throw an error during the update process when significant modifications are made. In the past, customers that had introduced data tables broke the updates, but maybe this has changed. :woman_shrugging:

6

So far the Tiller Excel (beta) add-in updates still work with my workbook. Both my Transactions and Balance History sheet are now Excel tables (not Excel data tables, which is apparently a different thing that I know nothing about).

1 Like
7

Heather, what is the Tiller plan for Excel? It seems your team has stopped excel development and bug fix. I prefer Excel but lack of meaningful development means no new templates etc.

1 Like
8

Thanks for the tips.

I looked at the formulas in my TillerBalTable sheet and they already have the “IF(LEN))” logic. I didn’t put it in there.

Could you be using an old template?

9

See Impact of "Money in Excel" feature on Tiller - #3 by peter. That is from April, and suggests that Tiller might be looking at Excel again soon.

10

Many of the TillerBalTable columns are fine. I think it may have been just column B.

I downloaded a pristine copy of the template from Using the Tiller Money Tracker template for Excel | Tiller Help Center and it looks like column B of the TillerBalTable sheet does lack the IF(LEN(… logic.

11

I am going to try an edit strategy to see if it makes a difference.

I am defining ranges which are effectively defined on the Tiller Helper sheet. I will make the ranges larger (more rows) than is currently needed. I can then use these ranges to replace the INDIRECT logic. That should hopefully reduce the calculation times.

12

My original intent in switching from Quicken to Tiller was to use Excel. I found the Google Sheets implementation so much more feature rich, that I initially started my financial tracking with that. My experience with the Tiller’s Excel sheet was the same as the OP: SLOW! I tried tinkering with the supplied formulas, but actually ended up just using the data feeds and generating my own worksheets from scratch. My financial situation is a little different, so I am not so interested in a traditional budget which the Google Sheets implementation is focused on. I primarily use pivot tables, rather than complicated array formulas, and my workbook is very fast. This year I am running Google Sheets and Excel in parallel, but am thinking of doing only Excel next year.

1 Like
13

Following up on @matta’s excellent post on using Excel tables to improve query performance…

  • For those of you who implemented Excel tables in your Transactions and/or Balance History sheets, have you had any issues with your Tiller feeds from the Excel add-in?
  • Have you ever had new rows added to your sheets that are not contiguous with the existing data and don’t expand the Excel table area?

Thanks!

1 Like
14

The LEN is STILL missing. I added it to mine and it is faster. I have 15 years and like 27k rows on Transactions so any speed improvements will be incredible.

15

Due to speed issues with the Tiller-supplied spreadsheet I decided to keep only the current year’s transactions in the spreadsheet and move all prior year’s transactions to a separate spreadsheet. I realized that for day-to-day use I didn’t need prior year transactions and now the spreadsheet is super fast.

In the spreadsheet with prior year transactions I wrote some formulas and did some filtering so I can pull any analysis I want.

1 Like
16

@dminches, did you try out the beta Monthly Budget template yet?! :grin:

17

I have been populating it. Works very nicely. I need to link it to get full functionality.

1 Like