Yahoo data pull failing in Google Sheets

[Ashstevens848]

Hi All

I have created a very simple google sheet to track my shares and dividend info I'm not a coder by any means, until a few days ago I was able to get all the US dividend info using a link such as below to pull the data into my google sheet;

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

B10 being the stock ticker field cell such as T for AT&T

(not allowed to post links but hopefully you get the idea)

The UK and European shares never worked but does anyone know why this would have stopped working suddenly or know of an easy way for me to fix this or a better way for me to get the dividend amount ?

Many Thanks in Advance

[doolally]

Have a look at this thread: viewtopic.php?f=27&t=38834

We can't see all of your cell contents, but I suspect that a change from the Yahoo v7 API to v6 may fix the problem
doolally

[Ashstevens848]

not using an API just and importxml query any ideas ? not familiar on how to use an API

[ReformedCharacter]

Hi All

I have created a very simple google sheet to track my shares and dividend info I'm not a coder by any means, until a few days ago I was able to get all the US dividend info using a link such as below to pull the data into my google sheet;

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

B10 being the stock ticker field cell such as T for AT&T

(not allowed to post links but hopefully you get the idea)

The UK and European shares never worked but does anyone know why this would have stopped working suddenly or know of an easy way for me to fix this or a better way for me to get the dividend amount ?

Many Thanks in Advance

I can't get it to work even though the xpath looks OK.

Perhaps a different site would help, this works for me:

=importxml("https://www.wallstreetzen.com/stocks/us/nyse/t/dividends","/html/body/div[1]/div[2]/div/div[1]/div/div[2]/div[3]/div[2]/div/div[2]")

(that gives the yield - 5.65%), if that is what you want.

RC

[doolally]

not using an API just and importxml query any ideas ? not familiar on how to use an API

You can't post links until you have created around 5 posts. You might be able to paste the cell contents between code tags or perhaps in pre tags. That would give us a better idea where you are getting your data from
doolally

[ReformedCharacter]

You might be able to paste the cell contents between code tags or perhaps in pre tags. That would give us a better idea where you are getting your data from
doolally

It looks like it's coming from here:

https://finance.yahoo.com/quote/T?p=T&.tsrc=fin-srch

The problem appears to be that the Google sheets importxml function is giving an error, suggesting that it cannot retrieve the data from the xpath. I don't know why that is, the xpath looks correct and it's what I get when I inspect it. I've spent some time trying to scrape data with importxml with mixed results. I wouldn't be surprised if some sites block it but that's a guess on my part.

RC

[Itsallaguess]

It looks like it's coming from here:

https://finance.yahoo.com/quote/T?p=T&.tsrc=fin-srch

The problem appears to be that the Google sheets importxml function is giving an error, suggesting that it cannot retrieve the data from the xpath. I don't know why that is, the xpath looks correct and it's what I get when I inspect it.

I've spent some time trying to scrape data with importxml with mixed results. I wouldn't be surprised if some sites block it but that's a guess on my part.

I've just had a quick look at a new Google Sheet, and when I use the following it returns what I think is the expected 'Forward Dividend' result from the AT&T Yahoo page -

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



Source - my own Google Sheet

That's using a slightly different source URL to the one you've used in your quoted post above, and is more aligned with what I think is the OP's Yahoo URL...

https://finance.yahoo.com/quote/T

If this is the expected returned data for this particular XML import, then it's not quite clear what difference there is in the working Google Sheets formula I've used above, and the one the OP is using...

Cheers,

Itsallaguess

[Ashstevens848]

Thanks Itsallaguess

That is exactly what I was trying to do and exactly what I was doing.

I tried it in a new sheet and it works with nothing changed and the same syntax in both it doesn't work in the original sheet.

Fort anyone else who reads this

I think the issue is that I saved it on my desktop and converted it to excel to work on it locally and then uploaded it again and put it back to google sheets which may have broke something. Can't be sure but that is all i can think of.

[Ashstevens848]

You wouldn't happen to know a way to get it to work with European shares would you same syntax doesn't work with them such as IMB.L for Imperial Brands

[Ashstevens848]

update no seems to have worked for an hour or so and now showing errors again

