I'm Categorically Confused... Showing Simple Actuals Sheet

I am in the process of getting tiller configured with my data. What I am having issues with is Categories.

I was under the impression (maybe wrongly) that once I categorized my transactions the data for each transaction would be aggregated into each category automatically. Put simply, if I had five transactions for the “Restaurants” category then that data would be aggregated into the appropriate category on the Category sheet. For me, this would allow me to see how much total money was being spent in each category without entering the data manually.

Am I confused on how the Categories sheet is supposed to work?

I might not be understanding your issue but here’s a try …

The Category sheet is to identify which categories you want. For instance, Restaurants. Someone else may need Work Shoes and also Play Shoes. Whatever you need.

Then, on the transactions sheet, you have to marry the correct category with the transactions. So for transaction 1 - Bill’s Diner - you select Restaurants. For transaction 2 - Rhonda’s Steak House - you select Restaurants, etc.

(You can - and should - make liberal use of AutoCat, by the way, makes like way easier!)

Then there are several other sheets that take that info and aggregate it in many different ways. For instance, one place you can see how much money you spent in Restaurants each month is in the Monthly Analysis spreadsheet. Or the Category Report or the Category Rollup Report - and, several others, actually.

I hope this helps.

1 Like

That’s a great answer, @susandennis.

FYI, @juddbonham, you can also use the Monthly and Yearly Budgets within your Foundation Spreadsheet to aggregate transactions into categories.

Let us know if this solves the issue for you.

I have added all of the categories I want to the categories tab. I have also categorized all of the transactions in the transactions tab. I am getting around to using auto-cat but I am not going to get ahead of myself before I understand how it works.

Are you saying that the categories tab doesn’t populate the dollar amounts even though it has zeros for every month all the way across? Monthly Budget view is not populating either. I know I am new to this but, this does not seem intuitive to jump through multiple hoops to see data for each category when it could be displayed in the Category tab in the slots that are already meant for transactional amount.

The Category Rollup did achieve what I wanted I guess I just wanted the Categories tab to do something it wasn’t designed to do.

I’m new to this too and they have great videos that help you understand the intended methods. Like you, I thought the Jan - Dec Budget columns in Categories would be a great place for Actual data to show. That worksheet is intended for you to build your budget for monthly amounts. Since I’m interested in only tracking Actual, and not doing Budget yet, those columns are not used by me.

AUTOCAT will be your best friend to save time. For Groceries, my AutoCat value has 2 lines. The one for Kroger replaces “KROGER #0591 BURLESON TX” with simply the name.

image

As mentioned above, the Monthly Analysis will show the Jan - Dec spread by month of your actual transactions. The other Add On Templates will give you other views.

Here’s the link for the great webinars on getting started:

2 Likes

So, I am going to watch the webinar Tuesday which I know will help with my questions. I wanted it kind of “dumb” I guess. I was thinking the automations would go a little further in helping me understand my family’s money with just some basic input. I see now there is more work to be done. That’s ok! I know I will get the hang of it if I keep at it. For all of my tech know-how I am woefully unequipped to understand the in’s and out’s of a spreadsheet program whether it be Google Sheets or Excel. Practice, practice, practice!

At least I know I can post here and get great community support. My wife has her way of doing the budget down to a simple science and this is my way of being more on point with the finances. Anything that can help me get a quick glance or a deep dive whenever I have time is a step in the right direction!

I am sure I will be back with more questions soon!
Thanks from a fellow Texan!

1 Like

Welcome to using Tiller! I just wanted to mention since I didn’t see it yet, is that in the Categories sheet, the dollar amounts would be the amount per month that you have budgeted for that category. If you put in a dollar amount in one month, it will autofill to the remaining months. For actual vs. budget, there are several sheets that show that, I personally use Monthly Budget and that shows a sort of P&L for the month.

With a little formula magic, you could definitely create a simple sheet that renders the information you want to see, @juddbonham. Consider, making a copy of the Monthly Budget sheet and strip it down to just what you need.

  • Duplicate the Monthly Budget sheet
  • Unhide the hidden columns from J+ to the right
  • Build off the formula in V15 which sums category actuals within a period by deleting what you don’t need (see note below)

The formula is this:
=iferror(query(Transactions!A:AA,"SELECT "&K26&",SUM("&K27&") WHERE "&K26&" IS NOT NULL AND "&K25&" >= date '"&TEXT(K15,"yyyy-mm-dd")&"' AND "&K25&" < date '"&TEXT(eomonth(K15,0)+1,"yyyy-mm-dd")&"' GROUP BY "&K26&" LABEL SUM("&K27&") 'Actuals'"))

It’s a little complicated but the key references you will need to preserve are:

  • K15: the start of the query period
  • eomonth(K15,0)+1: the end of the query period (formula can be replaced with a static date)
  • K25: Transaction sheet column lookup for Date
  • K26: Transaction sheet column lookup for Category
  • K27: Transaction sheet column lookup for Amount

Have fun with it!

The reports mentioned previously in this thread work beautifully for displaying comprehensive category information. For a little more flexibility, I use the GoogleSheets pivot table function with a line for each category vs the sum of values. Then it’s easy to compare monthly or yearly totals by putting them in the columns.

2 Likes

That is exactly what I use to look at just actual data. The pivot table works perfectly for this requirement.

1 Like

Please explain @kennethehutchins what you mean by actual data? I have seen and heard this here but not exactly sure how you view that within the sheets.

Thanks.

The actual data is the information pulled from your connected accounts, and is part of the Transactions tab. In other tabs, Tiller aggregates budget data from the Categories tab and actual data from the Transactions tab, for example. when preparing the Monthly Budget tab or the yearly budget tab. The Category sheet might is an area where you define you categories, groups, and type (income, expense, transfer). It is where you enter your budget data.