Reduce data in Transactions and Balance History

Hello Everyone! First time poster, long time lurker … I’ve been using Tiller for a few years, but have only scratched the surface of the product and mostly use it to track and categorize transactions for tax purposes.

I know many people in the community have differing opinions about how many years of transactions to keep in the same spreadsheet, but I’m curious how to do accomplish this reduction regardless of whether I do it in practice or not. Sort of curious what kind of insights can be gleaned having all of that data in the sheet? What kinds of reports are you running?

Is filtering the most common way to keep the Transaction tab to a manageable amount?

If I did want to remove transactions from a certain year, do I do that manually in the transactions tab? Will that data just get re-populated the next time I sync my accounts?

Lastly, I have a ton of transfers between accounts filling my Transaction tab. I have the withdrawal from one account, then the corresponding deposit into the target account. Dividend re-investments, credit card payments, etc. They’re all considered transfers in my spreadsheet. I’m curious what strategies people have found to best deal with these types of transactions?

I have seen a few people said they just keep the deposit side of the transfer to make things more legible for themselves, I’m curious how to do that as well. How do I set a category to not be listed in the transactions tab? I see the “Hide from reports” column in in the Categories tab, but the Transactions tab is not a report, correct?

Sorry for all the questions. Personal finance has always been fairly opaque to me, but I’ve woken up to the powerful tools Tiller has made available and I’m starting to really dig in.

Thanks in advance for any information (or links to topics) you can provide!

Cheers,

Ben

2 Likes

These are all great questions.

For me, the best solution is to keep everything together… until performance suffers. I haven’t hit that point yet but it depends on how many templates you’re running, how much data you have, etc.

If your spreadsheet performance is suffering, I’d consider:

  • The Trim Balance History workflow in the Tiller Money Labs add-on. This utility will help with your Balance History sheet.
  • Making a copy of your Transactions sheet (e.g. Transactions Archive) and then trimming the main Transactions sheet to just a year or two. This won’t reduce the amount of data in your spreadsheet, but it will reduce the number of rows formulas in your templates need to scan every time you display your budget (i.e. it should help with performance).

When you delete data from your spreadsheet the Tiller Money Feeds add-on won’t refill the data IN THE ACTIVE SHEET… but it will fill your full account history if you create and link a new spreadsheet with the same accounts.

I’d consider making a copy of your spreadsheet before making these changes since they are lossy.

Hope this helps,
Randy

2 Likes

Am I correct in saying that these solutions are limited to the google sheets version and not excel?

Is the add-on even available for Excel?

In Excel, at the end of every month I manually delete the balances in the balance history sheet from the 2nd to the day before the end of the month.

For transactions I create a new spreadsheet every year so the current spreadsheet only has, at most, a year’s worth of transactions.

I make a new sheet every year. I think after a few years I finally have the right categories, but I was finding things never quite synced up and I didn’t want to go back more than January for recategorizing.

To still have historic data and an easy comparison, I take a snapshot of my annual spending for each past year and add those as tabs in the current year’s budget. I then manually adjust so categories align year to year directly in the current year spreadsheet. (I started out pretty granular and now do use more inclusive buckets.)

Also, I find that closing old accounts makes things clunky. Since I do a fair bit of credit card churning, having a fresh workbook with only active accounts makes the reporting more useful for me.

(I have been using Tiller since 2016.)

1 Like

I’m pretty new to tiller but have thought alot about this too. Where I have landed so far is that it is all about saved “filter views” for your transactions. So if you just go to your transactions page, you will see all the transactions, even the ones you hide from your reports. But then I set up a bunch of filter views: One for only transactions that haven’t been categorized, one for transactions that haven’t been reviewed, and to your point, one that doesn’t show any transactions that I mark as a category “Transactions to Hide” or “Accounts to Hide.”

I also have autocat set up so that any transaction that is tied to retirement accounts is categorized as “Accounts to Hide.” So while they still will show up in the transactions tab, I can filter them easily with a saved filter view, and I can custom format them so that they kind of fade into the background.

Here’s a screenshot of the custom formatting (with no filters applied).

1 Like

Sorry, @dminches, but we do not currently offer an analog to the the Trim Balance History workflow (or the broader Tiller Money Labs add-on) for Excel.

We offer an “add-in” for Excel but the focus is pure bank feeds. (It does not include the workflows, templates & reporting available in the Tiller Money Labs add-on.)

Randy

P.S. Personally, I’m pretty happy to hang onto all my historic transactions in one spreadsheet (currently about 20 years worth). I will revisit that once the spreadsheet slows to a crawl.

In Excel, by the end of the year the spreadsheet takes forever to recalculate and it is slow to navigate through. And, I have a very powerful computer. Keeping data for more than a year would not work for me.

Do you know if there is going to be future development on the Excel side? It has been a couple years since anything new has come out.

Are you using the budgeting template for Excel or just the Transactions/Balance sheets that the add-in creates automatically?

None of our larger templates have been built with performance as a goal so that would not surprise me, but if the core data sheets are slow that’s something we’d need to look into.

-Brasten

I am using the transaction/balances template in Excel and it is painfully slow even after just 3-4 months of data. Each month I have to delete tons of balances or it grinds to a halt. The formulas in the tiller helper sheet are inefficient and time consuming to execute.

1 Like

Hey, thanks for your feedback! That seems like a great solution to de-emphasizing transfer-related transactions. I’m familiar how to create and save filter views, but how do you do the conditional formatting for the strikethrough and text color?

In the transactions tab, select all the rows, and then Format>Conditional Formatting. You’ll have to set up a few rules, and its kind of like autocat but different, the rules on the top will override the rules on the bottom. This is my set up now:

In this set up, column C is the transaction amount column, and column H is the category Group.
Here is the specific formula for one:

And here is another formula - should give you a sense of what you need to do:

1 Like

Thanks again for the info and the great screenshots, really appreciate it! Had a question about your Group column in the above image: is that something you enter manually during your review step, or have you made your own secondary Autocat-style function that sets the appropriate Group?

Thanks again for the tips!

The group is a tiller labs add on. Once installed, you can set the group name in the categories tab. In the transaction tab the group will autopopulate based on what you have in the category.