I am looking for a way to use Tiller to find my recurring subscriptions/memberships, etc similarly to how the Truebill app does. Does anybody have any suggestions? Thanks.
1 Like
Hi @kim.dushinski,
One way to do this would be to make a new sheet that lists transactions based on how often the same description is used. This might help identify recurring transactions you don’t want.
If you are using the Foundation budget, you can put this formula in cell A1 of a new sheet:
=QUERY(Transactions!C:E,"SELECT C,SUM(E),COUNT(E),SUM(E)/COUNT(E) GROUP BY C ORDER BY COUNT(E) DESC LABEL SUM(E) 'Total', COUNT(E) 'Number', SUM(E)/COUNT(E) 'Ave Amount' ")
This query assumes that Column:
C = Description
E = Amount
If your Transactions sheet uses different columns for Description and Amount, change all the C’s to match your Description column letter and change all E’s to match your Amount column.
On the new sheet, you might want to format the Total and Ave Amount columns using the $ format.
Let us know if that works.
If you want to understand the formula, here’s a sort of English translation. Using the columns C thru E in your Transactions sheet, SELECT the Description column. GROUP the results by Description and well and get a COUNT of the number of transactions and the total amount of those transactions. ORDER the result by the transaction count descending. And finally LABEL the calculated columns as Total, Number and Ave Amount.
Advanced Formula
If you to limit the results to only descriptions that are (for example) greater than 10 transactions, you can add another query to the formula.
=QUERY(QUERY(Transactions!C:E,"SELECT C,SUM(E),COUNT(E),SUM(E)/COUNT(E) GROUP BY C ORDER BY COUNT(E) DESC LABEL SUM(E) 'Total', COUNT(E) 'Number', SUM(E)/COUNT(E) 'Ave Amount' "),"WHERE Col3 > 10")
Note the start QUERY(QUERY(… and the end ,"WHERE Col3> 10")
If you want to limit the result to a different number that 10 recurring descriptions, just change the number 10 to what you want.
2 Likes
I use a tag report to track my subscriptions. @heather shared a post about this here: Add the Tags Report for Transaction Tagging
2 Likes
Thank you so much for this answer! I gave it a try and it worked - sort of. Descriptions are not always exactly the same on recurring charges so not everything showed up. For example: these charges did not appear on the new sheet:
Debit Purchase -visa 10/08 card xxxx, Zoho Corporationx-x-4428ca
Debit Purchase -visa 09/08 card xxxx, Zoho Corporationx-x-4428ca
Debit Purchase -visa 08/08 card xxxx Zoho Corporationxxx-xxx-4428ca
Debit Purchase -visa 07/08 card xxxx Zoho Corporationxxx-xxx-4428ca
Debit Purchase -visa 06/08 card xxxx Zoho Corporationxxx-xxx-4428ca
Debit Purchase -visa 05/08 card xxxx Zoho Corporationxxx-xxx-4428ca
But these are the exact kind of things I am looking for.
2 Likes
Hi @kim.dushinski,
I’m glad it sort of worked for you. If you don’t do the advanced version of the formula, all those Debit Purchases will show up at the bottom of the new sheet since they each have just 1 Transaction per Description. Since the list is alphabetized they will all show up together.
Let’s say you want a list that sorts by the first TWO words in the Description. Then all those Debit Purchases will be grouped together. You could use this formula in A1 a new sheet:
=QUERY({Transactions!E:E,ARRAYFORMULA(SPLIT(Transactions!C:C," "))},"SELECT Col2,Col3,SUM(Col1),COUNT(Col1),SUM(Col1)/COUNT(Col1) GROUP BY Col2,Col3 ORDER BY COUNT(Col1) DESC LABEL SUM(Col1) 'Total', COUNT(Col1) 'Number', SUM(Col1)/COUNT(Col1) 'Ave Amount' ")
Again, this query assumes that Column:
C = Description
E = Amount
If your Transactions sheet uses different columns for Description and Amount, change all the C’s to match your Description column letter and change all E’s to match your Amount column.
If you wanted to match and group using the first THREE words, then:
=QUERY({Transactions!E:E,ARRAYFORMULA(SPLIT(Transactions!C:C," "))},"SELECT Col2,Col3,Col4,SUM(Col1),COUNT(Col1),SUM(Col1)/COUNT(Col1) GROUP BY Col2,Col3,Col4 ORDER BY COUNT(Col1) DESC LABEL SUM(Col1) 'Total', COUNT(Col1) 'Number', SUM(Col1)/COUNT(Col1) 'Ave Amount' ")
The formula is pretty complex. And you don’t need to understand it to use it. But basically, it splits the Description into each individual word using the SPLIT() function. Then it groups the results by the first two or three columns. The
ARRAYFORMULA(SPLIT(Transactions!C:C," "))creates a virtual array that splits each word from the Description column.
This would group all your Debit Purchase -visa together. Since you already found these recurring transactions, you likely don’t need to do this. But it might help others here.
1 Like
Even is simple pivot table that uses Description as the Row setting and then the values are COUTA of the description. Sort the rows by descending on the COUNTA. This shows me my top repeating transactions. But if the descriptions are slightly different that won’t help too much. A pivot table is super simple in Google Sheets.
Basic pivot table steps here:
1 Like