Google sheets not pulling stock prices

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
zincTwo
Posts: 510
Joined: Tue Oct 30, 2018 11:31 pm
Location: California

Re: Google Sheets' googlefinance function not working

Post by zincTwo »

stormcrow wrote: Mon Mar 15, 2021 7:28 am
hoppy08520 wrote: Fri Mar 12, 2021 10:02 am
But my spreadsheet still works because I added 3 (!) backup services to get prices. If any one service is down, then the other services get used. See my earlier comment in this thread.
Oh wow, this is impressive, thank you!
+1 Very instructive thread. I've found GoogleFinance to be the most convenient, but not always the most accurate. Google MF prices are delayed much longer than doing a scrape from Morningstar site. But GF is generally accurate within a 24 hr window - I'm learning to be content with that. Expense Ratios seem to take longer to update whenever they've changed (sometimes months). The "TICKERs vlookup" method seems to provide much value. I can mix & match for ETFs and MFs, and also easily determine if somebody is "off".

Thank you.
User avatar
Stef
Posts: 1247
Joined: Thu Oct 10, 2019 10:13 am

Re: Google sheets not pulling stock prices

Post by Stef »

stocknoob4111 wrote: Fri Mar 12, 2021 11:17 am I never use Google Finance since their quotes are horribly delayed. I have a custom function that screenscrapes off the MarketWatch site, in my experience MW publishes prices the fastest. Also there are rendering issues with Google Sheets that sometimes get stuck in a "Loading..." state, I fixed that by using a custom trigger on open (default OPEN trigger can't execute HTTP requests) and putting values in a separate sheet then doing a VLOOKUP into that dataset, now it works 100% of the time reliably
It's delayed by 5-15min, is that so bad?
65% USA 15% Switzerland 10% Developed Markets 10% Emerging Markets
User avatar
hoppy08520
Posts: 2178
Joined: Sat Feb 18, 2012 11:36 am

Re: Google sheets not pulling stock prices

Post by hoppy08520 »

Bylo Selhi wrote: Mon Mar 15, 2021 12:30 pm
hoppy08520 wrote: Sun Mar 07, 2021 4:22 pmIf you want to copy this sheet and use it on your own, you'll need to "authorize" the script to run on your own sheet.
Could you please expand on the above snippet. I found the scripts in the GoogleSheets script editor but I don't see where/how to "authorize" them.
Glad this is working for you!

As for "authorize", sometimes when you first run a google app script, you might get prompted to agree to let the script run on your sheet. This is a defense against potential malware. If you open your sheet that have any of the custom functions like getNavMstar("VTI") then Google might ask you to authorize the script to run.
stocknoob4111
Posts: 2233
Joined: Sun Jan 07, 2018 12:52 pm

Re: Google sheets not pulling stock prices

Post by stocknoob4111 »

Stef wrote: Tue Mar 16, 2021 1:29 am It's delayed by 5-15min, is that so bad?
Referring to Mutual Funds, the prices are released usually at 6pm EST or thereabouts and MarketWatch has them within 5 mins of release while Google Finance still shows old prices for hours and usually not until the next day.
User avatar
zincTwo
Posts: 510
Joined: Tue Oct 30, 2018 11:31 pm
Location: California

Re: Google Sheets' googlefinance function not working

Post by zincTwo »

BroIceCream wrote: Mon Mar 15, 2021 2:49 pm Expense Ratios seem to take longer to update whenever they've changed (sometimes months).
I just reviewed my newly created "TICKERS" and looked for differences where there should be none (i.e. Expense Ratios).
I found seven funds (2 vanguard, 5 fidelity) where googleFinance and MorningStar returned different values. In 6 cases, MorningStar was correct (comparing to Fido or Vanguard sites), Google finance was off by 2-5 bps in most cases (>100bps in two instances). In one case it was rounding error (GF rounded up, Mstar truncated).

I swapped my TICKERS preference to use Mstar, w/ GoogleFinance as the backup.
pascalwager
Posts: 2094
Joined: Mon Oct 31, 2011 8:36 pm

Re: Google sheets not pulling stock prices

Post by pascalwager »

stocknoob4111 wrote: Tue Mar 16, 2021 11:11 am
Stef wrote: Tue Mar 16, 2021 1:29 am It's delayed by 5-15min, is that so bad?
Referring to Mutual Funds, the prices are released usually at 6pm EST or thereabouts and MarketWatch has them within 5 mins of release while Google Finance still shows old prices for hours and usually not until the next day.
For a long time, I had no ETFs, only mutual funds and my sheet had dual tables for just about everything I was doing--GoogleFinance prices and earlier manually-entered Yahoo! prices. Normally, there was 100% agreement by about 11:30 pm PST, but once in awhile there would be a laggard GF price that I'd see corrected the following morning.

The Yahoo! prices were usually posted about 1 1/2 hours after market closing. Later, I set up a Watch List at Vanguard for all of my MFs (Vg and DFA) and stopped using Yahoo! to avoid occasional price errors.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Hydromod wrote: Sun Mar 07, 2021 5:01 pm I like the bigcharts scrape, because I pull TIAA tickers that are hard to get and this updates soon after posting. The example below has the ticker name in cell A1. The 6 gives two decimal places, which works for most tickers. Some TIAA tickers have 4 places, and it needs an 8 instead.

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&A1), "table"),3,1)),6))
this works regardless of the price magnitude.

