Hi all,
I am attempting to create a portfolio for my financial stocks using Google spreadsheet. I would like to create something like this:
[https://ibb.co/SBDJhgs](https://ibb.co/SBDJhgs)
In this case A2 is where I put the ticker, for example, AAPL stands for Apple. Afterwards, I hope to automatically extract the value of 'Sector" in cell B2 and "Industry" in cell C2. I attempt to create a general formula, so that I can expand the formula for a long list of tickers, instead of creating a unique xpath for every ticker.
The website from which I attempt to receive the data is:
[https://eresearch.fidelity.com/eres...esearch/evaluate/snapshot.jhtml?symbols=AAPL)
When you scroll downstairs, you will be able to see "Sector" and "Industry". I want to extract those values, so in case of apple, this would be 'Information Technology' for sector and 'Technology Hardware, Storage and Peripherals'. See: [https://ibb.co/sCbT801](https://ibb.co/sCbT801)
If I highlight Information technology and copy the x-path I find this:
//\*\[@id="companyProfile"\]/div\[8\]/span/a
So then I try the following formula, but receive an error:
=IMPORTXML("[AAPL | Stock Snapshot - Fidelity";"//\*\[@id="companyProfile"\]/div\[8\]/span/a](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL";"//*[@id="companyProfile"]/div[8]/span/a)")
However, this one seems to be working:
=IMPORTXML("[AAPL | Stock Snapshot - Fidelity";"/html/body/table/tbody/tr/td\[4\]/table\[2\]/tbody/tr/td\[1\]/div\[3\]/div\[8\]](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL";"/html/body/table/tbody/tr/td[4]/table[2]/tbody/tr/td[1]/div[3]/div[8])")
But it gives me the "sector" AND "information technology" and I only wish to receive the information technology.
Furthermore, I would like it to use this formula ideally, but it also gives back an error:
=IMPORTXML("[Unavailable - Fidelity Investments"&A2](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols="&A2) ";" ""//\*\[@id="companyProfile"\]/div\[8\]/span/a")
Can anyone please give me advice on the matter? Thanks much in advance!
I am attempting to create a portfolio for my financial stocks using Google spreadsheet. I would like to create something like this:
[https://ibb.co/SBDJhgs](https://ibb.co/SBDJhgs)
In this case A2 is where I put the ticker, for example, AAPL stands for Apple. Afterwards, I hope to automatically extract the value of 'Sector" in cell B2 and "Industry" in cell C2. I attempt to create a general formula, so that I can expand the formula for a long list of tickers, instead of creating a unique xpath for every ticker.
The website from which I attempt to receive the data is:
[https://eresearch.fidelity.com/eres...esearch/evaluate/snapshot.jhtml?symbols=AAPL)
When you scroll downstairs, you will be able to see "Sector" and "Industry". I want to extract those values, so in case of apple, this would be 'Information Technology' for sector and 'Technology Hardware, Storage and Peripherals'. See: [https://ibb.co/sCbT801](https://ibb.co/sCbT801)
If I highlight Information technology and copy the x-path I find this:
//\*\[@id="companyProfile"\]/div\[8\]/span/a
So then I try the following formula, but receive an error:
=IMPORTXML("[AAPL | Stock Snapshot - Fidelity";"//\*\[@id="companyProfile"\]/div\[8\]/span/a](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL";"//*[@id="companyProfile"]/div[8]/span/a)")
However, this one seems to be working:
=IMPORTXML("[AAPL | Stock Snapshot - Fidelity";"/html/body/table/tbody/tr/td\[4\]/table\[2\]/tbody/tr/td\[1\]/div\[3\]/div\[8\]](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols=AAPL";"/html/body/table/tbody/tr/td[4]/table[2]/tbody/tr/td[1]/div[3]/div[8])")
But it gives me the "sector" AND "information technology" and I only wish to receive the information technology.
Furthermore, I would like it to use this formula ideally, but it also gives back an error:
=IMPORTXML("[Unavailable - Fidelity Investments"&A2](https://eresearch.fidelity.com/eresearch/evaluate/snapshot.jhtml?symbols="&A2) ";" ""//\*\[@id="companyProfile"\]/div\[8\]/span/a")
Can anyone please give me advice on the matter? Thanks much in advance!
