How to Build a Portfolio Tracker in Google Sheets

Portfolio tracker sketches framing a clear title area

You can build a working portfolio tracker in one sitting with a free Google Sheet. Make four tabs (Holdings, Contributions, Monthly Snapshots, Returns), pull live prices with the GOOGLEFINANCE function, and update it once a month. The part that trips most people up is not the formulas. It is keeping new deposits separate from investment gains, so your numbers tell you the truth.

This guide walks through the layout, the exact formulas, the return math, and the habits that keep a tracker accurate for years.

Profitomics
Build Confidence in Your Investing
Explore practical eBooks with actionable frameworks for stock market investing, designed for people seeking structured financial education.
Explore practical eBooks

TL;DR

  • Use four tabs: Holdings, Contributions, Monthly Snapshots and Returns.
  • Pull prices with GOOGLEFINANCE, always writing the exchange and the ticker together, for example NASDAQ:GOOG.
  • Subtract deposits before you call anything a return, or your tracker will flatter you.
  • Update monthly and freeze a snapshot on the same day each time. Daily checking invites bad decisions.
  • Keep the file private: it holds your holdings and cost basis.

Table of Contents

What a portfolio tracker should record

A tracker answers four questions: what do I own, what did it cost me, what is it worth now, and how much of my growth came from my own money versus the market. Everything you put in the sheet should serve one of those.

For each holding, record:

  • Ticker with exchange, such as NYSEARCA:VTI.
  • Account, such as a 401(k), IRA or taxable brokerage account.
  • Asset class, such as US stocks, international stocks, bonds, cash or crypto.
  • Shares or units owned.
  • Total cost basis, taken from your broker statements rather than memory.

Then add the things that quietly change your results over time: contributions, withdrawals, dividends and fees. The SEC’s investor education arm explains that transaction fees and ongoing expenses reduce the value of your portfolio and compound over time, so even small differences in a fund’s expense ratio matter over decades. A column for each fund’s expense ratio costs you nothing and keeps that visible.

Last, track allocation. FINRA-linked guidance from Investor.gov stresses that diversification and time horizon should drive asset allocation, and that regular rebalancing keeps a portfolio aligned with your goals. A tracker that shows your actual percentages next to your target percentages makes rebalancing a ten-minute job instead of a guess.

The four-tab layout

One giant sheet gets messy fast. Splitting the job into four tabs keeps each one simple and lets the formulas stay short.

Tab What it holds How often you touch it
Holdings Every position, live price, value, gain, allocation Whenever you buy or sell
Contributions A dated log of deposits and withdrawals Every time money moves
Monthly Snapshots Frozen copies of your total value by account Once a month
Returns Return, annualized return, benchmark comparison Once a month

The Contributions and Snapshots tabs are the ones beginners skip, and they are the ones that make the tracker useful. Without them you can see today’s value but never learn whether you are actually doing well.

Step by step: build the Holdings tab

Open a new Google Sheet and name the first tab Holdings. Use these columns in row 1:

Column Header
A Ticker
B Account
C Asset class
D Shares
E Cost basis
F Price
G Value
H Gain ($)
I Gain (%)
J Allocation

Enter your positions starting in row 2. Then add these formulas to row 2 and drag them down:

  • Price (F2): =GOOGLEFINANCE(A2,"price")
  • Value (G2): =D2*F2
  • Gain in dollars (H2): =G2-E2
  • Gain in percent (I2): =H2/E2
  • Allocation (J2): =G2/SUM(G$2:G$100)

Format I and J as percentages, and E through H as currency.

Get the ticker format right

Google’s own documentation says to include both the exchange symbol and the ticker symbol, for example NASDAQ:GOOG, to avoid discrepancies. A bare ticker can match the wrong listing. So type NYSEARCA:VTI, not VTI, in column A, and the formula reads it directly.

Total your allocation by asset class

Below your positions, or on a small side table, list each asset class in one cell and use SUMIF to total it:

