Not sure if anyone noticed this but the schedule pulls in day before the last day in the month consistently at least with my data. I figured this was due to the time included in the date so I changed the formulas in the hidden section to pull “BalanceHistory[Date]<(EOMONTH(AJ2,0)+1)” instead of “BalanceHistory[Date]<=EOMONTH(AJ2,0)”. This seems to have fixed the issue.
1 Like
Thanks for sharing this solution @joe.beaulieu !
Hello, I was wondering how one could modify this worksheet to display the contribution of each asset class to the total assets (instead of the actual value). This is probably trivial but my understanding of complex Excel formulas is near zero. At this stage I just created a copy of the Net worth tracker that points to the original worksheet and divides each asset by the total but this will break if I add a new account or change the time range. Thank you
What you’re asking for is certainly possible, @sombrero2003. There might be community members or paid consultants that can help you with this modification. In the interim, I think the way you created the content you need is clever and functional so long as you don’t change the number of accounts.
1 Like
Nice teamwork on this sheet and great comments and suggestions!
I see here you mentioned getting customized work done via a paid consultant. Can you point me where to go to do this? I would like to create an “Everything Else” section on the Monthly and Yearly Budget templates to show everything that wasn’t included in a budget. Thank you.
The value is there, @mu3484343. That’s just what Excel does when the column isn’t wide enough to show all the digits. Just widen the column.
1 Like
@randy - would love your help here. I’ve got 31 different accounts that are listed in the Accounts sheet.
On the Net Worth Sheet, only 27 of them show. Is there a limit to how many can be shown on this sheet? Nothing I change seems to get them all to show up at the same time. I want to be able to see the decrease in some of the liabilities (Student Loans) month over month, but can’t get them to show.
I’m surprised by that, @jrobertson427. I just peeked at the master and it looks like it uses spill ranges and should be able to handle a couple hundred accounts. Have you peeked over at the source data in the hidden area at right O:AR? There might be some clues there? Are the accounts marked as hidden?
@randy - none of the accounts are marked as hidden.
edit: figured it out.
I have a closed Chase account that is still being added to my spreadsheet. For whatever reason, that account breaks the net worth sheet. Once I removed it from the accounts sheet (and from the list of accounts I have added), everything shows up as expected
Glad to hear you were able to figure that out.
Unsure if I’m doing something wrong; however, I have two manual accounts (an asset and a liability) they are in the same Group. The Net Worth tracker shows the sum of both as the top line value for each Group.
Asset1 = $50.
Liability1 = $25.
In the “Liability” section of this workbook it shows
Group = $75
Liability1 = $25
and in the “Asset” section it shows the inverse:
Group = $75
Asset1 = $50
Oddly enough, the actual Totals for Asset and Liability correctly add up.
It seems that maybe the problem is that there is an Asset and a Liability in the same group? Perhaps this is an unusual categorization/setup that I have?
Yes. I believe there is a limitation in how the template creates group totals. (I agree that it SHOULD sum discretely for Assets and Liabilities.) In the interim, I’d recommend using different group names.
Came here looking to understand why my values were showing blank. I found some hints above and just wanted to share a datapoint that there seems to still be an issue where the order of the columns on the Balance History sheet is causing an error when I brought it in to the most recent Tiller foundation template. I rearranged the columns in my customized file to match that of the NWT and the values all populated as expected.
Thanks for sharing this input. Are you still having an issue with this template?
Umm, no. I fixed it as shared. My point was just that the latest template (still) does not work correctly with the latest addin. My help was to flag it that it might be included in the next revision or that someone else having this issue might see my post and be able to quickly tweak it.
1 Like
I just spent some time trying to repro this issue and would love to hear more about this, @jamesluntz…
I started with the latest Tiller Foundation Template, copied in 14k rows of Balance History data from another sheet, then installed the 0.94 Net Worth Tracker from the link in the header topic.
Some things I noticed:
- The Sheets Balance History source data had a different column order. When I copied the full/raw Balance History block out of Sheets and overwrote the Excel headers, the operation seemed to blow up column references in the Excel Accounts worksheet. (I could see that the hidden area column references were non-sensical.) I copied over the data again starting below the headers and adhering to the Excel column order. This worked.
- When I inserted the Net Worth Tracker worksheet, the account names populated with the local workbook data immediately but the balance data was blank throughout the worksheet. I checked the formulas and could see that the balance lookup in the NWT hidden area was still referencing the master in my downloads folder. After I followed the instructions updating the source, the values all populated properly. Don’t forget this step!
- I could not reproduce the column order issue (unless it is related to copying in data per the first bullet above.
TLDR; I don’t think there are issues with the template master.
I’m curious how this aligns with the experiences of others.
2 Likes
Just wanted to add some additional info for Excel 2021 Professional Plus (local) users. I too had the same issue with the NWT not updating my balances after following all instructions. I also reordered my balance history columns per the guidance given here. However, it still did not work. After exploring the hidden formulas in Net Worth sheet, I suspected it had something to do with newer functions/Excel versions. To test, I uploaded the NWT template to OneDrive and copied/pasted my Balance History and Accounts information, and huzzah, it worked. After some research, it turns out the CHOOSECOLS function that @randy mentioned indeed is not available in Excel 2021. Per this link, Microsoft says you can simulate the function in Excel 2021 PP using Dynamic Arrays, but that sounded like a lot of work so I’ll just keep a separate OneDrive version where I periodically update my Balance History and Account information (since the NWT is designed with monthly views, it makes sense to only manually update every 4 weeks at a minimum). Hope this helps anyone else that may have run into issues using this view in Excel 2021 PP. Great sheet, by the way!
2 Likes
Thanks for that insight @conarda that’s really helpful to know