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: 6669
Joined: Sun Jun 28, 2009 2:21 pm

Google sheets not pulling stock prices

Post by pshonore » Tue Mar 27, 2018 8:45 am

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: 96
Joined: Tue Dec 02, 2014 2:23 pm

Re: Google sheets not pulling stock prices

Post by Krischi » Tue Mar 27, 2018 8:46 am

Yes, seen the same issue yesterday/today.

alshayed
Posts: 62
Joined: Fri Aug 19, 2016 1:34 pm

Re: Google sheets not pulling stock prices

Post by alshayed » Tue Mar 27, 2018 8:47 am

I'm seeing the same thing this morning with BND

Horsefly
Posts: 521
Joined: Sat Oct 24, 2015 8:13 am
Location: Colorado, mostly

Re: Google sheets not pulling stock prices

Post by Horsefly » Tue Mar 27, 2018 8:58 am

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: 213
Joined: Wed Apr 10, 2013 12:59 pm
Location: A major metropolitan area

Re: Google sheets not pulling stock prices

Post by ivyhedge » Tue Mar 27, 2018 9:05 am

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: 57
Joined: Thu Jan 11, 2018 12:56 pm

Re: Google sheets not pulling stock prices

Post by BeautifulDisaster » Tue Mar 27, 2018 10:56 am

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: 306
Joined: Mon Jun 01, 2015 11:47 pm

Re: Google sheets not pulling stock prices

Post by anonsdca » Tue Mar 27, 2018 10:57 am

Yep, I see it too. Its very frustrating.

User avatar
Leif
Posts: 2755
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif » 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.

truenorth418
Posts: 452
Joined: Wed Dec 19, 2012 7:38 am

Re: Google sheets not pulling stock prices

Post by truenorth418 » Tue Mar 27, 2018 11:08 am

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: 306
Joined: Mon Jun 01, 2015 11:47 pm

Re: Google sheets not pulling stock prices

Post by anonsdca » Tue Mar 27, 2018 11:10 am

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: 452
Joined: Wed Dec 19, 2012 7:38 am

Re: Google sheets not pulling stock prices

Post by truenorth418 » Tue Mar 27, 2018 11:10 am

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: 6671
Joined: Wed Jan 11, 2017 8:05 pm

Re: Google sheets not pulling stock prices

Post by aristotelian » Tue Mar 27, 2018 11:13 am

The Hedge Fund Contest spreadsheet was broken yesterday. Seems to be fixed now.

MariaT
Posts: 172
Joined: Mon Feb 19, 2007 9:34 pm

Re: Google sheets not pulling stock prices

Post by MariaT » Tue Mar 27, 2018 11:14 am

Having same issues as well.

Not pulling quotes for VSS, SCHE, IEMG, VEU, BND. Hopefully it will correct itself.

User avatar
alpenglow
Posts: 863
Joined: Tue May 31, 2011 12:02 pm

Re: Google sheets not pulling stock prices

Post by alpenglow » Tue Mar 27, 2018 11:28 am

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

viewtopic.php?t=240332

bluebolt
Posts: 1042
Joined: Sat Jan 14, 2017 9:01 am

Re: Google sheets not pulling stock prices

Post by bluebolt » Tue Mar 27, 2018 1:38 pm

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: 2000
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 » Tue Mar 27, 2018 1:47 pm

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 contest results: 2019: #233 of 645 | 18: #150 of 493 | 17: #516 of 647 | 16: #121 of 610 | 15: #18 of 552 | 14: #225 of 503 | 13: #383 of 433 | 12: #366 of 410 | 11: #113 of 369 | 10: #53 of 282

User avatar
GreatOdinsRaven
Posts: 550
Joined: Thu Apr 23, 2015 8:47 pm

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

Post by GreatOdinsRaven » Tue Mar 27, 2018 6:29 pm

[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: 306
Joined: Mon Jun 01, 2015 11:47 pm

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

Post by anonsdca » Tue Mar 27, 2018 6:31 pm

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: 550
Joined: Thu Apr 23, 2015 8:47 pm

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

Post by GreatOdinsRaven » Tue Mar 27, 2018 6:43 pm

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: 802
Joined: Sun Jun 24, 2012 8:31 pm

Re: Google sheets not pulling stock prices

Post by jdilla1107 » Tue Mar 27, 2018 7:26 pm

It started working for me now after being broken for two days.

bluebolt
Posts: 1042
Joined: Sat Jan 14, 2017 9:01 am

Re: Google sheets not pulling stock prices

Post by bluebolt » Tue Mar 27, 2018 8:29 pm

Just started working for me also.

letsgobobby
Posts: 12074
Joined: Fri Sep 18, 2009 1:10 am

Re: Google sheets not pulling stock prices

Post by letsgobobby » Tue Mar 27, 2018 8:37 pm

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:37 pm, edited 1 time in total.

codmaxkor
Posts: 17
Joined: Tue Apr 10, 2018 8:55 pm

Re: Google sheets not pulling stock prices

Post by codmaxkor » 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))

