Get market data from Quandl and other data providers directly via cell formulas:
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
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!
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 v10.0. 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:
importxlwingsasxwimportquandlquandl.ApiConfig.api_key='MY_QUANDL_API_KEYfirstname.lastname@example.org@xw.ret(expand='table')# this makes it a dynamic arraydefQDH(ticker,start_date=None,end_date=None):returnquandl.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,
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.)
Like what you see?
xlwings is being used by some of the biggest companies across all industries to make their lives with Excel
easier, safer and testable. Become an xlwings expert by attending one of our webinars!
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: