Google Sheets for portfolio tracking

[donheff]

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]

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]

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]

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]

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]

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]

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]

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]

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

 

[target2019]

You can not password protect Google files

But you can zip an archive and password protect that, and upload to Google.

 

[aim-high]

I have created a folder on my account that I have shared with anyone who has this link.

https://drive.google.com/open?id=0B0MtA8E8O9g3R0VlV0t6U3gzZUE&authuser=0

I have put a test spreadsheet in there. Anyone with Google Docs should be able to open it. They will not be able to edit the spreadsheet, but they can copy it and save it to their own Google Docs account. From there they can edit the spreadsheet.

The only identifiable information of mine that is on the spreadsheet that shows up is my name "Aim High". Thus I wouldn't do this if I wanted to keep my privacy and my personal name was on my gmail account.

 

[steelyman]

I just got an update to the Drive app. Now I need to fake up a Sheets spreadsheet on a new account and give it to aim-high. It'll be really simple, like three accounts with three funds/stocks each, but enough to get the idea.

 

[MBAustin]

Wow, had no idea Google had all of that built-in. DH has a system to update our portfolio tracker but I think it's pretty kludgy compared to this. Think we'll look into this. Thanks for all the tips!

 

[target2019]

Here is a link to a spreadsheet that I've set up to read Google stock and fund functions for ETF, Stock, and MF.

If you sign in to google, then you can copy the sheet to your own drive and edit.
I hope. LOL.

There is also a sheet to see how to read in Yahoo data. That still needs some work.