Skip to main content
Back to HomeReturn Home
More Menu
Reading ListGanti ke TerangSearch
Reading List

Queue · 0 items

Your reading list is empty. Save articles to read them later.

Start Reading
ESCto close
↑↓to navigate

Google Sheets GOOGLEFINANCE Function: The Complete Guide

SheetHub7 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.

What Is GOOGLEFINANCE?

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.

GOOGLEFINANCE Syntax

The function has a flexible syntax depending on what you need: 
=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
The simplest call returns the current stock price: 
=GOOGLEFINANCE("AAPL")
That single formula gives you Apple's latest trading price.

Real-Time Stock Price Tracker

A live price tracker is where this gets practical. Set up columns for tickers, current price, daily change, and percentage change:
TickerCompanyCurrent PriceChangeChange %
AAPLApple=GOOGLEFINANCE(A2)=GOOGLEFINANCE(A2, "change")=GOOGLEFINANCE(A2, "changepct")
GOOGLAlphabet=GOOGLEFINANCE(A3)=GOOGLEFINANCE(A3, "change")=GOOGLEFINANCE(A3, "changepct")
MSFTMicrosoft=GOOGLEFINANCE(A4)=GOOGLEFINANCE(A4, "change")=GOOGLEFINANCE(A4, "changepct")
 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.

Available Attributes

Beyond the current price, GOOGLEFINANCE offers a full set of attributes:
AttributeReturns
"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
 Use them together for a full stock snapshot: 
=GOOGLEFINANCE("AAPL", "marketcap")

Historical Data for Analysis

Need more than a snapshot? GOOGLEFINANCE returns historical price data over any date range: 
=GOOGLEFINANCE("AAPL", "close", DATE(2026, 1, 1), DATE(2026, 7, 27), "DAILY")
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.

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))
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.

Currency Conversion

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: 
=GOOGLEFINANCE("USDGBP")
=GOOGLEFINANCE("EURUSD")
=GOOGLEFINANCE("USDEUR")
For small business owners importing or exporting, this is the feature you'll use the most. Build a live currency converter:
Currency PairRate
USD to EUR=GOOGLEFINANCE("USDEUR")
USD to GBP=GOOGLEFINANCE("USDGBP")
USD to JPY=GOOGLEFINANCE("USDJPY")
 The rates update automatically during market hours. No more Googling exchange rates before sending an invoice.

Portfolio Tracker

Now let's put it all together: a portfolio tracker that shows current value, daily P&L, and visual performance for every holding.
TickerSharesCurrent PriceTotal ValueDay Change30-Day Trend
AAPL50=GOOGLEFINANCE(A2)=B2*C2=GOOGLEFINANCE(A2,"change")=SPARKLINE(INDEX(GOOGLEFINANCE(A2,"close",TODAY()-30,TODAY()),,2))
GOOGL30=GOOGLEFINANCE(A3)=B3*C3=GOOGLEFINANCE(A3,"change")=SPARKLINE(INDEX(GOOGLEFINANCE(A3,"close",TODAY()-30,TODAY()),,2))
MSFT40=GOOGLEFINANCE(A4)=B4*C4=GOOGLEFINANCE(A4,"change")=SPARKLINE(INDEX(GOOGLEFINANCE(A4,"close",TODAY()-30,TODAY()),,2))
 To handle many tickers efficiently, use ARRAYFORMULA with GOOGLEFINANCE for multi-ticker tracking across your entire portfolio. Add a totals row:
=SUM(D2:D4)=AVERAGE(E2:E4)
 That's a live portfolio dashboard, built entirely with formulas. You never type a price in by hand.

Common Errors and Troubleshooting

ErrorCauseFix
#N/AInvalid ticker symbolDouble-check the ticker on Google Finance
#N/ATicker not traded todayMarkets closed — check during trading hours
Rate limitToo many GOOGLEFINANCE callsSpace queries 1-2 minutes apart
Wrong valueTypo in attribute nameUse the exact attribute list above
Old dataTicker delisted or mergedFind the new ticker or remove the row
 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: 
=GOOGLEFINANCE("AAPL")
The correct ticker returns Apple's current price. A misspelled ticker, like =GOOGLEFINANCE("APPL"), returns #N/A — fix the symbol before wiring it into your dashboard.

Limitations

GOOGLEFINANCE is powerful, but it has constraints worth knowing:
  • 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.
These limits keep the service free. For most investors checking prices daily or weekly, they're not a problem.

AI Prompt Callout

Now that you understand how GOOGLEFINANCE works, here's how to describe your needs to an AI assistant (ChatGPT, Gemini, or Claude):
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.
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.

SheetHub's Take

GOOGLEFINANCE is the most powerful Google Sheets-exclusive function for financial data. With one formula you can:
  • Track live stock prices and portfolio value
  • Convert currencies in real time
  • Analyze historical trends over any date range
  • Build visual dashboards with SPARKLINE
Open a sheet and try =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.
Topics

Topics in this article

Explore related topics and continue reading similar content.

Google SheetsFormulasGOOGLEFINANCEPersonal FinanceTutorial

Share this article

Discussion

Preparing the comments area...

You Might Also Like