Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

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:

  1. Basic usage with a specific year:

    =hf_Price_to_Tangible_Book_Ratio("MSFT", 2022)

    Retrieves the ratio for the year 2022.

  2. Specify a particular quarter:

    =hf_Price_to_Tangible_Book_Ratio("MSFT", 2022, 2)

    Returns the ratio for Q2 of 2022.

  3. Trailing twelve months from a specific quarter:

    =hf_Price_to_Tangible_Book_Ratio("MSFT", 2022, 3, "TTM")
  4. 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.