- Introduction
- Download
- Installation & Activation
- Troubleshooting
- Data Refresh/Updates
- Software Updates
- Manage Your Subscription
- Example Spreadsheets

- Introduction
- Simple Moving Average (SMA)
- Exponential Moving Average (EMA)
- Volume Weighted Average Price (VWAP)
- Average True Range (ATR)
- Bollinger Bands
- Aroon

- Account Setup
- Formula Reference
- VBA Functions Reference
- Historical Price Data Download To Excel
- Historical Price Data Download To File
- Historical Client Sentiment Data Download
- Watchlists
- Live Positions, Transactions & Activity History

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

Historical data available includes prices and dividends.

This page describes these array formulas provided by the Add-in followed by a Walkthrough of how to use them:

- What is an array formula?
- EPF.Yahoo.HistoricIntraday
- EPF.Yahoo.HistoricDailyLookback
- EPF.Yahoo.HistoricDailyLookbackClose
- EPF.Yahoo.HistoricDailyLookbackAdjustedClose
- EPF.Yahoo.HistoricDatePeriod
- EPF.Yahoo.HistoricDailyWeeklyMonthly
- EPF.Yahoo.DividendsDatePeriod
- Walkthrough: Using the historical daily lookback array formula

An array formula is a formula which affects (or populates with data) more than its "own" cell.

The Add-in provides these types of formulas so that you can use a single formula to download a time-series of historical data. These formulas are great for providing data for backtesting trading ideas or historical data analysis.

For example, here is a 50 day historical time series (open, high, low, close and volume) for Google stock using just one Excel formula:

`=EPF.Yahoo.HistoricDailyLookback("GOOGL",50,"DESC",1)`

Unless you are using Office 365 (see box below) 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 Add-in currently provides 7 dynamic array formulas:

This formula populates your spreadsheet with historical intraday data for a single day:

`=EPF.Yahoo.HistoricIntraday(ticker, resolution, date, includePrePost, order, includeHeaders)`

The input parameters required are:

Parameter | Optional | Description |
---|---|---|

Ticker | No | The ticker for the instrument, eg "APPL" or "SPY" |

Resolution | No | Must be an intraday resolution, so one of: 1 min, 2 min, 5 min, 15 min, 30 min or 1 hour |

Date | No | The date for the intraday data request |

IncludePrePost | Yes | Whether to include pre and post market prices in the returned dataset |

Order | Yes | The order that the data will be displayed, either "DESC" for Descending (latest first) or "ASC" for Ascending (oldest first). |

Include Headers | Yes | Whether to display column headers for the data, either 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date | The date/time of the price sample. |

Open | The open price of the period/bar. |

High | The high price of the period/bar. |

Low | The low price of the period/bar. |

Close | The close price of the period/bar. |

Volume | The volume of stock traded during the period/bar. |

**Example**

We would like to populate our spreadsheet with intraday 1 minute Nasdaq data for the current trading day. We insert the following formula into cell B1:

`=EPF.Yahoo.HistoricIntraday("^IXIC","1 Min",TODAY(),0,"DESC",1)`

Where `"^IXIC"`

is the Nasdaq ticker; `"1 Min""`

is for 1 minute resolution/bars; `TODAY()`

is the formula for todays date; `0`

as we don't want pre or post market data'; `"DESC"`

as we want the data descending, so latest first; `1`

as we want to display the column headings.

This will output the following to Excel:

Each time the spreadsheet is refreshed the latest 1 minute data bar is downloaded to the sheet.

This formula populates your spreadsheet with historic daily data for the specified number of days back from today:

`=EPF.Yahoo.HistoricDailyLookback(ticker, daysBack, order, includeHeaders)`

The input parameters required are:

Parameter | Description |
---|---|

Ticker | The ticker for the instrument, eg "APPL" or "SPY" |

Days Back | 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 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date | The date of the price sample. |

Open | The open price of the day. |

High | The high price of the day. |

Low | The low price of the day. |

Close | The close price of the day. |

Volume | The volume of stock traded for the day. |

**Example**

We would like to populate our spreadsheet with the last 5 trading days of historical data for Apple stock. We insert the following formula into cell A1:

`=EPF.Yahoo.HistoricDailyLookback("AAPL",5,"DESC",1)`

Where `"AAPL"`

is for Apple Stock; `5`

is for the number of days back; `"DESC"`

as we want the data descending, so latest first; `1`

as we want to display the column headings.

This will output the following to Excel:

This formula populates your spreadsheet with historic daily close prices for the specified number of days back from today:

`=EPF.Yahoo.HistoricDailyLookbackClose(ticker, daysBack, order , includeHeaders)`

The input parameters required are:

Parameter | Description |
---|---|

Ticker | The ticker for the instrument, eg "APPL" or "SPY" |

Days Back | 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 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date | The date of the price sample. |

Close | The close price of the day. |

**Example**

We would like to populate our spreadsheet with the last 5 trading days close prices for Intel stock. We insert the following formula into cell A1:

`=EPF.Yahoo.HistoricDailyLookbackClose("INTC",5,"DESC",1)`

Where `"INTC"`

is for Intel Stock; `5`

is for the number of days back; `"DESC"`

as we want the data descending, so latest first; `1`

as we want to display the column headings.

This will output the following to Excel:

This formula populates your spreadsheet with historic daily adjusted close prices for the specified number of days back from today:

`=EPF.Yahoo.HistoricDailyLookbackAdjustedClose(ticker, daysBack, order , includeHeaders)`

