Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →You can track a stock portfolio in Excel with a simple holdings table, formulas for value and return, and—if your Excel account supports it—the built-in Stocks data type for delayed, refreshable fields. Download the free Excel stock tracker
Note: This template is for personal organization and education. Excel stock information may be delayed and is supplied as-is; it is not trading advice or a basis for placing trades. Confirm prices, holdings, and tax information with your broker and appropriate professional advisers.
As an Amazon Associate I earn from qualifying purchases.
A stock portfolio tracker only needs three things to be useful: an accurate share count, a defensible cost figure, and a price with a visible date. The free workbook below handles those basics with formulas that continue to work even if you never use Excel’s online market-data features.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteDownload the free Excel stock tracker
Download the Excel Stock Portfolio Tracker (.xlsx)
The workbook includes a beginner-friendly Holdings sheet, an optional Transactions log, and an optional History sheet for a single security’s historical closing prices.
#1 Best Overall
Important: This template is for personal organization and education. Excel stock information may be delayed and is supplied as-is; it is not trading advice or a basis for placing trades. Confirm prices, holdings, and tax information with your broker and appropriate professional advisers.
What the tracker calculates
For each position, the workbook calculates simple cost basis, current market value, unrealized gain or loss, return percentage, and portfolio allocation. “Simple” matters here: Shares × average cost is useful for a personal snapshot, but it is not an official tax-basis calculation. Fees, multiple tax lots, stock splits, wash-sale adjustments, dividend reinvestment, corporate actions, and local tax rules can change the figure used for reporting.
Use the Holdings sheet for a clear current view. Keep broker statements and, if needed, the Transactions sheet for the underlying record.
Set up or update the Holdings sheet in five steps
1. Enter the positions you own now
On Holdings, add one row per security. Enter a ticker, the number of shares currently held, and your average cost per share. Name, notes, and last-updated time are deliberately manual fields: they make the sheet understandable when you reopen it months later.
| Column | Enter or calculate | Why it is there |
|---|---|---|
| Ticker | Enter | The security identifier; it can optionally become an Excel Stocks data type. |
| Name | Enter or extract | A readable company, fund, or ETF name. |
| Shares | Enter | Your current quantity, including fractional shares where applicable. |
| Avg Cost | Enter | Your simple average purchase cost per share. |
| Cost Basis | Formula | Simple position cost, before the tax-basis exceptions noted above. |
| Current Price | Enter or link | The displayed price used for the snapshot. |
| Market Value | Formula | Shares multiplied by current price. |
| Gain/Loss | Formula | Unrealized difference between market value and simple cost basis. |
| Gain/Loss % | Formula | Position gain or loss relative to simple cost basis. |
| Portfolio % | Formula | The holding’s share of the current portfolio value. |
| Last Updated | Enter | Shows how fresh the displayed price is. |
| Notes | Enter | Account name, goal, data source, or a reminder. |
Enter the price manually if that is the most reliable route for you. For example, record the price you see in your broker account, add the date and time in Last Updated, and put the source in Notes. That is better than treating an unverified or stale number as current.
2. Let the table calculate value and return
The holdings range is an Excel Table named tHoldings. Tables use structured references, which adjust as rows are added or removed; calculated-column formulas also fill down automatically. If rebuilding the workbook, select the range, choose Insert > Table, confirm that it has headers, then use Table Design > Table Name to name it tHoldings. Microsoft’s structured-reference guide explains why this is more durable than fixed cell ranges.
Use these English-language Excel formulas in the calculated columns:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Column | Formula | Meaning |
|---|---|---|
| Cost Basis | =[@Shares]*[@[Avg Cost]] |
Shares × average cost per share. |
| Market Value | =[@Shares]*[@[Current Price]] |
Shares × displayed current price. |
| Gain/Loss | =[@[Market Value]]-[@[Cost Basis]] |
Unrealized dollar gain or loss. |
| Gain/Loss % | =IFERROR([@[Gain/Loss]]/[@[Cost Basis]],"") |
Prevents a divide-by-zero error for an empty or zero cost basis. |
| Portfolio % | =IFERROR([@[Market Value]]/SUM(tHoldings[Market Value]),"") |
Allocation based on the current total value. |
Format Avg Cost, Cost Basis, Current Price, Market Value, and Gain/Loss as currency. Format Gain/Loss % and Portfolio % as percentages. Apply conditional formatting to Gain/Loss so negative amounts are visually distinct, but retain the minus sign and the clear column heading; color should not be the only signal.
3. Add prices: manual first, linked data when eligible
Excel’s Stocks data type is optional. It can save typing for supported instruments, but it is not a live trading feed and it is not available in every setup. Microsoft states that stock information is delayed, supplied as-is, and not for trading purposes or advice. Read Microsoft’s stock-quote notice.
- Type a ticker symbol, company name, or fund name in the Ticker column.
- Select the relevant Ticker cells and choose Data > Stocks.
- If Excel displays a question-mark icon, open the selector and choose the intended company, fund, or security. Do not assume an ambiguous symbol matched correctly.
- Select a converted cell and use Insert Data to add a field such as Price. In the Current Price column, a table formula can also be
=[@Ticker].Priceonce the Ticker cells are linked Stocks data types. - To refresh a selected instrument, right-click it and choose Data Type > Refresh. To refresh linked data types and other workbook connections together, choose Data > Refresh All.
Microsoft documents Stocks and Geography data types for Microsoft 365 accounts or users with a free Microsoft Account, with an English, French, German, Italian, Spanish, or Portuguese editing language added. Check the current Stocks data-type requirements and workflow. The available financial examples include equities, mutual funds, ETFs, indexes, currency pairs, commodities, and treasury benchmarks, but availability varies by source and account environment. Microsoft’s linked-data catalog is the right place to check coverage rather than assuming every symbol is supported.
Rank #3
4. Read the dashboard as a snapshot
The cards above the table summarize the positions currently listed:
- Total Cost Basis:
=SUM(tHoldings[Cost Basis]) - Total Market Value:
=SUM(tHoldings[Market Value]) - Total Gain/Loss:
=SUM(tHoldings[Gain/Loss]) - Total Return:
=IFERROR(SUM(tHoldings[Gain/Loss])/SUM(tHoldings[Cost Basis]),"") - Data as of: a manually entered timestamp, or a clearly labelled last-refresh time.
These figures describe unrealized gain or loss relative to the simple cost entered in the sheet. They do not measure investor performance when deposits, withdrawals, dividends, taxes, interest, or dated cash flows need to be considered. Portfolio % is useful for a quick allocation check, not a recommendation to buy or sell.
5. Add a historical closing-price chart only if you need it
The optional History sheet is for one supported instrument—not the whole portfolio. Put a ticker in B1 and a start date in B2, then enter this formula in an otherwise empty area:
=STOCKHISTORY($B$1,$B$2,TODAY(),0,1,0,1)In order, the final arguments request daily data (0), headers (1), Date (0), and Close (1). STOCKHISTORY returns a spilling array, so leave cells below and to the right empty. Microsoft lists it for Microsoft 365 Personal, Family, Business Standard, and Business Premium subscriptions; the full syntax and eligibility are in its STOCKHISTORY documentation. A recognized Stocks data type does not guarantee that historical data is available for that instrument.
Rank #4
Select the Date and Close output, then choose Insert > Recommended Charts and select a line chart, or use Insert > Line. Line charts are designed to show trends across equal time intervals. Microsoft’s chart guidance covers the chart-selection steps. Title the result Historical closing price, not “portfolio performance,” unless you also track dated contributions, withdrawals, income, and holdings through time.
Use Transactions when one number is no longer enough
The optional Transactions sheet has Date, Ticker, Action, Quantity, Price, Fees, Total Cash Flow, Account, and Notes. It is a record of what happened, which makes it easier to explain how a position changed or to reconcile it with statements.
A simple average-cost Holdings sheet remains appropriate for a personal overview. Do not turn it into tax reporting by assumption: tax-lot selection, wash-sale rules, dividend reinvestment, splits, mergers, and jurisdiction-specific rules require the broker’s records and, where appropriate, qualified local advice.
Optional: import a broker CSV without overwriting the tracker
Broker exports are useful source material, but each broker uses its own column names, date formats, currencies, and treatment of fees. Keep the original export unchanged. Import it into a separate staging sheet first; do not paste it over Holdings.
Free tools Windows power users keep installed
One-click scans. No signup required.
- Choose Data > Get Data > From Text/CSV.
- Select the broker export and inspect the preview.
- Choose Transform Data to review or correct fields in Power Query before loading, or choose Load only when the preview is already right.
- Map fields deliberately: Symbol → Ticker, Quantity → Shares, and Average Cost → Avg Cost are common examples, not universal rules.
- Check signs, decimal separators, dates, currency, fractional shares, and whether fees are included before using any imported number.
Power Query can detect CSV delimiters, headers, and types, but detection is a starting point rather than proof that the import is correct. Microsoft’s CSV-import instructions explain the Load and Transform Data choices.
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Troubleshooting
| What you see | What to check |
|---|---|
| Stocks button or STOCKHISTORY is missing | Confirm your Excel edition, sign-in/account eligibility, updates, and editing language. STOCKHISTORY has narrower subscription requirements than the basic Stocks data type. If the feature is unavailable, enter Current Price manually and timestamp it. |
| The wrong company or fund appears | Open the data-type selector, search with a more specific term, and select the intended instrument. Do not rely on a short ticker alone. |
#FIELD!, #VALUE!, or #NAME? |
Make sure the requested field exists and that the cell converted successfully. Unsupported Excel versions can show #VALUE! in linked-data cells and #NAME? in formulas that refer to them. Microsoft also notes limitations with direct use of linked data in PivotTables, Power Query, Power Pivot, and some charts. See Microsoft’s linked-data FAQ. |
| STOCKHISTORY will not spill | Clear every obstructing cell in the expected output range, then verify that the selected instrument has historical data. |
| Broker-export numbers look wrong | Return to the import preview or Power Query transformation. Check delimiter, decimal separator, date parsing, numeric type, and whether the export is using a different currency or accounting convention. |
Keep the workbook useful
- Update share counts after every buy, sale, transfer, split, or dividend reinvestment.
- Refresh or replace prices at a deliberate interval, then record when you did it.
- Reconcile at least periodically against broker statements.
- Use the Notes column to identify the account or data source when the same security appears in more than one place.
- Keep a backup before restructuring formulas or importing data.
The tracker is most valuable when it is a transparent snapshot you can audit. Its formulas make the arithmetic visible; they do not replace brokerage records, market-data verification, diversification decisions, or professional tax and investment advice.
Optional desk reference
If you want a general Excel guide while customizing the workbook, search for Microsoft 365 Excel For Dummies paperback. It is a general Excel reference for spreadsheet basics, data organization, calculations, tables, and charts—not a stock-investing guide and not required to use this template.
Frequently Asked Questions
Can I use this tracker without Microsoft 365?
Yes. The workbook’s value, gain/loss, return, and allocation formulas work with manually entered Current Price values. Enter the source and timestamp in Notes or Last Updated so the number is not mistaken for a live quote.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Does this template calculate investment performance?
No. It compares current market value with a simple average-cost-based cost basis, so it shows unrealized gain or loss. A true portfolio-performance calculation needs dated contributions, withdrawals, income, fees, and holdings history.
Can I use the Cost Basis column for taxes?
No. It is not tax-ready and does not determine official tax basis. Use broker statements and qualified local tax guidance for lots, wash sales, reinvestments, corporate actions, and reporting.
Can I track more than one brokerage account?
Usually, yes. Enter each holding as a separate row and use Notes or Account to identify where it is held. If you want a combined view, ensure each row’s shares and average cost reflect that account only, then let the table totals aggregate them.
The Bottom Line
Enter accurate holdings, use the formulas to calculate a transparent snapshot, and label every price with its source and time. Excel can reduce the spreadsheet work; your broker remains the record to verify.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
