Yahoo Finance Array Formulas

The Add-in provides several formulas which are designed to be used as "Array Formulas".


What is an array formula?

An array formula is a formula which affects more than its "own" cell. They differ from standard formulas in two ways:

  • They require you to highlight the cells which the formula will populate.
  • They require a special key combination (CTRL-SHIFT-ENTER) to be used after entering the formula.

They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you always need to press Ctrl+Shift+Enter to enter them.

Important Note: If you have a current version of Office 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula.

The Excel Price Feed Yahoo Finance Array Formulas

The Excel Price Feed Add-in currently provides 3 array formulas:

EPF.Yahoo.HistoricDailyLookback

This formula populates your spreadsheet with historic daily data for a number of days back from today.

The parameters required are:

Parameter Description
Ticker The ticker for the instrument, eg "APPL" or "SPY"
Number of Data Points The number of days data the formula will retrieve (back from today).
Order The order that the data will be displayed, either "DESC" for Descending (latest first) or "ASC" for Ascending (oldest first).
Include Headers Whether to display column headers for the data, either TRUE to show or FALSE to hide.

Example
=EPF.Yahoo.HistoricDailyLookback("AAPL",10,"DESC",TRUE)
"AAPL" for Apple Stock; 10 for the number of days back; "DESC" as we want the data descending, so latest first; TRUE as we want to display the column headings

EPF.Yahoo.HistoricDateRange

This formula populates your spreadsheet with historic data for a specified resolution and date range.

The parameters required are:

Parameter Description
Ticker The ticker for the instrument, eg "APPL" or "SPY"
Resolution This can be one of: "1 Minute","2 Minute","5 Minute","15 Minute","30 Minute","1 Hour","Daily","Weekly","Monthly"
Start Date The start date of the data the formula will retrieve.
End Date The end date of the data the formula will retrieve.

Example
=EPF.Yahoo.HistoricDateRange("AAPL","1 Hour","18 Nov 2019 14:30","19 Nov 2019 23:00")
"AAPL" for Apple Stock; "1 Hour" for the resolution; "18 Nov 2019 14:30" is the start date; "19 Nov 2019 23:00" is the end date

EPF.Yahoo.HistoricDailyWeeklyMonthly

This formula populates your spreadsheet with historic daily/weekly/monthly data for a specified date range.

The parameters required are:

Parameter Description
Ticker The ticker for the instrument, eg "APPL" or "SPY"
Start Date The start date of the data the formula will retrieve.
End Date The end date of the data the formula will retrieve.
Order The order that the data will be displayed, either "DESC" for Descending (latest first) or "ASC" for Ascending (oldest first).
Include Headers Whether to display column headers for the data, either TRUE to show or FALSE to hide.

Example
=EPF.Yahoo.HistoricDailyWeeklyMonthly("AAPL","1 Oct 2019","15 Nov 2019", "DESC", TRUE)
"AAPL" for Apple Stock; "1 Oct 2019" is the start date; "15 Nov 2019" is the end date; "DESC" as we want the data descending, so latest first; TRUE as we want to display the column headings


Walkthrough: Using the historic daily lookback array formula

All of the Array Formulas are used in a similar way, this walkthrough will show how to use the EPF.Yahoo.HistoricDailyLookback formula.

Important Note: If you have a current version of Office 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. There is no need for the ""Ctrl-Shift-Enter" key combination.
1. Highlight the output range in your spreadsheet

Select the range of the cells where you would like the results of the formula to be displayed. The formula returns 6 columns: Date, Open, High, Low, Close, Volume (OHLC-V).

For this example we will request the previous 10 days OHLC-V daily data for Apple stock. We want to display the column headings, therefore we need to highlight a range of 11 rows by 6 columns as shown below:

Excel array formula range selection

2. Enter the array formula

We will now enter the array formula into cell A1, as this is the top left corner of our selected range.

The formula we enter is: =EPF.Yahoo.HistoricDailyLookback("AAPL",10,"DESC",TRUE)

"AAPL" for Apple Stock; 10 for the number of days back; "DESC" as we want the data descending, so latest first; TRUE as we want to display the column headings

3. Apply the array formula to the highlighted range

Once we have entered the formula we press "Ctrl-Shift-Enter" (all 3 keys at the same time) to action the formula and retrieve the data.

The sheet is then populated with the data as shown below:

Excel array formula OHLC-V for Apple stock

4. Refreshing the data

The real power of this array formula is that it will always retrieve the latest data when we refresh the spreadsheet. So, the next trading day I can refresh the sheet and the oldest day will drop off to be replaced by the latest day, the sheet will always contain the last 10 trading days worth of data!