How to Make a Stock Tracker in Google Sheets

Building your own stock tracker is a great way to take control of your investment portfolio without relying on third-party apps. This guide will walk you through creating a powerful, real-time stock portfolio tracker from scratch using nothing more than Google Sheets and one powerful formula.

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

Why Use Google Sheets for Stock Tracking?

Before diving into the steps, it's worth knowing why Google Sheets is such a great tool for this job. While there are plenty of dedicated portfolio tracking apps, a custom spreadsheet offers unique advantages:

• It's Free: Google Sheets is completely free to use with a Google account. You don't need to pay for any subscriptions or deal with feature limitations.
• It's Customizable: You have total control. You can track exactly what you want, calculate custom metrics, and organize your dashboard in a way that makes sense to you.
• It's Cloud-Based: Access your portfolio from any device, anywhere. Your data is always saved and synced automatically, so you don't have to worry about losing your work.
• Powerful Integrations: The secret ingredient is the GOOGLEFINANCE function, a built-in tool that pulls live and historical financial data directly into your sheet.

Setting Up the Basic Structure

First, let's lay the groundwork. Open a new Google Sheet and create the column headers for your tracker. This structure will hold all your key portfolio information. We will build on this foundation step-by-step.

Here are the essential columns you’ll want to start with. Type each of these into the first row (A1, B1, C1, and so on):

• Ticker Symbol
• Name
• Shares Owned
• Purchase Price
• Cost Basis
• Current Price
• Market Value
• Gain/Loss ($)
• Gain/Loss (%)

You can always add more columns later, but this is a solid start for a comprehensive overview of your investments.

The Magic Behind It All: The GOOGLEFINANCE Function

The entire tracker is powered by a single, versatile function: GOOGLEFINANCE. This function fetches current or historical financial data from the Google Finance service right into your spreadsheet cells.

The basic syntax looks like this:

=GOOGLEFINANCE(ticker, [attribute])

• ticker: This is the stock symbol for the company you want to track, like "GOOG" for Alphabet or "AAPL" for Apple. It should be enclosed in quotes or referenced from another cell.
• attribute (optional): This tells the function what specific piece of data you want to pull. If you don't specify an attribute, it will default to the current price.

Here are some of the most useful attributes you’ll use:

• "price": The real-time price (up to a 20-minute delay).
• "name": The full name of the company or fund.
• "marketcap": The company's market capitalization.
• "pe": The price-to-earnings ratio.
• "eps": Earnings per share.
• "high52": The 52-week high price.
• "low52": The 52-week low price.

For example, to get Apple’s current stock price, you would use the formula:

=GOOGLEFINANCE("AAPL", "price")

And to get its full name, you’d use:

=GOOGLEFINANCE("AAPL", "name")

Now that you understand the core function, let's put it to work building your 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

Building Your Google Sheets Stock Tracker, Step-by-Step

With your headers in place and a basic understanding of GOOGLEFINANCE, you're ready to start populating your tracker with real data. We’ll go column by column.

Step 1: List Your Stock Tickers

In your first column, "Ticker Symbol" (Column A), list the stock symbols for all the investments you own. Start from cell A2 and list one ticker per row.

For example:

• A2: GOOG
• A3: AAPL
• A4: MSFT
• A5: AMZN

Tip: Make sure to include the exchange prefix for stocks outside the US, like "TSE:SHOP" for Shopify on the Toronto Stock Exchange.

Step 2: Automatically Fetch Company Names

You don't need to type out company names manually. Let Google Sheets do the work.

In cell B2, enter:

=GOOGLEFINANCE(A2, "name")

This formula looks at the ticker symbol in cell A2 ("GOOG") and automatically pulls its full name ("Alphabet Inc."). Now, click on cell B2, grab the small blue square (the fill handle) in the bottom-right corner, and drag it down. The formula will automatically adjust for each row, fetching the name for AAPL, MSFT, and so on.

Step 3: Enter Your Holding Information

The next two columns, "Shares Owned" (C) and "Purchase Price" (D), require your personal investment data. Enter the number of shares you own for each stock and the average price you paid per share.

This is the only manual data entry you’ll need for the core of the tracker.

Step 4: Calculate Your Cost Basis

Your cost basis is simply the total amount you paid for your position in a particular stock. It’s a straightforward calculation.

In cell E2, under "Cost Basis," enter:

=C2*D2

This multiplies your "Shares Owned" by your "Purchase Price." As you did with the Name column, drag this formula down to apply it to all your stocks.

Step 5: Pull the Current Stock Price

