Streaminghunter598

1. Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In China
2. Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Location
3. Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Change
4. Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Labels
5. Excel Mac 2016 Get Longitude Latitude Coordinates For An Address List

Ever wanted to calculate the distance between two addresses in Excel? I recently had the following issue: from a list of over approx. 50 administration offices across my city I wanted to find the one that is closest to my workplace. Of course open Google Maps and type each location one by one… and then choose the shortest connection. By why bother when we have Google Maps Distance Matrix API!!!

To get the COORDINATES of any ADDRESS read this post

Using the Google Maps Distance Matrix API

This help guide will show you how to take a list of addresses and get latitude & longitude coordinates. To begin, you will need your addresses (street address with city and country; just a city) in CSV or Excel format with one address per row. The address may be in one column or across multiple columns (e.g.: a column each for street, city, and country). Find latitude and longitude from address I am using qlikmap to show data on map.I have 3 columns in my excel file zipcode,town and country.is it possible in qlickview to read them and convert to longitude and latitude so that I can show them on map.Or do I have to create columns in excel for latitude and longitude and read them in qlickview. Addresses to longitude and latitude ‎ 05:01 AM I have a list of customers and there own addresses, i was trying to use the map Visualization but i need to define the Latitude and Longitude, but they are a written addresses!! Use these URL templates to get latitude and longitude coordinates that correspond to location information provided as a query string. Location Recognition Use this URL template to get location information such as local businesses, natural points of interests, and a reverse geocoded address for a specified latitude and longitude coordinate.

Google has a lot of useful API out there and I encourage you to go sometime to the Google API Explorer and have a look at what other information you can easily utilize from Excel or your other applications.

Let’s however focus on getting the distance between two addresses. Google facilitates Google Maps Distance Matrix API for limited usage.

The API is configured using GET HTTP Params. A simple example below.
Say we want to get the distance between San Francisco and Victoria BC. We want to get there by bicycle. Copy this link into your browser:

Google Distance Matrix Builder

Want to quickly test the Google API? Below a simple form for building a quick Distance URL. Simply type in the From an To addresses, select the transportation mode and hit Build URL!. Go here for more options for configuring the Google Maps Distance Matrix API.

Google Maps Distance Matrix URL:

Calculate distance between two addresses using Google Maps in Excel

So I knocked up quickly this VBA Function in Excel which uses Google API distance matrix function to calculate the Google Maps distance.

Be sure to first replace YOUR_KEY with your personal API key obtained from here.

See the VBA code here:

Get Google Maps distance in meters

Get Google Maps duration (in seconds)

Calculate distance between two coordinates

You can calculate the distance between two coordinates (2 pairs of latitudes and longitudes) using either Google’s API or a simple VBA Function.

Calculate distance between coordinates using a VBA function

Taking into account the elliptic nature of Mother Earth you can easily calculate the distance between coordinates without using Google API .

The function returns the distance and using the unit variable you can state if you want the function to return the distance in Kilometres ('K'), Miles ('M') or even nautical miles ('N').

Calculate distance between coordinates using Google Maps in Excel

To get Google Maps distance between two coordinates simply use the same GetDistance function as above and replace the start and dest parameters with the coordinates in this format:

Final call example:

Same goes for the duration function:

How to set it up in Excel?

Important: Please remember that you need a direct Internet connection! Not via proxy etc.
Follow the steps:

Add new VBA Module

Go to the DEVELOPER ribbon and add select Visual Basic a and new Module to your VBA Project

Insert the VBA code

Insert the code from sections above (notice that the function is “Public” – therefore will be visible in your workbook as a so called UDF (User Defined Function)

Input the function in Excel

Go to the worksheet and input the function as shown below:

Make sure to replace ; with your default formula list separator (e.g. comma in the US)!

Calculate distance using Google Maps between any number of coordinates/addresses in Excel

Now that we know how to leverage our newly learned GetDistance and GetDuration functions we can use them to measure the distance/duration for any routes, with any coordinates/addresses in between our starting point and our destination. For this I created 2 simple procedures MultiGetDistance and MultiGetDuration:

Get distance between any number of locations

By using our GetDistance function we can get the distance between any number of locations using the Visual Basic procedure below:

Below example usage:

And here is the output:

Get duration between any number of locations

Similarly by using our GetDuration function we can get the duration between any number of locations using the Visual Basic procedure below:

Below example usage:

Parameters for calculating the Google Maps Distance

The following parameters are available in the API query:

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In China

For other parameters see the original Google Distance Matrix API page:
The Google Distance Matrix API

Limits and common issues

Read more on the limitations of the Google Distance Matrix API here:
The Google Distance Matrix API

Google limits the amount of free queries to the following:

• 100 elements per query.
• 100 elements per 10 seconds.
• 2 500 elements per 24 hour period.

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Location

So beware of any mass recalculation of these functions as you may quickly find yourself exceeding these limits.

Common issues

The functions above are not full proof and don’t take into account:

• Be sure to obtain a Google Distance Matrix Key!
• In case of errors be sure to check if you are sitting behind a proxy server. If so check here how to configure a proxy
• That Google won’t find the exact addresses and will approximate with a similar address (see component filtering for more precision)
• That Google might return several matches. While the function takes the first one from the returned list
• HTTP or timeouts. See my Web Scraping Kit for how I dealt with such
• That distances/duration differ depending on which location is set as origin and which is set as destination (one way roads, detours etc.)

Having trouble with matching a large dataset with distances and durations? Reach out for a free quote.

Download an example

You can download an example of the above below and support AnalystCave.com:

Help!

Need help with calculating the distance between two addresses using the approach above? Feel free to comment below or ask a new Question via Questions page.

Next steps

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Change

Want to get the geolocation (coordinates) of any address?
Excel: Get geolocation (coordinates) of any address
Want to add maps and other cool Google Charts in Excel?
Excel: Google Charts Tool
Want to use Google translate in Excel?
Excel: Google Translate functionality

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Labels

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address List