Market Data in Excel from Alpha Vantage

Alpha Vantage is a provider of live and historical prices of global stocks, indices and forex rates (both fiat and crypto) as well as time series of statistical indicators. 

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.

Live feeds are based on one-minute intraday series. Historical data include daily close, open, high, low and volume quotes and go back at least 20 years. Alpha Vantage provide a so called "fremium" service, whereby anyone can receive for free up to 5 quotes per minute and have the option of upgrading to a higher limit service through a subscription that starts at $20 per month.

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 AV in a manner that minimizes the required number of server requests so that your applicable free or purchased AV 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 AV.

Since AV access requires a special code (api key) that can be obtained for free by registering your email address at the AV 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:  

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 AV. 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 AV 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 &HistAV_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:  

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: 

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

Getting Intraday Historical Data​ 

You may request a live intraday series of data with one-minute sampling interval by changing the value TIME_SERIES_DAILY associated with the key Function Name= to TIME_SERIES_INTRADAY. You may type this new value in or select it from the dropdown list shown below: 

If you do that, the formula in cell K2 returns an error with the respective message displayed on the wizard when the cell is selected, as below: 

Following the wizard's instruction, you can now add the key Interval= with the associated value of 1min to get your one-minute intraday data as shown below: 

Getting Series of Statistical Indicators 

The cutting edge of Alpha Vantage is its time series of 50+ statistical indicators, such as SMA (Simple Moving Average), MACD (Moving Average Convergence / Divergence), APO (Absolute Price Oscillator) and many others. You may choose any of them by setting the respective value for the key Function Name= as described above. But by far the easiest means is by asking the wizard to generate the HistAV object so that the various additional required and optional inputs will be automatically generated, as the next video demonstrates: 

Below is how the wizard displays the received SMA data: 

Merging Different Series into One​ 

Deriscope allows you to setup a composite request that references several single requests and returns the data combined in the same table with one common date column on the left. You achieve this by creating an object of type HistComp as below: 

Then feeding the created handle name into the formula dsLive() produces the final result shown below: 

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

Cell A1 contains the formula =dsLiveStartEngine("AV";60;A2:A6;B1:C1) that starts the live engine, effectively a repetitive request of live data from the AV server. The number 60 indicates the number of seconds between successive requests. Deriscope sets it by default to 60 seconds because there exist 5 tickers and the free version of AV cannot process more than 5 api requests per minute, i.e. one api request per 13 seconds.

The function returns the text &LiveAV, 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("AV")} pasted over the range B2:C6, 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.

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