Great, let me know if you run into any issues in the future.
@chris.oneill619 @Rocketghost Wanted to tag you 2 in case you are still looking for an investment tracker. Happy to help you set this up or help you with Cowboy’s one if you prefer that one.
@purujit.saha Thanks!! You guys are so helpful in here! However, just like in cowboys sheet the instructions are written with the expectation it is working but i think we need stuff when or why it doesn’t. So i will comment for what its worth in the event it helps?
After copying template to foundations:
- Populate the accounts to track (column A): Fine, I see my investment accounts
- investment categories (column B) and periods to track (column C). Fine, category’s are linked.
- “The rest should auto-populate”
Right away we have issue. Nothing happens
Reading further:
" It requires Transactions <ok!>, Accounts and Balance History sheets in the foundation template. <ok!> “Don’t modify the hidden section that contains external references forINDIRECTand some other computed columns but everything else should be fair game.” <ok!>
Right from the “start” a question i would have is “where does it get the starting balance?” The formula in there is not able to be read by non technical folks. I also assume from looking it only tracks the main accounts, (e.g. a total brokerage) and is not able to bring in specific holdings within that account?
I wonder on that because I actually maintain an excel sheet that does that using the Data>Stocks function so wondering if that may be incorporated, but of course lacks the banking uploads. I might take a stab at combining the foundations and that, although I am new to Google sheets
@Rocketghost Thanks for trying this out. If you are up for it, we can debug this.
- In your screenshot, if you hover your mouse over the “#NUM!” cells, does it show an error message? If so, what is the error message.
- In the template, columns D through AA are hidden. If you expand it (see my screenshot below for how to expand it), columns M through P should have the transactions of the categories that you listed in Column B populated. Is that coming through?
- What period did you put in?
Forgot the screenshot. Here it is. Note the two arrows between columns D and AB and click on it to expand the hidden columns.
AH, well came back after a few hours and many the errors in the screenshot i provided resolved themselves. Weird, as I did nothing since i took the shot. Anyway the remaining #NUM errors say: " Error XIRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to."
My current return shows as several million %, (i wish haha) and all the starting balances remain @ $0.0.
I expanded the columns and see all the background data. (Column M through P have my transactions).
How does it determine starting balances? Is there a way to manually add this to get it to show more realistic returns?
Wow, that’s strange.
I have only seen temporary errors from the cells that use GoogleFinance calls - those calls are sometimes flaky. Anyways, if it happens frequently let me know
Regarding starting balance, it uses the “Balance History” sheet. Let’s say you enter “2023-01-01:2024-02-29” as your period. The formula will find the latest balance with a date prior to 2023-01-01 and use that as the starting balance. If there is no such balance, it’d use 0. So if you don’t have any balance in Tiller prior to that start date, your returns will be overestimated.
You have 2 options.
- Select a period such that your investment accounts have a balance in Tiller Balance History sheet prior to that date i.e. a date after you started using Tiller. This is useful for people who have always used Tiller or don’t need that long of a history. I used this option since I did not import transactions from Mint.
- Or, if you imported transactions from another finance app like Mint, you can enter your balance for those accounts manually in the Balance History sheet (add new rows on the bottom since Tiller adds rows on the top) with a date on or before the start date of the period you want to track. I am assuming you have transactions already imported - so cash flow should be pulled from there as long as you populate the Account ID column in the Transactions sheet. Tiller assigns a unique Account ID for all of your accounts. It is in “Accounts” sheet hidden column G and transactions sheet hidden column L and should look something like
655e9909fcecd2002f8095b6. Imported transactions won’t have this populated and @RedNell above had to populate that column to get the cash flow data imported.
Let me know if you have any questions or run into other issues. The XIRR issue will go away once your balances and cash flows are fixed. The calculation of XIRR uses a numerical method (there is no closed formula) and relies on an initial guess. So returns of millions of percent often won’t compute.
@purujit.saha I plan to try this out today. Is the version listed at the top of the post updated to include any fixes that you have implemented to date?
Yes, it’s still the same template. I haven’t been versioning it. So there is just one version. Lmk if you run into issues - I’ll be glad to help.
I am getting errors on Cash Flow column. All accounts show #VALUE! with red triangle in upper right corner saying " Error Unable to parse query string for Function QUERY parameter 2: Can’t perform the function product on values that are not numbers"
Suggestions?
I am reluctant to send screen shot with my financial data in it. If that is needed, then I will scrub it a bit and just include a few accounts. I set up a new template and simplified categories to just transfer or investment cash flow like Cowboy suggests.
Using 1M as period and starting balances are extracted as zero for many but not all accounts. The balance data seems fine, its the cash flow that is not working
Column L looks like gibberish … mostly blank after that column. entries are like this one
664f710cb3a500a4b2243a57
Update : Found my error … I had started off with other Categories and switched after reading Cowboy post. I forgot to update the table in green … I just recategorized the data. Now the fields populate but that zero starting balance gives me unrealistic rates of return of course. I know I can just wait and then get more reasonable numbers. Let me know if there is a simple fix (although waiting is simple). Finally, what are the periods I can choose from weekly, monthly, yearly, year to date … do those go like this 1W, 1M, 1Y, YTD … and then is there a lifetime number? LFT?
Glad, you got it working.
Regarding starting balance, I had the same issue since I migrated from mint. I ended up creating one manual transaction for each account that was a deposit a day or two before the date I moved to Tiller. That solves the problem of having a zero starting balance - but of course you only see returns since then. If you have imported transactions from before that, I think we can make that work but will need some tinkering.
For time periods, yes, you can use either “YTD”, or a number followed but W, M, Y etc. and also a date range of course. The “since inception” is interesting and I’ll implement it.
@johnw I added support for the string “now” for date ranges - so if you started using Tiller on Nov 15, 2023, you can specify the date range as “2023-11-15:now”. If you don’t want to reimport the template, just copy the formula from the hidden cell V2 (the column header should say “Expanded Periods”).
Thanks! I added some manual entries to get it to have the proper non-zero starting balance so that seems to work fine. I will download the update as well … and copy and paste the V2 cell as you suggest 
Great! Lmk if you have any other feature requests.
Its working very well. I have a portfolio that is a mix of stocks (VTI index fund) and Bonds (VGIT index fund). When do the comparison, it seems I have to choose either VTI or VGIT, when I would like to do a mix. Any way to compare against a mixed portfolio?
My inspiration when designing the comparison feature was how investment platforms show benchmarks. I am in a similar boat as you and have a mix of stocks and bonds and I wanted to know how I am doing compared to a benchmark like a target date fund - I used VTTSX.
Does that make sense?
In other words, your benchmark should be a standard portfolio or an index fund, imo because I think if you are shooting for a 60-40 portfolio and also comparing it to an 60-40 mix of VTI and VGIT, they’ll just match each other and the comparison will not be very useful. Lmk if I am missing something about your scenario.
Kinda off-topic, I ended up building a way to pull total return data from yahoo finance that accounts for dividends, stock splits etc. If that’s material for you, happy to share but it requires some familiarity with deploying apps scripts for sheets.
I just did as you suggested on my own. I am conservatively invested so went with VTINX. I don’t need the dividend stuff … all good there. Great job with this tool …I love it. Thank you!
1 Like