MarketXLS analyst and earnings functions return analyst coverage, price targets, recent earnings results, and forward EPS estimates in Excel. The main ones are =AnalystConsensus("AAPL") (number of analysts in the consensus), =TargetPrice("AAPL") (mean price target), =EPSSurprisePercent("AAPL") (last quarter's earnings surprise), and =EPSEstimateNextY("AAPL") (next fiscal year's consensus EPS). Each is described below with examples.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
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 optionalWeeksAgoargument to retrieve historical values. For example, use-1for 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.
| Symbol | Actual EPS | Estimate | Surprise % |
|---|---|---|---|
| GOOGL | =EPSActual(A2) | =EPSEstimate(A2) | =EPSSurprisePercent(A2) |
Price Target Analysis
Compare a stock’s price to analyst targets.
| Symbol | Current Price | Low Target | Mean Target | High Target |
|---|---|---|---|---|
| TSLA | =Last(A2) | =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: Analyst and earnings data update as new estimates and reports are published. The MarketXLS refresh button retrieves the latest values.
- License: A MarketXLS subscription that includes analyst data is required to use these functions.
