IEX (Investors Exchange) is a new United States national stock exchange launched in New York in September 2016. Since February 2017 IEX publishes free of charge their trading data in real time. These include live and historical prices of over 8,000 US stocks and mutual funds plus financial information and news.
If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in your spreadsheet, check out the 30 seconds videos below:
Getting easily live stock prices in Excel
Getting easily live forex rates in Excel
Getting historical data in Excel
Update as of January 2020: Deriscope has now a new dedicated ribbon that greatly simplifies the live feeds user interface.
For details, visit the Introduction to Deriscope Live Feeds.
Update as of September 2019: Since June 2019 most of IEX feeds require a special user key - either free or subscription based - that may be obtained here. On its absence, Deriscope will alert you with detailed instructions on how to acquire the key and how to enter it in Deriscope's Excel wizard. Note that live stock prices are still accessible without the need of a key through the IEX Tops service, as described further below.
Deriscope Initialization
Deriscope is an Excel AddIn specializing in derivatives valuation and live feeds acquisition in Excel. Its newest version also allows you to receive data from IEX.
Deriscope comes with an integrated wizard in the form of a dedicated Excel taskpane as shown below that helps you generate various spreadsheet formulas, for example those required to fetch live feeds from IEX.
Getting Historical Data for a single Ticker between Specific Dates
You can easily let the wizard generate and paste the formulas required for receiving historical data by clicking on the Tools button and choosing the appropriate menu items, as shown in this picture, where the wizard has been temporarily transferred to the left so that the cascading menu items do not overlap with each other:
After you click on the Time Series menu item, the appropriate formulas will be pasted at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be two formulas inserted in cells A1 and A2 as shown below:
Cell A1 contains the formula =dsLive(A2) that returns the text &Variant_A1:1.1, which is the handle name of an object that lives in Excel's memory and contains the historical data received from IEX. If you select cell A1 this object's contents become visible inside the wizard, as the previous image demonstrates. As you see, the received data comprise 12 columns labeled Time, change, changeOverTime, changePercent, close, high, label, low, open, unadjustedVolume, volume and vwap.
This has been achieved as follows:
When the formula =dsLive(A2) runs, it sends the request defined in cell A2 to the IEX server. When the requested data are received, they are stored in some memory-resident object that is assigned the unique handle name &Variant_A1:1.1. Finally this unique handle name is returned as the formula's output.
In the next image, cell A2 has been selected so that its contained formula =ds(A3:B18) is shown in the formula bar.
This formula has returned the text &HistIEX_A2:1.2, which is the handle name of the object containing the specifications of a particular request formatted as a collection of key-value pairs. The input range A3:B18 contains these key-value pairs, whereby the keys are all on the left column and contain the = suffix, while the respective values are on the right column. The wizard on the right displays the contents of the created request object. For example, the key-value pair Symbols= MSFT indicates the request applies to the MSFT ticker alone. The meaning of most of the keys should be obvious, but at any case their exact definition is displayed at the bottom of the wizard as soon as the containing cell is selected.
Displaying Output Data in the Spreadsheet
Deriscope provides special formulas that take a handle name, such as the &Variant_A1:1.1, as input and return a selected portion of its contents in the spreadsheet. There is no need to memorize these formulas because the wizard can generate and paste the correct formula for you. The following video shows how to use the wizard to create an array formula that display the portion of the object's data consisting of the left 3 columns and top 4 rows:
This browser does not support the video element.
The generated array formula is {=ds("ObjectTools::Show";"Reference=";$A$1;"Key=";"1,4,1,3")} and applies on the range A20:C23 as shown below:
If you are simply interested in displaying all IEX data straight in the spreadsheet, the easier and more efficient method is setting the value associated with the key Output As Object= to FALSE. Then the formula =dsLive(A2) circumvents the creation of an intermediate object and simply returns all received data in the form of an array. In this case, you should also paste that formula as an array using CTRL-SHIFT-RETURN over a big enough range that can fit the output data.
Getting Historical Data for Several Tickers between Specific Dates
You may set more than one tickers in your request specification. The following image shows how you may slightly change the input to the ds formula so that several tickers are assigned to the key Symbols=.
This next image shows an alternative – perhaps simpler - specification that leads to the same effect:
Below you see the respective historical data of both MSFT and GOOG as displayed by the wizard:
Getting Live Quotes through Manual, Synchronous Request
Apart from historical data, you can instruct the wizard to generate a formula that fetches on demand live data from the IEX server and outputs them in the spreadsheet. The following image shows the required menu selections:
After you click on the Synchronous (extended) menu item, the appropriate formulas will be pasted at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be the array formula =dsLiveGetSync("IEX";A2:A13;B1:C1;FALSE) inserted in range B2:C13 as shown below:
This formula is volatile, meaning it is primarily intended for spreadsheets set in manual calculation mode and is refreshed when you explicitly recalculate the containing worksheet. It takes as input a column containing the tickers and a row containing the requested output fields. It returns the corresponding IEX feeds. You may add as many rows as you wish and also more columns in order to display additional fields. Currently IEX supports 34 different fields. You can easily change a given field by selecting the containing cell and choosing an item out of the displayed dropdown list, as shown below:
Getting Live Quotes through Automatically Recurring Asynchronous Requests
Deriscope has formulas that display in spreadsheet live data that keep being automatically updated according to a specified time interval in a manner that does not interfere with other spreadsheet activities. This live data acquisition is called asynchronous and the required formulas can be pasted through the menu option Asynchronous (extended). This pastes the appropriate formulas at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be a regular formula inserted in cell A1 and an array formula inserted in range B2:C13 as shown below:
Cell A1 contains the formula =dsLiveStartEngine("IEX";5;A2:A13;B1:C1) that starts the live engine, effectively a repetitive request of live data from the IEX server. The number 5 indicates the number of seconds between successive requests. Deriscope sets it by default to 5 seconds but you may set it to as low as 1 second.
The function returns the text &LiveIEX, which is the handle name of an object that contains information about the current status of the live engine. If you select cell A1 this object's contents become visible inside the wizard, as the previous image demonstrates.
Far more important is the array function {=dsLiveGetAsync("IEX")} pasted over the range B2:C13, shown in the image below:
This array function is declared volatile and upon calculation returns the latest feeds acquired by the live engine. Although the live engine keeps receiving feeds from the server, the array function normally does not run by itself, so the feeds are generally not displayed as they are received. At the time when this function was last calculated, no feeds had been received and therefore only zeros are being displayed. You may press SHIFT-F9 to calculate the sheet in order to get the currently available feeds or more conveniently press the yellow highlighted Auto Refresh button shown in the above image to set an automatic recalculation of that array function as soon as new feeds arrive. (See video in next section)
Getting Live Bid/Offer quotes and sizes
IEX also supplies real time bid/offer prices and sizes through their IEXTops api interface.
You can get those in the spreadsheet by invoking the IEXTops provider.
For example, this is how you can generate the formulas that fetch asynchronously and display this type of feeds:
The video below is a continuous loop of a 10-second recording of the spreadsheet when both live engines are active simultaneously and the update interval has been set to 1 second. The seen colors are an optional Deriscope visual indication of the direction of any changes occurred in the displayed data: Green for upticks, red for downticks and yellow for non-numeric changes.
This browser does not support the video element.
Getting Any Type of Data
Deriscope's integration with IEX goes beyond live prices and historical series.
You can bring into Excel any type of data made available by the IEX server.
The easiest way is to start with the Any menu item shown below:
After you click on the Any menu item, the appropriate formulas will be pasted at and below the currently selected cell. For example, if cell A1 is currently selected, the result will be two formulas inserted in cells A1 and A2 as shown below:
Cell A1 contains the formula =dsLive(A2) that returns the text &Variant_A1:4.1, which is the handle name of an object that lives in Excel's memory and contains the data received from IEX. If you select cell A1 this object's contents become visible inside the wizard, as the previous image demonstrates. The received data comprise 2 columns and 40 rows - of which only the top 15 are visible here.
The reason you see this particular output is that the request object in cell A2 has been constructed with Service= quote.
You may choose a different value for Service= through the dropdown at cell B5 as shown below:
Accessing the Deep Order Book
An interesting choice for the Service= key is the deep value, which returns bid/offer quotations beyond the top. Below you see the kind of data returned by the deep service:
The data consist of 16 key-value pairs, out of which the bids and asks are the most interesting. Their associated values are two-dimensional arrays, as indicated by the lens icon to the left of the displayed handle names. The contents of these arrays can be viewed by clicking on the respective lens icons. So for example, the array value for bids looks like that:
, which shows the existence of 2 bids, the "top" one at $94.58 for 250 shares and a "deeper" one at $93 for 100 shares. This can be brought to the spreadsheet too, through a special Deriscope formula that the wizard generates as follows:
This browser does not support the video element.
Below you see the array formula pasted by the wizard over the range A15:C17 that produces the bids:
Getting the Company's Earnings
Another interesting choice for the Service= key is the earnings value, which returns quarterly company data.
Below you see the kind of data returned by the earnings service:
Like before, the earnings data are inside the two-dimensional array object indicated by the lens icon. Clicking on it we get 13 columns containing data about the last 4 quarters, as shown below:
Stock Trading Simulator in Excel
You can download from the Deriscope download site a special spreadsheet that allows you to enter BUY and SELL simulated orders on the actual real time bid/ask quotes from IEX. Your P&L that results from these simulated transactions is shown so that you can test if you are able to achieve a consistent profit under real and current market conditions, before you start trading with real money.
More details in this article.
The following two images show the operational and P&L sections of the spreadsheet:
You may download the spreadsheet produced with the above steps here.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com