Using importXML function to get sector data of stock

1
votes

I am trying to use importXML to get an automatic sector update and industry for my stocks in google sheets. In this case, in cell A5, is the name of the ticker. Normally, I would expect the following website:

https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL

Where instead of AAPL, I wish to insert "A5"

Furthermore, I wish to receive the "Information Technology" in one formula and "Technology Hardware, Storage & Peripherals" in another subheading.

The formula I have created is the following:

=ALS(ISLEEG(A5);"";importxml("https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=​"&A5;"/html/body/table/tbody/tr/td[4]/table[2]/tbody/tr/td[1]/div[3]/div[8]"))

It is however, not working, can someone please help me?

Thanks much in advance.

1
google-sheetsgoogle-sheets-formula

1 Answers

0
votes

I believe your goal as follows.

  • You want to retrieve the values of Information Technology and Technology Hardware, Storage & Peripherals.
  • In the case of https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL, you want to retrieve, the values of Sector (GICS®) and Industry (GICS®).

In this case, I thought that the values might be able to be retrieved by a xpath. The sample xpath is as follows.

Sample xpath:

//div[@class='sub-heading']//h3[../span/a[text()='Information Technology' or text()='Technology Hardware, Storage & Peripherals']]

Modified formula:

=IMPORTXML(A1;"//div[@class='sub-heading']//h3[../span/a[text()='Information Technology' or text()='Technology Hardware, Storage & Peripherals']]")
  • In this case, the URL of https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL is put in the cell "A1".

Result:

enter image description here

  • When you want to put the values to the columns, in this case, please use TRANSPOSE.

Note:

  • This xpath is for the URL of https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL. So when you change the URL, the values you expect might not be able to be retrieved. So please be careful this.

Reference: