Precious Metals Portfolio Tracker Excel: Monitor Gold, Silver, and Mining Stocks Live in 2026

Precious metals portfolio tracker Excel — if that's what brought you here, you're probably watching the same thing every investor is watching right now: gold testing record highs, silver surging past multi-year resistance, and mining stocks delivering outsized gains while the broader market churns. Whether you're a financial advisor building a hedge for client portfolios or a self-directed investor trying to figure out how much exposure to precious metals makes sense, the right Excel tracker can transform scattered data into a clear, actionable dashboard.

This guide walks you through building a comprehensive precious metals portfolio tracker in Excel using live MarketXLS formulas. We'll cover the ETFs, the miners, the ratios that matter, and a scoring system that ranks opportunities across value, momentum, and yield. You'll also get two downloadable templates — one pre-filled with current data, and one with live-updating formulas — so you can start tracking immediately.

Precious Metals at a Glance: Key Tickers and Metrics

Before diving into the build, here's a snapshot of the precious metals universe we'll be tracking. This table covers the core ETFs and mining stocks that give you broad exposure across gold, silver, and streaming/royalty companies.

This mix gives you exposure to physical metals (ETFs), operational leverage (miners), and cash-flow stability (streamers/royalty companies). Each behaves differently when metals prices move, which is exactly why tracking them together matters.

Why Precious Metals Are Commanding Attention in 2026

The precious metals rally unfolding in 2026 isn't happening in a vacuum. Several macro forces are converging to drive institutional and retail flows into the space:

Central Bank Demand Remains Elevated

Central banks globally have been net buyers of gold for several consecutive years. This structural demand creates a floor under gold prices that didn't exist a decade ago. The diversification away from dollar-denominated reserves is a multi-year trend, not a trade.

Silver's Industrial-Plus-Monetary Demand

Silver occupies a unique position as both an industrial metal and a monetary metal. Solar panel manufacturing, electric vehicle components, and electronics all consume physical silver. When investment demand stacks on top of industrial demand, supply constraints can amplify price moves — which is exactly what we're seeing with silver mining stocks like First Majestic (AG) posting significant gains.

Mining Stocks as Leveraged Plays

Mining companies carry operational leverage to metals prices. When gold moves from $2,000 to $2,400, a miner's profit margin doesn't expand by 20% — it can expand by 50-100% depending on their all-in sustaining cost (AISC). This is why miners like Coeur Mining (CDE) and Newmont (NEM) can deliver multiples of the underlying metal's return. But that leverage works both ways, which is why tracking these positions carefully matters.

The Gold-Silver Ratio

The gold-silver ratio — the number of silver ounces needed to buy one gold ounce — is a metric that precious metals investors watch closely. Historically, extremes in this ratio have signaled rotation opportunities between the two metals. Tracking this ratio in real time helps inform allocation decisions between gold-focused and silver-focused positions.

“

Note: This analysis is educational. Past performance and historical ratios are not reliable indicators of future results. Always conduct your own research and consult with a qualified financial advisor before making investment decisions.

Building the Precious Metals Dashboard in Excel

The core of this tracker is a dashboard that pulls live data for all 10 tickers using MarketXLS formulas. Here's how to set it up.

Input Cells: Your Personal Parameters

Every good tracker starts with input cells that drive the rest of the workbook. Set these up with yellow backgrounds (#FFFF00) and bold borders so they're easy to find:

These inputs flow through every sheet in the workbook — from position sizing to scenario analysis — so changing one number recalculates everything.

Live Data with MarketXLS Formulas

Here's how to populate your dashboard with live-updating data. Each column uses a specific MarketXLS formula:

Current Price:

=QM_Last("GLD")
=QM_Last("NEM")
=QM_Last("AG")

The QM_Last() function pulls the most recent trading price directly into your cell. For a full dashboard, you'd place the ticker symbol in a reference cell (say, column A) and use:

=QM_Last(A2)

Fundamental Metrics:

=PERatio("NEM")          → Price-to-Earnings ratio
=DividendYield("WPM")    → Annual dividend yield as a percentage
=DividendPerShare("FNV") → Dollar amount of annual dividend
=MarketCapitalization("GOLD") → Total market cap
=EarningsPerShare("NEM")        → Earnings per share
=Revenue("GOLD")         → Total revenue

Technical Indicators:

=SimpleMovingAverage("GLD", 50)  → 50-day simple moving average
=SimpleMovingAverage("SLV", 200) → 200-day simple moving average
=RSI("AG")                       → Relative Strength Index
=Beta("CDE")                     → Beta coefficient vs. S&P 500

Price Relative to Moving Average:

To determine whether a stock is trading above or below its 50-day moving average, you can create a formula column:

=QM_Last("GLD") / SimpleMovingAverage("GLD", 50) - 1

Format this as a percentage. Positive values mean the price is above the 50-day SMA (bullish momentum); negative values indicate it's below.

Conditional Formatting for Quick Visual Reads

Apply conditional formatting to make the dashboard scannable at a glance:

• RSI column: Red above 70 (potentially overbought), green below 30 (potentially oversold), neutral in between
• Price vs. SMA: Green when above 50-day SMA, red when below
• Dividend yield: Gradient from light to dark green as yield increases
• Beta: Red for beta above 1.5 (higher volatility), yellow for 1.0-1.5, green below 1.0

The Mining Stock Scoring System

Raw data is useful, but a scoring system helps you compare apples to apples across very different types of companies. Here's a three-factor scoring model that ranks each mining stock on value, momentum, and yield.

Value Score (0-10)

The value score combines P/E ratio and earnings metrics to identify miners trading at reasonable valuations:

=PERatio("NEM")    → If P/E < 15: score 8-10
                   → If P/E 15-25: score 5-7
                   → If P/E > 25: score 1-4

=EarningsPerShare("NEM")  → Higher EPS relative to price = higher value score

ETFs like GLD and SLV don't have P/E ratios in the traditional sense, so the value score for ETFs is based on their premium/discount to net asset value when available, or excluded from the scoring comparison.

Momentum Score (0-10)

Momentum captures whether the trend is working in your favor:

=RSI("AG")                        → RSI 40-60: neutral (5)
                                  → RSI 60-70: bullish (7-8)
                                  → RSI > 70: extended (3-4, caution)
                                  → RSI < 40: weak (2-3)

=QM_Last("AG") vs SimpleMovingAverage("AG", 50)
                                  → Above 50-SMA: +2 points
                                  → Above 200-SMA: +1 additional point

Yield Score (0-10)

For income-focused investors, the yield score highlights miners and streamers that return cash to shareholders:

=DividendYield("WPM")   → Yield > 3%: score 8-10
                         → Yield 1-3%: score 5-7
                         → Yield < 1%: score 2-4
                         → No dividend: score 0

Streaming and royalty companies like Wheaton Precious Metals (WPM) and Franco-Nevada (FNV) tend to score highest here, as their business models generate consistent cash flow without the operational risk of running mines.

Composite Score

The composite score weights all three factors. You can adjust the weights based on your investment approach:

The template includes dropdown selection for approach type, and the composite scores recalculate automatically.

Scenario Analysis: What If Gold Moves?

One of the most valuable sheets in the tracker is the scenario analysis. It models how your portfolio value changes across different gold price levels, accounting for the fact that miners move more than ETFs (leverage effect).

The "Portfolio Impact" column references your input cells — portfolio value, allocation percentage, and position weights — to calculate the dollar impact of each scenario on your actual portfolio.

Important: These scenario estimates are hypothetical and for educational purposes only. Actual stock price movements depend on many factors beyond the underlying commodity price, including company-specific fundamentals, market sentiment, and macroeconomic conditions. The estimated moves shown are illustrative approximations based on historical relationships that may not hold in the future.

Correlation Analysis: Diversification Within Precious Metals

Not all precious metals positions move in lockstep. Understanding the correlation between your holdings helps you build a more resilient portfolio. The correlation sheet creates a matrix showing how each ticker moves relative to every other ticker.

Key correlations to watch:

• GLD vs. SLV: Typically high correlation (0.7-0.9), but silver can diverge during industrial demand shifts
• ETFs vs. Miners: Moderate correlation. Miners carry company-specific risk that ETFs don't
• Streamers (WPM, FNV) vs. Miners (NEM, AG): Streamers tend to have lower drawdowns and more stable correlation to gold prices
• CDE vs. AG: Both are smaller miners with higher beta. High correlation between them means holding both may not add as much diversification as you'd think

The template uses color-coded conditional formatting: dark green for high correlation (>0.8), light green for moderate (0.5-0.8), yellow for low (0.2-0.5), and red for negative correlation.

Portfolio Allocation and Position Sizing

The allocation sheet translates your input parameters into concrete position sizes. Here's the logic:

1. Total precious metals allocation = Portfolio Value × Allocation %
2. Gold vs. Silver split applied based on your input ratio
3. Within each metal, positions are weighted by composite score (higher-scoring tickers get larger allocations)
4. Max position cap prevents any single ticker from exceeding your maximum single position %
5. Rebalancing signals flag when any position has drifted more than 5% from its target weight

Example output for a $100,000 portfolio with 15% precious metals allocation:

The Current Value column uses =QM_Last(ticker) * shares_held to calculate real-time position values.

How MarketXLS Makes This Possible

Building this tracker from scratch would require juggling multiple data sources, manually updating prices, and writing complex lookup formulas. MarketXLS eliminates that friction by bringing live financial data directly into Excel cells.

Here's a summary of every MarketXLS function used across the template:

Every formula in the table above has been verified against the MarketXLS function library. To explore the full list of 1,100+ available functions, visit MarketXLS Features.

Download the Templates

We've built two versions of this tracker so you can start immediately regardless of whether you have a MarketXLS subscription:

Download the templates:

• — Pre-filled with current data as of March 29, 2026. Includes a formula reference column showing which MarketXLS function generates each value.
• — Live-updating formulas that refresh automatically with your MarketXLS subscription. Every data cell uses a verified MarketXLS function.

Both templates include all six sheets: How To Use, Precious Metals Dashboard, Scenario Analysis, Mining Stock Screener, Portfolio Allocation, and Correlation Matrix. Input cells are highlighted in yellow with bold borders for easy customization.

What's in Each Sheet

How To Use: Step-by-step instructions for customizing the tracker, including how to add your own tickers, adjust scoring weights, and modify scenario assumptions. Links to MarketXLS documentation and the option to book a demo for personalized setup help.

Precious Metals Dashboard: The central hub. All 10 tickers with live price, P/E, dividend yield, market cap, 50-day SMA, RSI, and beta. Color-coded for instant visual assessment.

Scenario Analysis: Five gold price scenarios from $1,800 to $2,600 with estimated impacts across ETFs, miners, and your total portfolio. References your input cells for personalized results.

Mining Stock Screener: The three-factor scoring system (value, momentum, yield) with composite rankings. Sortable and adjustable based on your investment approach.

Portfolio Allocation: Position sizing with target weights, current values, drift calculations, and rebalancing signals. Automatically adjusts when you change your portfolio value or allocation percentage.

Correlation Matrix: Color-coded correlation table showing how each position moves relative to every other position. Essential for understanding true diversification within your precious metals allocation.

Frequently Asked Questions

How do I add more tickers to the precious metals tracker?

Simply add a new row to the dashboard sheet and enter the ticker symbol in column A. All MarketXLS formulas reference the ticker cell, so the entire row populates automatically. You can add mining stocks, ETFs, or even commodity futures symbols that MarketXLS supports. The scoring system and correlation matrix will need their ranges extended to include the new rows.

Can I track physical gold and silver prices, not just ETFs?

Yes. MarketXLS supports commodity symbols. You can use =QM_Last("GC=F") for gold futures and =QM_Last("SI=F") for silver futures to track the underlying commodity prices alongside your equity positions. This is particularly useful for calculating the gold-silver ratio in real time.

How often do the MarketXLS formulas update?

MarketXLS formulas update when you refresh your Excel workbook. The QM_Last() function pulls the most recent available price. During market hours, this gives you near-real-time data. For streaming real-time prices, MarketXLS also offers Stream_Last() functions. Visit MarketXLS to learn about refresh options.

Is this tracker suitable for financial advisors managing client portfolios?

Absolutely. The input cells make it easy to model different portfolio sizes and risk tolerances for individual clients. You can duplicate the workbook for each client, adjust the inputs, and the entire tracker recalculates. The scenario analysis sheet is particularly useful for client conversations about risk exposure. Book a demo to see how advisors are using MarketXLS.

What's the difference between mining stocks and streaming/royalty companies?

Mining companies (NEM, AG, CDE, PAAS, GOLD) operate mines and bear the full cost and risk of extraction. Their profits are highly sensitive to metals prices — when gold goes up, their margins expand dramatically (operational leverage). Streaming and royalty companies (WPM, FNV) provide upfront capital to miners in exchange for the right to purchase metals at fixed, below-market prices. This gives them exposure to metals prices with lower operational risk, more predictable cash flows, and generally higher dividend yields.

How do I calculate the gold-silver ratio in Excel?

In the template, you can add a cell with: =QM_Last("GC=F") / QM_Last("SI=F"). This divides the current gold price per ounce by the current silver price per ounce. The historical average is roughly 60-70, though it has ranged from below 20 to above 120 in extreme conditions. Some investors use extremes in this ratio to inform relative allocation decisions between gold and silver positions.

The Bottom Line

Tracking precious metals effectively requires more than watching gold tick up or down. It requires understanding how ETFs, miners, and streamers interact — how operational leverage amplifies returns (and losses), how correlation affects true diversification, and how your personal allocation targets translate into concrete position sizes.

A well-built Excel tracker gives you all of this in one place, updated in real time. The templates provided in this guide — powered by verified MarketXLS formulas — give you a starting point that you can customize to match your specific portfolio and investment approach.

Whether you're adding precious metals exposure for the first time or refining an existing allocation during this historic rally, having live data at your fingertips makes better analysis possible.

Ready to build your own live-updating financial dashboards? Visit MarketXLS to explore 1,100+ Excel functions for stocks, ETFs, options, and more. Want a personalized walkthrough? Book a demo and see how MarketXLS can streamline your investment research workflow.