=SUMIF(C:C,"US stocks",G:G)/SUM(G:G)

Put your target percentage next to it, then a third column that subtracts the target from the actual. When a gap grows past the range you set for yourself, say five percentage points, that is your rebalancing signal. If you are still deciding what those targets should be, our step-by-step investing plan for beginners and the risk tolerance check are good places to settle them before you fill in the sheet.

Build the Contributions tab

This tab is a plain log with four columns: Date, Account, Amount, and Type (deposit, withdrawal, dividend reinvested). Deposits are positive numbers and withdrawals are negative.

Add a total at the top with =SUM(C2:C1000) and a monthly figure with SUMIFS on the date column. The reason this tab exists is simple: when your account balance jumps by $500, you need to know whether the market did that or you did.

If you automate contributions from a paycheck, log them the same day each month. Five seconds of data entry saves an afternoon of reconstructing history later.

Build the Monthly Snapshots tab

On the same day each month, record the total value of each account and the grand total. The important trick: paste values only, not formulas. In Google Sheets, copy your totals, then use Paste special, Values only. If you leave live formulas in the snapshot, last month’s number will change every time prices move, and the history is worthless.

There is a second reason to keep your own history. Google notes that historical GOOGLEFINANCE data cannot be downloaded or accessed through the Sheets API or Apps Script, and that attempting it produces a #N/A error. Your frozen snapshots are your record, so do not rely on the function to rebuild one later.

Snapshots also matter outside performance. A dated chain of month-end values and your broker statements can be a useful supporting record. The SEC’s guide for older investors points out that a spreadsheet showing purchase history and methodology can support a record of cost basis that heirs may need. If you want to go beyond investments and track everything you own and owe, our 30-minute net worth workflow uses the same monthly snapshot habit.

Measure returns without mistaking deposits for gains

This is the step that separates a tracker from a balance viewer. Suppose your account grew from $10,000 to $12,000 in a year, but you deposited $1,500 during that time. You did not earn 20%.

FINRA defines total return as the change in value plus any income, and percent return as that amount divided by the amount invested. To apply that to an account where you add money, strip the deposits out of the change first. A simple version for your Returns tab:

=(Ending value - Beginning value - Contributions) / Beginning value

Using the example: ($12,000 - $10,000 - $1,500) / $10,000 = 5%. That is the honest number. Pull Ending and Beginning from your Snapshots tab and Contributions from your log for the same period.

This method is an approximation. It works well for steady monthly contributions. If you made one very large deposit right before or after a big market move, the result will be a little off, and a time-weighted or money-weighted return would be more precise.

Measure returns without mistaking deposits for gains

Annualize so periods can be compared

Because holdings are held for different lengths of time, FINRA recommends annualizing the return with the formula AR = (1 + return)^(1/years) - 1. In Sheets, if your return is in B2 and the number of years is in C2:

=(1+B2)^(1/C2)-1

A 5% gain over six months is not the same as a 5% gain over three years, and the annualized figure shows why.

Add a benchmark

A return number means little alone. Add one broad index fund you could have bought instead, such as a total US market fund, and run the same return calculation on it over the same dates. Then your tracker answers the question that matters: are your choices beating the simple alternative, or are you just depositing more?

Track dividends, fees and multiple accounts

A few small additions make the sheet reliable.

  • Dividends: If dividends are paid in cash, log them in Contributions as income, not as deposits, so they count toward return. If they are reinvested, they raise your share count and cost basis, so update Holdings.
  • Fees: Add an expense ratio column in Holdings and a yearly estimate of cost: value times expense ratio. Seeing the dollar figure makes the compounding effect real.
  • Multiple accounts: Keep one Holdings tab with an Account column, then use SUMIF on that column for per-account totals. Avoid separate tabs per account, which makes allocation across everything harder to see.
  • Crypto: GOOGLEFINANCE support for coins can be limited, so many people enter crypto prices by hand each month. If crypto is a large part of your portfolio, our crypto allocation guide shows how to size it against a risk budget.

