Hey George, sorry to hear your IMPORTXML formula stopped working—Yahoo Finance regularly tweaks their page structure and rolls out anti-scraping measures, which is almost certainly the issue here. Let's go through three reliable fixes to get your stock price data flowing again:

1. Update the XPath to Match Yahoo's Current Page Structure #

Yahoo has shifted to using <fin-streamer> tags for real-time price data instead of the old div-based structure your original formula targeted. Try replacing your formula with this updated version:

=INDEX(IMPORTXML("https://finance.yahoo.com/quote/SGLN.L","//fin-streamer[@data-field='regularMarketPrice']"),1)

This XPath targets the specific streamer element that holds the regular market price. If this still fails, it's likely Yahoo's anti-scraping is blocking Google Sheets' default request headers.

2. Use a Custom Google Apps Script (More Reliable for Anti-Scraping) #

Google Sheets' built-in IMPORTXML can't customize request headers, which makes it easy for Yahoo to block. A custom Apps Script can mimic a browser request and extract the price directly from the page's JSON data:

1. Open your Google Sheet, go to Extensions > Apps Script
2. Replace the default code with this script:

function getYahooPrice(ticker) {
  const url = `https://finance.yahoo.com/quote/${ticker}`;
  // Mimic a desktop browser request to avoid being blocked
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36'
    }
  });
  const html = response.getContentText();
  // Extract the regular market price from the embedded JSON
  const priceMatch = html.match(/"regularMarketPrice":{"raw":(\d+\.?\d*)/);
  return priceMatch ? parseFloat(priceMatch[1]) : 'Price unavailable';
}

3. Save the script (name it something like YahooPriceFetcher)
4. Back in your sheet, use the custom function like this:

=getYahooPrice("SGLN.L")

This method is far more resilient because it bypasses basic anti-scraping checks and uses regex to pull the price from the page's underlying data, rather than relying on fragile HTML structure.

3. Switch to Google Finance (Simplest & Most Stable Option) #

If you don't need to stick with Yahoo Finance, Google's own GOOGLEFINANCE function is a much more reliable alternative—it uses official APIs instead of scraping, so it rarely breaks. For SGLN.L (listed on the London Stock Exchange), use:

=GOOGLEFINANCE("LON:SGLN")

This will return the current market price directly, with no need to worry about page structure changes or anti-scraping blocks.

Any of these should resolve your issue—start with the Google Finance option if simplicity is your priority, or the Apps Script if you need to stay with Yahoo's data.

内容的提问来源于stack exchange，提问作者George Bates