Investments Current (Historical) Formula in Excel

Welcome to this comprehensive guide on using the Investments Current (Historical) formula in Excel with MarketXLS. This function is designed to help investors and analysts quickly retrieve the value of a company's current investments (marketable securities) for a specified year and quarter, or on a trailing twelve-month (TTM) basis. Read on to learn how to integrate this powerful MarketXLS function into your Excel workflow.

Understanding Investments Current (Historical)

Investments Current (Historical) refers to short-term or easily liquidated investments, often showing up on a company's balance sheet as marketable securities or short-term investments. Tracking this metric can help you:

• Understand a company's liquidity position.
• Evaluate how the company manages its short-term excess funds.
• Compare multiple periods’ short-term financial health.

You might use this function when analyzing a company's historical financial statements, assessing short-term investments changes over time, or conducting comparisons between different reporting periods.

Syntax and Parameters

Below is the official syntax for the Investments Current (Historical) formula:

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

?? Note: This function requires a MarketXLS subscription with historical fundamental data access.

Return Value

• Returns the value of the company's current investments for the requested period.
• If the symbol is invalid or data is unavailable, it returns "NA".

Date Input Formats

Though this function typically requires a year rather than a full date, you can still reference cells or convert dates to years when passing the year parameter:

1. Cell references:

   =hf_Investments_Current(A1, 2022)

   (Where A1 might contain a ticker symbol like "MSFT".)

2. Using a full date string (if you decide to convert it to a year):

   =hf_Investments_Current("MSFT", TEXT("2024-03-15","yyyy"))

3. Converting a referenced date to a year string:

   =hf_Investments_Current("MSFT", TEXT(A1,"yyyy"))

   (Assuming A1 contains a valid Excel date.)

Symbol Formats

You can input different types of symbols for greater flexibility:

• Regular stocks:

  =hf_Investments_Current("MSFT", "2022")

• Indices:

  =hf_Investments_Current("^SPX", "lq")

• Options:

  =hf_Investments_Current("@MSFT 110122C00020000", 2022, 3)

• Cryptocurrencies:

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

Examples and Usage

Below are some practical usage examples to showcase how you can make the most of this function:

1. Retrieve Investments Current by Year

   =hf_Investments_Current("MSFT", 2022)

   Returns MSFT’s current investments for the year 2022.

2. Specify Quarter and Year

   =hf_Investments_Current("MSFT", 2022, 2)

   Returns the Q2 2022 current investments.

3. Access Trailing Twelve Months (TTM)

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

   Returns the trailing twelve months figure from Q3 of 2022.

4. Use Last Quarter or Last Year Shorthand

   =hf_Investments_Current("MSFT", "lq")
   =hf_Investments_Current("MSFT", "ly")

   Returns the respective last quarter or last year figure.

? Pro Tip: Combine this function with Excel’s built-in features like referencing cells for the Symbol or year to build dynamic models that automatically update as new data or symbols are entered.

Common Questions

1. Why do I get "NA" instead of a numeric value?

This typically means:

• The symbol may be invalid or incorrectly typed.
• Data for the symbol and requested period may not be available.
• Your MarketXLS subscription may not include historical fundamentals access.

2. Is there a limit to how many calls I can make?

Performance usually depends on your MarketXLS subscription and the volume of data you request. MarketXLS caches data to improve performance, but making a large number of queries in quick succession can still slow performance.

3. Can I use this function for multiple securities in bulk?

Yes. By referencing different cells containing various symbols (e.g., in a column), you can drag down or copy the formula to return current investments data for many securities simultaneously.

4. Does the function account for different fiscal year definitions?

Yes. The backend attempts to handle company-specific fiscal policies. However, results can differ if a company’s fiscal year is not aligned with the calendar year.

By incorporating the Investments Current (Historical) formula into your workflow, you can quickly analyze a company’s short-term investment strategy and liquidity over various periods—directly within Excel.

Use this guide to get started, and don’t hesitate to explore further MarketXLS historical fundamental formulas for deeper financial insights.