I spent some time adding in historical balances of manual accounts to make my net worth data more accurate. At one point I noticed some very weird results in the Net Worth sheet - basically it would copy one old value and use it for all subsequent dates, even if there were new entries. Sometimes it would just do this for my manual account, sometimes for all accounts after that specific entry (although the Net Worth Snapshot always gave expected values for the given dates).
At first I assumed I had made a data error, which I spent a long time chasing down (restoring backups, repairing all of the ‘month’ and ‘week’ column values, trying to limit the culprit to one single line, replacing with a fresh Net Worth sheet).
Eventually I found that the sorting of the balance history sheet seems critical (date descending). Is this intentional? If so, it’d be nice if it could be documented somewhere, if it can’t be more flexible on inputs.
Hi @RedNell That’s a good observation and sorry to hear about the additional time spent troubleshooting, but I believe you’re correct that the Net Worth sheet is working off an assumption that the Balance History is sorted Date descending. It appears that it’s doing a filter of any balances before the given date and then taking the one at the top of the list. So if the data is not sorted it won’t grab the one that you expected it to. I was able to recreate your observation. On the other hand, the Net Worth Snapshot is using a query function and sorting the data first - this is why you saw a difference between the two. I could only speculate why the Net Worth sheet works that way, but I think that all Tiller built solutions that populate Balance History will likely always sort it and/or insert the data in the chronological order. So the problem only arises if manual edits are made to the balance history. In this case you would always need to do a sort afterwards. I have numerous manual balances that I update at least monthly and I remarked in recent post that I found it faster to insert them into the Balance History directly than using the sidebar. So I created a template for this and it ensures that the balances are inserted in chronologically descending order. I might do a show and tell to share it at some point.
In the meantime for what it’s worth I can share a slight edit that (although not tested extensively) I think would resolve that for the Net Worth Sheet in case Balance History isn’t sorted. Note that the Net Worth sheet doesn’t look at the Time of day, so more edits would be needed if going for multiple balances in a day (I’m guessing not a likely use-case). If you want to try this then I’d suggest making a copy of the Net Worth Sheet that will be your edited copy- in the hidden area to the far right, under the section Data for Trends Table you can make the following formula edits (note that I was testing it in row 25 but you could start at the top and fill down).
