Drive Time Calculations in Excel

Building on my most popular post, Getting Information From the Web Using Excel VBA, I had the need in a recent project to calculate drive times on many rows of data, and decided to build a function in Excel to handle the dirty work for me using Google Maps.

Essentially, we had 200 or so latitude/longitude points and needed to see which of six addresses were closer from a drive time perspective. Luckily, Google came to the rescue once again, because one can use latitude/longitude coordinates in lieu of an address in order to get directions (which includes drive time) to a physical address (or, I suppose, a second set of latitude/longitude coordinates). What’s more, the URL for google maps to give you this information is fairly simple:

http://maps.google.com/maps?q=from: [PointA] to: [PointB]

So, once I dug through the HTML code behind the google maps directions results to find the div element containing the drive time of the quickest route, it was easy enough to create the following function. The two parameters are the addresses, zip codes, coordinates, or whatever else Google will allow you to use to approximate the starting and ending points.

Function DriveTime(PointA As String, PointB As String)

  Dim myURL As String
  myURL = _
    "http://maps.google.com/maps?" & _
    "&q=from: " & PointA & " to: " & PointB

  Dim inet1 As Inet
  Dim mypage As Variant

  Set inet1 = New Inet
  With inet1
    .Protocol = icHTTP
    .URL = myURL
    mypage = .OpenURL(.URL, icString)
  End With
  Set inet1 = Nothing

  Dim intStart As Double, intEnd As Double
  intStart = InStr(mypage, "<div class=""altroute-rcol altroute-info"">") + 41
  intEnd = InStr(intStart, mypage, "</div>") - intStart
  DriveTime = Mid(mypage, intStart, intEnd)

End Function

It took about 45 seconds to calculate the drive time for appx. 6 * 200 or 1,200 routes. My only other option was to copy and paste those 1,200 coordinates one-by-one into Google maps and retype the drive time. Which probably would have taken at least half a day. I call that a win.

Feel free to use this function for any number of drive time calculations, but I would assume that at some point Google will pitch a fit that your IP is sending so many requests to its map server. However, it didn’t bat an eye at my 1,200 queries, so who knows? Enjoy!

Advertisement