Clean your customer master data with Excel 2013 and Public data

The open data in Denmark has started to evolve – and now a public marketplace of data and apps is available as well via – https://data.virk.dk/

One of the apps in the App market is actually very nice and can then be used in Excel to wash/check your master about Danish companies. The app is a mirror of data from The Central Business Register – http://www.CVR.dk and can be accessed via http://cvrapi.dk/, and is not associated with http://cvr.dk

Using the new Excel functions WEBSERVICE and FILTERXML can then be used to lookup values via the API.

First I found a list of all the companies in Denmark where the name Microsoft was included.

A simple search via the API – for the first VAT number gives me the result but also the syntax to call the API – http://cvrapi.dk/api?search=13612870&country=dk

The documentation reveals that the result can be returned either as JSON or XML via a Format parameter

So by calling http://cvrapi.dk/api?search=13612870&country=dk&format=XML we get an XML result.

Then we use the new WEBSERVICE function in Excel to call the API

=WEBSERVICE(“http://cvrapi.dk/api?search=”&A3&”&country=dk&format=xml”)

This returns the full XML text in the cell.

Then we use FILTERXML function to retrieve the different xml node values – so to get the company name

=IFERROR(FILTERXML($D3;”//”&E$2);NA())

I use the headers of the table to specify the different xml node values – that I want to retrieve

Easy and fast I can retrieve meta data for all the companies in the list – including addresses, number of employees, industry and more via the API.

Then add some Conditional formatting and you can quickly identify differences between your data and the public data.

The use of the API could also be used in Power Query to create a function to add meta data to existing items in other tables.

You can download the sample file from here – Link