Google Sheets for portfolio tracking

[gcgang]

iPad.


Sent from my iPad using Early Retirement Forum

[steelyman]

Without trying to feel that I am back at work (this is E-R.org), make sure you have an Apple account and Google, and search the App Store for Sheets. I just did this and it was there. There is no "purchase" involved (no money).

[tulak]

+1 for Google Sheets.

I migrated all my Excel spreadsheets over a year ago and have no complaints. The GoogleFinance function is way easier than anything Excel supports and the ability to view my spreadsheets on multiple devices is a nice extra. Plus, there's nothing to backup since it's on Google Drive.

[donheff]

Quote:

Originally Posted by explanade Yeah I use a Google spreadsheet that lists all the stocks and funds across all my accounts. Maybe takes up 30-40 rows. I didn't break them up into separate tabs or workbooks -- which I think is an Excel feature. So it tallies up each second and then a grand total at the bottom. The GoogleFinance function is indispensable. There are ways to do updating of securities prices in offline spreadsheets but it seems to be more hassle to set up.

I did the same thing for DW and I. For each fund the spreadsheet calculates current price, YTD gain/loss, percent of portfolio, fund type (for AA), then subtotals by account, then totals for all funds. Below all that I run some rows with AA, year by year spend, and SWR calculations using several approaches just to keep track of where I would be with each. I am tempted to drop the SWR part since I can't really go back to some other approach but will probably wait for a big downturn to see how they diverge. For overall growth I just calculate last year's EOY total against current total (which includes the negative amount of my spending). I recognize that doesn't tell me the actual total return of my combined funds - I will leave that amount of detail to the engineers.

What I like best about the Google sheet is watching the numbers go up after a big up day. I would not recommend regular peeks for risk averse investors since the down days can be eye popping.

[explanade]

How do you get YTD info and fund type? Are these special functions or different parameters of GoogleFinance function?

[donheff]

Quote:

Originally Posted by explanade How do you get YTD info and fund type? Are these special functions or different parameters of GoogleFinance function?

I just invented some designators for fund type based on my own assessment I then add up the %of portfolio for all funds in those types for calculating my AA -- basically, it is a manual, subjective calculation. I add up equities and bonds and then break those up by small cap, value, and international. Fairly close but not scientific.

YTD is a GF function -- =GoogleFinance(B7,"returnytd") where B7 is the ticker cell for the fund in row 7. There are other functions I use, like 12 mth distribution yield -- =GoogleFinance(B7,"yieldpct") and 1 yr total return -- =GoogleFinance(B7,"return52"). I look at those to evaluate which funds should be tapped for periodic withdrawals.

The key current price value returned every evening to track portfolio values is -- =GoogleFinance(B7;"closeYest"). Like others, I periodically update the numbers to reflect changes due to distributions. Some people do this automatically but I don't like giving any other sites access to my fund sites so I update manually every few months.

I borrowed the concept from a sheet someone here posted years ago and then modified it to give me what I am interested in.

[explanade]

Thanks, I'll check those out.

[Cobra9777]

Quote:

Originally Posted by donheff I just invented some designators for fund type based on my own assessment I then add up the %of portfolio for all funds in those types for calculating my AA -- basically, it is a manual, subjective calculation. I add up equities and bonds and then break those up by small cap, value, and international. Fairly close but not scientific. ...I periodically update the numbers to reflect changes due to distributions. Some people do this automatically but I don't like giving any other sites access to my fund sites so I update manually every few months.

This is precisely why I use sites like Personal Capital and Fidelity Full View (with GPS) for basic data accumulation and portfolio analytics. For starters, they always know the correct share count. But more importantly, they dissect each fund down to the individual securities being held for a very precise measurement of asset allocation, performance, yield, expense ratios, duration, sectors, developed vs emerging, etc. For example, you'd be surprised how much cash you're actually holding when you see how much cash each of your funds holds. Of course, I can't spend that at the grocery store, but it does affect performance and thus asset allocation and fund selection decisions.

I think Google Sheets and Excel both have advantages and disadvantages. But my overall point is that spreadsheets in general are better suited for periodic decision support, analysis, modeling, scenario planning, what-if's, etc. Basic real-time data accumulation and categorization is much more efficient and accurate with readily-available, robust tools designed for that specific purpose.

Regarding security, I can remember 10-15 years ago when I wasn't comfortable entering my credit card number on Amazon. We all got past that. I wasn't comfortable with the account aggregation tools until about 2 years ago. So I understand the hesitation. But at some point, you just have to get past that too. On the other hand, if you are a die-hard DIY type who's OK with subjective estimates of AA, then carry on.

