To build a stock tracker in Excel, list each holding with its ticker, share count, and purchase price, then add a column that pulls the current price and a few columns that calculate market value, gain or loss, and percentage return. Plain Excel can do the math, but it cannot fetch prices on its own without a data source. The MarketXLS add-in fills that gap with formulas such as =Last("AAPL") for the latest price and =DividendYield("AAPL") for yield, so the tracker updates when Excel recalculates instead of by manual copy and paste.
Why track stocks in Excel
Excel works well for a stock tracker because you control the layout and the math. You can track stocks, ETFs, mutual funds, and bonds on one sheet, add your own return or risk formulas (for example standard deviation of returns or a break-even price), and keep a full record of purchases and sales. The trade-off is that Excel needs a data feed to keep prices current, which is what an add-in such as MarketXLS provides.
How to create a stock tracker in Excel, step by step
- Set up the holdings table. Create columns for Ticker, Shares, Purchase Price, and Purchase Date. One row per position (or per lot if you buy the same stock more than once).
- Add the current price. With MarketXLS installed, enter
=Last(A2)where A2 holds the ticker. On Excel for Mac or Excel for the web (the Microsoft 365 add-in), use=mxls.Last(A2). - Calculate market value.
=B2*E2(shares times current price). - Calculate gain or loss.
=(E2-C2)*B2(current price minus purchase price, times shares). - Calculate percentage return.
=E2/C2-1, formatted as a percentage. - Add portfolio totals. Sum the market value and gain columns, and divide each position's value by the total to get its weight.
- Add optional data columns. For example
=DividendYield(A2),=PERatio(A2), or=Beta(A2)for income, valuation, and risk context. - Log transactions and cash. Keep a separate sheet for buys, sells, dividends, and cash deposits or withdrawals so the holdings table stays clean.
Can Excel automatically update stock prices?
Yes, but only with a data connection. Microsoft 365 includes a built-in Stocks data type, and add-ins such as MarketXLS add formula-based prices and fundamentals. With MarketXLS, formulas like =Last("MSFT") refresh when the workbook recalculates. On the Standard plan, US stock quotes are 15-minute delayed; the Advanced and Business plans include real-time streaming quotes through QM_Stream_ functions such as =QM_Stream_Last("MSFT"), which update automatically while streaming is on. See pricing for plan details.
Google Sheets is an alternative if you want a tracker you can share and open from any browser. MarketXLS also offers a Google Sheets add-on (see stock market data in Google Sheets).
Typical components of a stock tracker
A useful stock tracker usually includes:
- Holdings: ticker, shares, purchase price, purchase date.
- Live values: current price, market value, day change.
- Performance: gain or loss in dollars, percentage return, total return including dividends.
- Allocation: weight of each position, and optionally weight by sector or asset class.
- Income: dividend yield, dividend per share, and a dividend history or calendar.
- Risk: beta, volatility, or a Sharpe ratio for the portfolio or each holding.
- Transaction log: every buy, sell, dividend, deposit, and withdrawal with dates.
Using a MarketXLS template for your stock tracker
MarketXLS publishes ready-made Excel templates, including portfolio and dividend trackers, in its template library. A template gives you the layout and formulas already wired together; you replace the sample tickers with your own holdings. The templates need the MarketXLS add-in installed to refresh data. If a template does not fit your needs, build the table yourself using the steps above and add MarketXLS functions column by column.
For related guides, see Track Your Dividend Portfolio with the MarketXLS Spreadsheets and tracking dividends with a spreadsheet template.
Relevant MarketXLS functions on this topic
| Function Title | Function Example | Function Result |
|---|---|---|
| On Balance Volume | =OnBalanceVolume("MSFT") | It is a technical indicator that uses volume data to predict changes in stock price |
| Sharpe Ratio | =SharpeRatio("AAPL",,4%) | It is defined as the difference between the returns of the investment and the risk-free return, divided by the standard deviation of the investment |
| Dividend Frequency | =DividendFrequency("MSFT") | How many times each year the company typically pays a regular dividend. |
| Stock Return Thirty Days | =StockReturnThirtyDays("MSFT") | The return is calculated on the closing prices for the given calendar days. |
| Average Daily Volume | =AverageDailyVolume("MSFT") | Returns the average number of shares traded over a 14-day period. Sizable volume increases signify something is changing in the stock that is attracting more interest. |
Search all MarketXLS functions in the formula library.
Note: a spreadsheet built with MarketXLS pulls the latest data only if you have MarketXLS installed. If you do not have MarketXLS, see the MarketXLS pricing plans.
Relevant blogs that you can read to learn more about the topic
Track Your Dividend Portfolio with the MarketXLS Spreadsheets
