Transaction Comparison - Google Sheets

What is the goal of your workflow? What problem does it solve, or how does it help you?
Sometimes it’s useful to compare transactions from one time period against transactions from another time period. This allows you to specify two time periods, and then an optional Category and/or Tag to filter by.

How did you come up with the idea for your workflow?
Someone in the community was looking for this functionality, and I could envision what it might look like.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Copy the sheet from the link below to your Tiller Foundation Template by clicking the triangle to the right of the sheet name at the bottom, choosing ‘Copy to’ and then ‘Existing Spreadsheet’. Choose your Tiller Foundation Template as the destination and the new sheet will appear there. It will automatically find your Date, Description, Category, Amount and Tags (if you have one) columns in the Transactions sheet as long as you have those column names. Only change the green highlighted cells. By default, it will compare all transactions from January of 2021 against those from January of 2022. You can change those start and end dates, and can filter the transactions down to a Category, a Tag, or both (only transactions that have that Category AND that Tag). A ‘Total Amount’ appears at the top of each list to make it easy to compare the overall total of all the transactions shown.

Is it ok for others to copy, use, and modify your workflow?
Yes, and please let me know if you have an issues, or if you have ideas for improvements!

If you said yes above, please make a copy of your workflow and share the copy’s URL:
You can now find this in the Tiller Community Solutions extension, called “Transactions Comparison”.

GREAT idea! Unfortunately, I hit a snag that I’m not smart enough to noodle out.

I get a ref error in B5

Screenshot 2022-01-25 10.38.01 AM

I’m guessing you don’t have a ‘Tags’ column on your Transactions sheet? I guess I laid everything out assuming it would be there. The quick fix would be to add one. I’ll have to figure out how to handle sheets that don’t have one though, since many people don’t use tags.

YEP!! I just slapped a column to the far right and added the word Tag to it. No bueno, of course, so then I changed it to Tags and YAHOOO!!

This is a fabulous addition to the Tiller arsenal. Thank you!!

No problem. I’ll figure out how to better handle the absence of a Tag column. Let me know if you run into any other issues or have suggestions!

OK, I figured out a quick fix if you don’t want a Tags column. I’ve updated the template with these changes, so you could delete the sheet and recopy it, or you can change the following two cells in your existing sheet (you’ll need to unhide the columns on the right side of the sheet):

  • Change cell S10 to =IFNA("'"&P$5&"'!$"&R10&"2:"&R10,"'"&P$5&"'!$A2:A")
  • Change cell Q13 to =IFNA(SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",FILTER(INDIRECT(S10),INDIRECT(S10)<>"")),",")))))

Basically, if I don’t find a Tags column, I have the query look at the empty ‘A’ column on the Transactions sheet instead.

1 Like

Deleted the tags, deleted the sheet. Copied a new one over and BAM! Worked perfectly But, now, with this sheet, I’m thinking about adding some tags to my transactions… This is truly a very cool new thing. Thank you.

1 Like

I updated the template to include a checkbox to ‘Filter Transactions’. This would only be useful if you weren’t already filtering by Category. With the box checked, if you are filtering by Tag or have no filter, any transactions with Category or Group names that contain ‘Transfer’ will not be shown.

1 Like

I updated the template to include the ability to filter by Description as well. You can filter to only transactions that have descriptions that match what you type in, or you can get very powerful by using Regular Expressions (eg. .Google.) to filter down to exactly what you’d like to compare.

2 Likes

I really like this and already used it to find some discrepancies.
One thing I would ask if it’s possible to restrict those categories that are hidden. (For example I have some transfers that don’t always balance out, or “debt payoff received” items.)

I updated the template to properly hide transactions with Categories marked as ‘Hide’ on the Categories sheet instead of trying to match ‘Transfer’ in the Group or Categories column. The checkbox is now called ‘Filter Hidden’, and is checked by default (which hides hidden transactions). This helps it behave the same as most of the other Tiller solutions. Uncheck the box if you want the list to include transfers and other ‘hidden’ transactions.

Thanks, I saw that a few minutes after I posted. :slight_smile:
I was in middle of working on a work-around so luckily I don’t have to do that.

One suggestion for a feature is a drop down for date periods, so I don’t have to manually change four dates, like “last month, this month, YTD, QTD, etc.”

Could also be neat to have an easy way to compare this month, last month, YTD, etc… Just like the “Period Comparison” sheet does it. I’m not going to pretend I understand how the custom selection works on that sheet, but it’s pretty cool.

@tilleruser @yossiea I updated the template to use the same menu of periods that the Period Comparison sheet uses. Delete your old template and copy again and let me know if you find any problems!

@jpfieber it seems there is date validation on the Description input preventing you from inputting anything. Also it seems the note in there no longer describes that this is a regex and seems to suggest just inputting any text will match your descriptions. This looks great though, thanks!

Just fixed both. Thanks!

2 Likes

Thanks! This is a really great tool. I just modified one thing and added a Group column and filter, so I can filter by group or category.
-edit-
What I did to add Group/Category filter is as follows:
First I added a few columns to the sheet, so I can fit in the group/category into the results. This meant columns on the right moved over.
I added two rows in Column T-W and used the Category sheet rather than the transaction sheet to pull cat/group;
Column T is where you should have a list of fields, and W should be the final result, such as Transactions!Amount or similar.
Column T, I put in two rows, one with “Category” and one with “Group”.
Column U, =MATCH(T10,INDIRECT("'Categories'!$1:$1"),0)
Column V,=char(64+U10)
Column W=="'Categories'!$"&V10&"2:"&V10
I added a group dropdown, simply copy/paste from the category dropdown.
You should already have a list of Groups, in my case it’s in Column V.
Just add that list to the data validation, replacing the list for Categories in the new Group dropdown.
Then, in the header query for the Category list, I replaced the formula with an if statement, using the Category sheet (the new field you created prior):

=if(I5="",unique(SORT(filter(INDIRECT(W12),(INDIRECT(W12)<>"")))),sort(filter(indirect(W10),indirect(W11)=I5)))

Now, the Category drop down list will return all categories, and if you select a Group, then it will filter those categories to match the correct group.

1 Like

One more bug/feature…
The query for filtering transactions should “upper” both Col3 and the drop down.

upper(Col2) matches '" & upper(I11)

I missed a few transactions that had different cases.

Also, if you use col2 contains i11, you don’t need to use Regex, which might be easier, but you’ll then lose the Regex capabilities. I’m also not sure about speed.

I like what you’re laying down here. I added a ‘Group’ dropdown, as well as a group column to each side of the comparison. Now you can compare directly by group, or, as you suggested, you can further break it down by category, with a Category dropdown that’s limited to that group. Also, had to go back and fix my custom dates, which are hidden down below the filters, only showing up if you choose ‘Custom’ as the period (not sure if you noticed them hiding down there, might want to check on their health). Thanks for the suggestion and for doing all the heavy lifting on the implementation!

I added this, thanks! For now I’m thinking the extra power of Regex might be useful for some, but if performance proves to be an issue, that could be revisited.