Stock Investment Tracking using Google Sheets

A stock tracking spreadsheet is a free, fully customisable way to follow a portfolio, and in Google Sheets it is built on one function: GOOGLEFINANCE. This guide covers the attributes that function can pull, the columns a working tracker needs, the formulas that turn transactions into performance, and the specific things a spreadsheet cannot do.

Key takeaways

• Google Sheets tracks stocks through the GOOGLEFINANCE function, which pulls quotes and fundamentals such as price, P/E, EPS, market capitalisation, beta and 52-week high and low into a cell.
• GOOGLEFINANCE prices for US exchanges are delayed by up to 20 minutes and are explicitly not for trading purposes, so a Google Sheets tracker is a monitoring tool rather than a live one.
• GOOGLEFINANCE has no dividend-per-share or dividend-yield attribute for individual stocks, so dividend income in a Google Sheets tracker has to be entered by hand.
• A useful stock tracking spreadsheet stores one row per transaction, not one row per holding, because cost basis, realised gains and money-weighted return all need the individual trades.
• A stock tracking spreadsheet breaks down on multi-currency holdings, corporate actions such as splits and spin-offs, and accounts held at several brokers, which is where a dedicated portfolio tracker earns its place.

How do you track stocks in Google Sheets?

You track stocks in Google Sheets by putting ticker symbols in one column and using the GOOGLEFINANCE function in adjacent columns to pull live data for each of them. The function takes a ticker and an attribute name and returns that value into the cell.

=GOOGLEFINANCE(ticker, attribute, [start_date], [end_date], [interval])

The simplest form fetches the current price of a stock:

=GOOGLEFINANCE("AAPL", "price")

Point the first argument at a cell rather than a hard-coded string and the sheet becomes reusable: if A2 contains AAPL, then =GOOGLEFINANCE(A2, "price") returns Apple's price, and copying the formula down prices every ticker in the list. Qualify ambiguous or non-US tickers with the exchange, as in "NASDAQ:AAPL" or "LON:VOD".

Two limits matter before you build anything on top of it. GOOGLEFINANCE data for US exchanges is delayed by up to 20 minutes and Google states it is not for trading purposes, and it covers only a subset of the world's listings, so some funds, bonds and smaller foreign listings return #N/A.

What data can the GOOGLEFINANCE function pull?

The GOOGLEFINANCE function pulls current quote data, a handful of fundamentals, and historical price series, selected by the attribute string you pass as the second argument.

Historical prices need three more arguments: a start date, an end date and an interval. This returns Apple's daily closing prices for the whole of 2022:

=GOOGLEFINANCE("AAPL", "close", DATE(2022,1,1), DATE(2022,12,31), "DAILY")

A historical query returns an array with a header row rather than a single value, so leave empty cells below and to the right of it or the formula returns a #REF! spill error.

One attribute people expect and do not get is dividends. GOOGLEFINANCE exposes no dividend-per-share or dividend-yield attribute for individual stocks; the yieldpct attribute works only for mutual funds. Any dividend column in a Google Sheets stock tracker is therefore typed in by hand from your broker statements, which matters if you are tracking yield on cost or building a dividend yield view. Google's own GOOGLEFINANCE documentation lists the full attribute set.

What columns should a stock tracking spreadsheet have?

A stock tracking spreadsheet should have one row per transaction rather than one row per holding, because cost basis, realised gains and return calculations all need the individual buys and sells with their dates.

Build the transaction log with these columns:

A second summary sheet then aggregates that log into one row per holding: quantity held, average cost, current price, market value, unrealised gain and portfolio weight.

What formulas should a stock portfolio tracker use?

A stock portfolio tracker in Google Sheets needs five formulas: market value, cost basis, unrealised gain, portfolio weight, and a return figure. Everything else on a tracker dashboard is a variation on these.

Market Value        = GOOGLEFINANCE(Ticker, "price") * Quantity
Cost Basis          = (Quantity * Price Per Share) + Fees
Unrealised Gain %   = (Market Value - Cost Basis) / Cost Basis
Portfolio Weight    = Market Value / SUM(All Market Values)
Money-Weighted Ret. = XIRR(Cash Flow Range, Date Range)

Written as spreadsheet formulas with the ticker in A2, quantity in B2, cost basis in C2 and market value in D2:

=GOOGLEFINANCE(A2, "price") * B2
=(D2 - C2) / C2
=D2 / SUM($D$2:$D$100)
=XIRR(F2:F100, E2:E100)

XIRR is the one that does real work. It solves for the money-weighted return, the single rate that makes every dated cash flow plus the ending value net to zero, so it accounts for deposits and withdrawals. Enter money you put in as negative and dividends, sale proceeds and the final value as positive, or the sign convention inverts the answer. Stripping contribution timing out instead gives a time-weighted return, which needs the portfolio value recorded at every cash flow date and is far more manual.

