4 minutes reading time (802 words)

Market Data in Excel from Yahoo Finance


Yahoo Finance is a provider of market information that includes live and historical prices of global stocks, indices, mutual funds, futures, options, commodities as well as forex (both fiat and crypto) and US treasury bond rates. Depending on the sourcing exchange, live feeds may be instant or delayed by a few minutes according to this table. Historical data include daily close, adjusted close, open, high, low and volume quotes 

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 YF.

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 YF. 

Getting Historical Data for several Tickers between Specific Dates

Deriscope allows you to receive historical data from YF that relate to prices, dividends or splits. The article about Yahoo Finance historical feeds explains how this is done.  

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 YF server and outputs them in the spreadsheet. The following image shows the required menu selections, 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 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("YF";A2:A21;B1:C1;FALSE) inserted in range B2:C21 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 YF feeds. You may add as many rows as you wish and also more columns in order to display additional fields.

Currently YF supports 56 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:C21 as shown below:  

Cell A1 contains the formula =dsLiveStartEngine("YF";5;A2:A21;B1:C1) that starts the live engine, effectively a repetitive request of live data from the YF server. The number 5 indicates the number of seconds between successive requests. Deriscope sets it by default to 5 second, but you are free to change it even down to 1 second.

The function returns the text &LiveYF, 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("YF")} pasted over the range B2:C21, 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. But there is a button on the wizard – shown yellow highlighted below - that can set an automatic recalculation of that array function as soon as new feeds arrive. 

The video below is a continuous loop of a 10-second recording of the spreadsheet when 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. This last image shows a snapshot of how the spreadsheet looks like when the feeds arrive. 

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 

Market Data in Excel from Barchart
Market Data in Excel from World Trading Data