Formulas & Functions•7 min read
Google Sheets GOOGLEFINANCE Function: The Complete Guide
Track stocks and currencies in Google Sheets with GOOGLEFINANCE. Build a live portfolio tracker — no API keys needed.
SheetHub••7 min
You check stock prices every morning. You type them into a spreadsheet. By lunch, they're outdated. Then you do it again the next day.
What if your spreadsheet updated itself — with live market data, no copy-pasting, no API keys, and no paid tools?
That's exactly what the Google Sheets GOOGLEFINANCE function does.
GOOGLEFINANCE is Google Sheets' built-in financial data source that pulls real-time and historical data directly into your cells. Stock prices, currency exchange rates, mutual fund performance, and market indices all come from a single formula.
The best part? There's no Excel equivalent. This is Google Sheets exclusive. If you manage investments, track currencies for a business, or just want a live dashboard of your portfolio, GOOGLEFINANCE saves you hours of manual data entry.
No setup required. No API registration. Just type the formula and your data appears.
The function has a flexible syntax depending on what you need:
That single formula gives you Apple's latest trading price.
A live price tracker is where this gets practical. Set up columns for tickers, current price, daily change, and percentage change:
Every time you open the sheet, prices refresh. Add conditional formatting (green for positive change, red for negative), and you've got a live market dashboard in under two minutes.
Use them together for a full stock snapshot:
Need more than a snapshot? GOOGLEFINANCE returns historical price data over any date range:
This returns a table with dates and closing prices from January to July 2026. From there you can calculate moving averages, volatility, or year-to-date returns.
For analyzing trends over time, use QUERY to filter and aggregate historical stock data — a great complement to the raw GOOGLEFINANCE output.
With a start and end date, GOOGLEFINANCE returns a two-column table (Date and Close). The INDEX function pulls just the Close column (the second column), so SPARKLINE plots only the prices, not the dates. This creates a mini line chart of the stock's 30-day performance inside a single cell. We cover this in detail in our guide on how to add SPARKLINE mini-charts to visualize stock performance. Prices are just the start, though — the same function also converts currencies.
The format is source currency + target currency. For example, USDGBP converts US Dollars to British Pounds, EURUSD converts Euros to US Dollars, and USDEUR converts US Dollars to Euros:
For small business owners importing or exporting, this is the feature you'll use the most. Build a live currency converter:
The rates update automatically during market hours. No more Googling exchange rates before sending an invoice.
Now let's put it all together: a portfolio tracker that shows current value, daily P&L, and visual performance for every holding.
To handle many tickers efficiently, use ARRAYFORMULA with GOOGLEFINANCE for multi-ticker tracking across your entire portfolio.
Add a totals row:
That's a live portfolio dashboard, built entirely with formulas. You never type a price in by hand.
Tickers must be on major exchanges: NYSE, NASDAQ, or London Stock Exchange. GOOGLEFINANCE won't work for OTC stocks or cryptocurrencies.
Before building a whole tracker around a ticker, test it in a single cell:
The correct ticker returns Apple's current price. A misspelled ticker, like
GOOGLEFINANCE is powerful, but it has constraints worth knowing:
Now that you understand how GOOGLEFINANCE works, here's how to describe your needs to an AI assistant (ChatGPT, Gemini, or Claude):
The AI will generate the formulas you need based on the example above — just replace the tickers and adjust the layout to match your sheet.
GOOGLEFINANCE is the most powerful Google Sheets-exclusive function for financial data. With one formula you can:
What Is GOOGLEFINANCE?
GOOGLEFINANCE Syntax
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])- ticker — The stock symbol (e.g., "AAPL" for Apple, "GOOGL" for Alphabet)
- attribute (optional) — What data point you want: price, high, low, volume, marketcap, and more
- start_date / end_date (optional) — For historical data ranges
- interval (optional) — "DAILY" or "WEEKLY" for historical frequency
=GOOGLEFINANCE("AAPL")Real-Time Stock Price Tracker
| Ticker | Company | Current Price | Change | Change % |
|---|---|---|---|---|
| AAPL | Apple | =GOOGLEFINANCE(A2) | =GOOGLEFINANCE(A2, "change") | =GOOGLEFINANCE(A2, "changepct") |
| GOOGL | Alphabet | =GOOGLEFINANCE(A3) | =GOOGLEFINANCE(A3, "change") | =GOOGLEFINANCE(A3, "changepct") |
| MSFT | Microsoft | =GOOGLEFINANCE(A4) | =GOOGLEFINANCE(A4, "change") | =GOOGLEFINANCE(A4, "changepct") |
Available Attributes
Beyond the current price, GOOGLEFINANCE offers a full set of attributes:| Attribute | Returns |
|---|---|
"price" | Current stock price (default) |
"high" | Today's high |
"low" | Today's low |
"open" | Today's open |
"close" | Previous close |
"volume" | Trading volume |
"marketcap" | Market capitalization |
"pe_ratio" | Price-to-earnings ratio |
"change" | Price change from previous close |
"changepct" | Percentage change |
=GOOGLEFINANCE("AAPL", "marketcap")Historical Data for Analysis
=GOOGLEFINANCE("AAPL", "close", DATE(2026, 1, 1), DATE(2026, 7, 27), "DAILY")Building a 30-Day Performance Sparkline
Drop a SPARKLINE next to each ticker for instant visual context:=SPARKLINE(INDEX(GOOGLEFINANCE(A2, "close", TODAY()-30, TODAY()),, 2))Currency Conversion
=GOOGLEFINANCE("USDGBP")
=GOOGLEFINANCE("EURUSD")
=GOOGLEFINANCE("USDEUR")| Currency Pair | Rate |
|---|---|
| USD to EUR | =GOOGLEFINANCE("USDEUR") |
| USD to GBP | =GOOGLEFINANCE("USDGBP") |
| USD to JPY | =GOOGLEFINANCE("USDJPY") |
Portfolio Tracker
| Ticker | Shares | Current Price | Total Value | Day Change | 30-Day Trend |
|---|---|---|---|---|---|
| AAPL | 50 | =GOOGLEFINANCE(A2) | =B2*C2 | =GOOGLEFINANCE(A2,"change") | =SPARKLINE(INDEX(GOOGLEFINANCE(A2,"close",TODAY()-30,TODAY()),,2)) |
| GOOGL | 30 | =GOOGLEFINANCE(A3) | =B3*C3 | =GOOGLEFINANCE(A3,"change") | =SPARKLINE(INDEX(GOOGLEFINANCE(A3,"close",TODAY()-30,TODAY()),,2)) |
| MSFT | 40 | =GOOGLEFINANCE(A4) | =B4*C4 | =GOOGLEFINANCE(A4,"change") | =SPARKLINE(INDEX(GOOGLEFINANCE(A4,"close",TODAY()-30,TODAY()),,2)) |
=SUM(D2:D4) | =AVERAGE(E2:E4) |
|---|
Common Errors and Troubleshooting
| Error | Cause | Fix |
|---|---|---|
#N/A | Invalid ticker symbol | Double-check the ticker on Google Finance |
#N/A | Ticker not traded today | Markets closed — check during trading hours |
| Rate limit | Too many GOOGLEFINANCE calls | Space queries 1-2 minutes apart |
| Wrong value | Typo in attribute name | Use the exact attribute list above |
| Old data | Ticker delisted or merged | Find the new ticker or remove the row |
=GOOGLEFINANCE("AAPL")=GOOGLEFINANCE("APPL"), returns #N/A — fix the symbol before wiring it into your dashboard.
Limitations
- 15-20 minute delay — Prices aren't truly real-time. For day trading, use a dedicated broker platform.
- Rate limits — Google imposes limits on concurrent queries. Large portfolios may need staggered refresh.
- No options or futures — Only stocks, ETFs, mutual funds, indices, and currencies.
- Major exchanges only — NYSE, NASDAQ, LSE. No OTC, crypto, or smaller exchanges.
AI Prompt Callout
AI Prompt Template
I have a list of stock tickers in column A (AAPL, GOOGL, MSFT). I need a portfolio tracker that shows current price, daily change, percentage change, and a SPARKLINE mini-chart for 30-day performance of each stock. Write the GOOGLEFINANCE formulas for each column and include a total portfolio value row.
SheetHub's Take
- Track live stock prices and portfolio value
- Convert currencies in real time
- Analyze historical trends over any date range
- Build visual dashboards with SPARKLINE
=GOOGLEFINANCE("AAPL"). Then add five more tickers, throw in a SPARKLINE column, and watch your portfolio come to life. You don't need API keys, imports, or paid tools — just Google Sheets doing what it does best.Article Topics
Recommended Next Reading
Excel
=EXCEL(...)Excel ROUND Function: MROUND, CEILING & FLOOR Guide
Explore ↗
Excel
=EXCEL(...)Excel LAMBDA Recursive Loops: Advanced Calculations
Explore ↗
Excel
=EXCEL(...)Excel SWITCH Function: Simplify Nested IF Logic
Explore ↗
Share this tutorial
Discussion & Community
Share questions, tips, or edge-cases about this spreadsheet formula.