Google sheets not pulling stock prices

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
pshonore
Posts: 8212
Joined: Sun Jun 28, 2009 2:21 pm

Google sheets not pulling stock prices

Post by pshonore »

I've been using Google sheets to get prices since the Yahoo functions stopped working. Noticed a problem yesterday that it no longer pulls prices for selected symbols (BIV,EDV,VT, etc). Still works ok for VTI, LQD, etc. Its pretty simple - the Google finance functions with "price" parameter. Instead of the price it returns "#N/A"

As an example: =(Googlefinance(B56),"price") where cell B56 contains BIV

Anyone else notice this problem?
Krischi
Posts: 101
Joined: Tue Dec 02, 2014 1:23 pm

Re: Google sheets not pulling stock prices

Post by Krischi »

Yes, seen the same issue yesterday/today.
alshayed
Posts: 76
Joined: Fri Aug 19, 2016 1:34 pm

Re: Google sheets not pulling stock prices

Post by alshayed »

I'm seeing the same thing this morning with BND
Horsefly
Posts: 702
Joined: Sat Oct 24, 2015 8:13 am
Location: Colorado, mostly

Re: Google sheets not pulling stock prices

Post by Horsefly »

It seems to be not working on some subset of tickers. FUSVX and IVV work fine, as do individual stocks (GOOG and AMZN), but BND doesn't. I would say it is only Vanguard stuff that fails, but VTI works fine.
User avatar
ivyhedge
Posts: 258
Joined: Wed Apr 10, 2013 12:59 pm
Location: A Town of Beans with only one hill remaining

Re: Google sheets not pulling stock prices

Post by ivyhedge »

No quotations for Vanguard: VSS, VIOV; none for non Vanguard: FBT, MLPI, RYT, SUB, PUTW.

All others fine, including ETFs and individual equities.

Time: 1005hrs.
Polymath.
BeautifulDisaster
Posts: 61
Joined: Thu Jan 11, 2018 11:56 am

Re: Google sheets not pulling stock prices

Post by BeautifulDisaster »

I didn't even know this was a feature on Google Sheets!

Do you mind sharing a blank template here so I can play around with it?
anonsdca
Posts: 480
Joined: Mon Jun 01, 2015 11:47 pm

Re: Google sheets not pulling stock prices

Post by anonsdca »

Yep, I see it too. Its very frustrating.
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif »

Pulled prices this morning. No issues. I pulled yesterday's closing prices. Looks like from the posts the issue is with ETFs. Mine are mutual funds, except for VTIP.
truenorth418
Posts: 637
Joined: Wed Dec 19, 2012 6:38 am

Re: Google sheets not pulling stock prices

Post by truenorth418 »

It's not pulling SCHA nor VSS for me.

Sounds like it is a widespread problem, hopefully the delicate geniuses over there will fix it soon.
anonsdca
Posts: 480
Joined: Mon Jun 01, 2015 11:47 pm

Re: Google sheets not pulling stock prices

Post by anonsdca »

Leif wrote: Tue Mar 27, 2018 11:02 am Pulled prices this morning. No issues. I pulled yesterday's closing prices. Looks like from the posts the issue is with ETFs. Mine are mutual funds, except for VTIP.
No, it is for various investments, not just ETFs. I have several tickers it is not pulling. Very frustrating, especially since I use my Google Doc almost as my brokerage account.
truenorth418
Posts: 637
Joined: Wed Dec 19, 2012 6:38 am

Re: Google sheets not pulling stock prices

Post by truenorth418 »

Here is the function I use to pull quotes and calculate current values in real time, using VTI as an example..

=googlefinance("vti","price")*M9

..where M9 is the cell location with the number of shares
aristotelian
Posts: 12277
Joined: Wed Jan 11, 2017 7:05 pm

Re: Google sheets not pulling stock prices

Post by aristotelian »

The Hedge Fund Contest spreadsheet was broken yesterday. Seems to be fixed now.
MariaT
Posts: 226
Joined: Mon Feb 19, 2007 8:34 pm

Re: Google sheets not pulling stock prices

Post by MariaT »

Having same issues as well.

Not pulling quotes for VSS, SCHE, IEMG, VEU, BND. Hopefully it will correct itself.
User avatar
alpenglow
Posts: 1804
Joined: Tue May 31, 2011 12:02 pm

Re: Google sheets not pulling stock prices

Post by alpenglow »

I love google sheets but the finance function isn't cutting it these days. Try this solution!

viewtopic.php?t=240332
bluebolt
Posts: 2137
Joined: Sat Jan 14, 2017 8:01 am

Re: Google sheets not pulling stock prices

