google spreadsheet function, 'GoogleFinance'

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Topic Author
vandizzle
Posts: 4
Joined: Tue May 01, 2012 9:09 pm

google spreadsheet function, 'GoogleFinance'

Post by vandizzle »

Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.

e.g.

Ticker, # of shares, and share price (I can load this dynamically via =GoogleFinance("VNQ"; "Price")), total holding amount

this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.

Anyone else have helpful spreadsheet functions?
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: google spreadsheet function, 'GoogleFinance'

Post by hoppy08520 »

Yes, a great utility. See http://support.google.com/docs/bin/answ ... ic/1360898 page for more uses. I like the "expenseratio" feature and the undocumented "name" feature which returns the fund name.
theCase
Posts: 16
Joined: Wed Jan 05, 2011 7:37 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by theCase »

I've been using the Google spreadsheet and it's "GoogleFinance" extensively to track my investments / plan my retirement to the point I am beginning to max out the spreadsheet size and number of functions. I switched to Google after problems with MSN Stock quotes stopped working for me (is this still broke???).

One thing I wish they had was a "Dividend" feature. (e.g. it would return .51 for KO). Alas it is not a perfect world....

In the mean time I've been using the Yahoo ImportData function, then doing a VLookup. Not pretty, but it works
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: google spreadsheet function, 'GoogleFinance'

Post by Cash »

vandizzle wrote: this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.
Same here. I no longer have to manually update the price info to see how far I'm straying from my AA; I just load the spreadsheet. Great feature, and I'll be happy when some of the other functions (like expenseratio) work with ETFs.
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: google spreadsheet function, 'GoogleFinance'

Post by SSSS »

hoppy08520 wrote:Yes, a great utility. See http://support.google.com/docs/bin/answ ... ic/1360898 page for more uses. I like the "expenseratio" feature and the undocumented "name" feature which returns the fund name.
I did not know about that "name" function. That is awesome. I just incorporated it into my spreadsheet and it works great.

My only wish is for a way to automatically update share quantity to account for reinvested distributions.
ataloss
Posts: 887
Joined: Tue Feb 20, 2007 2:24 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by ataloss »

Thanks for posting. I had been doing this manually in Excel!
User avatar
midareff
Posts: 7711
Joined: Mon Nov 29, 2010 9:43 am
Location: Biscayne Bay, South Florida

Re: google spreadsheet function, 'GoogleFinance'

Post by midareff »

I use M* portfolio tracker with the items I wish to track in the M*. I use two different views actually for this. I set up an excel spread sheet with one page for my master allocation and variation tracking using present price, last 12 months return and other items for my tracking purposes. In that excel work book is a page for each holding where I record buy/sell/re-invest, prices, nember or shares, etc. Current number of shares is imported from each pages holding into the allocation tracker page. I download the M* page, click enable editing and the master allocation page in my asset manager spread sheet is updated with present values, variation from target, etc., as well as anything else I would like to track. Click, click, done!.
ef11
Posts: 350
Joined: Sat Mar 10, 2012 9:39 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by ef11 »

I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
50% S&P 500 IDX ER .01% | 10% Ext Mkt ER .04% | 10% Small Cap Value ER .15% | 20% International TM ER .08% | 10% Vang Total Bond Market ER .03%
User avatar
Langkawi
Posts: 851
Joined: Sat Oct 09, 2010 1:25 am

Re: google spreadsheet function, 'GoogleFinance'

Post by Langkawi »

ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
They may be collective trusts:
http://thefinancebuff.com/collective-tr ... rence.html
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: google spreadsheet function, 'GoogleFinance'

Post by SSSS »

ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
Choose an equivalent fund or ETF and pretend you have an equivalent amount of that.

For example, let's say you have the following you your 401k:

$23,732.65 in BlackRock US Equity Market Index-F
$9,611.93 in BlackRock US Debt Index-T

BlackRock US Equity Market Index is reasonably identical to Vanguard Total Stock Market ETF (VTI), which closed at $70.28/share Friday -- $23,732.65 / $70.28 = 337.69 shares of VTI, so that's what you enter on your spreadsheet.

