Data Checker Tool

[Mark.S]

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:

Invalid Categories:

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.

1. 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")
2. 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:

1. 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).
2. Copy/Paste-special-Values-only the following formula into cell A1 (Values only):

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.0", header, tool,
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, IFERROR(SORT(UNIQUE(FILTER(IF(cCats<>"",cCats,IF((cGroups<>"")+(cTypes<>""),"<blank>","")),(COUNTIF(cCats,cCats)>1)+(COUNTIF(cTypes,cCats)>1)*(cTypes<>"Transfer")+((cCats="")*((cGroups<>"")+(cTypes<>"")))))),""),
badCatCatsCnt, SUM(--(badCatCats<>"")),
badGroups, IFERROR(SORT(UNIQUE(FILTER(IF((cGroups = "") * (cCats <> ""), "<blank>", IF(cGroups=cCats, cGroups, "")), (cGroups = cCats) + (cGroups = "")))),""),
badGroupsCnt, SUM(--(badGroups<>"")),
badTypes, 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, COUNTBLANK(tCats),
badCatsCnt, COUNTA(IFNA(FILTER(tCats,ARRAYFORMULA(NOT(REGEXMATCH(tCats,"^"&TEXTJOIN("$|^",TRUE,cCats)&"$"))),tCats<>""))),
badDatesCnt, ARRAYFORMULA(SUM(IF(ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))), 1, IF(tDates = "", 1, 0)))),
badAcctGrps, IFERROR(UNIQUE(FILTER(acctGroups, (acctGroups<>"") * (COUNTIFS(acctClass, "<>"&acctClass, acctGroups, acctGroups)+(acctGroups="") * (COUNTIF(acctClass, "<>"&acctClass)+COUNTIF(acctGroups, "")>1)) )),""),
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))
)))))))
)

3. Select the A column, right-click > Resize column > 85 > OK
4. 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

Microsoft Excel

Modify the Transactions sheet using these steps:

1. 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.
2. Copy/Paste-Values the following formula into cell A1 (Values only):

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.1", header, tool,
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, IFERROR(SORT(UNIQUE(FILTER(IF(cCats<>"",cCats,IF((cGroups<>"")+(cTypes<>""),"<blank>","")),(COUNTIF(cCats,cCats)>1)+((COUNTIF(cTypes,cCats)>1)*(cTypes<>"Transfer"))+((cCats="")*((cGroups<>"")+(cTypes<>"")))))),""),
badCatCatsCnt, SUM(--(badCatCats<>"")),
badGroups, IFERROR(SORT(UNIQUE(FILTER(IF((cGroups = "") * (cCats <> ""), "<blank>", IF(cGroups=cCats, cGroups, "")), (cGroups = cCats) + (cGroups = "")))),""),
badGroupsCnt, SUM(--(badGroups<>"")),
badTypes, 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, COUNTBLANK(tCats),
badCatsFlags, --(MMULT(--EXACT(tCats, TRANSPOSE(cCats)), ROW(cCats)^0)=0) * (tCats <> ""),
badCatsCnt, SUMPRODUCT(badCatsFlags),
badDatesCnt, SUM(IF(ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))), 1, IF(tDates = "", 1, 0))),
badAcctGrps, IFERROR(UNIQUE(FILTER(acctGroups, (acctGroups<>"") * (COUNTIFS(acctClass, "<>"&acctClass, acctGroups, acctGroups)+(acctGroups="") * (COUNTIF(acctClass, "<>"&acctClass)+COUNTIF(acctGroups, "")>1)) )),""),
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)
)))))))
)

3. Select the A column, right-click > Column Width > 85 Pixels > OK
4. 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

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 \^$.|?*+()[]{}

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. You probably will not notice a performance difference either way, but the idea is that it all adds up.
Default: 100

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

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]

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

[Mark.S]

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]

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]

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

[1vane1993sanchez]

Also 1email2rulethemall is also mine

[KyleT]

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]

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

[jshelham]

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]

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]

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

[Mark.S]

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]

@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]

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]

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

[Mark.S]

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?