Google Sheets for portfolio tracking

[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.

[MBAustin]

How is this different from doing it in Excel?

[steelyman]

Quote:

Originally Posted by MBAustin How is this different from doing it in Excel?

I think it's less work. Another plus is that it's always available on any device and backed up (ignoring the recent cloud bad press).

[photoguy]

I really came to dislike google spreadsheets after having used it for work (it chokes on anything but the smallest files). But it should be okay for this purpose and it makes it easier if you want to share it with a spouse.

I probably have too many sheets but here's how I organized it:

- sheet for the funds organized by account / holding company
- sheet summarizing asset allocation (cash, bonds, equites broken down into large/small/value/foreign/reit)
- sheet for historical net worth (only goes back to 2007)
- sheet for current prices of stocks
- sheet calculating total cost of funds (via expense ratios) and expected dividends
- sheet summarizing asset allocation by taxable / non-taxable
- sheet for fixed income (I-bonds, CDs, etc.)

Everything is calculated automatically from the prices. I only need to update #shares and fixed income

[steelyman]

photoguy, I believe you on Sheets choking on complex/large spreadsheets. Mine is not that complex. But I am still a Quicken user, so that handles the more analytical functions.

I do use Sheets to also share family finance information with siblings and an accountant, and it's very convenient.

[sengsational]

Quote:

Originally Posted by MBAustin How is this different from doing it in Excel?

I like the snappiness of having a spreadsheet local, but can you do the automatic price update with Excel?

Quote:

...current price (obtained through the GoogleFinance function)

[photoguy]

The sharing and commenting capability of the google suite is awesome. I think it's a killer feature and seems to work much better than anything else I've used (e.g. iCloud). Simultaneous editing also works quite well. It just has the achilles heel of working in a browser.

[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.

[steelyman]

Quote:

Originally Posted by photoguy It just has the achilles heel of working in a browser.

I mainly use it through an iTouch app, which is fine unless you have lots of edits. I just check it like my dad used to check stock quotes in the paper.

One does need an Internet connection, although you can work in an offline mode.

[steelyman]

Quote:

Originally Posted by explanade I didn't break them up into separate tabs or workbooks -- which I think is an Excel feature.

Sheets lets you create tabs ("sheets") within a workbook and they can refer to each other. I rely on that.

[steelyman]

I had (have) this huge Excel workbook that was to tell me when I had enough to stop working. All kinds of projections, scenarios, history, etc.

Now I just ask "what do I have today?", and that's enough for me.

[Cobra9777]

Quote:

Originally Posted by sengsational I like the snappiness of having a spreadsheet local, but can you do the automatic price update with Excel?

Yes. Excel has a built-in connection to MSN MoneyCentral for stock prices, currency rates, and major indices. You can set it up to refresh in the background at whatever interval you want. It's under the Data tab, then click Existing Connections. I've used it in the past... a little clunky, but works fine. However, it was always a hassle to maintain accurate share counts with reinvestment, etc. So now, I just use Personal Capital and Fidelity Full View. They both have good portfolio analytics in addition to aggregating all your account balances and transaction information. I have a large retirement spreadsheet in excel that I mostly use for planning, decision support, tax what-if's, etc. I update the portfolio tab in that spreadsheet once or twice per month and it only takes about 2 minutes to copy ~25 numbers from Personal Capital.

[target2019]

Google sheets can access market data with built-in functions. With Excel you need to add the functions through some method, or make web queries.

In sheets it is simply something like:

=GoogleFinance(CSCO,"price")

Yes, this can be done with Excel, but not as simple out of the box for the novice spreadsheet user. Newer versions of Excel (I use 2007) may be better at this.

I have a gsheet with about 100 hundred rows, one stock per row. It pulls in live data, maybe 6-10 fields per stock. There are less than 26 columns, and it takes a minute or two sometimes to load data and recalculate. So it is not as good as Excel, IMO, but it is free.

Also found that I needed this function to import dividend data:

=IFERROR(IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",CSCO,"&f=d")))

[gcgang]

I get an error message when I try to download the Sheets App, that it is no longer available.


Sent from my iPad using Early Retirement Forum

[steelyman]

Quote:

Originally Posted by gcgang I get an error message when I try to download the Sheets App, that it is no longer available. Sent from my iPad using Early Retirement Forum

Mine was just updated. You might want to try "Google Docs" or "Google Drive", which is an umbrella name.

[gcgang]

Sheets, Doc, Drive, no luck.


Sent from my iPad using Early Retirement Forum

[steelyman]

Quote:

Originally Posted by gcgang Sheets, Doc, Drive, no luck. Sent from my iPad using Early Retirement Forum

I think I get my app through Apple's AppStore. For web, I just go to drive.google.com .

[target2019]

Quote:

Originally Posted by gcgang Sheets, Doc, Drive, no luck. Sent from my iPad using Early Retirement Forum

No ipad here, but maybe this is what you need?
https://itunes.apple.com/us/app/goog...842849113?mt=8

[gcgang]

Thanks for the link, but it was same place I was before. "The item is no longer available for purchase."

Sometimes, I hate computers.

[steelyman]

Quote:

Originally Posted by gcgang Thanks for the link, but it was same place I was before. "The item is no longer available for purchase." Sometimes, I hate computers.

You haven't said what you are using.

Are you on a PC or a mobile device?