1. Templates
  2. Portfolio Tracker

Build a Portfolio Tracker

Create a comprehensive portfolio tracker spreadsheet to monitor your investments in real-time.

Overview

Build a professional portfolio tracker that automatically updates with real-time prices, calculates gains/losses, and shows your overall portfolio performance.

What You’ll Build

  • Real-time portfolio value tracking
  • Individual stock performance metrics
  • Total gain/loss calculations
  • Portfolio allocation breakdown
  • Automatic data refresh

Prerequisites

  • Tickerdata installed and activated
  • Basic Google Sheets knowledge
  • A list of your stock holdings

Building the Tracker

Create the Sheet Structure

Set up your column headers in row 1:

ColumnHeader
ATicker
BShares
CCost Basis
DCurrent Price
ECurrent Value
FGain/Loss
GGain/Loss %
HDay Change %

Enter Your Holdings

In columns A-C, enter your stock holdings:

TickerSharesCost Basis
AAPL50$150.00
MSFT25$280.00
GOOGL10$125.00

Tip

Cost basis should be your average purchase price per share.

Add Current Price Formula

In cell D2, enter:

=TICKERDATALIVE(A2, "Price")

Drag this formula down for all your holdings.

Calculate Current Value

In cell E2, enter:

=B2*D2

This multiplies shares × current price.

Calculate Gain/Loss

In cell F2, enter:

=E2-(B2*C2)

This is current value minus total cost.

Calculate Percentage Gain/Loss

In cell G2, enter:

=F2/(B2*C2)

Format this column as percentage.

Add Day Change

In cell H2, enter:

=TICKERDATALIVE(A2, "Price") / (TICKERDATALIVE(A2, "Price") - TICKERDATALIVE(A2, "Change")) - 1

Or simply reference the change percentage if tracking daily performance.

Adding Portfolio Summary

Below your holdings table, add a summary section:

// Total Portfolio Value
=SUM(E:E)

// Total Gain/Loss
=SUM(F:F)

// Overall Return %
=SUM(F:F)/SUMPRODUCT(B:B,C:C)

Conditional Formatting

Make gains green and losses red:

  1. Select columns F, G, and H
  2. Go to Format > Conditional formatting
  3. Add rule: If greater than 0, format with green text
  4. Add rule: If less than 0, format with red text

Advanced Features

Portfolio Allocation

// In column I (Allocation %)
=E2/SUM($E$2:$E$100)

Sector Breakdown

// In column J (Sector)
=TICKERDATALIVE(A2, "Sector")

Info

You can use SUMIF with sectors to create a sector allocation summary.

Dividend Tracking

// Annual Dividend per Share
=TICKERDATALIVE(A2, "Dividend")

// Total Annual Dividend Income
=B2 * TICKERDATALIVE(A2, "Dividend")

// Yield on Cost
=TICKERDATALIVE(A2, "Dividend") / C2

Adding Crypto Holdings

For cryptocurrency, use the combined symbol format:

TickerSharesCost BasisCurrent Price
BTCUSD0.5$30,000=TICKERDATALIVE(A2, "Price")
ETHUSD2.0$1,800=TICKERDATALIVE(A3, "Price")

Warning

Remember: Crypto tickers use the format "BTCUSD" not "BTC" or "BTC-USD".

Pro Tips

  1. Freeze the header row: View > Freeze > 1 row
  2. Sort by value: Keep your largest positions visible
  3. Use cell references: Put the ticker in column A and reference it in all formulas
  4. Name your ranges: Makes formulas more readable

Next Steps