[SOLVED] How to parse and capture Dividend Value from <you tell me> in Google Sheets

How to parse and capture Dividend Value from <you tell me> in Google Sheets

Objective: Capture $ or % dividend value for a given stock.

Using Google Sheets, the following worked perfectly fine =SPLIT(INDEX(IMPORTHTML(concatenate("https://finance.yahoo.com/quote/",G14),"table",2),6,2)," ") where G14 is a stock symbol, like AAPL

The above worked well until last week. At the time of this writing this gives an error

Could not fetch url: https://finance.yahoo.com/quote/AAPL

Solution

The page is built by javascript on the client side, not the server side. This means that you cannot now use the importhtml or importxml function. However, the data is imported into a big json called 'root.App.main' which you can analyze and retrieve the information you need, like:

function dividend() {
  var code = 'AAPL'
  var url='https://finance.yahoo.com/quote/'+code
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)
  var dividendRate = data.context.dispatcher.stores.QuoteSummaryStore.summaryDetail.dividendRate.raw
  Logger.log(dividendRate)
}