Google Sheets Portfolio Tracker: Track Investments With Live Data and Analytics

A Google Sheets portfolio tracker lets you monitor your investments from any device, share with your advisor, and build custom analytics that no app can match. But most Google Sheets portfolio trackers are limited by GOOGLEFINANCE, which only provides delayed prices and basic data points.

This guide shows you how to build a professional portfolio tracker in Google Sheets using MarketXLS with real-time prices, P/L tracking, dividend income monitoring, risk analytics, and fundamental data for every holding.

Why Build a Portfolio Tracker in Google Sheets

Google Sheets has advantages over dedicated portfolio apps:

• Full customization: Build exactly the dashboard you want with the metrics that matter to you
• Collaboration: Share with your advisor, spouse, or trading partners with one click
• Access anywhere: Works on desktop, tablet, and phone through a browser
• No app lock-in: Your data stays in your spreadsheet, not trapped in someone else's platform
• Powerful calculations: Use Google Sheets formulas for custom analytics no app provides
• Free platform: Google Sheets costs nothing to use

The missing piece has always been data. GOOGLEFINANCE provides delayed prices, no dividends, no fundamentals, and no risk metrics. MarketXLS fills every gap.

Step 1: Portfolio Holdings Sheet

Create a sheet called "Holdings" with these columns:

Enter your holdings starting at row 2. Copy all formulas down.

Portfolio Summary Row

At the bottom of your holdings, add summary formulas:

Total Market Value:  =SUM(F2:F100)
Total Cost Basis:    =SUM(G2:G100)
Total P/L ($):       =SUM(H2:H100)
Total P/L (%):       =SUM(H2:H100)/SUM(G2:G100)
Total Day P/L:       =SUM(K2:K100)

Step 2: Add Dividend Tracking

Add dividend columns to your holdings sheet or create a separate "Dividends" sheet:

Column L (Dividend Yield):

=DividendYield(A2)

Column M (Dividend Per Share):

=DividendPerShare(A2)

Column N (Annual Dividend Income):

=C2*DividendPerShare(A2)

Column O (Ex-Dividend Date):

=Ex_DividendDate(A2)

Column P (Payout Ratio):

=PayoutRatio(A2)

Dividend Summary

Total Annual Dividend Income:  =SUM(N2:N100)
Portfolio Yield on Cost:       =SUM(N2:N100)/SUM(G2:G100)
Portfolio Current Yield:       =SUM(N2:N100)/SUM(F2:F100)

This dividend tracking is impossible with GOOGLEFINANCE because it does not provide dividend yield, dividend per share, or ex-dividend dates.

Step 3: Add Fundamental Analysis

Create columns for fundamental data to evaluate each holding:

=PERatio(A2)              // P/E Ratio
=Beta(A2)                 // Beta
=EBITDA(A2)               // EBITDA
=Revenue(A2)              // Revenue
=MarketCap(A2)            // Market Cap
=BookValuePerShare(A2)    // Book Value

For deeper analysis, create a separate sheet with historical financials:

=hf_revenue("AAPL", 2024)           // 2024 Revenue
=hf_revenue("AAPL", 2023)           // 2023 Revenue
=hf_revenue("AAPL", 2022)           // 2022 Revenue
=hf_net_income("AAPL", 2024)        // Net Income
=hf_free_cash_flow("AAPL", 2024)    // Free Cash Flow
=hf_total_debt("AAPL", 2024)        // Total Debt
=hf_cash_and_equivalents("AAPL", 2024) // Cash

This gives you a complete fundamental picture of each holding in your Google Sheets portfolio tracker.

Step 4: Portfolio Allocation Analysis

Add allocation calculations to understand your portfolio composition:

Weight of each holding:

=F2/SUM($F$2:$F$100)

Sector allocation: Since MarketXLS provides =Sector(A2) for each stock, you can build a sector breakdown using SUMIFS:

=SUMPRODUCT((sector_range="Technology")*(value_range))/SUM(value_range)

This shows you what percentage of your portfolio is in each sector, helping you identify concentration risk.

