When building lei-lookup.com, one of the most requested features by users was integration with Microsoft Excel.
This tutorial shows you how, in a few minutes, you can set up an Excel sheet to use the lei-lookup.com REST API to import LEI information directly into your spreadsheets. This can save hours of time compared to manually searching LEIs and cut and pasting results.
Let's go through the steps required to get LEI data into Excel. For this example we will use the search function - which searches for LEIs just like the main website search. These examples have been captured using Excel 2013 on windows 8. In other versions of Excel the screens may look a little different.
Get an API token
Log in to lei-lookup.com and select “API” from the top level menu. This brings up the API Settings dialog. The REST APIs are secured using a unique 'token' that must be provided with each call. Select the Token and copy it using <ctrl>c.
Build an Excel Sheet
Next open up Excel 2013 and create a new sheet. Copy the token into the sheet in cell B2 as shown in the screenshot and fill in the other cells as shown. (We are going to call the REST API with two parameters - one for the token and one for the string we are searching for)
Note: When pasting the token you may need to select "match destination formatting" to make sure the white token text that was copied is visible.
Setup the Web Query
Next we will use Excel’s Web query to fetch the data from lei-lookup.com. In Excel select the DATA tab and “Get External Data” - “From Web” as shown below.
In the “New Web Query” dialog replace the address with the text below and hit Go
Don’t worry that it says “Access denied for this resource” (like in the screenshot below) this is because we have not yet set up the token - which is what will grant access. Hit the Import button.
Next you should get the import data pop-up. Decide where you would like to put the data that will be returned by the query. In this example we chose $a$5.
Next we have to tell the web query where to get the parameterised values for “search” and “token”. We point them at the values we set up previously at cell B3 for search and B2 for token. We will also check the two checkboxes to allow us to re-use this value and have it update automatically
See the search results
If all goes well, the web query should now return the data for the search. In this case we searched for “apple” and the screenshot below shows the results. As we are using the “search” function these results are the same as if the search had been typed into the LEI site. The query returns the API code, the legal name, city and country. The detailsUrl contains a link that will take you to the relevant details page on lei-lookup.com for that LEI.
Note that the search API returns the first 2000 results. If you need more you should consider doing more than one query that returns less results, or contact us.
Results updated automatically
As we clicked the checkboxes to automatically update this means we can change the search string from “apple” to “google”, and automatically get a new set of results.
Search is just one of the functions available from the REST API. There are also:
- Details - Retrieves a single Legal entity's details by providing an LEI
- Matching - Fuzzy matches a single query against our database.
To further explore the use of these services use the API Integration tool. Go to the API page on lei-lookup.com and click on “Open API integration tool”. This allows you to try out the APIs, generate an example url, and see the result in your web browser. You can then follow a similar process to the tutorial and use your new url and a web query to import the results into Excel.
If you would like the Excel file from the tutorial and another more complex example showing usage of the other APIs please email firstname.lastname@example.org a put “Excel Integration Examples” in the subject.
We have seen how it is possible to quickly build a web query to automatically extract LEI information from lei-lookup.com into an Excel sheet saving many hours of manual searching, copy and pasting.