User Request: Detailed Category Tracker

Google Sheets Show & Tell
, , , ,
1

@lillytryon asked this question in another thread:

Is there a way that you can create a sheet that allows me to choose a category and see the details of transactions for that category only, for a defined period of time? For example, maybe I want to review the gift category to review transactions during December. Or my Reimbursement category (used for expenses that will be reimbursed–employer, health insurance, etc.) to see what I need to submit for reimbursement for the last quarter.

I initially pointed her to the Category Tracker solution, but that wasn’t exactly what she needed reporting-wise.

That’s sort of what I’m looking for. But I need the dates and details (notes, tags) for each transaction. And it wouldn’t be helpful at all if identical descriptions were combined into a single summed line.

Unfortunately, I didn’t know of any other solution that met her needs reporting-wise. The Category Tracker was close, but not quite there.

After playing around with a copy, I was able to adjust the Category Tracker solution to display discrete transactions, including date, description, amount, tags, and notes. Aside from the more detailed and discrete reporting, it is identical in usage to the Category Tracker solution.

You can add a copy of my Detailed Category Tracker solution to your existing Tiller Foundation spreadsheet, and it should be ready to report on transactions.

There is one known issue with the sheet as it is now, and that is that if your Transactions sheet does not have a Notes column added, the report will not populate due to the column lookup failing. To resolve this, you can either add a Notes column to your Transactions sheet, or you can modify the solution to not include a Notes column by replacing the formula in cell D7 with the below formula:

=iferror(QUERY(Transactions!A:AF,"SELECT " &L4& ", " &L1& ", " &L3& ", " &O1& " WHERE " &L4& " >= DATE """ & text(F4, "yyyy-mm-dd") & """ AND " &L4& " <= DATE """ & text(F5, "yyyy-mm-dd") & """ AND " &L2& " = '" & F3 & "' ORDER BY " &L4& " ASC, "&L1&" ASC, "&L3&" ASC", -1),"")

I hope that this helps a few people out!

Update:
Thanks, everyone! I really appreciate all of your feedback and suggestions along the way.

I’ve made a new enhancement, adding in the option to query either at the category or group level, still having the option to show a transaction summary or detailed list. This was a substantial enough change that I have created a new template, the Transaction Tracker . Please try it out and let me know what you think. :slight_smile:

Update 2:
I made a minor correction on the Transaction Tracker to the Total for Category/Group label in E8 and G8. The header wasn’t changing with the Category/Group dropdown, and the sum wasn’t taking the column drift into account when looking for the Amount column.

If you want to simply copy/paste formulas, you can replace them with the following. Otherwise, you can replace your entire Transaction Tracker sheet with the template.
E8

="Total for "&G3

G8

=IF(G3="Category",sum(F10:F),IF(G3="Group",sum(G10:G),""))

2

Sounds like an interesting tool to have. I tried it out but it didn’t work for me. I previously removed the blank column A, and I’ve also added a Tags column, so I think the formula is looking in the wrong columns for my setup. I suspect others may also have issues if they’ve added various ‘Solutions’.

3

That’s odd… It shouldn’t matter if you’ve added or removed any columns to the Transactions sheet (besides the ones being searched for) as the sheet looks up the proper columns based on the header titles using regexmatch(), address(), match(), and indirect(). If you’ve renamed any of the searched for columns that would cause it to fail, however. Could you unhide the hidden columns on your copy of the Detailed Category Tracker and share what is showing for cells J1:O6?

4

Wow! Thanks so much! I copied the Tracker to my Tiller Foundation spreadsheet. I then added dates and category, but nothing populates.

5

@jpfieber and @lillytryon,

I’m not sure what is causing your issues. To test, I created a fresh Foundation Template spreadsheet and added the sample data provided in this forum, and added the Tags and Notes columns as those aren’t created by default. I added the Detailed Category Tracker to the spreadsheet and everything worked immediately upon specifying a date range and category.

image
image1059×433 41.9 KB

I also tried adding and deleting unnecessary columns to the Transactions sheet and there was no negative impact. The only thing I can think of is that you are missing or have renamed one of the key columns that the sheet is looking for. If your Transactions or Categories sheet is missing or has renamed any of the below columns, no data will populate.

Transactions

  • Description
  • Category
  • Amount
  • Date
  • Tags
  • Notes

Categories

  • Category
  • Hide From Reports

Here is what the column header lookup information looks like in my hidden section of the Detailed Category Tracker sheet.
image

Here is the same section with Transactions : Amount renamed to Amt and Transactions : Tags removed.

image

If you have renamed any of the above columns, you can transpose that name change into columns K or N. If they are completely missing, you can add them to your Transactions or Categories sheet. Once you have resolved any #N/A errors, the report should run as expected.

6

OK, think I have mine figured out. I expanded the hidden columns and saw that O2 was unhappy. You’re looking for ‘Notes’, and for whatever reason, my column was named ‘Note’. I changed it to Notes and now “Transaction Description By Category” is filling in properly. I’m not seeing anything under “Category Amount Summary” on the left side, is that one supposed to fill in as well (don’t really need it as I can get it from the original sheet, just curious). Thanks!

7

That was my issue, too. Fixed now! And the tracker is marvelous!

8

Glad to hear it, @jpfieber and @lillytryon! I’m glad I was able to help fill a need. :slight_smile:

I see what you are referring to, @jpfieber, and that’s my oversight. I didn’t update the formula for that section to refer to the shifted columns, so it wasn’t displaying anything. I’ve updated the Detailed Category Tracker to fix that and some other formatting details. You should be able to copy it over to your foundation spreadsheet and everything should still be in working order.

1 Like
9

Yup, works great now! Not to give you more work, but wondering if it would be difficult to have the option on the same sheet to either show the summary or the details. Maybe a checkbox or a dropdown that flips between summary and details, so if one still likes the summary, they don’t have to keep two sheets.

1 Like
10

That’s a great idea! I’m definitely a fan of reducing overhead. I’ll look into it and see what I can do. Seems like a simple IF() checking said dropdown/checkbox and changing the formula accordingly should be able to do it. :thinking:

11

This is super cool, @cculber2. I love how you remixed & built on an existing solution.
I tried it out and it is working great for me.

Two quick thoughts on the Notes issue…

First, the column header standard is singular, so most users with have a Note column, not a Notes column.

Second, a small tweak to your query in D9 will make the formula work even if the Note/Notes column is not present…

Just change the part of the query that fetches the Note column from this:
&L5& ", " &L6&
To this:
&L5&iferror(", " &L6,"")&

The final formula should look like this:
=IF(F3="Detailed",iferror(QUERY(Transactions!A:AI,"SELECT " &L4& ", " &L1& ", " &L3& ", " &L5& iferror(", " &L6,"")& " WHERE " &L4& " >= DATE """ & text(F5, "yyyy-mm-dd") & """ AND " &L4& " <= DATE """ & text(F6, "yyyy-mm-dd") & """ AND " &L2& " = '" & F4 & "' ORDER BY " &L4& " ASC, "&L1&" ASC, "&L3&" ASC", -1),""),IF(F3="Summary",iferror(QUERY(Transactions!A:AI,"SELECT ' ', " &L1& ", " &L3& " WHERE " &L4& " >= DATE """ & text(F5, "yyyy-mm-dd") & """ AND " &L4& " <= DATE """ & text(F6, "yyyy-mm-dd") & """ AND " &L2& " = '" & F4 & "' ORDER BY " &L4& " ASC, "&L1&" ASC, "&L3&" ASC LABEL ' ' ''", -1),""),""))

I’m hope this pays down a tiny fraction of my substantial formula debt to you, @cculber2… :wink:

2 Likes
12

Thanks, for the love and suggestions, @randy! I’ll apply those tweaks along with @jpfieber’s request as soon as I have a moment. With those changes we may have a working replacement for the existing Category Tracker.

1 Like
13

I’m open to it, @cculber2!

1 Like
14

Changes are in! The report now pivots quickly between a detailed list of transactions and a summary of transaction descriptions with a simple switch of a dropdown, and thanks to @randy’s suggestions the query is more resilient against a missing Note column (I renamed this from Notes to Note in the hidden section to be in keeping with user conventions). I also took it a step further and applied the same fix for the Tags column as that only gets added with the Tags Report solution, making the report completely Foundation-compatible.

Thank you, everyone, for helping debug my work and give us a useful tool!

1 Like
15

Well done, @cculber2. Thanks for all your hard work.

1 Like
16

Looks great, I just replaced my Category Tracker sheet with yours!

1 Like
17

This is a fantastic solution… better than the original! Great work. I’ve made it my primary Category Tracker and think Tiller should make this the default for the Foundation Spreadsheet. Yes, someone could sort their transaction columns to see the same info, but this is SO much easier and elegant.

Thanks for developing this!

3 Likes
18

Just installed the Detailed Category Tracker…works great! Many thanks and appreciate the posts and insight!!!

1 Like
19

VERY useful and zippy. Thanks so much for this.

1 Like
20

Thanks, everyone! I really appreciate all of your feedback and suggestions along the way.

I’ve made a new enhancement, adding in the option to query either at the category or group level, still having the option to show a transaction summary or detailed list. This was a substantial enough change that I have created a new template, the Transaction Tracker. Please try it out and let me know what you think. :slight_smile:

1 Like