Code: Select all

=value(substitute((Index(ImportHtml("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&$A2, "table"),3,1)),"Last:",""))
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

hoppy08520 wrote: Sun Mar 07, 2021 4:22 pm Sorry for the delay, here it is:

Tickers Share Price Sample | GoogleSheets

See the "Read Me" tab for explanations.

What makes this spreadsheet interesting is that it pulls prices from three different services, and takes what it can get. I find that my sheet almost always works now because at least one of the three services works for every fund that I have. If one service changes how it functions (like alters the HTML structure or renames fields), then you will need to fix it or adapt. It's kind of a moving target.
...
I have shamelessly stolen your ideas here and updated a sheet I had shared a year ago that computes real-time during the trading day Mutual prices synthesized from the current movement of a paired ETF.

https://docs.google.com/spreadsheets/d/ ... sp=sharing (updated to revised link on NOV-21-2021)

It incorporates the KevinM functions for Yahoo and Morningstar (extended to return trade-date/time and current change %) as well as your iferror approach (much cleaner than my previous hacked set of byzantine calculations.)

I already had incorporated KevinM's getVanguardPriceYieldAndAttributes() function that provides loads of fund related data cleanly. Note, for VG investors, yield data, ER, etc are all well recorded and up to date using this function. I extended Kevin's VG function to include ETFs as well as Mutual Funds.

I also created a function getVanguardLastPriceYield() that returns the most recent fund price for a Vanguard Mutual Fund or CIT (in retirement plans) so I can get daily updates of my Vanguard 401(K) funds. Previously I depended upon authoritative answers only by downloading daily from Vanguard's 401K website. This is my favorite part of this sheet.

There are three basic sheets:
- BestMFTracking - provides a similar service to what hoppy provided previously for solid up-to-date mutual fund prices.
- EfficientTickers - using the iferror approach, ensures a reliable if not timely price based upon the synthetic pricing model described above.
- BestTickers - queries that ensure both a reliable and most up to date price based upon the synthetic pricing model described above. Achieved by running all queries and finding that most recent errorless query. This version is the most bullet proof fund pricing sheet I have ever created.

Make a copy and enjoy. the vlookup approach for finding the prices by symbol works well with all the tabs. I used made up names for the Vanguard CITs for that lookup since they don't have symbols. you can change them as necessary if you have a VG 401K.
Last edited by retiringwhen on Mon Nov 22, 2021 7:29 am, edited 1 time in total.
Hydromod
Posts: 695
Joined: Tue Mar 26, 2019 10:21 pm

Re: Google sheets not pulling stock prices

Post by Hydromod »

retiringwhen wrote: Sat Mar 27, 2021 6:33 am
Hydromod wrote: Sun Mar 07, 2021 5:01 pm I like the bigcharts scrape, because I pull TIAA tickers that are hard to get and this updates soon after posting. The example below has the ticker name in cell A1. The 6 gives two decimal places, which works for most tickers. Some TIAA tickers have 4 places, and it needs an 8 instead.

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&A1), "table"),3,1)),6))
this works regardless of the price magnitude.

