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

3 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

2 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!