Yahoo data pull failing in Google Sheets

[Ashstevens848]

If it helps I normally use the below for price

=GOOGLEFINANCE(C14, "price")

[smokey01]

I only have an iPad so cannot add the JSON script code to my google sheet. I have tried this

=INDEX(split(IMPORTXML(concatenate("https://finance.yahoo.com/quote/IMB.L"),"//*[@id='quote-summary']/div[2]/table/tbody/tr[6]/td[2]"),"()"),1)

to get the IMB.L dividend value but it came back with #N/A.

The values of most interest to me are the current price and the previous close price.

Any ideas?

[Itsallaguess]

I only have an iPad so cannot add the JSON script code to my google sheet. I have tried this

=INDEX(split(IMPORTXML(concatenate("https://finance.yahoo.com/quote/IMB.L"),"//*[@id='quote-summary']/div[2]/table/tbody/tr[6]/td[2]"),"()"),1)

to get the IMB.L dividend value but it came back with #N/A.

The values of most interest to me are the current price and the previous close price.

Any ideas?

I think if you look back at the first post on this thread, it was the recent sporadic failure of the normally-working INDEX process you've mentioned above that then led to the development of the JSON script function mentioned in recent posts.

I've no experience with using Google Sheets on the iPad, presumably via the app itself, as all my recent development work has been browser-based, logging directly into the web-based Google Sheets processes from a Windows desktop.

I have a suspicion that Apple may have locked down device-based scripting via the iPad Google Sheets app, but hopefully someone with more experience with the Apple side of things might be able to corroborate that?

Cheers,

Itsallaguess

[y0rkiebar]

The google sheets IMPORTXML function doesn't work with certain characters in the requested URL, "." (dot) being one of them (yes, crazy I know!).
So using it to access yahoo finance is pretty much a non starter for UK stocks (IMB.L etc etc)

https://support.google.com/docs/thread/ ... stop?hl=en

You're better off creating a google scripts function which uses the scripts UrlFetchApp capability, as per this post viewtopic.php?f=27&t=39118#p589024 by itsallaguess

[ReformedCharacter]

As mentioned in other threads elsewhere, the v10 Yahoo API has been really quite reliable for some time now, and so you might want to have a play with grabbing some JSON fields from their v10 API data, which helpfully does seem to provide for the dividend information you're looking for...

Create the following simple Script Function in your Google Sheet, using the 'SELECT ALL' option at the top of the code window below and the using CTRL-C to copy all the function text ...
Itsallaguess

Thanks again, I've been using the Google finance price function for my portfolio for years and it's been pretty reliable with only the occasional "N\A", but recently it's been more flakey and today 80% of my holdings show an error.

I tried your getJSON function which works brilliantly and gives prices for all of my holdings. Your help has come at a very opportune time

RC

[Hallucigenia]

The google sheets IMPORTXML function doesn't work with certain characters in the requested URL, "." (dot) being one of them (yes, crazy I know!).
So using it to access yahoo finance is pretty much a non starter for UK stocks (IMB.L etc etc)

You can urlencode it with %2E if needed? Eg :

https://finance.yahoo.com/quote/IMB%2EL

[Itsallaguess]

Thanks again, I've been using the Google finance price function for my portfolio for years and it's been pretty reliable with only the occasional "N\A", but recently it's been more flakey and today 80% of my holdings show an error.

I tried your getJSON function which works brilliantly and gives prices for all of my holdings. Your help has come at a very opportune time

Thanks RC, that's great news, and I'm glad it's been helpful.

I've been using the v10 Yahoo API data-source for a while now with my Excel portfolio tool, for a number of different things, and given it's recent reliability whilst other Yahoo sources have been flaky, I hope it's going to be a good option for us, and especially given the speed of data-return from something that only takes a single EPIC at a time...

Cheers,

Itsallaguess

[y0rkiebar]

The google sheets IMPORTXML function doesn't work with certain characters in the requested URL, "." (dot) being one of them (yes, crazy I know!).
So using it to access yahoo finance is pretty much a non starter for UK stocks (IMB.L etc etc)

You can urlencode it with %2E if needed? Eg :

https://finance.yahoo.com/quote/IMB%2EL

Yes, I'd tried that, still doesn't work.

[praiz]

Maybe it is a temperamental yahoo issue so not a reliable resource, does anyone know a better way that i can pull this data from somewhere?

As mentioned in other threads elsewhere, the v10 Yahoo API has been really quite reliable for some time now, and so you might want to have a play with grabbing some JSON fields from their v10 API data, which helpfully does seem to provide for the dividend information you're looking for...

Create the following simple Script Function in your Google Sheet, using the 'SELECT ALL' option at the top of the code window below and the using CTRL-C to copy all the function text -

