Geoclient in Excel

Excel is one of the most intuitive ways for people to conduct data processing and data analysis, so wouldn’t it be nice if we could easily geocode in an Excel spreadsheet? Leveraging the WEBSERVICE function in Excel (version 2013 and later, only available on Windows), we can easily make requests to the Geoclient API, a geocoding web service maintained by NYC DoITT (if you are not familiar with NYC’s geocoding products, read this quick overview of NYC Geocoding tools). This blog post will take you through a step by step tutorial of how to use the WEBSERVICE function to get Geoclient results.

1. In order to make Geoclient API calls, you need to get your own Geoclient API id and API key, which you can get from the Geoclient developer portal.
2. To get started, populate your spreadsheet with parsed addresses and place your API id (B1) and API key (B2) in separate reference cells.

In this example we will use the Geoclient address function, which takes in house number (hnum), street name (sname) and borough (boro) and returns address level information.

Example requests:

https://api.cityofnewyork.us/geoclient/v1/address.json?houseNumber=314&street=west 100 st&borough=manhattan&app_id=abc123&app_key=def456

2. The WEBSERVICE function has one input parameter, which is a url. Following the format above, we can generate corresponding request URLs for each address in the table:

The Geoclient documentation website has sample responses for different function calls. The sample response for the address function is under section 1.2.1

3. Make WEBSERVICE function call by invoking WEBSERVICE and passing through the request URL, then store the returned JSON objects in the result column.

4. To retrieve different result attributes out of the JSON responses, we will use Excel power query. Select the table range you want to perform the query on (make sure you include the result column) and click “From Table/Range”

5. A query editor window will pop open. Select the result column and click on “Transform” tab, then click “JSON” in the “Parse” dropdown

6. Once the result is parsed, click on the expansion button on the top right corner of the address column and expand all.

7. As you can see, if you expand “address” you will see all the Geoclient returned attributes. From here, you can use the search bar to find the attributes you are looking for, such as BBL, BIN, etc.

8. Once you have made your selections and click “OK”, your selected attributes will automatically be populated as new columns.

If you are happy with the result you can go ahead and click “Close & Load” to save the query result table. A new sheet with query results will be automatically generated.

automatically generated new sheet with query results

you can use the result sheet just like any other excel sheets, all the values are linked to addresses in sheet 1

Moving forward, you can leverage the convenience of Excel to filter and select geocoded results. If you made any edits to the original input data, just click “Data>Refresh all” to update everything simultaneously.

Performance wise, Excel uses multithreading to make network calls meaning that we are geocoding multiple records at the same time. During testing, I created a spreadsheet of about 2.6K addresses and it took about 1 ~ 3 seconds to return the Geoclient results. Naturally, performance will vary depending on your hardware and internet speed.