How to fetch Market Data in Excel like a Pro

Get market data from Quandl and other data providers directly via cell formulas:

Overview

1. Bloomberg’s infamous BDH formula
2. Enter xlwings
3. Building QDH

Bloomberg’s infamous BDH formula

Tell a financial analyst to download market data into Excel, and the odds are that they will start typing the following formula in an Excel spreadsheet: =BDH("TICKER", "PX_LAST",...). This formula stands for “Bloomberg Data History” and will download the end-of-period prices of the indicated ticker directly into your spreadsheet - given that you have Bloomberg’s Excel addin connected to a Bloomberg terminal.

Now, the special thing about that formula is that it fills your sheet with an array of historical prices from a single cell - without being a proper Excel array formula! While this is slightly against the idea of how Excel is supposed to work, it’s very useful in practice and usually one of the first things you start missing when you change jobs and loose access to a Bloomberg terminal.

Enter xlwings

xlwings is a free and open-source Python package that allows you to ditch VBA in favor of Python and therefore allows you to achieve quite a few nice things quite easily. One of these nice things that we’ve added with the recent release of v0.10 is the possibility to create BDH-style formulas for any available data source and in exactly the way you like!

Building QDH

While it’s equally easy to use Yahoo! Finance or Google Finance data (e.g. by using the pandas-datareader package), for this sample, we’re going to use Quandl, a data platform for both free and paid data. Although Quandl offers an excellent Excel addin, it’s missing (you guessed it) a BDH-like formula. Let’s build it and call it “QDH”.

Step 1: Install Python with xlwings

While there are many ways to install Python and the required dependencies, we strongly recommend you to download and install Anaconda which already comes with everything we need in a single installer.

Just run conda upgrade xlwings from a command prompt to make sure you have at least version. Please follow the xlwings docs to install the xlwings addin.

Step 2: Create the QDH formula

Let’s start a new xlwings project by executing the following from the command prompt:

xlwings quickstart qdh

This will create a qdh directory with an Excel file qdh.xlsm and an empty Python source file qdh.py. In the Python source file we’re now going to write the following function:

import xlwings as xw
import quandl

quandl.ApiConfig.api_key = 'MY_QUANDL_API_KEY'

@xw.func
@xw.ret(expand='table')  # this makes it a dynamic array
def QDH(ticker, start_date=None, end_date=None):
    return quandl.get(ticker, start_date=start_date, end_date=end_date)

Note: You don’t need to provide a Quandl API key (comes with a free account), but the number of calls will be limited, see here.

That’s all we need for the most basic version of QDH! The magic happens in the return decorator where we tell the formula to autoexpand the data into a dynamic array.

Switch over to Excel, import the function via the xlwings addin, type =BDH("WIKI/AAPL", "2016-01-01") into a cell and hit enter. You will be presented with the end-of-day information for Apple since the beginning of 2016 (this corresponds to the animated gif at the top of this article.)

We can make this formula a bit more useful and robust by changing it like this:

import xlwings as xw
import quandl
import datetime as dt

quandl.ApiConfig.api_key = 'MY_QUANDL_API_KEY'

@xw.func
@xw.ret(expand='table')
def QDH(ticker, column, start_date=None, end_date=None):
    if isinstance(start_date, dt.datetime):
        start_date = start_date.strftime('%Y-%m-%d')
    if isinstance(end_date, dt.datetime):
        end_date = end_date.strftime('%Y-%m-%d')
    return quandl.get('{0}.{1}'.format(ticker, column),
                      start_date=start_date, end_date=end_date)

Now we can link to date-formatted cells and also specify which column we would like to fetch. Following the setup of the animated GIF, type the following into cell A4: =QDH(A3,11,$B$1,$D$1). Here, 11 is the 11th column from before, i.e. the Adj. Close: