How to Get Live & Historical Market Data in Excel?

1
As a first step, you need to identify your data sources. These can be the exchanges where the data originate or the companies that acquire and redistribute such data to the wider public. An example of the former in the case of US stocks is IEX (the Investors Exchange), which broadcasts all data in real time. An example of the latter is Yahoo Finance, which assembles data from various exchanges before publishing them.

After you have identified a source, you can download the data into your Excel spreadsheet by three different methods:

Method 1: Manual Download

Most market data vendors provide detailed instructions on their websites on how to manually download certain portions of their feeds.

You will often find a button that allows you to download anonymously a so called csv file, which is just a text file that can be automatically converted by Excel into a spreadsheet.

While this approach may be acceptable for historical data that do not require frequent update, it becomes quite burdensome if you must monitor your portfolio several times a day.

Method 2: Native Excel Automatic Download

As you would probably know already, Excel has an interesting feature that allows you to download certain portions of data displayed on some website. This feature is accessible through the From Web option of the Get External Data group under the ribbon item Data, as shown below:

If you click on From Web, a popup window appears that contains the current contents of the website, the URL of which is specified in the Address field at the top. Usually the site URL is initially set to google.com and appears like that:

 

The data importing works then as follows:

First you enter in the Address input field the specific URL that contains the data you are looking for. When the respective page appears, you would select the portion you are interested in through the little orange arrow and finally click on Import to bring the selected portion into your spreadsheet.

In reality things do not run so smoothly. For example, when you try the URL https://finance.yahoo.com/quote/MSFT, you will be flashed with script errors. After pressing IGNORE a few times you will manage to get a few data into your spreadsheet, but not the ones you would expect! You will only get unintelligible text that includes threatening words such as “unauthorized”, “deactivation”, “missing cookies” etc. Apparently Yahoo Finance is not willing to honor the request sent out from your Excel through the From Web method.

Other websites may be more cooperative. For example, the website with URL https://www.marketwatch.com/investing/stock/live looks like that in a browser:

After you try it out with the From Web method, the data received in the spreadsheet occupy … 435 rows and up to 5 columns.

At least you will be able to locate the featured prices starting at row 34 in a descent tabular form as you can see here:

A useful feature of this native Excel web query is that you can set up an automatically recurring refresh of the fetched data, albeit no more frequently than once per minute.

The main drawback is its reliance on the relative location of the various data within the website of the data host. If, for example, marketwatch decides to shift the S&P 500 quotes to the top row of the displayed data table, your automatic query will also shift the respective spreadsheet data one row higher, whatever this may mean for your formulas that link to those cells.

There is a second native Excel method that makes use of the built-in formula =WEBSERVICE(url), where url can be any web address.

