, , ,

Auto-Populate Latitude and Longitude in Excel

When analyzing real estate investments in Excel, it’s often necessary to include the latitude and longitude coordinates of the properties we’re modeling. To do this, most of us open up Google Maps, or some other mapping tool, and find the coordinates that way. Others use a latitude/longitude lookup tool such as latlong.net to find what they need. However, these tools require leaving Excel, manually grabbing the coordinates, and then copying them back into Excel. While not overly time consuming, there is a more effective and accurate way to quickly grab a property’s coordinates without leaving Excel.

Geocoding

Leaving Excel to Grab Lat/Long. Coordinates Takes Time and Increases Transcription Errors

Google Geocoding in Excel

Google has developed a service for converting addresses (like “555 Main St., Anywhere USA, CA”) into geographic coordinates (like latitude 36.411022 and longitude -120255489). The service, made possible by the Google Maps Geocoding API, is commonly used online to quickly convert physical addresses to coordinates. What is less common, but just as easily done, is to use the geocoding API with a custom Excel function or via an Excel Add-In to convert a physical address to latitude and longitude coordinates instantly and without having to leave Excel. I’ll show you how to incorporate these two options into your real estate modeling.

Option One: The Google Geocoding Custom Function

The first option for incorporating Google Geocoding into Excel, is to write a custom function that will take an address and instantly convert it to latitude and/or longitude coordinates using Google’s geocoding service. Now if you’re unfamiliar with the concept of custom functions in Excel, a custom function acts like any other function in Excel (e.g. SUM, EDATE, VLOOKUP) only that it is specific to the workbook you create it in, and requires you to save the workbook as a macro-enabled file. This may be exactly what you want since, if you share the workbook, other users of the workbook will have access to the custom function. However, if you’d like this function to be available every time you open up Excel, you might consider installing the Latitude/Longitude Add-In instead (see option two below).

Adding the Google Geocoding custom function only takes a few minutes and the process is quite painless. I learned how to do this after reading a post on the subject, written by Christos Samaras, on his engineering blog (of all places!). I’ve recorded a video (see below) that walks you through the simple process of creating the function using Mr. Samaras’ VBA code.

To get the VBA Code used in this tutorial: Click Here

Once the custom function is in your model, using the function is simple:

  1. In the cell of your choosing (e.g. cell B2), write a physical address in this order: STREET ADDRESS, CITY, STATE, ZIP CODE (example: 555 Main St, Anywhere USA, CA, 90229).
  2. To get the latitude of the address in cell B2, use the formula = GetLatitude(B2)
  3. To get the longitude of the address in cell B2, use the formula = GetLongitude(B2)
  4. To get both the latitude and longitude of the address in cell B2, use the formula = GetCoordinates(B2)

Option Two: The Latitude/Longitude Excel Add-In

Microsoft offers the ability to add more functionality to Excel via Add-Ins. One such Add-In I’ve discussed here at Adventures in CRE is the WST Macros Add-In, which expands the world of keyboard shortcuts in Excel exponentially. Add-Ins, when installed correctly, extend custom content to work across Excel files so that, for instance, a custom function like the Google Geocoding function can be used in every workbook you open.

I’ve created an Excel Add-In that, once installed, adds the Google Geocoding function discussed in option one as a standard function to your Excel workbooks (i.e. you will now be able to use =GetLatitude(), =GetLongitude, or =GetCoordinates() in all of your Excel files). You can download the Add-In for free using the link below. In terms of how to install Excel Add-Ins, the folks at Wall Street Training wrote great instructions for installing their WST Macros that can be used with installing any Add-In including this Latitude/Longitude Add-In. You can grab those instructions here. You might also find Microsoft’s notes on adding and removing Add-Ins helpful.

Once the Latitude/Longitude Excel Add-In is installed, using the function is the same as with option one:

  1. In the cell of your choosing (e.g. cell B2), write a physical address in this order: STREET ADDRESS, CITY, STATE, ZIP CODE (example: 555 Main St, Anywhere USA, CA, 90229).
  2. To get the latitude of the address in cell B2, use the formula = GetLatitude(B2)
  3. To get the longitude of the address in cell B2, use the formula = GetLongitude(B2)
  4. To get both the latitude and longitude of the address in cell B2, use the formula = GetCoordinates(B2)

As always, if you have any comments, questions, or suggestions, please let me know.

Latitude/Longitude Excel Add-In
  • Adds the Google Geocoding Functions, written by Christos Samaras, to Microsoft Excel
  • Use GetLatitude(), GetLongitude(), or Get Coordinates() to convert a physical address to latitude/longitude coordinates

 

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 14 years of real estate investment and development experience. In his current position, Spencer assesses new investments for a $40bn real estate fund. He resides with his wife and kids in Dallas, TX.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply