Home / Guide

How to use Excel

The European Bank Data API gives you computed bank metrics: CET1 ratio, leverage ratio, liquidity coverage ratio, and many more, across a large number of European banks and reporting periods. Each metric is a ratio or nominal amount that says something meaningful about a bank's capital, liquidity, asset quality, or exposure.

How do you get this data into Excel? That's especially useful if you don't want to write a line of code. This tutorial helps you do just that. And it doesn't require you to install anything beyond Excel itself, so there's nothing for IT to approve. It has two parts: a straightforward example to get started, and a more advanced set-up that allows you to fully automate data browsing, selection and downloading.

Example: Metrics

Connect through Power Query

The metrics endpoint is a JSON REST API, and Power Query, built into Excel under Data, talks to it directly. You don't need a dedicated connector.

  1. In Excel, go to Data → Get Data → From Other Sources → Blank Query.
  2. Open the Advanced Editor and paste:
  3. Adjust the API key to match your API key.
let
    Source = Json.Document(
        Web.Contents(
            "https://app-2b3bd7f4-0899-420d-be54-0a0cfb9b0879.cleverapps.io/v1/metrics/",
            [
                Query = [metric_name = "leverage_ratio"],
                Headers = [#"X-API-Key" = "eu_bank_data_..."]
            ]
        )
    ),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(
        ToTable, "Column1",
        {"entity_name", "country", "ref_period", "metric_label", "value", "unit"}
    )
in
    Expanded
  1. Click Close & Load. You now have a table with one row per bank and period, refreshable from the ribbon any time.

The first time you load a query against a new host, Power Query asks how to authenticate. Choose Anonymous (the key travels in the Headers argument above, not through Power Query's own credential prompt) and it won't ask again for that host.

Refresh

The whole point of connecting through Power Query rather than pasting a CSV is that the workbook stays live. The database behind the API refreshes daily, at 6:00 and 18:00 Paris time, so a quarterly disclosure that gets resubmitted, or a new period that gets added, shows up the next time you refresh, with no need to rebuild the query.

To refresh manually, Data → Refresh All. To refresh automatically whenever the workbook opens, right-click the query in the Queries & Connections pane → Properties → Refresh data when opening the file.

If you've added several queries, Refresh All reruns every one of them in turn: each one is a separate request. The full tutorial below avoids this by resolving every pick into a single request instead.

Example: League tables

A league table is one ready-made table per topic: one row per bank, one column per metric. It needs no pivoting. There are six: key_metrics, capital, liquidity, capital_requirements, asset_quality and ESG. You can narrow them with the same filters as the bank list (country, size, systemic_relevance, ssm, stoxx, ...), and pick a period:

A league table returns at most 200 banks. If your filters match more, the API answers with a message explaining why, and the query below shows that message as a one-row table instead of Excel's generic "Bad Request" error:

let
    Response = Web.Contents(
        "https://app-2b3bd7f4-0899-420d-be54-0a0cfb9b0879.cleverapps.io/v1/league_table/key_metrics/",
        [
            Query = [country = "Germany", period = "latest"],
            Headers = [#"X-API-Key" = "eu_bank_data_..."],
            ManualStatusHandling = {400}
        ]
    ),
    Status = Value.Metadata(Response)[Response.Status],
    Json = Json.Document(Response),
    Result =
        if Status = 400 then
            #table({"error"}, {{Json[detail]}})
        else
            Table.FromRecords(Json, null, MissingField.UseNull)
in
    Result

Ratios come back as decimal fractions (0.153 is 15.3%). Format the columns as percentages in Excel. Amounts are in each bank's own reporting currency, which is shown in the currency column.

Full tutorial: Building a full workbook

The example above is one query for one metric. This section builds a small workbook where everything downstream of a one-time Power Query paste is point-and-click: a Settings page holds your API key, two catalog pages let you browse every bank and metric the API knows about, and a picker page lets you tick the banks and metrics you want and hit Refresh All. Nobody touches the Advanced Editor again after this section.

The four pieces below build on each other in order: Settings feeds every other query, the two catalog queries feed the picker's dropdowns, and the picker feeds the final data pull. Set them up in the order they're presented.

Settings page

Every other query in this workbook reads its API key and base URL from one place, so changing keys (or pointing at a different environment) later is a cell edit, not a re-paste of M code.

  1. Add a worksheet named Settings. Enter two columns, Setting and Value, with one row each for API Key and Base URL:
SettingValue
API Keyeu_bank_data_...
Base URLhttps://app-2b3bd7f4-0899-420d-be54-0a0cfb9b0879.cleverapps.io/v1/
  1. Select the range and press Ctrl+T to turn it into a Table. In Table Design → Table Name, name it Settings.
  1. Paste your own API key into that Value cell. This is the only cell you should ever need to edit to rotate a key.
  1. Data → Get Data → From Other Sources → Blank Query, open the Advanced Editor, and paste:
    let
        Source = Excel.CurrentWorkbook(){[Name="Settings"]}[Content],
        ApiKey = Source{[Setting="API Key"]}[Value],
        BaseUrl = Source{[Setting="Base URL"]}[Value]
    in
        [ApiKey = ApiKey, BaseUrl = BaseUrl]
  1. Name the query Settings and, in the load dialog, choose Only Create Connection. It doesn't need its own worksheet, since the values already live on the Settings sheet. Every query below references it as Settings[ApiKey] and Settings[BaseUrl].

Note: If Close & Load To… is unavailable:

  1. Close the editor.
  2. Open Data → Queries & Connections.
  3. Right-click the query → Load To…
  4. Select Only Create Connection.

Getting the available metrics

This pulls the full metric catalog once, so the picker page can offer every metric as a dropdown instead of you needing to know exact metric_name values.

  1. New Blank Query, Advanced Editor, paste:
    let
        Source = Json.Document(
            Web.Contents(
                Settings[BaseUrl] & "metadata-metrics/",
                [Headers = [#"X-API-Key" = Settings[ApiKey]]]
            )
        ),
        ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expanded = Table.ExpandRecordColumn(
            ToTable, "Column1",
            {"metric_name", "category", "label", "definition", "unit_kind", "available_transformations"}
        )
    in
        Expanded
  1. Name the query AvailableMetrics and Close & Load it as a Table on a new worksheet named Metrics catalog. It's a static, checked-in catalog on the API side (see API reference), so it's cheap to refresh but won't change often.

Note: in some cases, depending on how Excel's settings are pre-configured, you may run into this error: Formula.Firewall: Query 'AvailableMetrics' (step 'Expanded') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

You can fix this as follows:

  1. Open Data → Get Data → Query Options.
  2. Select Current Workbook → Privacy.
  3. Choose Ignore the Privacy Levels and potentially improve performance (“Fast Combine”).
  4. Click OK.
  5. In Data → Get Data → Data Source Settings, select the API URL and choose Edit Permissions.
  6. Set credentials to Anonymous.
  7. Refresh AvailableMetrics.

Getting the available banks

Same idea, against /v1/banks/, so the picker page can offer every bank by name.

  1. New Blank Query, Advanced Editor, paste:
    let
        Source = Json.Document(
            Web.Contents(
                Settings[BaseUrl] & "banks/",
                [Headers = [#"X-API-Key" = Settings[ApiKey]]]
            )
        ),
        ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expanded = Table.ExpandRecordColumn(
            ToTable, "Column1",
            {
                "entity_lei", "entity_name", "entity_type", "institution_type", "country",
                "leverage_exposure_eur_bn", "leverage_exposure_currency", "leverage_exposure_ref_period",
                "size", "systemic_relevance", "ssm", "stoxx"
            }
        )
    in
        Expanded
  1. Name the query Banks and Close & Load it as a Table on a new worksheet named Banks.

Selecting and getting metrics

This is the page you'll actually use day to day: tick some banks, tick some metrics, refresh.

Excel doesn't support a true multi-select single dropdown without a macro, so this uses the standard no-VBA workaround instead: a small table where each row is one pick, and you add as many rows as banks or metrics you want.

1. Build the two picker tables.

On a new worksheet named Selection, create two small Tables (Ctrl+T each):

Add a handful of blank rows to each (extend the Table by tabbing past the last row, same as any Excel Table) and pick as many banks and metrics as you want from the dropdowns. Leave rows blank for picks you're not using yet.

2. Resolve picks to API identifiers. The picker tables store human-readable names; these two connection-only queries turn them into the entity_lei and metric_name values the API expects.

SelectedBankLeis:

let
    Picks = Excel.CurrentWorkbook(){[Name="BankPicks"]}[Content],
    NonBlank = Table.SelectRows(Picks, each [Bank] <> null and [Bank] <> ""),
    Merged = Table.NestedJoin(NonBlank, {"Bank"}, Banks, {"entity_name"}, "BankMatch", JoinKind.Inner),
    Expanded = Table.ExpandTableColumn(Merged, "BankMatch", {"entity_lei"}),
    Deduped = List.Distinct(Expanded[entity_lei])
in
    Deduped

SelectedMetricNames, the same pattern against the metric catalog. The picker stores label, the human-readable name; /v1/metrics/ wants metric_name, so this joins one to the other:

let
    Picks = Excel.CurrentWorkbook(){[Name="MetricPicks"]}[Content],
    NonBlank = Table.SelectRows(Picks, each [Metric] <> null and [Metric] <> ""),
    Merged = Table.NestedJoin(NonBlank, {"Metric"}, AvailableMetrics, {"label"}, "MetricMatch", JoinKind.Inner),
    Expanded = Table.ExpandTableColumn(Merged, "MetricMatch", {"metric_name"}),
    Deduped = List.Distinct(Expanded[metric_name])
in
    Deduped

Load both as Only Create Connection. They're intermediate steps, not something you need to look at.

3. Pull the data. /v1/metrics/'s entity_lei and metric_name parameters both accept repeated values (see API reference), so every bank and metric you've ticked comes back in a single request, not one call per combination:

let
    BankParams = Text.Combine(
        List.Transform(SelectedBankLeis, each "entity_lei=" & Uri.EscapeDataString(_)), "&"
    ),
    MetricParams = Text.Combine(
        List.Transform(SelectedMetricNames, each "metric_name=" & Uri.EscapeDataString(_)), "&"
    ),
    Segments = List.Select({BankParams, MetricParams}, each Text.Length(_) > 0),
    QueryString = Text.Combine(Segments, "&"),
    Url = if QueryString = "" then Settings[BaseUrl] & "metrics/" else Settings[BaseUrl] & "metrics/?" & QueryString,
    Source = Json.Document(
        Web.Contents(Url, [Headers = [#"X-API-Key" = Settings[ApiKey]]])
    ),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(
        ToTable, "Column1",
        {
            "entity_lei", "entity_name", "country", "ref_period", "category",
            "metric_name", "metric_label", "transformation", "value", "unit"
        }
    )
in
    Expanded

Name the query SelectedData and Close & Load it as a Table on a new worksheet named Results. Leaving BankPicks empty pulls the selected metrics for every bank; leaving MetricPicks empty pulls every metric for the selected banks; leaving both empty pulls everything, subject to the 1,000-row default cap (see API reference). Check X-Plan-Capped if a broad pull looks smaller than expected, and narrow the picks instead of raising limit from within Excel.

From here, Insert → PivotTable on the Results table, with ref_period on rows and entity_name on columns, value as the data field, gives you one column per bank and one row per quarter, ready for a line chart per metric.

Refreshing the workbook

Data → Refresh All re-runs the whole chain in dependency order: Settings, then Banks and AvailableMetrics, then SelectedBankLeis/SelectedMetricNames, then SelectedData. Excel resolves that order for you from the query references, so you don't need to refresh them one at a time.

Because the picks are resolved into repeated parameters on a single call, refreshing this workbook costs one request to /v1/metrics/ per refresh, no matter how many banks and metrics you've ticked.

To refresh automatically whenever the workbook opens, right-click SelectedData in the Queries & Connections pane → Properties → Refresh data when opening the file. Day to day, updating this workbook is: tick or untick a bank or metric on the Selection sheet, Refresh All, done.