How do the Excel Add-in formula functions work?

In addition to the Formula Builder, the Quandl Excel Add-in supports two formula functions to enable users to download data: QSERIES and QTABLE. Users can directly type a formula, or a user-defined function (UDF), into a cell to download data.

The QSERIES function is used for time-series datasets and the QTABLE function is used for datatable datasets. To learn the difference between a time-series and datatable dataset, please visit the  Data Organization section of our API documentation page.

QSERIES: For Time-Series Datasets

The structure of the formula is given below.

=QSERIES(Quandl code, date range, frequency, sort, transformation, limit, headers, dates, transpose)

The Quandl code is the only required input to the formula. The other parts of the formula are optional inputs.

Description
Quandl code (required) “DATASET CODE” or “DATASET CODE/COLUMN”

Each dataset code can be found on our website. You can specify the column by the name or number. If a column is not specified, all columns from the dataset will be retrieved. 

{“DATASET CODE/COLUMN”, “DATASET CODE/COLUMN”}

To specify multiple columns in a dataset, use curly brackets “{}” and indicate the columns you wish to query in quotation marks. 

Users are encouraged to directly reference cells that contain Quandl codes and columns. See the “Referencing cells in the Quandl formula” section below for details.

Examples:
  • “WIKI/AAPL” retrieves all columns from this dataset
  • “WIKI/AAPL/CLOSE” and “WIKI/APPL/4” retrieves only closing prices (column 4) of this dataset
  • {“WIKI/AAPL/CLOSE”, “WIKI/FB/CLOSE”} retrieves closing prices for both AAPL and FB tickers
  • A1:D1 retrieves datasets specified in these referenced cells
Date Range
(optional)
Single date:  “yyyy-mm-dd”
Period range:  ({“yyyy-mm-dd”, “yyyy-mm-dd”})
({“Start Date”, “End Date”})
This portion of the formula allows you to pick a single date or a period range. Users can also directly reference cells that contain the dates.

If you use the TODAY Excel formula to indicate the dates and reference it directly, the dates will update automatically. To learn more about referencing cells, see the “Referencing cells in the Quandl formula” section below for details.

Examples:
  • “2014-09-01” retrieves data for September 1, 2014
  • {“2014-09-01”,”2014-09-30”} retrieves data for the month of September in 2014
  • A2:A3 retrieves the dataset in the period range specified by these referenced cells (A2 as start date, A3 as end date)
Frequency
(optional)
“daily”, “weekly”, “monthly”, “quarterly”, or “annual”
By specifying the frequency, you will retrieve data in those frequencies. Otherwise, the data will be left at its default frequency.
Sort
(optional)
“asc” or “desc”
You can sort the data in ascending or descending order by specifying this part of the formula. Otherwise, the data will be left at its default which is in descending order.
Transformation
(optional)
“diff”, “rdiff”, “rdiff_from”, “cumul”, or “normalize”
  • “diff”: row-on-row change
  • “rdiff”: row-on-row percentage change
  • “rdiff_from”: latest value as percentage increment
  • “cumul”: cumulative sum
  • “normalize”: scale series to start at 100
Limit
(optional)
#
You can limit the number of observations you want to retrieve by entering an integer.
Headers
(optional)
TRUE or FALSE
If you do not wish to include headers in the data, specify FALSE. Otherwise, the headers will show up by default.
Dates
(optional)
TRUE or FALSE
If you do not wish to include the date column in the data, specify FALSE. Otherwise, the date column will show up by default.
Transpose
(optional)
TRUE or FALSE
By default, the data is downloaded with dates going down rows. If you would like to transpose the data to have dates across columns, specify TRUE.

Quick Start Examples

=QSERIES(“WIKI/AAPL”)
Downloads all historical data for WIKI/AAPL
=QSERIES(“WIKI/AAPL/OPEN”,,,,“rdiff”)
Downloads the percentage change of opening prices for all historical data in WIKI/AAPL
=QSERIES(“WIKI/AAPL”, “2016-08-06”)
Downloads data only for August 6, 2016 from WIKI/AAPL
=QSERIES(“WIKI/AAPL”,, “monthly”,,,30)
Downloads monthly data for the last 30 observations in WIKI/AAPL

