Topics | Getting Started

Creating your own Excel tools using Excel Internet Queries

We think that spreadsheet tools are really useful things to have. In fact, we like them so much we now have a special web service code sample that will allow you to instantly create an Excel spreadsheet tool, which you can then distribute as you wish, and over the next few minutes we’re going to show you exactly how to use them.

The first step is to decide which service you want to use. Because of the way we automatically generate our documentation and services, the Excel Internet Query endpoint that you need is available on any of our services, including any of your bespoke web services generated using MyServices.

For this example we’ll use our Bank Account and Sort Code validation service. To find the code sample we need to head to the products API page. This is done by selecting the product from the products menu, and then clicking the "API" link in the submenu.

In this example we want to use the functionality of the "BankAccountValidation Interactive Validate" API so we click on that link to reach the APIs documentation page. The box we are interested in is the "Code samples & end points" box in the top right hand corner of the page. Here we will select "Excel Internet Query", and click view. This will start the download process. Download the Excel Internet Query to your machine.

That’s all there is to do from the API point of view. Now we need to open an Excel spreadsheet and import some data. Within Excel (we're using Office 2010 here), select "Data" from the menu. Click on "Existing Connections", and then "Browse for More…". Locate the Excel Internet Query that we downloaded earlier, and click "Open".

First you need to select a cell to return the data to. Bear in mind that this will cause the resulting data table to be constructed using this cell as the top left-hand cell of the table. Once you’re done, click "OK".

Import web service data into Excel

You need a Postcode Anywhere key to access the service. You will need to enter the key as a parameter in the next box, and you should also check ‘Use this value/reference for future refreshes’ so that the same key is always used when the query updates.

Enter your key as a parameter

The next thing you will need to do is stipulate a cell that will contain the bank account number. Select the cell you wish to use, and tick both "Use this value/reference for future refreshes" and "Refresh automatically when cell value changes". This will mean that every time you change the account number the data will automatically refresh.

Select the bank account number cell

Next, set the cell for the sort code, repeating the process as above. Click "OK" to continue.

Select the sort code cell

That's all you need to do. You now have a fully functional Excel spreadsheet tool which will automatically update when the bank account or sort code values are changed.