Google Sheets Bigcharts Quotes Not Working Again

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
Topic Author
Bimmer
Posts: 215
Joined: Tue Mar 06, 2007 9:33 pm

Google Sheets Bigcharts Quotes Not Working Again

Post by Bimmer »

My Google Sheet hasn’t been working for several weeks to pull stock or ETF quotes. I’m using the following to pull the quotes:

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quick ... ttype=Fund"), "table"),3,1)),6))

Any suggestions how to fix this or better option to reliably pull quotes? I honestly can’t believe that Google can’t fix this for their own functions to pull stock quotes reliably.

Thanks.
____________________________ | Bimmer | ____________________________
MadAsgardian
Posts: 89
Joined: Sun Mar 21, 2021 5:35 am

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by MadAsgardian »

It works for me in Google Sheets, just tested it. Note the forum is shortening the display of the link code due to length. Formula with complete link code is:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb=VTI&insttype=&freq=&show=%22,C16,%22&insttype=Fund"), "table"),3,1)),6))
PaunchyPirate
Posts: 1164
Joined: Sun Nov 30, 2014 6:58 pm

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by PaunchyPirate »

It works fine for me. I used the string above.
mv34547
Posts: 3
Joined: Wed Jan 27, 2021 1:07 pm

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by mv34547 »

That string works for me, too. It uses MW's quick chart instead of advanced chart. I don't know why scraping from advanced stopped working but switching to that quick chart formula works. thx
retiringwhen
Posts: 4743
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by retiringwhen »

Google is rate limiting importhtml calls (or their servers are overloaded and they are failing more.)

It will fail intermittently for no apparent reason on your side.

If you look at this thread, we have been discussing alternative solutions.

I have a google sheets implementation using Google App Script with caching that seems to improve performance overall. Here is a link to the latest version: Cached Ticker Tracking v0.05
Topic Author
Bimmer
Posts: 215
Joined: Tue Mar 06, 2007 9:33 pm

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by Bimmer »

MadAsgardian wrote: Thu Dec 09, 2021 5:08 am It works for me in Google Sheets, just tested it. Note the forum is shortening the display of the link code due to length. Formula with complete link code is:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb=VTI&insttype=&freq=&show=%22,C16,%22&insttype=Fund"), "table"),3,1)),6))
Thanks for all the replies. The string you posted is slightly different from what I had before. It seems to be working at least intermittently now for ETF's and stocks, but not for mutual funds.

Do you use a different string for mutual funds?

Thanks.

-Bimmer
____________________________ | Bimmer | ____________________________
Topic Author
Bimmer
Posts: 215
Joined: Tue Mar 06, 2007 9:33 pm

Re: Google Sheets Bigcharts Quotes Not Working Again

Post by Bimmer »

retiringwhen wrote: Thu Dec 09, 2021 8:40 am Google is rate limiting importhtml calls (or their servers are overloaded and they are failing more.)

It will fail intermittently for no apparent reason on your side.

If you look at this thread, we have been discussing alternative solutions.

I have a google sheets implementation using Google App Script with caching that seems to improve performance overall. Here is a link to the latest version: Cached Ticker Tracking v0.05
Thanks so much RetiringWhen. I copied your "BestTickers-noMS" to the bottom of my Net Worth spreadsheet and am using it to pull quotes. Seems to be working well for all types of investments - MF, ETF, Stock.

Thanks again.
____________________________ | Bimmer | ____________________________
Post Reply