Holding more than one currency adds a conversion step. GOOGLEFINANCE supplies the rate, for example =GOOGLEFINANCE("CURRENCY:USDEUR"), but a rate pulled today converts a purchase made three years ago at the wrong rate. Correct multi-currency cost basis needs the rate on each transaction date, stored per row.

What are the limitations of a stock tracking spreadsheet?

The main limitation of a stock tracking spreadsheet is that it holds only the data you type into it, so every event that changes a position, other than a price move, is manual work you have to remember to do.

• Corporate actions break it silently. A 4-for-1 stock split, a spin-off or a merger changes your share count and cost basis. The price column updates automatically; your quantity column does not, so the tracker keeps reporting a wrong market value until you notice.
• Dividends are entirely manual. With no dividend attribute in GOOGLEFINANCE for stocks, every payment has to be entered from a statement, and missing them understates total return.
• Multiple brokers mean multiple imports. Each broker exports a different CSV layout, so consolidating three accounts means three separate reformatting jobs every time.
• Multi-currency needs historical FX. Converting at today's rate rather than the trade-date rate misstates cost basis and gain for every foreign holding.
• Prices are delayed and sometimes missing. US quotes lag by up to 20 minutes, and unsupported listings return #N/A, which then propagates through every formula that references them.
• Formulas rot. Inserting a row, dragging a fill handle one cell too far or renaming a tab quietly breaks a range, and a spreadsheet gives no warning that a total is now wrong.

None of these makes a spreadsheet a bad choice. For a single-currency portfolio of ten stocks at one broker, a Google Sheets tracker is genuinely hard to beat: it is free, it does exactly what you tell it, and you own the file. The maintenance cost grows with accounts, currencies and years, not with the number of stocks.

Does Google have a stock tracker?

Yes. Google provides a stock tracker on the Google Finance website, with both a watchlist and a portfolio feature, reachable from the hamburger menu on the left.

Google Finance's portfolio tracker shows a snapshot of your current holdings and their gain or loss. It does not keep a full transaction history, so it cannot produce historical performance over a period, dividend income history, or a benchmark comparison. For those, an online portfolio tracker such as Portseido is the closer fit, and there is a breakdown of how Portseido differs from Google Finance if you are choosing between them.

Is there a free stock tracking spreadsheet template?

Yes, though not from Google itself. Google does not publish an official stock tracking template for Sheets, but several investors and educators maintain free ones you can copy into your own Drive.

• Old School Value Stock Tracking Spreadsheet by Old School Value
• General Trading Stock Portfolio Template by SpreadsheetClass.com
• Stock Dashboard by Brian Turgeon

Before committing to any template, check three things: whether it stores transactions or only current holdings, whether it handles the currency you invest in, and whether the GOOGLEFINANCE calls resolve for your tickers. A template built for US large caps often returns #N/A across a European or Asian portfolio.

Frequently asked questions

Does GOOGLEFINANCE work in Microsoft Excel?

No. GOOGLEFINANCE is a Google Sheets function and has no equivalent in Excel. Excel offers a Stocks data type in Microsoft 365 that pulls quotes into linked cells, and it supports the STOCKHISTORY function for historical prices. The two are not interchangeable, so a Google Sheets tracker will not open correctly in Excel without rebuilding the price formulas.

How often does GOOGLEFINANCE update prices?

GOOGLEFINANCE refreshes quote data roughly every 20 minutes for US exchanges, and Google labels the feed as not for trading purposes. Sheets recalculates the function when the file is open and on a periodic schedule, so a closed spreadsheet will show stale figures until you reopen it. Delays for non-US exchanges can be longer.

Why does my GOOGLEFINANCE formula return #N/A?

A #N/A from GOOGLEFINANCE usually means the ticker is not in Google's coverage, the exchange prefix is missing or wrong, or the attribute does not exist for that security type. Mutual funds, many bonds and some foreign listings are not covered. Adding the exchange, as in "LON:VOD" instead of "VOD", resolves a large share of these errors.

Should I track stocks in a spreadsheet or an app?

Track stocks in a spreadsheet when you hold a single-currency portfolio at one broker and want full control of the layout. Move to a dedicated tracker when you hold accounts at several brokers, invest across currencies, or want portfolio return calculated consistently without maintaining the formulas yourself.

How to track stocks without a spreadsheet in Portseido

Portseido is a portfolio tracker that keeps the record a spreadsheet asks you to maintain by hand. It consolidates holdings across multiple brokers and currencies in one place, tracks cost basis, calculates time-weighted and money-weighted returns from your transaction history, records dividend income and yield on cost, and benchmarks the portfolio against indices and ETFs. Transactions come in through broker imports or a CSV or spreadsheet upload, so an existing Google Sheets log is a starting point rather than something you abandon; there is a spreadsheet import template if you would rather map your data to a known layout.

It suits investors whose portfolio has outgrown manual upkeep. Portseido tracks and reports on your portfolio; it does not give buy or sell recommendations. There is a free plan, and paid features come with a 14-day trial that does not need a credit card. Try Portseido free