Google Sheets Stock Price Tracker: GOOGLEFINANCE Formula Cheat Sheet
Why Bother Tracking Stocks in a Spreadsheet?
If you've ever kept a portfolio in Google Sheets the manual way, you know the routine. Wake up, open the laptop, open the brokerage, copy a price, switch tabs, paste, switch back, copy the next one. Fifteen tickers later you finally get to your coffee.
It's not just tedious — it's where small mistakes hide. One mistyped digit and you spend a week thinking you're down 30% before realizing it was just a fat-fingered "0".
GOOGLEFINANCE basically deletes that whole routine. It pulls live prices straight off the web into your sheet, so the numbers are always reasonably current and the only thing you touch by hand is share counts and buy prices.
Below: how the function actually works, the ticker formats for Taiwan and US stocks, and how to glue it into a portfolio tracker that updates itself.
GOOGLEFINANCE Basics: Syntax & Parameters
GOOGLEFINANCE is a built-in Google Sheets function that pulls stock prices, fund data, and currency quotes. Here's the syntax:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
Parameter Breakdown
ticker (required) The stock code. Format depends on exchange:
- Taiwan stocks:
TPE:code(e.g.,TPE:2330= TSMC) - US stocks: exchange prefix + code (e.g.,
NASDAQ:AAPL= Apple) - Cryptocurrencies:
CURRENCY:pair(e.g.,CURRENCY:BTCUSD= Bitcoin in USD; works for major coins, not officially documented)
attribute (optional, default "price") What you want to retrieve:
"price"— Current stock price (most common)"high"— Today's high"low"— Today's low"open"— Opening price"close"— Yesterday's closing price"volume"— Trading volume"pe"— Price-to-earnings ratio"market_cap"— Market capitalization
start_date & end_date (optional)
For historical prices, specify a date range. Format: DATE(2026,3,1) or "2026-03-01"
interval (optional)
Data frequency. 1 = daily, 7 = weekly.
Most Common Use Cases
Get current price:
=GOOGLEFINANCE("TPE:2330")
Get P/E ratio:
=GOOGLEFINANCE("TPE:2330", "pe")
Get today's high:
=GOOGLEFINANCE("TPE:2330", "high")
Taiwan Stock Examples
Taiwan Stock Ticker Format
Taiwan listed stocks use the format TPE:XXXX, where XXXX is a four-digit code. You can cross-check the code against Taiwan Stock Exchange (TWSE) listing information.
Common examples:
| Company | Code | GOOGLEFINANCE Format |
|---|---|---|
| TSMC | 2330 | TPE:2330 |
| Foxconn (Hon Hai) | 2317 | TPE:2317 |
| MediaTek | 2454 | TPE:2454 |
| Bank of Taiwan | 2880 | TPE:2880 |
| Cathay Financial | 2882 | TPE:2882 |
| Chunghwa Telecom | 1326 | TPE:1326 |
Real Formula Examples
Try these in your Google Sheet:
Get TSMC's current price:
=GOOGLEFINANCE("TPE:2330")
Returns: TSMC's current price (e.g., 990)
Get TSMC's P/E ratio:
=GOOGLEFINANCE("TPE:2330", "pe")
Returns: P/E ratio (e.g., 28.5)
Get Foxconn's trading volume:
=GOOGLEFINANCE("TPE:2317", "volume")
Returns: Volume in shares (e.g., 12345600)
US Stock Examples
US Stock Ticker Format
US stocks require the exchange prefix:
NASDAQ:— Nasdaq (tech-heavy)NYSE:— New York Stock Exchange (blue chips)
Common examples:
| Company | Ticker | Exchange | GOOGLEFINANCE Format |
|---|---|---|---|
| Apple | AAPL | NASDAQ | NASDAQ:AAPL |
| Microsoft | MSFT | NASDAQ | NASDAQ:MSFT |
| Tesla | TSLA | NASDAQ | NASDAQ:TSLA |
| TSMC ADR | TSM | NYSE | NYSE:TSM |
| Nvidia | NVDA | NASDAQ | NASDAQ:NVDA |
| GOOGL | NASDAQ | NASDAQ:GOOGL |
Real Formula Examples
Get Apple's stock price:
=GOOGLEFINANCE("NASDAQ:AAPL")
Get Tesla's P/E ratio:
=GOOGLEFINANCE("NASDAQ:TSLA", "pe")
Get Microsoft's daily high:
=GOOGLEFINANCE("NASDAQ:MSFT", "high")
Building Your Own Portfolio Tracker
Now that the syntax is out of the way, here's how to actually wire up a working tracker.
If you only need to verify the holdings registered in your name, you can also cross-check them with the Taiwan Depository & Clearing Corporation's e-Passbook.
Table Structure
Set up these columns:
| Stock Name | Ticker | Shares | Buy Price | Current Price (Auto) | Market Value | Cost Basis | Gain/Loss $ | Gain/Loss % |
|---|---|---|---|---|---|---|---|---|
| TSMC | TPE:2330 | 10 | 800 | ? | ? | 8,000 | ? | ? |
| Foxconn | TPE:2317 | 5 | 180 | ? | ? | 900 | ? | ? |
| Apple | NASDAQ:AAPL | 2 | 150 | ? | ? | 300 | ? | ? |
Formula Setup
Current Price column (assume column E):
=GOOGLEFINANCE(B2)
Automatically fetches the price for the ticker in cell B2.
Market Value column (assume column F):
=C2*E2
Shares × Current Price = What your position is worth today.
Cost Basis column (assume column G):
=C2*D2
Shares × Buy Price = How much you originally invested.
Gain/Loss $ column (assume column H):
=F2-G2
Market Value − Cost Basis = Profit or loss in dollars.
Gain/Loss % column (assume column I):
=(H2/G2)*100
(Gain/Loss $ ÷ Cost Basis) × 100 = Return percentage.
Complete Worked Example
Say you hold:
- 10 shares of TSMC, bought at 800
- Current price: 990
| Item | Value |
|---|---|
| Shares | 10 |
| Buy Price | 800 |
| Current Price | 990 (auto-fetched) |
| Market Value | 9,900 (10 × 990) |
| Cost Basis | 8,000 (10 × 800) |
| Gain/Loss $ | 1,900 (9,900 − 8,000) |
| Gain/Loss % | 23.75% (1,900 ÷ 8,000 × 100) |
Once the formulas are in, prices refresh on their own and your returns recalculate. The only thing you actually touch is share count when you buy or sell.
Things That Will Catch You Out
A few quirks worth knowing before you build something serious on top of it:
The data isn't truly live. It's officially documented as delayed by up to 20 minutes, with no guarantee of real-time data. If you're a day trader watching the second-by-second tape, this isn't your tool.
Crypto support isn't officially documented. The CURRENCY:BTCUSD-style currency-pair syntax works for major coins like BTC and ETH, but less common coins and historical data often come back #N/A — don't treat it as a stable source.
Only TWSE-listed stocks work. OTC, emerging-board, and unlisted shares all come back blank.
No internet, no update. GOOGLEFINANCE pulls live, so an offline sheet just shows whatever was cached on the last refresh.
Tickers are picky. Forget the colon (TPE2330) or use the wrong case and you get #N/A or #VALUE! straight away.
When You've Outgrown GOOGLEFINANCE
Sooner or later most people hit something the function can't do. At that point, there are usually three paths:
- Use IMPORTXML for public web pages — useful for small, occasional lookups, but a page redesign can break the formula and make maintenance less predictable than manual updates.
- Connect a brokerage API — gives you richer holdings and quote data and lets you choose where it lives; the tradeoff is handling code, API limits, and key security.
- Use a third-party tool — let a service handle quotes, FX, and visualisation, including tools such as WalletMap that use a spreadsheet as the source of truth. Check supported markets, refresh frequency, and actual data storage before choosing one.
Where to Start
GOOGLEFINANCE is genuinely useful. If you're tracking Taiwan and US stocks and that's it, a few formulas honestly get the whole job done.
The flow is short:
- Open a Google Sheet
- Create a table (ticker, shares, buy price)
- Use
=GOOGLEFINANCE("ticker")in the Current Price column - Add simple formulas for gains/losses
- Done. It updates automatically.
If your portfolio gets messier — crypto, multiple currencies, better visuals — or you just don't feel like babysitting a spreadsheet on weekends, choose an automation option that matches your needs. Check the data source, refresh frequency, supported markets, and storage model before comparing feature counts.
Either way, the move that matters is starting. Untracked assets feel bigger than they are and smaller than they are at the same time — until you can actually see them.
Further reading
- Step-by-step: How to Track Your Investments (guide)
- Track Stocks and Crypto in One Dashboard
- Why Track Your Assets in Google Sheets?