[ReformedCharacter]

update no seems to have worked for an hour or so and now showing errors again

It doesn't work for me either, although the Yahoo page looks good. I get:

Could not fetch URL: https://finance.yahoo.com/quote/T

Edit: now working again

RC

[Ashstevens848]

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 ?

[ReformedCharacter]

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 ?

I haven't found a single source that provides both UK and US data and many provide a yield but not the actual dividend value which is what you seem to be looking for.

RC

[Ashstevens848]

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 ?

I haven't found a single source that provides both UK and US data and many provide a yield but not the actual dividend value which is what you seem to be looking for.

RC

OK thanks RC

[Itsallaguess]

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: Select all

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


  // Navigate to where the main data is
let stats = json.quoteSummary.result[0].summaryDetail.dividendRate.raw

output = stats

return output
}

Then once you've saved that script in your sheet (Extensions / Apps Script), you can use the following in-sheet process to display the dividend data you're looking for, with the following example using the ticker IMB.L -

=getJSON("https://query2.finance.yahoo.com/v10/finance/quoteSummary/imb.l?modules=summaryDetail")

we can then replace the above hard-coded IMB.L ticker with a cell reference to gain an in-sheet list if required -

=getJSON("https://query2.finance.yahoo.com/v10/finance/quoteSummary/" & C4 & "?modules=summaryDetail")

Google Sheets output data using the above JSON function for AT&T and Imperial Brands -



Source - my own Google Sheet

Links provided below to the individual Yahoo Finance pages for AT&T and Imperial Brands, to verify the above 'Forward Dividend' information, which were correctly aligned at the time of this post -

https://finance.yahoo.com/quote/t

https://finance.yahoo.com/quote/IMB.L/

It could probably do with some error-handling to improve the process, but hopefully the above helps nudge you towards being able to use these v10 JSON sources, where some flexibility around the following line of function code would allow you to find other underlying information where available -

let stats = json.quoteSummary.result[0].summaryDetail.dividendRate.raw

Using the above JSON path, and taking a look at the following underlying batch of structured JSON data for IMB.L should hopefully help with that -

https://query2.finance.yahoo.com/v10/finance/quoteSummary/imb.l?modules=summaryDetail

Cheers,

Itsallaguess

[Ashstevens848]

let stats = json.quoteSummary.result[0].summaryDetail.dividendRate.raw

Using the above JSON path, and taking a look at the following underlying batch of structured JSON data for IMB.L should hopefully help with that -

https://query2.finance.yahoo.com/v10/finance/quoteSummary/imb.l?modules=summaryDetail

Cheers,

Itsallaguess

Thanks very much that is really helpful I'll give a try and hopefully get it working.

[ReformedCharacter]

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 ?

I haven't found a single source that provides both UK and US data and many provide a yield but not the actual dividend value which is what you seem to be looking for.

RC

Athough I'm sure Itsallaguess probably knows more than me

RC

[Itsallaguess]

I haven't found a single source that provides both UK and US data and many provide a yield but not the actual dividend value which is what you seem to be looking for.

Although I'm sure Itsallaguess probably knows more than me

I'm not sure about that - a week ago I didn't know much at all about Google Sheets and it's a really quite powerful scripting language, so getting this far has certainly been an interesting learning curve.

I've had another play with the earlier v10 API process, because there's a number of different JSON modules available from Yahoo that contain different share-related fields, so I thought I'd try to improve it to take that into account for a couple of different modules, with enough flexibility in the scripting code that will then allow it to expand into broader areas including other modules if that's then needed...

The improved getJSON scripting function below will now stack two different JSON modules, one being the already-used 'summaryDetail', which contains dividend-related information, and now also the 'price' module, which means we can also then get related share-price information as well -

Code: Select all

