Price To Earnings Ratio (PE Ratio - Historical) Formula in Excel

Understanding Price To Earnings Ratio (PE Ratio - Historical)

The Price To Earnings Ratio (PE Ratio - Historical) formula in Excel with MarketXLS helps you quickly retrieve a company’s price-to-earnings ratio (P/E) from historical data. This powerful metric reveals how a company’s stock price compares to its per-share earnings, giving you insight into whether a stock may be over- or undervalued.

• Purpose and Use Cases
  • Compare a stock’s share price to its earnings.
  • Identify potential overvaluation or undervaluation.
  • Track changes in a company’s valuation over time.
• Key Benefits
  • Saves time by automating P/E ratio retrieval.
  • Integrates seamlessly with Excel for powerful analysis.
  • Useful for both fundamental analysis and long-term investment decisions.
• When to Use
  • When evaluating a stock’s fair value.
  • When comparing different companies’ valuation metrics.
  • When creating or updating valuation models in Excel.

Syntax and Parameters

=hf_Price_to_Earnings_Ratio(Symbol, year, [quarter], [TTM])

?? Note: An active MarketXLS subscription with historical fundamentals data is required for this function. If the subscription or symbol is invalid, the function returns "NA".

? Pro Tip: For custom date references, you can use "ly-1" (last year minus 1), "lq-1" (last quarter minus 1), and "lt" (last 12 months) to fine-tune your data lookback period.

Return Value

• Returns a numeric value representing the historical price-to-earnings ratio (P/E).
• If no valid data is available, returns "NA".

Error Handling

• Returns "NA" for invalid symbols, licensing issues, or missing historical data.
• Verify your MarketXLS data subscription settings if you encounter repeated errors.

Examples and Usage

Below are sample usage scenarios, covering both basic and advanced data lookups.

1. Basic yearly lookup:

   =hf_Price_to_Earnings_Ratio("MSFT", 2022)

   Retrieves the P/E ratio for Microsoft for the year 2022 (calendar quarter defaults to 1).

2. Specifying a quarter:

   =hf_Price_to_Earnings_Ratio("MSFT", 2022, 2)

   Returns the P/E ratio for the second quarter of 2022.

3. Trailing twelve months (TTM):

   =hf_Price_to_Earnings_Ratio("MSFT", 2022, 3, "TTM")

   Returns the trailing 12 months’ P/E ratio for Microsoft using Q3 2022 data as the reference point.

4. Last quarter data:

   =hf_Price_to_Earnings_Ratio("MSFT", "lq")

   Fetches the most recently available quarterly P/E ratio for Microsoft.

5. Last year minus one:

   =hf_Price_to_Earnings_Ratio("MSFT", "ly-1")

   Pulls the P/E ratio for one year before the last completed year.

Using Date References in Other Ways

For date-based references (though year/quarter formats are generally recommended for this function), you could also use:

• Cell references for date:

  =hf_Price_to_Earnings_Ratio(A1, "2022")

  (Where cell A1 might contain "MSFT" or another valid symbol.)
• Direct date strings (less common for this function, but permissible if year parsing is done accordingly):

  =hf_Price_to_Earnings_Ratio("MSFT", "2024-03-15")

• Excel date functions (again, typically you’d convert these to valid year strings, but included here for completeness):

  =hf_Price_to_Earnings_Ratio(TEXT(A1,"yyyy-mm-dd"), "2022")

Common Questions

1. Why am I getting "NA"?

• Confirm your MarketXLS subscription includes historical fundamentals.
• Check the symbol formatting. For example, "MSFT" vs. "MSFT " (no trailing space).
• Ensure your year or quarter values are valid.

2. Can I use this function for cryptocurrency?

• Yes, use symbols like "BTCUSD:DEFAULT".
• Make sure your plan supports historical data for the desired asset class.

3. Are there performance considerations?

• This formula fetches data through MarketXLS APIs.
• MarketXLS uses caching to improve performance, but multiple consecutive requests for many symbols may still impact Excel’s speed.
• For large-scale computations, consider batching your requests or using fewer real-time calls.

4. What if I want to compare multiple metrics?

• Use similar historical fundamental functions (e.g., Revenue (Historical), Cost of Revenue (Historical), etc.) in adjacent cells to quickly compare ratios.
• Create dashboards or custom templates leveraging multiple MarketXLS functions to analyze trends cross-metric and cross-symbol.

? Pro Tip: Explore related historical fundamental functions like Revenue (Historical) or Gross Profit (Historical) to get a comprehensive financial perspective.