Energy Stock Screener Excel: Build an Oil Crisis Portfolio Analysis Template in 2026

Energy stock screener excel tools have become essential for investors navigating the current energy market upheaval. With Iran ground operations escalating across the Middle East, oil supply disruptions rippling through global markets, and crude prices swinging between panic and euphoria, having a structured approach to screening energy stocks is no longer optional — it is a survival skill. This guide walks you through building a comprehensive energy stock screening template in Excel using live MarketXLS formulas, complete with scoring systems, scenario analysis, and portfolio allocation models designed specifically for the energy sector.

Quick Energy Stock Comparison — March 2026

Before diving into the template build, here is a snapshot of key energy stocks across sub-sectors as of March 2026:

These scores are generated by the template's weighted scoring system, which accounts for valuation, income, momentum, and distance from 52-week highs. The downloadable template lets you adjust inputs to generate your own scores in real time.

Why Energy Stocks Need a Dedicated Screener Now

The energy sector in March 2026 is unlike anything investors have seen in years. The Iran ground war has disrupted Middle Eastern oil supply chains, pushing Brent crude above $95/barrel and injecting a geopolitical risk premium into every energy name. But not all energy stocks respond equally to these forces.

Generic stock screeners treat energy companies the same as tech or consumer staples. They filter on P/E, market cap, and dividend yield without understanding the nuances that drive energy stock performance: oil price sensitivity, crack spreads, pipeline contract structures, reserve replacement ratios, and sub-sector dynamics.

A dedicated energy stock screener in Excel addresses this gap. By building your own screening tool with MarketXLS formulas, you gain the ability to:

• Filter by energy sub-sector — upstream exploration companies behave differently from midstream pipeline operators and downstream refiners
• Model oil price scenarios — see how each stock's fundamentals shift across $60 to $120 oil
• Score stocks on energy-specific criteria — weighting metrics that matter for the sector rather than applying generic screens
• Track dividend sustainability — critical for midstream income investors who need to assess payout ratios against commodity cycles
• Monitor geopolitical sensitivity — using beta and volatility metrics to gauge exposure to Middle Eastern supply disruptions

The energy sector has outperformed the S&P 500 by a significant margin since tensions escalated in early 2026. But within the sector, performance dispersion is enormous. Upstream E&P companies have surged on higher crude prices while downstream refiners have seen mixed results depending on crack spread dynamics. Midstream operators, largely insulated by fee-based contracts, have offered stability but less upside. A screener that captures these distinctions is worth building.

Understanding the Energy Sub-Sectors

Before constructing the screener, it helps to understand the three primary energy sub-sectors and how each responds differently to oil price movements.

Upstream (Exploration & Production)

Upstream companies explore for and produce crude oil and natural gas. Think Exxon Mobil (XOM), ConocoPhillips (COP), EOG Resources (EOG), Devon Energy (DVN), Occidental Petroleum (OXY), and Diamondback Energy (FANG).

Oil price sensitivity: Very high. Revenue is directly tied to commodity prices. A $10/barrel increase in crude can add billions to revenue for large-cap upstream companies. In the current environment, upstream stocks have been the primary beneficiaries of the Middle East supply disruption.

Key metrics to screen: P/E ratio, EV/EBITDA, free cash flow yield, reserve life, and production growth. Operating margin is particularly revealing — upstream companies with margins above 25% tend to be well-positioned to weather oil price corrections.

Risk profile: Cyclical and volatile. Upstream stocks have the highest beta in the energy sector, meaning they amplify market movements. OXY's beta of 1.65 and DVN's beta of 1.72 illustrate this volatility.

Midstream (Pipelines & Storage)

Midstream companies transport, process, and store oil and gas. Key names include Kinder Morgan (KMI), Williams Companies (WMB), Energy Transfer (ET), ONEOK (OKE), and Targa Resources (TRGP).

Oil price sensitivity: Low to moderate. Most midstream revenue comes from fee-based contracts tied to volumes rather than commodity prices. This makes them more stable but less explosive during oil price spikes.

Key metrics to screen: Dividend yield, debt-to-equity ratio, distributable cash flow coverage, and EV/EBITDA. Midstream investors often prioritize income stability — ET's 7.20% dividend yield and KMI's 5.65% yield make them attractive for income-focused portfolios.

