How to Build a Google Sheets Stock Portfolio Tracker to Monitor Your Investments

Most investment apps show you what you own, but not how it fits together. You get a balance, a return percentage, maybe a chart — but you cannot slice by sector, calculate your cost basis exactly the way you want, or run a custom formula against your data. A Google Sheets stock portfolio tracker fixes that. You own the data, you control the formulas, and you can build exactly the views your financial situation requires.

This guide walks through building a complete portfolio tracker in Google Sheets: a Holdings tab with live prices via GOOGLEFINANCE, a Dividends tab to track income, a Performance Dashboard, and a Rebalancing helper — all in a single spreadsheet.

Set Up the Holdings Tab

Rename Sheet1 to Holdings. Add these column headers in row 1:

| Column | Header | Notes | |---|---|---| | A | Ticker | e.g. AAPL, MSFT | | B | Company Name | Text | | C | Shares Held | Number | | D | Avg Cost Per Share | Number, 2 decimal places | | E | Current Price | Formula or manual | | F | Market Value | =C2*E2 | | G | Total Cost | =C2*D2 | | H | Gain/Loss | =F2-G2 | | I | Gain % | =H2/G2 | | J | Sector | Dropdown |

For the Sector column, use Data → Data Validation → Dropdown from a list. Enter sectors like Technology, Healthcare, Financials, Consumer Discretionary, Energy, Real Estate, Other.

For Current Price, you can enter values manually or pull live prices with GOOGLEFINANCE:

=GOOGLEFINANCE("AAPL", "price")

Replace the hard-coded ticker with a cell reference to make it dynamic: =GOOGLEFINANCE(A2, "price") pulls the price for whatever ticker is in column A of that row. GOOGLEFINANCE auto-refreshes and typically has a ~15-minute delay. It works for most NYSE and NASDAQ-listed stocks. For some OTC stocks, mutual funds, and international tickers it returns errors — enter those prices manually.

Format column I (Gain %) as a percentage. Apply conditional formatting: Format → Conditional formatting → Custom formula =I2>0 → fill green; custom formula =I2<0 → fill red. Now every holding shows green profit or red loss at a glance.

Freeze row 1 on this tab: View → Freeze → 1 row. This keeps your headers visible as you scroll through a long holdings list.

Add a Dividends Tab

Create a new tab named Dividends. Add these columns:

| Column | Header | |---|---| | A | Ticker | | B | Ex-Dividend Date | | C | Payment Date | | D | Amount Per Share | | E | Shares Held | | F | Total Received |

For Total Received: =D2*E2

To calculate annual dividend income per ticker, use SUMIF to total all dividends for a given symbol:

=SUMIF(A:A, "AAPL", F:F)

Make it dynamic by referencing a ticker cell: =SUMIF(A:A, H2, F:F) where H2 contains the ticker symbol you are summing. This tab is especially valuable for income investors — dividend income tracking is separate from price appreciation, and some portfolios generate significant yield even if price gains are modest.

The Personal Finance Tracker (Google Sheets) ($19) pairs perfectly with this portfolio tracker — log investment income alongside salary, freelance revenue, and expenses to see your complete financial picture in one spreadsheet.

Build the Performance Dashboard

Create a tab named Dashboard. This is your read-only summary view — no data entry, only formulas pulling from the Holdings and Dividends tabs.

Add these summary metrics in a clean table at the top:

| Label | Formula | |---|---| | Portfolio Total Value | =SUM(Holdings!F:F) | | Total Cost Basis | =SUM(Holdings!G:G) | | Total Gain/Loss | =B2-B3 | | Overall Gain % | =B4/B3 |

Where B2 = Portfolio Total Value and B3 = Total Cost Basis, referenced from the rows above.

For sector allocation, use SUMIF to group Market Value by sector:

=SUMIF(Holdings!J:J, "Technology", Holdings!F:F)

Add a row for each sector category you use. This shows the dollar amount and percentage of your portfolio concentrated in each industry — critical for spotting overconcentration in a single sector.

For top 5 holdings by Market Value, sort the Holdings tab by column F (Market Value) descending and the Dashboard will reflect those positions in order. Alternatively, use LARGE to extract the top 5 market values dynamically, then INDEX/MATCH to retrieve the corresponding tickers.

Add a Rebalancing Helper

Below the Dashboard summary, add a rebalancing table:

| Column | Header | Notes | |---|---|---| | A | Sector | Text | | B | Target % | Your desired allocation (manual entry) | | C | Actual % | Formula | | D | Difference | =B2-C2 |

For Actual %, use:

=SUMIF(Holdings!J:J, A2, Holdings!F:F) / SUM(Holdings!F:F)

This calculates what percentage of your total portfolio is currently in each sector. Format column C as a percentage.

Apply conditional formatting to column D: fill red if Difference is less than -0.05 (more than 5% underweight), fill green if greater than 0.05 (more than 5% overweight). The visual signal makes rebalancing decisions immediate — no mental math required.

The target percentages are yours to set. A simple 3-sector target (e.g., 40% Tech / 30% Healthcare / 30% Other) is easier to maintain than a 10-sector model. Start simple and adjust as your portfolio grows.

Tips for Keeping It Accurate

Protect formula columns. Select columns F through I on the Holdings tab → Right-click → Protect range → add a description like "Calculated columns — do not edit" → set yourself as the only editor. This prevents accidental overwrites of your formulas when you are entering data quickly.

Duplicate the tab for each account. If you have a brokerage account, a Roth IRA, and a 401(k), duplicate the Holdings tab three times: one per account. Name them Holdings-Brokerage, Holdings-Roth, Holdings-401k. Update your Dashboard SUMIF formulas to pull from all three tabs. The Dashboard stays consolidated; the detail stays separated by account for accurate tax-lot tracking.

Flag large moves with conditional formatting. On the Holdings tab, select column I (Gain %) → Format → Conditional formatting. Add a rule: if the value is greater than 0.20 or less than -0.20, apply a bold orange fill. That flags positions that have moved significantly and may need a rebalance review or profit-taking decision.

Add a monthly snapshot tab. At the start of each month, create a tab named Snapshot-YYYY-MM. Copy your entire Holdings tab → Edit → Paste special → Values only. This preserves a historical record of your portfolio at each month's start. Over time, you build a complete performance history without any manual journaling or paid tools.

---

Building your own tracker is not about avoiding paid apps — it is about understanding your portfolio at a level most apps do not enable. When you calculate your own cost basis, build your own sector buckets, and write your own rebalancing formulas, you understand your financial position in a way that a pre-built dashboard rarely delivers.

Monthly Budget Planner (Google Sheets) ($19) → — complete your financial toolkit with monthly spending categories, savings rate tracking, and variance analysis all in one connected spreadsheet.