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 spreadsheet knowledge
- A list of your stock holdings
Building the Tracker
Create the Sheet Structure
Set up your column headers in row 1:
| Column | Header |
|---|---|
| A | Ticker |
| B | Shares |
| C | Cost Basis |
| D | Current Price |
| E | Current Value |
| F | Gain/Loss |
| G | Gain/Loss % |
| H | Day Change % |
Enter Your Holdings
In columns A-C, enter your stock holdings:
| Ticker | Shares | Cost Basis |
|---|---|---|
| AAPL | 50 | $150.00 |
| MSFT | 25 | $280.00 |
| GOOGL | 10 | $125.00 |
Tip
Add Current Price Formula
In cell D2, enter:
=TICKERDATALIVE(A2, "Price")=TICKERDATA.LIVE(A2, "Price")Drag this formula down for all your holdings.
Calculate Current Value
In cell E2, enter:
=B2*D2This 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=TICKERDATA.LIVE(A2, "Price") / (TICKERDATA.LIVE(A2, "Price") - TICKERDATA.LIVE(A2, "Change")) - 1Or 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:
Select columns F, G, and H, then add two rules: greater than 0 formats green, less than 0 formats red.
The rules live under Format > Conditional formatting.
The rules live under Home > Conditional Formatting > Highlight Cells Rules.
Advanced Features
Portfolio Allocation
// In column I (Allocation %)
=E2/SUM($E$2:$E$100)Sector Breakdown
// In column J (Sector)
=TICKERDATALIVE(A2, "Sector")// In column J (Sector)
=TICKERDATA.LIVE(A2, "Sector")Info
Dividend Tracking
// Annual Dividend per Share
=TICKERDATALIVE(A2, "Last Dividend")
// Total Annual Dividend Income
=B2 * TICKERDATALIVE(A2, "Last Dividend")
// Yield on Cost
=TICKERDATALIVE(A2, "Last Dividend") / C2// Annual Dividend per Share
=TICKERDATA.LIVE(A2, "Last Dividend")
// Total Annual Dividend Income
=B2 * TICKERDATA.LIVE(A2, "Last Dividend")
// Yield on Cost
=TICKERDATA.LIVE(A2, "Last Dividend") / C2Adding Crypto Holdings
For cryptocurrency, use the combined symbol format:
| Ticker | Shares | Cost Basis | Current Price |
|---|---|---|---|
| BTCUSD | 0.5 | $30,000 | =TICKERDATALIVE(A2, "Price")=TICKERDATA.LIVE(A2, "Price") |
| ETHUSD | 2.0 | $1,800 | =TICKERDATALIVE(A3, "Price")=TICKERDATA.LIVE(A3, "Price") |
Warning
Pro Tips
- Freeze the header row - View > Freeze > 1 row in Sheets, View > Freeze Panes in Excel
- Sort by value: Keep your largest positions visible
- Use cell references: Put the ticker in column A and reference it in all formulas
- Name your ranges: Makes formulas more readable
Next Steps
- Build a Stock Analyzer for deeper analysis
- Learn about historical data with TICKERDATA
- Explore valuation models
Didn't find what you came for?
Tell us the sheet you're trying to make and we'll point you at the right numbers.