Historical Fundamental Functions

You are here:
< Back

We have added new Historical fundamental functions that are available in Pro Version. 

Full list of data points is shown below. The repective functions can be accesed by typing =hf_

 

Projected fiscal year-end date
Number of months since last reporting period. If this field is other than 12 months, the company has likely changed their fiscal year end.
Loans
Investment securities
Lease financing income
Other interest income
Federal funds sold/purchased
Interest bearing deposits
Loans held for resale
Trading account securities
Time deposits placed
Other money market investments
Total money market investments
Total interest income
Deposits
Short term debt
Float term debt
Federal funds purchased/securities sold
Capitalized lease obligations
Other interest expense
Total interest expense
Net interest income (expense)
Provision for loan loss
Trust fees by commissions
Service charge on deposit accounts
Other service charges
Security transactions
Premiums earned
Net realized capital gains
Investment banking profit
Other non-interest income
Total non-interest income
Salaries and employee benefits
Net occupancy expense
Promotions and advertising
Property/liability insurance claims
Policy acquisition costs
Amortization def. policy acquisition cost
Current and future benefits
Other non-interest expense
Total non-interest expense
Premium tax (credit)
Minority interest
Income taxes
Income, acquired in process R&D
Income, restructuring and M&A
Other special charges
Special income/charges
Net income from continuing operations
Net income from discontinued operations
Net income from total operations
Extraordinary income/losses
Income from cumulative effect of account chg
Income from tax loss carry forward
Other gains (losses)
Total net income
Normalized income
Net income available for common
Preferred dividends
Basic EPS from continuing operations
Basic EPS from discontinued operations
Basic EPS from total operations
Basic EPS from extraordinary income
Basic EPS from cumulative effect of accounting chg
Basic EPS from tax loss carry forward
Basic EPS from other gains (losses)
Basic EPS, total
Basic normalized net income/share
Diluted EPS from continuing operations
Diluted EPS from discontinued operations
Diluted EPS from total operations
Diluted EPS from extraordinary income
Diluted EPS from tax loss carry forward
Diluted EPS from other gains (losses)
Diluted EPS, total
Diluted normalized net income/share
Dividends paid per share
Revenues, year to date
Cash and due from banks
Restricted cash
Federal funds sold/securities purchased
Interest bearing deposits at other banks
Investment securities, net
Loans
Unearned premiums
Allowance for loans and lease losses
Net loans
Premises and equipment
Due from customers’ acceptance
Trading account securities
Other receivables
Accrued interest
Deferred acquisition cost
Accrued investment income
Separate account business
Time deposits placed
Intangible assets
Other assets
Total assets
Non-interest bearing deposits
Interest bearing deposits
Short term debt
Other liabilities
Bankers’ acceptance outstanding
Federal funds purchased/securities sold
Accrued taxes
Accrued interest payables
Other payables
Capital lease obligations
Claims and claim expense
Future policy benefits
Unearned premiums
Policy holder funds
Participating policy holder equity
Separate accounts business
Minority interest
Float term debt
Preferred stock equity
Common stock equity
Common par
Additional paid in capital
Cumulative translation adjustment
Retained earnings
Treasury stock
Other equity adjustments
Foreign currency adjustments
Net unrealized loss/gain on investments
Net unrealized loss/gain on foreign currencies
Net other unearned losses/gains
Total equity
Total liabilities
Shares outstanding common class only
Preferred shares
Total ordinary shares
Total common shares outstanding
Treasury shares
Basic weighted shares outstanding
Diluted weighted share outstanding
Number of employees
Number of part-time employees
Net income (earnings)
Provision for loan losses
Depreciation and amortization
Deferred income taxes
Change in assets (receivables)
Change in liabilities (payables)
Investment securities gain
Net policy acquisition costs
Realized investment gains
Net premiums receivables
Change in income taxes
Other non-cash items
Net cash from operating activities
Proceeds from sale/mat. inv.
Purchase of investment securities
Net increase federal funds sold
Purchase of property & equipment
Acquisitions
Other investing changes, net
Net cash from investing activities
Net change in deposits
Cash dividends paid
Repayment of Float term debt
Change of short term debt
Issuance of Float term debt
Issuance of preferred stock
Issuance of common stock
Purchase of treasury stock
Other financing activities
Net cash from financing activities
Effect of exchange rate changes
Net change in cash & equivalents
Cash at beginning of period
Cash at end of period
Total risk-based capital ratio
Auditor’s name
Auditor’s report
Operating revenue (revenue/sales)
Total revenues
Adjustments to revenue
Cost of sales
Cost of sales with depreciation
Gross margin
Gross operating profit
Research & development expense
Selling, gen & administrative expense
Advertising
Operating income
Operating income before depreciation (EBITDA)
Depreciation
Depreciation unreconciled
Amortization
Amortization of intangibles
Operating income after depreciation
Interest income
Earnings from equity interest
Other income, net
Income, acquired in process R&D
Income, restructuring and M&A
Other special charges
Special income/charges
Total Income available for interest expense (EBIT)
Interest expense
Pre-tax Income (EBT)
Minority interest
Preferred securities of subsidiary trust
Income before tax
Income from cumulative effect of accounting chg
Excise taxes
Diluted EPS from cumulative effect of accounting chg
Revenues, year-to-date
Income from total operations, year-to-date
Diluted EPS from total operations, year-to-date
Dividends paid per share, year-to-date
Cash and equivalents
Marketable securities
Accounts receivable
Loans receivable
Other receivable
Receivables
Raw materials
Work in progress
Purchased components
Finished goods
Other inventories
Inventories adjustments & allowances
Inventories
Prepaid expenses
Current deferred income taxes
Other current assets
Total current assets
Land & improvements
Building & improvements
Machinery, furniture & equipment
Construction in progress
Other fixed assets
Total fixed assets
Gross fixed assets (plant, property & equipment)
Accumulated depreciation & depletion
Net fixed assets (net PP&E)
Intangibles
Cost in excess
Non-current deferred income taxes
Other non-current assets
Total non-current assets
Total assets
Inventory valuation method
Accounts payable
Notes payable
Accrued expenses
Accrued liabilities
Deferred revenues
Current deferred income taxes
Other current liabilities
Total current liabilities
Capital lease obligations
Other non-current liabilities
Preferred securities of subsidiary trust
Preferred equity outside stock equity
Total non-current liabilities
Total liabilities
Preferred stock equity
Common stock equity
Common par
Additional paid in capital
Treasury stock
Total capitalization
Total equity
Total liabilities & stock equity
Cash flow
Working capital
Free cash flow
Invested capital
Shares outstanding common class only
Preferred shares
Total common shares outstanding
Treasury shares
Basic weighted shares outstanding
Diluted weighted shares outstanding
Number of employees
Number of part-time employees
Net income (loss)
Depreciation
Amortization
Amortization of intangibles
Deferred income taxes
Operating (gains) losses
Extraordinary (gains) losses
(Increase) Decrease in receivables
(Increase) Decrease in inventories
(Increase) Decrease in prepaid expenses
(Increase) Decrease in other current assets
(Increase) Decrease in payables
(Increase) Decrease in other current liabilities
(Increase) Decrease in other working capital
Other non-cash items
Net cash from continuing operations
Net cash from discontinued operations
Sale of property, plant, equipment
Sale of Float term investments
Sale of short term investments
Purchase of property, plant, equipment
Acquisitions
Purchase of Float term investments
Purchase of short term investments
Other investing changes net
Cash from disc. investing activities
Issuance of debt
Issuance of capital stock
Repayment of debt
Repurchase of capital stock
Payment of cash dividends
Other financing charges, net
Cash from disc. financing activities
Effect of exchange rate changes
Net change in cash & cash equivalents
Cash at beginning of period
Cash at end of period
Foreign sales
Domestic sales
Auditor’s name (aud. name & aud. op.)
Auditor’s Report (aud. name & aud. op.)
Close price/earnings ratio
High price/earnings ratio
Low price/earnings ratio
Gross profit margin (profit margin after CGS)
Pre-tax profit margin
Post-tax profit margin
Net profit margin (PM from total operations)
Interest coverage from continuing operations
Interest as a percentage of invested capital
Effective tax rate
Income per employee
Normalized close price/earnings ratio
Normalized high price/earnings ratio
Normalized low price/earnings ratio
Normalized net profit margin
Normalized return on stock equity
Normalized return on assets
Normalized return on invested capital
Normalized income per employee
Quick ratio
Current ratio
Payout ratio
Total debt to equity ratio
Float-term debt to total capital
Leverage ratio
Asset turnover
Cash as a percentage of revenue
Receivables as a percentage of revenue
SG&A expense as percentage of revenue
R&D Expense as percentage of revenue
Revenue per dollar, cash
Revenue per dollar, plant (net)
Revenue per dollar, common equity
Revenue per dollar, invested capital
Receivable turnover
Inventory turnover
Sales per dollar, receivables
Sales per dollar, inventory
Revenue to assets
Number of days cost of goods sold in inventory
Current assets per share
Total assets per share
Intangibles as percentage, book value
Inventory as percentage, revenue
Float term debt per share
Current liabilities per share
Cash per share
Float-term debt to equity ratio
Float term debt as percentage of invested capital
Float term debt as percentage of total liabilities
Total liabilities as a percentage of total assets
Working capital as a percentage of equity
Revenue per share
Book value per share
Tangible book value per share
Price to revenue ratio
Price to equity ratio (price to book)
Price to tangible book ratio
Working capital as percentage of price
Working capital per share
Cash flow per share
Free cash flow per share
Return on stock equity (return on equity)
Return on invested capital
Return on assets
Price/cash flow ratio
Price/free cash flow ratio
Sales per employee
Sales indicator
Earnings indicator
Earnings per share indicator
Price indicator
Price book indicator
Price sales indicator
Price cash indicator
Price free cash indicator
Debt equity indicator
Currency rate indicator
Gross profit indicator
Pre tax profit indicator
Post tax profit indicator
Net profit indicator
Return equity indicator
Level rate indicator
Asset turn over indicator
Research & development expense as percentage of revenue

 

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.

If you just want to get the last quarter value then simply =hf_revenue(“MSFT”,”lq”)

lq = last quarter.

If you just want to get the last quarter value then simply =hf_revenue(“MSFT”,”ly”)

ly = last quarter.

If you want to get the quarter before the last quarter then use =hf_revenue(“MSFT”,”lq-1″)

If you want to get the quarter before the last year then use =hf_revenue(“MSFT”,”ly-1″)

So, instead of using the actual year and quarter value, you should be able to use “lq-x”, where x is the number that represents how many quaters you want to go back from the last quarter. The same works exactly the same way for “ly-x”.

Here is a template on how to use Historical Fundamentals.

Important Note: All functions that return a ratio, like ROE, ROA, Margins etc are reported for trailing 12 months.

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

Name
Revenues
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

Name
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

Name
Total Assets
Current Assets
Assets Non-Current
Cash and Equivalents
Cash and Equivalents (USD)
Trade and Non-Trade Receivables
Goodwill and Intangible Assets
Inventory
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

Name
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