🏆 Institution Alerts Notification - Google Sheets

[jpfieber]

It’s quite common for forum members to ask about issues with one of their accounts, which often turn out to be a problem with the feed from the institution that hosts it. Tiller has a spreadsheet called Institution Alerts that maintains a list of known issues with institutions that Tiller supports. It occurred to me that if we have a list of the institutions that host our accounts, and a list of institutions that may be having an issue, we should be able to give ourselves a heads up of potential problems!

This isn’t a full-blown template, I’ve instead baked it down to either one column or one cell depending on how you want the results to appear. It’s not going to be perfect, and it’s not aware of account types so it may flag account types you don’t have at that institution (eg. there’s a problem with credit cards at American Express but you have a savings account, which isn’t having problems). Note that because the Institution Alerts source sheet is a Google Sheet file, making this work in Excel is much more difficult, so I don’t have an equivalent solution for Excel.

List of Institutions

This formula pulls the Institution column from Tiller’s Institution Alerts sheet and compares it against the Institution column on your Accounts sheet, and lists the institutions that have a partial match. To use it, find a column that has enough room to hold the unique number of institutions in your accounts sheet (for that terrible day when they are all down!). Copy the formula below and paste it into the cell where you want the “Alerts” heading to appear. In my case, I live in the Transactions sheet, so I got rid of the Tiller logo in A1, and pasted my formula there. The Alerts heading is also a link to Institution Alerts page so you can get more information about the problem and the tentative ETA. You can of course customize the “Alerts” heading in the formula to say anything you’d like.

={HYPERLINK("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Alerts");SORT(UNIQUE(
   IFERROR(LET(results,BYROW(SORT(UNIQUE(INDIRECT(LET(ltr,SUBSTITUTE(ADDRESS(1,MATCH("Institution", Accounts!$A$1:$1,0),4),1,""),"Accounts!$"&ltr&"$2:$"&ltr)))),
   LAMBDA(
      institution,
      IF(institution="","",
         LET(institutions,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Dashboard!A5:A"),
         REGEXREPLACE(IFNA(FILTER(institutions,SEARCH(institution,institutions)))," \([0-9]+\)","")
         )
      )
   )
),FILTER(results,LEN(results)>0)),"No Alerts")
))}

Number of Institutions

This formula pulls the Institution column from Tiller’s Institution Alerts sheet and compares it against the Institution column on your Accounts sheet, and returns the number of institutions that have a partial match. To use it, find any cell where you would want to see this. Copy the formula below and paste it into the cell. The alert text is also a link to Institution Alerts page so you can get more information about the problem and the tentative ETA. You can of course customize the "Institution Alerts: " text in the formula to say anything you’d like (or delete "Institution Alerts: " & if you just want a number).

=HYPERLINK("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333",IFERROR("Institution Alerts: " & COUNT(BYROW(SORT(UNIQUE(INDIRECT(LET(ltr,SUBSTITUTE(ADDRESS(1,MATCH("Institution", Accounts!$A$1:$1,0),4),1,""),"Accounts!$"&ltr&"$2:$"&ltr)))),
   LAMBDA(
      institution,
      IF(institution="","",
         LET(institutions,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Dashboard!A5:A"),
         IFNA(LET(results,FILTER(institutions,SEARCH(institution,institutions)),IF(results>0,1,0)))
         )
      )
   )
)),"ERROR"))

Notes

I’ve only tried this on MY Foundation template (I currently have 2 alerts), so let me know how it works on yours. If you have ideas on how to improve this, or on the perfect place to include these in a Foundation Template, I’d love to hear them! If you find this useful, please let everyone know by clicking the “Vote” button in the upper left corner of this post!

[jpfieber]

I just updated the list script to remove the " (9565)" information after the institution name so it’s cleaner and takes less space, and I don’t know what that number means…

[KyleT]

Awesome, I like your thinking on this one @jpfieber - a simple indicator that something could be going on with one of your institutions. Nice job! I gave it a try and didn’t have any current alerts but I temporarily edited an account name to one on the alert list in my Balance History tab and saw that it got ID’d successfully. In the first formula, I also edited the IFERROR string at the end to say “No Alerts”. (btw I think you removed that error catching upon your first edit).