For example, you could enter in any spreadsheet cell the formula =WEBSERVICE(“https://api.iextrading.com/1.0/stock/MSFT/quote/latestPrice”) and get back the latest Microsoft stock price directly from IEX.

Please be aware that this function is not declared volatile, which has the consequence that you would still need to write Visual Basic code if you wanted the returned feeds to be refreshed automatically.

Accessing Live Feeds Method 3: Programmatic Download

It is a fact that Excel’s API (Application Programming Interface) supports the connection to all possible applications. It follows that the perfect solution ought to be a customized program that fetches the data from the provider’s server and displays them in the spreadsheet.

Writing such a program in Visual Basic is perhaps straightforward. It is nevertheless more sensible to get a free or commercial utility that does the job for you.

In what follows I will show you how a popular Excel Add-In called Deriscope, may be used to bring both live feeds and historical time series in Excel. By all means, you can also try other alternative utilities, but I have chosen Deriscope for demonstration purposes for four main reasons: First because it is not bound to a single data feed provider, second because it is particularly user friendly, third because it can also be used for other related tasks such as option and bond pricing and fourth because it costs far less than most other alternatives: Currently only around $10 for a lifetime ownership and 6 month support, with a free version also available that carries somewhat limited features.

As of this writing, Deriscope supports the following six providers:

Yahoo Finance, IEX, Barchart, Alpha Vantage, World Trading Data and TrueFX.

They generally supply real time prices of stocks, indices, mutual funds, commodities, certain options, currencies and cryptocurrencies on a world wide scale. For an up-to-date coverage you are advised to read this blog article

The table below shows the current status:

Step-by-Step Example: Getting Live Feeds on 12 Tickers

In what follows, I will use the Yahoo Finance provider because it currently provides the most complete set of feeds on a world wide scale, with some of them being true real time, as this list indicates. As you see, a few major stock exchanges, such as the NSE and BSE in India, are also part of the true real time data feed sources.

Let us assume you want to get feeds on the following 12 tickers:

MSFT              <- Microsoft (NASDAQ)

SIE.DE           <- Siemens AG (XETRA)

BNS.TO          <- Bank of Nova Scotia (Toronto)

GAZP.ME      <- Gazprom (MCX)

6702.T             <- Fujitsu (Tokyo)

600999.SS       <- China Merchants Securities (Shanghai)

INFY.NS        <- Infosys (NSE)

EURUSD=X  <- Forex Rate EUR/USD

CL=F              <- Light Sweet Crude Oil Futures (NYM)

^DJI                <- Dow Jones Industrial Average Index

^FTSE             <- FTSE 100 Index

^N225             <- Nikkei 225 Index (Osaka)

Your first step would be to place these tickers in some vertical range, for example in cells A2 to A13 and type the following simple formula on top of them in cell A1:

=dsLiveStartEngine(“YF”;5;A2:A13)

as shown below:

When this formula runs, the live feeds engine starts requesting feeds from Yahoo Finance (due to “YF” in the first argument) every 5 seconds (due to 5 in the second argument) for the 12 tickers specified in the range A2:A13 of the third argument.

The great thing about Deriscope is that you are not bound in Yahoo Finance. You may change the code “YF” in the first argument to any other valid code to redirect the engine to the respective provider. Replacing for example YF with IEX, your engine starts pulling data from the IEX server.

The engine keeps receiving feeds every 5 seconds but does not display them in the spreadsheet. The latter is accomplished by a special array formula called {=dsLiveGetAsync(“YF”)} that may be used as follows: Select a vertical range with a height of 12 cells, then type in the formula bar the formula =dsLiveGetAsync(“YF”) and finally press simultaneously CTRL-SHIFT-RETURN to have this formula entered as an array formula. Then each time this formula is calculated, it will return the most recently received feeds.

The next image shows the spreadsheet with the array formula pasted to the immediate right of the column containing the tickers, so that the association between tickers and prices is obvious.

The array formula is declared volatile, which means it will be automatically recalculated when anything in the spreadsheet changes. But you can force its recalculation to occur as soon as feeds arrive by clicking on a special Auto Refresh button in the Deriscope wizard (see below). In any case, when the displayed prices change, the respective cells will flash green if their price goes up and red if their price goes down. This is a typical display of the spreadsheet at the end of a 5 second cycle:

You are by no means limited to last traded prices. If your chosen provider supplies additional fields, you can display them by adding a fourth argument inside the dsLiveStartEngine formula that references a row of fields entered anywhere in the spreadsheet.

You can view all valid field names by selecting any ticker-containing cell, for example cell A2 containing the ticker MSFT, and clicking on the appeared validation dropdown as per image below:

Yahoo Finance is particularly prolific in this respect and supplies the following 56 fields:

ask,  askSize,  avg200day,  avg50day,  avgChange200day,  avgChange50day,  avgChangePercent200day,  avgChangePercent50day,  avgDailyVolume10day,  avgDailyVolume3month,  bid,  bidSize,  bookValue,  change,  changePercent,  currency,  earningsTimestamp,  epsTrailing12month,  exchange,  exchangeDataDelayedBy,  exchangeFullName,  financialCurrency,  gmtOffSetMilliseconds,  high,  high52week,  highChange52week,  highChangePercent52week,  longName,  low,  low52week,  lowChange52week,  lowChangePercent52week,  market,  marketCap,  marketState,  open,  previousClose,  price,  priceHint,  priceToBook,  quoteSourceName,  quoteType,  range,  range52week,  region,  shares,  shortName,  sourceInterval,  symbol,  time,  timezoneName,  timezoneNameLong,  trailingAnnualDividendRate,  trailingAnnualDividendYield,  trailingPE,  volume

You can also copy in clipboard all fields supported by the selected provider by clicking on the wizard’s Tools button (see below) and choosing the Copy Allowed Parameter Values menu item.

Let us assume, you want to display the three fields: price, previousClose and time.

You can do that by pasting cell A2 in cells B1, C1 and D1 and then use the respective dropdowns to select the desired fields. Then you should add the range B1:D1 as the fourth argument inside the dsLiveStartEngine formula and extend the range on which the array formula applies to B2:D13 so that three columns of feeds are displayed:

Using the integrated Deriscope Wizard

You do not need to memorize the various formulas or type them in by hand.

Deriscope comes with an integrated wizard in the form of a taskpane that can be conveniently hidden to the right of the spreadsheet window. If not hidden, it looks like that:

It may look a bit imposing at first sight, perhaps because it is designed to deal primarily with derivatives pricing and portfolio management and only marginally with live feeds. But if you click on the button with the tools symbol, you can easily navigate to the final menu item Asynchronous (extended) that generates the exact same formulas discussed so far.

It is even possible to receive live feeds from several providers simultaneously. The video below is a continuous loop of a 10-second recording of a spreadsheet where three providers are employed, each with an update interval of 1 second.

Step-by-Step Example: Getting Historical Data on Microsoft

The acquisition and display of historical data is accomplished with the help of two Deriscope formulas.

The first and most important formula is simply called =ds(…), which is the flagship Deriscope formula being used in almost all possible situations. The input inside the parentheses is a range containing the parameters that specify the type of historical data needed, for example the associated ticker, time interval, type of reported fields, but also meta-processing instructions like ordering, filtering, scaling etc.

The second formula is called =dsLive(…) and is used to actually send the request specified by ds(…) to the provider’s server, receive the response and display it on the spreadsheet.

The most convenient way to enter both formulas in the spreadsheet is to let the wizard do it for you. Simply click on the Tools button, select the Insert Function item, followed by the Live Feeds item, then the (Yahoo Finance) item and finally click on Time Series in order to paste the necessary formulas at and below my currently selected cell.

The following video shows these steps:

that results in the following:

Understanding the dsLive Formula in Cell A1

The request in cell A1 has been constructed by the wizard with regard to the daily historical prices of the Microsoft stock (ticker of MSFT) for the course of one year.

The formula =dsLive(A2) returns the text &Variant_A1:1.1, which is the handle name of the collection of historical data held in Excel’s memory. The contents of this collection are displayed inside the Browse Area of the wizard as soon as cell A1 is selected.

Understanding the ds Formula in Cell A2

Cell A2 contains the formula =ds (A3:B15), which defines the details of the request. It returns the text &HistYF_A2:1.1, which is the handle name of an object of type Hist YF. As above, the contents of this request are displayed by the wizard as soon as cell A2 is selected.

Below you see the spreadsheet with cell A2 selected.

Output as Array versus Output as Object

An important input parameter is the Output As Object that can be set to either TRUE or FALSE.

The default value is TRUE that results in the output being the handle name of an object. Then the historical data are not returned to the spreadsheet but are kept inside the referenced object.

You can easily bring all or a portion of the data shown in the wizard to the spreadsheet as shown in the video below:

A more efficient way for displaying all data in the spreadsheet is to set Output As Object to TRUE, in which case dsLive should be inserted as an array formula because it returns the historical data as an array without the intermediate creation of an object.

This is shown below, where all optional input parameters have been removed for simplicity:

Choosing the Sampling Interval

The sampling interval can be changed by clicking the validation dropdown on the cell next to the Interval parameter as shown below:

Setting it to Month, the result looks like:

Choosing between History, Dividends and Splits

The Events parameter controls the kind of data requested from the server.

There exist three valid options as shown below:

Setting it to Dividends, you get the following result:

Choosing the Time Range

The time range spanned by the historical data is controlled by the parameters From and To. Deriscope helps you change the respective dates by offering you a popup calendar as soon as you select any date-containing cell:

Choosing which Columns are Displayed

You can display only a subset of the original columns by changing the value of the Columns property. When you select the value cell, the following validation dropdown appears:

If you set it to Exclude, the function returns error, due to missing input. The wizard will helpfully instruct you to add the additional Column List property, the purpose of which is to specify the indices of the excluded columns.

You see below how the Open, High, Low and Volume columns are excluded, after you enter the text 1,2,3,6:

Several Symbols

You can process multiple symbols by associating an array of cells with the Symbols input parameter.

If you do that, the Columns property will not be required as input because it will be set by default to Include. Then the Column List property can be used to specify which columns are displayed.

So setting Column List to 4 results in the display of the Close column alone:

Setting Column List to 4,5 results in the display of the Close and Adj Close columns:

Rescaling all Values to some Common Base

When you display historical data on several symbols side-by-side, you may be interested in comparing the price series in a meaningful sense by rescaling the values so that they all start with the same number, typically 100.

As an example, assume you want to compare the MSFT and GOOG prices. Since MSFT moves around 100 and GOOG around 1,000, you cannot perform any direct comparison. You would even be unable to display both price evolutions in the same chart, since the MSFT curve’s variations would be hardly visible in a chart designed to display values from 0 to 1000.

Deriscope’s solution is the Rescale Values property, which transforms all numbers so that they start with the common base of 100. It is even possible to customize both the common base value and the date when it applies.

Below you see the result, where I have also set the Interval to 3 Months so that all output data can be easily seen inside the wizard:

Now I can easily compare the price evolution of the two stocks and conclude that Microsoft grew faster than Google, since both stocks started at the same price of 100 a year ago.

 

 

Leave a Reply

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

Pin It on Pinterest

Share This