Excel Custom Functions for IEX Trading API
Investors Exchange is a stock exchange based in the United States. It was founded in 2012 and launched as a national securities 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 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:
Investors Exchange is a stock exchange based in the United States. It was founded in 2012 and launched as a national securities 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 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:
Yahoo Finance Excel Stock Pricer Function
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.
Quote
Dividends
Advanced Stats
Balance Sheet
Dividends
Advanced Stats
Balance Sheet
Cash Flow
Collections
Advanced Dividends
Company
Collections
Advanced Dividends
Company
Upcoming Events
Volume by Venue
Technical Indicators
Splits
Volume by Venue
Technical Indicators
Splits
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:
https://cloud.iexapis.com/
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.
https://iexcloud.io/docs/api/#introduction
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.
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.
If you are interested in obtaining these or additional IEX Custom functions for any of the data provided by IEX please contact me here:
Click here for contact information
Response Attributes for IEXQuote Function
MarketDataType
| Key | Type | Description |
|---|---|---|
| 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 latestPrice was last updated. Represented in milliseconds since midnight Jan 1, 1970. |
| latestTime | string | Refers to a human readable time/date of when latestPrice was last updated. The format will vary based on latestSource is inteded to be displayed to a user. Use latestUpdate for machine readable timestamp. |
| calculationPrice | string | Refers to the source of the latest price. Possible values are "tops", "sip", "previousclose" or "close" |
| latestSource | string | This will represent a human readable description of the source of latestPrice. Possible values are "IEX real time price", "15 minute delayed price", "Close" or "Previous close" |
| change | number | Refers to the change in price between latestPrice and previousClose |
| changePercent | number | Refers to the percent change in price between latestPrice and previousClose. For example, a 5% change would be represented as 0.05. You can use the query string parameter displayPercent to return this field multiplied by 100. So, 5% change would be represented as 5. |
| volume | number | Total volume for the stock, but only updated after market open. To get premarket volume, use latestVolume |
| 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 latestPrice so users can display the two prices separately. |
| extendedChange | number | Refers to the price change between extendedPrice and latestPrice. |
| extendedChangePercent | number | Refers to the price change percent between extendedPrice and latestPrice. |
| extendedPriceTime | number | Refers to the last update time of extendedPrice |
| 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 latestPrice. |
| 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 iexRealtimePrice in milliseconds since midnight Jan 1, 1970 UTC or -1 or 0. If the value is -1 or 0, IEX has not quoted the symbol in the trading day. |
| 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. |
If you enjoyed this post and would like to comment or share improvements please send your comments Here.
If you would like to say hello and buy me a cup of coffee Click here.
If you would like to say hello and buy me a cup of coffee Click here.
