1

I'm trying to use IMPORTHTML or IMPORTXML to get price value from https://finance.yahoo.com/quote/GSPX.L/

or in fact from any site that will give me a relatively up-to-date number.

I used to be able to get the value using the GOOGLEFINANCE formula, but that's recently stopped working for a few tickers (not all!)

I've managed to get IMPORTHMTL to work in the past with morningstar.co.uk for some index funds, but the page layout is different with GSPX.L. I've tried using the Full XPath with IMPORTXML but that doesn't seem to work and as far as I understand it IMPORTXML is extremely finnicky.

I just need this one number! Any insight into how to get it, but ideally something more systematic that I can use in the future?

booms
  • 23
  • 5

1 Answers1

1

If I am not mistaken, what you want to get is the 7.03 price value from that page. I think there is something on the page blocking Excel and Google Sheets formulas from running on it so it would not be possible to use that site you provided.

Instead I was able to get it from markets.ft.com. You can use the following formula to get that exact same number:

=IMPORTXML("https://markets.ft.com/data/etfs/tearsheet/summary?s=GSPX:LSE:GBP", "/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[1]/span[2]")

enter image description here

References:

Fernando Lara
  • 2,263
  • 2
  • 4
  • 14
  • 1
    If you want to retrieve the info from strictly from that specific page only it may be possible but that would require a script and I do not know if that is something you would like to use as a workaround. – Fernando Lara May 08 '23 at 14:58
  • That's fantastic @Fernando Lara- that's exactly what I needed. I'll read through the documentation you linked to. I suspected that that Yahoo was somehow being difficult. I've found morningstar is working at the moment. How could you tell that Yahoo was blocking it? I'd like to be able to understand what to do when I get an #N/A! – booms May 09 '23 at 17:37
  • I found [this post](https://stackoverflow.com/questions/66608232/resource-url-not-found-with-yahoo-finance) useful @booms – Fernando Lara May 09 '23 at 18:30