Tiller Sheet Performance - How many is too many transactions?

I thought I would ask here since it might be valuable to others. At the beginning of 2020 I started a new foundations template and keep all 2019 data in separate sheet. No problem. Now I am starting to wonder if it makes more sense to have 2019 data in the same sheet with the 2020 data to make comparing months from previous years easier. I have a little over 2400 transaction in 2019 and doubt much will be different in 2020. So my question(s) are.

  • What do most people do? Do you keep multiple years in one sheet or do you break it up?
  • At what point, in number of transactions, does performance start to degrade?

Thanks,

Hugh

4 Likes

For comparison purposes, I do think it is easier to keep multiple years of data in my current year’s spreadsheet. I’m wondering if instead of storing all of your transactions from the previous year, you can just keep the totals of each category in your current spreadsheet. That way you can can have a Current Year vs Last Year comparison.

Also, if you are using the Foundation Template, you can try using the Trim Balance History Add-On. This will trim entries from the balance history tab. Check out the link below for more information.

1 Like

I have been using Tiller for 16 months now and many consider me a power user. I have been wrestling with these issues too. I thought about setting up a separate sheet for 2019 but decided against it at this point in time. In January 2021, I might split off 2019.

Performance is a factor of many things so it is hard to answer your question. I have 7,500 transactions. You should look to see how many balance history items you have as that might be a bigger issue than the number of transactions. I would not be concerned about 2,400 transactions. If you are having performance issues with 2,400 transactions, then it is likely something else.

Search the community as I have posted much on the topic of performance. I think Tiller should take a more active role regarding this but to date they have not. I understand that some things are out of Tiller’s hands and some of it deals with technology limitations. Performance issues are very frustrating. Tiller keeps saying I have too many tabs in my sheet. I do not think I have too many. The community and Tiller produce many great templates, tools, etc. Basically, my point is what good is it to have all this great stuff but only be able to use a few of them before you start having performance issues. All users will eventually face performance issues as they continually add data to their sheets and add new templates/tools to assist them in managing their ever changing personal financial situations.

Blake

3 Likes

I am not having any performance issues per se. I just broke out 2019 as a preventative measure. I was just curious what most folks do and if there is some threshold that if you get above x number of transactions, you are can expect issues. I may bring in 2019 transactions to test it out.

This is something that I have always been attempting to resolve. I migrated from other finance platforms and had data back since 2009 (in the same tab). This did not do well for Tiller. I also liked a lot of the data analysis that tiller labs provided and practically loaded most of them in one sheet. So my first issue was - too many transactions, and my second was too many tabs. This slowed down a lot the loading of the file. So I did some testing:

  • reduced the number of transactions (started from 2017, which was somewhat of a milestone for me): this did not work at all. The spreadsheet still took a while to load, the categories were not loading up fast enough, etc;
  • reduced the number of tabs: I figured, since Tiller allows me to have multiple spreadsheets, most likely they already predicted this behavior of too many tabs and is telling me to use separate files for different analysis types. This was somewhat better, but still not the best solution as the files still took a while to load;
  • test my browser: I use firefox as default, with not too many addons, but still enough to make it not so “optimum”. This was the key issue. I decided to use Chrome for Tiller as they are in GDrive, they can also work offline. This did improve the performance quite significantly. I was using a completely blank Chrome browser (no addons no sync… nothing). So I figured that this would be the solution, however, sometimes there would be errors in the “offline” mode and eventually make the file online only and again have some lag;

So after all this testing, I saw that maybe the best solution was to use the excel version of Tiller. I lose a lot of prebuilt functions from the google sheets, but at least I can get the transactions quickly, make comparison that I built myself… etc. So it has been doing well. A shame for the autocat though. This is being killer for me. But I am looking into an alternative.

So before trying to change anything in regards to data, balance history, or whatever, maybe it would be a good idea to try a clean browser and see how that goes.

1 Like

