Excel Custom Functions For IEX Trading API

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:

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
Cash Flow
Collections
Advanced Dividends
Company
Upcoming Events
Volume by Venue
Technical Indicators
Splits
 
Obtaining excel stock prices could not be easier with my IEX Custom Excel Functions

 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 IEX trading custom functions can bring back over 40 different market data Types

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.

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.
Raymond Mills MBA, MS

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 

Comments are closed.