Excel Formula Reference: Historical Market Data

The Add-in provides 3 different ways to get historical data into your Excel spreadsheet:

  • Single historical price in an Excel cell.
  • Multiple historical prices (time series) and dividends in Excel using formulas.
  • Multiple historical prices in Excel without formulas.
This page covers the first option, for the others please see: Historic Data Download Yahoo Finance

These formulas populate an Excel cell with a single price on a specific date and/or time.

We currently provide formulas for daily, hourly, 5 minute and 1 minute resolution. However, there are limits on how far back historic prices can be retrieved:

  • 1 Minute: 7 days back
  • 5 Minute: 60 days back
  • 1 Hour: 730 days back
  • Daily: No limit

Please note, all times must be specified as Coordinated Universal Time (UTC). For example New York is currently UTC -4 hours so all US market price requests must use this offset.

Excel Price Feed also incudes a formula to convert UTC to local time: EPF.Dates.UTCToLocal


Daily Historical Prices

All of these formulas require both a ticker and a historical date, for example:

=EPF.Yahoo.Historic.Close("TSLA", "1 May 2019")

will populate the Excel cell with the close price of Tesla stock on 1 May 2019.

Formula Description
EPF.Yahoo.Historic.AdjustedClose Adjusted Close price on specified date.
EPF.Yahoo.Historic.Close Close price on specified date.
EPF.Yahoo.Historic.High High price on specified date.
EPF.Yahoo.Historic.Low Low price on specified date.
EPF.Yahoo.Historic.Open Open price on specified date.
EPF.Yahoo.Historic.Volume Volume on specified date.


Hourly Historical Prices

All of these formulas require both a ticker and a historical date/time, for example:

=EPF.Yahoo.Historic.Open("AAPL", "1 May 2019 15:00")

will populate the Excel cell with the price of Apple stock at 15:00 UTC 1 May 2019 (which is actually 11:00 NY time on 1 May 2019).

Formula Description
EPF.Yahoo.Historic.Close.1Hour Close price of specified hourly bar.
EPF.Yahoo.Historic.High.1Hour High price of specified hourly bar.
EPF.Yahoo.Historic.Low.1Hour Low price of specified hourly bar.
EPF.Yahoo.Historic.Open.1Hour Open price of specified hourly bar.


5 Minute Historical Prices

All of these formulas require both a ticker and a historical date/time, for example:

=EPF.Yahoo.Historic.Open("MSFT", "1 May 2019 19:55")

will populate the Excel cell with the price of Microsoft stock at 19:55 UTC 1 May 2019 (which is actually 15:55 NY time i.e. 5 minutes before market close).

Formula Description
EPF.Yahoo.Historic.Close.5Minute Close price of specified 5 minute bar.
EPF.Yahoo.Historic.High.5Minute High price of specified 5 minute bar.
EPF.Yahoo.Historic.Low.5Minute Low price of specified 5 minute bar.
EPF.Yahoo.Historic.Open.5Minute Open price of specified 5 minute bar.


1 Minute Historical Prices

All of these formulas require both a ticker and a historical date/time, for example:

=EPF.Yahoo.Historic.Open("XOM", "1 May 2019 13:31")

will populate the Excel cell with the price of Exxon Mobil stock at 13:31 UTC 1 May 2019 (which is actually 09:31 NY time i.e. 1 minute after market open).

Formula Description
EPF.Yahoo.Historic.Close.1Minute Close price of specified 1 minute bar.
EPF.Yahoo.Historic.High.1Minute High price of specified 1 minute bar.
EPF.Yahoo.Historic.Low.1Minute Low price of specified 1 minute bar.
EPF.Yahoo.Historic.Open.1Minute Open price of specified 1 minute bar.