Overview Tab in Foundation Template Showing Duplicate Income and Expenses

[daniukid]

Hi, I’m a new user and really trying to dig in and set everything up correctly. However, one problem that I’m encountering is:

-Foundation Template
-Overview Tab
-For INCOME and EXPENSES, they are showing exact same duplicates in INCOME and almost 90% duplicates in EXPENSE.
-For example, B24 for me under EXPENSE would be “Amazon Unknown” in some bolded font. Then B25 would duplicate “Amazon Unknown” in unbolded or stylized font.
-But then I have Auto duplicated in B26 and B27 but with different amounts in C26 and C27
-Finally, I also have some EXPENSE that are not duplicated (but definitely the minority)

Unfortunately when I click on the cell to see the formula that is driving it, it just shows the number. I also can’t trace dependency on G Sheet like I can on Excel.

So I’m hoping someone can help me by shedding some light on this.
Thanks in advanced,
David “Newbie”

[Clint.C]

Hi @daniukid,

Welcome to Tiller Money!

By Overview Tab, are you referring to the Yearly Budget sheet? If so, it sounds like you might have something setup incorrectly in the Categories sheet.

Clint

[daniukid]

Hi Clint,

Sorry I wasn’t more clear. The Overview tab is the default ‘Spending Trends’ 1st tab on the Tiller Foundation Template that I started with.

I also use the ‘Categories’ sheet in the default Tiller Fondation Template. You said something may be setup incorrectly there.

Any thoughts on what it may be and how to fix?

Thanks,
David

[dmetiller]

Welcome! Here’s where that data is coming from in the Categories sheet:

The upper case bold is the TYPE.

The lower case bold is the GROUP.

The lower case normal is the CATEGORY.

If you go to your Categories sheet, have you set different GROUPS for each CATEGORY? So, for example, you might assign the Category of Auto to a Group called Transportation. And is there more than one Category assigned to a Group, which is normal and would explain why the amount in the category Auto does not equal the amount in the group AUTO?

Hope that helps.

[Mark.S]

