Historical Fundamental Functions

Discussion in 'Historical Data' started by marketxls, Apr 10, 2017.

Historical Fundamental Functions

  1. marketxls

    marketxls Administrator
    Staff Member

    Dec 21, 2015
    Likes Received:
    Historical fundamental functions are available in Pro Version. This category of function can be accessed through Excel’s in built function menu as shown below:


    All functions in this category start from a prefix “hf_” hf stands for “Historical Fundamentals”. These functions can be used to get historical values (quarterly, yearly, TTM) of fundamentals from a company’s financial statements. The values returned from these functions will exactly match the values that you would get from SEC filings.

    You can just start typing =hf_ and Excel will also give a list of all functions available like shown below:


    All these functions have a consistent format of usage which is explained with examples below:

    Revenue of MSFT Q3 2013: =hf_revenue("MSFT","2013","3")

    Revenue of MSFT Year 2015 : =hf_revenue("MSFT","2015")

    TTM Revenue of MSFT Year 2015 Q3 : =hf_revenue("MSFT","2015","3","TTM")
    Following are the arguments for these functions…

    1. Symbol – Any US stock symbol
    2. Year – Year as string. Enclose the year number in Double quotes if entering in the formula itself. You can also refer to a cell that carries the year numeric value.
    3. Optional – Quarter Number – Enclose the Quarter number in Double quotes if entering in the formula itself. You can also refer to a cell that carries the year numeric value.
    4. Optional TTM – Type “TTM” within double quotes
    Important Note : All Quarterly values refer to Calendar Quarters and not the fiscal quarters. So, when you ask to get data for Q1 it means data for January to March End. Now this time period could actually be Quarter 3 in some companies whose Fiscal year is from April to March.

    These functions are particularly useful when you want to analyse the fundamental strength of a company over time. Since, these functions are in Excel you can use these in many different ways like for comparing multiple companies for multiple metrics over time, looking at trends of revenue, margins and other key indicators.

    A total of 100 indicators are available as listed below:

    Income Statement

    Revenues (USD)
    Cost of Revenue
    Gross Profit
    Research and Development Expense
    Selling, General and Administrative Expense
    Earning Before Interest & Taxes (EBIT)
    Earning Before Interest & Taxes (USD)
    Interest Expense
    Income Tax Expense
    Net Income
    Preferred Dividends Income Statement Impact
    Net Income Common Stock
    Net Income Common Stock (USD)
    Net Income from Discontinued Operations
    Earnings per Basic Share
    Earnings per Basic Share (USD)
    Earnings per Diluted Share
    Weighted Average Shares
    Weighted Average Shares Diluted
    Dividends per Basic Common Share
    Cash Flow Statement

    Net Cash Flow from Operations
    Depreciation, Amortization & Accretion
    Net Cash Flow from Investing
    Capital Expenditure
    Net Cash Flow from Financing
    Issuance (Repayment) of Debt Securities
    Issuance (Purchase) of Equity Shares
    Payment of Dividends & Other Cash Distributions
    Effect of Exchange Rate Changes on Cash
    Net Cash Flow / Change in Cash & Cash Equivalents
    Balance Sheet

    Total Assets
    Current Assets
    Assets Non-Current
    Cash and Equivalents
    Cash and Equivalents (USD)
    Trade and Non-Trade Receivables
    Goodwill and Intangible Assets
    Total Liabilities
    Current Liabilities
    Liabilities Non-Current
    Total Debt
    Total Debt (USD)
    Trade and Non-Trade Payables
    Shareholders Equity
    Shareholders Equity (USD)
    Accumulated Retained Earnings (Deficit)
    Accumulated Other Comprehensive Income
    Metrics & Ratios

    Asset Turnover
    Average Assets
    Book Value per Share
    Current Ratio
    Debt to Equity Ratio
    Dividend Yield
    Earnings Before Interest, Taxes & Depreciation Amortization
    Earnings Before Interest, Taxes & Depreciation Amortization (USD)
    EBITDA Margin
    Earnings before Tax
    Average Equity
    Enterprise Value
    Enterprise Value over EBIT
    Enterprise Value over EBITDA
    Free Cash Flow
    Free Cash Flow per Share
    Foreign Currency to USD Exchange Rate
    Gross Margin
    Invested Capital
    Invested Capital Average
    Market Capitalization
    Profit Margin
    Price Earnings Damodaran Method
    Price to Earnings Ratio
    Price to Sales Ratio
    Price Sales Damodaran Method
    Price to Book Value
    Return on Invested Capital
    Sales per Share
    Payout Ratio
    Return on Average Assets
    Return on Average Equity
    Return on Sales
    Tangible Asset Value
    Tangible Asset Book Value per Share
    Working Capital
    #1 marketxls, Apr 10, 2017
    Last edited: Sep 24, 2017
  2. Jay Zylstra

    Jay Zylstra New Member

    Sep 22, 2017
    Likes Received:
    The availability of these historical fundamental functions will be very useful. However, as I've begun to use them, I've noticed inconsistent results, although I admit that I may be using the functions incorrectly. For example, if I look up the Enterprise Value over EBIT of Microsoft from 2013 Q3 using a single function, I get the result of "7":
    =hf_Enterprise_Value_over_EBIT("MSFT", 2013, 3)

    But if I calculate the same value by separately getting the Enterprise Value and EBIT, I get a different result of "33.68":
    =hf_Enterprise_Value("MSFT", 2013, 3) / hf_Earning_Before_Interest_and_Taxes_EBIT("MSFT", 2013, 3)

    Why the inconsistency?
  3. marketxls

    marketxls Administrator
    Staff Member

    Dec 21, 2015
    Likes Received:
    We have updated new MarketXLS fundamental functions. There should be no inconsistency.

Share This Page