Risk profile: Defensive within energy. Betas typically range from 0.78 (KMI) to 1.10 (ET), well below upstream peers. The tradeoff is lower capital appreciation potential during oil rallies.

Downstream (Refining & Marketing)

Downstream companies refine crude oil into products like gasoline, diesel, and jet fuel. Marathon Petroleum (MPC), Phillips 66 (PSX), and Valero Energy (VLO) are the major names.

Oil price sensitivity: Complex. Downstream profitability depends on crack spreads — the difference between crude input costs and refined product prices. High oil prices can actually compress margins if refined product prices do not keep pace.

Key metrics to screen: P/E ratio (typically lowest in the sector), crack spread exposure, refining capacity utilization, and free cash flow yield. VLO's P/E of 7.9 and MPC's P/E of 8.2 reflect the market's valuation of these cash-generative but cyclical businesses.

Risk profile: Moderate. Downstream stocks often act as a hedge within energy portfolios because their performance can diverge from crude oil prices. In the current environment, strong gasoline demand and tight refining capacity have supported margins despite elevated crude costs.

Building Your Energy Stock Screener in Excel

The template is structured across six sheets, each serving a distinct analytical purpose. Here is how to build the core screening dashboard using MarketXLS formulas.

Setting Up Input Cells

The dashboard begins with five input cells that control the screening logic. These cells use yellow backgrounds (#FFFF00) to indicate they are user-adjustable:

• Portfolio Size — total capital available for energy positions
• Risk Tolerance — Conservative, Moderate, or Aggressive
• Min Dividend Yield — minimum acceptable dividend yield percentage
• Max P/E Ratio — maximum price-to-earnings threshold
• Min Market Cap — minimum market capitalization in billions

Pulling Live Data with MarketXLS Formulas

Each stock row in the dashboard uses MarketXLS formulas to pull real-time data. Here are the key formulas for a single stock (using XOM as an example):

Price:              =Last("XOM")
Market Cap:         =MarketCapitalization("XOM")
P/E Ratio:          =PERatio("XOM")
Dividend Yield:     =DividendYield("XOM")
Beta:               =Beta("XOM")
RSI:                =RSI("XOM")
52-Week High:       =FiftyTwoWeekHigh("XOM")
52-Week Low:        =FiftyTwoWeekLow("XOM")
% Below 52-Wk High: =PercentBelowFiftyTwoWeekHigh("XOM")
Analyst Target:     =OneYrTargetPrice("XOM")

These formulas update automatically when the MarketXLS add-in refreshes, giving you a live view of the energy sector without manual data entry.

The Scoring System

The template uses a weighted scoring formula that generates a 1-to-10 score for each stock based on your inputs. The scoring logic combines:

• Valuation score — lower P/E relative to the sector earns higher points
• Income score — higher dividend yield earns higher points
• Momentum score — RSI in a healthy range (40-60) earns higher points
• Discount score — greater percentage below 52-week high earns higher points

The formula in the Score column uses a combination of these factors:

=MIN(10, MAX(1, 10 - (PERatio/5) + DividendYield + (PercentBelow52WkHigh/3)))

This creates a composite score where value-oriented, income-generating stocks that have pulled back from highs receive the highest ratings. You can adjust the weighting by modifying the divisors in the formula.

Additional Formulas for Deep Analysis

Beyond the dashboard, the template uses these MarketXLS formulas across other sheets:

EV/EBITDA:          =EnterpriseValueToEBITDA("XOM")
Enterprise Value:   =EnterpriseValue("XOM")
Free Cash Flow:     =HF_FreeCashFlow("XOM")
Operating Margin:   =OperatingMargin("XOM")
Gross Margin:       =GrossMargin("XOM")
Debt/Equity:        =TotalDebtToEquity("XOM")
Revenue:            =HF_Revenue("XOM")
Return on Equity:   =ReturnOnEquity("XOM")
PEG Ratio:          =PEGRatio("XOM")
Book Value/Share:   =BookValuePerShare("XOM")

Each formula follows the same pattern — the function name with the ticker symbol as a string argument. This consistency makes it straightforward to replicate the screener across as many energy stocks as you want.

Scenario Analysis: Oil at $60 to $120

The Scenario Analysis sheet models how different oil price environments affect the top five energy stocks. This is particularly relevant in 2026, where oil prices could swing dramatically depending on the trajectory of the Iran conflict and OPEC+ production decisions.

The model uses a baseline of $85/barrel and scales revenue and margin projections proportionally:

The template version uses live formulas that scale from each company's current revenue:

Revenue at $100 oil: =HF_Revenue("XOM") * 100/85
Margin at $100 oil:  =OperatingMargin("XOM") * (0.5 + 0.5 * 100/85)

Key observations from the scenario analysis:

• Upstream companies see the most dramatic revenue swings. DVN's revenue nearly doubles from the $60 to $120 scenario
• Midstream companies show minimal revenue variation because their fee-based contracts are less sensitive to commodity prices
• Downstream refiners present a more complex picture — higher oil prices increase input costs, and margins depend on whether refined product prices keep pace

This analysis helps investors understand the asymmetric risk/reward profile of different energy sub-sectors. An investor who believes oil will stay above $100 due to prolonged Middle East tensions might overweight upstream exposure. An investor who sees potential for de-escalation and falling prices might favor midstream stability.

Portfolio Construction for Energy Exposure

The Portfolio Allocation sheet translates the screening analysis into a model portfolio framework. This is educational in nature and designed to illustrate how different risk tolerances lead to different sub-sector weightings.

Sub-Sector Allocation by Risk Tolerance

The logic is straightforward:

• Conservative portfolios emphasize midstream for income stability and lower volatility
• Moderate portfolios balance upstream growth potential with midstream income
• Aggressive portfolios concentrate on upstream E&P for maximum oil price leverage

Position Sizing

The template calculates position sizes based on portfolio size and allocation percentages. For a $100,000 moderate portfolio:

Upstream allocation:  $45,000 across 4 positions (~$11,250 each)
Midstream allocation: $35,000 across 3 positions (~$11,667 each)
Downstream allocation: $20,000 across 2 positions (~$10,000 each)

Share counts are calculated using live prices:

Shares of XOM: =INT(DollarAmount / Last("XOM"))

Dividend Income Projection

The template projects annual dividend income for the model portfolio using:

Annual dividend per position: =DollarAmount * DividendYield("TICKER") / 100

For the moderate $100,000 model, the template estimates approximately $3,800 to $4,200 in annual dividend income, representing a blended yield of around 3.8% to 4.2%. This yield is significantly above the S&P 500 average, reflecting the energy sector's generally higher income characteristics — particularly from midstream holdings like ET (7.20% yield) and KMI (5.65% yield).

The Template — What's Inside

The downloadable template contains six fully built sheets:

Sheet 1: How To Use

A guide page explaining each sheet, how to set inputs, and how MarketXLS formulas work. Includes links to MarketXLS and the demo booking page.

Sheet 2: Energy Dashboard

The main screening interface with 15 energy stocks, input cells for customization, and the weighted scoring system. All data cells use live MarketXLS formulas in the template version. Headers use MarketXLS blue (#0066CC) with white text, alternating row colors for readability, and frozen panes for easy scrolling.

Sheet 3: Scenario Analysis

Oil price scenario modeling from $60 to $120/barrel showing estimated revenue and margin impacts for the top five energy stocks. Helps visualize how portfolio value shifts under different commodity price assumptions.

Sheet 4: Upstream vs Midstream vs Downstream

A sub-sector comparison sheet categorizing all stocks by their position in the energy value chain. Key metrics include EV/EBITDA, FCF yield, dividend yield, and debt-to-equity ratio with educational notes on each sub-sector's characteristics.

Sheet 5: Portfolio Allocation

A model portfolio allocation framework with position sizing, share count calculations, and dividend income projections. Tied to the Portfolio Size and Risk Tolerance inputs from the Dashboard sheet.

Sheet 6: Correlation Matrix

A cross-stock metric comparison matrix covering P/E, EV/EBITDA, dividend yield, debt-to-equity, RSI, beta, operating margin, and percentage below 52-week high. Color-coded with green for attractive values and red for caution signals.

Key MarketXLS Formulas Used Throughout

All formulas require the MarketXLS Excel add-in. Visit the pricing page for subscription options.

Download the Templates

Download the templates:

• — Pre-filled with March 2026 data
• — Live-updating formulas

The static version includes sample data and formula references so you can see exactly which MarketXLS functions populate each cell. The template version contains live formulas that update when you open the file with the MarketXLS add-in installed.

Frequently Asked Questions

How do I screen energy stocks in Excel?

Build a screening dashboard with columns for key metrics — price, P/E ratio, dividend yield, beta, RSI, and 52-week price range. Use MarketXLS formulas like =Last("XOM"), =PERatio("XOM"), and =DividendYield("XOM") to pull live data. Add input cells to set minimum and maximum thresholds, then create a scoring formula that weights each metric according to your investment criteria. The downloadable template above provides a complete ready-to-use framework.

What metrics matter most for energy stock analysis?

The most important metrics vary by sub-sector. For upstream E&P companies, focus on EV/EBITDA, free cash flow yield, and operating margin — these capture how efficiently a company converts oil production into cash. For midstream operators, prioritize dividend yield, debt-to-equity, and distributable cash flow coverage. For downstream refiners, watch P/E ratio, crack spread sensitivity, and refining capacity utilization. Across all sub-sectors, beta helps gauge how much a stock amplifies broader market and oil price movements.

How do oil prices affect different energy sub-sectors?

Upstream companies have the most direct relationship — a $10/barrel increase in crude typically boosts upstream revenue by 10-15% depending on production mix and hedging. Midstream companies are largely insulated because their revenue comes from fee-based transportation and processing contracts tied to volumes rather than prices. Downstream refiners have a complex relationship: higher oil prices increase input costs, but profitability depends on crack spreads (the gap between crude costs and refined product prices). In the current environment, strong product demand has kept crack spreads healthy despite elevated crude prices.

Can I track energy stock dividends in Excel?

Yes. Use =DividendYield("XOM") to get the current dividend yield as a percentage and =DividendPerShare("XOM") for the per-share dollar amount. For payout sustainability analysis, =PayoutRatio("XOM") shows what percentage of earnings goes to dividends. The Portfolio Allocation sheet in the template calculates projected annual dividend income for each position based on your allocation and current yields.

What is the best way to compare upstream and midstream stocks?

Use different primary metrics for each. Compare upstream stocks on EV/EBITDA, operating margin, and free cash flow yield — these reflect production efficiency and valuation. Compare midstream stocks on dividend yield, debt-to-equity, and beta — these reflect income stability and financial risk. The Upstream vs Midstream vs Downstream sheet in the template presents these metrics side by side with color coding for easy comparison. Cross-referencing both groups on the Correlation Matrix sheet reveals which stocks offer the best combination of value and income.

How do MarketXLS formulas update energy stock data?

MarketXLS formulas refresh automatically when you open your Excel workbook with the add-in installed. Each formula calls the MarketXLS data engine, which pulls current market data from exchange feeds. For example, =Last("XOM") retrieves the most recent trade price for Exxon Mobil. Formulas like =PERatio("XOM") and =DividendYield("XOM") pull calculated fundamental metrics that update as companies report earnings and declare dividends. You can also force a manual refresh at any time through the MarketXLS ribbon in Excel. Visit MarketXLS to learn more about the add-in, or book a demo to see the formulas in action.

The Bottom Line

The energy sector in 2026 presents both extraordinary opportunity and significant risk. Middle East tensions have created a volatile pricing environment where upstream, midstream, and downstream stocks respond in fundamentally different ways. A dedicated energy stock screener in Excel — powered by live MarketXLS formulas — gives you the analytical framework to screen, score, and compare these stocks systematically rather than reactively.

The template provided here covers the full workflow: from initial screening on the Energy Dashboard, through scenario modeling at different oil price levels, to sub-sector comparison and portfolio allocation. Every data cell uses verified MarketXLS formulas that update in real time, eliminating the need for manual data entry or third-party data subscriptions.

Whether you are building an income-focused portfolio around midstream dividend payers like ET and KMI, pursuing growth through upstream E&P companies like DVN and FANG, or looking for value in downstream refiners like VLO and MPC, the screener adapts to your criteria through adjustable input cells and a transparent scoring system.

To get started with live energy stock data in Excel, visit MarketXLS or book a demo to see how the formulas integrate directly into your spreadsheet workflow.