Investors Exchange (IEX) is a stock exchange based in the United States. It was founded in 2012 and launched as a national securities exchange in September 2016. On October 24, 2017, IEX received regulatory approval from the SEC to list companies.
Why is it important to Financial Developers?
IEX offers an API (an application program interface) that provides a vast amount of financial information, taking the place of Yahoo finance API which is presenty very limited. You can see my (non subscription) Yahoo Finance Custom Function here:
IEX Cloud is a platform that makes financial data and services accessible to everyone through an API. The ability to pull down an amazing amount of financial data is just a few keystrokes away. For example, under the stock category below is a sample of API calls available.
Volume by Venue
In this demo I will demonstrate two of those functions: a stock market data function and a data usage function.
The user need only know the data they need and how to insert an Excel Function (a simple Process). The Function does all the parsing thus streamlining the process.
There is a catch, you must be efficient with the number of trips you make to the server (API calls) as they are limited. (Mine is an unpaid subscription). My custom excel functions will allow you to use Excel and the API to pull stock, Mutual Fund and ETF prices, they help you minimize server trips and easily monitor usage.
The base url for the API is:
I suggest you visit the introduction page that details the incredible amount of financial data available. I have only provided the bare minimum details for the IEX AP Interface on this page. Please study the nature and timing of the data provided to ensure it meets your needs.
I’ll start with my IEXQuote function:
My VBA Custom function will pull back over 40 different market data types. The Function has 3 arguments:
1) the instrument ‘ticker’ – in this case MMM or 3M Company a diverse manufacter.
2) The market Data type – i.e. latestPrice , previousClose, primaryExchange, see the complete list below. – extracted from IEX API developer introduction
3) the Secret token that I have redacted (hidden. You get a secret token when you sign up for an account.
My custom usage function provides data on the amount of messaging you’ve used in your subscription. As you will note the results to the lef,t I’ve only used a tiny portion of my budget. 223 of 500,000 (less than 5/100 of a percent).
There is no message charge to get your usuage.
Response Attributes for IEXQuote Function
|latestPrice||number||Use this to get the latest price
Refers to the latest relevant price of the security which is derived from multiple sources. We first look for an IEX real time price. If an IEX real time price is older than 15 minutes, 15 minute delayed market price is used. If a 15 minute delayed price is not available, we will use the current day close price. If a current day close price is not available, we will use the last available closing price (listed below as previousClose)
IEX real time price represents trades on IEX only. Trades occur across over a dozen exchanges, so the last IEX price can be used to indicate the overall market price.
15 minute delayed prices are from all markets using the Consolidated Tape.
This will not included pre or post market prices.
|latestVolume||number||Use this to get the latest volume
Refers to the latest total market volume of the stock across all markets. This will be the most recent volume of the stock during trading hours, or it will be the total volume of the last available trading day.
|latestUpdate||number||Refers to the machine readable epoch timestamp of when
|latestTime||string||Refers to a human readable time/date of when
|calculationPrice||string||Refers to the source of the latest price.
Possible values are
|latestSource||string||This will represent a human readable description of the source of
Possible values are
|change||number||Refers to the change in price between
|changePercent||number||Refers to the percent change in price between
|volume||number||Total volume for the stock, but only updated after market open. To get premarket volume, use
|open||number||Refers to the official open price from the SIP. 15 minute delayed (can be null after 00:00 ET, before 9:45 and weekends)|
|openTime||number||Refers to the official listing exchange time for the open from the SIP. 15 minute delayed|
|close||number||Refers to the official close price from the SIP. 15 minute delayed|
|closeTime||number||Refers to the official listing exchange time for the close from the SIP. 15 minute delayed|
|previousClose||number||Refers to the previous trading day closing price.|
|previousVolume||number||Refers to the previous trading day volume.|
|high||number||Refers to the market-wide highest price from the SIP. 15 minute delayed during normal market hours 9:30 - 16:00 (null before 9:45 and weekends).|
|low||number||Refers to the market-wide lowest price from the SIP. 15 minute delayed during normal market hours 9:30 - 16:00 (null before 9:45 and weekends).|
|extendedPrice||number||Refers to the 15 minute delayed price outside normal market hours 0400 - 0930 ET and 1600 - 2000 ET. This provides pre market and post market price. This is purposefully separate from
|extendedChange||number||Refers to the price change between
|extendedChangePercent||number||Refers to the price change percent between
|extendedPriceTime||number||Refers to the last update time of
|delayedPrice||number||Refers to the 15 minute delayed market price from the SIP during normal market hours 9:30 - 16:00 ET.|
|delayedPriceTime||number||Refers to the last update time of the delayed market price during normal market hours 9:30 - 16:00 ET.|
|marketCap||number||is calculated in real time using
|avgTotalVolume||number||Refers to the 30 day average volume.|
|week52High||number||Refers to the adjusted 52 week high.|
|week52Low||number||Refers to the adjusted 52 week low.|
|ytdChange||number||Refers to the price change percentage from start of year to previous close.|
|iexRealtimePrice||number||Refers to the price of the last trade on IEX.|
|iexRealtimeSize||number||Refers to the size of the last trade on IEX.|
|iexLastUpdated||number||Refers to the last update time of
|iexMarketPercent||number||Refers to IEX’s percentage of the market in the stock.|
|iexVolume||number||Refers to shares traded in the stock on IEX.|
|iexBidPrice||number||Refers to the best bid price on IEX.|
|iexBidSize||number||Refers to amount of shares on the bid on IEX.|
|iexAskPrice||number||Refers to the best ask price on IEX.|
|iexAskSize||number||Refers to amount of shares on the ask on IEX.|
|symbol||string||Refers to the stock ticker.|
|companyName||string||Refers to the company name.|
|primaryExchange||string||Refers to the primary listing exchange for the symbol.|
|peRatio||number||Refers to the price-to-earnings ratio for the company.|
|lastTradeTime||number||Epoch timestamp in milliseconds of the last market hours trade excluding the closing auction trade.|
|isUSMarketOpen||boolean||For US stocks, indicates if the market is in normal market hours. Will be false during extended hours trading.|
Raymond Mills, M.B.A., M.S. has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst. His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” If you have a challenge with Excel, Access or Word and would like to speak with Ray, you can get his contact details by clicking here: Contact Me