Post by bluebolt »

If you head to the Google issue tracker for this issue, you can click on the star in the upper left to "vote" for this issue.

You will also get updates when there are changes/posts.

https://issuetracker.google.com/u/1/iss ... 3135?pli=1
User avatar
sperry8
Posts: 3065
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 »

Not working for me either. VOE isn't pulling but many Vanguard ETFs are (VTV and VBR are working). A variety of others aren't working too - and yet some others are. Let's get on the ball Google! :P

Here is another place to complain: https://productforums.google.com/forum/ ... iK_9xBBAAJ
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
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

google finance no longer able to query some tickers in Google Sheets

Post by GreatOdinsRaven »

[Thread merged into here, see below (next page). --admin LadyGeek]

I've been using Google Sheets for some time to monitor my portfolio. Approximately two days ago, it stopped being able to query a few of the tickers that I own (a minority of them). The vast majority of my tickers are still working.

This is what used to work in google sheets:
=(Googlefinance("VOO", "closeYest"))

This is the error I'm receiving in google sheets:
Error
When evaluating GOOGLEFINANCE, the query for the symbol: 'VOO' returned no data.

I'm getting the same errors for VEA, VEU, MTUM, VMMXX, VSS and IEMG.

If you substitute any other ticker such as VTI, brk.b etc it still works.

Any suggestions on what's wrong and how I can contact google to let them know about this problem?

Thank you,
GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
anonsdca
Posts: 480
Joined: Mon Jun 01, 2015 11:47 pm

Re: google finance no longer able to query some tickers in Google Sheets

Post by anonsdca »

GreatOdinsRaven wrote: Tue Mar 27, 2018 6:29 pm I've been using Google Sheets for some time to monitor my portfolio. Approximately two days ago, it stopped being able to query a few of the tickers that I own (a minority of them). The vast majority of my tickers are still working.

This is what used to work in google sheets:
=(Googlefinance("VOO", "closeYest"))

This is the error I'm receiving in google sheets:
Error
When evaluating GOOGLEFINANCE, the query for the symbol: 'VOO' returned no data.

I'm getting the same errors for VEA, VEU, MTUM, VMMXX, VSS and IEMG.

If you substitute any other ticker such as VTI, brk.b etc it still works.

Any suggestions on what's wrong and how I can contact google to let them know about this problem?

Thank you,
GOR
Already a thread on this

viewtopic.php?f=2&t=245411&newpost=3850 ... ead#unread
User avatar
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: google finance no longer able to query some tickers in Google Sheets

Post by GreatOdinsRaven »

Thanks. I did a google search before posting and unfortunately the pertinent thread didn't appear.

Maybe lady geek will merge my post with the current thread.
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
jdilla1107
Posts: 849
Joined: Sun Jun 24, 2012 8:31 pm

Re: Google sheets not pulling stock prices

Post by jdilla1107 »

It started working for me now after being broken for two days.
bluebolt
Posts: 2137
Joined: Sat Jan 14, 2017 8:01 am

Re: Google sheets not pulling stock prices

Post by bluebolt »

Just started working for me also.
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Google sheets not pulling stock prices

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:37 pm, edited 1 time in total.
codmaxkor
Posts: 19
Joined: Tue Apr 10, 2018 8:55 pm

Re: Google sheets not pulling stock prices

Post by codmaxkor »

Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
User avatar
fetch5482
Posts: 1722
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by fetch5482 »

codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
You can also use GOOGLEFINANCE as your backup api if the Morningstar call errors:

Code: Select all

