TABLES
Download data with the Quandl Formula Builder
- Click Get Data to access the Quandl Formula Builder dialog box.
- 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".)
- Data: Choose the table you would like to download.
- Columns: Select the columns you wish to include (by default, all columns will be downloaded).
- Filter: The dialog box will provide the relevant filters dynamically.
- Placement: Select the cell where you would like to place the data.
- 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 Quandl QTABLE formula
In addition to the Quandl Formula Builder, the Quandl 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 CODETable 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 quandl.com/databases/ZFA. |
| Filter columns (optional) | COLUMN NAMEYou 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. See the Reference cells in the Quandl formula section below for details. 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 COMPONENT | EXPLANATION |
|---|---|
=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.
| Filter | Description |
|---|---|
| .gt | Modifies the parameter to return values greater than the requested value |
| .lt | Modifies the parameter to return values less than the requested value |
| .gte | Modifies the parameter to return values greater than or equal to the requested value |
| .lte | Modifies 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 Quandl Formula
Users are encouraged to directly reference cells that contain the desired specification when using the Quandl formula. The following two examples are meant to get you familiar with referencing cells in the Quandl Formula.
Example 1: Query multiple columns in tables
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
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 Quandl's Excel add-in, please visit our YouTube channel and Help Center.
Updated about 4 years ago