🏆 Data Checker Tool

Mark.S · April 16, 2024, 10:49pm

Overview

This is a lightweight data integrity checker intended to quickly identify common problems with Transactions/Categories/Accounts data that causes issues with reports frequently asked about within the Tiller Community. e.g. “Why doesn’t the Monthly Budget match my Transactions?”

This is an expansion of my Finding Invalid Categories on Transactions Sheet solution, now with additional checks, including inspiration from a couple of my favorite solutions:
Transactions sheet: Tip to Show count of Uncategorized transactions in A1 or show Tiller logo if there are none, plus highlight Uncategorized rows by @mu3484343
Institution Alerts Notification by @jpfieber

Data Checker combines all these (plus more) together in one multi-purpose, priority-order formula. You just need one sheet column anywhere to put the formula. The Transactions sheet A1 cell is the ideal location.

Examples using Data Checker in Transactions sheet A1 cell:
(this is a sample, there are more checks than this)

Uncategorized count:
Transactions - uncategorized

Invalid Categories:
Transactions - Bad Cats

Bad Dates:
Transactions - Bad Dates

All Good

Installation

There is no template to install. For Google Sheets, proceed to Setup.

For Microsoft Excel, you will need to import the Institution Alerts Google Sheet via Power Query, if that Data Checker feature is desired.

Microsoft Excel - Institution Alerts - Power Query

This is optional, Data Checker works without it, but it is needed for the Alerts check.

Since the Institution Alerts spreadsheet has exporting disabled, this flow requires creating a separate Google spreadsheet, using importrange, and the user will need to keep it open in a browser tab to keep the alerts data refreshed. This is a work-around, really, to be able to reference the latest alerts in Excel.

Note that Excel for the web does not currently support creating Power Queries, nor external data connections.

Create a Google spreadsheet
a. Always keep this spreadsheet active in a browser tab to keep alert data refreshed.
b. File > Share > Share with others > General Access > Anyone with the link > Role > Viewer
c. rename the file to something you like, maybe Institution Alerts Import
d. rename the sheet to Dashboard
e. copy/paste the following formula into the A1 cell:
=importrange("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Dashboard!A:H")
Open your Tiller Foundation template using Microsoft 365 Excel.
a. Menu > Data > From Web >
https://docs.google.com/spreadsheets/d/[ID]/export?format=xlsx&id=[ID]
where [ID] is after /d/ and before /edit from Step #1 new spreadsheet Share URL.
b. Navigator > select Dashboard (view to confirm alerts dashboard)
c. Navigator > Load To > Import Data > Table > New Worksheet
d. rename new worksheet to Institution Alerts
e. Data > Queries & Connections > [right-click]>rename>Institution Alerts
[right-click]>Properties> check Refresh data when opening the file > OK

Setup

Add the new formula to the Transactions sheet A1 cell. This is the ideal location for the Data Checker, since it is where the data resides and the checks will be readily seen on a daily basis.

Google Sheets

Modify the Transactions sheet using these steps:

By default, the A1 cell will already contain an image link or embedded image. Simply delete it (it can easily be restored back, if needed).
Copy/paste formula into A1 cell:
a. Copy formula using copy icon in upper-right corner of code box
b. Double-click A1 cell to get cursor in the cell, and Ctrl+Shift+V to Paste as plain text
c. If you get a #REF! error, be sure the column of cells below the formula is empty for the tool results

=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))
)))))))
)

Select the A column, right-click > Resize column > 85 > OK
Select the A1 cell
a. Menu > Format > Wrapping > Wrap
b. Menu > Format > Conditional formatting > Apply to Range: A1 >
Format rules: Text does not contain Data Checker >
Formatting style > Fill color > + Add a custom color > Hex: #ee5253 > OK
Text color > white (upper-right corner) > Done
*Note: the red fill color with white font is from the Tiller Color Palette

Google Sheets - Additional Conditional Formatting (Optional)

Here are additional conditional formatting rules that are tied to when it’s corresponding check fails. Feel free to pick-and-choose those that are most hopeful to you.