=IFERROR(IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']"), GOOGLEFINANCE(A2))
(Assumes A2 cell has the ticker symbols of choice)

Now if someone can help convert that Morningstar call into appscript code, I'd be very thankful!
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
User avatar
sperry8
Posts: 3065
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 »

Is there a way to pull in 7 or 30 day SEC Yield into a google spreadsheet? For example, it's shown here for Vanguard Prime MM (VMMXX) as 1.78% (a/o today) https://personal.vanguard.com/us/funds/ ... irect=true

I perused this (https://support.google.com/docs/answer/3093281?hl=en) but only see 12 mo yield "yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.

Perhaps via BigCharts or elsewhere?

I'm trying to pull in Yield from Vanguard Prime MM (VMMXX) and Vanguard Municipal MM (VMSXX) so I can monitor them and ensure I'm always in the right one.
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
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven »

I've had similar issues with google sheets not being able to pull quotes for ETFs and mutual funds over the past year and a half or so. Usually, I'll report the issue via the Google Issue Tracker and it will be repaired within a week. Most recently (about two weeks ago) I noticed that Google Sheets/Google Finance was no longer pulling the data for Vanguard Prime Money Market (VMMXX) and Vanguard Federal Money Market (VMFXX). I reported this issue, but they have not repaired it and it's wrecking my spreadsheet. Does anyone know how to escalate this issue or report it in a way that will receive attention? I keep receiving email notifications from Google that issues reported long after I reported my problem have been solved. Here's the formula I'm using:

Code: Select all

=Shares!$E$23*(Googlefinance("VMMXX", "closeYest"))
I'd prefer not to abandon Google Finance for this issue, but in the event that I have to do so, does anyone have an alternative and an explanation of how would I incorporate it into my spreadsheet?

Thank you

GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
User avatar
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven »

GreatOdinsRaven wrote: Sat Jun 30, 2018 9:04 am I've had similar issues with google sheets not being able to pull quotes for ETFs and mutual funds over the past year and a half or so. Usually, I'll report the issue via the Google Issue Tracker and it will be repaired within a week. Most recently (about two weeks ago) I noticed that Google Sheets/Google Finance was no longer pulling the data for Vanguard Prime Money Market (VMMXX) and Vanguard Federal Money Market (VMFXX). I reported this issue, but they have not repaired it and it's wrecking my spreadsheet. Does anyone know how to escalate this issue or report it in a way that will receive attention? I keep receiving email notifications from Google that issues reported long after I reported my problem have been solved. Here's the formula I'm using:

Code: Select all

=Shares!$E$23*(Googlefinance("VMMXX", "closeYest"))
I'd prefer not to abandon Google Finance for this issue, but in the event that I have to do so, does anyone have an alternative and an explanation of how would I incorporate it into my spreadsheet?

Thank you

GOR
For example, I use my Google Sheets spreadsheet to scrape expense ratio data from Morningstar for all of my funds and then I calculate my portfolio weighted expense ratio. I can't recall how I ever figured out how to do this...

I'm using the following formula:

Code: Select all

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", C23), "table", 1),1,13), " "), 1, 2)*100


Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
BHUser27
Posts: 655
Joined: Mon Jan 18, 2016 2:07 pm
Location: A Midwestern Town

Re: Google sheets not pulling stock prices

Post by BHUser27 »

GreatOdinsRaven wrote: Sat Jun 30, 2018 9:30 am Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?
The URL to the data you need is:

Code: Select all

http://quotes.morningstar.com/fund/c-header?t=VMMXX
However, I haven't figured out how to parse out the Nav Price using index & split functions.
User avatar
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven »

BHUser27 wrote: Sun Jul 01, 2018 8:11 am
GreatOdinsRaven wrote: Sat Jun 30, 2018 9:30 am Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?
The URL to the data you need is:

Code: Select all

http://quotes.morningstar.com/fund/c-header?t=VMMXX
However, I haven't figured out how to parse out the Nav Price using index & split functions.
For the benefit of anyone reading this in the future. I found a work-around that uses M* instead of Google Finance:

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", "VMMXX"), "table", 1),1,1), " "), 1, 2)

The "1" in bold, italics and underlined is the key. This is the NAV price as of the close of the market the day prior. For a money market fund the value should be 1.

My formula pulls the number of shares and multiplies it by the NAV. The actual formula is:

Code: Select all

=Shares!$E$23*(INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", "VMMXX"), "table", 1),1,1), " "), 1, 2))
Where Shares!$E$23 is the name of the sheet and the cell I'm using to list shares owned. I use this method (which seems redundant), because I update my shares on one sheet for all funds owned. This makes it easy to see what and how much I own across the board, rather than having to update individual cells elsewhere in the spreadsheet. It also helps to calculate the portfolio weighted expense ratio, in another field, elsewhere, by referencing back.

In a prior post, above, the value I was using was "13". "13" pulls the expense ratio.

GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
zuma
Posts: 641
Joined: Thu Dec 29, 2016 11:15 am

Re: Google sheets not pulling stock prices

Post by zuma »

My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
User avatar
typical.investor
Posts: 5263
Joined: Mon Jun 11, 2018 3:17 am

Re: Google sheets not pulling stock prices

Post by typical.investor »

zuma wrote: Sat Jul 28, 2018 2:20 am My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
Did Total Bond switch over to NASDAQ? There were posts recently saying so.

If BND was part of that, I'd try:

Code: Select all

=GOOGLEFINANCE("NASDAQ:BND","price")
It's not working now so maybe it's not correct or maybe the listing isn't coming through yet.
User avatar
Bylo Selhi
Posts: 1310
Joined: Mon Feb 19, 2007 9:40 pm
Location: Great White North
Contact:

Re: Google sheets not pulling stock prices

Post by Bylo Selhi »

zuma wrote: Sat Jul 28, 2018 2:20 am My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
Have you experimented with Morningstar using the IMPORTXML function as @walletless describes upthread in viewtopic.php?p=3875022#p3875022 ?

Google's quotes source stopped working for me about a month ago with some Canadian securities. I was able to get those quotes from M* instead using that technique.
User avatar
typical.investor
Posts: 5263
Joined: Mon Jun 11, 2018 3:17 am

Re: Google sheets not pulling stock prices

Post by typical.investor »

typical.investor wrote: Sat Jul 28, 2018 2:38 am
zuma wrote: Sat Jul 28, 2018 2:20 am My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
Did Total Bond switch over to NASDAQ? There were posts recently saying so.

If BND was part of that, I'd try:

Code: Select all

=GOOGLEFINANCE("NASDAQ:BND","price")
It's not working now so maybe it's not correct or maybe the listing isn't coming through yet.
It's working now.

79.01
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker »

walletless wrote: Wed Apr 11, 2018 2:47 am
codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
...
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?
User avatar
fetch5482
Posts: 1722
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by fetch5482 »

munemaker wrote: Mon Jul 30, 2018 9:51 am
walletless wrote: Wed Apr 11, 2018 2:47 am
codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
...
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?
sure, i am out today and tomm. will try on wed.
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker »

walletless wrote: Mon Jul 30, 2018 12:39 pm
sure, i am out today and tomm. will try on wed.
Thanks! Much appreciated.
User avatar
fetch5482
Posts: 1722
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by fetch5482 »

munemaker wrote: Mon Jul 30, 2018 9:51 am
walletless wrote: Wed Apr 11, 2018 2:47 am
codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
...
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?
Try this for daily percent change:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
If your ticker symbol (VBTIX in this case) is in cell A2, you can do:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
nyclon
Posts: 664
Joined: Fri Oct 02, 2015 5:30 pm

Re: Google sheets not pulling stock prices

Post by nyclon »

I noticed yesterday that google finance no longer shows the s&p 500 or s&p 500 total return indices. Anyone else notice this?
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker »

walletless wrote: Thu Aug 02, 2018 2:44 am
munemaker wrote: Mon Jul 30, 2018 9:51 am
walletless wrote: Wed Apr 11, 2018 2:47 am
codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
...
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?
Try this for daily percent change:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
If your ticker symbol (VBTIX in this case) is in cell A2, you can do:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
Wow! Thank you for doing this! I appreciate it.
zuma
Posts: 641
Joined: Thu Dec 29, 2016 11:15 am

Re: Google sheets not pulling stock prices

Post by zuma »

Bylo Selhi wrote: Sat Jul 28, 2018 9:41 am
zuma wrote: Sat Jul 28, 2018 2:20 am My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
Have you experimented with Morningstar using the IMPORTXML function as @walletless describes upthread in viewtopic.php?p=3875022#p3875022 ?

Google's quotes source stopped working for me about a month ago with some Canadian securities. I was able to get those quotes from M* instead using that technique.
My Google sheet is back to normal. BND is now being shown correctly as Vanguard Total Bond Market ETF.
User avatar
Bylo Selhi
Posts: 1310
Joined: Mon Feb 19, 2007 9:40 pm
Location: Great White North
Contact:

Re: Google sheets not pulling stock prices

Post by Bylo Selhi »

Here's Google's new documentation/help for their GOOGLEFINANCE function. This may help explain why some exchanges and security symbols are no longer recognized. It also provides a nice summary of all the security attributes that this function can report.
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Google sheets not pulling stock prices

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Tue Apr 23, 2019 8:54 pm, edited 1 time in total.
User avatar
sperry8
Posts: 3065
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 »

walletless wrote: Thu Aug 02, 2018 2:44 am
munemaker wrote: Mon Jul 30, 2018 9:51 am
walletless wrote: Wed Apr 11, 2018 2:47 am
codmaxkor wrote: Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
...
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?
Try this for daily percent change:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
If your ticker symbol (VBTIX in this case) is in cell A2, you can do:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
Do you know how to use your morningstar code to pull in 1 year returns for ETFs?
This code works for mutual funds (B10 represents a cell with the fund name inside it), but doesn't work for ETFs.

Code: Select all

=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:",B10,"&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&alig n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[6]")
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
changingtimes
Posts: 483
Joined: Mon Jul 24, 2017 9:28 am

Re: Google sheets not pulling stock prices

Post by changingtimes »

Just wanted to say thanks for this thread. Google stopping the 6 pm NAV updates for mutual funds has driven me nuts.

I went with this for the prices:

Code: Select all

=VALUE(Right((Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&B3&"", "table"),3,1)),7)) 
And I used this for the last reported date:

Code: Select all

=Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&B3&"", "table"),1,6)
highdesert
Posts: 77
Joined: Sat Nov 28, 2015 10:50 am

