Price To Book Value (PB Ratio -Historical) Formula in Excel

Understanding Price To Book Value (PB Ratio -Historical)

The Price To Book Value (PB Ratio -Historical) formula in Excel, powered by MarketXLS, helps you gauge how a company’s current market price compares to its book value over specific historical periods. This ratio is commonly used by investors to identify potentially undervalued (or overvalued) stocks, making it a key metric in fundamental analysis.

• Purpose: Quickly retrieve a company's historical price-to-book (P/B) ratio data.
• Key Benefits:
  • Evaluates how the market values a company relative to its net assets.
  • Assists in long-term, fundamentals-driven investment decisions.
  • Provides historical context to compare P/B ratios over multiple years or quarters.
• When to Use: Use this formula when you need historical P/B ratio data for valuations, trend analysis, or comparing multiple stocks over different periods.

Syntax and Parameters

Use the following syntax in Excel to call this function:

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

?? Note: If the specified symbol is invalid or your data subscription does not include historical fundamental data, the function will return "NA".

Return Value

• Returns the P/B ratio as a numeric value if data is found.
• Returns "NA" if an error occurs or if the data is not available.

Error Handling

• Invalid symbols or missing subscriptions: returns "NA".
• Non-numeric results from data feed: returns "NA".

? Pro Tip: Use valid ticker symbols and ensure you have the required MarketXLS historical fundamentals data subscription to get the most accurate results.

Examples and Usage

Basic Examples

1. Retrieve the price-to-book ratio for Microsoft for the year 2022 (1st quarter by default):

   =hf_Price_to_Book_Value("MSFT", 2022)

2. For the year 2022 and the 2nd quarter:

   =hf_Price_to_Book_Value("MSFT", 2022, 2)

3. For the year 2022 and trailing twelve months:

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

Special Year Keywords

• Last quarter’s data:

=hf_Price_to_Book_Value("MSFT", "lq")

• Last quarter minus one:

=hf_Price_to_Book_Value("MSFT", "lq-1")

• Last year’s data:

=hf_Price_to_Book_Value("MSFT", "ly")

• Last year minus one:

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

• Last twelve months:

=hf_Price_to_Book_Value("MSFT", "lt")

• Previous last twelve months:

=hf_Price_to_Book_Value("MSFT", "lt-1")

Performance Considerations

• The function queries MarketXLS’s backend historical database. For large datasets or frequent recalculations, performance may depend on your internet speed and data subscription plan.
• Caching is implemented to help with repeated requests.

Common Questions

1. Can I use cell references for Symbol and year?
   Absolutely! For example:

   =hf_Price_to_Book_Value(A2, B2)

   Where A2 might contain "MSFT" and B2 might contain "2022".

2. What if I need data for a specific date range?
   This function specifically focuses on annual or quarterly data. For more granular control, consider other MarketXLS functions or direct date references using advanced fundamental functions.

3. Why does the function keep returning "NA"?

   • Double-check your ticker symbol (e.g., "MSFT", "^SPX").
   • Ensure your MarketXLS subscription includes historical fundamentals.
   • Verify you are using supported year formats (e.g., 2022, "ly", "lq-1", etc.).

4. Is the PB ratio the same as book value per share?

   • No, the PB ratio is the price divided by the book value per share.
   • Book value per share can be retrieved by a different function (e.g., “Book Value (Historical)”) if available.

?? Note: For date-based lookups (not typical with this function), you can still apply MarketXLS best practices using: • Direct references like =hf_Price_to_Book_Value(A1)
• Text-based dates like =hf_Price_to_Book_Value("2024-03-15")
• Excel date functions like =hf_Price_to_Book_Value(TEXT(A1,"yyyy-mm-dd"))
However, for historical fundamentals, specify the relevant year and quarter parameters as shown above.

• Related Functions:
  • Revenue (Historical): Retrieves total revenue for a specified historical period.
  • Cost Of Revenue (Historical): Fetches total cost of revenue historically.
  • Gross Profit (Historical): Returns gross profit figures for a past period.
  • R & D Expenses (Historical): Provides research and development expenses historically.
  • Selling General and Administrative Expense (Historical): Offers SG&A expenses for a given historical period.