BlackRock US Debt Index is reasonably identical to Vanguard Total Bond Market ETF (BND), which closed at $83.93/share on Friday -- $9611.93/$89.93 = 106.88 share of BND.

This will yield very accurate results for a while, but will eventually star to drift out of sync (because of the different expense ratios, different distribution schedules, tracking error, etc) so you should re-calibrate at least once a year, preferably right before you do your regular account "maintenance" (i.e. rebalancing, yearly IRA contribution).

When doing this, make sure you're comparing apples to apples -- i.e. make sure all amounts above reflect Friday's closing prices; if you're dividing Thursday by Friday (i.e. because your 401k website is slow to update) you're going to have a bad time. You could do this during the week but it's more difficult. I recommend doing this Sunday or maybe early Monday morning, as you should be reasonably certain you're seeing Friday's closing data by that point.

If your non-401k portfolio consists mostly of ETFs, I'd suggest choosing ETFs for your equivalents as I did above. Even though your 401k holdings don't have published intraday values, this lets you see a reasonable approximation, and keeps your balance from being all wamperjawed if you check while the market is open. If you only hold traditional funds rather than ETFs, you'd be best off picking traditional funds for your equivalents.
User avatar
tfb
Posts: 8397
Joined: Mon Feb 19, 2007 4:46 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by tfb »

ataloss wrote:Thanks for posting. I had been doing this manually in Excel!
You can update prices in Excel with an add-in if you don't want to post your portfolio online. http://www.bogleheads.org/forum/viewtopic.php?t=75476
Harry Sit has left the forums.
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: google spreadsheet function, 'GoogleFinance'

Post by SSSS »

Here's a screenshot of my spreadsheet:

Image

Only the light yellow fields are editable, everything else is auto-calculated.

The "Variation" column has conditional formatting to show how far out of balance you are (yellow/orange/red).
porcupine
Posts: 1267
Joined: Thu Nov 04, 2010 11:05 am

Re: google spreadsheet function, 'GoogleFinance'

Post by porcupine »

vandizzle wrote:Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.
[...]
Anyone else have helpful spreadsheet functions?
Here is one :-)

- Porcupine
User avatar
Igglesman
Posts: 439
Joined: Fri May 01, 2009 2:20 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Igglesman »

The addiction of using "googlefinance" is that it allows me to hit just one button on my smartphone and instantaneously I can see the value of my total portfolio by using Google Documents.

This addition is worse than watching CNBC and the ups and downs of the market. Of course if you own any mutual funds you only get the thrill of one or two updated values a day.

In my google spreadsheet I have added graphics...so I can see my Bonds/Stocks ratio graphically (I know, big deal).

:wink: :wink: :wink:
User avatar
JamesSFO
Posts: 3404
Joined: Thu Apr 26, 2012 10:16 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by JamesSFO »

Very cool to know about. Also interesting to see how some folks have created some spreadsheets around this.
User avatar
Igglesman
Posts: 439
Joined: Fri May 01, 2009 2:20 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Igglesman »

Some additional Googlefinance features you may like...

=googlefinance(".dji","price") ---- looks up the current value of the Dow Jones Industrial Average

=googlefinance(".dji","change") ---- price change in DJIA

=googlefinance(".dji","changepct")/100 ---- and the percent change since last close of DJIA

Simlarly, you can add .INX for the S&P 500.

No more relying on other stock quote websites, I can display the data how I wish.
User avatar
tetractys
Posts: 6249
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Re: google spreadsheet function, 'GoogleFinance'

Post by tetractys »

Thanks for posting. I'm keeping an eye on things like this in anticipation of the future. Right now Google spreadsheets are undeveloped in many ways, and things still work fine in Quicken, so will see how it goes. -- Tet
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: google spreadsheet function, 'GoogleFinance'

Post by Cash »

SSSS wrote: The "Variation" column has conditional formatting to show how far out of balance you are (yellow/orange/red).
Similar to mine, except I do without columns I, L, M, N, and O.
frequentT
Posts: 184
Joined: Sat Jan 28, 2012 3:06 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by frequentT »

