To build an investment tracker in Excel, list one holding per row with ticker, shares, and average cost; pull the current price with a formula such as MarketXLS =Last(A2); then calculate market value (=D2*G2), unrealized gain (=H2-F2), portfolio weight, and dividend income (=D2*DividendPerShare(A2)). A second sheet sums those rows into total value, gain, yield, and sector allocation. The workbook stays on your machine, so you do not hand brokerage credentials to a third-party app. Prices are 15-minute delayed on the MarketXLS Standard plan and real-time on Advanced and Business.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
Why Track Investments in Excel Instead of a Web App
An Excel tracker keeps your data on your machine, unlike browser-based portfolio tools that keep it behind a login screen. You own the file, you control the layout, and you decide which metrics matter.
Full Ownership of Your Data
When you build your tracker in Excel, every row of data lives on your hard drive or your OneDrive. There is no vendor lock-in, no surprise paywall when you try to export a CSV, and no risk that a startup shuts down and takes your transaction history with it. You can back the file up, version it, or share it with your accountant in seconds.
Unlimited Customization
Web apps give you the dashboard they designed. Excel gives you a blank canvas. Need a column for tax-lot ID? Add it. Want to color-code positions that are down more than ten percent? A single conditional-formatting rule handles it. Need a pivot table that groups holdings by sector, then by market-cap tier? Two clicks. Most portfolio apps do not offer that level of flexibility.
Adding Live Prices With MarketXLS
The missing piece in a plain Excel tracker has always been live prices. Manually typing closing prices every evening is tedious and error-prone. MarketXLS fills that gap. The formula =Last("AAPL") pulls the latest price for a US-listed stock, ETF, or mutual fund. For streaming intraday data on the Advanced and Business plans, use =QM_Stream_Last("AAPL"). The rest of this article shows you how to wire those formulas into a complete dashboard.
Essential Columns for an Excel Investment Tracker
An investment tracker starts with the right column structure. A well-organized layout makes it easy to enter trades, calculate performance, and spot problems at a glance. Here is the recommended set of columns for your core holdings sheet.
Column-by-Column Breakdown
| Column | Header | Purpose |
|---|---|---|
| A | Ticker | Stock or ETF symbol (e.g., AAPL, VTI) |
| B | Company Name | Human-readable name for quick scanning |
| C | Sector | Technology, Healthcare, Financials, etc. |
| D | Shares | Total shares held across all lots |
| E | Avg Cost Basis | Weighted average purchase price per share |
| F | Total Cost | =D2*E2: what you paid in total |
| G | Current Price | =Last("AAPL"): price from MarketXLS |
| H | Market Value | =D2*G2: what the position is worth now |
| I | Unrealized Gain/$ | =H2-F2: dollar gain or loss |
| J | Unrealized Gain/% | =I2/F2: percentage gain or loss |
| K | Weight % | =H2/SUM($H$2:$H$50): portfolio weight |
| L | Dividend Yield | =DividendYield("AAPL"): trailing yield (decimal) |
| M | Annual Dividend/Share | =DividendPerShare("AAPL"): trailing twelve-month payout |
| N | Annual Dividend Income | =D2*M2: total income from this position |
| O | P/E Ratio | =PERatio("AAPL"): valuation check |
| P | Market Cap | =MarketCapitalization("AAPL"): size classification |
This layout gives you a complete picture of each position in a single row. Let us walk through the build process step by step.
How to Build the Tracker Step by Step
The ten steps below assume you have MarketXLS installed as an Excel add-in. On Mac or Excel for the web (the Microsoft 365 add-in), prefix the functions with mxls., for example =mxls.Last(A2). If you have not installed it yet, visit MarketXLS pricing to choose a plan and download the add-in.
Step 1: Create the Holdings Sheet
Open a new Excel workbook. Rename the first sheet Holdings. In row 1, type the column headers listed in the previous section (Ticker through Market Cap). Freeze the top row so headers remain visible as you scroll.
Step 2: Enter Your Positions
Starting in row 2, enter each position you own. Type the ticker symbol in column A, the company name in column B, the sector in column C, the number of shares in column D, and your average cost basis in column E. If you hold multiple tax lots of the same stock, either consolidate them into one row with a weighted average cost or create separate rows per lot, whichever suits your tax-reporting needs.
Step 3: Add the Cost Basis Formula
In cell F2, enter =D2*E2. This multiplies shares by your average purchase price to show the total amount you invested in that position. Copy the formula down for every row.
Step 4: Pull Live Prices With MarketXLS
In cell G2, enter the formula:
=Last("AAPL")
Formula documentation: Last
Replace "AAPL" with a cell reference if you prefer dynamic lookups:
=Last(A2)
Formula documentation: Last
This formula reaches out to MarketXLS data servers and returns the latest available price. It refreshes each time Excel recalculates or you click the MarketXLS refresh button. For intraday streaming prices (Advanced and Business plans), use:
=QM_Stream_Last(A2)
Formula documentation: QM_Stream_Last
QM_Stream_Last pushes new prices into the cell while streaming is on. =QM_Last(A2) is the on-demand QuoteMedia quote, which returns a snapshot when Excel recalculates.
Step 5: Calculate Market Value and Gain/Loss
In cell H2, enter =D2*G2 for market value. In cell I2, enter =H2-F2 for dollar gain. In cell J2, enter =I2/F2 for percentage gain. Copy all three formulas down.
At this point your tracker shows P&L for every position.
Step 6: Compute Portfolio Weights
In cell K2, enter:
=H2/SUM($H$2:$H$50)
Adjust the range to cover your actual data. This shows each position's weight as a fraction of total portfolio value. Format the column as a percentage. Now you can instantly see if any single stock dominates your portfolio.
Step 7: Add Dividend Data
MarketXLS provides dedicated dividend formulas. In cell L2:
=DividendYield(A2)
Formula documentation: DividendYield
In cell M2:
=DividendPerShare(A2)
Formula documentation: DividendPerShare
In cell N2:
=D2*M2
Column N now shows the estimated annual dividend income from each holding. Sum the column at the bottom to see your total projected dividend income for the year.
Step 8: Add Valuation Metrics
In cell O2:
=PERatio(A2)
Formula documentation: PERatio
In cell P2:
=MarketCapitalization(A2)
Formula documentation: MarketCapitalization
These columns help you quickly identify overvalued positions or check that your portfolio is diversified across large-cap, mid-cap, and small-cap stocks.
Step 9: Add Revenue (Optional)
If you want a quick fundamental check, add a column for trailing twelve-month revenue:
=Revenue(A2)
Formula documentation: Revenue
This is especially useful when comparing companies within the same sector.
Step 10: Format and Polish
Apply conditional formatting to column J so that positive percentages appear in green and negative percentages appear in red. Add a currency format to dollar columns. Bold the header row. Add alternating row colors for readability. Your holdings sheet is now complete.
Building a Dashboard Summary Sheet
A dashboard sheet summarizes the holdings into portfolio-level metrics. Create a second sheet called Dashboard and populate it with the following sections.
Total Portfolio Value
In a prominent cell, enter:
=SUM(Holdings!H2:H50)
This single number tells you exactly what your portfolio is worth right now.
Total Cost Basis
=SUM(Holdings!F2:F50)
Total Unrealized Gain
=SUM(Holdings!I2:I50)
Total Unrealized Gain %
=SUM(Holdings!I2:I50)/SUM(Holdings!F2:F50)
Total Annual Dividend Income
=SUM(Holdings!N2:N50)
Portfolio Yield
=SUM(Holdings!N2:N50)/SUM(Holdings!H2:H50)
Top Five Positions
Use LARGE and INDEX/MATCH to list the five largest positions by market value. This immediately shows concentration risk.
Sector Allocation Pie Chart
Create a helper range that uses SUMIF to total market values by sector, then insert a pie chart. Sector allocation is one of the most important risk metrics for any portfolio, and a pie chart makes imbalances obvious at a glance.
Tracking Dividends in Detail
If dividend income matters to your strategy, track each payment, not just the yield. Add a third sheet called Dividends with these columns:
| Column | Header | Description |
|---|---|---|
| A | Ticker | Stock symbol |
| B | Ex-Dividend Date | Date you must own shares to receive the dividend |
| C | Payment Date | Date the cash hits your account |
| D | Dividend Per Share | =DividendPerShare(A2) |
| E | Shares Held | Linked from Holdings sheet |
| F | Expected Payment | =D2*E2 |
| G | Received? | Checkbox or Y/N for reconciliation |
Over time this sheet becomes a complete dividend income ledger. You can sum column F by quarter to project cash flow, or chart monthly income to visualize your passive-income trajectory.
Dividend Growth Tracking
For investors who follow a dividend-growth strategy, add a column with the prior year's dividends, for example =DividendBetweenTwoDates("AAPL", StartDate, EndDate) for the prior twelve months, and compare it against the current =DividendPerShare("AAPL"). This lets you calculate year-over-year dividend growth rates for each holding.
Historical Performance With QM_GetHistory
Historical price data adds a time dimension to the tracker. The =QM_GetHistory("AAPL") formula returns a table of historical OHLCV data that spills into adjacent cells. You can use this data to:
- Chart price history for any holding directly inside your workbook.
- Calculate trailing returns over 1 month, 3 months, 6 months, and 1 year.
- Compare individual stock performance against a benchmark like the S&P 500.
- Backtest simple strategies such as buying on dips below the 50-day moving average.
To pull historical data for Apple, simply enter in any empty cell:
=QM_GetHistory("AAPL")
Formula documentation: QM_GetHistory
The formula outputs dates, open, high, low, close, and volume into a block of cells. From there, standard Excel charting tools let you build candlestick charts, line charts, or area charts.
Sector Allocation Analysis
A sector view shows whether too much of the portfolio sits in one sector. Here is how to build a sector allocation section.
Step 1: Assign Sectors
In the Holdings sheet, make sure every row has a sector value in column C. Use consistent naming: Technology, Healthcare, Financials, Consumer Discretionary, Consumer Staples, Energy, Industrials, Materials, Utilities, Real Estate, Communication Services.
Step 2: Build a Sector Summary Table
On your Dashboard sheet, list each sector in a column. Next to each sector, use a SUMIF formula:
=SUMIF(Holdings!C:C,"Technology",Holdings!H:H)
Repeat for each sector. Add a total row and a percentage column that divides each sector's value by the portfolio total.
Step 3: Create a Pie or Donut Chart
Select the sector names and percentage values, insert a pie chart, and add data labels. Now you have an instant visual of where your money is concentrated.
Step 4: Set Allocation Targets
Add a column for your target allocation (e.g., Technology 25%, Healthcare 15%). Then add a column that calculates the difference between actual and target. Positive values mean you are overweight; negative values mean you are underweight. This makes rebalancing decisions straightforward.
Excel vs Other Portfolio Tracking Methods
An Excel tracker with MarketXLS compares with the main alternatives as follows:
| Feature | DIY Excel (No Add-in) | Google Sheets (GOOGLEFINANCE) | MarketXLS (Excel Add-in) | Web Apps (Yahoo Finance, Morningstar) |
|---|---|---|---|---|
| Live stock prices | No (manual entry) | Yes (delayed up to ~20 min) | Yes (15-min delayed on Standard; real-time and streaming on Advanced and Business) | Yes |
| Dividend data | No | Limited | Yes: =DividendYield(), =DividendPerShare() | Yes |
| P/E ratio, fundamentals | No | Limited | Yes: =PERatio(), =Revenue(), =MarketCapitalization() | Yes |
| Historical data | No | Daily prices via GOOGLEFINANCE | Yes: =QM_GetHistory() | Varies |
| Streaming intraday prices | No | No | Yes: =QM_Stream_Last() (Advanced and Business) | Some |
| Custom formulas and logic | Yes | Yes | Yes (full Excel engine) | No |
| Pivot tables and charts | Yes (powerful) | Yes (limited) | Yes (powerful) | No |
| Offline access | Yes | No | Yes | No |
| Data ownership | Full | Stored in your Google account | Full (local file) | Platform-dependent |
| Options data | No | No | Yes | Limited |
| Cost | Free | Free | Subscription from $70/month billed annually (see plans) | Free to freemium |
| Best for | Simple tracking | Casual investors, collaboration | Serious investors wanting depth | Quick overview |
Key Takeaways From the Comparison
DIY Excel without an add-in works if you only need a static record of your trades and are willing to type prices manually. It falls apart the moment you want live data or fundamental metrics.
Google Sheets with GOOGLEFINANCE is a popular free option and works well for basic price lookups. However, data is delayed roughly 20 minutes, dividend and fundamental data is limited, and there is no streaming capability. Pivot tables and charting in Sheets are also less powerful than in desktop Excel.
MarketXLS adds 1000+ financial functions to Excel for prices, fundamentals, dividends, option chains, and historical data. It suits investors who already analyze in Excel and want the tracker and the data in one workbook.
Web apps like Yahoo Finance or Morningstar are convenient for a quick glance but offer almost no customization. You cannot build your own formulas, you cannot run scenario analyses, and exporting data is often cumbersome.
Snapshot vs Streaming Prices
MarketXLS offers two ways to update prices: snapshot functions that refresh on recalculation, and streaming functions that update automatically.
Snapshot Pricing With =Last()
The =Last("AAPL") function fetches the most recent price each time the workbook refreshes. This is sufficient for end-of-day reviews or periodic check-ins during market hours. It does not continuously push new data into the cell.
Streaming Pricing With =QM_Stream_Last()
The =QM_Stream_Last("AAPL") function pushes new prices into the cell while streaming is on, so the sheet updates without manual refreshes. Streaming requires the Advanced or Business plan and uses one live symbol subscription per unique symbol.
Use =Last() for holdings you check once a day. Use =QM_Stream_Last() for positions you are actively managing or for a watchlist you monitor throughout the trading session.
Refresh Behavior
MarketXLS refreshes snapshot functions when you open the workbook or click the refresh button in the MarketXLS ribbon. Streaming functions update during market hours while streaming is on.
Advanced Features
Five extensions make the tracker more useful:
Watchlist Sheet
Create a separate sheet for stocks you are considering but do not yet own. Use the same MarketXLS formulas to pull price, P/E, dividend yield, and market cap. Add a column for your target buy price and a conditional-formatting rule that turns the row green when the current price drops below your target.
Alerts With Conditional Formatting
Excel's conditional formatting can serve as a simple alert system. For example, highlight any position where unrealized loss exceeds 10 percent in bold red. Or highlight any position where the P/E ratio exceeds 30 in orange as a valuation warning.
Transaction Log
Add a Transactions sheet that records every buy and sell with columns for date, ticker, action (buy/sell), shares, price, fees, and notes. This becomes your audit trail and feeds into tax reporting at year end. Use SUMIFS formulas on the Holdings sheet to automatically compute total shares and weighted average cost basis from the transaction log.
Multi-Currency Support
If you hold international stocks, add a column for the currency and another for the exchange rate. Multiply foreign-currency values by the exchange rate to convert everything to your home currency.
Benchmark Comparison
Pull S&P 500 historical data with =QM_GetHistory("SPY") and calculate your portfolio's return over the same period. Charting both lines on the same axis shows whether your stock-picking is adding value relative to a simple index fund.
Common Mistakes to Avoid
An investment tracker can mislead you if the data or formulas are wrong. Watch out for these pitfalls.
Not Accounting for Dividends in Total Return
If you only track price appreciation, you understate the return of dividend-paying stocks. Always include a dividend income column and add it to your total return calculation.
Ignoring Transaction Costs
Brokerage commissions and fees erode returns. Include them in your cost basis or track them in a separate column.
Hardcoding Prices Instead of Using Formulas
Typing a price manually is fine as a one-time snapshot, but it becomes stale immediately. Use =Last(A2) or =QM_Stream_Last(A2) so that your tracker reflects current prices.
Overcounting Shares After a Partial Sale
If you sell half your position, make sure the Holdings sheet reflects the updated share count. A transaction log that feeds into Holdings via SUMIFS prevents this error.
Not Backing Up Your Workbook
Your tracker contains your entire investment history. Save it to OneDrive, Google Drive, or another cloud service so a hard-drive failure does not wipe it out.
Tips for Long-Term Maintenance
A tracker is only useful if you keep it current:
- Update after every trade. Enter the transaction on the same day you execute it. Procrastination leads to missing data.
- Reconcile monthly. Compare your tracker against your brokerage statement once a month. Fix any discrepancies immediately.
- Review sector allocation quarterly. Markets shift and so does your allocation. A quarterly review ensures you rebalance before imbalances become extreme.
- Archive annual snapshots. At year end, save a copy of the workbook with the date in the filename. This gives you a permanent record you can reference at tax time.
- Keep the formula library handy. Bookmark the MarketXLS function list so you can quickly look up new formulas when you need them.
Frequently Asked Questions
How do I get live stock prices in my Excel investment tracker?
Install the MarketXLS add-in and use the formula =Last("AAPL") in any cell. Replace AAPL with the ticker symbol of your choice or reference a cell that contains the symbol. The function returns the latest available price (15-minute delayed on the Standard plan) and refreshes when Excel recalculates. For continuous intraday updates on the Advanced and Business plans, use =QM_Stream_Last("AAPL") instead.
Can I track dividends in my Excel investment tracker?
Yes. MarketXLS provides =DividendYield("AAPL") to pull the current annual yield and =DividendPerShare("AAPL") to get the dollar amount per share. Multiply DividendPerShare by your share count to calculate projected annual income for each position.
Is an Excel investment tracker better than Google Sheets?
It depends on your needs. Google Sheets offers the free GOOGLEFINANCE function, but data is delayed about 20 minutes, fundamental coverage is limited, and there is no streaming option. Excel with MarketXLS adds dividend and fundamental functions, real-time and streaming prices on the Advanced and Business plans, and Excel's pivot tables. MarketXLS also offers a Google Sheets add-on at the same pricing if you prefer Sheets.
How many stocks can I track in one Excel workbook?
Excel supports over a million rows per sheet, so the practical limit is how many formulas recalculate comfortably. Hundreds of positions are workable when spread across sheets. Streaming is limited by live symbol subscriptions (one per unique symbol). Organize by account, strategy, or asset class.
Can I track options alongside stocks in my investment tracker?
Yes. MarketXLS includes options functions that pull live option prices and Greeks. You can add an Options sheet to your workbook with columns for the option symbol, strike, expiration, premium, and current bid/ask. This keeps your stocks and options in one place. Options data is end-of-day on the Standard plan and real-time on Advanced and Business.
How do I share my investment tracker with my financial advisor?
Simply email the Excel file or share it via OneDrive or Dropbox. Because the tracker is a standard .xlsx file, your advisor can open it in any version of Excel. If they do not have MarketXLS installed, the formula cells will show the last cached values rather than live data, which is usually sufficient for a review meeting.
Getting Started With MarketXLS
MarketXLS runs as a Windows desktop add-in and as a Microsoft 365 add-in for Excel on Mac and Excel for the web, with 1000+ functions covering stocks, ETFs, mutual funds, options, and crypto.
Here is a quick summary of the key formulas used in this guide:
| Formula | What It Returns |
|---|---|
=Last("AAPL") | Latest stock price |
=QM_Last("MSFT") | On-demand QuoteMedia quote |
=QM_Stream_Last("AAPL") | Streaming price (Advanced and Business) |
=DividendYield("AAPL") | Trailing dividend yield (decimal) |
=DividendPerShare("AAPL") | Trailing twelve-month dividend per share ($) |
=PERatio("AAPL") | Price-to-earnings ratio |
=Revenue("AAPL") | Trailing twelve-month revenue |
=MarketCapitalization("AAPL") | Market capitalization |
=QM_GetHistory("AAPL") | Historical OHLCV data |
Which Plan Is Right for You?
MarketXLS US plans are Standard ($70/month billed annually; 15-minute delayed stock quotes, end-of-day options), Advanced ($125/month billed annually; real-time streaming stocks and options), and Business ($200/month billed annually; adds real-time forex and indices). Quarterly billing is also available. See the pricing page for details.
Ready to build your own investment tracker in Excel? Get started with MarketXLS and start pulling live data into your spreadsheets today.
