Download data with the Formula Builder

  1. Click Get Data to access the Formula Builder dialog box.
  2. Database: Select the database you would like to download from or search with a database code. (For instance, the database code for Mergent Global Fundamentals is "MF1".)
  3. Data: Choose the table you would like to download.
  4. Columns: Select the columns you wish to include (by default, all columns will be downloaded).
  5. Filter: The dialog box will provide the relevant filters dynamically.
  6. Placement: Select the cell where you would like to place the data.
  7. Click Insert.

📘

NOTE:

If a valid API key is not used, some tables will default to returning sample data. If you are not receiving all the expected data, please double check your API key.

Download table data with the QTABLE formula

In addition to the Formula Builder, the Excel add-in allows users to download tables data with the QTABLE formula. Users can directly type a formula into a cell to download data.

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
Table codes can be found on each database page on our website.


Example: The table code for Zacks Fundamentals Condensed (“ZACKS/FC”) can be found on data.nasdaq.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 table 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.


Examples:
comp_name retrieves only the data found in the column comp_name.


A1:E1 retrieves the columns specified by these referenced cells.
Filter rows (optional)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.


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


Example: “ticker”, “per_type”, “per_end_date” are possible filters for the ZACKS/FC table.

Composition of a QTABLE Formula

The following QTABLE formula retrieves the complete history of revenue per share for Nokia from the Mergent Global Fundamentals Data (MF1) table:

=QTABLE("MER/F1",{"reportdate","amount"},"mapcode","-5370","compnumber","39102","reporttype","A")

📘

NOTE:

Only columns designated as “filterable” in the table's documentation page can be used as criteria to filter rows. For a list of the filterable columns for the MF1 table, click here.

We can break down this request as follows:

FORMULA COMPONENTEXPLANATION
=QTABLE("MER/F1",This portion of the call queries the MER/F1 table.
{"reportdate","amount"},This parameter will retrieve data only for the columns reportdate and amount. If this portion is left blank then by default data for all columns will be returned.
“mapcode”,”-5370”This filter removes everything except for where the mapcode = -5370 (this is the identifier used by Mergent for revenue per share).
compnumber","39102",This filter removes everything except for the rows where compnumber=39102 (39102 = Nokia).
"reporttype","A")This filter removes everything except for the rows showing records for the "annual" report type (A = annual).

Using Filter Operators in the QTABLE Formula

The following are optional filter operators, which can be appended to all filters in your QTABLE formula, regardless of the table. The filter operators will further modify your results.

FilterDescription
.gtModifies the parameter to return values greater than the requested value
.ltModifies the parameter to return values less than the requested value
.gteModifies the parameter to return values greater than or equal to the requested value
.lteModifies the parameter to return values less than or equal to the requested value

Example 1: Query data after a specific date

The following QTABLE formula retrieves the revenue per share for Nokia where reportdate is greater than 2014-01-01 . Note the filter reportdate.gt where the operator .gt (greater than) is appended to reportdate.

=QTABLE("MER/F1",{"reportdate","amount"},"mapcode","-5370","compnumber","39102","reporttype","A","reportdate.gt","2014-01-01")

Example 2: Query data for a specific date range

The following QTABLE formula retrieves the revenue per share for Nokia where reportdate is greater than or equal to 2008-01-01 and less than or equal to 2018-01-01. Note the filter reportdate.gte where the operator .gte (greater than or equal to) is appended to reportdate and note the filter reportdate.lte where the operator .lte (less than or equal to) is appended to reportdate.

=QTABLE("MER/F1",{"reportdate","amount"},"mapcode","-5370","compnumber","39102","reporttype","A","reportdate.gte","2008-01-01","reportdate.lte","2018-01-01")

Reference Cells in the Formula

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

Example 1: Query multiple columns in tables

689

This example queries and downloads four columns from the ZACKS/FC tables. Enter the Datatable Code and 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 2: Query multiple columns with filters in tables

778

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 Datatable Code and 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.

Useful Links

For other useful tips on Excel add-in, please visit our YouTube channel and Help Center.