Excel everywhere

In my professional career, I had the opportunity to play with a large amount of reporting tools, nevertheless the preferred one for my colleagues was always Excel. On today’s world tools like Microsoft BI, Tableau or SAP Analytics seems to be the “saveur du jour” in business world but there will always be someone wanting to have the data in or from an Excel form.

The challenge

So, one day, I saw my colleague Roberto coming into my office as he wanted to create a map chart with bubble the size of payables amount. The problem is that for many of these tools, you need a latitude and longitude coordinates, and he only had a worksheet with something like a thousand addresses.

Google maps

At the time, I was using the Google maps API, which provides the longitude and latitude based on an address query. The problem is that nowadays using google maps API has become a bit quirky since you first need to register to the API to get a key. Also, this API is no longer free, and when you work for a business stingier than Mr Burns injected with Duck McScrooge DNA and when you have an IT department so inept that you have to work against it instead of with it to get things done, you need to be creative.

Nominatim

So, let’s have a look to OpenStreetMap. OpenStreetMap is a collaborative project to create a free editable geographic database of the world, and there is a free API, Nominatim, that you can use to find GPS coordinates related to text addresses. You can find more details here: https://nominatim.org/ and query it here: https://nominatim.openstreetmap.org/ui/search.html. Note that while using this API is free, the terms of use require a maximum of 1 request per second, so don’t use it to map your entire LFA1 table please, but if you have a few thousand items, it should do the job.

Excel Macro

You can use Excel to query web API directly, but the trick here is that we need to put the address we want to query in the URL. It might be possible to do with a dedicated power query, but the easier is still to write a dedicated vba macro.

Accessing the VBA editor

You can access the vba editor with alt-F11, or via the macro button in the developer tab. If the developer bar is not visible, select File on the menu, then on the File tab, go to Options > Customize Ribbon; under Customize the Ribbon and under Main Tabs, select the Developer check box.

Create a new module

In the editor, create a new module, by selecting “insert”, then “module”.

Adding a reference to XML library

Then go in the “tools” toolbar and select references. Then look after the Microsoft XML reference, as indicated below

The VBA Code

We will write a macro that will return a matrix with latitude, longitude, and if there is nothing corresponding in the database, an error message. Copy the VBA code below in the module you created.

Function AddressToCoordinates(address As String) As Variant
Const APIPREFIX = "https://nominatim.openstreetmap.org/search?format=xml&q="
Dim returnValue(2) As String
Dim Xdoc As New MSXML2.DOMDocument60
    Xdoc.async = False
    Xdoc.Load (APIPREFIX + WorksheetFunction.EncodeURL(address))
    If Xdoc.parseError.ErrorCode <> 0 Then
        AddressToCoordinates(2) = Xdoc.parseError.reason
    Else
        Xdoc.SetProperty "SelectionLanguage", "XPath"
        Dim loc As MSXML2.IXMLDOMElement
        Set loc = Xdoc.SelectSingleNode("/searchresults/place")
        If loc Is Nothing Then
            returnValue(2) = Xdoc.XML
        Else
            returnValue(0) = loc.getAttribute("lat")
            returnValue(1) = loc.getAttribute("lon")
        End If
    End If
    AddressToCoordinates = returnValue
End Function

In the Excel workbook

I found on the internet a list of fictional places, that we can use to test our function. The first column contains the address, the second the owner of this address. We will add three other columns for latitude, longitude and a third one for the eventual error messages.

We will then use the function we just created. Select the range C2 to E2 like in our example. Type in the Excel formula: =ADDRESSTOCOORDINATES(A2) and then CTRL+SHIFT+ENTER since this is a matrix formula. Copy down the columns C,D and E And check the final result:

As you can see, in the table above, you will not be able to send a postcard to SpoungeBob SquarePants at 124 Conch St., Bikini Bottom, Pacific Ocean, but 1329 Carrol avenue, Home of the Halliwell family of “Charmed” fame, exists at the given coordinates of 34.0697 -118.254.

I hope you find this article interesting, and that it will be of use for you. Have a nice day, Stéphane Catoire.

References: HTTP://WWW.MKRGEO-BLOG.COM/THE-COSTLESS-WAY-TO-GEOCODING-ADDRESSES-IN-EXCEL-PART-3-BULK-DATA-GEOCODING-WITH-NOMINATIM-AND-OTHERS-GEOCODING-TOOLS/