Yahoo Finance Live Feeds in Excel after their API Discontinuation in November 2017

On the fateful Wednesday of November 1st, 2017 Yahoo decided to stop their – until then – free service of delivering real time market data as a text stream through a special URL. For hundreds of businesses and individuals who had relied for years on Yahoo's benevolent free service, this single action meant only one thing: Instant death!

In the ensuing hours and days, the by then "dead" businesses and individuals started to wander in cyberspace as "zombies" looking not for virgin blood, but for some alternative live feeds provider, who could help them come back to financial "life".

Yahoo service's demise – attributed to a change of strategy of their recent new owner Verizon – presented a huge opportunity to a few other businesses, who aspired in filling the gap left by Yahoo. One of them was mine. I am the developer of the Deriscope Excel AddIn that specializes in the valuation of derivatives and acquisition of live feeds from diverse providers. By the end of August 2017 I had finished the integration with the Alpha Vantage provider, a new venture that supplied real time quotes and historical data for stocks around the world. By November 1st, my website https://www.deriscope.com had about 20 visitors a day. On the single day of November 3rd my site was hit by 373 unique visitors!

By now, August 2018, people have been flocking to alternative free or low cost providers in order to somehow fulfill their business needs. I have followed suit by integrating Deriscope with additional providers such as IEX (Investors Exchange) who delivered non-delayed prices and historical data on US stocks and ETFs and TrueFX who deliver mainstream currency exchange rates. But the truth was that everybody missed the trusted global coverage and robustness of the Yahoo Finance feeds.

The great news is that Deriscope is now capable of retrieving live data from Yahoo Finance again! Being likely the first in the world to have achieved this, you would hopefully forgive me for not revealing the technical details behind this feat. But if you are simply interested in getting Yahoo Finance feeds in your spreadsheet, then all you need is downloading and installing the non-intrusive Deriscope AddIn from https://www.deriscope.com/freedownload.php

Afterwards each time you start Excel, a new ribbon item called "Deriscope" would appear, as shown below.

By now no Deriscope dlls are loaded so that you may work with Excel without worrying about a possible interference between Deriscope and any other 3rd party loaded AddIns. If you wish to use Deriscope, you need to click on the "Deriscope" ribbon item and select the "Enable and Show Wizard" button:

The result is the appearance of the Deriscope wizard in the form of a taskpane that acts as your control panel for carrying out various tasks in Excel:





For example, you can ask the wizard to generate all required formulas for retrieving live data from Yahoo Finance in an asynchronous fashion and paste these formulas in the area of which the top/left corner is the currently selected spreadsheet cell, by clicking on the Tools button and choosing the appropriate cascading menu items as shown in the next image. Note I have temporarily placed the wizard on the left side in order to avoid a visual overlap among the various menu item selections.

The next image shows the result of two spreadsheet formulas that reference a demo list of tickers on the left column and output fields on the top row. I have also moved the wizard back to its usual location on the right.

Cell A1 contains the first formula =dsLiveStartEngine("YF";5;A2:A21;B1:C1), the job of which is to start the live feeds engine asynchronously with the main Excel thread. In less technical terms this means that live feeds are requested from the Yahoo Finance server in regular time intervals – every 5 seconds in this case due to the number 5 in the second argument – in a background thread, i.e. without interfering with the other actions undertaken by the user, such as typing in cells, opening workbooks etc.

You will notice that even though the live feeds engine has started, no feeds appear in the output columns B and C. The reason is the following:

Over the columns B and C exists the second formula {=dsLiveGetAsync("YF")}, which is a single array formula – that's why it is enclosed in {} – that all it does, is returning the most recently acquired feeds. To make it clear: This array formula does not request any feeds. It only outputs the feeds that have been previously fetched by the dsLiveStartEngine formula.

Now you may guess why no feeds are displayed. Because the array formula {=dsLiveGetAsync("YF")} has not yet run after the live feeds engine started. But as soon as you recalculate this formula – for example by selecting it and pressing CTRL-SHIFT-RETURN simultaneously, the feeds will appear on the range covered by the array formula, as shown below:

The green color indicates the respective quote has moved up.

It is also possible to instruct the wizard to perform this recalculation automatically every time new feeds arrive by clicking on the Auto Refresh button shown below:

Finally you may display more output fields by inserting additional columns and entering the appropriate title. Currently Yahoo Finance supports 56 different fields that you may choose through the dropdown as shown below:

Testing has shown that this setup is very stable even when you have hundreds of tickers being updated every second!

A video tutorial with voice narration is also available here:

Feel free to contact me if you want to share any thoughts with me with regard to this product or if you want me to add any particular features. Contact info and social media links are available at my web site  https://www.deriscope.com

Like this post and wanna learn more? Have a look at Knowledge rather than Hope: A Book for Retail Investors and Mathematical Finance Students

FinViz - an advanced stock screener (both for technical and fundamental traders)

Leave a Reply

Your email address will not be published. Required fields are marked *