Excel VBA: Google Map Analytics

Determine the optimal location is an important factor for the success of a business. Imagine that you have a list of potential customers or suppliers, and you want to determine what is the best place to built a warehouse or an store. Well, now it is possible using excel. First, you need a list of addresses, you have to download a VBA program, and run excel solver. Jamie Bull created a VBA program (the link is below) that allow us to get directly in excel the same information that we can get from Google maps inserting the addresses. Some of the list of commands are here:  

• Distance in Miles (By Car):   =G_DISTANCE(Address 1, Address 2)
• Latitude: =L_LAT(Address)
• Longitude: =G_LONG(Address)
• Time in hours (By Car): =G_DURATION(Address 1, Address 2)
• Address: =G_ADDRESS(Latitude&Longitude)

Having the latitude and longitude, we have to determine what are going to be the main factors at the time to choose a place (volume, distance, time, etc). Here I attached one excel example using distance as a main factor. To get the optimal point I used solver. A detail explanation of how to setup excel can be found in Wayne Winston's book (link here). For the map of the optimal point I used Google Fusion Tables.  

If you found this helpful, please share.

Source of the VBA code
Excel Function Maps