Trying to use IMPORTHTML or IMPORTXML to get price value from https://finance.yahoo.com/quote/GSPX.L/ in Google Sheets
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?
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]")
References:
- How can I use Google Sheets Apps Script to Auto Generate today's date when an edit is made to a given column?
- IsDate ArrayFormula
- how to show instruction with image after install google workspace extension
- Formula to convert matrix to rows in Google Sheets
- Regular expression to extract valid cell references from a spreadsheet formula
- Is Google Sheets less or more accurate than Microsoft Excel?
- google sheet merge two column into one column with prefix words
- Get highest occurring row number of key?
- If checkbox checked set values
- Clarification on using the forEach() function
- Why is this ARRAYFORMULA + VLOOKUP() + REGEXEXTRACT() with multiple conditions not working?
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- Add title row with ARRAYFORMULA in Google Sheets
- Rank based on which column is being sorted by in a Table
- Auto border to a Dynamic Data
- How can I determine if a respondent's email address is unique in a list?
- Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?
- Google Sheets - returning Nth result with multiple matching criteria
- Can a label in a Google Sheets Query be dynamic; can it reference a variable?
- Sum a number of range values; process and add to array
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Why does my Google Service Account not see files shared with it, but can see files created with it?