Docs: Transaction Tracker (Excel)

Overview

This is an update to my Excel Transaction Tracker which now doesn’t require any other sheets to run.
With this tracker, you can select a category and instantly see transactions. Want to refine? Just select another drop down filter. Want to see all transactions for a month, for a specific account? You simply select the month in the drop down and then account. All filters are optional and you can use them in tandem with each other. (Except for the date filter which will have priority over the month filters.)

Installation

Download the Transaction Tracker Excel workbook.

Setup

Insert the Account Filter Worksheet

(Thanks to Randy for these steps. Docs: Account Filter (Excel) - Microsoft Excel / Show & Tell - Tiller Community (tillerhq.com))
Recommendation is not to use web based Excel for this.

  1. Open your linked Tiller Feeds workbook in Excel.
  2. Open the workbook you just downloaded containing Transaction Tracker in Excel.
  3. Right click on the Transaction Tracker sheet in the workbook you just downloaded and click “Move or Copy”.
  4. In the “To book” dropdown, select your linked Tiller Feeds workbook.
  5. Click the “Create a copy” checkbox.
  6. In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
  7. The new worksheet should appear in your Tiller Feeds workbook with the name “Transaction Tracker”.
  8. Navigate to “Data / Edit Links…” in the menu (not the ribbon).
  9. Select the link to the downloaded workbook in the box.
  10. Click the “Change Source…” button.
  11. Navigate to the active workbook (i.e. select your personal spreadsheet).

At this point your new template should be functional and linked to your local workbook’s data.

Usage

Simply select the filter you wish to use and your data will be reflected below, along with the total amount.
Two things to keep in mind:

  1. If you are putting in a specific date, that will negate the month filter.
  2. If you use this sheet on the web, the “Has Note” checkbox won’t work. If you wish to query transactions with a note, just type “True” in the field.

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yes

ohhhh i want this for Google sheets!

Here you go :slight_smile:
Transaction Tracker

Thank you!! this is amazing. It’s so fast and so good.

I can’t seem to make Type and Group work and would love to add Accounts but I can’t figure out how this amazing thing works. Where are the mechanics?

For now, Type just lets you filter the group filter to make it easier but doesn’t filter the results. I can add accounts, and not sure why I missed that. I’ll see if I can do it today.
But if you want to explore, the query is in B9 and the data comes from the hidden columns.

ahhh I should have figured out the hidden columns thing.

it’s such a fabulous tool. thank you so very much

You’re very welcome. It should be straightforward to add account since it is on the Transactions sheet. Otherwise I’ll get to it later and update. I accidentally submitted two posts but they are stuck in pending mode but I’ll add it to show and tell for Google

I fixed the Type filter, I saw what you meant, but it still doesn’t filter the results, just the group/category filters.
I did add Account to the filter and results.
Transaction Tracker - Google Sheets

ahhh on the type. now i get it and it works great and the account addition is just great.

really thank you for this entire sheet - it’s exactly what I wanted and had no idea.

Just curious why you should avoid the web based version? I find that it actually works quite well with the foundation templates.

Hi, sorry if I wasn’t clear. I meant for the downloading/updating of the template, not actual usage of the template or Tiller. I found it easier to do upgrades using the program rather the web version.

1 Like

Thanks so much for this, @yossiea! Your Transaction Tracker is simple but original and super handy. It is the Swiss-army knife of slicing and dicing transactions history. The UX is simple and intuitive. The performance is responsive. Bonus points for knocking it out a Transaction Tracker sibling in Google Sheets. Hat’s off.

We are super excited to award our first 2022 Microsoft Excel Builders Challenge spot award, a $100 gift certificate. :clap:

If you’re open to a little developer-to-developer feedback for a future release, here is what I’ve got:

  • The Category filter doesn’t seem to be wired in A10.
  • The sheet doesn’t work great when the Tags and/or Note are not present. (They are not standard in our Excel Foundation Template.) Is there a way to make the sheet more tolerant of failures to find these columns?
  • When no results are found, the user sees a #CALC error. Consider wrapping A10 in an IFERROR() function with a “No results” output. (Though this might hide other issues— like columns not being present.)
  • Consider changing the user-input fields backgrounds to a light green color to indicate they are user-editable.
  • I like the Transaction Tracker name but “Tracker” is such a broad term. This almost feels more like a Transaction Slicer and Dicer. :hocho: :kiwi_fruit:

Thanks again for all your hard work and creativity, @yossiea!

Thanks for the kind words! I will take a look at your suggestions and see what I can do. I know I was looking at some sort of filter to select which columns to include but not sure if that’s allowed. I think we can somehow figure out the tag/note column.
Quickly, with regards to the category filter, not sure what you mean since the category selection does filter out the results on my end.

Good point re: the Category filter… I must’ve have damaged the formula somehow in my testing. Just reinstalled and it worked as expected.

Another minor thing… In my sheet, because my Transactions table extended beyond the data, I was getting weird null results at the end of J4:J.

image

I think you can fix this with a formula like this (which also has the advantage of not using the Month column which can be unreliable for manual transactions):
=SORT(UNIQUE(FILTER(DATE(YEAR(Transactions[Date]),MONTH(Transactions[Date]),1),Transactions[Month]<>0,FALSE)))
You would want to put this in K4:
=SORT(J4#,1,-1)

Thanks for that pointer, I made the change and also pushed it out to Z.
I am also thinking of just removing the “note” filter since we don’t really use it and see about putting tags as optional.
I updated the template and marked it as Version 1.02

For the name, I think that Slicer and Dicer is good but many people might not be aware of what a slicer is. Perhaps Review or Analysis or something similar might work?

-edit- I seemed to have fixed the “tag” column, but can’t 100% confirm since I have tags and it does work when I delete but just want to make sure it works. What I did was put an iferror around the filter for Tags column, so if it’s not there, it’s #ref, which then blanks the column.


I am getting a calc error. see attached screenshot. any ideas?

It looks like it’s still pulling from the template and not your sheet. Right now it will show that if there’s no data. I don’t think I put the error wrapper in yet.

How do I fix it? I get the error when I try to change the source

Regards,

Bob Wegman
513-404-7623

I will work on it. I do see it happening now on my end as well.

I apologize. That was happening due to the Tag field/filter. I removed it since it’s causing issues and it’s not part of the standard Tiller fields. If you try again, it should work.
Current version is 1.03.