Legal Entity Identifier & Business Identifier Code Mapping

How to enrich LEI / BIC mapping csv file with additional LEI data

Aim

This article explains why the GLEIF published LEI/BIC relationship mapping csv file cannot always easily be used to derive the correct LEI related to a given BIC. It shows how to enrich the csv file with additional LEI fields, to better differentiate and select the correct LEI when more than one is related to a single BIC.

Background

In the various ISO 20022 adoptions across the payments domain, specifically the planned introduction of enhanced data, certain Financial Market Infrastructures (FMIs) are beginning to mandate the use of Legal Entity Identifiers (LEI). The use of of LEIs should therefore be part of any ISO 20022 implementation for banks and PSPs.

In this article we’ll be looking at the use-case where, for the given organisation (whether an agent or party), the Business Identifier Code (BIC) is available and from this, we need to get the relevant LEI to populate the ISO 20022 message.

LEI / BIC Mapping

Helpfully, Swift and GLEIF have produced an open-source csv relationship file which maps a BIC assigned to an organisation to its LEI. This is updated each month and can be downloaded from the GLEIF website.

This file could therefore be imported each month to a reference data store and used to lookup the correct LEI for a given BIC.

Many to Many Relationships

If it were that straightforward, this article would be redundant, so here’s the (small) complication…a single BIC can be related to multiple LEIs; likewise, a single LEI can be related to multiple BICs.

At the time of writing, the November 2023 mapping csv file has 22447 mapped items. Of these, 98.4% (22084) are BICs which only map to a single LEI. However, there are a minority of BICs which map to multiple LEIs. For example the BIC “INGCITM1XXX” maps to 6 LEIs as shown in the picture below. With only the LEI field itself, it is not easy to differentiate between them and ensure the correct organisation is selected.

Example: Multiple LEIs for a single BIC

Enriching with Additional LEI Data

To solve this we can enrich the csv file with additional LEI fields thereby, the correct selection should be easier. The enriched file with 6 additional fields is shown below.

Example: Enriched multiple LEIs for a single BIC

How to Enrich

The GLEIF APIs provide everything needed to get the additional organisation data fields, specifically using the lei-records > Level 1 Information (LEI Records) > GET LEI Record By ID (LEI).

Using this API endpoint with the page[size] parameter and the filter[lei], where the latter is a comma separated string of the LEIs to enrich. A snippet of this Python code is below.

# URL of the GLEIF API endpoint
url = f"https://api.gleif.org/api/v1/lei-records?page[size]={batch_size}&filter[lei]={lei_csv_param}"

# Make the API call to GLEIF
response = requests.get(url)

# Check if the API call was successful
if response.status_code == 200:
  # Parse the JSON response
  data = response.json()
  # Extract the data from the response
  if not data['data'][0]:
    return 'No LEI data found'

  else:
    for item in data['data']:
      # Get the LEI
      if 'attributes' in item:
        lei = item['attributes'].get('lei')
        legal_name = item['attributes']['entity']['legalName']['name']
        legal_adr_city = item['attributes']['entity']['legalAddress']['city']
        legal_adr_ctry = item['attributes']['entity']['legalAddress']['country']
        legal_adr_postalcode = item['attributes']['entity']['legalAddress']['postalCode']
        status = item['attributes']['entity']['status']
        lei_enriched_data.append([lei, legal_name, legal_adr_city, legal_adr_ctry,
                                  legal_adr_postalcode, status])

return lei_enriched_data

GitHub Repository

The full Python code can be cloned from my GitHub repo here:

GitHub domdigby/GLEIF

I’m a hobbyist coder, just learning Python, therefore you have my apologies for the parlous state of this code, however, it works and doesn’t exceed the GLEI API rate limits. It also includes other methods calling on the GLEIF API endpoints and a method to validate the format and checksum of an LEI.

I hope these are useful. Enjoy!

GLEIF API Health Warning

A health warning about the GLEI APIs: read the terms and conditions (below). They are rate limited and exceeding the rates will likely result in 429 status codes (too many requests).


Terms & Conditions

There is no charge for the use of GLEIF’s LEI data.

GLEIF does not enter into individual contractual relationships with specific users of the LEI data.

Rate limiting is currently set at 60 requests, per minute, per user, for all users.

Please refer instead to the LEI Data Terms of Use available here: https://www.gleif.org/en/meta/lei-data-terms-of-use/


Using Excel?

In case you are using excel to open or view the original and enriched csv files, please note that due to the accented and non-Latin characters supported by LEI fields, such as those found in legal name, the python code uses UTF-16 encoding when writing the data to the enriched csv file. If you try to open this using Excel (double clicking the csv file), it may not work. Instead, follow these steps to import the csv file into Excel:

  1. Open Excel, then open a blank workbook.
  2. From the ribbon select File then Open.
  3. Browse to the location of the csv file, select it then choose Open.
  4. At the Text Import Wizard, ensure you select Unicode (UTF-8) as the File origin. This is very important — see picture below.
  5. If asked to convert the data, select Don’t Convert.
Setting Excel import encoding to UTF-8
Excel asking to convert LEI to number using scientific notation – DO NOT CONVERT!

Finally, If you must use Excel, I’d advise using a Power Query import. For large csv files this is more efficient. Certainly, don’t try to import the ISIN / LEI mapping file unless using a data model in Power Query. The ISIN / LEI csv has over 2 million records and is >240Mb.

Leave a Reply

Your email address will not be published. Required fields are marked *