This is a bad problem for me, and may cause me to leave Tiller if I can’t find a workaround. I built my own reports in tabs, since the views I wanted aren’t provided by default. I’m using Google Sheet formulas to assemble the data in my reports, and learned many of the formulas by looking at what Tiller was doing. Any change in my spreadsheet (typing a character) retriggers most of the calculations to start all over again, which is painfully slow when compounded with network calls to Google servers.

Things I’ve tried to fix this:

  • splitting out my older transactions into another tab (>3 years old). Like other users here, I have many years of financial history from prior tools. This helped, but also excludes this data from my reports which I don’t like
  • researching on the internet what I could do to speed things up, trying suggestions one by one, none of which made a difference.
  • verifying that removing all my custom tabs does speed things up. Alas, then I don’t have the reports I want.

The root of my slowdown seems to me to be that I can’t stop all auto recalculation from happening any time a change is made in the file. I spent weeks learning Google Sheets functions to build the reports that I have, which are non-performant. From what I can tell, the next step would be for me to learn to code Google Apps in their external environment (so I could code a manual start/stop trigger function per report, and do all calculations outside of the spreadsheet in code instead of inline using cell formulas). That’s an expensive skillset I don’t want to invest in.

If anyone has figured out how to stop automatic triggering of recalculation on normal cell formulas – without external code – please please share!

1 Like

Hello Blake . I am a fairly recent Tiller user. I use it to track personal finances as it user friendly for me. I am trained accountant and am intrigued as to what someone would use the sheets for when you have so many transactions. What makes it better than say accounting software? Not nosy just curious and maybe I can learn something. Cheers Henry

@henryb

Below are my transaction rows by year. When I started with Tiller, I brought over prior history from other previous tools that I had used. It is not that many when you look at a single year. I do not budget, I mainly track the past. Tiller offers much more than just plain accounting. It is flexible and you can customize. I do not need Quickbooks, Peachtree, etc. Tiller works for me. You say you are an accountant; me too. I have spent almost my whole career in public accounting; 7 years with Big 4, 3 years in industry, and last 20 years with large regional firm. Please let me know if I can ever be of help. Blake

2015 793
2016 640
2017 1478
2018 1854
2019 2282
2020 498
total 7545

2 Likes

Ah cool Blake that gives me a better idea. Somehow I thought there were thousands in a year. Likewise I dont use it to budget but more as a tool to track spending. I am in Australia and although have not practiced accounting for many years you just cant take the accountant out of me. :grinning:

This may not be for everyone, depending on what you’re looking at, just an idea if you want to cut down transactions: I have a lot of transactions which are actually account transfers which I don’t find much value in. Typically you have one from your checking, and one to another account which represent the same transaction. You could remove the account debits and only leave the account credits as they are typically the only ones you’d want to see/track.

My sheet is very granular which is intentional. True, everybody needs to do what works for them. I share what I do as an example which might give others ideas. I just checked, I have 32 transfer categories and 2,478 transaction rows which I have the Type coded as Transfer. I doubt there is any user more detailed and granular than me. It all serves a purpose. Cheers, Blake

I’ve been a Tiller user since 2016. My current sheet has transactions starting in 2018 and has over 10,000 transaction rows.

I’ve never noticed performance issues due to too many transactions since it’s a one time import. Where I have had performance issues in the past is due to the number of accounts I was updating (50+). It would simply time out so I’d have to update them a handful at a time. The team has since fixed this issue.

2 Likes

Makes sense that it’s due to # of accounts being updated. Seems like a limitation of Google Apps Script. Curious @randy how you guys solved this issue.

The main Google Apps Script limitation we need to work around is the process timeout which is around 5 minutes. If a process can’t complete within that timeframe, it is terminated. (The timeout is longer for business accounts, I believe.)

As for the fix you are referring to, @jack… that fix was implemented by Tom Holman on the development team. I believe he updated the code to more-efficiently consolidate the write operations from many accounts rather than writing one-by-one. Perhaps, @tom will chime in with more details… :wink:

