CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user3105233
user3105233

Reputation: 71

google sheets importxml resource at url not found - Yahoo Finance

I tried to get Walgreen's number of full-time employees from Yahoo Finance using importxml like so:

=importxml("https://finance.yahoo.com/quote/WBA/profile", "/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/section/div[1]/div/div/p[2]/span[6]/span")

enter image description here

I have used the function successfully in getting other figures from Yahoo Finance. Example (market cap):

=mid(importxml("https://finance.yahoo.com/quote/WBA", "/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[1]/td[2]/span"),1,6)+0

But with the number of employees (and, by the way, also the trailing twelve months' (ttm) revenues) - I get this error.

Without VBA, with which I am not familiar, how can this be fixed?

Thanks!

Upvotes: 1

Views: 297

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

This site is built client side by javascript, not server side. Therefore, native functions are inoperative.

You have to extract the json inside the source and parse it.

The object is named root.App.main inside the source.

To get employees for instance

function fullTimeEmployees(url='https://finance.yahoo.com/quote/WBA/profile'){
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/root.App.main = ([\s\S\w]+?);\n/)
  if (!jsonString || jsonString.length == 1) return;
  var data = JSON.parse(jsonString[1].trim())
  Logger.log(data.context.dispatcher.stores.QuoteSummaryStore.assetProfile.fullTimeEmployees)
}

Upvotes: 1

Related Questions