Create a Stock Portfolio in Excel: Step-by-Step Build
Learn how to create a stock portfolio in Excel with the Stocks data type, cost basis columns, and gain/loss formulas.
You can create a stock portfolio in Excel with a blank sheet, the Stocks data type, and a handful of formulas. This walkthrough is for Microsoft 365 users who want a working register today. If you prefer a prebuilt Quotes and Portfolio layout, return to the hub guide or the PlanoNest template.
Key takeaway: Enter tickers and your share data first. Add live prices second. Calculate value and gain last so you never paste over formulas by accident.
Step 1 — Headers and sample rows
DIY portfolio setup
- Open a new workbook. In row 1 add: Ticker, Price, Shares, Avg Cost, Cost Basis, Market Value, Gain/Loss, Gain %.
- Type three tickers you actually own (or watch).
- Enter Shares and Avg Cost from your brokerage.
- Format the range as a Table (Insert > Table).
Step 2 — Convert tickers to Stocks
Select the Ticker column. Data > Stocks. Excel links each symbol to market data. Use the Add Column control to insert Price, or type =[@Ticker].Price in the Price column if your build supports linked field formulas. Refresh with Data > Refresh All when you review.
Warning: Stocks data type needs Microsoft 365 connectivity. Offline Excel will not update prices.
Step 3 — Formulas
For a table row:
- Cost Basis = Shares × Avg Cost
- Market Value = Shares × Price
- Gain/Loss = Market Value − Cost Basis
- Gain % = Gain/Loss ÷ Cost Basis
Add a total row for Cost Basis, Market Value, and Gain/Loss. Conditional formatting on Gain/Loss helps scan winners and losers without a chart.
Step 4 — Optional sell rule column
Add Target Price and a Status column with =IF([@Price]>=[@[Target Price]],"REVIEW","HOLD"). Treat it as a reminder, not automated advice.
Step 5 — Harden the habit
Export brokerage activity monthly. Update shares and avg cost before you trust totals. Keep closed positions on a separate Closed sheet or with a Status filter. Guides from NYIM Training and Investopedia expand on risk columns and multi-sheet views if you outgrow one table.
Product highlight: Three verified sample rows catch formula mistakes before you import fifty tickers.
When to stop DIY
If you need a transaction log, historical CAGR charts, and a dedicated Quotes sheet, building from a blank file takes longer than starting from a structured workbook. PlanoNest’s tracker packages those sheets as a one-time purchase with instant download—use the product page for live pricing.
Keep the workbook honest over time
Personal portfolio spreadsheets fail quietly. A ticker stops resolving, a dividend reinvestment changes share count, or cash sits in the brokerage and never appears in the total. Schedule a short reconciliation: open the brokerage positions view, match tickers and shares, then match cash. Only after that reconciliation should you trust allocation percentages or month-over-month charts.
Document exceptions on the Start Here sheet. Examples: a delisted ticker kept for history, a mutual fund priced manually, or a joint account that should be excluded from personal allocation targets. Future you will not remember why a row looks odd. A one-line note prevents a weekend of reverse engineering.
When you outgrow a single table, add sheets rather than columns. A Transactions sheet preserves the story of each buy and sell. A Snapshots sheet stores month-end total value for a simple line chart. A Closed sheet holds exited positions so active filters stay readable. Resist the urge to automate every edge case. Spreadsheet trackers stay useful when they remain understandable after three months away.
Practical boundaries for spreadsheet investing tools
Treat the file as a register and a memory aid. It will not replace trade confirmations, tax documents, or a fiduciary plan. Avoid storing brokerage passwords in the workbook. Prefer exports and manual entry over fragile login scrapers. If you share the file with a partner, agree who may edit share counts and who only reads the dashboard.
Currency and multi-country holdings add complexity. Pick a reporting currency and convert at a stated rate source, or keep separate sheets per currency and sum at the end. Do not hide FX assumptions inside unnamed cells. The same clarity applies to fees: either include fees in cost basis or show them as separate cash outflows in the transaction log—mixing both methods double-counts pain.
Keep the workbook honest over time
Personal portfolio spreadsheets fail quietly. A ticker stops resolving, a dividend reinvestment changes share count, or cash sits in the brokerage and never appears in the total. Schedule a short reconciliation: open the brokerage positions view, match tickers and shares, then match cash. Only after that reconciliation should you trust allocation percentages or month-over-month charts.
Document exceptions on the Start Here sheet. Examples: a delisted ticker kept for history, a mutual fund priced manually, or a joint account that should be excluded from personal allocation targets. Future you will not remember why a row looks odd. A one-line note prevents a weekend of reverse engineering.
When you outgrow a single table, add sheets rather than columns. A Transactions sheet preserves the story of each buy and sell. A Snapshots sheet stores month-end total value for a simple line chart. A Closed sheet holds exited positions so active filters stay readable. Resist the urge to automate every edge case. Spreadsheet trackers stay useful when they remain understandable after three months away.
Practical boundaries for spreadsheet investing tools
Treat the file as a register and a memory aid. It will not replace trade confirmations, tax documents, or a fiduciary plan. Avoid storing brokerage passwords in the workbook. Prefer exports and manual entry over fragile login scrapers. If you share the file with a partner, agree who may edit share counts and who only reads the dashboard.
Currency and multi-country holdings add complexity. Pick a reporting currency and convert at a stated rate source, or keep separate sheets per currency and sum at the end. Do not hide FX assumptions inside unnamed cells. The same clarity applies to fees: either include fees in cost basis or show them as separate cash outflows in the transaction log—mixing both methods double-counts pain.
Notes for create a stock portfolio in excel readers
Readers searching for create a stock portfolio in excel usually want a file they can trust after the first weekend. Prioritize correct share counts and cost basis over decorative charts. Link back to the pillar when you need the wider download decision, and open the product page only when you are ready for a structured workbook rather than another blank grid.
Keep the workbook honest over time
Personal portfolio spreadsheets fail quietly. A ticker stops resolving, a dividend reinvestment changes share count, or cash sits in the brokerage and never appears in the total. Schedule a short reconciliation: open the brokerage positions view, match tickers and shares, then match cash. Only after that reconciliation should you trust allocation percentages or month-over-month charts.
Document exceptions on the Start Here sheet. Examples: a delisted ticker kept for history, a mutual fund priced manually, or a joint account that should be excluded from personal allocation targets. Future you will not remember why a row looks odd. A one-line note prevents a weekend of reverse engineering.
When you outgrow a single table, add sheets rather than columns. A Transactions sheet preserves the story of each buy and sell. A Snapshots sheet stores month-end total value for a simple line chart. A Closed sheet holds exited positions so active filters stay readable. Resist the urge to automate every edge case. Spreadsheet trackers stay useful when they remain understandable after three months away.
Practical boundaries for spreadsheet investing tools
Treat the file as a register and a memory aid. It will not replace trade confirmations, tax documents, or a fiduciary plan. Avoid storing brokerage passwords in the workbook. Prefer exports and manual entry over fragile login scrapers. If you share the file with a partner, agree who may edit share counts and who only reads the dashboard.
Currency and multi-country holdings add complexity. Pick a reporting currency and convert at a stated rate source, or keep separate sheets per currency and sum at the end. Do not hide FX assumptions inside unnamed cells. The same clarity applies to fees: either include fees in cost basis or show them as separate cash outflows in the transaction log—mixing both methods double-counts pain.
Notes for create a stock portfolio in excel readers
Readers searching for create a stock portfolio in excel usually want a file they can trust after the first weekend. Prioritize correct share counts and cost basis over decorative charts. Link back to the pillar when you need the wider download decision, and open the product page only when you are ready for a structured workbook rather than another blank grid.
FAQ
Does GOOGLEFINANCE work in Excel?
No. GOOGLEFINANCE is a Google Sheets function. In Excel, use Stocks data type, Power Query, or an add-in.
Can I track ETFs the same way?
Yes when Excel recognizes the symbol. Verify the linked instrument before trusting price.
What about dividends?
Add a Transactions sheet with type Dividend; do not silently change share counts unless you reinvest and update shares intentionally.
Disclosure
PlanoNest sells related templates. Educational only; not investment advice.
About the author
Robin is the independent maker behind PlanoNest.