Google spreadsheets are wonderful and almost unlimited in what you can track and/or calculate. There are some master spreadsheet jockeys over on the M* forum if you are a member there that have some good 'public samples' that you can download and build off of.

If you are used to working in excel and writing formulas you can customize to your own liking.

Two features that I have written formulas for are stock dividends and bond interest to help me project annual income.

Good luck!
User avatar
NORDO
Posts: 155
Joined: Fri Apr 20, 2012 9:03 am

Re: google spreadsheet function, 'GoogleFinance'

Post by NORDO »

frequentT wrote:Google spreadsheets are wonderful and almost unlimited in what you can track and/or calculate. There are some master spreadsheet jockeys over on the M* forum if you are a member there that have some good 'public samples' that you can download and build off of.

If you are used to working in excel and writing formulas you can customize to your own liking.

Two features that I have written formulas for are stock dividends and bond interest to help me project annual income.

Good luck!
Although I don't use the 'GoogleFinance' function myself (since nearly all our investments are in our 401k's, neither of which employ any listed funds) I agree that Google Spreadsheets are incredibly helpful for tracking investments. I set ours up so I can simply copy/paste the current account balances from our 401k websites into a sheet and then it will extract all the pertinent information - share price, shares, balances, etc. - and give me an updated allocation across all our accounts. Very handy for rebalancing (I set up another worksheet to do this) across all the accounts, too.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

Have been using it in all of my portfolio management spreadsheets for some time.

I also have a spreadsheet that I use to get a market overview. First section is US stock ETFs, next international ETFs, then bond ETFs. I use ChangePct, with conditional formatting that shows different shades of blue (up) or red (down) depending on magnitude of percent change. I don't pay much attention to it unless there has been an extended up or down market, in which case I tend to rebalance on days when there are dark shades of red or blue.

Also, you can use a VG ETF to get an idea about the percent change in the corresponding VG mutual fund at the close. Probably not worth it to most people, but it can help in more precise rebalancing if you enter your mutual fund orders shortly before the close, especially on big up or down days. This was useful, for example, last year when the REIT fund dropped about 10% in one day. VNQ told me it was a day to add to VGSLX, and gave me a pretty good idea of how much to add. Of course you could do this without the GoogleFinance function, but it makes it convenient to work with multiple funds in a single spreadsheet.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

I thought some might be interested in seeing the "market quotes" spreadsheet I mentioned earlier.

Here are stock ETFs as of today:

Image

Note that you also can get TradeTime, and from that compute how old the quote is (I believe this is updated every minute in the spreadsheet, so the TradeTime could be up to a minute older than shown).

Note the slightly darker shade of blue for VGK (Europe). For the conditional formatting of the background color for stocks I use 1% bands, from < -4% to > 4%. You can only use ten conditions (last I checked).

I convert TradeTime to Pacific time with this formula: =GoogleFinance($B2,G$1)-1/8. I discovered that you convert TradeTime using the time zone difference in hours as a fraction of 24 hours, so 3 hours (PT vs ET) = 3/24 = 1/8.

The formula for "Age" is =(now())-G2.

Here are bond ETFs as of today:

Image

Since bonds are less volatile than stocks, for the conditional formatting of the background color I use 0.5% bands from < -2% to > 2%.

Note that you also can get historical quotes, and easily create a chart of the results (only part of the data is shown):

Image

The formula for historical quotes used here is: =googlefinance(A68,"close","01/01/12",today()). I added the historical ChgPct calculation.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
masrapido
Posts: 8
Joined: Mon May 07, 2012 2:23 pm
Location: Portland, OR

Re: google spreadsheet function, 'GoogleFinance'

Post by masrapido »

Does anyone know if this spreadsheet function uses the Google Finance API? This is important bc the API has been deprecated and will be removed completely in October:
https://developers.google.com/finance/

I couldn't find any mention of this in the spreadsheet function documentation. My guess is that they are still managing the database but are just pulling the cord on public access via the web API. I guess it's a good way to get people to use Google Docs instead of rolling their own portfolio management tool.

I started down the road of rolling my own tool using a python script and yahoo finance queries, but the hard part is determining/characterizing funds by their asset class/subclass. I think I'll try the spreadsheet. Thanks for the hint.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: google spreadsheet function, 'GoogleFinance'

