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.

Note: This function requires you to insert a Google Geocoding API key (which you can get for free – instructions below). The API sets a limit on the number of requests you can make each second (50) and each day (2,500). When you exceed the limit, you get a message that reads something like: “Requestor has exceeded the server limit.” Google offers the ability to increase the limit via a premium plan. Learn more about Google’s Usage Limits Here.

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. The video also explains how to get and insert your Google Geocoding API key.

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 below.

To help you install the Excel Add-In, I’ve recorded a brief walk-through video. Keep in mind, part of installing this add-in involves inserting your custom Google Geocoding API Key into the Add-in’s VBA code. Thus, it’s important to watch the video to ensure the Add-in is installed correctly.

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.

Download The Model

To make this model accessible to everyone, it is offered on a "Pay What You're Able" basis with no minimum (enter $0 if you'd like) or maximum (your support helps keep the content coming - typical real estate models sell for $100 - $300 each). Just enter a price together with an email address to send the download link to, and then click 'Continue'. If you have any questions about our "Pay What You're Able" program or why we offer our models on this basis, please reach out to either Mike or Spencer.

Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.

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
  • Important: Use of the Add-in requires you have your own Google Geocoding API key (free from Google) - see walk-through video above for instructions
 
   
By clicking Continue, I agree to the following Terms & Conditions.
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software.
www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.

www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:

1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.

AdventuresinCRE.com will not sell or distribute your email address to third parties, but you understand AdventuresinCRE.com and/or its direct affiliates may email you from time to time including an initial email containing a link to download the aforementioned Excel file.

Troubleshooting Issues

Here are the most commons issues that arise when using this tool:

  1. Reach your daily request limit. Google offers the ability to increase the 2500 daily request usage limit, however you’d need to get premium/paid Google API key.
  2. Add-in not installing. In newer versions of Excel, you may need to expressly “Unblock” the Add-in During/After installing and whitelist the folder where the Add-in is located. Wall Street Training has instructions on how to Unblock Add-ins here.
  3. Failure to Enable XNL v3.0 library. In order to use this function you must enable the XML, v3.0 library from VBA editor. To do this, ‘Go to Tools -> References -> check the Microsoft XML, v3.0.
  4. You haven’t inserted an API Key into the code. As of 2018, the code requires you to insert your own Google Maps Geocoding API key. To get a free API key from Google, click here and then hit ‘Get a Key’.

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