Market Data in Excel from Barchart

Barchart is – among else - a provider of live and historical prices of global stocks, indices, mutual funds, futures, options and forex rates (both fiat and crypto). 

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.

In the free version of their service, live feeds are restricted to less than 25 symbols per query and 400 queries per day whereas the historical data may extend up to the last 6 months. End of day data are available from AMEX, NYSE, NASDAQ, whereas CBOE BZX exchange equity and Forex data are included on a 15-minute delay. The free version also excludes indices, mutual funds, futures and options. 

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 BC (Barchart) in a manner that minimizes the required number of server requests so that your applicable free or purchased BC limits are not breached. 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 BC.

BC access through Deriscope requires that a special code (user key) has been set in its configuration section. If the existing default code does not work, you will be advised to acquire a code from the Barchart website and then enter it in the wizard's Settings entry labeled User Key, as the video below demonstrates: 

In the final user entry screen shown below, you may also see the entry labeled Tickers Per Request, which is set to 25 by default. As the information text at the bottom of the taskpane makes clear, this number should match the maximum number of tickers per api request allowed by your current BC subscription. Deriscope sets it initially to 25 because the current free BC limit is 25, but you should change this entry if the respective limit ever changes. 

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 BC. 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 7 columns labeled Time, open, close, high, low, volume and openInterest.

This has been achieved as follows:

When the formula =dsLive(A2) runs, it sends the request defined in cell A2 to the BC 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:B20) is shown in the formula bar. 

This formula has returned the text &HistBC_A2:1.1, 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:B20 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:

The generated array formula is {=ds("ObjectTools::Show";"Reference=";$A$1;"Key=";"1,4,1,3")} and applies on the range A22:C25 as shown below: 

If you are simply interested in displaying all BC 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 

Even though BC can process only one ticker per request, you may still 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:

Please note that a single Deriscope request comprising N tickers is actually resolved through the submission of N separate api requests to the BC server.

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 BC 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("BC";A2:A15;B1:C1;FALSE) inserted in range B2:C15 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 BC feeds. You may add as many rows as you wish and also more columns in order to display additional fields. Currently BC supports 46 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:C15 as shown below:

Cell A1 contains the formula =dsLiveStartEngine("BC";5;A2:A15;B1:C1) that starts the live engine, effectively a repetitive request of live data from the BC 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 &LiveBC, 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("BC")} pasted over the range B2:C15, 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.

The next image shows a snapshot of how the spreadsheet looks like when the feeds are displayed. 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. 

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