How To Get Data From Quandl With Python Or Excel

For systematic traders, your choice of data source is important. There is no shortage of options ranging from free end of day data to subscription only real time feeds. System timeframes, markets and budget all play an important role in choosing providers.

In this post we are going to take a look at a relatively recent player in the area, Quandl.com. We’ll look at getting set up and how to get data using python or Excel. Some data in Quandl is paid for but there is also a vast amount of data that is free.

Who are Quandl?

Founded in 2013, Quandl has become a respected data provider. They now boast over 250,000 users from individuals to large hedge funds and investment banks. Quandl provides a wide ranging dataset across two main categories:

• • Core Financial Data: coverage of securities and market data across all asset classes

• Alternative Data: a vast collection of data sources from outside of the normal or regulatory data companies or markets

Quandl covers much of the core financial data you are used to and provides both free and premium options. As you’d expect most of the more popular sources require subscription.

It is in the second class of data where Quandl are seeing the most growth and they are now in fact seen as leading providers of alternative data. Lets take a closer look at what exactly alternative data is.

What is alternative data?

Alternative data is any form of data that can be used by market participants to evaluate a company or market where that data is from outside the normal data sources. Normal data sources can include financial statements, regulatory reports, press releases, corporate meetings and presentations etc.

With exponential growth in both our potential to generate data points and ability to consume and understand it, the rise of ‘Big Data’ and ‘Data Science’ has been meteoric in recent years.

The emergence of these fields has directly led to the explosion in alternative data sources in recent years. A fact that can be illustrated by the growth in alternative data providers as published by https://alternativedata.org:

Examples of the types of data that coupled with data science and big data techniques can be used to inform investment decisions are:

• • credit and debit card transactions

• • satellite imagery and gps

• • air/rail/sea traffic

• • social media

• • internet traffic and trends

• new car registrations

In a crowded and efficient investment landscape the attraction of these none traditional data sources as a possible ‘edge’ is inevitable.

How Quandl helps you invest & trade

Quandl provides a wide range of data and importantly a lot of flexibility in how you consume that data. Client libraries for data access in Ruby, R, Excel and Python are available, as well as an open and well documented API that can be integrated with any language of your choice.

For the purposes of this tutorial we will look at how Quandl’s Excel and Python clients can be used to obtain data.

Quandl Signup

The first step to begin to investigate Quandl is to sign up for an account here:

• • Enter your name

• Select Personal (for the purposes of this demo)

• • Enter your email address

• Select a reason for using the data

• • Enter a secure password

• • Press ‘Create Account’

• You will receive an email verification that you must select to activate the account

On log in to your new Quandl account, you will see your Dashboard view. The dashboard provides you with easy access to all available data whether free or paid.

Quandl APIs

Now that we have a Quandl account we will look at how we can use the platform to provide trading data. The first step in that process is to generate the appropriate API key.

Quandl API Key

To work with any version of the Quandl APIs you must first ensure that you have a Quandl API client key. Go to the Account Settings page in your Quandl account:

Your API key which is a long string of random characters will is now displayed. At any point in the future you can regenerate the key if required here.

Having now obtained your Quandl API key we will investigate the Excel and Python API offerings.

Excel API

Quandl provides an excellent Excel Add-on which automatically integrates with any supported version of Windows Excel. Currently supported Excel versions are 2010, 2013 & 2016.

Quandl Excel Add-On Configuration

The add on is available at https://www.quandl.com/tools/excel. To install run the downloaded installer file and follow the instructions. After installation the next time you open Excel you will be asked permission to install the Excel customisation:

• Press ‘Install’ and continue

In excel create a new blank worksheet and you should see a ‘Quandl’ menu item in Excel. We want to select this menu item and enter our API Key to complete the configuration:

Using the Formula Builder to obtain historic data

1. Open the Quandl Formula Builder within Excel:

2. Select a data set to download. We are going to use the freely available CME futures data:

3. Choose the instruments/tickers for which you want to download data. For simplicity we are choosing only one instrument a gold options contract:

4. Choose the data points for the time series you want to download:

5. In the next tab you have the option to filter the data to be downloaded. You can chooses the date range to pull data for, the frequency of the series (Daily/Weekly/Monthly etc) and sort or limit the data returned to your needs.

6. Finally we confirm the data to be downloaded and where to place it in our Excel file.

7. Your data is now available in the Excel sheet. At any time you can refresh the data using the built in tools.

Now that you have the data available in Excel you can do all manner of things with it such as data analysis or loading it into a backtesting software.

Python API

The second approach for data integration we will look at is via the python client provided by Quandl. This method takes a little more work but can provide much more flexibility when needed.

Setting up your python environment

We need to install python on your machine before we can start working with the Quandl API. Thankfully installing python is simple and if you are using MacOS it will already be available.

Follow the steps below to install Python 2 on a windows machine.

1. 1. Download the latest python 2 installer from https://www.python.org/downloads/windows/. NB: You have the option of installing python 2 or 3 currently — we’ll use python 2.

1. Run the downloaded installer and select “Install for all users” when prompted:

3. Select the directory to install python to, the default is fine.

4. On the Customise Python Screen select“Add python.exe to Path” and select “Will be installed on local hard drive”:

5. From this point on you can select the defaults in the installation wizard and click through to completion. When the install is complete you can open a command prompt and run “python -V” to confirm python 2 is installed and available:

Installing the Quandl Python Library

After installing python we’ll need to make the Quandl library available before trying to get some data. Recent versions of python (including the latest 2.7.15) come with a tool called pip that makes installation very simple.

Simply run “pip install quandl” on your command line and after a few moments the installation will be complete:

Our first python script

Lets test out the Quandl python library by running a simple python script that will get some data from Quandl.

1. Create a directory where we will store files:

2. Open a command prompt and type “python” to access the python environment:

3. In the python environment we can try out some simple commands to check out the Quandl API. Firstly we need to make the Quandl API available by importing it and then just like with the Excel API ensure our API key is set. Type the following, ensuring to replace the API key with your own:

import quandl
quandl.ApiConfig.api_key = ‘sufaEEBSq6gtFfX2SyXX’

4. Not much has happened yet so lets try to get some data as the Quandl API should now be available to use. Type the following two commands to get all the timeseries data for the Gold Contract we used in the Excel example.

data = quandl.get(‘CME/GCJ2020`)
print(data)

5. On enter you should see the retrieved data listed

So we have now successfully accessed the Quandl data provider using the python client but is not yet very useful. Lets write out the data retrieved to a csv file so it can be used to import to another trading system. Type:

data.to_csv(‘c:\quandl\GCJ2020.csv’)

Press ‘Enter’ and the csv file with your data will be saved to c:\quandl\GCJ2020.csv:

NB: you can exit the python command line interface by typing “quit()” and pressing enter.

Once you have the data exported it is yours for analysis or you can open it up in the backtesting platform of your choice.

Creating a repeatable script for downloading data

In the previous steps we successfully accessed Quandl via python, downloaded some data and stored it in a csv file. We can externalise all that logic to a python file so that we can simply run the file when we want rather than having to type all the commands again to the python command line.

1. 1. Create a python script file called c:\quandl\download_data.py

1. 1. Open the file with whichever editor you are comfortable with

1. In the file simple type in the previous commands

4. Run the script via the command line by typing the command below in the same directory as the file:

python download_data.py

5. List the contents of the directory to see that the data has been downloaded

The script could be modified easily to download a series of tickers and store them in individual files as shown below:

You can run the script to download the multiple data files and check they are created as so:

Final Thoughts

After following through the steps you now have a good understanding of the service offered by Quandl. The company continues to grow its access to datasets, particularly alternative data, that can support your trading and investing.

Quandl’s Excel Add-on and formula offering is a simple introduction to data access platforms. We have shown how you can use Excel to retrieve large or small datasets easily from Quandl’s free and premium collections.

If and when you are ready to become a little more advanced in how you retrieve and use datasets the Quandl python API becomes a great option. Python comes with first-class native support for working with data, numbers and statistics. Additionally, many of the most commonly used financial and quant libraries are python based.

However, you decide to source and manage your trading data I hope this overview of Quandl will be of benefit to you.