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])
Parameter | Description | Required | Example |
---|---|---|---|
Symbol |
The ticker symbol of the company or asset. Can be a stock (e.g., "MSFT"), index (e.g., "^SPX"), option (e.g., "@MSFT 110122C00020000"), or cryptocurrency (e.g., "BTCUSD:DEFAULT"). | Yes | "MSFT" |
year |
The year for the desired data. Supports direct year (e.g., 2022) or special keywords like "lq" (last quarter), "lq-1", "ly" (last year), "ly-1", "lt" (last twelve months), "lt-1". | Yes | 2022 / "lq" / "ly-1" |
quarter |
The calendar quarter to retrieve (1, 2, 3, or 4). If omitted, defaults to "1". | No | 2 |
TTM |
Set this parameter to "TTM" for trailing twelve-month data. Leave blank or omit for standard year/quarter calculations. | No | "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
-
Retrieve the price-to-book ratio for Microsoft for the year 2022 (1st quarter by default):
=hf_Price_to_Book_Value("MSFT", 2022)
-
For the year 2022 and the 2nd quarter:
=hf_Price_to_Book_Value("MSFT", 2022, 2)
-
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
-
Can I use cell references for
Symbol
andyear
?
Absolutely! For example:=hf_Price_to_Book_Value(A2, B2)
Where
A2
might contain "MSFT" andB2
might contain "2022". -
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. -
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.).
-
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.