[code]function getJSON(url) {

// Fetch the JSON
let response = UrlFetchApp.fetch(url);
let text = response.getContentText();
let json = JSON.parse(text);
......

Itsallaguess

Thank you for this solution. It works in some instances and also does not work for me in some other instances, all on google sheets.

It did not work for me when used with the following tickers
GC=F
SI=F
CC=F
KC=F
HG=F
CT=F
PA=F
PL=F
SB=F
BZT=F
CL=F
DASH-USD
MIOTA-USD
TRX-USD
ZTC-USD


Could you pls assist me?

[Itsallaguess]

Thank you for this solution. It works in some instances and also does not work for me in some other instances, all on google sheets.

It did not work for me when used with the following tickers

GC=F
SI=F
CC=F
KC=F
HG=F
CT=F
PA=F
PL=F
SB=F
BZT=F
CL=F
DASH-USD
MIOTA-USD
TRX-USD
ZTC-USD


Could you pls assist me?

I think you might have missed a later post with some improved Google Sheets code, which I've just tested with the above tickers, and which does return Yahoo prices as shown below, although please note that there will be no dividend information retrieved from the Yahoo site for your particular list. given the above 'non-company' tickers that you've listed above -



Source - my own Google Sheet


Here's a link to the post containing the improved Google Sheet code that will retrieve the above prices -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=39118#p589024

I've dip-checked the above retrieved price information using the relevant Yahoo Finance page for some of the tickers, but as always, I'd please ask that you also carry out your own due-diligence before using the above process in anger...

Cheers,

Itsallaguess

[praiz]

Thank you for this solution. It works in some instances and also does not work for me in some other instances, all on google sheets.

It did not work for me when used with the following tickers

GC=F
SI=F
CC=F
KC=F
HG=F
CT=F
PA=F
PL=F
SB=F
BZT=F
CL=F
DASH-USD
MIOTA-USD
TRX-USD
ZTC-USD


Could you pls assist me?

I think you might have missed a later post with some improved Google Sheets code, which I've just tested with the above tickers, and which does return Yahoo prices as shown below, although please note that there will be no dividend information retrieved from the Yahoo site for your particular list. given the above 'non-company' tickers that you've listed above -



Source - my own Google Sheet


Here's a link to the post containing the improved Google Sheet code that will retrieve the above prices -



I've dip-checked the above retrieved price information using the relevant Yahoo Finance page for some of the tickers, but as always, I'd please ask that you also carry out your own due-diligence before using the above process in anger...

Cheers,

Itsallaguess

Works like a charm. Thank you so so very much. You saved my day.

[Itsallaguess]

Works like a charm.

Thank you so so very much. You saved my day.

Great news

Cheers,

Itsallaguess

[praiz]

Works like a charm.

Thank you so so very much. You saved my day.

Great news

Cheers,

Itsallaguess

Woke up this morning and for some reason the i get errors when using
=getJSON(GC=F, "price") and =getJSON("https // query2 finance yahoo com/v10/finance/quoteSummary/"GC=F"?modules=summaryDetail")

[Itsallaguess]

Woke up this morning and for some reason the I get errors when using

=getJSON(GC=F, "price") and =getJSON("https // query2 finance yahoo com/v10/finance/quoteSummary/"GC=F"?modules=summaryDetail")

Yes, it looks like Yahoo have now turned off simple access to their v10 pricing API unfortunately...

Cheers,

Itsallaguess

[praiz]

Woke up this morning and for some reason the I get errors when using

=getJSON(GC=F, "price") and =getJSON("https // query2 finance yahoo com/v10/finance/quoteSummary/"GC=F"?modules=summaryDetail")

Yes, it looks like Yahoo have now turned off simple access to their v10 pricing API unfortunately...

Cheers,

Itsallaguess

Too bad, but still good to know. I hoping for an alternative in the future.

Cheers.

[Ashstevens848]

Is it me or have all the yahoo json links stopped working, my sheet has all errors now.

[Urbandreamer]

I'm curious as to why Yahoo is being used as a feed for Google sheets.

Is it because a given ticker can't be found using the =GoogleFinance(G5, "price") where G5 contains the likes of LON:AV
For what it's worth I've begun trying to replicate HYPTUSS on google sheets.
It's slow going, as I'm having internet problems.

Note that you can also get the yield data from ShareCast. I.E
=IMPORTHTML("https://www.sharecast.com/equity/The_Renewables_Infrastructure_Group_Limited","table",1)

If you are not aware you can link a timer trigger to a Google app script to do an end of day update.
While I didn't implement HYPTUSS back in 2020, I did auto log FTSE 100, Allshare and my portfolio at the end of each working day with this very simple script.

Code: Select all

function addProduct() {
  //skip if weekend
   var day = new Date();
    if (day.getDay()<6 && day.getDay()!=0) {
 
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var secondSheet = spreadsheet.getSheets()[1];
      var numColumns = 8;
      var firstOpenRow = secondSheet.getLastRow() + 1;
      var firstOpenRange = secondSheet.getRange(firstOpenRow, 1, 1, numColumns+1);
      var existingData = secondSheet.getRange(2, 1, 1, numColumns);
      for (var i = 0; i < numColumns; i++) {
        firstOpenRange.getCell(1, i+1).setValue(existingData.getCell(1, i+1).getValue());
      }
    firstOpenRange.getCell(1, numColumns+1).setValue(day.getDay());
    }
}


NOTE it is VERY simple and relies on things like the order of my sheets and the fact that I fill in Row 2 with the data that I want to record. I could do a lot better.

[y0rkiebar]

Lots of tickers aren't available in googlefinance so I either scrape markets.ft.com or use the Yahoo API (which in recent days ain't great).

[kiloran]

Is it me or have all the yahoo json links stopped working, my sheet has all errors now.

It looks like Yahoo have now disabled all of their financial data sources, though I've seen no formal announcement of this.

--kiloran

[JonnyT]

I use Python with the yfinance module to pull stock prices from yahoo into google sheets if the googlefinance function doesn't work, which it often doesn't