Post by hoppy08520 »

masrapido wrote:Does anyone know if this spreadsheet function uses the Google Finance API? This is important bc the API has been deprecated and will be removed completely in October:
https://developers.google.com/finance/

I couldn't find any mention of this in the spreadsheet function documentation. My guess is that they are still managing the database but are just pulling the cord on public access via the web API. I guess it's a good way to get people to use Google Docs instead of rolling their own portfolio management tool.

I started down the road of rolling my own tool using a python script and yahoo finance queries, but the hard part is determining/characterizing funds by their asset class/subclass. I think I'll try the spreadsheet. Thanks for the hint.
I paniced after reading this, but googling around, I found the following which seems to suggest that the GoogleFinance() function in Google Spreadsheets will still be supported:

http://social.microsoft.com/Forums/en/m ... 628dc6ff96

From this linked page above:
Karolina Netolicka, Google Finance Product Manager wrote: we could serve more people better by integrating the data into other Google products rather than requiring them to write code to access the data. For example, check out the GoogleFinance() function in Google Spreadsheets which replicates some of the API's functionality without requiring you to write code.
masrapido
Posts: 8
Joined: Mon May 07, 2012 2:23 pm
Location: Portland, OR

Re: google spreadsheet function, 'GoogleFinance'

Post by masrapido »

Nice find, hoppy! :sharebeer As I suspected, it sounds like the Google Finance API is indeed alive and well behind the scenes. I'll stop writing code and give the Google finance spreadsheet a shot.
User avatar
LazyNihilist
Posts: 1005
Joined: Sat Feb 19, 2011 8:56 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by LazyNihilist »

I love GoogleFinance function and google spreadsheet. I use it to track investments and check when to rebalance.
The strong do what they can and the weak suffer what they must -Thucydides
User avatar
LazyNihilist
Posts: 1005
Joined: Sat Feb 19, 2011 8:56 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by LazyNihilist »

ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
As langkawi noted, they may be collective trusts and GoogleFinance only has publicly traded stocks/funds.

Here is an example of one of the 401k fund my company uses
https://institutional.vanguard.com/VGAp ... 1385059599
It has no ticker symbol, it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.

I use GoogleFinance for publicly traded funds, and for this fund, I do an automated screen scraper program to retrieve the value.
If your 401k is one of the Vanguard TR funds, I can give you the small python script to retrieve the value. :happy
The strong do what they can and the weak suffer what they must -Thucydides
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

LazyNihilist wrote:it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
Can't you just retrieve price for TR 2020 and multiply by the scaling factor that gives you the price of your fund?

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
LazyNihilist
Posts: 1005
Joined: Sat Feb 19, 2011 8:56 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by LazyNihilist »

Kevin M wrote:
LazyNihilist wrote:it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
Can't you just retrieve price for TR 2020 and multiply by the scaling factor that gives you the price of your fund?

Kevin
Yes that will work too, but sometimes you might be off by 1 or 2 cents. But we need to be absolutely accurate. :mrgreen:
The strong do what they can and the weak suffer what they must -Thucydides
papoono
Posts: 15
Joined: Sun Mar 29, 2009 1:02 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by papoono »

LazyNihilist wrote:
ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
As langkawi noted, they may be collective trusts and GoogleFinance only has publicly traded stocks/funds.

Here is an example of one of the 401k fund my company uses
https://institutional.vanguard.com/VGAp ... 1385059599
It has no ticker symbol, it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.

I use GoogleFinance for publicly traded funds, and for this fund, I do an automated screen scraper program to retrieve the value.
If your 401k is one of the Vanguard TR funds, I can give you the small python script to retrieve the value. :happy
I did a search in this thread and didn't see any mention of the Google "ImportHTML" function. I'm able to scrape the price from the link you quoted above, using the following Google function:

