How to Create an Investment Tracker in Google Sheets

Tracking your investments scattered across different brokerage accounts and crypto exchanges can feel like a full-time job. Instead of juggling a dozen apps, you can build a powerful, automated investment tracker using a tool you already know: Google Sheets. This tutorial will walk you through setting up a live portfolio tracker, from organizing your assets to pulling real-time prices and visualizing your performance.

Why Use Google Sheets for Investment Tracking?

While dedicated apps have their place, Google Sheets offers a unique blend of flexibility, control, and cost-effectiveness. Here’s why it’s a great choice for building your own personal finance dashboard:

• It’s completely free. You don't need to pay for a subscription to get advanced functionality.
• It's cloud-based and accessible anywhere. Check your portfolio from your laptop, tablet, or phone.
• It's fully customizable. You decide what metrics matter and how to display your data. You aren’t limited by the pre-built dashboards in a standalone app.
• It has a superpower called GOOGLEFINANCE. This built-in function can automatically pull current and historical financial data - like stock prices, currency exchange rates, and market caps - directly into your spreadsheet. No more manual data entry.

Step 1: Set Up Your Basic Portfolio Structure

First, let's create a foundation for your tracker. The goal is to build a simple transaction ledger where you can record every purchase you make. Open a new Google Sheet and create the following columns:

• Asset Name: The full name of the stock, ETF, or cryptocurrency (e.g., "Google," "Bitcoin").
• Ticker Symbol: The official market symbol (e.g., "GOOGL," "BTC").
• Category: What type of asset is it? (e.g., "Stock," "ETF," "Crypto"). This will be useful later for creating charts.
• Quantity: How many shares or coins you bought.
• Purchase Price (per unit): The price you paid for one share or coin.
• Purchase Date: When you bought the asset.
• Total Cost: The total amount of money you spent on this transaction.

Now, go ahead and populate a few rows with your actual investment data. For this example, we’ll use a mix of stocks and cryptocurrencies to show how it works. After entering your initial data in columns A through F, we can calculate the Total Cost using a simple formula.

In the first cell under Total Cost (G2 in our example), type the following formula and press Enter:

=D2*E2

This formula multiplies the Quantity (cell D2) by the Purchase Price (cell E2) to get the total cost of that transaction. You can then click the small blue square in the bottom-right corner of the cell and drag it down to apply the formula to the rest of the rows.

Step 2: Pull Live Prices with the GOOGLEFINANCE Function

This is where the magic happens. We’re going to add a few more columns to our sheet to automatically fetch live market data. Create the following three new columns to the right of your Total Cost column:

• Current Price: The live market price of the asset.
• Current Value: The total current market value of your holding.
• Gain/Loss ($): Your total profit or loss in dollars.
• Gain/Loss (%): Your total profit or loss as a percentage.

To populate the Current Price, we'll use the GOOGLEFINANCE function. Its basic syntax is GOOGLEFINANCE(ticker, [attribute]).

Fetching Stock and ETF Prices

For standard stocks and ETFs traded on major exchanges like NASDAQ and NYSE, you just need to pass the ticker symbol to the function. In cell H2, we can fetch the price of Google (GOOGL) by referencing its ticker symbol in cell B2:

=GOOGLEFINANCE(B2)

Fetching Cryptocurrency Prices

Fetching crypto prices is just as easy, but you need to structure the "ticker" in a specific way: "CURRENCY:CRYPTOUSD". For example, to get the price of Bitcoin in U.S. Dollars, you'd use "CURRENCY:BTCUSD".

To make our formula work for both stocks and crypto, we can use an IF statement. This tells Sheets: "if the category is Crypto, format the ticker for crypto pricing, otherwise, use the regular stock ticker."

In the Current Price column (cell H2), enter this formula:

=IF(C2="Crypto", GOOGLEFINANCE("CURRENCY:"&B2&"USD"), GOOGLEFINANCE(B2))

Let's break that down:

• IF(C2="Crypto", ... ) checks if the value in the Category column is "Crypto".
• If it is, it runs GOOGLEFINANCE("CURRENCY:"&B2&"USD"). The & symbol combines text, so if B2 is "BTC," it creates the ticker "CURRENCY:BTCUSD".
• If it's not crypto, it defaults to the standard GOOGLEFINANCE(B2) for stocks.

Drag this formula down the column, and just like that, you have live price data for all your assets, updating automatically!

Step 3: Calculate Your Portfolio’s Performance

Now that you have your costs and a live feed of current prices, calculating your overall performance is straightforward. We'll use simple formulas for the last three columns.

1. Calculate Current Value

In the Current Value column (cell I2), multiply the Quantity you own by the Current Price:

=D2*H2

This tells you what your holding is worth right now.

2. Calculate Dollar Gain/Loss

In the Gain/Loss ($) column (cell J2), subtract your initial Total Cost from the Current Value:

=I2-G2

A positive number means you have a profit, and a negative number means you have a loss.

3. Calculate Percentage Gain/Loss

Finally, in the Gain/Loss (%) column (cell K2), divide your dollar gain/loss by your initial cost:

=J2/G2

After entering this formula, select the entire column and go to Format > Number > Percent to display the values correctly as percentages.

Once you’ve filled in these formulas for the first row, drag them all down to see your performance across your entire portfolio.

Step 4: Visualize Your Portfolio with Charts and Summaries

Numbers are great, but visuals make your data much easier to understand at a glance. We can add charts and summary totals right in our sheet.

Creating a Portfolio Allocation Pie Chart

A pie chart is perfect for visualizing how your capital is distributed across different categories (Stocks, ETFs, Crypto).

1. First, you’ll need to sum up the total value for each category. Find a clear space on your sheet and use the SUMIF function. For example: SUMIF(C:C, "Stock", I:I) would sum up the Current Value for all assets in the "Stock" category. Do this for each category you have.
2. Once you have those totals, select the category names and their corresponding total values.
3. Go to Insert > Chart.
4. In the Chart editor that appears on the right, select Pie chart.

Google Sheets will generate a clean, colorful chart showing your asset allocation. The best part? It updates automatically as your portfolio's values change.

Adding Summary Totals

It’s also helpful to have a bird's-eye view of your total portfolio performance. At the top of your sheet or at the bottom, add labels like "Total Portfolio Value" and "Total Gain/Loss". Then use the SUM formula to get your totals:

• Total Portfolio Value: =SUM(I2:I) (sums the entire Current Value column)
• Total Gain/Loss: =SUM(J2:J) (sums the entire Gain/Loss ($) column)

This gives you a quick snapshot of how you’re doing overall without needing to scan through individual rows.

Advanced Tip: Use Conditional Formatting

To make your gains and losses pop visually, use conditional formatting to color-code the Gain/Loss (%) column.

1. Select the entire Gain/Loss (%) column (column K).
2. Go to Format > Conditional Formatting.
3. In the rules pane, set the format to "Greater than" and enter 0. Now choose a green background color.
4. Click "Add another rule," set the format to "Less than" and enter 0. Choose a red background color.

Now, all your gains will be highlighted in green and all your losses in red, making it incredibly easy to see what’s working and what isn’t.

Final Thoughts

Building a dynamic investment tracker in Google Sheets puts you in control, giving you a real-time, customized view of your portfolio's performance. By leveraging the power of GOOGLEFINANCE and simple spreadsheet formulas, you can move beyond static apps and build a dashboard that truly fits your needs.

Manually setting up and maintaining different spreadsheets can still take time, especially if you also need to analyze performance from other platforms like Google Ads, Shopify or your CRM. At Graphed, we’ve made analysis even simpler by connecting to all your key data sources directly. You can create a dashboard that tracks your key business metrics just by describing what you want to see in plain English, getting back hours of your week previously spent on manual reporting.