Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Accumulated Other Comprehensive Income (Historical) Formula in Excel

Accumulated Other Comprehensive Income (Historical) is a powerful Excel formula provided by MarketXLS. It returns the total unrealized gains and losses on investments, pension plans, and hedging transactions that are netted below retained earnings in the equity section of a company’s balance sheet. By leveraging this formula, you can quickly access and analyze a company’s historical data for sound investment decisions.

Understanding Accumulated Other Comprehensive Income (Historical)

  • Purpose and Use Cases
    Accumulated Other Comprehensive Income (Historical) helps investors and analysts see changes in equity that are not reported directly in net income. This can include fluctuations in the value of available-for-sale securities, cash flow hedges, foreign currency translations, and more.

  • Key Benefits

    • Evaluate unrealized gains or losses that affect shareholder equity.
    • Gain insights into long-term trends in comprehensive income.
    • Make more informed decisions by identifying hidden strengths or risks in a company’s financials.
  • When to Use
    Use this formula when you need to examine a company’s historical balance sheet components, particularly for unrealized gains and losses impacting equity over multiple quarters or years.

Syntax and Parameters

=hf_Accumulated_Other_Comprehensive_Income(Symbol, year, [quarter], [TTM])
Parameter Description Required Example
Symbol The security identifier. Examples include standard tickers like "MSFT", indices like "^SPX", options like "@MSFT 110122C00020000", or crypto like "BTCUSD:DEFAULT". Yes "MSFT"
year The fiscal year (e.g., 2022) or a special reference: "lq" (last quarter), "ly" (last year), or "lt" (last 12 months). You can also use formats like "lq-1", "ly-1", "lt-1". Yes 2022 or "lq"
quarter A calendar quarter (1, 2, 3, or 4). If omitted, the formula returns annual data. No 2
TTM Optional parameter set to "TTM" for trailing twelve months data from the specified quarter. No "TTM"

?? Note: This function returns the accumulated other comprehensive income for a specified historical period. If data is not available or you do not have the required MarketXLS subscription, it may return "NA".

Return Value

• Returns a numeric value representing the accumulated other comprehensive income (in the same currency as the underlying financial statements).
• Returns "NA" if the data is not available, the symbol is invalid, or the subscription license does not include historical fundamentals.

Date Inputs

You can supply dates in multiple ways:
• By cell reference:

=hf_Accumulated_Other_Comprehensive_Income(A1, 2022)

• By direct date input:

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

• By using an Excel date function:

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

Examples and Usage

Below are some practical examples to illustrate how you might use this formula in your Excel spreadsheets:

  1. Annual Data for a Specific Year

    =hf_Accumulated_Other_Comprehensive_Income("MSFT", 2022)

    Retrieves the accumulated other comprehensive income for Microsoft in the year 2022.

  2. Quarterly Data

    =hf_Accumulated_Other_Comprehensive_Income("MSFT", 2022, 2)

    Returns the value for the second quarter of 2022.

  3. Trailing Twelve Months (TTM)

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

    Provides TTM data ending in the third quarter of 2022.

  4. Last Quarter/Year Lookups

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

? Pro Tip: Combine this formula with other MarketXLS historical fundamentals, such as hf_Revenue or hf_Gross_Profit, to build comprehensive financial statements directly in Excel.

Common Questions

  1. Why am I getting “NA”?

    • Ensure you have the correct subscription for historical fundamentals.
    • Verify the ticker symbol is valid.
    • Check if the year/quarter is correctly formatted (e.g., 2022, "lq", "ly-1", etc.).
  2. How can I improve performance with large datasets?

    • Limit the number of calls by consolidating data retrieval.
    • Use Excel tables and references efficiently.
    • Use MarketXLS caching features when available.
  3. Which currency is the value reported in?

    • The formula returns data in the currency used by the company’s primary financial statements.
  4. Does the function update automatically?

    • MarketXLS formulas can refresh automatically based on your Excel settings. You can also manually refresh when needed.

By incorporating the Accumulated Other Comprehensive Income (Historical) formula into your Excel workflows, you gain a more complete financial picture, enabling better analysis and decision-making.

?? Note: This formula requires a valid MarketXLS license with access to historical fundamental data. If you receive a subscription-related error, please confirm your plan has the necessary data permissions or contact MarketXLS support.