🏆 Transaction Tracker for Google Sheets

Google Sheets Show & Tell
, , , ,
41

I think the issue might be with the apostrophe. Can you try this code?
Copy and paste into A12:

=QUERY({INDIRECT(V14),INDIRECT(V15),INDIRECT (V19),INDIRECT(V18),INDIRECT(V20),INDIRECT(V21),INDIRECT(V19), indirect(V22), indirect(V24), indirect(V26)}, "select Col1,Col2,Col3,Col4,Col10,Col9,Col5, Col6, Col8 where " & if(D6="","","upper(Col2)  contains '" & upper(D6) & "' and ")& if(D5="","","Col9 ="""&D5&""" and ")& if(D4="","","Col10 = '" & D4 & "' and ")& if(H4="","","Col9 != '" & H4 & "' and ")& if(H5="","","Col4 != '" & H5 & "' and ") & if(B5="","","Col3 = '" & B5 & "' and ")  & if(B6="","","Col4 = '" & B6 & "' and ") & if(F6="","","Col6 CONTAINS '" & F6 & "' and ") &  "Col1 >= date '"&TEXT(AC13,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(AC14,"yyyy-mm-dd")&  if(H11=FALSE,"'","' and NOT Col4 MATCHES '" & TEXTJOIN("|",true,AA13:AA) & "'") & if(F4 = FALSE,"","and Col8 <> '' ") &if(F5="","","and upper(Col8)  contains '" & upper(F5) & "' ")  & if(D7 ="Negative","and Col5 <0 ","")& if(D7 ="Positive","and Col5 >0 ","") & if(H8 ="","","and Col5 <= "&H8&"")& if(H7 ="","","and Col5 >= "&H7&"") & if(F7 ="","","and Col5 = "&F7&"") & "Order by Col1 " &H6 &" ,Col9 asc Label Col1 'Date', Col2 'Description', Col3 'Group', Col4 'Category', Col5 'Amount', Col6 'Tags', Col8 'Note', Col9 'Account', Col10 'Institution'")
42

That did the trick! Thank you so much for all your help. I have one more question :smile:. Is there an ability to modify the filter drop downs to be able to select more than one item (but not all) within a particular filter group? For example, in a standard excel sheet when you drop down a filter you are able to unselect all and choose only the items you want to filter, checking all that apply. Unless I am missing something here (which could be very likely) I don’t see the option to do that within these filter drop downs. To illustrate I have provided a screen shot below. In this example, with shopping and last year selected as filters, it would be nice to be able to select multiple accounts at one time to view shopping across only the selected accounts vice only one or all. In the screen shot I would want to filter down to 1, 2, and 3 at one time to view shopping across only three of those accounts. The same functionality in all drop downs would also be very useful.

4C7D2119-9FA9-41DD-9094-D82F0B3C83A0_1_105_c
4C7D2119-9FA9-41DD-9094-D82F0B3C83A0_1_105_c1070×734 92.7 KB

43

Right now that can’t be done. The closest there is now, is to use the “Exclude Account” function, which will return whatever is selected, minus those transactions in the Exclude Account.
I will look into the multiple select, but it might be too complicated, or take up more real estate, since it might need manual input.

44

Hi Yossiea - I just added the recent version 1.13 to my Google Sheets. I noticed this version doesn’t have the “group by” option anymore. I used it to see all the transactions aggregated by account. Will you be adding this back in? Thanks!

45

It should still be there, but it was minimized, by default, for real estate. On top of Column I should be an option to expand. You can then leave it expanded.
image

46

Just added the TT template and really love the ease of filtering with it. Thank you. Saw this post, however, and created the new sheet and results highlighted some older data issues. Have been using Tiller since Jan 2021 but was primarily focused on expense categories… some accounts took on new names creating some of this mismatch. … One in particular, for example, in Bal Hist is “360 Money Market” which was changed to “Cap One MM”. (snip below) However the imported CSV transactions from Jan 2021 to Oct 2021 were named “360 MM”. The first two show up in Bal Hist, so the TT can be used - but in line with other commentors, requires filtering one, then another. The first 10 months imported were “360 MM” are not in Bal Hist, so they aren’t in TT as an option.

Q1> To get some consistency in data (and eliminate the need to filter two acounts), can I simply copy the “Cap One MM” over the older name “360 Money Market”? Would there be other data issues doing this?
image

Q2> Apparently missed the step in first set up that import transactions had to be in Bal Hist too. What’s the recommendation to put Transactions into Balance History so they are viewable in TT? I realize in my example 2021 is old data, but using this as a clean up to get sheets in sync.

Great tip to do this comparison and will retain to help keep in sync.

47

Thanks for the kind words. One note, your account names and balances all come from Balance History. The Transactions then look at that information.
What you might want to do is make a backup and then simply do a global find/replace. Or just rename the old accounts in Balance History, then go to Transactions and rename those accounts. Then, I’d go to the Accounts sheet and make sure everything is up to date.

1 Like
48

hi, Just added TT template and all is working but question on Group. I see all the groups I defined in Catagories sheet and thought if I selected one on the TT group pulldown I would get a list of all categories assigned to that Group but nothing displays. Read comments and see that Group column needs to be added to transactions. Did this but still nothing displays. Then realized I had to add group name to each transaction in the Group column.
With hundreds of transactions updating group column will take some time. Is there an easier way of doing this so I get TT group filter to work. The yearly budget groups using catagories sheet not needing anything in the transaction group column. Just looking for easier way to get group filter to work. Do I understand this correctly or am I missing something?

49

Add a column to Transactions and put this in row 1:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))

This assumes D is your Category. This will now populate a Group in the Transactions sheet.

2 Likes
50

Perfect. Just what I needed. Thanks

51

image
image1640×2360 407 KB

Get error when trying to add TT to a new SS

52

Can you change the filter to only select today or yesterday? It’s possible you have special character somewhere that is messing up the query.

53

Changed a category from macy’s cc to macy cc and now works.

54

Glad that work around works for you. I can fix it but it would make the query even more unreadable and difficult to maintain. Interestingly enough, I think many of the other filters accounts for that. If you want to try to edit the query, you can find where the col = B6 and add another double quote to the front and back of that.

55

Thanks.
Question: Have added a manual account and then a few transactions. The pulldowns show account, category, group, etc but can’t get any transactions to display. Any ideas? **** forget question got it working.

56

@yossiea I just took a second look at this tracker and it’s working great. Thanks for all the work!

57

hi, Added a new column to Transactions called Symbol (Stock symbols) and would like to group dividend amounts for each symbol. Added Symbol to download Group by in TT sheet but looks like I need to do something else. Can you help?
thanks,

58

The “Amount <=” filter does not work for me. I tried restoring the sheet and I have the same problem. Does the “Amount <=” filter work for anyone?

59

not just you@anthony.yeung >= works fine. <= be broke for me!

@yossiea - would you plz check this?

60

It works fine for me. I just tried with positive and negatives numbers. Are you using this filter in conjunction with the polarity filter?