Google Sheets IMPORTXML函数访问Yahoo Finance URL报错：如何在不使用VBA的情况下获取WBA全职员工数及TTM营收

I’ve run into this exact issue before—Yahoo Finance frequently tweaks its page structure, and its anti-scraping measures often block direct IMPORTXML requests from Google Sheets. Since you want to avoid VBA, here are several reliable workarounds to get WBA’s full-time employee count and TTM revenue:

1. Use Google’s Built-in GOOGLEFINANCE Function (Most Stable) #

This is the best option because it’s officially integrated with Google Sheets, so you won’t hit anti-scraping blocks or deal with broken XPaths. Try these formulas:

• Full-time employees:

  =GOOGLEFINANCE("WBA", "employees")
  

• TTM Revenue:
  Google Finance doesn’t have a direct "TTM revenue" attribute, but you can calculate it by summing the last four quarterly revenues. First, pull quarterly data:

  =GOOGLEFINANCE("WBA", "revenue", TODAY()-365, TODAY(), "QUARTERLY")
  

  Then use SUM on the revenue column to get the TTM total. Alternatively, use this single formula to combine the steps:

  =SUM(INDEX(GOOGLEFINANCE("WBA", "revenue", TODAY()-365, TODAY(), "QUARTERLY"),,2))
  

2. Update Your XPath (If You Still Want to Use IMPORTXML) #

Yahoo’s page structure changes often, so your old XPath is likely invalid. To get a working XPath:

• Go to WBA’s Profile page on Yahoo Finance
• Right-click the full-time employee number > Inspect
• In the DevTools, right-click the highlighted element > Copy > Copy full XPath
• Paste this new XPath into your IMPORTXML formula.

Note: If the data loads dynamically (via JavaScript), IMPORTXML won’t be able to grab it—Yahoo loads many profile details after the initial page load, which is why you get the "resource not found" error. In that case, skip to option 3.

3. Combine IMPORTHTML with INDEX/MATCH #

If GOOGLEFINANCE lacks the exact field you need, use IMPORTHTML to pull the profile table, then extract the row you want:

=INDEX(IMPORTHTML("https://finance.yahoo.com/quote/WBA/profile", "table", 1), MATCH("Full Time Employees", IMPORTHTML("https://finance.yahoo.com/quote/WBA/profile", "table", 1), 0), 2)

This grabs the first table on the profile page, finds the "Full Time Employees" row, and returns the value in the second column.

4. Extract Data with REGEXEXTRACT + IMPORTXML #

If the data is in a text block instead of a table, use IMPORTXML to pull the relevant paragraph, then regex to extract the number:

=REGEXEXTRACT(IMPORTXML("https://finance.yahoo.com/quote/WBA/profile", "//p[contains(text(), 'Full Time Employees')]"), "Full Time Employees\s*([\d,]+)")

Adjust the regex pattern if the text formatting on Yahoo’s page changes.

Why Your Old Market Cap Formula Worked #

The market cap section on Yahoo’s main quote page is statically loaded, so IMPORTXML can access it easily. Employee counts and TTM revenue, however, are often loaded dynamically or placed in sections that block direct scraping requests.

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