Keep in mind that conditional formatting can break for similar reasons that cause Data Checker failures (like inadvertent copy/paste) and so it’s considered as a helpful visual guide and Data Checker results are considered golden.

Also, the column letters in these formulas could be different from your spreadsheet, so please adjust accordingly.

Transactions Sheet

I recommend restricting the range to the top 100 rows for best performance on a large dataset like the Transactions sheet.

<number> uncategorized
(Category column D)
Select the D2 cell
Menu > Format > Conditional formatting > Apply to Range: D2:D100 >
Format rules: Custom formula is >

=IF(ISNUMBER($A$1), ISBLANK($D2))

Formatting style > Fill color > CUSTOM > + Add a custom color > Hex: #ee5253 > OK
Text color > white (upper-right corner) > Done
Hover over the 4 vertical dots to left of color box >
click-hold-drag-up to top of any pre-existing list to make this have top priority

Repeat for the remaining checks using the following ranges and formulas.

Bad Cats
(Category column D)
Apply to Range: D2:D100
Custom formula:

=IF($A$1="Bad Cats", NOT(REGEXMATCH($D2, "^"&TEXTJOIN("$|^", TRUE, INDIRECT("'Categories'!A2:A"))&"$")))

Bad Dates
(Date column B)
Apply to Range: B2:B100
Custom formula:

=IF($A$1="Bad Dates", IF(ISERROR(DATEVALUE(TEXT($B2, "mm/dd/yyyy"))) + ISBLANK($B2) = 0, FALSE, TRUE))

Categories Sheet

Bad Cat Cats
(Category column A, Group column B, Type column C)
Apply to Range: A2:A200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Cat Cats",(COUNTIF($A:$A,$A2)>1)+((COUNTIF($C:$C,$A2)>0)*($C2<>"Transfer"))+(($A2="")*(($B2<>"")+($C2<>""))))

Bad Groups
(Category column A, Group column B)
Apply to Range: B2:B200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Groups", (COUNTIF($A:$A,$B2)>0)*($B2<>"") + ($B2="")*($A2<>""))

Bad Types
(Category column A, Type column C)
Apply to Range: C2:C200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Types", ($A2<>"")*(NOT(EXACT($C2,"Income")))*(NOT(EXACT($C2,"Expense")))*(NOT(EXACT($C2,"Transfer"))))

Accounts Sheet

Note that some columns are in the hidden helper columns in the right-hand portion of the sheet.

Bad Act Groups
(Group column P, Class column O)
Apply to Range: O2:O100,P2:P100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Act Groups", ($P2<>"") * COUNTIFS($O:$O, "<>"&$O2, $P:$P, $P2))

(Group column C)
Apply to Range: C2:C100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Act Groups", COUNTIF(INDIRECT("Transactions!A2:A100"), $C2) > 0)

Alerts
(Account A, Unique Account Identifier F, Account J, Institution L)
Apply to Range: F2:F100,J2:J100,L2:L100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Alerts", ($L2<>"") * ISNUMBER(SEARCH($L2, TEXTJOIN(";",TRUE,INDIRECT("Transactions!A2:A100")))))

Apply to Range: A2:A100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Alerts", NOT(ISERROR(XMATCH($A2, FILTER($F$2:$F$100, ($L$2:$L$100<>"") * ISNUMBER(SEARCH($L$2:$L$100, TEXTJOIN(";", TRUE, INDIRECT("Transactions!A2:A100")))))))))

Microsoft Excel

Modify the Transactions sheet using these steps:

By default, the A-column of the Excel Transactions spreadsheet is Date and is apart of the Transactions Table. Select column-A, [right-click]>Insert, to insert a new column to the left of the table. This column is along-side the Transactions Table and not apart of it.
Copy/paste formula into A1 cell:
a. Copy formula using copy icon in upper-right corner of code box
b. Double-click A1 cell to get cursor in the cell, and Ctrl+V to Paste
c. If you get a #SPILL! error, be sure the column of cells below the formula is empty for the tool results

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.1.0", header, tool,
badCatCatsOn, TRUE, badGroupsOn, TRUE, badTypesOn, TRUE, uncatsOn, TRUE, badCatsOn, TRUE, badDatesOn, TRUE, badAcctGrpsOn, TRUE,
lastRow_, IF(lastRow<>"",lastRow,ROWS(Transactions[Date])+1),
cCats, Categories[Category], cGroups, Categories[Group], cTypes, Categories[Type],
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&"300")),
acctIds, INDIRECT(LET(ltr,CHAR(64+XMATCH("Account Id",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctHides, INDIRECT(LET(ltr,CHAR(64+XMATCH("Hide",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
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),
badCatsFlags, IF(badCatsOn,--(MMULT(--EXACT(tCats, TRANSPOSE(cCats)), ROW(cCats)^0)=0) * (tCats <> ""),0),
badCatsCnt, SUMPRODUCT(badCatsFlags),
badDatesCnt, IF(badDatesOn,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,INDIRECT("'Institution Alerts'!A5:A1000"),
       alertDescs,INDIRECT(LET(ltr,CHAR(64+XMATCH("Description",INDIRECT("'Institution Alerts'!A4:Z4"))),"'Institution Alerts'!"&ltr&"5:"&ltr&"1000")),
       institutions2,IF(alertsStrict, institutions1, IFERROR(FILTER(institutions1,alertDescs<>"Site Temporarily Unavailable"),"")),
       institutions,IFERROR(TRIM(LEFT(institutions2, SEARCH(CHAR(9), SUBSTITUTE(institutions2, "(", CHAR(9), LEN(institutions2) - LEN(SUBSTITUTE(institutions2, "(", ""))))-1)),institutions2),
       BYROW(SORT(UNIQUE(FILTER(acctInsts,NOT(ISNUMBER(FIND("manual:",acctIds)))*(acctHides<>"Hide")*(acctInsts<>""),""))),
       LAMBDA(institution,
             IFERROR(TEXTJOIN(";", TRUE, FILTER(institutions,ISNUMBER(SEARCH(institution, institutions)),"")),"")
             ))),),
alertsFlat, IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(";", TRUE, alertsResults), ";")),""),
alerts, SORT(UNIQUE(FILTER(alertsFlat,(LEN(alertsFlat)>0)*(NOT(ISNUMBER(SEARCH(alertsFlat,alertsHide)))),""))),
alertsCnt, SUM(--(alerts<>"")),
IF(badCatCatsCnt > 0, VSTACK("Bad Cat Cats",badCatCats),
IF(badGroupsCnt > 0, VSTACK("Bad Groups",badGroups),
IF(badTypesCnt > 0, VSTACK("Bad Types",badTypes),
IF(uncatsCnt > 0, uncatsCnt,
IF(badCatsCnt > 0, VSTACK("Bad Cats",SORT(REDUCE(,FILTER(tCats,badCatsFlags,""), LAMBDA(a,v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))))),
IF(badDatesCnt > 0, VSTACK("Bad Dates",SORT(UNIQUE(FILTER(IF(tDates="", "<blank>", tDates), ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))) + (tDates = ""),"")))),
IF(badAcctGrpsCnt > 0, VSTACK("Bad Act Groups",badAcctGrps),
IF(alertsCnt > 0, VSTACK("Alerts",alerts),
header)
)))))))
)

Select the A column, right-click > Column Width > 85 Pixels > OK
Select the A1 cell
a. Menu > Home > Wrap Text
b. Menu > Conditional Formatting > Highlight Cells Rules > More Rules >
Format only cells with: Specific Text > not containing > Data Checker >
Format… > Fill > More Colors > Custom > Hex: #ee5253 > OK
Font > Color > [select white] > OK > OK
*Note: the red fill color with white font is from the Tiller Color Palette

Microsoft Excel - Additional Conditional Formatting (Optional)