Re: Google sheets not pulling stock prices

Post by highdesert »

I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?

Thanks in advance for any suggestions.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker »

highdesert wrote: Mon Dec 24, 2018 11:36 am I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?

Thanks in advance for any suggestions.
I am scraping the fund prices from Marketwatch and today most of the Fidelity fund prices I import did not load. I am using Google Finance for individual stock prices and they all loaded correctly. Overall, the Marketwatch imports have been working well until today (maybe a small glitch yesterday), so I am not overly concerned YET.

Sorry I do not have any suggestions. I am not going to put any time into a fix for a couple days, to see if it rights itself.

UPDATE: At 8:15 pm, I noticed that the Fidelity Funds that I actually own had updated. I track others that I don't own, and many of those had still not updated. At least the ones I need to calculate my totals updated and I am ok with that.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker »

munemaker wrote: Mon Dec 24, 2018 6:38 pm
highdesert wrote: Mon Dec 24, 2018 11:36 am I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?

Thanks in advance for any suggestions.
I am scraping the fund prices from Marketwatch and today most of the Fidelity fund prices I import did not load. I am using Google Finance for individual stock prices and they all loaded correctly. Overall, the Marketwatch imports have been working well until today (maybe a small glitch yesterday), so I am not overly concerned YET.

Sorry I do not have any suggestions. I am not going to put any time into a fix for a couple days, to see if it rights itself.

