Investments NonCurrent (Historical) Formula in Excel

Investments NonCurrent (Historical) in Excel with MarketXLS lets you retrieve a company’s long-term investment figures for a specified historical period. Long-term (or non-current) investments are those that the company does not expect to liquidate or fully realize within the current accounting year. By integrating this function into your Excel workflow, you can streamline your financial analysis, compare data across multiple years or quarters, and make well-informed investment decisions.

Understanding Investments NonCurrent (Historical)

  • Purpose and Use Cases
    Investments NonCurrent (Historical) helps you uncover details about a company’s long-term (illiquid) investments for a particular period. This can be vital for understanding a company’s strategic asset allocation and long-term financial commitments.

  • Key Benefits

    • Quickly retrieve the value of a company’s non-current investments for a specific year or quarter.
    • Compare annual or quarterly trends in long-term investment strategies.
    • Combine with other historical fundamental functions for deeper analysis of financial stability.
  • When to Use
    Use this function when performing valuation analyses, building historical trend reports, or conducting fundamental research on a company’s balance sheet composition.

Syntax and Parameters

=hf_Investments_NonCurrent(Symbol, Year, [Quarter], [TTM])
Parameter Description Required Example
Symbol The ticker symbol or identifier of the security (e.g., "MSFT" for Microsoft). Supports indices (e.g., "^SPX"), options (e.g., "@MSFT 110122C00020000"), and crypto (e.g., "BTCUSD:DEFAULT"). Yes "MSFT"
Year The reporting year. You can also use special values like "lq", "lq-1", "ly", "ly-1", "lt", "lt-1" to reference last quarter/year or trailing 12 months. Yes 2022
Quarter An optional quarter value ("1", "2", "3", "4"). Defaults to "1" if not provided. No 2
TTM An optional indicator for trailing twelve months ("TTM"). Leave blank ("") if not needed. No "TTM"

Return Value
• Typically returns a numeric value indicating long-term investments for the specified period.
• Returns "NA" if the symbol is invalid, the license is not valid, historical data is not available, or any runtime error occurs.

?? Note: You must have the required MarketXLS subscription to access historical fundamental data. Otherwise, the formula may return "NA."

Examples and Usage

Below are some practical ways to use the Investments NonCurrent (Historical) formula in Excel:

  1. Basic Retrieval:

    =hf_Investments_NonCurrent("MSFT", 2022)

    Retrieves Microsoft’s non-current investments data for the year 2022.

  2. Specify Quarter:

    =hf_Investments_NonCurrent("^SPX", 2022, 2)

    Retrieves the non-current investments data for the S&P 500 index for Q2 of 2022.

  3. Use Trailing Twelve Months (TTM):

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

    Returns the trailing twelve-month value from Q3 2022.

  4. Reference Cells for Symbols and Years:

    =hf_Investments_NonCurrent(A2, B2)

    Where cell A2 contains the symbol (e.g., "BTCUSD:DEFAULT") and B2 contains the year (e.g., 2021).

  5. Use “Last Quarter” or “Last Year” Shortcuts:

    • Last Quarter:
      =hf_Investments_NonCurrent("MSFT", "lq")
    • Last Quarter minus one period:
      =hf_Investments_NonCurrent("MSFT", "lq-1")
    • Last Year:
      =hf_Investments_NonCurrent("MSFT", "ly")
    • Last 12 months:
      =hf_Investments_NonCurrent("MSFT", "lt")

? Pro Tip: Combine this formula with other historical fundamental functions like Revenue (Historical) or Gross Profit (Historical) to build comprehensive financial models.

Common Questions

1. Why am I getting "NA" as the output?

  • Make sure you have a valid MarketXLS subscription for historical fundamental data.
  • Verify that the ticker symbol is correct and not delisted or invalid.

2. Can I use different date formats for the Year parameter?

  • Yes, you can use special placeholders like "lq" (last quarter), "ly" (last year), or numeric values like 2021, 2022, etc. The function does not accept traditional date formats like "2024-03-15" for the Year parameter, but you can reference a cell that derives a valid string by using Excel functions, for example, TEXT().

3. Do I need to specify the Quarter if I use trailing 12 months (TTM)?

  • Quarter is optional. If you provide TTM, the function will compute a trailing 12-month figure for the specified timeframe. However, specifying both (e.g., Year = 2022, Quarter = 3, TTM = "TTM") refines which quarter is used as the endpoint.

4. How does performance get affected with multiple calls in a large spreadsheet?

  • Historical fundamental data calls can be somewhat resource-intensive. If you notice performance issues, consider reorganizing your spreadsheet to reduce redundant calls or use caching options available in MarketXLS.

?? Note: Always confirm the fundamental data source and last available update date through MarketXLS to ensure accurate, up-to-date figures.


By using the Investments NonCurrent (Historical) function alongside other MarketXLS formulas, you can efficiently analyze, track, and compare a company’s long-term investment trends for more informed decision-making.