MarketXLS

Price To Earnings Ratio (Historical)

Returns the historical price-to-earnings (P/E) ratio for a company. This classic valuation metric shows how much investors pay per dollar of earnings.

Formula

P/E Ratio = Share Price / Earnings Per Share

Parameters

Parameter Required Description
Symbol Yes Stock ticker symbol
Year Yes Fiscal year or period code
Quarter No Quarter 1-4
TTM No "TTM" for trailing twelve months

Interpretation

  • Lower P/E may indicate undervaluation or slow growth
  • Higher P/E may indicate growth expectations or overvaluation
  • Compare within same industry

Syntax

=hf_Price_to_Earnings_Ratio(Symbol, Year, [Quarter], [TTM])
Excel Desktop (Windows)

Parameters

symbolstringRequired

Stock ticker symbol

yearstringRequired

Fiscal year or period code

quarterstring

Calendar quarter (1-4)

TTMstring

Set to 'TTM' for trailing twelve months

Returns

number

P/E ratio

Examples

=hf_Price_to_Earnings_Ratio("AAPL", 2023, 4)
Q4 2023 P/E
=hf_Price_to_Earnings_Ratio("MSFT", "ly")
Last year P/E
=hf_Price_to_Earnings_Ratio("GOOGL", 2023, , "TTM")
TTM P/E ratio

When to Use

  • Historical valuation analysis
  • Valuation trend tracking
  • Mean reversion analysis
  • Industry P/E comparisons

When NOT to Use

Scenario Use Instead
Current P/E PERatio()
Forward P/E forwardPE()
PEG ratio PEGRatio()

Common Issues & FAQ

Why is P/E negative or NA?

Negative earnings result in meaningless P/E. NA may indicate no earnings data.

What's a good P/E ratio?

Varies by industry and growth. S&P 500 historical average is around 15-20.

Excel Templates Using Price To Earnings Ratio (PE Ratio - Historical)

These ready-made MarketXLS templates call hf_Price_to_Earnings_Ratio() in their worksheet formulas. Open one to see the function working inside a complete model.

Related Formulas

More MarketXLS Historical Fundamentals formulas you can use in the same worksheet:

See hf_Price_to_Earnings_Ratio used in a complete workbook: Crude Oil Stock Correlation Excel: Track Energy Sector Exposure in Real Time

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

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Price To Earnings Ratio (PE Ratio - Historical) and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions