Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Google Sheets for portfolio tracking
Old 09-02-2014, 03:57 PM   #1
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Google Sheets for portfolio tracking

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

__________________

steelyman is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 09-02-2014, 04:07 PM   #2
Moderator
MBAustin's Avatar
 
Join Date: Jul 2010
Posts: 4,158
How is this different from doing it in Excel?
__________________

__________________
"One of the funny things about the stock market is that every time one person buys, another sells, and both think they are astute." William Feather
----------------------------------
ER'd Oct. 2010 at 53. Life is good.
MBAustin is offline   Reply With Quote
Old 09-02-2014, 04:11 PM   #3
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by MBAustin View Post
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).
__________________

steelyman is offline   Reply With Quote
Old 09-02-2014, 04:30 PM   #4
Thinks s/he gets paid by the post
photoguy's Avatar
 
Join Date: Jun 2010
Posts: 2,301
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
__________________
photoguy is offline   Reply With Quote
Old 09-02-2014, 04:41 PM   #5
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
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.
__________________

steelyman is offline   Reply With Quote
Old 09-02-2014, 04:48 PM   #6
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 3,837
Quote:
Originally Posted by MBAustin View Post
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)
__________________
sengsational is offline   Reply With Quote
Old 09-02-2014, 04:55 PM   #7
Thinks s/he gets paid by the post
photoguy's Avatar
 
Join Date: Jun 2010
Posts: 2,301
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.
__________________
photoguy is offline   Reply With Quote
Old 09-02-2014, 04:55 PM   #8
Thinks s/he gets paid by the post
 
Join Date: May 2008
Posts: 3,423
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.
__________________
explanade is offline   Reply With Quote
Old 09-02-2014, 05:05 PM   #9
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by photoguy View Post
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 is offline   Reply With Quote
Google Sheets for portfolio tracking
Old 09-02-2014, 05:08 PM   #10
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Google Sheets for portfolio tracking

Quote:
Originally Posted by explanade View Post

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 is offline   Reply With Quote
Old 09-02-2014, 06:23 PM   #11
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
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.
__________________

steelyman is offline   Reply With Quote
Old 09-02-2014, 09:18 PM   #12
Thinks s/he gets paid by the post
Cobra9777's Avatar
 
Join Date: Jul 2012
Location: Texas
Posts: 1,132
Quote:
Originally Posted by sengsational View Post
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.
__________________
Retired at 52 in July 2013. On to better things...
AA: 55% stock, 15% real estate, 27% bonds, 3% cash
WR: 2.0% SI: 2 pensions, some rental income, SS later
Cobra9777 is offline   Reply With Quote
Old 09-02-2014, 09:18 PM   #13
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,708
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:
Code:
=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:
Code:
=IFERROR(IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",CSCO,"&f=d")))
__________________
target2019 is offline   Reply With Quote
Old 09-02-2014, 09:27 PM   #14
Full time employment: Posting here.
gcgang's Avatar
 
Join Date: Sep 2012
Posts: 928
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
__________________
In theory, there's no difference between theory and practice. In practice, there is. YB
gcgang is online now   Reply With Quote
Google Sheets for portfolio tracking
Old 09-02-2014, 09:31 PM   #15
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Google Sheets for portfolio tracking

Quote:
Originally Posted by gcgang View Post
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.
__________________

steelyman is offline   Reply With Quote
Old 09-02-2014, 09:41 PM   #16
Full time employment: Posting here.
gcgang's Avatar
 
Join Date: Sep 2012
Posts: 928
Sheets, Doc, Drive, no luck.


Sent from my iPad using Early Retirement Forum
__________________
In theory, there's no difference between theory and practice. In practice, there is. YB
gcgang is online now   Reply With Quote
Old 09-02-2014, 09:44 PM   #17
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by gcgang View Post
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 .
__________________

steelyman is offline   Reply With Quote
Old 09-02-2014, 09:57 PM   #18
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,708
Quote:
Originally Posted by gcgang View Post
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
__________________
target2019 is offline   Reply With Quote
Old 09-02-2014, 10:18 PM   #19
Full time employment: Posting here.
gcgang's Avatar
 
Join Date: Sep 2012
Posts: 928
Thanks for the link, but it was same place I was before. "The item is no longer available for purchase."

Sometimes, I hate computers.
__________________
In theory, there's no difference between theory and practice. In practice, there is. YB
gcgang is online now   Reply With Quote
Old 09-02-2014, 10:25 PM   #20
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: Triangle
Posts: 3,218
Quote:
Originally Posted by gcgang View Post
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?
__________________

__________________

steelyman is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Google Drive app & Google Docs steelyman Other topics 19 03-14-2014 05:07 PM
There's No Tracking Like UPS Tracking easysurfer Other topics 60 05-04-2013 02:28 PM
Google Chrome - Google's new web browser Marquette Other topics 22 09-04-2008 03:24 PM
Google launches Google Finance Olav23 FIRE and Money 11 02-08-2008 02:42 PM
Check out the "google" at the top of the google page cute fuzzy bunny Other topics 0 03-17-2006 09:40 PM

 

 
All times are GMT -6. The time now is 03:42 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.