Price To Tangible Book Ratio (Historical) Formula in Excel
The Price To Tangible Book Ratio (Historical) formula in Excel with MarketXLS helps you evaluate a company’s valuation by comparing its stock price to its tangible book value. This function queries historical data and returns the ratio for a specific year, quarter, or a trailing-twelve-month (TTM) period. By incorporating relevant fundamentals, it provides quick insights into how a company’s tangible assets compare to its current market price.
? Pro Tip: Use this function alongside other fundamental metrics (e.g., Revenue (Historical), Net Income (Historical)) for a more comprehensive financial analysis.
Understanding Price To Tangible Book Ratio (Historical)
- Purpose and Use Cases: This ratio is often used to assess if a stock is undervalued or overvalued based on its tangible book value.
- Key Benefits:
- Easy historical analysis with a single formula.
- Automatic data retrieval ensures up-to-date metrics.
- Simplifies valuation comparisons across multiple companies.
- When to Use: Utilize this ratio when you need to evaluate a company’s worth against its tangible assets, especially for industries where tangible assets are a reliable measure of value.
Syntax and Parameters
=hf_Price_to_Tangible_Book_Ratio(Symbol, year, [quarter], [TTM])
Parameter | Description | Required | Example |
---|---|---|---|
Symbol |
The ticker symbol of the company (e.g., "MSFT", "^SPX", "@MSFT 110122C00020000", or "BTCUSD:DEFAULT"). | Yes | "MSFT" |
year |
The year or special keyword for historical reference (e.g., "2022", "lq", "ly", "lt"). | Yes | 2022 |
quarter |
The quarter of the year ("1", "2", "3", or "4"). Defaults to "1" if omitted. Supports "lq-n" for last quarter offsets. | No | 2 |
TTM |
Set this to "TTM" to calculate trailing twelve months from the specified year/quarter. If empty, calculates a point-in-time. | No | "TTM" |
?? Note:
- Returns "NA" if the symbol is invalid, the license is not valid, or data is unavailable.
- The function outputs a numeric value representing the Price to Tangible Book ratio.
Examples and Usage
Below are some practical ways to use the hf_Price_to_Tangible_Book_Ratio
formula in Excel:
-
Basic usage with a specific year:
=hf_Price_to_Tangible_Book_Ratio("MSFT", 2022)
Retrieves the ratio for the year 2022.
-
Specify a particular quarter:
=hf_Price_to_Tangible_Book_Ratio("MSFT", 2022, 2)
Returns the ratio for Q2 of 2022.
-
Trailing twelve months from a specific quarter:
=hf_Price_to_Tangible_Book_Ratio("MSFT", 2022, 3, "TTM")
-
Last quarter, last year, and variations:
• Last quarter:=hf_Price_to_Tangible_Book_Ratio("MSFT", "lq")
• Last quarter offset by 1:
=hf_Price_to_Tangible_Book_Ratio("MSFT", "lq-1")
• Last year:
=hf_Price_to_Tangible_Book_Ratio("MSFT", "ly")
• Last 12 months:
=hf_Price_to_Tangible_Book_Ratio("MSFT", "lt")
? Pro Tip: Combine this formula with standard Excel references. For instance, place the year in cell A1 and use:
=hf_Price_to_Tangible_Book_Ratio("MSFT", A1)
Date Input Formats
Although this function primarily uses a year and quarter, you can adapt date references if needed (for broader usage in MarketXLS functions):
- From a cell reference (e.g., cell A1 contains 2024-03-15):
=hf_Price_to_Tangible_Book_Ratio("MSFT", TEXT(A1,"yyyy"))
- Directly typing a date string:
=hf_Price_to_Tangible_Book_Ratio("MSFT", "2024")
- Using Excel date functions:
=hf_Price_to_Tangible_Book_Ratio("MSFT", TEXT(A1,"yyyy"))
Common Questions
-
What does the formula return when data is unavailable?
- It returns "NA". This can occur if the symbol is not recognized or the license validation fails.
-
Can this formula slow down my spreadsheet?
- Multiple calls to external data sources can increase loading times. To address this:
- Use fewer instances of the formula on a single worksheet.
- Consider caching values or referencing a single cell containing the formula.
- Multiple calls to external data sources can increase loading times. To address this:
-
Does it work with all stock exchanges and asset types?
- Yes, you can use this formula with equities, indices, options, and crypto, provided MarketXLS has the data coverage.
-
What if I want to compare multiple symbols at once?
- Use a table of symbols and reference them in each formula. This allows quick scanning across companies or asset classes.
?? Note: The resulting metric should be used as part of a broader financial analysis. Always consider other ratios and market conditions before making investment decisions.