ReformedCharacter wrote:ReformedCharacter wrote:
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 -
pricedividend_ratedividend_yieldso 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 SheetVerification 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/tImperial 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%2CsummaryDetailand 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-workingCheers,
Itsallaguess
