Date Key (Historical) Formula in Excel

Understanding Date Key (Historical)

The Date Key (Historical) formula, provided by MarketXLS, allows you to retrieve a unique identifier for a historical date key associated with a company’s financials. This powerful Excel function helps you:

• Quickly pinpoint the specific reporting or fiscal date for a given year and quarter.
• Reference trailing twelve months (TTM), last quarter (LQ), or last year (LY) data.
• Streamline fundamental data lookups for historical performance analysis.

Use this formula when researching historical financial information, creating backtests, or analyzing trends in a company’s fundamental data.

Syntax and Parameters

Use the following syntax in Excel:

=hf_Date_Key(symbol, year, [quarter], [TTM])

Return Value

• Returns the unique date key for the specified period or “NA” if invalid inputs or insufficient subscription levels are detected.

?? Note: The function checks for a valid MarketXLS license and historical fundamental data subscription. If these conditions are not met, it will return "NA."

Examples and Usage

Below are some practical examples of using the Date Key (Historical) formula in Excel:

1. Retrieve the unique date key for a specific year:

   =hf_Date_Key("MSFT", 2022)

   Returns the date key for Microsoft in 2022 (defaulting to quarter 1).

2. Include a specific quarter:

   =hf_Date_Key("MSFT", 2022, 2)

   Returns the date key for Microsoft in 2022 for quarter 2.

3. Use trailing twelve months:

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

   Returns the date key for Microsoft referencing trailing twelve months from Q3 2022.

4. Get data for last quarter or last year directly:

   =hf_Date_Key("MSFT","lq")      // Last quarter
   =hf_Date_Key("MSFT","ly")      // Last year
   =hf_Date_Key("MSFT","lt")      // Last 12 months

5. Subtracting one from last quarter or last year:

   =hf_Date_Key("MSFT","lq-1")    // Last quarter minus one
   =hf_Date_Key("MSFT","ly-1")    // Last year minus one
   =hf_Date_Key("MSFT","lt-1")    // Previous trailing 12 months

? Pro Tip: Combine this function with other MarketXLS historical fundamental formulas (e.g., revenue, cost of revenue) for a complete timeline analysis of a company’s financial performance.

Common Questions

Why am I getting “NA” as a result?

• Make sure your subscription includes historical fundamental data.
• Verify that you have entered a valid symbol and year format.
• Check that you have not exceeded your MarketXLS license limits.

What happens if I omit the quarter parameter?

• The function defaults to quarter 1 if none is specified.

Can I use cell references for parameters?

• Yes. For example:

  =hf_Date_Key(A1, B1, C1, D1)

  Where each cell contains the respective symbol, year, quarter, or TTM indicator.

Are there any performance considerations?

• Frequent calls for many symbols may slow down Excel. It’s best to reference cells rather than repeatedly typing the function, and ensure you have a stable internet connection.

Which date formats can I use?

• Directly as text: "2022" or "ly" or "lq".
• Using Excel’s date functions:

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

  (Though typically, the function expects a year or relative code, not a full date.)

Use the Date Key (Historical) formula with other MarketXLS functions for powerful, dynamic analyses of historical financial data, ensuring you have the right time-based information at your fingertips.