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

A precious metals portfolio tracker in Excel lists your gold and silver ETFs, mining stocks, and royalty companies in one table, with a MarketXLS formula in each column: =QM_Last("GLD") for price, =PERatio("NEM") for valuation, =DividendYield("WPM") for yield, and =SimpleMovingAverage("GLD", 50) and =RSI("AG") for trend. Input cells for portfolio value and target allocation then drive position sizing, a gold-price scenario table, and rebalancing flags.

This guide shows the formulas for each column, a three-factor scoring model (value, momentum, yield), and the scenario, correlation, and allocation sheets. Two downloadable templates are included: one pre-filled with data as of March 29, 2026, and one with MarketXLS formulas that refresh when Excel recalculates.

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 covers physical metals (ETFs), operational leverage (miners), and more stable cash flows (streamers and royalty companies). Each group reacts differently when metals prices move, so tracking them side by side shows where your exposure really sits.

Factors that drive precious metals prices

Four factors explain most of what a precious metals tracker should watch:

Central Bank Demand Remains Elevated

Central banks have been net buyers of gold for several consecutive years, largely to diversify reserves away from the US dollar. This is a source of demand that sits outside ordinary investment flows.

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, and silver miners such as First Majestic (AG) tend to move more than silver itself.

Mining Stocks as Leveraged Plays

Mining companies carry operational leverage to metals prices. If gold rises 20%, a miner's profit margin can rise far more than 20%, depending on its all-in sustaining cost (AISC). For example, a miner with an AISC of $1,400 earns $600 an ounce at $2,000 gold and $1,000 an ounce at $2,400 gold, a 67% increase. That is why miners like Coeur Mining (CDE) and Newmont (NEM) can move by multiples of the metal's return. The leverage also works on the way down.

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 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 price, valuation, yield, and trend data for all 10 tickers with MarketXLS formulas.

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.

MarketXLS formulas for each dashboard column

Each dashboard column uses one MarketXLS formula. On the Microsoft 365 add-in (Excel for Mac and Excel for the web), add the mxls. prefix, for example =mxls.QM_Last("GLD").

Current Price:

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

Formula documentation: QM_Last

The QM_Last() function returns the latest available price. On the Standard plan, US stock and ETF quotes are 15-minute delayed; Advanced and Business plans include real-time data. For a full dashboard, you'd place the ticker symbol in a reference cell (say, column A) and use:

=QM_Last(A2)

Formula documentation: QM_Last

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

Formula documentation: PERatio, DividendYield, DividendPerShare, MarketCapitalization, EarningsPerShare, 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

Formula documentation: SimpleMovingAverage, RSI, Beta

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

Formula documentation: QM_Last, SimpleMovingAverage

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

Formula documentation: PERatio, EarningsPerShare

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

Formula documentation: RSI, QM_Last, SimpleMovingAverage

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

Formula documentation: DividendYield

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?

The scenario analysis sheet models how your portfolio value changes at different gold prices, with miners assumed to move more than ETFs because of operating leverage. The gold price levels below are illustrative; set them around the current gold price when you use the template.

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 current position values.

MarketXLS functions used in the tracker

The MarketXLS Excel add-in returns each value below into a cell from a ticker symbol, so the tracker needs no manual price updates or copy-paste from other sources. Example values are illustrative:

Every formula in the table is in the MarketXLS function library. The full list of 1,000+ functions is on the formulas page.

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.
• : Formulas that refresh with a 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 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?

On-demand MarketXLS formulas such as QM_Last() return a snapshot that updates when Excel recalculates. Streaming functions such as =QM_Stream_Last("GLD") update automatically while streaming is on. Data speed depends on the plan: US stocks and ETFs are 15-minute delayed on Standard and real-time streaming on Advanced and Business. See /pricing for plan details.

Is this tracker suitable for financial advisors managing client portfolios?

Yes. 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 rises, their margins expand faster than the metal price (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 gains and losses, how correlation affects diversification, and how your allocation targets translate into position sizes.

An Excel tracker built on MarketXLS formulas puts price, valuation, yield, trend, scenarios, and allocation drift in one workbook. The two templates in this guide are a starting point you can customize with your own tickers and weights.

MarketXLS plans start at $70/month (Standard, billed annually, 15-minute delayed quotes); see /pricing. For a walkthrough, book a demo.