IMPORTXML Formula in Google Sheets-CodePudding

I'm having trouble pulling the financial data from 'Book Value Per Share' using Google Sheet's IMPORTXML function

=ImportXML("https://stockanalysis.com/stocks/bby/statistics/", [Xpath])

Xpath: //*[@id="main"]/div[2]/div[2]/div[4]/table/tbody/tr[6]/td[1]/span

CodePudding user response：

You can't use import xml since the page is buit using javascript. You can parse the json contained in the web page

=bookValuePerShare(A1)

with custom function

function bookValuePerShare(url) {
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.split('<script id="__NEXT_DATA__" type="application/json">')[1].split('</script>')[0]
  var data = JSON.parse(jsonString)
  result = data.props.pageProps.data.balance.data[5][2]
  return result
}

CodePudding user response：

You can alter your xpath as follows and use the relationship of elements and the text descriptor:

=ImportXML("https://stockanalysis.com/stocks/bby/statistics/", "//span[text()='Book Value Per Share']/parent::td/following-sibling::td[1]")