Positions & Analysis dashboards - Investment Contest Entry

[valerie1]

Overview

I built 2 complementary Investment dashboards for the Tiller contest: Positions & Analysis. You can install them separately if you wish.

Here’s a video that lets you see them in action: https://drive.google.com/file/d/1gN_dB2XlPpXm8lYACvmA4MrSrlYhGz1z/view?usp=sharing

1. Positions : lets you monitor account balances and individual holdings across your portfolio or by account, with custom sort & filtering, and live market data from Google Finance.

2. Analysis: I made this dashboard in 2 sizes so you can pick your layout preference:
   Compact (1 sheet) or Relaxed (2 sheets). Both feature:

   • At-a-glance KPIs: portfolio value, 1-day and 90-day change, asset allocation (Stock / Bond / Cash / Other) and tax breakdown (Taxable / Tax-advantaged / Tax-free)

   • Top 10 holdings by value, gain, loss, return or concentration, with a configurable concentration warning when a single stock passes your risk threshold

   • Movers: your biggest gainers and losers over different time horizons

   • A heatmap of accounts by holding type, and many types of rollup charts to show you details by account, holding type, allocation, tax bucket or Account Group

   • Drill in or zoom out (portfolio or account)

   • Stock diversification: foreign vs domestic and Fund vs. individual stock exposure, plus an S&P 500 monitor to track real-time market conditions

3. Click images below to see Positions, Analysis (1 sheet version), Analysis:Overview & Analysis Trends (2 sheet version)

And watch the video to see them working! https://drive.google.com/file/d/1gN_dB2XlPpXm8lYACvmA4MrSrlYhGz1z/view?usp=sharing

What is the goal of your workflow? What problem does it solve?

My sheets let you track the immediate and long term health of your portfolio, as well as monitor diversification and risk factors that align with your investment and savings strategies. Learn:

• What am I worth, and how is it trending?
• Where is my money? By account, holding type, allocation, tax bucket or Account Group.
• What’s my high-level asset allocation? Stock, Bond, Cash ratios.
• Where will I owe taxes? Taxable / Tax-advantaged / Tax-free buckets.
• Am I too concentrated in particular stocks, assets, or accounts?
• What moved the most? Biggest gainers and losers over the periods you choose.
• How are my investments doing?

My inspiration

I’ve been building custom dashboards on Tiller since 2019, and use them to answer many questions around my investment goals. I pulled in some of my favorite features from my own dashboards, focusing on the ones with minimal effort and maximum usefulness. The contest really inspired me to make nicer visuals too!

Installation

Analysis & Positions Sheets Templates (this link makes you your own copy)

1. Open the Sheets link while signed in to the Google account that has your Tiller sheet.
2. This will create your own copy of everything!
3. For each template sheet you want to copy into your primary Tiller sheet: right-click the tab → Copy to → Existing spreadsheet → pick your Tiller spreadsheet → Insert. Order doesn’t matter. Always include iConfig.
   1. Copy over iConfig and Positions tabs
   2. Copy over the Analysis tab(s) you want. Choose from the compact view (Analysis tab) OR the relaxed view (both Analysis: Overview and Analysis: Trends tabs) or all of them - the relaxed view tabs must be installed as a pair as they are interdependent.
4. In your Tiller sheet, rename each new tab to remove "Copy of " prefix (e.g. “Copy of iConfig” → “iConfig”) and any number it added after sheet name, so the expected sheet names exist.
5. Make sure your personal Tiller sheet contains Holdings and Holdings History sheets, for these new reports to work.
6. In your iConfig sheet, give every account a tax bucket, via Account type defaults (common types are preset) or Account overrides.
7. Review iREADME in the link above for more details about all the features.
8. That’s it!

Setup

1. In iConfig, check your sheet names: change the Holdings & Holdings History names only if yours differ from Tiller’s defaults.
2. In iConfig, optionally set any custom overrides (e.g. tax bucket, allocation, or foreign stock) for an account, symbol, or holding type. There are automatic smart defaults for all Yodlee & Plaid account & holding types out of the box. Scroll right in iConfig to see all the tables and built-in classifications.
3. Optional: fill in other iConfig customizations. Customize your stock concentration warning level, hide accounts or symbols if you wish.
4. Optional: set up Account Groups on Tiller’s Accounts sheet to group accounts on Positions and see allocation by group in the Analysis rollups.

Usage

1. Open a dashboard:
   • Analysis (or Analysis: Overview) for the big picture.
   • Analysis: Trends for your balance over time and returns. (Relaxed version only)
   • Positions for every holding by account.
2. Choose a View: each dashboard has a View picker in its title bar. Pick “All Accounts” or one account.
3. Use the dropdowns to explore:
   • Top 10: by Value, Gain, Loss, Return or Concentration.
   • Movers: biggest gainers and losers over 1, 7, 30 or 90 days, or your own periods.
   • Rollups: by account, holding type, allocation, tax bucket, or allocation by Account Group.
   • Trends chart: 1 Month, Year-to-date or 1 Year. (Relaxed version only)
   • Positions: group the left side by Institution, Group or Type, and sort by any column.
4. Hover for help: headings with a small triangle in the corner explain what they show.

