Book Value Per Share (Historical) Formula in Excel

Are you looking for a quick way to measure a company's net equity on a per-share basis using Excel? The Book Value Per Share (Historical) formula in Excel (with MarketXLS) can help you do just that. This powerful function retrieves historical book value per share data, making it easy to analyze trends over specific years, quarters, or trailing periods.

Understanding Book Value Per Share (Historical)

• Purpose: The Book Value Per Share (Historical) formula calculates how much shareholder equity is available for each outstanding share, using historical data.
• Key Benefits:
  • Provides insight into a company's net worth on a per-share basis
  • Useful in fundamental analysis and valuation models
  • Allows easy comparison over different periods to track company growth or decline
• When to Use:
  • Compare a company's current book value per share to past periods.
  • Evaluate value-based metrics alongside other financial ratios.

? Pro Tip: A higher book value per share generally indicates that the company has more net equity relative to the number of shares issued, though it should always be used with other metrics for a complete analysis.

Syntax and Parameters

=hf_Book_Value_per_Share(Symbol, Year, [Quarter], [TTM])

?? Note: This function requires a valid MarketXLS subscription that includes historical fundamental data.

Return Value

• Returns a numeric value representing the book value per share for the specified period.
• If the data is unavailable or an error occurs, the function returns "NA".

Examples and Usage

Below are some practical examples demonstrating various ways to use hf_Book_Value_per_Share in Excel:

1. Using a direct symbol and year:

   =hf_Book_Value_per_Share("MSFT", 2022)

2. Referring to a symbol from a cell reference:

   =hf_Book_Value_per_Share(A1, 2022)

3. Specifying quarter and TTM to get trailing twelve months:

   =hf_Book_Value_per_Share("MSFT", 2023, 2, "TTM")

4. Using different types of date inputs:
   • Cell reference:

     =hf_Book_Value_per_Share(A1, TEXT(B1,"yyyy"))

   • Direct date:

     =hf_Book_Value_per_Share("MSFT", "2024-03-15")

   • Excel date function:

     =hf_Book_Value_per_Share("MSFT", TEXT(A1,"yyyy-mm-dd"))

5. Using different symbol formats:
   • Regular security:

     =hf_Book_Value_per_Share("MSFT", "ly")

   • Index:

     =hf_Book_Value_per_Share("^SPX", 2022, 3)

   • Option:

     =hf_Book_Value_per_Share("@MSFT 110122C00020000", "lq-1")

   • Cryptocurrency:

     =hf_Book_Value_per_Share("BTCUSD:DEFAULT", "lt")

? Pro Tip: Combine the Book Value Per Share (Historical) formula with other historical fundamentals (e.g., revenue, cost of revenue) to build comprehensive stock analysis worksheets.

Common Questions

1. What if the function returns "NA"?

• This can happen if the symbol is invalid, if historical data for the specified period is unavailable, or if you do not have the required subscription.

2. Can I use this function for non-U.S. equities?

• Yes, MarketXLS supports many international symbols as long as the data subscription covers that market. Always check symbol coverage before usage.

3. Does TTM data cover exactly 12 months?

• The trailing twelve months are based on quarterly data—MarketXLS calculates the most recent four quarters to estimate TTM values.

4. Are there any performance considerations?

• Retrieving large amounts of historical data repeatedly can slow down your spreadsheet. To optimize, consider limiting the frequency of recalculations or storing data in a local table.

Use the Book Value Per Share (Historical) formula alongside other fundamental metrics to deepen your analysis of a company’s financial health. Including this function in your Excel toolkit helps you quickly spot shifts in shareholder equity over time and aids in making more informed investment decisions.