Share Price Adjusted Close (Historical) Formula in Excel

Gain deeper insights into a stock’s performance by utilizing the Share Price Adjusted Close (Historical) formula in Excel. This powerful function from MarketXLS retrieves a company’s adjusted closing price for a specified year or time period, reflecting changes due to corporate events like stock splits and dividends. Use it to make more informed decisions based on accurate, historical price data.

Understanding Share Price Adjusted Close (Historical)

• The Share Price Adjusted Close (Historical) metric accounts for stock splits and dividend-derived changes to provide a more accurate reflection of the stock’s true performance.
• Adjusted close reflects a stock’s value after such adjustments, as opposed to raw closing prices that may not capture these corporate actions.
• Use this formula to:
  • Evaluate long-term investment returns.
  • Adjust for stock splits and dividends in your calculations.
  • Compare performance of different stocks over common time frames.

Syntax and Parameters

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

?? Note: If the provided symbol or licensing information is invalid, the function returns "NA".

Return Value

This function returns a numeric adjusted closing price for the specified period, reflecting historically accurate price data.

? Pro Tip: You can reference a cell containing the year, or enter the year directly. For instance:
• =hf_Share_Price_Adjusted_Close(A1, B1)
• =hf_Share_Price_Adjusted_Close("MSFT", 2022)
Advanced usage might include chaining Excel functions, such as =hf_Share_Price_Adjusted_Close("MSFT", TEXT(A1,"yyyy")).

Examples and Usage

Below are practical examples demonstrating how to use the Share Price Adjusted Close (Historical) formula in Excel:

1. Retrieve the adjusted close for a full year:

   =hf_Share_Price_Adjusted_Close("MSFT", 2022)

   This returns Microsoft’s adjusted closing price for the specified year.

2. Specify quarter and trailing twelve months (TTM):

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

   This fetches the stock’s adjusted price for Q3 2022 on a TTM basis.

3. Last quarter (LQ) references:

   =hf_Share_Price_Adjusted_Close("MSFT", "lq")

   Returns the adjusted closing price for the most recently reported quarter.

4. Offsetting past quarters and years:

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

   Retrieves the adjusted closing price for one year prior to the last reported year.

5. Different symbol types:

   • Indices:

     =hf_Share_Price_Adjusted_Close("^SPX", 2023)

   • Options:

     =hf_Share_Price_Adjusted_Close("@MSFT 110122C00020000", "lq")

   • Crypto:

     =hf_Share_Price_Adjusted_Close("BTCUSD:DEFAULT", "ly")

?? Note: For historical fundamental data, ensure your MarketXLS license includes historical fundamentals capability. Otherwise, the function may return “NA.”

Common Questions

1. Why am I getting “NA”?

• Check if your MarketXLS subscription includes historical fundamental data.
• Ensure the symbol is valid (e.g., no spelling mistakes).

2. Does this formula work for all securities?

• Yes, you can use standard stocks, indices, options, and even some cryptocurrencies, provided MarketXLS supports them.

3. How do I handle performance considerations?

• Referencing the formula multiple times with different parameters can be intensive. To improve performance:
  • Place the results in a helper cell for repeated references.
  • Use Excel caching or run calculations on demand.

4. Can I use cell references for the year?

• Absolutely. You can insert your year, quarter, or TTM indicator in cells and reference them in the formula:

  =hf_Share_Price_Adjusted_Close(A1, B1, C1, D1)

? Pro Tip: Combine various references and advanced Excel functions (like TEXT()) to dynamically set your year or quarter and streamline your workflow.

By using the Share Price Adjusted Close (Historical) formula, you’ll have more accurate insights into a security’s true historical performance, helping you make better investment decisions.