User avatar
walletless
Posts: 946
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by walletless » 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']")
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!

User avatar
sperry8
Posts: 2000
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 » Wed Apr 18, 2018 9:41 am

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 contest results: 2019: #233 of 645 | 18: #150 of 493 | 17: #516 of 647 | 16: #121 of 610 | 15: #18 of 552 | 14: #225 of 503 | 13: #383 of 433 | 12: #366 of 410 | 11: #113 of 369 | 10: #53 of 282

User avatar
GreatOdinsRaven
Posts: 550
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven » 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
"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: 550
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven » Sat Jun 30, 2018 9:30 am

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: 650
Joined: Mon Jan 18, 2016 3:07 pm
Location: A Midwestern Town

Re: Google sheets not pulling stock prices

Post by BHUser27 » 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.

User avatar
GreatOdinsRaven
Posts: 550
Joined: Thu Apr 23, 2015 8:47 pm

Re: Google sheets not pulling stock prices

Post by GreatOdinsRaven » Mon Jul 02, 2018 10:12 pm

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: 474
Joined: Thu Dec 29, 2016 12:15 pm

Re: Google sheets not pulling stock prices

Post by zuma » 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?

typical.investor
Posts: 1359
Joined: Mon Jun 11, 2018 3:17 am

Re: Google sheets not pulling stock prices

Post by typical.investor » 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.

User avatar
Bylo Selhi
Posts: 1119
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Google sheets not pulling stock prices

Post by Bylo Selhi » 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.

typical.investor
Posts: 1359
Joined: Mon Jun 11, 2018 3:17 am

Re: Google sheets not pulling stock prices

Post by typical.investor » Sat Jul 28, 2018 8:00 pm

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: 4147
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker » 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?

User avatar
walletless
Posts: 946
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by walletless » Mon Jul 30, 2018 12:39 pm

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.

User avatar
munemaker
Posts: 4147
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker » Mon Jul 30, 2018 1:01 pm

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
walletless
Posts: 946
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by walletless » 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]")

nyclon
Posts: 353
Joined: Fri Oct 02, 2015 5:30 pm

Re: Google sheets not pulling stock prices

Post by nyclon » Thu Aug 02, 2018 10:37 am

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: 4147
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker » Thu Aug 02, 2018 11:15 am

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: 474
Joined: Thu Dec 29, 2016 12:15 pm

Re: Google sheets not pulling stock prices

Post by zuma » Tue Aug 07, 2018 5:37 am

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: 1119
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Google sheets not pulling stock prices

Post by Bylo Selhi » Wed Oct 10, 2018 8:23 am

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: 12074
Joined: Fri Sep 18, 2009 1:10 am

Re: Google sheets not pulling stock prices

Post by letsgobobby » Sun Oct 28, 2018 11:51 am

Deleted
Last edited by letsgobobby on Tue Apr 23, 2019 8:54 pm, edited 1 time in total.

User avatar
sperry8
Posts: 2000
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Google sheets not pulling stock prices

Post by sperry8 » Sun Nov 18, 2018 3:16 pm

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 contest results: 2019: #233 of 645 | 18: #150 of 493 | 17: #516 of 647 | 16: #121 of 610 | 15: #18 of 552 | 14: #225 of 503 | 13: #383 of 433 | 12: #366 of 410 | 11: #113 of 369 | 10: #53 of 282

User avatar
changingtimes
Posts: 241
Joined: Mon Jul 24, 2017 9:28 am

Re: Google sheets not pulling stock prices

Post by changingtimes » Tue Nov 20, 2018 9:43 pm

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: 46
Joined: Sat Nov 28, 2015 11:50 am

Re: Google sheets not pulling stock prices

Post by highdesert » Mon Dec 24, 2018 12:36 pm

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: 4147
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker » Mon Dec 24, 2018 7:38 pm

highdesert wrote:
Mon Dec 24, 2018 12:36 pm
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: 4147
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google sheets not pulling stock prices

Post by munemaker » Wed Dec 26, 2018 1:48 pm

munemaker wrote:
Mon Dec 24, 2018 7:38 pm
highdesert wrote:
Mon Dec 24, 2018 12:36 pm
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: 8179
Joined: Mon Feb 19, 2007 5:46 pm
Contact:

Re: Google sheets not pulling stock prices

Post by tfb » Wed Dec 26, 2018 2:34 pm

highdesert wrote:
Mon Dec 24, 2018 12:36 pm
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, taking a break from the forums.

highdesert
Posts: 46
Joined: Sat Nov 28, 2015 11:50 am

Re: Google sheets not pulling stock prices

Post by highdesert » Thu Dec 27, 2018 4:31 pm

tfb wrote:
Wed Dec 26, 2018 2: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: 2147
Joined: Sat Feb 18, 2012 11:36 am

Re: Google sheets not pulling stock prices

Post by hoppy08520 » Thu Feb 21, 2019 11:48 am

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