[explanade]

I don't know the Fidelity Full View. Most of my accounts are with Vanguard, including IRAs that I moved from Fidelity.

I checked out Personal Capital but leery about giving my login data to some external party. And then they apparently look at your portfolio and try to sell you services.

[steelyman]

I guess it's about what you are interested in. As I've mentioned in LOL!'s timing thread, I currently just do monthly shifts of funds within an account. The rest of the time I just hang and let the funds or stocks do their thing. I know generally what their stated purposes are.

[aim-high]

Quote:

Originally Posted by steelyman Here's how I organize my workbooks for tracking investments with Sheets. I have separate workbooks for taxable and retirement accounts. Each book has a "Summary" sheet that pulls the totals from the other sheets. Then there is a "Securities" sheet that lists the various investments, names, ticker symbols, and current price (obtained through the GoogleFinance function). This can be referred to by other sheets. The remaining sheets are specific holdings at different companies (Fidelity, Vanguard, etc.). These are the only things I have to update due to purchases, sales, distributions.and so on. Otherwise, all I have to do is open the workbook and everything is up-to-date through the magic of Google Sheets.

Any chance you can create a version of them with your personal data removed and share them? It would give some of us less sophisticated types something to start with.

[steelyman]

Quote:

Originally Posted by aim-high Any chance you can create a version of them with your personal data removed and share them? It would give some of us less sophisticated types something to start with.

I can try, for sure. It's not too complicated. I'll just make up data. I don't know if I can post it to E-R.org, so any tips would help.

[aim-high]

Quote:

Originally Posted by steelyman I can try, for sure. It's not too complicated. I'll just make up data. I don't know if I can post it to E-R.org, so any tips would help.

1. Make a new folder in your Google Docs. Eg. Call it ERSheets

2. Right click on each of your spreadsheet documents and make a copy of them.

3. Right click on the copies and move them to your ERSheets folder.

4. Edit the sheets to replace your data with fictitious data.

5. Right click on the folder and choose "Share" to share it publicly by changing access to allow "Anyone who has a link" to have view only rights to the folder.

6. Right click again on the folder, choose "Share" and it will give you the link for access to it. You can copy / paste that link here and it should share the file.

If you're uncomfortable sharing it with the world, you can share it with just me, and I can copy them to my aim-high google account and share them. Just PM me the link. I will keep it private.

I don't think there is any risk of losing anonymity by sharing a folder, but just in case I've setup an anonymous gmail acount for such occasions.

[target2019]

Quote:

Originally Posted by steelyman I can try, for sure. It's not too complicated. I'll just make up data. I don't know if I can post it to E-R.org, so any tips would help.

If this is Google sheets, copy your existing file and rename. Change all the data. Close it and share to "anybody with the link." don't let everyone have capability to change. You can post the link, or just email it to those you trust.

[steelyman]

OK thanks, aim-high. I will get it done by the end of the weekend.

Anonymity is important to me but I can call out my gangsta friends, the moderators, to lay a hurt on any violators.

[target2019]

Quote:

Originally Posted by aim-high 1. Make a new folder in your Google Docs. Eg. Call it ERSheets 2. Right click on each of your spreadsheet documents and make a copy of them. 3. Right click on the copies and move them to your ERSheets folder. 4. Edit the sheets to replace your data with fictitious data. 5. Right click on the folder and choose "Share" to share it publicly by changing access to allow "Anyone who has a link" to have view only rights to the folder. 6. Right click again on the folder, choose "Share" and it will give you the link for access to it. You can copy / paste that link here and it should share the file. If you're uncomfortable sharing it with the world, you can share it with just me, and I can copy them to my aim-high google account and share them. Just PM me the link. I will keep it private. I don't think there is any risk of losing anonymity by sharing a folder, but just in case I've setup an anonymous gmail acount for such occasions.

Your procedure is excellent.
When sharing I've only used my anonymous Gmail account. That's the best path given a the uncertainty about security.

[steelyman]

What's an anonymous Gmail account? Mine is my name.

[target2019]

Quote:

Originally Posted by steelyman What's an anonymous Gmail account? Mine is my name.

If it's Steelyman, then relatively obscure depending on how you've used the account.
If it's Your Name, then not anonymous.

[steelyman]

Quote:

Originally Posted by target2019 If it's Steelyman, then relatively obscure depending on how you've used the account. If it's Your Name, then not anonymous.

Well, it's not Steelyman. This is the only barroom I go by that. But I understand what you're saying, thanks!

[frugal_dave]

You can not password protect Google files