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]

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]

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?

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

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]

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]

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]

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]

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]

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/google-sheets/id842849113?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]

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?

 

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

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?