Stock Price Tracker: Build a Free Portfolio in Excel

You do not need a paid portfolio app to follow your positions — a five-column sheet answers "what is it worth today" in seconds. This tracker is built around one rule: keep the stock price in its own column so every profit, loss, and allocation figure updates the moment you refresh it.

Stock Price Tracker: Build a Free Portfolio in Excel

Build the Stock Price Table in WPS Spreadsheet

Set the sheet up once and the same layout works for ten holdings or a hundred.

1. In row 1 type the headers: Ticker, Shares, Buy Price, Stock Price, Value, Gain/Loss, Return %.

2. Enter your holdings under Ticker, Shares, and Buy Price. Leave the Stock Price column empty for now.

3. In the Value column use =B2*D2 (shares multiplied by the current stock price).

4. In Gain/Loss use =(D2-C2)*B2, and in Return % use =IFERROR((D2-C2)/C2,"") so a blank price never shows an error.

   

   Stock price table layout in WPS Spreadsheet

Use Word, Excel, and PPT for FREE

Free Download

Update Stock Prices Without Typing Every Number

A hand-typed price goes stale the moment the market moves. Two habits keep the sheet current without a subscription data feed.

1. Download a CSV of your positions or a quote list from your broker, then open it in WPS Spreadsheet with Data > Import Data and copy the closing prices into the Stock Price column.

2. Keep a dated column per week if you want history — Price 2026-10-16, Price 2026-10-23 — so you can chart the trend later.

3. Note that the Microsoft 365 Stocks data type is not available in WPS Spreadsheet, so treat any online price you paste as a snapshot rather than a live tick.

   

   Import stock prices from a CSV file

Turn Prices into Profit, Loss, and Allocation

Now the summary rows do the reading for you.

1. Below the last row, total the portfolio with =SUM(E2:E20) for value and =SUM(F2:F20) for gain or loss.

2. Add an Allocation % column with =IFERROR(E2/$E$21,""), pointing at the total cell.

3. Select the Return % column and apply Conditional Formatting > Highlight Cells Rules so any holding below your chosen threshold turns red.

4. Insert a donut chart from the Ticker and Allocation columns to see concentration at a glance.

   

   Profit, loss, and allocation in a portfolio sheet

Use Word, Excel, and PPT for FREE

Free Download

The Best Free Alternative: WPS Office

The Microsoft 365 Stocks data type is locked to a subscription, yet the maths behind a tracker — multiply, subtract, divide — is the same in every spreadsheet. WPS Spreadsheet opens your existing .xlsx portfolio for free and keeps every formula and chart intact.

• No licence wall: conditional formatting and donut charts are free, so the tracker looks the same at zero cost.

• Same file, same formulas: your SUM, IFERROR, and IF formulas keep working when Excel opens the workbook.

• Works offline: prices you import stay on your device instead of syncing to a cloud service.

• Free templates: start from a ready-made budget or tracker layout instead of a blank grid.

WPS Spreadsheet for a stock price tracker

FAQs

Does Excel track stock prices automatically?

Only Microsoft 365 offers the Stocks data type, which can refresh quotes for a ticker. In other versions, and in WPS Spreadsheet, you import or paste a price list instead.

Can I build this tracker for free?

Yes. The tracker is a plain spreadsheet, so any free tool that opens .xlsx — including WPS Spreadsheet — can run it with no subscription.

How often should I update the stock price column?

As often as you check the portfolio. Daily refresh is enough for most people; weekly is fine if you only review positions at the weekend.

How do I calculate total return on a portfolio?

Total the Gain/Loss column with SUM and divide it by the total cost, which is SUM(Shares multiplied by Buy Price). Format the result as a percentage.

Can I track dividend payments in the same sheet?

Add a Dividend column and include it in the Value total, or keep a separate Income sheet and pull the running total into the summary row.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

Free Download

100% secure

Algirdas Jasaitis