Create a new sheet in your Tiller spreadsheet and copy the below Data Checker Tool into cell A1.
It will flag common user data integrity issues.

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.1", header, tool,
badCatCatsOn, TRUE, badGroupsOn, TRUE, badTypesOn, TRUE, uncatsOn, TRUE, badCatsOn, TRUE, badDatesOn, TRUE, badAcctGrpsOn, TRUE,
cCats, INDIRECT(LET(ltr,CHAR(64+XMATCH("Category",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
cGroups, INDIRECT(LET(ltr,CHAR(64+XMATCH("Group",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
cTypes, INDIRECT(LET(ltr,CHAR(64+XMATCH("Type",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
tCats, INDIRECT(LET(ltr,CHAR(64+XMATCH("Category",INDIRECT("Transactions!B1:Z1"))+1),"Transactions!"&ltr&"2:"&ltr&lastRow)),
tDates, INDIRECT(LET(ltr,CHAR(64+XMATCH("Date",INDIRECT("Transactions!B1:Z1"))+1),"Transactions!"&ltr&"2:"&ltr&lastRow)),
acctInsts, INDIRECT(LET(ltr,CHAR(64+XMATCH("Institution",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr)),
acctIds, INDIRECT(LET(ltr,CHAR(64+XMATCH("Account Id",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr)),
acctHides, INDIRECT(LET(ltr,CHAR(64+XMATCH("Hide",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!$"&ltr&"2:"&ltr)),
acctClass, INDIRECT(LET(ltr,CHAR(64+XMATCH("Class",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctGroups, INDIRECT(LET(ltr,CHAR(64+XMATCH("Group",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
badCatCats, IF(badCatCatsOn,IFERROR(SORT(UNIQUE(FILTER(IF(cCats<>"",cCats,IF((cGroups<>"")+(cTypes<>""),"<blank>","")),(COUNTIF(cCats,cCats)>1)+((COUNTIF(cTypes,cCats)>0)*(cTypes<>"Transfer"))+((cCats="")*((cGroups<>"")+(cTypes<>"")))))),""),""),
badCatCatsCnt, SUM(--(badCatCats<>"")),
badGroups, IF(badGroupsOn,IFERROR(SORT(UNIQUE(FILTER(IF((cGroups = "") * (cCats <> ""), "<blank>", cGroups), (COUNTIF(cCats,cGroups)>0)*(cGroups<>"") + (cGroups="")*(cCats<>"") ))),""),""),
badGroupsCnt, SUM(--(badGroups<>"")),
badTypes, IF(badTypesOn,IFERROR(SORT(UNIQUE(FILTER(IF(cTypes<>"",cTypes,"<blank>"),(cCats<>"")*(NOT(EXACT(cTypes,"Income")))*(NOT(EXACT(cTypes,"Expense")))*(NOT(EXACT(cTypes,"Transfer")))))),""),""),
badTypesCnt, SUM(--(badTypes<>"")),
uncatsCnt, IF(uncatsOn,COUNTBLANK(tCats),0),
badCatsCnt, IF(badCatsOn,COUNTA(IFNA(FILTER(tCats,ARRAYFORMULA(NOT(REGEXMATCH(tCats,"^"&TEXTJOIN("$|^",TRUE,cCats)&"$"))),tCats<>""))),0),
badDatesCnt, IF(badDatesOn,ARRAYFORMULA(SUM(IF(ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))), 1, IF(tDates = "", 1, 0)))),0),
badAcctGrps, IF(badAcctGrpsOn,IFERROR(UNIQUE(FILTER(acctGroups, (acctGroups<>"") * COUNTIFS(acctClass, "<>"&acctClass, acctGroups, acctGroups) )),""),""),
badAcctGrpsCnt, SUM(--(badAcctGrps<>"")),
alertsUrl, "https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333",
toolUrl, "https://community.tillerhq.com/t/data-checker-tool/25582#list-of-checks-5",
alertsResults,IF(alertsOn, LET(institutions1,IMPORTRANGE(alertsUrl,"Dashboard!A5:A"),alertDescs,IMPORTRANGE(alertsUrl,"Dashboard!H5:H"),
       institutions,IF(alertsStrict, institutions1, IFERROR(FILTER(institutions1,alertDescs<>"Site Temporarily Unavailable"),"")),
       BYROW(SORT(UNIQUE( FILTER(acctInsts,NOT(ISNUMBER(FIND("manual:",acctIds))),acctHides<>"Hide",acctInsts<>"") )),
       LAMBDA(institution,
             REGEXREPLACE(TEXTJOIN(";", TRUE, IFNA(FILTER(institutions,SEARCH(institution,institutions)))),"\s*\([0-9]+\)","")
             ))),),
alertsFlat, IFERROR(TRANSPOSE(SPLIT(TEXTJOIN(";", TRUE, alertsResults), ";")),),
alerts, SORT(UNIQUE(IFNA(FILTER(alertsFlat,LEN(alertsFlat)>0,NOT(ISNUMBER(SEARCH(alertsFlat,alertsHide))))))),
alertsCnt, COUNTA(alerts),
IF(badCatCatsCnt > 0, {HYPERLINK(toolUrl,"Bad Cat Cats");badCatCats},
IF(badGroupsCnt > 0, {HYPERLINK(toolUrl,"Bad Groups");badGroups},
IF(badTypesCnt > 0, {HYPERLINK(toolUrl,"Bad Types");badTypes},
IF(uncatsCnt > 0, uncatsCnt, 
IF(badCatsCnt > 0, {HYPERLINK(toolUrl,"Bad Cats");SORT(UNIQUE(IFNA(FILTER(tCats,ARRAYFORMULA(NOT(REGEXMATCH(tCats,"^"&TEXTJOIN("$|^",TRUE,cCats)&"$"))),tCats<>""))))},
IF(badDatesCnt > 0, {HYPERLINK(toolUrl,"Bad Dates");SORT(UNIQUE(FILTER(IF(tDates="", "<blank>", tDates), ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))) + (tDates = ""))))},
IF(badAcctGrpsCnt > 0, {HYPERLINK(toolUrl,"Bad Act Groups");badAcctGrps},
IF(alertsCnt > 0, {HYPERLINK(alertsUrl,"Alerts");alerts},
HYPERLINK(toolUrl,header))
)))))))
)

[daniukid]

Wow Mark!

Thanks so much and will try this out as well this weekend when I dive back into this. Cheers,
David

[twalane]

Let us know how it goes @daniukid !

[daniukid]

All, quick update and question.

1. @dmetiller thank you so much for clarifying the structure. That’s when I realized that on my ‘Categories’ tab in the Foundation Template, I made the mistake of having a Category also be a Group, which I think is causing this issue. For example, I would create a Misc Category and then a Misc Group.

2. @Mark.S that macro / code is amazing. It highlighted all the “Bad Groups” that I have, which is causing issues.

However, my lingering question is that one “Bad Group” was because I had “Charitable Donation” as a category and group. I then updated to Charitable Donation as a category and Donation as the Group. After doing that, it was no longer a Bad Group based on Mark’s macro / code. So I thought I would be good with the categories rolling up into the groups.

But it didn’t seem to update on the ‘Spending Trends’ tab for the Foundation Template. See screenshots. It seemed to keep the original Charitable Donation in this example, while also creating a new Donation as well. So instead of the two Charitable Donation, I have one new Donation (Group) and one Charitable Donation (Category).

Is there a way that it updates or refreshes after I correct the issue of not using the same Category and Group? So it would just show in the ‘Spending Trends’ tab Donation since that is the group and all the Charitable Donations would roll into it since that is the category?

Cheers,
David

[Mark.S]

The Spending Trends report should update right away.

I’m not sure I’m understanding the issue you have. The group lines are the totals of all the categories within that group, so your image looks correct to me.

It appears from the screenshot that there are more Bad Groups to fix?
The Data Checker Tool reports issues in a priority order, so once you have all Bad Groups fixed, there could be additional issues to fix in another category. Keep going until you likely see Alerts, which are institution alerts for your information and not necessarily something that needs fixing - but seeing Alerts means your data passes all the other checks.

[daniukid]

Mark, thanks so much for your help. After reviewing again, I think I’m making progress and in a good spot.
Appreciate you and the community,
David

[morgan]

You are also welcome to reach out to our support team. We can help troubleshoot issues with the Foundation Template, bank connections, the Tiller Money Feeds sidebar, or give you tips if you get stuck anywhere!

(Chat with support via the chat window in the lower right corner of the Console at https://my.tillerhq.com/)