Category Not Matching Reports

[tdc720]

My grocery category is not calculating correctly in the monthly and yearly budget sheets. In April 2024, the grocery amount is short by $1500. Several of the transactions are split. Everything is categorized correctly. Some are from a manual cash account, and some are credit card. I can’t seem to pinpoint which transactions are not being pulled in.

[Mark.S]

Hey Todd,

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 that cause reporting 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.tiller.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))
)))))))
)

[tdc720]

Thanks Mark. I tried this following all the steps. I can’t seem to make it work.

[Mark.S]

You can’t get the formula to work?
Please provide more info on what is happening. Screenshots are helpful

[tdc720]

If I had any idea what I am doing, it probably would work. I tried both ways. Adding another sheet. And adding the formula in A1 using the tutorial from the link you supplied. Pretty frustrating.

(attachments)

[Mark.S]

@tdc720

Screenshot 2024-10-03 162520.png1600×136 20.1 KB

This is correct. The number is how many Category’s are uncategorized (have a blank Category).
Fix those and then Data Checker will report any other issues.

[tdc720]

Those must be from 2023. I just deleted those rows of transactions. Now I get a sheet with a list of institution alerts that I believe are unrelated to my reporting issue.

(attachments)

[Mark.S]

Okay, yeah, when you see the institution alerts, then all other checks pass.

Then I would try restoring the problematic templates from:
Extensions > Tiller Money Feeds > Templates

[tdc720]

I did that earlier. Could renaming a manual account cause an issue like this? I renamed my Cash account not too long ago. But I also fixed all the transactions as well as in Balance History. Everything has been renamed to the updated account name.

[Mark.S]

I don’t think the budget reports depend on Accounts. I would filter the Transactions sheet for April 2024 and the grocery category and attempt to reconcile. I’ve seen incorrect polarity on some transactions cause what you describe (like positive amounts cancelling negative amounts).

Monthly Budget source data references (No Accounts, no Balance History):

[tdc720]

Thank you Mark. I truly appreciate your time.

[morgan]

Did you get this figured out based on any suggestions?