Docs: Repair Balance History workflow

Google Sheets Show & Tell
,
1

About Repair Balance History

Inconsistencies in your Balance History sheet can occur over time when you rename accounts, remove and re-add accounts, or changes occur with the data feed. Data inconsistencies can result in duplicated or missing accounts and erratic rendering of account data.

Screen Shot 2020-03-05 at 9.27.33 AM
Screen Shot 2020-03-05 at 9.27.33 AM1930×704 60.5 KB

The Repair Balance History workflow finds inconsistencies and unifies account information using the most recent records.

These issues fall into three categories:

  • Blank Account ID. Accounts without Account ID that appear to match an account but without an Account ID. Possible matches are identified by a matching Account #.
  • Account ID Mismatch. Accounts with matching Account # but different Account ID, which Tiller uses to uniquely identify and enumerate accounts in your spreadsheet.
  • Inconsistent Account Data. Unique accounts with mismatched Account name, Institution, Class, or Type throughout your Balance History.

For best results be sure that you have your desired Account names set on the Tiller Console under Account Summary for all accounts before repairing Balance History.

How to use Repair Balance History

  1. Open the Tiller Community Solutions add-on from the Add-ons menu at the top of your Tiller-powered Google Sheet.
  2. Navigate to Tools > Utilities > Repair Balance History. The workflow will check for compatibility issues.
  3. The tool will analyze your Balance History sheet for inconsistent data.
  4. If there are any issues, the sidebar will report them in three groups: Blank Account ID, Account ID Mismatch & Inconsistent Account Data.
  5. Review the found issues carefully.
  6. To correct an issue, click the checkbox to the left of it, then click the Repair Checked Accounts button.
  7. New Balance History inconsistencies may appear after correcting one or more issues. Continue addressing issues until no new issues appear.

Screen Shot 2020-03-05 at 9.28.40 AM
Screen Shot 2020-03-05 at 9.28.40 AM598×1306 86.1 KB

Be aware that modifications by the Repair Balance History workflow can only be undone using Sheet’s Version History tools.

How the Repair Balance History Works

Blank Account ID

Accounts with blank Account ID are most often created when records are migrated from another tool, platform, or Feed Bot spreadsheet.

The Repair Balance History workflow identifies matched accounts using the Account # column. When balance history rows are found with a) empty Account ID and b) an Account # that matches an account with a populated Account ID, the workflow will recommend writing the known Account ID into the rows where that field is blank.

Account ID Mismatch

Account ID Mismatch issues are flagged when one or more Account IDs are used for accounts with the same Account #. Account IDs are often re-assigned for a single account when you remove the account from the Tiller Console (deleting the data from our databases) and then reconnect the account later and link it to the same sheet.

The Repair Balance History workflow will overwrite the Account ID field in older, matched accounts to reflect the new Account ID, unifying the accounts within the spreadsheet.

When Account ID Mismatch issues are corrected, they may result in new Inconsistent Account Data issues (because additional records are now unified under a single Account ID).

Inconsistent Account Data

Other than the Date and Balance columns, the fields in each balance history row should stay consistent for each account— i.e. fields such as Account, Class and Institution should not vary. If an account is renamed in the Tiller Console or account metadata changes during data aggregation, these fields may change resulting in erratic behavior.

The Repair Balance History workflow will flag changes within rows with the same Account ID and repair them by unifying fields to the values from the latest additions.

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this tool in the Google Sheets category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

1 Like