Historical Fundamental Functions

This category of function can be accessed through Excel’s inbuilt 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 Quarter 3 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 a 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

If you want to get the last quarter value then simply use:

If you just want to get last year value then simply use:

If you want to get the quarter before the last quarter then use

If you want to get the quarter before the last year then use

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 quarters 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.

These functions are particularly useful when you want to analyze the fundamental strength of a company over time. Since these functions are in Excel you can use these in many different ways like 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