Quandl: A Step-by-Step Guide

What is Quandl?

Quandl is a platform that provides its users with economic, financial and alternative datasets.

Users can download free data, buy paid data or sell data to Quandl.

In this guide, we will cover how to extract data using Excel and Python (separately).

What is Quandl’s Data Structure?

Quandl’s data comes in two formats: the first one being Time-series and the second one being Tables.

Have in mind that some datasets can be shown in both formats.

Time-series

Time-series data refer to data taken over a period of time.

Time-series are commonly displayed using line graphs, where the X-axis represents dates and the Y-axis represents other numeric observations.

The Time-series format only contains numerical data which is indexed by one date field. This means that the data is sorted by dates. This allows users to retrieve the entire time-series or a particular part of it, also called a “slice”.

Most Quandl datasets are stored in the Time-series format as financial data mostly consist of dates and observations which fit the format nicely.

Table format

Tables don’t only contain numerical values.

They can contain various unsorted data types such as strings, numbers, dates, etc. They can also be filtered into different fields.

Quandl ID code

Each feed of data has a short ID, also known as a Quandl code.

For example:

• Federal Reserve Economic Data (time-series): FRED
• Core US Fundamentals (tables): SF1

Time-series data such as FRED is built-up of a large number of individual time-series where each of them has its own Quandl code appended to the main Quandl code, for example: US civilian unemployment rate:  FRED/UNRATE.

On the other hand, Tables data such as SF1 contain one or more tables where each of them has its own Quandl code, for example:  The Quandl code for the Core US Fundamentals table is SHARADAR/SF1.

When it comes to Data Analysis tools, Quandl directly supports API, R, Python, Excel, Ruby and more.

The Pros:

• Quandl offers an enormous collection of data (over 20 million datasets).
• All datasets are available for instantaneous download in any preferred format.
• All datasets on Quandl are available through the same API, irrespective of who originally published the data or in what format.
• Data is transparent.
• Datasets are easy to find and clean.
• Some parts of Quandl is free and open for everyone.
• New data is added weekly.
• Quandl can be used in many programs (Excel, Python, R, Ruby, MATLAB…)
• You can use Quandl to sell your data.

The Cons:

• Many of the more exotic datasets are not free.
• They offer limited amounts of help when constructing analyses or discovering information.
• Not too beginner friendly.
• They don’t have real-time or delayed stock price data.
• They don’t have a master security list.

Is Quandl Free?

Quandl offers both free and premium products.

The Quandl API is free to use and grants access to all free datasets.

Quandl users have to pay to access Quandl’s premium data products.

What are the differences between Free and Premium datasets?

As the devil is in the details, their free products come from various “credible and established” sources such as central banks, government agencies, organizations, etc.

On the other hand, their premium products come from data providers such as: AlgoSeek, Applied Research, Barchart, Brave New Coin, and CryptoCompare etc.

What are the types of Free Data in Quandl?

When it comes to free data, Quandl offers you a massive range from Prices & Volume, Fundamentals, Sentiment, Derived Metrics, National Statistics, and Other.

Let’s mention some of the free datasets:

• Wiki Continuous Futures – This dataset is built on top of raw data from ICE, CME, LIFFE, etc, and is curated by the Quandl community.
• Zillow Real Estate Data – This dataset is the leading real estate and rental marketplace.
• Federal Reserve Economic Data – This dataset includes things such as growth, employment, inflation, labor, manufacturing and many more US economic data.

What are the types of Paid Data in Quandl?

When it comes to premium data, Quandl offers datasets related to Estimates, Corporate Actions, Alternative Data, Technical Analysis and more.

Let’s mention some of the premium datasets:

• Zacks Sales Estimates – This dataset has a consensus of sale estimates for over 4k US and Canadian listed companies.
• Nasdaq Dorsey Technical Analysis Data – This dataset is made up of 50+ technical indicators and chart information for over 49k global equities, mutual funds and ETFs.
• Sharadar Core US Equities and Fund Prices – This dataset is self-explanatory.

How to create an account in Quandl?

Having a Quandl account is important as it allows you to:

• Access their API, libraries and tools.
• Download free and/or premium data in any format.
• Access their export and visualization tools.
• View relevant information about pricing and licensing for their premium data.

Let’s get you started with your Quandl account. Have in mind that it is free and doesn’t require any credit card information.

Let’s go over the steps:

How to access Quandl Data?

The data can be accessed using Excel, Python, R, MATLAB, Ruby and more.

We will cover the Excel and Python methods in this article.

How to extract Quandl Data in Excel?

When it comes to Excel, you can pull Quandl datasets in a matter of minutes without any programming skills required.

It’s time for us to see Quandl in action. But first, we need to set up the Quandl Plugin for Excel.

Let’s do the steps together:

Example of Quandl usage in Excel

1. Let’s get our Database. Press the “Get Data” button inside of your Quandl toolbar.
2. After your API key gets verified (remember that?), we’ll select our Data. For this example, I’ll pick the European Stock Prices for FSE Frankfurt Stock Exchange.

3. Click the blue “Next” button and select your preferred stock. I’ll use the Zooplus (ZO1_X). Click “Next”.

4. After that, a new window will open that will ask you to specify what Columns will your dataset have. I selected the Date, Open, High, Low and Close columns. Click “Next”.

5. Now, we’ll select the filters that we want for our data. As I
don’t want to make the steps too complicated, I won’t apply any filters. 

6. After clicking the “Next” button, you are given a Placement option aka where do you want your data to be placed at in your Excel sheet. When you specify your Excel Sheet and Cell, click the “Insert” button.

7. Voilà! Your data is ready! If it didn’t appear for you, don’t panic! Just press the “Refresh Sheet” button that is found in your Quandl toolbar.