Step 5: Add Risk Analytics

MarketXLS provides portfolio-level risk analytics that no GOOGLEFINANCE-based tracker can offer:

Beta for each holding:

=Beta(A2)

Portfolio Beta (weighted average):

=SUMPRODUCT(beta_column, weight_column)

MarketXLS also provides functions for calculating Sharpe ratio, Sortino ratio, Value at Risk, maximum drawdown, and correlation between holdings. These are the same risk metrics used by institutional portfolio managers and financial advisors.

Step 6: Technical Analysis Overlay

Add technical indicator columns to identify entry and exit points for your holdings:

=RSI(A2)                          // RSI
=SimpleMovingAverage(A2, 50)      // 50-day SMA
=SimpleMovingAverage(A2, 200)     // 200-day SMA
=MACD(A2)                         // MACD

Use conditional formatting to highlight:

• RSI below 30 (green, potentially oversold)
• RSI above 70 (red, potentially overbought)
• Price below 200-day SMA (yellow, downtrend warning)

Step 7: Historical Performance

Track how your portfolio has performed over time using historical price data:

=QM_GetHistory("AAPL")
=Close_Historical("AAPL", "2025-01-02")

You can calculate returns for each holding over any time period:

Return = (Current Price - Historical Price) / Historical Price

Build a performance chart by tracking your total portfolio value daily or weekly in a separate sheet.

Step 8: Real-Time Upgrades with QuoteMedia

For the most current data in your Google Sheets portfolio tracker, use QuoteMedia functions:

=QM_Last(A2)              // Real-time price
=QM_Bid(A2)               // Real-time bid
=QM_Ask(A2)               // Real-time ask
=QM_ChangePercent(A2)     // Real-time % change
=QM_ShareVolume(A2)       // Real-time volume

These functions pull data from professional trading feeds, giving you the same data quality as institutional platforms.

Portfolio Tracker for Financial Advisors

Financial advisors can use this Google Sheets portfolio tracker for client management:

Client dashboards: Share a view-only link with each client so they can see their portfolio performance in real-time. Google Sheets permissions let you control who sees what.

Multi-client tracking: Create a separate sheet for each client within the same workbook, or use separate workbooks per client.

Reporting: Use the data to generate quarterly performance reports, dividend income summaries, and risk analysis documents.

Compliance: Keep a record of all holdings, transactions, and performance data in a structured format that is easy to audit.

GOOGLEFINANCE Portfolio Tracker vs MarketXLS Portfolio Tracker

Frequently Asked Questions

How do I track my stock portfolio in Google Sheets?

Create a spreadsheet with your stock symbols, shares owned, and average cost. Use MarketXLS functions like =Last(A2) for current prices, then calculate market value, P/L, and other metrics with standard Google Sheets formulas.

Can I track dividends in a Google Sheets portfolio tracker?

Yes. MarketXLS provides =DividendYield(), =DividendPerShare(), =Ex_DividendDate(), and =PayoutRatio(). Multiply =DividendPerShare() by your shares to calculate annual dividend income for each holding.

Is a Google Sheets portfolio tracker better than a portfolio app?

For customization, sharing, and flexibility, yes. Google Sheets lets you build exactly the tracker you want with the metrics that matter to you. With MarketXLS providing 1,000+ data functions, you get more data than most portfolio apps offer.

Can my financial advisor see my Google Sheets portfolio tracker?

Yes. Share your Google Sheet with your advisor using their email address. You can give them view-only access or edit access depending on your preference.

How do I get real-time portfolio value in Google Sheets?

Use =Last() or =QM_Last() for each holding price, multiply by shares, and sum the total. This gives you a real-time portfolio value that updates automatically.

Summary

A Google Sheets portfolio tracker built with MarketXLS gives you everything a professional portfolio management tool offers: real-time prices, P/L tracking, dividend monitoring, fundamental analysis, technical indicators, risk analytics, and historical performance data. All in a spreadsheet you can customize, share, and access from any device.

Get MarketXLS for Google Sheets | View Pricing | Install from Google Workspace