=Index(ImportHTML(ʺhttps://institutional.vanguard.com/VGApp/iip/site/institutional/investments/price?strategy=1385059599ʺ,ʺtableʺ,11),4,2)

Hope this helps others.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

papoono, I get a parse error. One problem I've seen with this approach in my one or two experiments with it in the past is that the web page changes, so arguments must be modified. I'm wondering if this is the case here. I can verify that the URL works.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
papoono
Posts: 15
Joined: Sun Mar 29, 2009 1:02 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by papoono »

Kevin M wrote:papoono, I get a parse error. One problem I've seen with this approach in my one or two experiments with it in the past is that the web page changes, so arguments must be modified. I'm wondering if this is the case here. I can verify that the URL works.

Kevin
Hi Kevin,

When you copy and paste the formula, do the double quotes get substituted with a 'box' character like this □ ? Try pasting the formula into Notepad and see if this is the case, then substitute the 'box' characters with double quotes. I reproduced your parse error if I just try to copy the formula "as-is" from the post.

And yes, this is a rather fragile way of retrieving the data. In fact I this used to use this method to retrieve fund prices from uii.nysaves.s.upromise.com, and this used to work, but is now broken :(
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

papoono wrote:When you copy and paste the formula, do the double quotes get substituted with a 'box' character like this □ ? Try pasting the formula into Notepad and see if this is the case, then substitute the 'box' characters with double quotes. I reproduced your parse error if I just try to copy the formula "as-is" from the post.(
Nope, I see quotes, whether pasting directly into google docs spreadsheet or notepad. No big deal; I like this idea, due to fragility, don't use it anywhere. Last time I tried it was to get bond or CD quotes from the Fidelity site, and I had to modify it to work, and it couldn't retrieve all the quotes I needed anyway.

Thanks for sharing though; it's good for us google docs users to know about these tricks.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
brad_g
Posts: 82
Joined: Sun May 15, 2011 5:14 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by brad_g »

tfb wrote:
ataloss wrote:Thanks for posting. I had been doing this manually in Excel!
You can update prices in Excel with an add-in if you don't want to post your portfolio online. http://www.bogleheads.org/forum/viewtopic.php?t=75476
No real need for an add-in as you can simply create a web-query in Excel and import just about anything you like. Personally, I keep a list of securities in Google Finance (a "portfolio") and import that into my excel spreadsheet (automatically). AA, performance, accounts, gains, etc, are all tracked and presented in Excel.

I prefer Excel as it's *much* more powerful than the Google app. Only downside though not a big one is the lack of cloud support. So I can't check my nw from my phone (shucks!).

Best,
Brad
User avatar
LazyNihilist
Posts: 1005
Joined: Sat Feb 19, 2011 8:56 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by LazyNihilist »

papoono wrote: I did a search in this thread and didn't see any mention of the Google "ImportHTML" function. I'm able to scrape the price from the link you quoted above, using the following Google function:

=Index(ImportHTML(ʺhttps://institutional.vanguard.com/VGApp/iip/site/institutional/investments/price?strategy=1385059599ʺ,ʺtableʺ,11),4,2)

Hope this helps others.

papoono,
This is great stuff. I didn't know I could do this from google docs. Brilliant. :sharebeer
The strong do what they can and the weak suffer what they must -Thucydides
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: google spreadsheet function, 'GoogleFinance'

Post by hoppy08520 »

Here's another use of GoogleFinance, if you want to return closing share price on a particular date. This is useful for computing your holdings on a particular date in time:

Code: Select all

=INDEX(GoogleFinance("VFINX", "close", "12/31/2011") ;2;2 )
Supposing you have a the date in a different cell, then you can reference the cell containing the date (assuming it's in cell B3):

Code: Select all

=INDEX(GoogleFinance("VFINX", "close", B3) ;2;2 )
And you'll commonly have the ticker symbol in a different cell as well (so you can see it), so in practice you'll use the function like this, assuming you have the ticker symbol in cell B2:

Code: Select all

=INDEX(GoogleFinance(B2 ,"close", B3) ;2;2 )
The reason you need to include the INDEX wrapper function around this is that without it, the function will expand into the neighboring cells like this:

Code: Select all

Date	           | Close
------------------------------
1/3/2012 16:00:0  | 117.59
Using the INDEX() wrapper with the ;2;2 arguments tells the INDEX function to return the value in the cell that is 2 over and 2 down, which is the actual closing price (117.59 in the example above).

Credit to this Google knowledgebase post that pointed this out to me.
User avatar
Igglesman
Posts: 439
Joined: Fri May 01, 2009 2:20 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Igglesman »

GoogleFinance Guru's... In the last 2 to 3 days, my lookup for Dow Jones Industrials ( .dji ) and S&P 500 ( .inx ) have stopped working.

All other lookups work fine. Did Google change the syntax? Anyone else having similar experience?

This is my cell contents for example:

=googlefinance(".dji","price")

Thanks.
papoono
Posts: 15
Joined: Sun Mar 29, 2009 1:02 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by papoono »

seems to be working fine for me. I used the same forumula as you

Code: Select all

=GoogleFinance(".DJI","price")
=GoogleFinance(".INX","price")
for 8/1/2012 the values are 12,976.13 and 1,375.14 respectively
User avatar
Igglesman
Posts: 439
Joined: Fri May 01, 2009 2:20 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Igglesman »

papoono wrote:seems to be working fine for me. I used the same forumula as you

Code: Select all

=GoogleFinance(".DJI","price")
=GoogleFinance(".INX","price")
for 8/1/2012 the values are 12,976.13 and 1,375.14 respectively
Thanks pappono for the reply. Interesting, if I type the one formula into a new worksheet, it works.
If I add the formula to an open cell on my existing worksheet, it does not work.
Reminder, this spreadsheet has worked for me daily for at least 2 years...on PC, Phone and Tablet never with a problem.

I use Chrome, but tried it on Firefox..still does not work. I also confirmed I get the same error on Android.

I did find a thread on this problem from two days ago on the Google Forums, with no resolution...but many people having same experience. Weird.

Thanks again for your help.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: google spreadsheet function, 'GoogleFinance'

Post by hoppy08520 »

FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
User avatar
JamesSFO
Posts: 3404
Joined: Thu Apr 26, 2012 10:16 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by JamesSFO »

Igglesman wrote: Thanks pappono for the reply. Interesting, if I type the one formula into a new worksheet, it works.
If I add the formula to an open cell on my existing worksheet, it does not work.
Reminder, this spreadsheet has worked for me daily for at least 2 years...on PC, Phone and Tablet never with a problem.

I use Chrome, but tried it on Firefox..still does not work. I also confirmed I get the same error on Android.

I did find a thread on this problem from two days ago on the Google Forums, with no resolution...but many people having same experience. Weird.

Thanks again for your help.
There's a limit on the number of calls to GoogleFinance you can make per spreadsheet, might you be hitting that limit?
rustymutt
Posts: 4001
Joined: Sat Mar 07, 2009 11:03 am

Re: google spreadsheet function, 'GoogleFinance'

Post by rustymutt »

vandizzle wrote:Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.

e.g.

Ticker, # of shares, and share price (I can load this dynamically via =GoogleFinance("VNQ"; "Price")), total holding amount

this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.

Anyone else have helpful spreadsheet functions?
I use getquote("VNQ") with OpenSource, in the same way. If you have your dividends reinvested into your cash account, you don't have to update shares quarterly, and have some cash for re-balancing available.
Even educators need education. And some can be hard headed to the point of needing time out.
rustymutt
Posts: 4001
Joined: Sat Mar 07, 2009 11:03 am

Re: google spreadsheet function, 'GoogleFinance'

Post by rustymutt »

hoppy08520 wrote:FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
Google finance still shows RZV as a Rydex fund, rather than Guggenheim fund.
Even educators need education. And some can be hard headed to the point of needing time out.
User avatar
Igglesman
Posts: 439
Joined: Fri May 01, 2009 2:20 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Igglesman »

JamesSFO wrote:
Igglesman wrote:
There's a limit on the number of calls to GoogleFinance you can make per spreadsheet, might you be hitting that limit?
This is possible...if they changed the limit in the last week... all the calls I do make to my index funds still work...so unless it is calls to "the averages" -- dow jones, S&P -- I don't think that is the answer.

I am following a google forums thread on this subject, I am not the only one experiencing this problem. It has been escalated to the "engineers".

Thanks for the help.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: google spreadsheet function, 'GoogleFinance'

Post by hoppy08520 »

rustymutt wrote:
hoppy08520 wrote:FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
Google finance still shows RZV as a Rydex fund, rather than Guggenheim fund.
I think that's probably because some of the databases from which GoogleFinance pulls data are outdated. The names of those funds changed just a few months ago.

http://www.indexuniverse.com/sections/n ... name-.html

I hope that at least the "price" is correct.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

Igglesman wrote:GoogleFinance Guru's... In the last 2 to 3 days, my lookup for Dow Jones Industrials ( .dji ) and S&P 500 ( .inx ) have stopped working.

All other lookups work fine. Did Google change the syntax? Anyone else having similar experience?

This is my cell contents for example:

=googlefinance(".dji","price")

Thanks.
Having the same problem In my Market Quotes spreadsheet. Attributes for all ETFs are being returned, but getting #N/A for .INX attributes. Attributes for SPY still work fine, so at least you can get a good estimate of percent change for the S&P 500 if you're interested in that.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Kevin M
Posts: 15789
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: google spreadsheet function, 'GoogleFinance'

Post by Kevin M »

GoogleFinance once again is returning data for .INX (S&P 500 Index).

Kevin
If I make a calculation error, #Cruncher probably will let me know.
Blue Jay
Posts: 4
Joined: Mon Jul 07, 2014 2:12 pm

Re: google spreadsheet function, 'GoogleFinance'

Post by Blue Jay »

It bothered me that GoogleFinance doesn't work with ETF for expenseratio, so I spent some time playing with importHTML to get this. Here's a universal expense ratio function that works for both funds and etfs. Hope others will find this useful ;-)

Code: Select all

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A2), "table", 1),1,13), " "), 1, 2)
A2 is the cell of the ticker e.g. VTI.
User avatar
sperry8
Posts: 3065
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: google spreadsheet function, 'GoogleFinance'

Post by sperry8 »

I've been using this for years in my personal spreadsheets for prices (so I can see my net returns). Just saw that I am able to pull in additional data, such as "expenseratio". But when I use this it shows as 62% rather than .62% for example.

I am typing:
=GoogleFinance ("POAGX", "expenseratio")

The cell shows .62 but when I click format, number, percent - it shows as 62%, not .62%.

Anyone know how to "fix"?
Blue Jay wrote:It bothered me that GoogleFinance doesn't work with ETF for expenseratio, so I spent some time playing with importHTML to get this. Here's a universal expense ratio function that works for both funds and etfs. Hope others will find this useful ;-)