For the next example, the steps are the same but the dataset will be different. I’ll show you a Corporate Annual Report data for the Facebook Inc. dataset:

The next example will be different. We’ll use the Quandl “Formulas” option to code what we need and want.

Let’s follow the steps:

1. Let’s suppose that we want to download the daily closing prices for two assets in the last 3 days (as shown in the picture). First we need to specify that we want the closing prices by writing the following string (=’your cell’&”/CLOSE”) in our formula Excel section. Be sure to do this for both assets. You can simply drag the cell with the written code to the other cell.

2. Now, let’s add our Start and End dates. For this I’ll use the “today()” formula in Excel. For the Start date we will write “today()-3” which means past 3 days and for the End date we will just use “today()”.

3. This step requires us to use the “qseries” formula which, in our example, looks like this “=QSERIES(D2:E2,B1:B2)” As you can see, the D2:E2 specifies our asset filter and the B1:B2 part specifies our Start and End date. After the formula is written, refresh the sheet as we did in our past example, and you’re done.

If you want to change stuff up, and look at the data for the past 3 years, in the Start date field write “=today()-365*3”. Be sure to play with Quandl so you get a hang of it!

How to extract Quandl data in Python?

For this part, I’ll be using the Jupyter Notebook from the Anaconda application for Data Science.

Example 1: WTI Crude Oil prices

For this example, we want to get the WTI Crude Oil price from the United States department of Energy:

1. We need to import all the packages we’re going to use. For this example, I’ll import Quandl, NumPy and Pandas. Note that you need to have NumPy and Pandas installed. They come pre-installed with the Anaconda program and the codes go like this: “pip install NumPy” and “pip install Pandas”.

2. After that, we need to activate our API key. Use the following command: quandl.ApiConfig.api_key = “YOUR_API_KEY”

3. The next step is to use the following command “mydata = quandl.get(“EIA/PET_RWTC_D”)”.

It is of utmost importance to know the Quandl code (“EIA/PET_RWTC_D”) of each dataset you want to use.

Example 2: US GDP Data

For our next example, let’s download a piece of macroeconomic data with specific dates and save it as a .csv file.

Downloading data from a specific date range in Quandl

1. In order to set specific Start and End dates, we shall write the following: mydata = quandl.get(“FRED/GDP”, start_date=”2019-12-31″, end_date=”2020-9-26″)

2. This is where Pandas come into play, as we want to save our extracted data to a .csv aka Excel format.

We will convert our data into a Pandas DataFrame using the following command: “df = pd.DataFrame(mydata, columns= [‘Date’,’Value’])”

3. After that, we will export our data and give the .csv file a name: “df.to_csv (‘Name.csv’)”

Example 3: Facebook Annual Report Data

Let’s download a Corporate Annual Report for Facebook. The steps are the same as in our first Python example, but with a little twist. Our Quandl code for this one is “WIKI/FB”.

We specify the years we want to go through and get the data we’ll need to preprocess it. This means that we need to extract only the annual part of it.

For this, we shall use the following command: mydata = quandl.get(“WIKI/FB”, collapse=”annual”)

Now our data looks like this:

Now you are all equipped to go on a Quandl coding journey in Python!

“May the code be with you!” – Obi Wan Kenobi

Quandl in other programming languages

Quandl is also equipped for other programming languages like R, MATLAB, Ruby and others.

In this article, we won’t go into them as it would make it too long. But R, Ruby and MATLAB enthusiasts worry not, as I’ll provide you with useful links where you can learn how to use Quandl in your programs!

What are the limitations for Quandl downloads?

Quandl needs to limit their users when it comes to downloading so the API doesn’t get over flooded and the whole service crashing down.

Anonymous users are limited to 20 calls per 10 minutes and 50 calls per day; while authenticated users have a limit of 300 calls per 10 seconds, 2k calls per 10 minutes and 50k calls per day.

When it comes to the free authenticated users, they have a concurrency limit of one which means that they can only make one call at a time and have an additional call waiting in the queue.

As for the premium users, they have a limit of 5k calls per 10 minutes and a limit of 720k calls per day. They are also limited to 10 bulk downloads per hour.

What are some Quandl alternatives?

When it comes to price and corporate fundamentals, there are the following: Yahoo finance, IEX, Alpha Vantage, Polygon, TradingView…

More on Quandl’s background

According to Quandl, they have over 400,000 users. These ranges from the world’s top hedge funds to investment banks and various asset managers.

Quandl’s specialization is in the field of data that gets collected over time and is categorized as economic, social and financial data – this includes things like: stock prices, crime rates, unemployment rates, various populations, etc.

They manage to do this by indexing millions of numerical datasets from around the internet. For example, if you click on a specific dataset that is indexed in Quandl, it will go to its original dataset source, extract the newest version of it, clean it, and give it back to you in whatever form you want.

Quandl is striving for the democratization of data and making it easier to find and use for all its users. Their goal is to provide their service to various types of users and professions.

Can you believe that they even offer data pertained to some basic sport statistics?!

Ok Igor, that all sounds sweet, but how does Quandl acquire data?

Well, Quandl acquires its data by having various human curators that point their bots at specific datasets. Recently, they’ve been looking for more curators that are similar to Wikipedia’s volunteer editors.

You can also become a vendor and sell your data to Quandl. For this, the technical requirements aren’t strict but you need to create, document and maintain the data yourself with a “professional standard of quality”.

The good thing is that the listing of your data to Quandl doesn’t come with any fees. You also get support from the Quandl team for free and your data is viewed as your intellectual property.

As a Quandl vendor, you have full transparency of who’s buying/accessing your data. You are also provided with a vendor dashboard that shows you all the sales, subscribers, revenues and other information in real-time.

Download Code