The input parameters required are:

Parameter | Description |
---|---|

Ticker | The ticker for the instrument, eg "APPL" or "SPY" |

Days Back | The number of days data the formula will retrieve (back from today). |

Order | |

Include Headers | Whether to display column headers for the data, either 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date | The date of the price sample. |

Adj. Close | The adjusted close price of the day. |

DEPRECATED: please use EPF.Yahoo.HistoricDatePeriod below as this formula allows you to specify a sort order and optional column headings.

This formula populates your spreadsheet with historical price data for a specified resolution and date period.

Please note: This formula can return not only dates but also times.

All times are Coordinated Universal Time (UTC).

To convert to local time you can use the EPF.Dates.UTCToLocal formula.

Some Examples, with no daylight saving adjustments:

All times are Coordinated Universal Time (UTC).

To convert to local time you can use the EPF.Dates.UTCToLocal formula.

Some Examples, with no daylight saving adjustments:

**US**: New York is UTC-4 therefore 13:00 UTC is 09:00 New York time.**Paris/Frankfurt**UTC+2 therefore 13:00 UTC is 15:00 Paris/Frankfurt time.**Hong Kong/Singapore**: UTC+8 therefore 08:00 UTC is 16:00 Hong Kong/Singapore time.**UK**: London is UTC+1 therefore 13:00 UTC is 14:00 London time.**Brazil**: Sao Paulo is UTC-3 therefore 13:00 UTC is 10:00 Sao Paulo time.

The input 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. |

Order | |

Include Headers | Whether to display column headers for the data, either 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date / Time | The date / time of the price sample. |

Open | The open price of the period. |

High | The high price of the period. |

Low | The low price of the period. |

Close | The close price of the period. |

Volume | The volume of stock traded during the period. |

**Example 1**

We would like to populate our spreadsheet with weekly historical data for Apple stock for January 2020. We insert the following formula into cell A1:

`=EPF.Yahoo.HistoricDatePeriod("AAPL","Weekly","1 Jan 2020","29 Jan 2020", "ASC", 0)`

Where `"AAPL"`

is for Apple Stock; `"Weekly"`

is for the resolution; `"1 Jan 2020"`

is the start date; `"29 Jan 2020"`

is the end date

This will output the following to Excel:

**Example 2**

You can also combine this formula with Excels built-in date functions to create a "rolling" period/window, so each time you refresh the sheet the period/window is rolled forward.

For example here, for Amazon daily data, we are using the EDATE and the TODAY() functions to dynamically specify the start and end dates. This provides a 12 month rolling window:

`=EPF.Yahoo.HistoricDatePeriod("AMZN","Daily", EDATE(TODAY(),-12),TODAY(), "ASC", 0)`

This will output the following to Excel:

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

The input 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 | |

Include Headers | Whether to display column headers for the data, either 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Symbol | The symbol/ticker of the stock. |

Date | The date of the price sample. |

Day Open | The daily open price. |

Day High | The daily high price. |

Day Low | The daily low price. |

Day Close | The daily close price. |

Day Adjusted Close | The daily adjusted close price. |

Day Volume | The daily volume of stock traded. |

Week Open | The weekly open price. |

Week High | The weekly high price. |

Week Low | The weekly low price. |

Week Close | The weekly close price. |

Week Adjusted Close | The weekly adjusted close price. |

Week Volume | The weekly volume of stock traded. |

Month Open | The monthly open price. |

Month High | The monthly high price. |

Month Low | The monthly low price. |

Month Close | The monthly close price. |

Month Adjusted Close | The monthly adjusted close price. |

Month Volume | The monthly volume of stock traded. |

**Example**

`=EPF.Yahoo.HistoricDailyWeeklyMonthly("AAPL","1 Oct 2019","15 Nov 2019", "DESC", 1)`

Where `"AAPL"`

is 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; `1`

as we want to display the column headings

This will output the following to Excel:

This formula populates your spreadsheet with historical dividend data for the specified date period:

`=EPF.Yahoo.DividendsDatePeriod(ticker, startDate, endDate, order, includeHeaders)`

The input parameters required are:

Parameter | Description |
---|---|

Ticker | The ticker for the instrument, eg "APPL" or "MSFT" |

Start Date | The start date of the dividend data the formula will retrieve. |

End Date | The end date of the dividend data the formula will retrieve. |

Order | The order that the data will be displayed, either "DESC" for Descending (latest dividends first) or "ASC" for Ascending (oldest dividends first). |

Include Headers | Whether to display column headers for the data, either 1 to show or 0 to hide. |

The output columns are:

Output Column | Description |
---|---|

Date | The dividend date. |

Dividend | The dividend amount. |

**Example**

We would like to populate our spreadsheet with the dividends paid by Apple since Jan 1 2019. We insert the following formula into cell A1:

`=EPF.Yahoo.DividendsDatePeriod("AAPL","1 Jan 2019",NOW(),"DESC",1)`

Where `"AAPL"`

is for Apple Stock; `"1 Jan 2019"`

is the start date; `NOW()`

is the end date, i.e. today; `"DESC"`

as we want the data descending, so latest first; `1`

as we want to display the column headings.

This will output the following to Excel:

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

formula (for non Office 365 users).

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.

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:

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",1)`

`"AAPL"`

for Apple Stock; `10`

for the number of days back; `"DESC"`

as we want the data descending, so latest first; `1`

as we want to display the column headings

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:

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!