Now for the exciting part - getting live data. This is what makes your static spreadsheet a dynamic tracker.

In cell F2, under "Current Price," use:

=GOOGLEFINANCE(A2, "price")

This will fetch the most recent stock price for the ticker in cell A2. Drag the fill handle down to get the current price for all your holdings. You'll notice these values refresh periodically throughout the day.

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

Step 6: Calculate Your Current Market Value

Similar to cost basis, your market value is what your position is worth right now. The formula is almost identical.

In cell G2, under "Market Value," enter:

=C2*F2

This multiplies your "Shares Owned" by the "Current Price." Drag this formula down for all your rows.

Step 7: Calculate Your Gain or Loss in Dollars and Percentage

The ultimate goal of a tracker is to see how your investments are performing. We can calculate this in both dollar and percentage terms.

For Dollar Gain/Loss (Column H):

In cell H2, enter:

=G2-E2

For Percentage Gain/Loss (Column I):

In cell I2, enter:

=H2/E2

After entering the formula for the percentage, select the entire column I and format it as a percentage by going to Format > Number > Percent. Drag both formulas down, and boom - you have a fully functional portfolio tracker!

Enhancing Your Stock Tracker with Advanced Features

A basic tracker is good, but you can make it even better with a few extra touches. These features can provide deeper insights and make your tracker more intuitive to read.

Add Conditional Formatting for a Quick Visual

It's easier to see performance at a glance if your gains are green and your losses are red. Conditional formatting does this automatically.

1. Select your "Gain/Loss ($)" and "Gain/Loss (%)" columns (H and I).
2. Go to Format > Conditional formatting in the menu.
3. In the "Format cells if..." dropdown, choose "Greater than" and enter 0. Under "Formatting style," select a fill color like light green.
4. Click "Add another rule." This time, choose "Less than" and enter 0. Set the fill color to light red.

Now, your performance columns will instantly show you who the winners and losers are.

Create a Summary Section

Build a small summary section at the top of your sheet to see the overall portfolio performance. Find some empty cells and add these labels:

• Total Portfolio Value
• Total Cost Basis
• Total Gain/Loss

Next to each label, use the SUM function:

• Total Portfolio Value: =SUM(G2:G)
• Total Cost Basis: =SUM(E2:E)
• Total Gain/Loss: =SUM(H2:H)

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

Visualize Performance with Sparklines

A sparkline is a mini-chart that lives inside a single cell, showing a trend over time. It’s perfect for visualizing recent stock performance.

Add a new column header called "30-Day Trend." In cell J2, enter:

=SPARKLINE(GOOGLEFINANCE(A2, "price", TODAY()-30, TODAY()))

Breakdown:

• GOOGLEFINANCE(A2, "price", TODAY()-30, TODAY()) fetches the daily closing prices for the past 30 days.
• SPARKLINE(...) creates a miniature line chart of that data.

Drag this formula down for each stock.

Track Additional Metrics

Don’t stop with price! You can easily pull in other useful metrics. Add columns for "52-Week High" and "P/E Ratio" with formulas:

• 52-Week High: =GOOGLEFINANCE(A2, "high52")
• P/E Ratio: =GOOGLEFINANCE(A2, "pe")

This provides more context to assess stocks' relative peaks and valuation.

Create Portfolio Allocation Pie Chart

Understanding not just which stocks you own but how your capital is distributed among them is crucial for managing risk. A pie chart is a perfect visualization for portfolio allocation.

1. Calculate Allocation Percentage

Add a new header called "Allocation." Next to your first stock (say column K), enter:

=G2/SUM($G$2:$G$100)

$G$2:$G$100 creates an absolute reference to your "Market Value" range. Adjust 100 if needed. Format this column as a percentage.

2. Insert the Chart

1. Select both your "Name" and "Allocation" columns.
2. Go to Insert > Chart.
3. In the Chart editor, select "Pie chart" as the chart type.

Final Thoughts

Building your own stock tracker in Google Sheets using the GOOGLEFINANCE function is a straightforward and empowering way to get a clear, custom view of your investment portfolio. You can now monitor performance, track key metrics, and make more informed decisions without expensive software.

Of course, a homemade tracker is just a starting point. As your data needs grow, you might find yourself manually pulling data from SaaS platforms for marketing, sales, and other business functions. When that happens, we've automated this entire process at Graphed. We let you connect data sources like Google Sheets, Google Analytics, social media ads, and sales CRMs, then use plain English to build real-time dashboards in seconds–no fumbling with complex reporting tools needed.