Code: Select all

=value(substitute((Index(ImportHtml("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&$A2, "table"),3,1)),"Last:",""))
That's a big improvement.

After I posted, I was having issues with the number of digits fluctuating and did a regexprep to extract just the number part, but your solution is much cleaner.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Hydromod wrote: Sat Mar 27, 2021 7:58 am That's a big improvement.

After I posted, I was having issues with the number of digits fluctuating and did a regexprep to extract just the number part, but your solution is much cleaner.
Nothing like peer reviews for code quality :sharebeer
Gaaraz
Posts: 10
Joined: Fri Dec 06, 2013 10:13 am

Re: Google sheets not pulling stock prices

Post by Gaaraz »

Hey guys, what's the simplest way to pull a fund's price into Google Sheets please? For example this one: https://www.hl.co.uk/funds/fund-discoun ... cumulation

I literally just want the price if possible please, I'd much rather not have to import loads of data. Thanks :)
User avatar
gas_balloon
Posts: 1152
Joined: Fri Aug 15, 2014 4:55 pm

Re: Google sheets not pulling stock prices

Post by gas_balloon »

I have pretty much exclusively moved to using Morningstar now. Just go to Tools -> Script Editor. Copy the code below there

Code: Select all

var MORNINGSTAR_PRICE_URL_PREFIX = "http://etfs.morningstar.com/quote-banner?&t=";
var MORNINGSTAR_PRICE_URL_SUFFIX = "";
var MAX_CACHE_NAV_TIMEOUT = 10800; //3 hours


function NAV(ticker="VTI", forceRefresh=false) {
  var cacheKey = "MORNINGSTAR-NAV-" + ticker;
  var cache = CacheService.getPublicCache();
  var cacheVal = cache.get(cacheKey);
  if (!forceRefresh && cacheVal != null) {
    return parseFloat(cacheVal);
  }

  url = MORNINGSTAR_PRICE_URL_PREFIX + ticker + MORNINGSTAR_PRICE_URL_SUFFIX;
  var resp = UrlFetchApp.fetch(url).getContentText();
  var ret = parseFloat(_morningstar_extractPrice(resp));
  cache.put(cacheKey, ret, MAX_CACHE_NAV_TIMEOUT);
  return ret;
}

function _morningstar_extractPrice(inputMarkup) {
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("Last Price"));
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("lastPrice"));
  inputMarkup = inputMarkup.substring(0, inputMarkup.indexOf("</div>"));
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("\">"));
  inputMarkup = inputMarkup.replace(/">/, "");
  return inputMarkup;
}
Now you can simply use something like this in your sheet cells to fetch the price:

Code: Select all

=NAV("VTI")
The script caches the price for ~3 hours so you don't get rate-limited if your sheet has a ton of stocks its pulling. If you want to always have a fresh value, you can just set the 2nd optional boolean `True` like so:

Code: Select all

=NAV("VTI", True)
or change the function declaration in the script above to make the 2nd parameter default to True, like so:

Code: Select all

function NAV(ticker="VTI", forceRefresh=true) {
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Gaaraz wrote: Thu Aug 05, 2021 11:01 am Hey guys, what's the simplest way to pull a fund's price into Google Sheets please? For example this one: https://www.hl.co.uk/funds/fund-discoun ... cumulation

I literally just want the price if possible please, I'd much rather not have to import loads of data. Thanks :)
I am not familiar with UK-based funds, does it have a ticker symbol? of so, just use the googlefinance() function in sheets.
User avatar
Bylo Selhi
Posts: 1247
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 »

Gaaraz wrote: Thu Aug 05, 2021 11:01 am Hey guys, what's the simplest way to pull a fund's price into Google Sheets please? For example this one: https://www.hl.co.uk/funds/fund-discoun ... cumulation

I literally just want the price if possible please, I'd much rather not have to import loads of data. Thanks :)
See this post viewtopic.php?p=5866025#p5866025
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

I'm using retiringwhen's Ticker Tracking scripts to pull prices for my Googlesheets and it seems to have gone all pear shaped as of Thursday.
I now see that retiringwhen's scripts are no longer available and I missed two of his updates.

They were working so beautifully.

Is anyone having the same problems?

My spreadsheet is throwing multiple errors that seem to change randomly - VALUE errors etc. so it's pretty much dead. I'm afraid to really change anything too as I suspect this could be pure goooglesheets problems and not the scripts and eventually everything will magically work again.
Thanks for any help.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Expro wrote: Sat Nov 20, 2021 11:38 am I'm using retiringwhen's Ticker Tracking scripts to pull prices for my Googlesheets and it seems to have gone all pear shaped as of Thursday.
I now see that retiringwhen's scripts are no longer available and I missed two of his updates.

They were working so beautifully.

Is anyone having the same problems?

My spreadsheet is throwing multiple errors that seem to change randomly - VALUE errors etc. so it's pretty much dead. I'm afraid to really change anything too as I suspect this could be pure goooglesheets problems and not the scripts and eventually everything will magically work again.
Thanks for any help.
Oops, I will republish the sheet. I was trying to fix the problem with Morningstar and I accidentally unshared the sheet.

It may take me a day to get back on the new version as my wife just informed me of a important and fun task that needs to be done today, she wins!

It looks like Morningstar's old site is in the process of being decommissioned. It sort of works when you do direct browser-based queries, but the when called from google sheets, it returns 404 errors. I am guessing that they have too many folks scraping prices like my sheet does (I did not invent the script, I just adapted KevinM's sheet)

On a related note, at the same time, big charts is becoming less reliable. This implies the issue/limits may be imposed by Google, that is what I was trying to investigate!
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

Thanks retiringwhen! Your work ( and all the folks' involved in this) has been very much appreciated.
I'll keep an eye out for your changes.

"It looks like Morningstar's old site is in the process of being decommissioned. It sort of works when you do direct browser-based queries, but the when called from google sheets, it returns 404 errors."

Exactly. So frustrating.

Thanks again.
milktoast
Posts: 468
Joined: Wed Jul 10, 2019 8:17 pm

Re: Google sheets not pulling stock prices

Post by milktoast »

Expro wrote: Sat Nov 20, 2021 12:53 pm "It looks like Morningstar's old site is in the process of being decommissioned. It sort of works when you do direct browser-based queries, but the when called from google sheets, it returns 404 errors."
Ditto. I would be content with googlefinance if it wasn’t for the huge delay on mutual fund quotes.

Morningstar had them by 3:30pm pacific. But googlefinance has nothing until way past my bedtime.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

I am starting to suspect that actually google is rate limiting the queries as I am seeing problems across the entire set of queries to TSP (importHtml), Morningstar (gscript) and Big Charts using (importHtml).

Note, each of those queries still work if requested via a browser.

yahoo is still working though.

The vanguard call also appears to be pretty solid as well as googlefinance. There are real server side costs for all these calls, maybe google is trying to kill off non-googlefinance queries. Maybe if they fixed the nightly mutual fund updates, we'd stop scraping other sites!

I am going to look at incorporating the caching approach mentioned by gas_balloon several months ago. It will be a bit of work though as his cache only includes the price while I am tracking dates, prices and change percentages. I will probably have to create two different functions since consistent caching across requested query types would be a nightmare.

If that improves things for Morningstar, I will create cached gscript calls for TSP and big charts too as a way to self-limit their calls.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

It seems that google sheets is no longer able to query morningstar.com or tsp.org. All forms of queries to the site from gscript or importhtml are immediately returned with errors. It looks like either Google or those sites are not allowing scraping activities anymore. It does not appear to be a rate limiting issue. I am guessing that Google has put the blocks in due to too many folks doing what we do (there are dozens of threads in stackexchange doing very similar things.)

I have revised my sheet and republished. This version still has morningstar and TSP included, but they fail a bit more gracefully.

I am working on a new sheet that will remove them and also add some defensive caching based upon gas_balloon's model later. In the end, it seems like big charts still works sporadically and yahoo is still solid, but yahoo is problematic for mutual funds. I may come up with a time based work-around for that. I'll have to see.

https://docs.google.com/spreadsheets/d/ ... sp=sharing
Last edited by retiringwhen on Mon Nov 22, 2021 8:27 am, edited 1 time in total.
TedSwippet
Posts: 3961
Joined: Mon Jun 04, 2007 4:19 pm
Location: UK

Re: Google sheets not pulling stock prices

Post by TedSwippet »

retiringwhen wrote: Sun Nov 21, 2021 7:14 am The vanguard call also appears to be pretty solid as well as googlefinance. There are real server side costs for all these calls, maybe google is trying to kill off non-googlefinance queries. Maybe if they fixed the nightly mutual fund updates, we'd stop scraping other sites!
Could this perhaps be a signal of intent to stop offering fund prices entirely? Googlefinance() used to serve up data for a decent selection of EU and UK based mutual funds, but Google silently canned that a couple of years ago (and at about the same time as they ruined the main Google Finance web portal). The disappearance happened gradually; sometimes data was old, sometimes unavailable, sometimes re-available, glitchy, and then finally ... gone entirely.

As a result, my own spreadsheet is now a morass of brittle importhtml() scrapes that I really wish weren't needed. :-(
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

TedSwippet wrote: Mon Nov 22, 2021 8:06 am Could this perhaps be a signal of intent to stop offering fund prices entirely?
My guess is that google is actually trying to get people to use googlefinance instead of the scrapes, but why they dropped non-US quotes, I am not sure. I would bet it has something to do with licensing costs though, just a guess.
Digital Dave
Posts: 77
Joined: Sat Mar 10, 2007 7:47 pm
Location: Western New York

Re: Google sheets not pulling stock prices

Post by Digital Dave »

I can get the bigcharts working temporarily by changing the fund ticker to the opposite case, vimax to VIMAX or VIMAX to vimax. I change it every time I want to get the prices back. Not a fix but a mitigation.
Investing in Mutual Funds, ETF's, Forever Stamps and Bittulips.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Digital Dave wrote: Mon Nov 22, 2021 10:23 am I can get the bigcharts working temporarily by changing the fund ticker to the opposite case, vimax to VIMAX or VIMAX to vimax. I change it every time I want to get the prices back. Not a fix but a mitigation.
I have seen similar tweaks working. It looks to me that google is "optimizing" the efficiency of the calls to importhtml() in a way that is not friendly to real-time ticker readers. Similar tweaks are required for the gscript calls. I wrote a script that took the tickers and tweaked them to force updates semi-automatically (you call the script from a menu), but even that is not 100% fool-proof.
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

Digital Dave wrote: Mon Nov 22, 2021 10:23 am I can get the bigcharts working temporarily by changing the fund ticker to the opposite case, vimax to VIMAX or VIMAX to vimax. I change it every time I want to get the prices back. Not a fix but a mitigation.
I've discovered that creating a copy of the entire Googlesheet results in everything working fine - but for one time only. Refresh the sheet and it fails. Create a new copy - literally one minute later - and it works. Reload THAT version and it fails.
So I don't know what that means...
milktoast
Posts: 468
Joined: Wed Jul 10, 2019 8:17 pm

Re: Google sheets not pulling stock prices

Post by milktoast »

I'm playing with https://rapidapi.com/apidojo/api/yh-finance/ If it works, I'll post something at end of week (after some testing).

One annoying thing is that mutual funds report their regularMarketTime as the current time during market open and regularMarketPrice as the last NAV. Unlike real quoting services that properly date the NAV as yesterday at 4pm eastern.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

milktoast wrote: Mon Nov 22, 2021 1:21 pm I'm playing with https://rapidapi.com/apidojo/api/yh-finance/ If it works, I'll post something at end of week (after some testing).

One annoying thing is that mutual funds report their regularMarketTime as the current time during market open and regularMarketPrice as the last NAV. Unlike real quoting services that properly date the NAV as yesterday at 4pm eastern.
It has some pretty expensive rate limits with commercial grade pricing for actual API usage. I'd probaby hit the monthly hard limits for free within 24 hours each month.

https://rapidapi.com/apidojo/api/yh-finance/pricing

BTW, the Yahoo script in my sheet still works and looks to be fundamentally about the same (even with a similar yahoo bug for how mutual prices are handled during trading hours.)
pkay
Posts: 165
Joined: Wed Jan 17, 2018 11:04 am

Re: Google sheets not pulling stock prices

Post by pkay »

retiringwhen wrote: Sat Mar 27, 2021 6:33 am
Hydromod wrote: Sun Mar 07, 2021 5:01 pm I like the bigcharts scrape, because I pull TIAA tickers that are hard to get and this updates soon after posting. The example below has the ticker name in cell A1. The 6 gives two decimal places, which works for most tickers. Some TIAA tickers have 4 places, and it needs an 8 instead.

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&A1), "table"),3,1)),6))
this works regardless of the price magnitude.

