Serious Issue: Renaming an investment account causes all accounts in that category to become ungrouped, an apparent table-related ‘#SPILL!’ error.
Steps to repro: Using Excel Foundation Template set up and functioning correctly with several different investment accounts from Fidelity Investments.
- Log into Fidelity Investments. Select one investment account that is already linked to my Tiller spreadsheet.
- Change the name of the account, which does not affect the account number.
- Log into my.tiller and Refresh all Fidelity Investment accounts. Note that renamed account still appears with old name, and new name of that account does not appear.
- On the Tiller Console, in ‘Linked Spreadsheets’, expand my named workbook to display all linked accounts. The newly named account appears without being checked as active. Checks the ‘active’ checkbox.
- Open my Tiller workbook.
- Click on ‘Balances’ spreadsheet tab to open it.
- With the ‘Tiller Money Feeds’ addin active, click on ‘Fill’
- Observe that all investment accounts now appear as ‘Ungrouped Asset’ and ‘Ungrouped Liability’ accounts.
- Click on the ‘Accounts’ tab and open that spreadsheet.
- Observe in the Accounts table, ‘Unique Account Identifier’ and ‘Account’ columns contain ‘#SPILL’ errors.
What is the correct way to change the name of a linked account in it’s source location and have that change properly reported in the Tiller spreadsheet (including transferring all previous transactions from originally named account to the newly named same account?)
Thanks for any help (Heather to the rescue!)
@kipmeister - It sounds like there is a bug with the way the rename happens when you rename on the Fidelity site directly. This is odd behavior and I haven’t seen it before, but something we can look into with our data provider at some point.
Our recommended workflow for renaming accounts is to nickname them on our side (rather than on Fidelity’s website directly).
Once you rename them on the Console the fill will have the updated name (it’s not retroactive) and then you’d need to re-select accounts on the Accounts sheet - there isn’t a way around that behavior where it groups then in the “ungrouped” buckets, just due to the way the data validation is set up.
Heather, thanks for looking at this.
In Fidelity, what happens is the nickname for the account is changed. The nickname is associated with the console spreadsheet, yet points to the same account number in Fidelity. When the nickname is changed in the Console workbook that’s linked to my Tiller workbook, the #SPILL error occurs in the ‘Accounts’ spreadsheet.
I went back into Fidelity and changed the nickname back to its original name, thinking that might fix things. In the Console, I checked that account so that it would be included in updated. Even though the account nickname was changed back to it’s original name, the #SPILL error persisted in the ‘Accounts’ spreadsheet. The only way around this for me now is to uncheck that account and unlink it, which of course distorts my reporting.
The #SPILL error is just another friendly gift from MS. It occurs as a ‘feature’ with the TABLES paradigm in Excel. We’re so lucky. A work-around might be to write code that reorgs the Accounts table any time a change like this occurs. Of course, I have no idea how this may affect downstream code and dependencies.
Another consideration is how pervasive this error might be. Does it occur anytime a naming change is made to preset accounts in the Accounts table? Are there any other changes to the Accounts table that might result in a similar error?
The issue is caused by artifact entries on the last line of the table containing Accounts data. These entries appear on the last line of the ‘Unique Account Identifier’ (Column F) and the 'Account" (Column H) columns. Removing these two entries allow these columns to populate as expected and the #SPILL error no longer occurs.