Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The Google Maps Geocoding API to retrieve the longitude and latitude for free. Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that. So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses. You create excel function to perform the geocoding for multiple addresses.
For this task I am using Microsoft Excel 2013, you can use 2007 or 2010 version. First task is to turn on the Developer tab. To find this tab in 2007 or 2010 version you have to play around.
1. Go to the Microsoft File > Options > Customize Ribbon and check Developer
You will notice Developer tab is added on the ribbon.
2. Now click the Visual Basic button on the far left corner which will open the new window. Now we want a new module to insert which will store our new function. For that click on Insert > New Module
Copy the below code and paste into the empty area (code source: policeanalyst.com)
This code make use of Microsoft’s XML library, so you have to add references to it. To add the reference go to Tools > References
You will see list of references with the check box, select the Microsoft XML v6.0, after that click Ok button.
Finally save your Visual basic window by pressing save button. Then from the drop down menu of Save as type select Excel Add-In, assign the file name of your desire. Once the file in saved in “*.xlam” extension you can open any new document and use the function. Close all the open excel files and open the new one.
3. Open the new excel file and then go to file > Options > Add-Inns. From the Manage drop down box select the Excel-Add Ins.
After selecting then press the GO button, this will list all the saved Add-ins files, as I have saved as Book1. Xlam I will check that box. Then press Ok after selecting,
4. As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value. Let us suppose I have address 65 Hawthorn way, Olds, Alberta, Canada then you type that value in excel and use the function MyGeocode (as you type my it will show function on the box).
It will take few seconds to run the function because it has to pull the information from the google server and then result will display.
Now if you have number of addresses and you want to run the geocode then this function will help you. Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.
Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 10 seconds break between addresses. If Google stop you for retrieving large number of addresses, you will see error saying “Not Found (try again, you may have done too many too fast)”. See below example, I have tried few number of addressee without following 10 seconds rule.
I hope you liked this tutorial, please comment if you have any question regarding this.