1. Skip to navigation
  2. Skip to content
  3. Skip to sidebar

Our Blog

GIS for the rest of us… or Another Excel-based Mapping Tool

Zach Gemignani

A client recently asked us if we could help him find the five locations that have the most customers nearby. I dug into the annals of our blog to find two tools that together (“wonder twin powers activate… shape of mini-GIS solution”) could solve this problem:

1. Juice geo-coding tool lets you get the precise latitude and longitude for a list of addresses and plot these locations in Google Earth.

2.ZIP code finder lets you enter a US address and returns zip codes within a certain number of miles of that address.

Led by our resident Excel guru David, we combined these features into an Excel tool that lets you answer common location-based questions such as:

  • How many libraries are within 10 miles of Worchester, MA?
  • Which cities have the most libraries within 10 miles? 20 miles?
  • Could I see the library locations on a map, please?

Here’s how it works:

1. Drop your location information into the “Geocode data” tab. We are using the Yahoo geocoding service.

2. Go to the results tab and fill in the table with your selected location addresses and distances. Press “Calculate.”

Download the Excel file here: Juice Distance Tool

We had a couple other features in mind, but thought it would be better to get some reader reaction before we loaded it up.

This tool is released under the MIT license.

Related posts
Topics:
, , ,
  • Bazily

    Cool tool. I built a geocoding tool for the web off of Google Maps and Yahoo Geocoding API, hosted here:
    http://www.bazily.com/freegeo

    It’ll do thousands of geocodings in a snap!

    Recently I was asked to use that experience to figure the distance between 5,000 retail locations and 30,000 competitors. Awesome.

  • Hagge

    Doh.

    F-Secure thinks the Excel-file contains an unknown virus.

  • Hagge

    I submitted the file for whitelisting, or whatever it is that they do to it, and the next f-secure database-update will fix the erroneous detection.

  • Chris

    This is an excellent tool that offers me a quick way to visualize my data. However, the location names do not seem to load into the Google Earth file. I’m using Excel 2003 on XP.

  • Aran

    Cool file. I have a long list of zip codes that I would like to experiment with. I wonder if there is a way to tweak the file so that it will geocode a zip code if the other address cells are left blank.

  • http://www.juiceanalytics.com Zach

    Last I tried, Yahoo’s geocoding service (the one baked into this tool) returns a lat/long value for just a zip code.

  • kelvin

    This is a GREAT TOOL! but ya, I can’t see the site names from the JUICE tool…however, geocode 3.1 xls works, BUT it ends with a debug error after it completes the macro. the end result file provides the site names tho, which is VERY HELPFUL for me! is there a way to plugin the address for each site name/location? that would be ideal.

    I use this tool to help me assess the number of apartment complexes in a given area. VERY USEFUL for me to get a quick idea of competition in a particular area i’m looking to invest in!

  • Jerry

    Chris (#4) It has nothing to do with Excel 2003. The outputKML macro is referring to wrong column numbers. I changed my Geocode tab to make the Name/Description column into two separate columns & then changed two lines to reflect it:
    name = CStr(ActiveSheet.Cells(r, 7))
    description = CStr(ActiveSheet.Cells(r, 8))

  • Jerry

    If you’re behind a firewall, the calls to http.send will fail ….

    Function yahooAddressLookup(addr As String, Optional Row As Long) As String
    ‘ perform RESTian lookup on Yahoo
    Dim response As String

    URL = “http://local.yahooapis.com/MapsService/V1/geocode?appid=yahoo_test&location=” & addr

    ‘Create Http object
    If IsEmpty(http) Then Set http = CreateObject(“WinHttp.WinHttpRequest.5.1″)

    ‘Send request To URL
    http.Open “GET”, URL

    http.send
    ….
    ….

    Only way around it is to add code for http options to work with proxy authentication

  • Eric

    Do you guys know how I can implement something similar to your Excel geocoder into MS Access where I can use a table of address records instead of a sheet of address rows in Excel.

  • http://www.cogniview.com/pdf2xl.php pdf to excel

    HI, 

    Thanks guys for sharing information about ZIP code finder  tool, I needed of it. 

  • http://www.mapserverpro.com/ host gis

    You will know the facts, geographical information systems is very expensive, can not be the majority of enterprises and organizations where given. This is a geographic information system service providers come into play.

blog comments powered by Disqus