Guide to the Updated Analyst and Earnings Functions

Written by  MarketXLS Team on 
Wed Aug 06 2025
 about MarketXLS

Meet The Ultimate Excel Solution for Investors

  • Live Streaming Prices Prices in your Excel
  • All historical (intraday) data in your Excel
  • Real time option greeks and analytics in your Excel
  • Leading data in Excel service for Investment Managers, RIAs, Asset Managers, Financial Analysts, and Individual Investors.
  • Easy to use with formulas and pre-made sheets

MarketXLS has added a new set of functions for analyst and earnings data. This guide provides an overview of each function and its usage.

Analyst Coverage Functions

These functions provide data on the number of analysts covering a stock.

  • =AnalystConsensus(Symbol): Returns the number of analysts whose estimates are included in the consensus figures.

Example:

=AnalystConsensus(“AAPL”) returns the number of analysts whose estimates are included in the consensus figures for Apple.


Price Target Functions

These functions return the consensus price targets for a stock.

  • =TargetPrice(Symbol, [WeeksAgo]): Returns the average (mean) analyst price target. Use the optional WeeksAgo argument to retrieve historical values. For example, use -1 for the target from one week ago.
  • =TargetPriceHigh(Symbol): Returns the highest analyst price target.
  • =TargetPriceLow(Symbol): Returns the lowest analyst price target.
  • =TargetPriceMean(Symbol): Returns the average (mean) analyst price target.

To get the date of the latest estimate, add “date” as the second argument. For example: =TargetPrice("MSFT", "date").


Recent Earnings Performance

These functions provide key figures from a company’s most recently reported quarter.

  • =EPSActual(Symbol): Returns the actual EPS the company reported.
  • =EPSEstimate(Symbol): Returns the consensus EPS estimate for the last quarter.
  • =EPSSurprise(Symbol): Returns the dollar difference between actual and estimated EPS.
  • =EPSSurprisePercent(Symbol): Returns the earnings surprise as a percentage.

Example:

=EPSSurprisePercent(“NFLX”) returns the percentage difference between Netflix’s actual and estimated EPS for the last reported quarter.


Forward-Looking Earnings Estimates

These functions provide consensus earnings estimates for future periods.

For Quarters and Years

  • =EPSEstimateCurrentQ(Symbol): Returns the consensus EPS estimate for the current quarter.
  • =EPSEstimateNextQ(Symbol): Returns the consensus EPS estimate for the next quarter.
  • =EPSEstimateCurrentY(Symbol): Returns the consensus EPS estimate for the current fiscal year.
  • =EPSEstimateNextY(Symbol): Returns the consensus EPS estimate for the next fiscal year.

For Long-Term Growth

  • =EPSGrowth5Y(Symbol): Returns the consensus forecast for the average annual EPS growth rate over the next five years.

Putting the Functions to Use

The following examples demonstrate how to combine these functions in a spreadsheet.

Earnings Surprise Analysis

Track recent performance with the following structure.

SymbolActual EPSEstimateSurprise %
GOOGL=EPSActual(A2)=EPSEstimate(A2)=EPSSurprisePercent(A2)

Price Target Analysis

Compare a stock’s price to analyst targets.

SymbolCurrent PriceLow TargetMean TargetHigh Target
TSLA[Market Price]=TargetPriceLow(A2)=TargetPrice(A2)=TargetPriceHigh(A2)

Other Earnings Functions

  • =EarningsOpportunitiesNext7Days(): Returns a table of all earnings opportunities in the next 7 trading days with target price momentum analysis.
  • =EarningsRunUpAnalysis(): Returns a table with earnings run-up analysis showing historical price movements before earnings.

Common Errors

  • #NAME? Error: This error indicates the MarketXLS add-in is not active. Ensure it is installed and enabled in Excel.
  • “NA” Value: A function returns “NA” when the ticker symbol is invalid or when the requested data for that symbol does not exist, such as for a company with no analyst coverage.
  • Data Freshness: Data is updated in real-time. The MarketXLS refresh button retrieves the latest information.
  • License: A MarketXLS subscription that includes analyst data is required to use these functions.
Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
Call: 1-877-778-8358
Ankur Mohan MarketXLS
Welcome! I'm Ankur, the founder and CEO of MarketXLS. With more than ten years of experience, I have assisted over 2,500 customers in developing personalized investment research strategies and monitoring systems using Excel.

I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.
Implement "your own" investment strategies in Excel with thousands of MarketXLS functions and templates.
MarketXLS provides all the tools I need for in-depth stock analysis. It's user-friendly and constantly improving. A must-have for serious investors.

John D.

Financial Analyst

I have been using MarketXLS for the last 6+ years and they really enhanced the product every year and now in the journey of bringing in AI...

Kirubakaran K.

Investment Professional

MarketXLS is a powerful tool for financial modeling. It integrates seamlessly with Excel and provides real-time data.

David L.

Financial Analyst

I have used lots of stock and option information services. This is the only one which gives me what I need inside Excel.

Lloyd L.

Professional Trader

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord

Related Formulas

No related formulas found.

Related Templates

No related templates found.