Market Data in Excel from World Trading Data
***********************************************************************************************
Deprecated! This service provider has been shut down!
***********************************************************************************************
World Trading Data is a provider of live and historical prices of global stocks, indices, US mutual funds and forex rates.
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.
Their expanding database currently covers over 58,000 stocks around the world, 25,000 US-based mutual funds and 20,000 fiat currency combinations. Live feeds are generally delayed by a few minutes, as is usual with this type of service and may thus be used for frequent portfolio valuation throughout the day. Historical data include daily and intraday close, open, high, low and volume quotes. The daily data go back as long as 30 years. The intraday data can be as frequent as minute-by-minute and cover a range of up to the last 30 days.
World Trading Data provide a so called "fremium" service, whereby anyone can receive for free a limited number of quotes per day and have the option of upgrading to a higher limit service through a subscription that starts at $8 per month. Currently the free service limits the number of requests sent to the WTD server to 250, with each request being capable of processing simultaneously up to 5 tickers in the case of real time data, 2 tickers in the case of single-date historical data and 1 ticker in the case of multi-date historical data. This makes up a maximum total of 1,250 real time quotes in the first case, a figure clearly sufficient for monitoring prices of small and average size portfolios. Users who need more frequent updates on a larger set of securities may choose one of these subscription packages.
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 WTD in a manner that minimizes the required number of server requests so that your applicable free or purchased WTD 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 WTD.
Since WTD access requires a special code (api key) that can be obtained for free by registering your email address at the WTD website, you must first let Deriscope know of that code. This must be done only once, by entering the code 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 5 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 WTD subscription. Deriscope sets it initially to 5 because the current free WTD limit is 5, 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 WTD. 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 6 columns labeled Time, open, close, high, low and volume.
This has been achieved as follows:
When the formula =dsLive(A2) runs, it sends the request defined in cell A2 to the WTD 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:B15) is shown in the formula bar.
This formula has returned the text &HistWTD_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:B15 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:
If you are simply interested in displaying all WTD 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.
The generated array formula is {=ds("ObjectTools::Show";"Reference=";$A$1;"Key=";"1,4,1,3")} and applies on the range A17:C20 as shown below:
Getting Historical Data for Several Tickers between Specific Dates
Even though WTD 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 WTD server.
Getting Historical Data for Several Tickers on a Single Date
If you are only interested in getting historical data for several tickers pertaining to a single past date, then you can setup your request in the same way as described above and set the From= and To= dates equal to each other. Then Deriscope is smart enough to make use of a special WTD api portal that allows the simultaneous processing of several tickers through a single api request.
For example, you may request the closing prices of the two stocks MSFT and GOOG as of 07 Sep 2018 by setting the following request:
Getting Intraday Data for a single Ticker over the recent Past
You can set your object of type Hist WTD to represent a request for intraday data by setting Request= Intraday. Then need to supply values for the Interval and Range entries. Currently Interval can be as low as Minute and Range can reach 30 days. The image below shows the intraday feeds in action:
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 WTD 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("WTD";A2:A10;B1:C1;FALSE) inserted in range B2:C10 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 WTD feeds. You may add as many rows as you wish and also more columns in order to display additional fields.
Currently WTD supports 36 different fields, 15 of which pertain to mutual funds exclusively. 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:
You may mix stocks with indices, mutual funds and forex rates. The Deriscope convention dictates that tickers prefixed with # denote mutual funds, whereas forex rates are entered in the format USD/EUR.
Although not required, it is better to arrange your tickers so that elements of the same group (stocks/indices, mutual funds, forex rates) are kept together, as this results to the least number of api calls sent to the WTD server.
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:C10 as shown below:
Cell A1 contains the formula =dsLiveStartEngine("WTD";300;A2:A10;B1:C1) that starts the live engine, effectively a repetitive request of live data from the WTD server. The number 300 indicates the number of seconds between successive requests. Deriscope sets it by default to 5 minutes because this is the current update frequency of the feeds sent by WTD.
The function returns the text &LiveWTD, 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("WTD")} pasted over the range B2:C10, 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.
Deriscope also provides an optional visual aid about the direction of any changes of 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.
Search Function
A unique feature of WTD is its search facility that can be launched from the menu selection Search shown below:
This brings the following dialog, where you can specify your query. In this example, the query is about all WTD data that contain the word "microsoft" either in the security symbol or the company name.
The result is data pasted in the spreadsheet as shown below:
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