How often to update it

Monthly is the sweet spot for most long-term investors: frequent enough to catch drift, rare enough to stay calm. The SEC says that checking stock quotations daily is probably too often and checking annually probably not often enough, and that the right rhythm depends on your goals and investments. The same guidance warns that watching too closely can lead to impulsive selling during temporary dips.

A simple routine:

  1. Pick one day, such as the first Saturday of the month.
  2. Update share counts if you bought or sold anything.
  3. Log deposits and withdrawals in Contributions.
  4. Paste a values-only snapshot.
  5. Read the Returns tab and compare allocation with your targets.

FINRA also suggests a fuller performance review once a year, at around the same time each year, and rebalancing when your allocation has shifted. If markets are rough when your review comes around, preparing for a volatile market before you act helps keep a rebalance a plan rather than a reaction.

Accuracy and privacy checks

Prices are not trading-grade. Google states that GOOGLEFINANCE quotes may be delayed by up to 20 minutes and are provided for informational purposes only, not for trading. That is fine for a monthly tracker, but do not place trades based on what a cell shows.

Cost basis comes from your broker. Copy it from statements or your broker’s cost basis page. Estimates made from memory are the most common source of wrong gain figures.

Accuracy and privacy checks

Keep the file private. The sheet shows what you own and what you paid. Do not set sharing to anyone with the link, and think twice before sharing a screenshot of it. Turn on two-factor authentication for the Google account that holds it.

Spot-check once a quarter. Compare your sheet’s total to the total on your broker’s statement. If they differ by more than a rounding error, find the missing trade or dividend before it compounds.

Our take on trackers

We build our guides around one idea: a system you will use beats a clever one you will abandon. A tracker is the same. Most people who give up on investing records do so because the setup was too ambitious, with twelve tabs, macros and dashboards before they had a single month of data.

So we start small and structured. Four tabs, a handful of formulas, a monthly routine. The templates and checklists in our ebooks follow the same logic, because the point is to move from reading to doing. When you can see your allocation, your contributions and your honest return in one place, risk stops being abstract and becomes a number you can act on.

If you are new, build the tracker before you chase returns. Knowing where you stand is the first real investing skill.

Kai

Skip the blank sheet

If you would rather start from a tested structure than design every column yourself, our Stock Market Mastery ebook is $47 one-off and is built around step-by-step frameworks, checklists and spreadsheets for beginner investors. It is delivered instantly as a PDF with companion bonus materials, so you can set up your tracker and your investing process in the same weekend.

Profitomics

If your goal is broader, such as building several income streams, the Passive Income Blueprint is $27 one-off. For more spreadsheet options, see our roundup of investing spreadsheet templates for U.S. investors.

FAQ

Is Google Sheets good enough to track investments?

Yes, for most individual investors. It handles live prices through GOOGLEFINANCE, simple return formulas and monthly snapshots, and it is free. It works best for stocks and ETFs listed on major exchanges. If you hold many niche assets, you may need to enter some prices by hand.

How do I track investments across several brokerages?

Keep one Holdings tab with an Account column and one row per position per account. Use SUMIF to total each account and SUM for the whole portfolio. Reconcile each account against its statement once a quarter so errors do not build up.

How do I calculate my return if I keep adding money?

Subtract your contributions from the change in value before dividing. The formula is (ending value minus beginning value minus contributions) divided by beginning value. Then annualize it with (1 + return)^(1/years) - 1 to compare different time periods.

How often should I update my tracker?

Once a month is enough for long-term investing, with a bigger review once a year. The SEC notes that checking quotes every day is probably too often. Choose one fixed day so your snapshots are comparable.

Can I use the tracker for taxes?

It can support your records, but your broker’s statements and tax forms are the official source. Use the sheet to organize purchase dates, cost basis and contributions, and ask a tax professional about anything specific to your situation.

Sources