Code: Select all

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A2), "table", 1),1,13), " "), 1, 2)
A2 is the cell of the ticker e.g. VTI.
and btw, this works for expense ratios. However the same issue occurs for all other fields (like returns, etc.) Would be nice to just have the direct Google Finance fix - to show properly.
BH Contests: 23 #89 of 607 | 22 #512 of 674 | 21 #66 of 636 |20 #253/664 |19 #233/645 |18 #150/493 |17 #516/647 |16 #121/610 |15 #18/552 |14 #225/503 |13 #383/433 |12 #366/410 |11 #113/369 |10 #53/282
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: google spreadsheet function, 'GoogleFinance'

Post by SSSS »

sperry8 wrote:I am typing:
=GoogleFinance ("POAGX", "expenseratio")

The cell shows .62 but when I click format, number, percent - it shows as 62%, not .62%.
The raw data the function returns is just "0.62" with no context specified, as the function only returns a numeric value, it has no way to directly specify that "0.62" means "0.62%"; perhaps they should have programmed the function to return "0.0062" instead, but they didn't. If you tell the spreadsheet to force formatting to "percentage", then displaying 0.62 as 62% is correct behavior.

You have at least a couple options to display it the way you want to:

1. Leave default formatting and modify your formula to concatenate a % to the end: =concatenate((googlefinance(B20,"expenseratio")),"%")

2. Divide by 100 so that forced formatting percentage will work as you want it to: =(googlefinance("POAGX","expenseratio"))/100
Post Reply