Guide
Keep a stock watchlist in Google Sheets without IMPORTXML
The usual way to get Yahoo Finance prices into a spreadsheet is an IMPORTXML formula, and it keeps breaking: Yahoo changes its page layout, the formula returns #N/A, and funds or foreign tickers never worked in the first place. GOOGLEFINANCE covers fewer exchanges and has no company profiles. This guide fills a sheet with one row per ticker every weekday: price, change, valuation figures and the company’s sector and industry, for any exchange Yahoo Finance covers.
This guide uses our Yahoo Finance Scraper on the Apify Store. Every step works with a free Apify account.
What you’ll end up with
A sheet that gains one row per ticker each day:
| scrapedAt | symbol | name | price | changePercent | priceTime | marketCap | trailingPE | dividendYieldPercent | sector |
|---|---|---|---|---|---|---|---|---|---|
| 2026-11-20 | AAPL | Apple Inc. | … | … | … | … | … | … | Technology |
| 2026-11-20 | VOD.L | Vodafone Group | … | … | … | … | … | … | Communication Services |
Prices are at least 15 minutes old, and priceTime says exactly when each one is from. It’s for watchlists and research, not for trading.
1. Write your tickers the way Yahoo Finance does
- US stocks and ETFs:
AAPL,BRK-B,SPY - Other exchanges add a suffix:
VOD.L(London),SAP.DE(Xetra),7203.T(Tokyo),RY.TO(Toronto),RELIANCE.NS(India) - Indices:
^GSPC,^FTSE; currencies:EURUSD=X; crypto:BTC-USD - Funds use Yahoo’s own code, such as
0P00016MCQ.F. Search the fund on finance.yahoo.com and copy the code from its page address.
2. Set up the run
- Open Yahoo Finance Scraper on Apify (its Apify Store page) and click Try for free. A free Apify account comes with monthly credit.
- Paste your tickers into Tickers, one per line. Leave Include company profile on: it’s included in the price.
- Click Start. A 50-ticker run takes under a minute. Open the Delayed quotes view to check it.
- Click Save as a new task and name it, for example “Watchlist”.
Cost: one row per ticker with a price. A 200-ticker watchlist every weekday is about 4,400 rows a month. The Actor’s Pricing tab has the price per row; tickers that aren’t found are free.
3. Run it on a schedule
In Apify Console, open Schedules → Create new and add your task:
- End-of-day snapshot: weekdays, 30 minutes after the market closes (for New York, 4:30 pm Eastern). The prices are then the day’s closing prices.
- During the day: every hour while the market is open. Each row’s
priceTimeshows the time of its price, which is at least 15 minutes earlier.
4. Send the rows to Google Sheets or Excel
- Google Sheets: on the task, open Integrations and add the Google Sheets integration. Choose “append” to keep history, or “replace” if you only want today’s figures.
- Excel: in Excel, use Data → From Web with the CSV address of the task’s last run (Apify Console → the task → API shows it). Power Query then refreshes it on demand. The address includes your API token, so keep the workbook private.
- API: the same address gives JSON for your own scripts.
5. Useful formulas
Where the price sits in its 52-week range (0% at the low, 100% at the high):
= (price - fiftyTwoWeekLow) / (fiftyTwoWeekHigh - fiftyTwoWeekLow)- Above or below the 200-day average:
= price / twoHundredDayAverage - 1 - Upcoming events: sort by
exDividendDateorearningsDateto see what’s coming this week. - London prices are usually in pence (
currencyisGBp). Divide by 100 for pounds.
Going further
- Enrich a list of companies: run tickers once with the profile on to get each one’s sector, industry, country, employees, website and business summary.
- ETFs and funds: rows include net assets, NAV, yield and expense ratio where Yahoo Finance has them.
- Values withheld during trading: while a session is under 15 minutes old, some intraday values (today’s high, low and volume) stay empty, and
withheldFieldsnames them. They fill in on the next run.
The Actor reads the public Yahoo Finance quote pages only, never real-time data, and collects company facts, not people: officers, executives and their pay are never read.
Need data from another public source, or a different setup? Get in touch.