Last Updated (Historical) Formula in Excel

Understanding Last Updated (Historical)

The Last Updated (Historical) formula in Excel with MarketXLS retrieves the most recent recorded date of historical fundamental data for a given symbol, year, quarter, and optional trailing twelve months (TTM). Use this function to:

• Track when fundamental data for a stock was last refreshed.
• Combine multiple fundamental metrics, ensuring their timelines align accurately.
• Enhance decision-making with the latest available data points for historical fundamentals.

? Pro Tip: The function helps confirm data freshness when analyzing financial trends across various time periods, enabling more reliable investment decisions.

Syntax and Parameters

Syntax

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

Parameters

?? Note: If the function cannot fetch data or if the symbol is invalid, it returns "NA".

Return Value

This function returns the date (as text) on which the fundamental data was last updated. If data is missing or unavailable for the specified inputs, "NA" is returned.

Examples and Usage

1. Basic Examples:

   • Retrieve the last updated date for Microsoft in 2022 (Default quarter = 1):

     =hf_Last_Updated("MSFT", 2022)

   • Retrieve the last updated date for Microsoft in 2022, Q2:

     =hf_Last_Updated("MSFT", 2022, 2)

   • Retrieve the last updated date for Microsoft in 2022, Q3, trailing twelve months:

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

2. Special Shorthand Examples:

   • Retrieve the last updated date for the last quarter:

     =hf_Last_Updated("MSFT", "lq")

   • Retrieve the last updated date for the previous last quarter:

     =hf_Last_Updated("MSFT", "lq-1")

   • Retrieve the last updated date for the last year:

     =hf_Last_Updated("MSFT", "ly")

   • Retrieve the last updated date for the previous last year:

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

   • Retrieve the last updated date for the last 12 months:

     =hf_Last_Updated("MSFT", "lt")

   • Retrieve the last updated date for the previous last 12 months:

     =hf_Last_Updated("MSFT", "lt-1")

3. Using Date Formats in Year (if applicable):

   • Referencing a cell A1 that contains a year:

     =hf_Last_Updated("MSFT", A1)

   • Passing a date as text (though typically you would pass a year, this is for demonstration):

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

   • Using Excel’s TEXT function:

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

? Pro Tip: Combine hf_Last_Updated with other Historical formulas (like Revenue (Historical)) to ensure consistency in date references when analyzing fundamental data for the same periods.

Common Questions

1. What if I don’t have a historical data subscription?

   • The function will return "NA" if your subscription plan does not include historical fundamentals.

2. Does this function support international symbols or crypto?

   • Yes. Use supported formats like "BTCUSD:DEFAULT" for crypto and standard ticker formats for international equities.

3. Are there limits on the number of calls per day?

   • Depending on your subscription, there may be limits to how many live or historical calls you can make daily.

4. Why do I get "NA" for known symbols?

   • The most common reasons are invalid licensing, subscription mismatch, or the particular fundamental data not being available for that time period.

5. How can I improve performance if I’m calling this function for many symbols at once?

   • Try using Excel’s calculation options to manage when formulas recalculate, and avoid large batch requests in rapid succession.

?? Note: Always ensure your MarketXLS add-in is up to date to avoid data compatibility issues.