Getting Started

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

General Formulas

• Black-Scholes Option Pricing
• Instrument Identifier Conversions
• Miscellaneous

Technical Analysis Toolkit

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

Federal Reserve Economic Data
(FRED)

• Overview
• Formula Reference

Yahoo Finance

• Formula Reference
• • All Formulas
  • Stocks
  • Stocks: Income Statement
  • Stocks: Balance Sheet
  • Stocks: Cash Flow
  • Stocks: Analysis
  • Stocks: ESG Risk Ratings
  • Options Chain
  • Stock Options
  • Mutual Funds
  • ETFs
  • FX Rates
  • Cryptocurrencies
  • Commodities
  • Historical Market Data
  • News
• Historical Array Formulas
• Historical Data Download
• VBA Functions Reference

IG Index / IG Markets

• 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

EODHistoricalData.com

• Overview & Setup
• Historical Price Data
• Stocks Fundamental Data
• Stocks Financial Analysis

Nasdaq Data Link

• Overview & Setup
• Formula Reference

Excel Formula Reference: Historical Market Data

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.