google sheets importxml resource at url not found - Yahoo Finance

user_3105233 0
Viewed 117

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!

google-sheets-formula google-sheets
1 Answers

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)
}
user_150193800
Related