function getJSON(epic, field) {
 
 // Fetch the multi-module JSON for the epic
  let response = UrlFetchApp.fetch('https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + epic + '?modules=price%2CsummaryDetail');
  let text = response.getContentText();
  let json = JSON.parse(text);


// Pick out some fields from the different v10 Yahoo modules
// price module first
var regularMarketPrice = json.quoteSummary.result[0]?.price?.regularMarketPrice?.raw      || 'Not available';

// then a couple from the summaryDetail module
var dividendRate = json.quoteSummary.result[0]?.summaryDetail?.dividendRate?.raw      || 'Not available';
var dividendYield = json.quoteSummary.result[0]?.summaryDetail?.dividendYield?.fmt      || 'Not available';

let output = "????"

if (field == "price") {
  output = regularMarketPrice;
}
else if (field == 'dividend_rate') {
  output = dividendRate;
}
else if (field == 'dividend_yield') {
  output = dividendYield;
}

return output
}

This improved getJSON function now expects to see the following more flexible type of input from a sheet cell -

=getJSON(epic, field)

where 'field' can be one of the three following current options, but which can be easily modified if more are then required in future -

price
dividend_rate
dividend_yield

so to get the share-price for an EPIC in cell C13, we'd use the following formula -

=getJSON(C13, "price")

and to get the other two dividend-related fields, we'd use -

=getJSON(C13, "dividend_rate")

and

=getJSON(C13, "dividend_yield")


Checking the above works as expected for the already-mentioned AT&T and Imperial Brands -



Source - my own Google Sheet

Verification links provided below to the Yahoo Finance pages for the above two shares, that currently corroborate the above returned fields -

AT&T - https://finance.yahoo.com/quote/t

Imperial Brands - https://finance.yahoo.com/quote/IMB.L/


For completeness, and just so you can see the two different v10 JSON modules stacked on top of each other in a browser tab, here's a URL link to the Yahoo v10 API information for Imperial Brands, with the above two 'price' and 'summaryDetail' modules being used -

https://query2.finance.yahoo.com/v10/finance/quoteSummary/imb.l?modules=price%2CsummaryDetail

and here's a link that lists all the different available v10 JSON modules that might contain different epic-related information fields, where hopefully the above process can then be simply modified to capture any broader field-related requirements -

https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working

Cheers,

Itsallaguess

[ReformedCharacter]

I'm not sure about that - a week ago I didn't know much at all about Google Sheets and it's a really quite powerful scripting language, so getting this far has certainly been an interesting learning curve.

I've had another play with the earlier v10 API process, because there's a number of different JSON modules available from Yahoo that contain different share-related fields, so I thought I'd try to improve it to take that into account for a couple of different modules, with enough flexibility in the scripting code that will then allow it to expand into broader areas including other modules if that's then needed...

The improved getJSON scripting function below will now stack two different JSON modules, one being the already-used 'summaryDetail', which contains dividend-related information, and now also the 'price' module, which means we can also then get related share-price information as well -


This improved getJSON function now expects to see the following more flexible type of input from a sheet cell -

=getJSON(epic, field)

where 'field' can be one of the three following current options, but which can be easily modified if more are then required in future -

price
dividend_rate
dividend_yield

so to get the share-price for an EPIC in cell C13, we'd use the following formula -

=getJSON(C13, "price")

and to get the other two dividend-related fields, we'd use -

=getJSON(C13, "dividend_rate")

and

=getJSON(C13, "dividend_yield")


Checking the above works as expected for the already-mentioned AT&T and Imperial Brands -



Source - my own Google Sheet

Verification links provided below to the Yahoo Finance pages for the above two shares, that currently corroborate the above returned fields -

AT&T - https://finance.yahoo.com/quote/t

Imperial Brands - https://finance.yahoo.com/quote/IMB.L/


For completeness, and just so you can see the two different v10 JSON modules stacked on top of each other in a browser tab, here's a URL link to the Yahoo v10 API information for Imperial Brands, with the above two 'price' and 'summaryDetail' modules being used -

https://query2.finance.yahoo.com/v10/finance/quoteSummary/imb.l?modules=price%2CsummaryDetail

and here's a link that lists all the different available v10 JSON modules that might contain different epic-related information fields, where hopefully the above process can then be simply modified to capture any broader field-related requirements -

https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working

Cheers,

Itsallaguess

Thanks! Last night I was looking at the modules to see how to get the price and yield...

RC

[Ashstevens848]

Amazing ! Thanks Itsallaguess