But then I realized I really like the one cell solution and I tend to agree that Row1 of the Transactions sheet is the place that it would probably work best for me. One additional thought I had was - I don’t know what the refresh rate is on the alerts list and so I don’t know if it’s necessary but it might be nice to control it with some IF statement, whether with a checkbox or just manual edit. It doesn’t seem like for me that it would need too recalculate very much, maybe just once a day or on demand.

[jpfieber]

Woops, think you’re right, just put the error check back in and used your suggested “No Alerts”. I did wonder about ‘refreshing’, but IMPORTRANGE does automatically refresh every hour while the document is open. I guess it would be good for people to share their experience with this part of it. Thanks for the feedback!

[ramerkw]

Nice handy addition. I tried to put in the ‘white space’ on balances, but elected to put into its own sheet for the moment.

[Mark.S]

Nice idea. I’ll try out this dual purpose Transactions cell A1, that first checks uncategorized transaction count. Once all categorized, the institution alert gets displayed.

=if((countifs(INDIRECT("$D$2:$D$100"),"") > 0),(
   countifs(INDIRECT("$D$2:$D$100"),"")),(
   {HYPERLINK("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Alerts");SORT(UNIQUE(
      IFERROR(LET(results,BYROW(SORT(UNIQUE(INDIRECT(LET(ltr,SUBSTITUTE(ADDRESS(1,MATCH("Institution", Accounts!$A$1:$1,0),4),1,""),"Accounts!$"&ltr&"$2:$"&ltr)))),
      LAMBDA(
         institution,
         IF(institution="","",
            LET(institutions,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333","Dashboard!A5:A"),
            REGEXREPLACE(IFNA(FILTER(institutions,SEARCH(institution,institutions)))," \([0-9]+\)","")
            )
         )
      )
   ),FILTER(results,LEN(results)>0)),"No Alerts")
   ))}
))

[jpfieber]

Nice idea, I’ll give that a try!

[Caroleen]

I love this idea!

I tested it out on my own sheet where I have a lot (a lot!) of different accounts, and my list flagged 6 institutions, which I found surprising, but sure enough, when I looked at the Institution Alerts list, I found them all on there, but some of those haven’t been giving me issues (whereas others are longstanding issues).

For example, Capital One was listed which I thought was odd since it was marked green on my Connected Accounts list. On the Institution Alerts list, it’s on there as “Site temporarily unavailable” since November, but I do have a December transaction and the account is listed as connected (and there aren’t any missing transactions *but this isn’t an account I use often). So now I’m wondering whether to trust the Institution Alerts list more than the Connected Accounts list, so this maybe is a question for a different thread…

But I love the functionality if I can figure out why it’s flagging things that seem otherwise fine and triggering anxiety!

[Mark.S]

@Caroleen

now I’m wondering whether to trust the Institution Alerts list more than the Connected Accounts list

Good point. I have 5 and none of them are causing any problems for me.

For a brief amount of time, I was able to view the Aggregator Log hidden sheet. In that sheet, there was a percentage affected type column, and checking a couple that percentage was very low, like 4-5%. My guess is we care about the high percentage alerts

Here is snapshot of my 5 Institution Alerts (Dashboard tab):

If I could filter out institutions that I don’t care about, I would remove a couple. One is a manual account.

But, yeah, if the list is false positives 99% of the time … I might just investigate them as they occur

[Mark.S]

@jpfieber

I just updated the list script to remove the " (9565)" information after the institution name so it’s cleaner and takes less space, and I don’t know what that number means…

It’s the Institution ID. There’s a column for that in the hidden Aggregator Log sheet, I was briefly able to view.

[rayfes]

Very nice! It correctly identified 2 institutions that are having problems. I periodically check the outage sheet but this is better and I added it to my custom dashboard sheet. Thank you!

[Whydontyouwork]

Has anybody tried linking this with banks that haven’t updated? I’m trying to setup an alert that is basically “go fix the connection” or “you lost the ability to automatically get data”

[Clinton]

Thank you Joseph for the Institutional Alerts Notification. I appreciate it. This would make a great status page with Stop Light (Green, Yellow, Red) on the community site too.

[bvog]

This is great! I frequently use my Balances sheet to check when accounts were last updated, so I added this formula there to quickly see if there’s an alert for an account that hasn’t refreshed in a few days. I have one account correctly identified with an alert (Fidelity Investments) and one false positive (Barclaycard). But still a great tool. Thanks for sharing!