Blog

Nothing can beat Excel as a multi-purpose user interface medium. It is then only natural to want to analyze the performance of your stock portfolio in Excel, as that would enable you to devise and apply your own custom metrics.

But in order to monitor your portfolio metrics in real time, you would need to get the required market data in real time as well. You may think that getting true real time prices is only available to expensive broker applications. This is generally not true! Several exchanges publish their data immediately as they are generated and allow everyone to access them through their API (Application Programming Interface). A few examples are IEX (Investors Exchange), the new exchange launched in New York in 2016, the Indian stock exchanges NSE (National Stock Exchange) and BSE (Bombay Stock Exchange) etc.

In the remaining article, I will describe how you can bring real time market prices in your spreadsheet. At some future post I will also cover the important topic of obtaining historical time series and how to use them in order to make qualified investment decisions.

Live Data without an external AddIn

All Excel versions starting with 2010 support the download of data from any website through a top menu item named From Web. In Excel 2016 is part of the ribbon item Get External Data and looks like that:

Clicking on From Web, a popup window is presented:

You must obviously overwrite the displayed URL with the one you are interested in, choose the preferred data section through the small orange arrow and click on Import to paste the chosen data into your spreadsheet.

Sadly several sites are conducive to your efforts! Trying, for example, to access data about the Google stock from the Yahoo Finance site through the URL https://finance.yahoo.com/quote/GOOG, several script errors will be generated. Worst still, a text output containing words such as “unauthorized”, “missing cookies” etc is finally pasted in Excel.

A few sites conform better. For example, the MarketWatch page at https://www.marketwatch.com/investing/stock/live looks in a browser window like that:

After trying out the described approach, the received data are pasted over 435 rows and up to 5 columns. The browser data shown above are reproduced at row 34 as seen below:

A very nice Excel feature is that you can set your preferences so that this download is repeated automatically as frequently as once per minute.

Provided that a suitable URL exists, which returns properly formatted text data, Excel allows you  to get these data directly in one single cell through the built-in formula =WEBSERVICE(“some-url-here”).

As an example, you can get the last traded price of the Google stock in IEX through the formula:  =WEBSERVICE("https://api.iextrading.com/1.0/stock/GOOG/quote/latestPrice").

Live Time Data through a  Programmatic Interface

While it is generally possible to write your own code that requests live data from these exchanges and displays the results in Excel, it makes more sense to use a free or commercial third party AddIn that is specialized in carrying out this type of task. One such AddIn is Deriscope, which not only supplies you with live feeds and historical data from several different providers, but also helps you price various types of derivatives and risk manage your portfolio.

Your first step should be to list all the tickers in which you are interested. For concreteness, pretend you would like to receive live feeds on the following:

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)

The image below shows these tickers entered in column A, beneath cell A1.

Cell A1 is reserved for entering the formula dsLiveStartEngine(“YF”;5;A2:A13) that starts an automatically recurring acquisition of data from Yahoo Finance, as declared by the provider code YF in the first argument. The number 5 in the second argument denotes the update frequency, while the third argument A2:A13 references the list of tickers.

You are not bound to Yahoo Finance. Currently Deriscope supports 6 different providers, each identified through a letter code as below:

Yahoo Finance (YF)

Investors Exchange (IEX)

World Trading Data (WTD)

Alpha Vantage (AV)

Barchart (BC)

TrueFX (TFX)

The formula dsLiveStartEngine(“YF”;5;A2:A13) only starts the engine, which stores the received feeds in some internal memory buffer. It does not display the feeds on the spreadsheet! For the latter, you need to apply the array formula {=dsLiveGetAsync("YF")}, on some vertical range consisting of 12 cells. In the image below, I do so on the range B2:B13.

The function {=dsLiveGetAsync("YF")}, is declared volatile and thus is recalculated in the same fashion as the built-in function NOW(). It is nevertheless possible to force its recalculation every time new feeds arrive by clicking on the Auto Refresh button located inside the Deriscope wizard. An additional Deriscope feature is that prices flash green if they go up and red if they go down as shown below:

You are not restricted in last traded prices only. You can also display all types of data made available by the live feeds provider. All you need to do, is adding a fourth argument inside the dsLiveStartEngine formula that references a row of fields entered anywhere in the spreadsheet. The list of field names supported by the given provider is reported in the little dropdown that appears when you select any ticker-containing cell, for example cell A2 containing the ticker MSFT, as shown below:

The next image shows the modified dsLiveStartEngine formula in cell A1 that now references the range of fields B1:D1 as well.

The feeds are still displayed by the same array formula {=dsLiveGetAsync("YF")}, which now applies to a wider 3-column range.

Above I showed you how to enter the various formulas by hand. In reality, you would never need to do that as Deriscope’s integrated wizard can generate all formulas required for any conceivable task. This is how the Deriscope wizard looks like:

The wizard has many features and visual controls because it assists the user not only with live feeds but also with the complex world of derivatives pricing and portfolio management.

You can access the live feeds functionality and indeed paste the exact same formulas we have seen so far, by clicking on the button with the tools symbol and navigating to the final menu item Asynchronous (extended) as shown below:

You may notice in the above screenshot the live feeds providers mentioned earlier.

The comparative characteristics of these providers are listed in the following table:

Deriscope supports concurrent live feeds from all these providers. For example, I can have three dsLiveStartEngine formulas that request feeds from three different sources. The next video shows a continuous loop of a 10-second recording of my spreadsheet when all these feeds keep coming with an update interval of 1 second.

Cell A1 ignites the engine with the TrueFX provider and requests bid/offer FX quotes on certain currency pairs.

Cell K1 ignites the engine with the Yahoo Finance provider and requests last traded prices of certain securities.

Cell A13 ignites the engine with the IEX provider and requests bid/offer price quotes on certain US stocks.