Everything reflects your last Tiller sync (“Holdings as of” date), except Positions’ “Today”, “52W Range”, and S&P500/SPY data, which come live from Google Finance. The Movers table also backfills missing prices from Google Finance.

Occasional upkeep (only when something changes)

• New account: if the Tax breakdown says “Add missing tax buckets in iConfig”, give that account, or its account type, a tax bucket in iConfig. The Tax Bucket rollup is also helpful here.
• New kind of holding: optionally give a holding type a display name and a Stock / Bond / Cash / Other allocation in iConfig.
• Leave something out (e.g. a donor-advised fund): set Hide for that account, account type or symbol in iConfig.
• Tidy iConfig: use the −/+ buttons above its tables to collapse the columns you’re not editing.

Permissions

Is it ok for others to copy, use, and modify your workflow? Yes

Notes

• I’d love community feedback and to keep adding improvements.
• I have tons more ideas, but stopped so I would actually get my entry in on time!
• My sheets are built to work gracefully for simple & complex portfolios, up to 30 accounts, and hundreds of current holdings. Lots more details in the iREADME sheet of the template!
• My dashboards work well with Tiller Account Groups.

[valerie1]

I’m still adding updates! There’s a new sheet in the template called “changelog” where you can see enhancements made since I initially submitted into the contest (and recorded the video).You can check which sheets have been updated & how.

It’ll be worth grabbing the latest if you have an earlier version, because I’m making continuous improvements based on real usage. Nothing drastic but still getting better and better!

Also, because it’s how I roll, I am not adding ANY changes inspired by other contest entrants while the contest is open. It’s been great to see the unique ideas.

Cheers.

[mysterytea]

This is very thorough and easy to use! One thing that I expected to see but seems to be missing is differentiating stock allocation between US and international. I’ve set my international stock funds as “Other” and that works but isn’t ideal. Is there a better way to handle this?

[valerie1]

I track this on my own portfolio but it requires manual effort periodically since it’s not in Tiller/Yodlee as of now, which is why I left it out. However, I’d be happy to add it. Do you want it at the per symbol level (as in the symbol is International/Domestic) or do you want to track it by % of symbol (e.g. 44% international), or something else?

[mysterytea]

Per-symbol would work for me. My portfolio is all index funds, one of which is an international stock index fund. No target date or other types of funds with mixed allocations.

[valerie1]

Cool, let me take a crack at this and I’ll reply back when I post it. Thanks for your feedback!

[valerie1]

@mysterytea i made a temporary template here: https://docs.google.com/spreadsheets/d/1jty30YQ0bR0nTBqYoh5MS4eoOLoBZgySAIgrr2xVY1A/copy

It has the following updates:

• iConfig now supports per-symbol foreign setting or %
• Rollup “Asset Allocation” now divides domestic & foreign stock across accounts
• New Rollup “Stock diversification” → domestic vs foreign & single vs funds
• Allocation by Account Group → includes domestic vs foreign split
• There’s a foreign % metric on the Analysis & Analysis:Overview tabs

Let me know if this looks helpful, if so I’ll publish to the main file. Otherwise let me know what you think.

[PCB]

I don’t know if somebody else did that, but could you make it so that the portfolio view also shows the comparison to a specific reference stock?

[valerie1]

Can you tell me more about what you mean? Like portfolio growth vs SPY (or some other market reference) for a certain period?

[PCB]

Yes, comparison with a benchmark for a given period.

[PCB]

Or with another stock/fund for the same period.

[valerie1]

let me give this some thought.

one major difference is that holding data does not know about fees, distributions, additional deposits/withdrawals in the period, etc, as is normally considered for published returns on benchmark indices so it’s not apples to apples, but if it’s data in google finance i may be able to calculate purely with that data instead of the the Tiller data which presumably would make it apples to apples.

if it’s Tiller data vs benchmark (which has a lot of adjustments), would you still find that valuable even with a big caveat that they’re not identical measures?

[valerie1]

also which Analysis sheet(s) are you using? the single page or the double page version?

[mysterytea]

Valerie, I tried this and it is working well! Thanks for the quick update!

[valerie1]

Great! Ok I’m going to publish this to the main contest sheet right now. I’d recommend grabbing the latest sheets from the main template linked here in a few minutes (if you made a copy of the test sheet). Also worth checking back at the end of the contest period to see if there are any other big updates in the main template - I’m noting them in the changelog sheet now.

I added automatic allocation settings for all Plaid/Yodlee holding types just a little while ago today. The version that has it has a new table called “Built-in holding types” in the iConfig sheet on the far right.

Cheers!

[PCB]

The holdings sheet doesn’t, but we get the transactions in the transactions sheet. I was thinking there maybe was a way to get a formula to account for that.

[ahorsch64]

@valerie1 I just installed and got it set up with no issues. At first glance, this looks fantastic. Looking forward to exploring in detail. Thanks!

[valerie1]

Integrating with Transactions would be really slick - tricky because different banks’ descriptions vary wildly. I spent some time experimenting over the weekend but didn’t get things to a place i would want to share it yet. I’ll let you know if I do come up with a good solution for a benchmark comparison to the holdings data over time.