Cheers!

Hey @jack,

Sorry to resurrect a dead thread here, but I ran across this and thought I’d provide a little more detail.

Performance is a combination of many things, of course. The biggest culprit in this instance (updating sheets) is the add-on trying to update the sheet a bunch while it is doing formula re-calculations. This situation can be exacerbated both by the process we use to update the sheet as well as the quantity and complexity of formulas on the sheet. Side note: interestingly, the amount of data on the sheet or being imported to the sheet isn’t itself so much a problem; it just adds more for the formulas to dig through during a re-calculation.

As to why the number of accounts were an issue: we were initially iterating through each account and updating transactions for each account before moving on to the next one. This would mean that after the first account, Google Sheets started recalculating the formulas and everything slowed waaaay down. Now I believe we pull all the information from all the accounts first then try to add all the new data at the end of the process and hope it all gets there before the sheet can start recalculating.

This is a trade-off. It helps some with performance (it helps a LOT with some edge-cases - complex sheets w/ lots of accounts) but has other undesirable traits – for example, it would mean that any kind of error in the process (even on account 39 of 40) would prevent any transactions from being filled to the sheet.

There are definitely things Google could do to help us out (temporarily pause recalculation, for example), but I don’t want to throw Google Sheets under the bus here. Ultimately, this is a limitation of how we’re choosing to build the product. We’ve built prototypes of these update processes that work shockingly fast on very large datasets. However, a product strategy centered around those designs looks a bit different (smaller, modular add-ons w/ simpler spreadsheets) than what we are currently building (application-like experiences in sheets w/ more integrated feature-sets).

Hope this provides some useful - or at least interest - context. :slight_smile:

-Brasten

These are all good points @brasten.

Smaller modular add-ons might look like one add-on for the data feeds, one for AutoCat functionality, one for manual balance entry, one for onboarding users into the product (a tour), one for the transaction splitter, one for manually adding and reconciling transactions, one for report generation, one for CSV line item importing, one for the version management and control of spreadsheet templates (solutions from Labs) and so on OR just not having any of those features at all and focusing solely on Feeds > spreadsheets.

We chose the “(application-like experiences in sheets w/ more integrated feature-sets)” because we assumed people wanted these features and they didn’t want 17 different add-ons to achieve them. People asked for them, and yes we could have said no, of course.

I’m curious whether our most engaged cusotmers would really care about having 17 different add-ons and needing to jump around to multiple places to do discrete little tasks and don’t need us to build templates or the tooling to make it easy to try the templates out. OR do they prefer a more wholistic approach and are willing to accept the tradeoffs with performance in order to have the features that make it easier and faster to get the job done in a spreadsheet. :woman_shrugging:

1 Like

I managed to import my data from Quicken going back to 2000, so I have over 20,000 rows in my Transactions tab. I think the performance is fine and I’ve never considered moving old transactions out.

1 Like

It’s a monolithic approach, but not a wholistic one. A wholistic approach would consider how these parts interact and design the system with all those factors in mind for the best possible user experience.

The vast majority of Tiller users would probably use two or three, and the available actions would be highly performant and available in the menu structures. Our current product design is to send users through a sidebar UX (with “Launch…” in the menu) for every action even though we know the sidebar UX to be sluggish and heavy-weight. We try to save users clicks and probably end up costing them time and frustration.

This is a definite and hope to offer a more intuitive menu structure in Labs and reduce the latency people experience in the sidebar.

For some reason the menus work differently in the Feeds add-on due to how the auth works and we have to enforce the “launch” step first.

All in all I hear you :slight_smile: and I hope as the Google ecosystem evolves we can be even more intentional about designing for a balanced UX within the technical limitations and/or best practices.

I do feel like we have considered how these various parts interact and have designed with user experience in mind while accepting some tradeoffs due to technical limitations. If it were truly monolithic, everything would be in 1 add-on so you at least got us to break it up into two :laughing: