Showing Transaction Data on a Calendar (Excel version)

Overview

A way to see your transaction data on a calendar. Data is aggregated by groups which can be customized per week.

Google Sheets version available here (link)

Installation

Open the template (here), and move or copy the calendar sheet into your Tiller workbook.

Setup

  1. On the left of the sheet, select the accounts to include
  2. On the right side of the calendar, choose the groups you want to breakout for each week
  3. At the top left, choose the year and month to display, and decide if you want to restrict data to only the chosen month, or for all dates shown in the calendar

Usage

Helps visualize which days of the month you have the biggest expenses

Permissions

Is it ok for others to copy, use, and modify your workflow? - Sure, go for it

Can’t get it to download.

This item might not exist or is no longer available

This item might have been deleted, or expired, or you might not have permission to view it. Contact the owner of this item for more information.

1 Like

I tried it on Chrome and Edge on a computer I’m not logged into, and it’s working for me. Can anyone else confirm they can or can’t download the template? Thanks!

I tried using Firefox and Edge and both have that same error “This item might have been deleted, or expired, or you might not have permission to view it. Contact the owner of this item for more information.”

Copying templates in Excel isn’t nearly as easy as it is in Google Sheets. Tiller put together an instruction sheet on how to do it, here’s what I’ve been putting in my documentation:

  1. Download the template from here: <link to your template, I put mine in OneDrive and share from there>
  2. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.

I was surprised and impressed at how @alan.heatherley published it. From his OneDrive, I downloaded Excel-Calendar-Tiller.xltx and copied that into my workbook. There was no need to futz with data links. Has anyone else tried the XLTX format before?

As for the functionality, the worksheet connected to my local data immediately, but I’m having some issues with the formulas… which are frustrating me because the formulas make sense. For me, only the Other rows (at the bottom of each day) are filling. I can see data in the X:AC area that should be filling into the days but somehow isn’t. The formula makes sense to me but I’m guessing there is something fussy about the filter:

=IF(AND($B$3="This Month Only",MONTH(H$29)<>$Q$1),"",IFERROR(SUM(FILTER($Z$2:$Z5000,($X$2:$X5000=H$29)*($AC$2:$AC5000=$C30))),0))

… Like maybe the worksheet or Transactions dates has an hour offset…? :person_shrugging:

Is it working for others?
It looks like it should work or is very close.

Thanks @alan.heatherley for continuing @jpfieber’s pioneering habit of publishing your templates on both the Sheets and Excel platforms. Per our Tiller Builder Rewards Program, we add half again to Show & Tell awards when templates are published on a second platform. So, we’ll send you another $150 in gift certificates.

Thanks for your great and inspiring work.
:trophy:

2 Likes

Hi @randy, thank you for the award!

As for the blank days, are the Groups filled in on the left of the calendar? If so, can you double-check that the formulas are pointing at the groups? They should be, but maybe I goofed something when I moved the calendar to it’s own file.

I think the lack of data links are because I used INDIRECT to refer to the Excel tables. I didn’t do it that way for that purpose, but it might be a side-effect. There’s a performance hit for using INDIRECT, so it’s probably not good for big data sets, but I haven’t noticed any issues on my Tiller sheet, but I only have a couple of years worth of data. Maybe someone with more might see an impact. I can optimize the queries further, if necessary.

Yes. I have the Groups filled in in C6:C11.

I don’t think you need the INDIRECT() formulas since Excel has the "Transactions[Date]” functionality to find columns via header references (which Sheets does not). Frankly, I’d remove them. That said, the INDIRECT references aren’t the issue in this case since the data rendered in X:AC via the INDIRECT() references contains the transactions that I’m hoping to see in the calendar, but the formula in H31 (that relies on that data) but does not contain INDIRECT references is failing to pull in the transaction.

I dug into this a little bit more and figured it out… The problem was that, since I had some groups unassigned in my Categories sheet, I was getting VLOOKUP failures in column AC which were faulting the IFERROR() function in the calendar body D:J, resulting in empty cell values.

The easiest solution is to update your formula in X2 to wrap the VLOOKUP with an IFERROR like this:
IFERROR(VLOOKUP(INDIRECT($Q$12),INDIRECT($P$14),2,FALSE),””)

Hope this makes sense.
Randy

@randy Thank you for the feedback! I’ve updated the template with that IFERROR statement and took out the INDIRECT’s. Thanks!

Hey Alan,

I’m seeing some issues with the latest version in OneDrive. My Accounts aren’t loading. There may be other downstream issues but initially it looks like the formulas are broking in T2 and V2. Both are using INDIRECT() references to strings in column Q that no longer exist (Q11 and Q16).

Is it possible I don’t have the right version?

Randy

Oops! Fixed. Thanks!

Works now for me. Thanks for staying after it.

Randy

Hi Alan,

I was able to get past the error message by hitting the “back” navigation in my browser. The shared One Drive version is 6 days old, so I don’t think I have your latest version. Also, it seems my groups are not being recognized as most of the entries flow to “other” instead of the correct groups I chose. Any suggestions?

Thanks,
kra808

Hi @kra808, I wish I knew what was up with the OneDrive link. It works for me, but obviously a lot of others are having trouble with it. I’ll keep working on it, though.

As for the mis-categorized data, what’s showing in columns X:AC? Do you see the appropriate groups in AC?