UPDATE: At 8:15 pm, I noticed that the Fidelity Funds that I actually own had updated. I track others that I don't own, and many of those had still not updated. At least the ones I need to calculate my totals updated and I am ok with that.
Everything on mine is working fine today. No problems.
User avatar
tfb
Posts: 8397
Joined: Mon Feb 19, 2007 4:46 pm

Re: Google sheets not pulling stock prices

Post by tfb »

highdesert wrote: Mon Dec 24, 2018 11:36 am The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX).
FUSVX doesn't trade any more. The new ticker symbol is FXAIX. You can find out the new ticker symbol for the ones that consolidated into others by entering the old symbol to the search box on the top right at fidelity.com.
Harry Sit has left the forums.
highdesert
Posts: 77
Joined: Sat Nov 28, 2015 10:50 am

Re: Google sheets not pulling stock prices

Post by highdesert »

tfb wrote: Wed Dec 26, 2018 1:34 pm FUSVX doesn't trade any more. The new ticker symbol is FXAIX. ...
Thanks, that was it -- the price functions are fine, it's just that all of my ticker symbols changed.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: Google sheets not pulling stock prices

Post by hoppy08520 »

Update to this older thread. I couldn't get my new Vanguard International Small Cap Admiral ticker VFSAX to be recognized by GoogleFinance or BigCharts.Marketwatch because the fund is so new, so I figured out another way to get realtime prices with the ImportXML function from the Bloomberg stock page:

Code: Select all

=importxml("https://www.bloomberg.com/quote/VFSAX:US", "//span[@class='priceText__1853e8a5']")
You'll probably be referencing a ticker symbol in another cell, so suppose that cell is A2, then your formula is:

Code: Select all

=importxml("https://www.bloomberg.com/quote/" & A2 & ":US", "//span[@class='priceText__1853e8a5']")
I tried to do this from Morningstar but I couldn't get any XPATH to work, but I could with Bloomberg.

I must say that I'm wary of depending on finding the NAV from a class with such a strange name (priceText__1853e8a5) (at first I thought it was dynamically generated to deter webscrapers) but it has been working for 3+ days now, both for mutual funds and ETFs.

An even more advanced tip is that my "Price" cell in my spreadsheet tries 3 different sites: first BigCharts.MarketWatch, then Bloomberg, and then GoogleFinance. If BigCharts.Marketwatch works, take it; if that fails, try Bloomberg; if that fails, try GoogleFinance. The syntax to do this is with nested IFERROR functions. Assuming the Ticker is in cell A2:

Code: Select all

=IFERROR(VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",A2,"&insttype=Fund"), "table"),3,1)),7)), iferror(importxml("https://www.bloomberg.com/quote/"&A2&":US", "//span[@class='priceText__1853e8a5']"), GoogleFinance(A2, "price")))
Post Reply