Export dashboard tables

A table in an Exabel dashboard can be exported as a data file with the Export dashboard table method (POST /v1/export/dashboardTable) in the Export API. The table is exported as the dashboard computed it: every column keeps the definition, formatting and time period it has in the app, and nothing is re-evaluated on export.

This is the structured alternative to the SQL query the deprecated Export data method takes. Most of what the SQL form can filter on, it can filter on — the difference is that the request is JSON, columns are named rather than quoted into a string, and a column can be addressed by an identifier that survives being renamed. Three things are reachable only here: excluding a tag, requiring all of one set of tags while also requiring any of another, and a relative-date bound that includes the day it names.

📘

Only table widgets are exported this way

Chart widgets are rendered as images with Export charts instead. Financial model widgets are only reachable through the SQL form.

Finding the table

Every request names one table widget by its resource name, dashboards/1234/widgets/2, in the table field. The dashboard id can be copied from the dashboard's URL in the web app; the widget id is found with the Management API.

The lookup methods below are all in the Management API, which is a separate host (https://management.api.exabel.com) from the Export API (https://export.api.exabel.com) but uses the same credentials.

📘

API key or access token?

A customer-level API key operates as the service account user, and therefore only sees folders and library objects shared with the entire customer. To reach dashboards that are private to you, or shared with a smaller group, use a user-level API access token. See Management API Introduction.

Search for the dashboard with Search for folder items, limiting the result to dashboards with itemType=DASHBOARD:

curl --request GET \
     --url 'https://management.api.exabel.com/v1/folders/-/items:search?query=weekly%20review&itemType=DASHBOARD' \
     --header 'accept: application/json' \
     --header 'x-api-key: xxx'

The name field of a dashboard item is dashboards/1234. Pass that id to List dashboard widgets, limiting the result to tables with widgetType=WIDGET_TYPE_SIGNALS_TABLE:

curl --request GET \
     --url 'https://management.api.exabel.com/v1/dashboards/1234/widgets?widgetType=WIDGET_TYPE_SIGNALS_TABLE' \
     --header 'accept: application/json' \
     --header 'x-api-key: xxx'

{
  "widgets": [
    {
      "name": "dashboards/1234/widgets/2",
      "displayName": "Weekly KPIs",
      "widgetType": "WIDGET_TYPE_SIGNALS_TABLE"
    }
  ]
}

Widget ids are only unique within their dashboard, so a widget is always named relative to the dashboard holding it.

Listing the columns

The columns of one table are listed with List dashboard table columns:

curl --request GET \
     --url https://management.api.exabel.com/v1/dashboards/1234/widgets/2/columns \
     --header 'accept: application/json' \
     --header 'x-api-key: xxx'

{
  "columns": [
    {
      "identifier": "c1f0a4b39d5e2c7a8b6d4f1e0a3c5b79",
      "displayName": "Company",
      "index": 0
    },
    {
      "identifier": "5b7d1e3f0a9c2648e1d3b5a7c9f0e2d4",
      "displayName": "Revenue",
      "index": 1
    },
    {
      "identifier": "9a3c5e7f1b0d2648a8c6e4f2d0b9a7c5",
      "displayName": "Last reported",
      "index": 2
    },
    {
      "identifier": "3e8b0d6a2c4f1957b0a2c4e6d8f1a3b5",
      "displayName": "Gross margin",
      "index": 3
    }
  ]
}

Position 0 holds the entity each row is about; positions 1 and up are the data columns.

Two things about this list are worth knowing before storing anything from it:

• An identifier is derived from the column's definition, so editing a column — renaming it included — gives it a new identifier. List the columns again rather than keeping one between sessions.
• Positions are absolute, and a gap in them reports a column that has not been calculated yet. A column added to the table since its last calculation has no identifier, so it cannot be named in an export and is left out of the list rather than offered without one.

Reading the columns requires the table to have been calculated at least once. A table that has never been calculated answers with an error rather than an empty list; reading it starts a calculation when none is running, so asking again shortly usually answers. Tables on a company page dashboard are calculated per company, on request, and their columns cannot be listed at all — nor exported, in either the structured or the SQL form.

Exporting the table

With the widget name in hand, POST it to the Export API:

curl --request POST \
     --url https://export.api.exabel.com/v1/export/dashboardTable \
     --header 'x-api-key: xxx' \
     --header 'content-type: application/json' \
     --data '{"table": "dashboards/1234/widgets/2"}' \
     --output table.csv

The response body is the file itself, not JSON, and it carries the whole table — every row, not the first page the app shows. The suggested file name is in the Content-Disposition header, as table-1234-2.csv.

Parameters

📘

Unknown fields are rejected

A field the request does not define is an error, at every level of the JSON. A misspelled filter would otherwise be dropped silently and export more rows than were asked for, with nothing in the file to say so.

Selecting columns

A column is named by its identifier, by the display name shown in the table, or by its position:

{
  "table": "dashboards/1234/widgets/2",
  "columns": [
    {"column": "5b7d1e3f0a9c2648e1d3b5a7c9f0e2d4"},
    {"column": "Last reported"},
    {"index": 3}
  ]
}

Display names are matched ignoring case and surrounding whitespace, and need not be unique: where a name matches several columns, columns selects all of them. Selections are not deduplicated either, so a column named twice — once by name and once by position, say — is exported twice. A filter or an ordering applies to one column, so those reject an ambiguous reference instead — use index there.

The entity columns are always included, so there is no need to select them: naming position 0 here is accepted and ignored rather than rejected. What index counts is the table's own positions, as List dashboard table columns reports them — position 0 is the single entity column and the first data column is position 1. The exported file is wider than that, because position 0 expands on the way out: for companies it becomes five columns (name, MIC, ticker, FactSet id and Bloomberg ticker), and for anything else a single entity name. Those output columns are not addressable, and they do not shift the numbering of the data columns.

Filtering rows

Rows can be filtered by entity, by tag, and by the values in the table's own columns. A numeric filter sets exactly one of four fields:

above and below are inclusive. between and outside are inclusive at each bound unless that bound is marked open, and leaving a bound out makes the interval unbounded in that direction — so a one-sided strict comparison is one bound and one flag:

A column holding dates is filtered with insideRelativeDays, which counts from today and is signed: -30 is thirty days ago and 30 is thirty days ahead.

{
  "table": "dashboards/1234/widgets/2",
  "entities": ["entityTypes/company/entities/F_000C7F-E"],
  "tagFilter": {
    "anyOfTags": ["tags/user:123"],
    "excludedTags": ["tags/user:456"]
  },
  "columnFilters": [
    {
      "column": {"column": "Revenue"},
      "numericFilter": {"between": {"start": 1000000000, "openAtStart": true}}
    },
    {
      "column": {"index": 2},
      "dateFilter": {"insideRelativeDays": {"start": -30, "end": 30}}
    }
  ]
}

A cell that holds no value of the filter's kind never passes: a numeric filter drops rows whose cell is empty or textual, and a date filter drops rows whose cell carries no date.

Sorting rows

One ordering is supported. Giving more is an error rather than a silent choice of one of them:

{
  "table": "dashboards/1234/widgets/2",
  "columnOrderings": [
    {"column": {"column": "Revenue"}, "direction": "DESCENDING"}
  ]
}

Rows whose cell in the sort column has no value are placed last in both directions.

Companies can be ordered by their Bloomberg ticker rather than by their displayed name with useTicker, which applies to the entity column at position 0 only:

{
  "table": "dashboards/1234/widgets/2",
  "columnOrderings": [
    {"column": {"index": 0}, "useTicker": true}
  ]
}

File formats

csv is the default. excel is the only format that carries the table's own cell formatting and its sub-row grouping, so a table whose rows expand into sub-rows in the app is best exported that way; the other formats flatten it. Unlike the older export methods, this one does not accept pickle, which cannot be read without executing its payload.

import requests

response = requests.post(
    "https://export.api.exabel.com/v1/export/dashboardTable",
    headers={"x-api-key": "xxx"},
    json={"table": "dashboards/1234/widgets/2", "outputFormat": "parquet"},
)
response.raise_for_status()
with open("table.parquet", "wb") as file:
    file.write(response.content)

Migrating from the SQL form

The SQL form keeps working, and nothing here is removed. New integrations should prefer the structured request, which is what the equivalent queries look like:

Note that a quoted name is a display name on both sides, in ORDER BY as in a filter; the positional form is spelled 'column_index=3' in SQL and {"index": 3} here.

Three differences are worth noting. The widget is always named: the SQL form exports the first widget of the dashboard when widget_id is left out, while a structured request has no default. Identifiers work everywhere a display name does, which the SQL form has no syntax for. And each mapping above preserves the filter it replaces exactly, bounds included — the date rows keep the open bound and the unbounded far side that days_in_past and days_in_future have always had, so a migrated query returns the same rows rather than a tidier-looking window.

When a request is rejected

The error message names what to do about it. The three that come up most:

• Column 'Revenu' not found — the message lists the available columns, as display name (identifier). Compare against List dashboard table columns for the table you are exporting; a renamed column changes its identifier.
• Column 'Revenue' is not unique; it matches columns at positions [1, 4] — the table holds that column twice. Refer to it by index in a filter or an ordering.
• Cannot find field: columnFilter — a field name is misspelled, or nested one level from where it belongs. columnFilters, columnOrderings, tagFilter and entities all sit at the top level of the request, next to table.