Friends! I come to you in the final hour! After hours in Tiller and on the message boards, I’m yet to find a solution for this, and I’m hoping that finally asking out loud will bring help and save me from trying yet another budgeting/money tracking software.
What I’m looking for doesn’t seem like it should be that complicated, and I think it is probably my lack of Google Sheets knowledge that is creating the problem but here we go…
I own two businesses and have personal spending. I use separate bank accounts and credit cards for each of those three entities. I found early on in Tiller that there was not a good way to see these all in one Google Sheet, and so I now have three separate Tiller Sheets (one for each business and one for personal)… I track the same 35 spending categories across all three entities. I would like a google sheet that
Takes the subtotal of each of these entities and shows me the grand total for the month (I currently have to do this manually)
Can show me the same for each of the spending categories per month
Can show me the averages across each spending category per month (e.g. In september, my two businesses and personal spent $500 on dining out, etc)
BONUS: This can be a separate sheet entirely, but I just want a document that will show me a line graph of all of my spending categories so i can track category spending using the grand total. I want to see which categories are going up and which categories are going down month to month.
Thank you so much for your help and guidance. I’d even be willing to hire someone to do this if there’s anyone out there looking for some extra work!
You should be able to achieve this using the IMPORTRANGE function in Google Sheets. You can find some documentation at the following link: IMPORTRANGE - Google Docs Editors Help
As they mention, it probably works best if you structure the data before pulling it into the aggregation sheet, and then you could do the final totaling and averaging functions there. For the BONUS item 4, perhaps SPARKLINE functions would get you the desired visual? Not sure if you are intending a separate chart for each category or one chart with 35 lines. Attached is a possible layout for the items that you’ve mentioned. Is that how you’re envisioning it?
There is a way to do it but it takes a little setup.
I would recommend just using one Google Sheet and linking all the accounts for the 3 entities to the single sheet.
THEN just use a category/group structure to keep things organized.
Group for Personal, Business 1, Business 2
Since the categories need to be unique you’d then just prepend them with something unique like “Mikey - Dining Out” and “Biz 1 - Dining Out”
Then you’ll be able to see everything in one place.
I recommend the Monthly Analysis sheet for the spending trend lines you’re wanting to see and checking out some of the other community solutions for the other reports you’re wanting.
Hopefully that helps point you in the right direction.
I recognise the problem and once had it myself. The solution was to tag each transaction with both a Category (Type of expense) and a Fund (can be a person, company or project) and then use standard SUMIFS to do the reporting across months.
I could give you access to my solution if the community allows it.
I have also posted a fuller explanation but Tiller did not allow me to quote a website for further details.
Thank you, Heather! So I found that it was too hard for my eyes/brain to track which transaction belonged to each business simply by trying to remember which cards belonged to each business… that was the hard part of keeping them all in one sheet.
If the transactions that occur on a particular card are always one business and not mixed then you could apply conditional formatting so that if x credit card is used then a company and it will change color depending on preference then if y credit card is used then it will be another color, this could help you i would think.
You could also use AutoCat rules to apply a category or tag with a filter criteria that’s looking at the card #s for you and automatically categorizing or organizing the transactions by card number.
It sounds like it would definitely make for easier analysis if you have it all in one sheet if they are separate but related. This will also make for easier maintenance, e.g. if you modify or add categories, then you only have to do it one time rather than three. As others have mentioned, I’d utilize AutoCat or conditional formatting, then maybe a custom column populated based on the account name which could identify the business/personal designation and then allow you to do all sorts of various analysis across the two dimensions of category and business.
Can you share it? I think it would be helpful. The usual solution people say is to have a category for each of the various things… but that seems to get super complicated
I agree with @Caroleen and @Jake, that creating redundant account-specific categories is suboptimal. I like the suggestion of tagging— using AutoCat or not— and embracing slice and dice…