Here are additional conditional formatting rules that are tied to when it’s corresponding check fails. Feel free to pick-and-choose those that are most hopeful to you.

Keep in mind that conditional formatting can break for similar reasons that cause Data Checker failures (like inadvertent copy/paste) and so it’s considered as a helpful visual guide and Data Checker results are considered golden.

Also, the column letters in these formulas could be different from your spreadsheet, so please adjust accordingly.

Transactions Sheet

I recommend restricting the range to the top 100 rows for best performance on a large dataset like the Transactions sheet.

<number> uncategorized
(Category column D)
Select the D2 cell
Menu > Home > Conditional Formatting > Manage Rules > Manage Rules in selection > +
Apply to Range: D2:D100 >
Check the box: If true then stop
Rule Type > Formula >

=IF(ISNUMBER($A$1), ISBLANK($D2))

Format with > Custom Format >
Fill color > More Colors… > Hex: ee5253 > OK
Font color > white (upper-left corner) > Done
Hover over Conditional Formatting box until cursor looks like a hand >
click-hold-drag-up to top of any pre-existing list to make this have top priority

Excel - uncategorized

Repeat for the remaining checks using the following ranges and formulas.

Bad Cats
(Category column D)
Apply to Range: D2:D100
Custom formula:

=IF($A$1="Bad Cats", (MMULT(--EXACT($D2, TRANSPOSE(INDIRECT("Categories!A2:A200"))), ROW(INDIRECT("Categories!A2:A200"))^0)=0) * ($D2 <> ""))

Bad Dates
(Date column B)
Apply to Range: B2:B100
Custom formula:

=IF($A$1="Bad Dates", IF(ISERROR(DATEVALUE(TEXT($B2, "mm/dd/yyyy"))) + ISBLANK($B2) = 0, FALSE, TRUE))

Categories Sheet

Bad Cat Cats
(Category column A, Group column B, Type column C)
Apply to Range: A2:A200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Cat Cats",(COUNTIF($A:$A,$A2)>1)+((COUNTIF($C:$C,$A2)>0)*($C2<>"Transfer"))+(($A2="")*(($B2<>"")+($C2<>""))))

Bad Groups
(Category column A, Group column B)
Apply to Range: B2:B200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Groups", (COUNTIF($A:$A,$B2)>0)*($B2<>"") + ($B2="")*($A2<>""))

Bad Types
(Category column A, Type column C)
Apply to Range: C2:C200
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Types", ($A2<>"")*(NOT(EXACT($C2,"Income")))*(NOT(EXACT($C2,"Expense")))*(NOT(EXACT($C2,"Transfer"))))

Accounts Sheet

Note that some columns are in the hidden helper columns in the right-hand portion of the sheet.

Bad Act Groups
(Group column O, Class column N)
Apply to Range: N2:N100,O2:O100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Act Groups", ($O2<>"") * COUNTIFS($N:$N, "<>"&$N2, $O:$O, $O2))

(Group column C)
Apply to Range: C2:C100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Bad Act Groups", COUNTIF(INDIRECT("Transactions!A2:A100"), $C2) > 0)

Alerts
(Account A, Unique Account Identifier F, Account H, Institution L)
Apply to Range: F2:F100,H2:H100,L2:L100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Alerts", ($L2<>"") * ISNUMBER(SEARCH($L2, TEXTJOIN(";",TRUE,INDIRECT("Transactions!A2:A100")))))

Apply to Range: A2:A100
Custom formula:

=IF(INDIRECT("Transactions!A1")="Alerts", NOT(ISERROR(XMATCH($A2, FILTER($F$2:$F$100, ($L$2:$L$100<>"") * ISNUMBER(SEARCH($L$2:$L$100, TEXTJOIN(";", TRUE, INDIRECT("Transactions!A2:A100")))))))))

Usage

The checks are performed in a priority order and each check needs to pass before proceeding to the next check. First, Categories sheet checks are done, then Transactions sheet checks, etc., and lastly Institution Alerts. The idea is that categories need to be clean before using them in Transactions.

