|
|
Google Sheets for portfolio tracking
09-02-2014, 02:57 PM
|
#1
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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.
__________________
|
|
|
|
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!
|
09-02-2014, 03:07 PM
|
#2
|
Moderator
Join Date: Jul 2010
Posts: 7,913
|
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.
|
|
|
09-02-2014, 03:11 PM
|
#3
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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).
__________________
|
|
|
09-02-2014, 03:30 PM
|
#4
|
Thinks s/he gets paid by the post
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
|
|
|
09-02-2014, 03:41 PM
|
#5
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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.
__________________
|
|
|
09-02-2014, 03:48 PM
|
#6
|
Moderator
Join Date: Oct 2010
Posts: 10,623
|
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)
|
|
|
|
09-02-2014, 03:55 PM
|
#7
|
Thinks s/he gets paid by the post
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.
|
|
|
09-02-2014, 03:55 PM
|
#8
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: May 2008
Posts: 7,418
|
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.
|
|
|
09-02-2014, 04:05 PM
|
#9
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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.
__________________
|
|
|
Google Sheets for portfolio tracking
09-02-2014, 04:08 PM
|
#10
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
Google Sheets for portfolio tracking
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.
__________________
|
|
|
09-02-2014, 05:23 PM
|
#11
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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.
__________________
|
|
|
09-02-2014, 08:18 PM
|
#12
|
Thinks s/he gets paid by the post
Join Date: Jul 2012
Location: Texas
Posts: 3,024
|
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.
__________________
Retired at 52 in July 2013. On to better things...
AA: 85/15 WR: 2.7% SI: 2 pensions, SS later
|
|
|
09-02-2014, 08:18 PM
|
#13
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,670
|
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")))
|
|
|
09-02-2014, 08:27 PM
|
#14
|
Thinks s/he gets paid by the post
Join Date: Sep 2012
Posts: 1,568
|
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
__________________
You know that suit they burying you in? Thar ain’t no pockets in that suit, boy.
|
|
|
Google Sheets for portfolio tracking
09-02-2014, 08:31 PM
|
#15
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
Google Sheets for portfolio tracking
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.
__________________
|
|
|
09-02-2014, 08:41 PM
|
#16
|
Thinks s/he gets paid by the post
Join Date: Sep 2012
Posts: 1,568
|
Sheets, Doc, Drive, no luck.
Sent from my iPad using Early Retirement Forum
__________________
You know that suit they burying you in? Thar ain’t no pockets in that suit, boy.
|
|
|
09-02-2014, 08:44 PM
|
#17
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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 .
__________________
|
|
|
09-02-2014, 08:57 PM
|
#18
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,670
|
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
|
|
|
09-02-2014, 09:18 PM
|
#19
|
Thinks s/he gets paid by the post
Join Date: Sep 2012
Posts: 1,568
|
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 know that suit they burying you in? Thar ain’t no pockets in that suit, boy.
|
|
|
09-02-2014, 09:25 PM
|
#20
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
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?
__________________
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|