Code: Select all

=value(substitute((Index(ImportHtml("https://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&$A2, "table"),3,1)),"Last:",""))
retiringwhen,
in the function, where does it reference the cell of the ticker symbol?
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

pkay wrote: Tue Nov 23, 2021 10:54 am In the function, where does it reference the cell of the ticker symbol?
The reference to cell $A2 in response (kinda lost in the translation to a forum post. you can change the $A2 to whatever cell or ticker value you desire.
The original used $A1.....

BTW, the bigcharts queries have begun to fail slowly and become much less reliable, I would not depend upon them in any meaningful way.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

I have been playing around the last few days and I am seeing many issues with the Google App Script overall. Strange errors, lack of refresh, broken functions that stopped working an hour ago, then start working again with no change, etc.

Magically, today, the TSP quotes started working again and Big Charts is seeing more queries complete successfully. I am going to be sanguine and hope that much of the failures of the last week can be attributed to some instability in the Google infrastructure. Maybe even the Morningstar queries will start working again.
User avatar
changingtimes
Posts: 324
Joined: Mon Jul 24, 2017 9:28 am

Re: Google sheets not pulling stock prices

Post by changingtimes »

Yes, it's back to working fine for me as well. Must have been Google's issue.
Hydromod
Posts: 695
Joined: Tue Mar 26, 2019 10:21 pm

Re: Google sheets not pulling stock prices

Post by Hydromod »

Still no luck for bigcharts for me. I resorted to Excel stocks for TIAA quotes, but even that doesn't work for CREF tickers like QREARX.
Typ997S
Posts: 87
Joined: Fri Apr 06, 2012 12:36 pm

Re: Google sheets not pulling stock prices

Post by Typ997S »

A couple things...

1) First, thanks to retiringwhen for their work and spreadsheet contributions. When the Big Charts "scrape" started acting up, I successfully adopted retiringwhen's spreadsheet mentioned upthread, specifically the "BestMFTracking" sheet. It seems at the moment, the Vanguard "scrape" is the only reliable method. Everything else is either dead or wildly unstable.

2) I have no idea if all this is connected, but there seems to be a lot of instability around Google services the last couple days. I've had other weirdness in Google sheets, and Gmail refused to load for most of the (PST) morning on my computer even though it was OK on my iPhone.

FWIW...and thanks again retiringwhen! :sharebeer
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

changingtimes wrote: Wed Nov 24, 2021 11:23 am Yes, it's back to working fine for me as well. Must have been Google's issue.
Still not working for me.
At this point I've lost track of all the little hacks I've had to try to get all my tickers updated. :oops:
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Here is a revised version of my tracking sheet that converted BigCharts queries to a google script. This improves performance immensely. I also added caching for many of the calls to improve speed and avoid rate limits in google sheets.

Morningstar and TSP both appear to be dead for now (the TSP script is a prototype and does not work regardless even if the site begins again since I can't really test it.)

I also put some heurstics into the caching algorithm that tries to understand how long a quote may be useful. It may or may not work well. feedback on stale quotes especially are welcome. See the Instructions tab for more details.

Comments welcome:

Cach Ticker Tracking
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

retiringwhen wrote: Sat Nov 27, 2021 2:38 pm Here is a revised version of my tracking sheet that converted BigCharts queries to a google script. This improves performance immensely. I also added caching for many of the calls to improve speed and avoid rate limits in google sheets.

Morningstar and TSP both appear to be dead for now (the TSP script is a prototype and does not work regardless even if the site begins again since I can't really test it.)

I also put some heurstics into the caching algorithm that tries to understand how long a quote may be useful. It may or may not work well. feedback on stale quotes especially are welcome. See the Instructions tab for more details.

Comments welcome:

Cach Ticker Tracking
Cool. I'll be working on replacing my first draft modified mess of your original version this afternoon. Wish me luck...
Hydromod
Posts: 695
Joined: Tue Mar 26, 2019 10:21 pm

Re: Google sheets not pulling stock prices

Post by Hydromod »

retiringwhen wrote: Sat Nov 27, 2021 2:38 pm Here is a revised version of my tracking sheet that converted BigCharts queries to a google script. This improves performance immensely. I also added caching for many of the calls to improve speed and avoid rate limits in google sheets.
I grabbed the sheet and it handled my funds no problem (although my bigCharts scrape was working today too, for a change). I'll be curious to see how it does during and immediately after market hours when bigCharts has been giving issues.

I was curious as to how the functions were implemented, which gave me a half hour of fun figuring out how Google sheets allows user functions. Finally I got the functions to be visible though. Now I have something to pore over!

Thanks much for this.
alx
Posts: 115
Joined: Sun Mar 17, 2019 4:52 pm

Re: Google sheets not pulling stock prices

Post by alx »

Those of us who need to look up TSP prices, did you notice that the google spreadsheets fail with the tsp lookup?

I have this for the G Fund for example:

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/ ... InvFunds=1", "table", 1), 2,2)

It has been failing since yesterday (or at least I noticed it then)

The underlying HTTP service does work currently but was down for some time yesterday (I checked with https://downforeveryoneorjustme.com/). However, google spreadsheet still shows an error.

Second question:
I know people have come up with clever fallbacks for ticker price lookups.
What would be a good fallback? I read this thread and I see https://www.tsptalk.com/tsp_share_prices.php mentioned. Is that reliable enough for a fallback? Does anyone have scraping code they can share?
alx
Posts: 115
Joined: Sun Mar 17, 2019 4:52 pm

Re: Google sheets not pulling stock prices

Post by alx »

Those of us who need to look up TSP prices, did you notice that the google spreadsheets fail with the tsp lookup?

I have this for the G Fund for example:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,2)
It has been failing since yesterday (or at least I noticed it then)

The underlying HTTP service does work currently but was down for some time yesterday (I checked with https://downforeveryoneorjustme.com/). However, google spreadsheet still shows an error.

Second question:
I know people have come up with clever fallbacks for ticker price lookups.
What would be a good fallback? I read this thread and I see https://www.tsptalk.com/tsp_share_prices.php mentioned. Is that reliable enough for a fallback? Does anyone have scraping code they can share?
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

alx wrote: Thu Dec 02, 2021 1:44 pm Second question:
I know people have come up with clever fallbacks for ticker price lookups.
What would be a good fallback? I read this thread and I see https://www.tsptalk.com/tsp_share_prices.php mentioned. Is that reliable enough for a fallback? Does anyone have scraping code they can share?
The TSP.gov site looks to be permenantly rejecting googlesheets scrapes and has been for a couple weeks. Here is a quick and dirty gscript hack for the tsptalk.com page you reference. I only tested it for about 5 minutes, YMMV. :sharebeer

Note that the page has odd date scheme and it shows yesterday's quote as today through COB. Very annoying. But that is the best I can do in short order.

Code: Select all

/**
 * Get asOfDateTime and price from TSP.gov for TSP F fund letter.
 * @param {"F"} fundLetter Fund ticker symbol.
 * @returns [asOf, price]
 * @customfunction
 */
function getPriceTSP(fundLetter="G") {

  var url = "https://www.tsptalk.com/tracker/tsp_funds_balance_returns_by_date.php";
  
  var resp = UrlFetchApp.fetch(url);
  var respText = resp.getContentText();
  Logger.log("responseCode = %s", resp.getResponseCode());
  var dateAsOf = _TSP_extractAsOf(respText);
  var price = _TSP_extractPrice(fundLetter,respText);
  return [[dateAsOf, price]];
}

function _TSP_extractAsOf(inputMarkup) {
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("Fund Returns From:</font></b></TD>"));
  var leftAnchor = "<font color=CC3300>";
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf(leftAnchor));
  inputMarkup = inputMarkup.substring(leftAnchor.length, inputMarkup.indexOf("</font> thru <font"));
  //Logger.log(inputMarkup);
  var parsedAsOf = Date.parse(inputMarkup);
  var dateAsOf = new Date(parsedAsOf);
  return dateAsOf;
}

function _TSP_extractPrice(fundLetter, inputMarkup) {
  var fundAnchor = fundLetter+"-fund";
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf(fundAnchor));
  var leftAnchor = "face=arial>";
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf(leftAnchor));
  inputMarkup = inputMarkup.substring(leftAnchor.length, inputMarkup.indexOf("</font>"));
  return parseFloat(inputMarkup);
}
alx
Posts: 115
Joined: Sun Mar 17, 2019 4:52 pm

Re: Google sheets not pulling stock prices

Post by alx »

retiringwhen wrote: Thu Dec 02, 2021 2:28 pm
The TSP.gov site looks to be permanently rejecting googlesheets scrapes and has been for a couple weeks.
That's bad. But good to know.
retiringwhen wrote: Thu Dec 02, 2021 2:28 pm Here is a quick and dirty gscript hack for the tsptalk.com page you reference.
I appreciate it! It works and I tweaked it a bit for my purposes. First time I'm using scripts in Google Spreadsheets - it all works - but I have to say, as much as I love having control I also hate adding complexity but for now at least this works well.
alx
Posts: 115
Joined: Sun Mar 17, 2019 4:52 pm

Re: Google sheets not pulling stock prices

Post by alx »

... and just as I was writing this, I realize GOOGLEFINANCE stopped working. I'll read above to see if there's consensus on a better setup but if anyone want to point to the "best practice" I'm all ears.

I cannot understand why Google cannot make this reliable. Looks like they don't care about it anymore?
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

Looks like Google is getting more and more strigent in its resource limiters: I got this message this morning (early):
Exception: Service invoked too many times for one day: urlfetch. (line xx)
This tells me that they are getting more and more picky about just what you are doing gscript and the html import functions.

BTW, urlfetch() is the core scraping request for a website in gscript.

I just found this link on quotas: https://developers.google.com/apps-scri ... ces/quotas
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

alx wrote: Thu Dec 02, 2021 5:55 pm ... and just as I was writing this, I realize GOOGLEFINANCE stopped working. I'll read above to see if there's consensus on a better setup but if anyone want to point to the "best practice" I'm all ears.

I cannot understand why Google cannot make this reliable. Looks like they don't care about it anymore?
I tried the Microsoft securities lookups in Excel about 18 mos. ago. IT was an absolute dumpster fire of bad implementation. In reality, google goes through periods of instability and then things get better again. Be patient. Google sheets is an amazing platform for doing stuff with money (and a lot of other stuff too). But remember, 99% of us are not paying a penny for all that functionality.
retiringwhen
Posts: 2470
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen »

I am putting versions on my sheets now for those of you who are actually copying the files.

I have released version v0.04 of my Cache Ticker Tracking sheet.
  • It has a revised, partially tested scrape for TSP (previously discussed).
  • It also did a ton of cleanup and improvements in the caching feature for just about all the data scraped from the web. So far, it is performing very well.
  • Look at the Instructions tab to find data on the calcCacheTTL to see how I am trying to limit the number of daily scrapes. I think I have hit a decent sweet spot between timeliness and rate limits.
  • Finally, as requested, I came up with an expense ratio scrape off yahoo.com that works reasonably well. It is expensive though, so use judiciously and definitely only the cached version of the call...
  • As a bonus. I threw in some Treasuries/TIPS queries and CPI-U queries I use in other sheets that I also improved with cached gscript calls.
All told, these have improved the performance of my daily dashboard sheets to almost zero open delays and reasonable timeliness.

Cache Ticker Tracking v0.04
Expro
Posts: 118
Joined: Mon Feb 08, 2016 3:37 am
Location: Girona

Re: Google sheets not pulling stock prices

Post by Expro »

I'll be copying the newest version later on today.
Thanks for all the work. I really enjoy reading your comments in the scripts. :sharebeer
Post Reply