Introduction to Deriscope – Part 5: Live Feeds
Deriscope enables you to receive in Excel live feeds from several different providers.
These feeds include real time or almost real time prices of financial securities (stocks, ETFs, indices, currencies, cryptocurrencies, futures and options), bid/offer quotes and sizes, time series of historical prices and statistical indicators, company financial data and market news.
Deriscope receives the feeds extremely fast in a silent mode avoiding interference with all other spreadsheet activities undertaken by the user.
This short video demonstrates how the real time prices of all S&P 500 stocks are updated every single second without making Excel any slower!
Deriscope acquires and displays the various feeds by means of spreadsheet formulas.
If you are already familiar with how Excel formulas work, you would find Deriscope very easy to use.
The most important advantage of formulas is their guarantee that their output is a) fully controlled by their input and b) affects only those cells where the formula applies. So, no surprising sets of data would ever appear in the middle of the spreadsheet, as is often the case with apps that operate with visual basic or other macros.
As of now, six sources of live feeds (providers) are supported, two of which (Yahoo and IEX Tops) can be accessed without prior registration. All others require a username that you can easily acquire by visiting their website. Deriscope will provide you with the needed guidance as soon as you try to access any of these providers without having the required credentials.
The relative strengths and weaknesses of the supported providers are summarized below:
|Provider||Securities||Domain||Time Delay||1 Min Time Series||Daily Time Series||Extra||Free Version Limits||Free Account with the Provider|
| Alpha Vantage||Stocks, Indices, Currencies, Crypto||Global||1 min||1 day||20 y||Statistical Indicators||1 symbol/query, |
| Barchart||Stocks, Indices, ETFs, Futures, Options, Currencies, Crypto||Global||15 min||1 month||6 months in free version||||25 symbols/query, |
No Indices, ETFs, Futures, Options
| IEX||US Stocks, US ETFs||US||No Delay||1 day||5 y||Bids, Asks, Earnings, News||500,000 Messages per month||Needed|
| IEXTops||US Stocks, US ETFs||US||No Delay||No||No||Bids, Asks||None||Not|
| TrueFX||Currencies (43 currency pairs)||Global||No Delay||No||No||||None||Needed|
|World Trading Data|
|Stocks, Indices, ETFs, Currencies||Global||varies||Up to 30 days||30 y||||5 symbols/query, |
| Yahoo Finance||Stocks, Indices, ETFs, Futures, Options, Currencies, Crypto||Global||varies||1 week||48 y||||None||Not |
Table of Contents
The Spreadsheet Formulas
Getting a single quote in Excel is as simple as entering the formula =dsLiveGet("YF","GOOG") that returns the current Google stock price fetched from Yahoo Finance.
The first argument "YF" is specifies the provider, while the second argument "GOOG" is the security symbol.
So, entering the formula =dsLiveGet("IEX","GOOG") would return the Google price fetched from IEX. This formula would return an error if you have not yet entered your IEX user key in the Settings section of the Deriscope wizard. You can easily acquire a user key for any provider by following the guidelines presented at the bottom of the wizard as soon as you select the cell that returned the error. Exceptionally, Yahoo Finance and IEX Tops require no such key.
It is also possible to process several symbols in one step by linking the second parameter to a range containing the list of symbols. The ensuing speed is very high. For example, a list consisting of the 500 symbols of the S&P500 index is processed in only one second (!), where other apps usually take much longer.
The problem with dsLiveGet is that its input is generally stable and therefore it would not be recalculated when you press F9.
Thankfully there exists the function dsLiveGetv, which is the volatile version of dsLiveGet, that gets recalculated and displays its retrieved feed every time the spreadsheet is modified.
So, it's up to you to decide which function, dsLiveGet or dsLiveGetv to use for your task at hand.
An interesting property of dsLiveGet is that it does not necessarily venture over the internet trying to contact the provider's server. It is smart enough – or lazy enough – to know if the feeds have been already fetched and stored by someone else who is referred as an "engine", in which case it simply grabs the feeds from there.
An engine is a mechanism that keeps collecting live feeds by contacting repeatedly the provider's server, albeit in a "silent" way so that it does not slow down any other Excel activities.
You decide whether to use an engine or not.
You can start an engine with a formula like =dsLiveStartEngine("YF",3,"GOOG", "price"), which contacts the YF (Yahoo Finance) server every 3 seconds requesting the price quote of the GOOG stock symbol.
You may replace "GOOG" with a column containing several symbols and "price" with a row containing several fields, such as "bid", "ask", "volume" etc.
If the above engine has started, a formula such as =dsLiveGet("YF","GOOG") would not try to reach the YF server but would grab the feed already stored in the engine.
The third and last function that specializes on live feeds is the =dsLive(handle), which has diverse uses, but primarily serves the need for historical data.
Getting dsLive to work by hand is tricky because one needs to first create its single input argument handle.
The usual procedure is to let either the Deriscope wizard or the dedicated DeriLive ribbon to do this job.
The Excel Ribbon for Live Feeds
You don't need to enter any formula by hand.
Deriscope comes with a ribbon tab called DeriLive that contains all the buttons you need to setup powerful spreadsheets with live feeds and historical data.
It looks as in the screenshot below:
The left part of the ribbon is the most interesting and contains the seven live feeds providers currently supported by Deriscope.
Getting live feeds from a particular provider in an asynchronous, repeated fashion is as simple as switching on the corresponding little grey lamp, by clicking on it.
Here you see what I got after I switched the YF (<= Yahoo Finance) lamp on:
I made the screenshot exactly when the live feeds hit the spreadsheet, which they keep doing every 5 seconds due to the number 5 in the shown formula.
The video below is a continuous loop of a 10-second recording of a spreadsheet with feeds coming in simultaneously from three different providers (Yahoo Finance, TrueFX and IEXTops). The update interval is 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.
The ribbon can help you customize the involved formulas, without even touching the keyboard!
For example, you may change the update frequency by clicking on the lens button and choosing the Engine Frequency menu item:
Then the following dialog would appear that lets you set any update interval you want, starting from one second:
Customizing the array of symbols is also very simple.
You may of course just edit the shown demo symbols on column A, but you may also opt to use your own column of symbols located elsewhere in the spreadsheet.
Similarly, you may want to modify the array of fields shown on the top row as price and time.
All these jobs – and many more - imply a modification of an existing formula and are therefore supported by the buttons shown in the middle area of the ribbon:
Finally, the right part of the ribbon contains several buttons that perform various utilities:
For example, you may change the flashing colors by clicking on the palette icon at the bottom right. Then you could, for example, make incoming feeds appear with colors affecting only the font, while the background stays the same. And also make the upticks shown as blue rather than green:
Displaying all Fields supported by a Provider
If the cell validation dropdown does not list all the fields that are supported by the given provider, you can generate them by clicking the button at the bottom, like the one circled in red shown below for the YF (Yahoo Finance) provider:
Then the following dialog appears that asks you the task you want to perform:
You may now select PASTE ALL FIELDS at the top right and click OK to have all the fields supported by Yahoo Finance pasted at and below the selected cell.
Below you see the list of fields arranged in three columns for a concise display:
Getting Historical Data and Technical Indicators
With regard to historical data, Deriscope makes it very simple to process several tickers together or even combine feeds received from different providers.
As an example, below you see how the simple spreadsheet formulas =ds() and =dsLive() can be used to form three different requests: One to Yahoo Finance for the historical Microsoft prices, one to Alpha Vantage for the historical SMA (Simple Moving Average) and one more to Alpha Vantage again for the historical MOM (Momentum) and display the three sets of received in one single consolidated table:
Retaining and Displaying the Most Recent Live Feeds
While under a usual operation the engine keeps only the last feed and discards all the rest, it is possible that it retains the most recent feeds in a memory buffer of a specified size.
The special function =dsLiveStartEngine(…) cannot be used to start an engine with the feed retention property. The Deriscope function =ds(…) should be used instead, which is the main function used in more advanced financial pricing applications.
Specifically, you should use =ds(…), which returns a handle of a special object that defines the various engine properties, such as the size of the intended buffer and then run the function =dsLive(handle) to actually start the engine.
Having done so, feeds will be collected and retained with the specified update frequency, but nothing yet will be displayed on the spreadsheet.
If you use =dsLiveGet(), you would only display the latest feed, which is not what you want.
In order to display all feeds currently stored in the buffer, you would need the function =ds(…), which returns a handle of a special object that defines the portion of the buffer data that must be displayed and then run the function =dsLive(handle) to actually display these data!
Obviously, you are not supposed to type all these four functions by hand.
The way to go is by clicking the Insert Function button associated with the chosen provider.
For example, if you want to display the most recent currency rates received from TrueFX, you must click the red-circled button below:
Then the same dialog appears as before when you wanted to paste all supported fields.
This time you keep the pre-selected QUICK START tab and choose the Display Series of Most Recent Live Data as shown below:
When you click OK and wait a few seconds, the following appears:
The buffer-retained feeds are shown in the middle in columns D and E and represent the bidPips of EUR/USD and USD/JPY, as specified respectively in cell H8 and range G11:G12.
Note the engine acquires every 5 seconds (due to the takt 5 in cell B7) many more feeds for all the currency pairs defined in column A, but I have decided to display only the bidPips of these two pairs in columns D and E.
The above screenshot was taken after about one minute of operation, during which time exactly 13 sets of feeds had arrived, shown above as the non-zero bracketed values.
The top rows always contain the most recent feeds. This means the displayed numbers are shifted one row lower every 5 seconds to make room for a new row of data at the top.
A short time later, my buffer of size 20 (due to my setting of 20 in cell B8) has been filled and the 13 rows of feeds shown above have been all shifted to the bottom, as shown at the next screenshot:
Storing Incoming Live Feeds Automatically to a File
Deriscope can be instructed to automatically store incoming live feeds to a designated local text file in CVS format.
Every time, new live feeds arrive, they are appended at the bottom of that file.
If no file initially exists, it is created.
After its creation, any already existing data are preserved, while new live feeds are appended at the bottom.
The formulas that make all this possible are very similar to those described in the section above.
They can be pasted in the spreadsheet by clicking the Insert Function button associated with the chosen provider (same as above) and choose the Store Incoming Feeds to File as shown below:
Next you click OK and the required simple formulas are pasted in the spreadsheet as shown:
I think the meaning of the above structure must be obvious.
Feeds are stored automatically because the Store Feeds value in cell B10 is TRUE.
Set it to FALSE to stop the storing process.
What exactly is being stored is defined in columns D and E.
In this case, each row of the created text file will contain the rates associated with the given 11 currency pairs. As a matter of fact, not just the rates, but all the fields defined in column E.
A new row will be appended every 5 seconds because the takt is set to 5 in cell B7.
The name and path of the created file are also specified.
Finally, I can easily navigate to the created file by selecting the cell B12 containing the green handle name &FPath_A18:1.1, which causes my windows explorer to pop up – because Auto Open in cell B22 is set to TRUE - with the text file FeedsTFX.cvs pre-selected as shown below:
I may open this text file with a text editor, for example notepad, to see its contents as below:
But for a more readable experience I would import these data in Excel using the ribbon Data tab and selecting From Text/CSV as shown here:
The final result looks much more pleasant:
Forex Trading Simulator
It is also realistic as the bid and offer quotes are coming in real time from TrueFX. You can specify your leverage and the currencies into which you wish to trade. You may trade simultaneously in several different currencies and watch the total P&L as well as the P&L per each currency. All transactions including brokerage fees are stored in the ledger area. More details are available in this article.
You may download the spreadsheet here.
Customization of Appearance
Most Deriscope features can be customized to suit the user's preferences.
For example, you may set the colors shown when cells flash by clicking on the Color Settings button:
Then the following dialog appears:
Similarly, you may change the default one second duration of the flashing colors by clicking on the Flash Duration button:
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