GOOGLE SHEETS WORKFLOW
How to Build a Stock Portfolio Tracker in Google Sheets
Log every buy, sell and dividend once, pull prices with GOOGLEFINANCE, and let SUMIFS turn the transactions into holdings, average cost, unrealized and realized gains and an allocation view that updates itself.
START WITH THE RIGHT TOOL
Why Google Sheets is a good home for a stock portfolio
A portfolio tracker has one job that a plain list of tickers cannot do: keep the price of what you own current without you typing it. Google Sheets ships that ability as a formula, GOOGLEFINANCE, and adds a few things a personal tracker needsβit lives in the browser, opens on a phone, and shares a single copy with a partner instead of e-mailing versions around.
Quotes are a formula
=GOOGLEFINANCE("NASDAQ:AAPL","price") returns a quote delayed by up to 20 minutes. No add-on, no macro, no copy-paste from a broker page.
FX rates the same way
=GOOGLEFINANCE("CURRENCY:USDEUR") converts a US holding into a euro portfolio value on the same sheet, with the rate stored where it can be audited.
One file, any device
The same spreadsheet opens on a laptop and a phone and can be shared read-only, so a monthly review does not depend on one computer.
Know what it is not
Prices are informational and can lag; some exchanges and funds are not covered; the function returns no dividend history. Those gaps are recorded by hand, which this guide shows.
If you prefer Excel, the structure below is the same one we use in how to track your investment portfolio in Excel; only the price source changes. Not sure which app to use? See Excel vs Google Sheets.
KEEP RAW DATA AUDITABLE
Five tabs, one source of truth
Transactions
One row per buy, sell, dividend, fee or cash movement. Nothing on any other tab is typedβeverything is calculated from here.
Holdings
One row per ticker you have ever owned. SUMIFS formulas compute shares held, average cost, market value and gains from the Transactions tab.
Prices
A small table of ticker, exchange, GOOGLEFINANCE price, name, day change and currency. Keeping the live formulas on their own tab makes a broken quote easy to spot.
Lists
Dropdown values for account, transaction type, sector and currency, plus an FX table. Consistent labels are what make the dashboard trustworthy.
Dashboard
Totals, allocation by holding and sector, a monthly value log and the questions you check at each review. It reads from Holdings and never from Transactions directly.
THE TRANSACTIONS TAB
Record every event once
Put the headers in row 1 and freeze it. Dates go in a real date column, prices and quantities are numbers, and fees are recorded on the row they belong to so cost basis includes them.
| Column | Example | Why it matters |
|---|---|---|
| A Β· Date | 2026-03-04 | Sorting, the monthly log and XIRR all depend on a real date value. |
| B Β· Account | Brokerage, ISA, IRA | Lets one sheet hold several accounts and still report per account. |
| C Β· Type | Buy / Sell / Dividend / Fee / Deposit / Withdrawal | The filter every SUMIFS uses. Dropdown only. |
| D Β· Ticker | AAPL | Must match the Holdings and Prices tabs exactly. Leave blank for deposits and withdrawals. |
| E Β· Shares | 10 | Always positive; the Type column says whether they came in or went out. |
| F Β· Price | 150.00 | Price per share in the securityβs currency. For a dividend, leave blank and use Amount. |
| G Β· Fees | 1.00 | Commission and taxes on this row. Added to cost on a buy, deducted from proceeds on a sell. |
| H Β· Amount | =IF(C2="Buy",-(E2*F2+G2),IF(C2="Sell",E2*F2-G2,...)) | Signed cash effect. Dividends and deposits are typed as positive amounts; fees and withdrawals as negative. |
| I Β· Currency | USD | Drives the FX conversion on Holdings when the portfolio currency differs. |
| J Β· Notes | DRIP, split 4:1, tax lot ID | Anything a future you would need to reconstruct the row. |
CALCULATE, THEN VERIFY
Holdings formulas: shares, average cost and P&L
The Holdings tab has one row per ticker in column A. Every other column is a formula that reads the Transactions tab (named Tx below, with columns as in the table above; adjust the ranges to your sheet). These formulas use the average-cost method: selling shares does not change the average cost of the ones you keep.
| Column | Formula (row 2) | Notes |
|---|---|---|
| Shares bought | =SUMIFS(Tx!E:E,Tx!D:D,A2,Tx!C:C,"Buy") | Includes split shares recorded as 0.00 buys. |
| Shares sold | =SUMIFS(Tx!E:E,Tx!D:D,A2,Tx!C:C,"Sell") | Always positive in the log; subtracted here. |
| Shares held | =B2-C2 | Zero for a position you have fully soldβkeep the row so realized gains still report. |
| Total cost of buys | =SUMPRODUCT((Tx!D:D=A2)*(Tx!C:C="Buy"),Tx!E:E,Tx!F:F)+SUMIFS(Tx!G:G,Tx!D:D,A2,Tx!C:C,"Buy") | Shares Γ price for every buy, plus the buy-side fees. |
| Average cost | =IF(B2=0,"",E2/B2) | Cost per share including fees. Blank rather than an error when nothing was bought. |
| Cost basis (open) | =D2*F2 | What the shares you still hold cost you. |
| Price | =IFERROR(VLOOKUP(A2,Prices!A:C,3,FALSE),"") | Pulled from the Prices tab, where GOOGLEFINANCE lives. |
| Market value | =IF(H2="","",D2*H2) | Blank when the quote is missing, so totals do not silently drop a position to zero. |
| Unrealized gain | =IF(I2="","",I2-G2) | Market value minus open cost basis; divide by G2 for the percentage. |
| Realized gain | =SUMPRODUCT((Tx!D:D=A2)*(Tx!C:C="Sell"),Tx!E:E,Tx!F:F)-SUMIFS(Tx!G:G,Tx!D:D,A2,Tx!C:C,"Sell")-C2*F2 | Sell proceeds minus sell fees minus the average cost of the shares sold. |
| Dividends | =SUMIFS(Tx!H:H,Tx!D:D,A2,Tx!C:C,"Dividend") | Cash dividends received; reinvested shares are separate Buy rows. |
| Total return | =IF(J2="","",J2+K2+L2) | Unrealized + realized + dividends. Divide by E2 for return on all money invested in the ticker. |
WORKED EXAMPLE
One ticker, two buys, one sale
An investor buys 10 shares at 150.00 and later 10 more at 170.00, paying a 1.00 fee each time. She then sells 5 shares at 180.00 (fee 1.00) and receives 12.00 in cash dividends. The current quote is 175.00.
Illustrative data- Total cost of buys
- 3,202.00 10Γ150 + 10Γ170 + 2 fees
- Average cost
- 160.10 3,202 Γ· 20 shares
- Realized gain
- 98.50 5Γ180 β 1 β 5Γ160.10
- Open cost basis
- 2,401.50 15 shares Γ 160.10
- Unrealized gain
- 223.50 15Γ175 β 2,401.50 (+9.3%)
- Total return
- 334.00 223.50 + 98.50 + 12 = 10.4% of 3,202
LIVE DATA, ISOLATED
Pull prices with GOOGLEFINANCE on their own tab
The Prices tab has four working columns. Column A is the ticker exactly as it appears on Holdings; column B is the exchange-qualified symbol Google Finance expects; columns C onward are formulas.
=IFERROR(GOOGLEFINANCE(B2,"price"),"")Delayed by up to 20 minutes. The IFERROR keeps a temporarily missing quote from breaking every total downstream.
=GOOGLEFINANCE(B2,"name") Β· =GOOGLEFINANCE(B2,"changepct")Company name for the dashboard and the dayβs percentage move for a quick βwhat moved todayβ list.
=GOOGLEFINANCE(B2,"currency") Β· =GOOGLEFINANCE("CURRENCY:GBPUSD")Read the quote currency per ticker, then look up the rate to your portfolio currency in one place instead of hard-coding it.
Qualify the exchange
Write NASDAQ:AAPL, NYSE:KO or LON:VOD rather than a bare ticker. Google Finance may resolve an unqualified symbol to a different listing, and the difference shows up as a wrong currency, not as an error.
Record the gaps by hand
GOOGLEFINANCE does not return dividend payments, does not cover every exchange or fund, and its data is not licensed for professional use. Dividends, corporate actions and unsupported assets go into Transactions as typed rows with a source in the Notes column.
For symbol syntax, historical prices and error fixes, see the full GOOGLEFINANCE guide for stock prices in Google Sheets.
CHECK THE LOGIC BEFORE YOU TRUST THE SHEET
Cost-basis and return calculator
The relationships below are the ones the Holdings tab implements. Use the calculator to test a position by hand, then compare the numbers with what your sheet shows for the same trades.
Total return = (Shares held Γ Price β Open cost basis) + Realized gain + Dividends
Open cost basis is shares held Γ average cost, where average cost is the total cost of all buys, fees included, divided by all shares bought. Realized gain on a sale is proceeds minus sell fees minus shares sold Γ average cost.
TRY THE WORKFLOW
Average cost, realized and unrealized gain
Enter hypothetical values. Results update in your browser and are not stored or sent anywhere.
Average-cost method, fees included in cost. Your brokerβs tax lots may differ.
MEASURE THE PORTFOLIO, NOT JUST THE TICKERS
Dashboard and monthly review
The Dashboard tab reads only from Holdings. Four blocks cover what most investors actually check, and Google Sheets has a native function for each.
Totals
=SUM(Holdings!I:I) for market value, the same for cost basis, unrealized, realized and dividends. Show total return as a number and as a percentage of money invested.
Allocation
Weight = market value Γ· total. A pie or bar chart on that column plus a QUERY grouped by sector shows concentration at a glance; compare against targets as in our allocation-drift guide.
Movers
=SORT(FILTER(...),changepct,FALSE) on the Prices tab lists todayβs biggest moves among what you ownβuseful, but not a reason to trade.
Value log
Once a month, paste total value, cost basis and net deposits as values into a dated table and add a SPARKLINE. GOOGLEFINANCE gives todayβs price, not your historyβthis table is your history.
- Reconcile with the brokerShares held and cash per account must match the statement. A mismatch is usually a missing dividend, fee or split row.
- Check every quoteA blank price means a symbol Google Finance could not resolve; a wrong currency means an unqualified ticker matched another exchange.
- Separate contributions from gainsCompare this monthβs total value change with net deposits before calling it performance.
- Log the monthPaste values, not formulas, into the value log so the history cannot change when a quote does.
- Review concentrationFlag any holding or sector above the limit you wrote down; decide on a plan, not on the dayβs move.
- Record dividends and reinvestmentsCash dividends as Dividend rows, reinvested shares as Buy rows, so total return and share count both stay right.
READY-MADE SPREADSHEET
Prefer a stock tracker that is already built?
The DIY structure above keeps every assumption visible. If you would rather start from a finished spreadsheet, review the current product page for included files, features and software requirements.
Stock Portfolio Tracker for Google Sheets & Excel
The current listing describes purchase and sale tracking with ticker, sector, exchange, quantity, price and fees, price updates, unrealized and realized P&L with ROI, multiple-currency support, dividend and tax tracking, historical trend views and a watchlist tab. The listing notes that the watchlist tab is available only in the Google Sheets version. Confirm the latest details on the product page.
- Unrealized and realized P&L per holding
- Multiple currencies and dividend tracking
- Watchlist tab in the Google Sheets version
Product requirements, included files and compatibility are listed on the product page and may change. Desktop or laptop use is recommended for spreadsheet editing.
Compare all Investment Portfolio TrackersFAQ
Stock portfolio tracker questions
How do I get live stock prices in Google Sheets?
Use =GOOGLEFINANCE("NASDAQ:AAPL","price") with the exchange-qualified symbol. Quotes can be delayed by up to 20 minutes and are intended for informational, non-professional use. Wrap the formula in IFERROR so a missing quote does not break your totals.
How do I calculate average cost per share?
Divide the total cost of every buy, including fees, by the total number of shares bought: =SUMPRODUCT(shares, price) + fees divided by =SUM(shares). Under the average-cost method, selling shares does not change the average cost of the shares you keep.
How do I track realized and unrealized gains?
Unrealized gain is shares held Γ current price minus shares held Γ average cost. Realized gain is sale proceeds minus sell fees minus shares sold Γ average cost. Add cash dividends for total return.
Can GOOGLEFINANCE show my dividends?
No. The function returns prices and some fundamentals but not dividend payments. Record each dividend as a transaction row; reinvested shares are entered as buys at the reinvestment price.
How do I handle a stock split in the tracker?
Add a transaction for the extra shares at a price of 0.00 (or a βSplitβ type with the ratio) instead of editing old rows. The share count rises, the total cost stays the same, and the average cost falls in proportion.
Is the average-cost gain the same as my taxable gain?
Not necessarily. Brokers report cost basis by tax lot and many default to first-in, first-out; rules differ by country. Use the tracker for performance and your brokerβs tax statement for the tax figure.
Sources and further reading
- Google Docs Editors Help: GOOGLEFINANCE
- Google Docs Editors Help: SUMIFS function
- Google Docs Editors Help: QUERY function
- Google Docs Editors Help: Create an in-cell dropdown list
- IRS Publication 550: Investment Income and Expenses (cost basis)
Sources and product details checked September 2026. Google Finance data availability, delays and usage terms are set by Google and may change. This article provides general educational information and is not investment, tax or legal advice.