QTABLE: For Datatable Datasets

The structure of the formula is given below. Note that you can have multiple filters as part of a single query.

=QTABLE(datatable code, columns, filter name, filter value, filter name, filter value)
Description
Datatable code (required)
“DATATABLE CODE”

Each datatable code can be found on each database page on our website.

Example: “ZACKS/FC” can be found on  quandl.com/databases/ZFA

Filter columns (optional)
“COLUMN NAME”

You can specify the column you wish to query here. If a column is not specified, all columns from the datatable will be retrieved.

{“COLUMN NAME”, “COLUMN NAME”}

To specify multiple columns, use curly brackets “{}” and indicate the columns you wish to query in quotation marks. Users are encouraged to directly reference cells that contain the column names. See the “Referencing cells in the Quandl formula” section below for details.

Examples:

  • “comp_name” retrieves only the data found in this column, “comp_name”
  • A1:E1 retrieves the columns specified by these referenced cells
Filter rows
By specifying one or more columns to act as the filter name, you can download your desired rows. If the value in a given column matches the specified filter value, the row containing that value is returned.

Note that only columns designed as “filterable” in the database’s documentation page can be used as criteria to filter rows.

Example:  “isin”, “per_date”, “per_type” are possible filters for the IEE (or INQ/EE) data table.

Quick Start Examples

=QTABLE(“ZACKS/FC”)
Downloads all data inthe ZACKS/FC datatable
=QTABLE(“ZACKS/FC”,“comp_name”)
Downloads all data in the comp_name column in the ZACKS/FC datatable
=QTABLE(“ZACKS/FC”,{“comp_name”, “tot_asset”})
Downloads all data in the comp_name and tot_asset column in the ZACKS/FC datatable
=QTABLE(“ZACKS/FC”,, “ticker”, “AAPL”)
Downloads all rows that have “AAPL” value in the ticker column from the ZACKS/FC datatable

Referencing Cells in the Quandl Formula

Users are encouraged to directly reference cells that contain the desired specification when using the Quandl formula. The following four examples are meant to get you familiar with referencing cells in the Quandl Formula.

Example 1: Querying multiple datasets for time-series databases (QSERIES)

Image

This example queries and downloads the closing prices for the three tickers (AAPL, FB, TCB). To do this, enter the Quandl codes for the three tickers in the worksheet cells (e.g. B1, C1, D1). Then, enter the QSERIES formula in cell A2 and reference the tickers by clicking and dragging your mouse over them.

Example 2: Querying multiple datasets in a period range for time-series databases (QSERIES)

Image

This example queries and downloads the closing prices for the three tickers (AAPL, FB, TCB) in the month of September. To do this, enter the Quandl codes for the three tickers in the worksheet (e.g. D1, E1, F1 cells). Enter the start and end dates in the worksheet (e.g. B1, B2 cells). Then, use the QSERIES formula in cell C2 and reference the tickers and start/end dates.

Example 3: Querying multiple columns for datatable datasets (QTABLE)

Image

This example queries and downloads four columns from the ZACKS/FC datatable. Enter the column names you wish to query in the worksheet (e.g. A1, A2, A3, A4 cells). Use the QTABLE formula and reference these cells (e.g. A1:A4).

Example 4: Querying multiple columns with filters for QTABLE

Image

This example queries and downloads four columns, filtering for only the ‘AAPL’ value in the ticker column and for the ‘Q’ value in the ‘per type’ column. To do this, enter the column names you wish to query in the worksheet (e.g. A1, A2, A3, A4 cells). Then, enter the filter names and filter values you wish to filter by. Use the QTABLE formula and reference these cells.

Tutorial Video

Watch this short tutorial on how to download multiple datasets using the QSERIES formula:

Still need help? Contact Us Contact Us