Note that Data Checker’s default configuration assumes the most recent Transactions are at the top of the sheet, which is most common.

For Google Sheets, the result header is also a hyperlink to this list of checks for an easy access reference.

List of Checks

Currently, these are the checks being performed, listed in the checking order. The check name becomes the name of the header when that check fails. The list below the header are the failures.

Bad Cat Cats
The Category on the Categories sheet is a duplicate, matches a Type, or it is blank when the Group or Type is not blank. Rename the Category to be unique - avoid using special characters \^$.|?*+()[]{}

Bad Groups
The Group on the Categories sheet either matches a Category name or it is blank when Category is not blank. Change the Group name to not match the Category name.

Bad Types
The Type on the Categories sheet either does not match one of Income, Expense, or Transfer; or it is blank when Category is not blank. Change Type to use only one of Income, Expense, or Transfer.

<number>
The number of transactions with a blank Category (uncategorized transactions). There is no fail list. Change Transactions sheet Category using dropdown selection.

Bad Cats
The Category on the Transactions sheet does not match a Category on the Categories sheet (case-sensitive). Change Transactions sheet Category using dropdown selection, or Rename the Category - avoid using special characters \^$.|?*+()[]{}
See Troubleshooting the Category dropdown in the Transactions sheet, if your Category dropdown is broken, missing, or not showing all categories.

Bad Dates
The Date on the Transactions sheet is not a real date or is blank. Change the date to a valid date - one option is to enter @date in the Date cell to get a calendar date picker.

Bad Act Groups
The Group on the Accounts sheet is the same for Asset and Liability Class (i.e. Asset Group name = Liability Group name). One issue this causes is with the Net Worth template incorrectly reporting the same value for this group’s Asset and Liability totals.
Change Group names so no Asset Group names are the same as Liability Group names. As an example, the default group names (when Group cell is empty), are UNGROUPED ASSET and UNGROUPED LIABILITY.

Alerts
The alert in the Institution Alerts list matches an Institution on the Accounts sheet. They may not affect your data, sometimes these are simply temporary hiccups, so just be aware of them. Consider using the alertsOn, alertsStrict, alertsHide parameters listed in the first row of the formula bar to help control the Alerts check.

Data Checker
When this tool name is displayed, all checks passed

Parameters

The visible top row of the formula bar has user changeable parameters. The parameters come in name,value pairs. The first is the parameter name, do not change it. The second is it’s value and can be changed to configure to user needs.

lastRow
This is for performance control and specifies the last row of the range in the Transactions sheet that gets processed (2:lastRow). Issues occur mostly in the recent transactions where changes are occurring, so no need to keep checking data that is not changing.
When first using Data Checker, use "" to cover all transaction rows, then feel free to change it to a smaller integer value to cover your daily changes - I like to use 100. Depending on the number of transactions in your sheet, you may not notice a performance difference either way, but the idea is that it all adds up.
Default: ""

alertsOn
Turns Institution Alerts check on/off (TRUE/FALSE). In my experience, there always seems to be some active alerts with my institutions. When working with and/or analyzing data, or other times when the alerts are not desired, this provides on/off control. This also turns on/off importing the alerts data.
Default: TRUE

alertsStrict
Turns Institution Alerts check strict mode on/off (TRUE/FALSE).
Strict TRUE is the unfiltered check - if an alert is found for your institution, it is reported.
Strict FALSE filters out alert Description Site Temporarily Unavailable. This provides a way to ignore more common temporary issues that may not really affect you. It is a way to keep the alert check on and just see more serious outages.
Default: TRUE

alertsHide
List of alerts to hide, typically for false positive cases or when there is an alert the user never wants to see reported. Use the full name of the alert that the check reports. Separate multiple names using semi-colon ;. The default "name1;name2" is a placeholder as reminder of format and will not hide any alerts.
Default: "name1;name2"

header
The header name when all checks pass. Using Data Checker as the header is a reminder of what tool is running in the cell.
With column width 85 pixels, text wrap set, and Roboto font size 11, Data Checker will display on two lines in the cell, as well as many of the check names, so that width does work nicely.
Consider using the DC abbreviation for something shorter, but it likely means the check names will not be fully visible without resizing the column.
If you want the Tiller image to display, replace header at the bottom of the formula with the following image command, instead of using this long name in the first row of the Data Checker formula.
image("https://storage.googleapis.com/assets.templates.tillermoney.com/Tiller_Icon_Reverse%402x.png")
Default: tool ("Data Checker")

<check>On
The second row of the formula bar has parameters to turn individual checks on/off (TRUE/FALSE). While the tool checks are based on commonly reported issues in the Tiller Community, this provides an easy on/off control for either debug or personal preference. This also turns on/off the calculation of the respective check for best performance.
Default: TRUE

Permissions

Feel free to copy, use, and modify to meet your needs.

Notes

The Institution Alerts check has a new feature to automatically hide accounts that are marked manual or Hide on the Accounts sheet, in addition to the alertsOn, alertsStrict, and alertsHide parameter controls. I also added support for multiple alert results per institution (I believe the original would return no results for this case).

Google Sheets is my primary Tiller use case and I used Excel for the web to test the Excel formulas with sample data.

Please let me know about any checks you would like to see get added to Data Checker

FAQ

Why isn’t Data Checker flagging something that looks bad?

Most likely it is beyond the lastRow specified in the formula first row parameter. Increase the lastRow value.

How can I get a clean result when there are always Alerts?

First, keep in mind that the Alerts check is the last check, so all other checks have passed if Alerts are displayed. Consider using the alertsOn, alertsStrict, alertsHide parameters listed in the first row of the formula bar to help control the Alerts check.

Data Checker is reporting a failure, how do I fix it?

For Sheets, click the header hyperlink for easy access to the List of Checks descriptions of why the check is failing.
For Excel, the link is available in the formula to copy/paste, but so far in my Excel testing, hyperlink hasn’t been working.

jpfieber · April 16, 2024, 11:05pm

Sounds very interesting! BUT, both of the code boxes are empty so I can’t yet test!

Mark.S · April 16, 2024, 11:22pm

I added the formulas now. I intentionally left them out initially, because I wanted to include a URL from this post first.

Let me know what you think

jpfieber · April 17, 2024, 12:05am

Nice! It pointed out that I had an old ‘Transfer’ category in my ‘Transfer’ group that I no longer needed (I use ‘Transfer In’ and ‘Transfer Out’). After deleting that, it then pointed out I had an uncategorized transaction, which I took care of, and now it’s letting me know that two of my accounts have alerts (the same functionality I was doing previously). I like that it’s doing more without taking any more space than was already allocated for a utility. You’ve got my vote!

Mark.S · April 17, 2024, 12:39am

Thanks! Glad you’re finding it useful and thanks for the inspiration!

1vane1993sanchez · April 17, 2024, 12:52am

Also 1email2rulethemall is also mine

KyleT · April 17, 2024, 7:58pm

Cool idea @Mark.S - I have to check it out further, but I’m a big fan of the LET function first line as user-defined inputs

Mark.S · April 17, 2024, 8:25pm

Thanks Kyle, yeah, the visible top row parameters is chef’s kiss

jshelham · April 18, 2024, 3:36pm

Thanks - this is pretty cool. I have added it to my Transactions tab and the first alert is “Bad Cats”. The six items listed are all on my Category sheet and they all have the same thing in common. These specific category names include numbers in this format “Category Name ($100)”. The amounts in parentheses are a hold over from when I moved from YNAB to Tiller a while back. They were intended to note the maximum amount for the roll over balance. Could the parentheses and/or numbers in the category name be what is causing this for me?

Mark.S · April 18, 2024, 3:50pm

Yeah, the parenthesis and dollar sign are special characters that mess with matching. Removing those will fix it. Having a number is fine.

Consider using the Rename or merge a category solution to make the changes easier.

Thanks for the feedback and glad you like it

bwentwor · April 18, 2024, 11:54pm

I like it! Is there a way to shut off one of the checks if one desires?

Mark.S · April 19, 2024, 1:00am

Thanks @bwentwor !

The Alerts check can be turned off by changing the top row parameter …
alertsOn, TRUE
to:
alertsOn, FALSE

If you wanted to disable a different check, the block of IF-statements at the bottom of the code is a good place to do it.

Say you want to disable the Bad Dates check. Change …
IF(badDatesCnt > 0,
to:
IF((badDatesCnt > 0) * FALSE,

Where the original condition gets wrapped with new parenthesis and * FALSE added to disable it.

May I ask what check you’d like to disable and the reason why?

bwentwor · April 19, 2024, 1:16am

@Mark.S Thanks! You did a great job and got a vote from me! However, I think that comparing the Assets and Liability Categories to ensure that they are not the same is a bit too much. I have a car loan and a savings account to fund the loan at the same bank. One is a liability and one is an asset. Using the same group name is fine and does not need to be flagged. It’s a personal preference.

Mark.S · April 19, 2024, 1:36am

Gotcha, thanks for the vote and the feedback! Just know that causes the group totals to incorrectly match for the Net Worth template, not sure what other affects it may have.

Also note that the default group naming before user changes has them unique, so perhaps that was done for the same/similar reasons.
UNGROUPED ASSET
UNGROUPED LIABILITY

Thanks again

chas.bicking · April 19, 2024, 11:58pm

Unfortunately I get my least favorite error: Formula Parse Error :frowning

Mark.S · April 20, 2024, 12:53am

Is that with Google Sheets?

Did you delete whatever was in the destination cell first and then use the handy copy icon in the upper-right corner for the formula in this post?

mpresley410 · May 7, 2024, 5:18pm

Ok. Maybe I’m being dumb? I had a conditional format formula in there before. Deleted all conditional formatting on the A1 cell and deleted what was in that field. So, all blank now. I copied the formula, went to A1, did paste special, values and nothing is in the cell at all. Thought, well, maybe that’s how it is supposed to look? Did the conditonal formatting as you list and now the field is just red. How do I get it to copy the formula so it can do all the checks? A1 still looks blank. When I click on it there is no formula or anything in the formula bar.

Mark.S · May 7, 2024, 5:27pm

Try either selecting the formula bar or double-clicking the A1 cell before pasting.

mpresley410 · May 7, 2024, 6:18pm

Can’t do a paste values when I click in it. Pasted the whole formula and get a #REF! error.

Mark.S · May 7, 2024, 6:26pm

Does hovering over #REF! error provide more info? The column of cells below the formula needs to be empty for the results.

Topic		Replies	Views
:trophy: Transactions sheet: Tip to Show count of Uncategorized transactions in A1 or show Tiller logo if there are none, plus highlight Uncategorized rows Show & Tell google-sheets , how-to	22	1892	August 16, 2024
:trophy: Finding Invalid Categories on Transactions Sheet Show & Tell excel , google-sheets , how-to , troubleshooting	13	922	October 10, 2024
:trophy: Transaction Comparison - Google Sheets Show & Tell tcs-gallery , tcs-trxn-compare	38	2614	September 16, 2024
Problem with error: The data you entered violates the data validation rules set on this cell Google Sheets google-sheets , troubleshooting	13	51450	November 10, 2023
Spreadsheet corruption - import transactions from old spreadsheet Microsoft Excel excel	2	295	February 23, 2023

🏆 Data Checker Tool

Overview

Installation

Setup

Transactions Sheet

Categories Sheet

Accounts Sheet

Transactions Sheet

Categories Sheet

Accounts Sheet

Usage

List of Checks

Parameters

Permissions

Notes

FAQ

Why isn’t Data Checker flagging something that looks bad?

How can I get a clean result when there are always Alerts?

Data Checker is reporting a failure, how do I fix it?

Related topics