How to Create a Stock Portfolio Tracker in Excel

Manually tracking your stock portfolio can feel like a chore, but Excel offers a surprisingly powerful and flexible way to build a personalized tracker that updates with live data. Forget static spreadsheets, you can create a dynamic dashboard to monitor your investments, calculate performance, and visualize your holdings. This article will guide you step-by-step through creating your own stock portfolio tracker right inside Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Setting Up Your Portfolio Tracking Sheet

First, we need to build the foundation of your tracker. This involves creating a structured table to hold the essential information for each stock you own. A well-organized table makes it easier to add formulas and manage your data as your portfolio grows.

1. Create Your Core Columns

Open a new Excel workbook and create a new sheet. Name it something intuitive, like "Portfolio Tracker." At the top of the sheet, create headers for the following columns:

• Ticker Symbol: The unique trading symbol for the stock (e.g., AAPL for Apple Inc.).
• Company Name: The full name of the company.
• Shares Owned: The number of shares you own.
• Purchase Price: The price per share when you bought the stock.
• Purchase Date: The date you acquired the shares.
• Total Cost: The total amount you paid for the shares (Shares Owned * Purchase Price).

This is your starting point. You will add more columns later to calculate performance and pull in live data.

2. Format as an Excel Table

Converting your data range into an official Excel Table is one of the most useful things you can do. Tables offer several benefits, including structured references that make formulas easier to write and automatic formatting that extends as you add new rows.

To do this:

1. Click anywhere within your data range (including the headers).
2. Go to the Insert tab on the Excel ribbon and click Table.
3. A small dialog box will pop up. Ensure the "My table has headers" box is checked.
4. Click OK.

Your data is now in a formatted table, ready for you to input your investment details. Go ahead and fill in the information for the stocks you currently own. For the Total Cost column, you can create a simple formula. In the first cell under the Total Cost header, type:

=[@[Shares Owned]]*[@[Purchase Price]]

Because you're using a table, this formula will automatically fill down for every row you add.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pulling Live Stock Data into Excel

This is where the magic happens. Excel's built-in Stocks data type can connect to online sources to pull real-time and historical data directly into your spreadsheet, turning it from a static list into a dynamic tracking tool.

Using the Stocks Data Type

To activate this feature, Excel needs to recognize your ticker symbols. Here’s how:

1. Select all the cells in your Ticker Symbol column.
2. Go to the Data tab on the ribbon.
3. In the "Data Types" group, click on Stocks.
4. Excel will connect to the web and convert your plain-text tickers into rich data types. You'll know it worked when a small "building" icon appears next to each ticker symbol. If Excel is unsure about a symbol (e.g., a ticker exists on multiple exchanges), a question mark will appear, allowing you to select the correct one.

Now that your tickers are "live," you can pull in a huge variety of information. We'll start by adding a column for the current trading price.

1. Create a new header in your table called Current Price.
2. With a ticker cell selected (e.g., the A2 cell containing "AAPL"), a small "Add Column" icon appears to the right. Click this icon.
3. A dropdown menu of available data fields will appear. Select Price.

Excel will instantly populate the Current Price column with the latest market price for each stock in your table. To keep this data fresh, you can go to the Data tab and click Refresh All anytime you want the latest numbers.

Feel free to add other useful data points using the same method. Some other great fields to add include:

• Change %: The daily percentage change in price.
• 52-Week High: The highest price over the last year.
• 52-Week Low: The lowest price over the last year.
• Market Cap: The company's total market capitalization.
• P/E Ratio: The price-to-earnings ratio.

Calculating Key Portfolio Metrics

With your purchase data and live market prices in place, you can now calculate your portfolio's performance. Add the following columns to your table and use the formulas provided to get instant insights.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Market Value

This shows the current value of your holding in each stock. Create a new column header named Market Value and enter this formula:

=[@[Shares Owned]]*[@[Current Price]]

This multiplies the number of shares you own by the current live price to give you the total worth of that position.

Gain/Loss ($)

This classic metric shows your unrealized profit or loss in dollar amounts. Add a column header named Gain/Loss ($) and use this formula:

=[@[Market Value]]-[@[Total Cost]]

This simply subtracts your initial investment from the current market value.

Gain/Loss (%)

Seeing your return as a percentage is often more useful for comparing performance across different investments. Add a column called Gain/Loss (%) and enter:

=[@[Gain/Loss ($)]]/[@[Total Cost]]

After entering the formula, select the column and go to the Home tab. In the "Number" group, click the percentage symbol (%) to format the numbers correctly.

Feel free to enhance readability with conditional formatting: select the Gain/Loss ($) and Gain/Loss (%) columns, then go to Home > Conditional Formatting > Color Scales, and choose a red-yellow-green scale to quickly see profits and losses at a glance.

Creating a Portfolio Summary Dashboard

A detailed table is great, but a high-level summary dashboard gives you an immediate picture of your overall portfolio health. You can create this on the same sheet or a new one.

Find some empty space above or to the side of your table and set up a small area for summary metrics. Here are a few essential totals:

• Total Portfolio Value: This is the current combined value of all your investments.
• Total Cost Basis: This is the total amount of money you've invested.
• Total Portfolio Gain/Loss ($): Your total unrealized profit or loss.

Organize these with clear labels to create a simple, at-a-glance dashboard.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualizing Your Portfolio with Charts

Visualizations can reveal insights that numbers alone can't. Charts help you understand portfolio allocation and spot top performers quickly. Two excellent charts for a stock tracker are a donut chart for asset allocation and a bar chart for performance comparison.

1. Portfolio Allocation Donut Chart

This chart shows you how your capital is distributed across your different holdings, immediately highlighting if you are over-concentrated in a particular stock.

1. Select the data in your Ticker Symbol and Market Value columns.
2. Go to the Insert tab and find the "Charts" group.
3. Click the pie chart icon and select Doughnut.
4. Resize and position the chart on your dashboard. You can customize the title, add data labels, and change colors to make it clearer.

2. Gain/Loss by Stock Bar Chart

This chart makes it super easy to see which of your investments are performing the best and which are lagging.

1. Select the data in your Ticker Symbol and Gain/Loss ($) columns.
2. Go to the Insert tab and click the bar chart icon. Select a 2-D Clustered Bar or 2-D Clustered Column chart.
3. Give the chart a clear title like "Profit/Loss by Stock." Excel might automatically add a color-coded legend, making it even easier to read.

Final Thoughts

Building a stock portfolio tracker in Excel gives you complete control and a deeper understanding of your investments. Using the Stocks data type, structured tables, and a few simple formulas, you can create a powerful dashboard that updates automatically and provides actionable insights. It’s a fantastic way to move beyond basic brokerage reports and truly own your financial data.

While DIY-ing in Excel is powerful, sometimes you need to combine investment data with business metrics from tools like Google Analytics, Shopify, or Salesforce to see the full picture. We built Graphed for this exact reason. Graphed connects all your data sources and lets you build dashboards and get answers using natural language, so you can analyze your entire business's financial health, from ad spend ROI to portfolio performance, all in one place and without managing complex spreadsheets.