A Google Maps journey time function for Excel

Posted on by Jamie Bull

I had a comment from Gabriel on the Google Distance post, asking if it could be adapted. After a bit of back-and-forth by email with Google Translate as an intermediary (Gabriel is Brazilian and my live-in translator/interpreter/girlfriend is in Brazil too so she couldn’t help), I figured out that what Gabriel wanted was to return the time taken rather than the distance.

This sounded like it might be a useful addition – you’d be able to work out things like average speed travelled. And it also turned out not to be too difficult, needing just a few changes to the function.

The first one is to change "//leg/distance/value" to "//leg/duration/value". That was easy enough to find out by referring to the XML schema for the Google Maps Directions API.

The other main thing to handle is that the Google API returns the duration in seconds, which needs converting into a more useful format. I decided to give two options with this – to return it as decimal hours where 1 1/2 hours is represented as 1.5 hours, and to return it in Excel’s date format where 1 1/2 hours is shown as 01:30:00. I chose the second as default.

The syntax for using the function is as follows:
=G_TIME(Origin, Destination) to return the time taken in Excel time format (you’ll need to format the cell to make it display correctly).
=G_TIME(Origin, Destination, "Decimal") to return the time taken as a decimal.

Function G_TIME(Origin As String, Destination As String, _
    Optional Format As String = "Date") As Double
' Requires a reference to Microsoft XML, v6.0
' Draws on the stackoverflow answer at bit.ly/parseXML
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim timeNode As IXMLDOMNode
    G_TIME = 0
    On Error GoTo exitRoute
    ' Check and clean inputs
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    ' Read the XML data from the Google Maps API
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    ' Make the XML readable usign XPath
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ' Get the time node value
    Set timeNode = myDomDoc.SelectSingleNode("//leg/duration/value")
    If Format = "Decimal" Then ' Return as a decimal - 30 mins as 0.5 hrs
        G_TIME = timeNode.Text / 3600 ' Seconds in an hour
    Else 'Return in Excel's 00:00:00 date format - 30 mins as 00:30:00
        G_TIME = 1 + timeNode.Text / 86400 ' Seconds in a day
    End If
exitRoute:
    ' Tidy up
    Set timeNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

Heat maps with Python and Inkscape

Posted on by Jamie Bull

Mel Starrs tweeted a week or so ago about using GIS software to do mapping and energy analysis. I wanted to do the something similar but for sub-metered zones in a multi-tenanted office building.

Rather than go down the route of learning a new package, I remembered a chapter on creating choropleth maps in Nathan Yau’s fantastic Visualize This. This post walks you through the process of creating the image above and the code draws heavily on the code in the book.

Organising the data – Excel

The first step is to gather your energy data. The Python script below requires this to be in a CSV file, organised into two columns. The first column holds the names of all your zones and the second column contains the energy data you want to visualise. In the example above this is kWh/m2/yr. Just create the file in Excel and save as CSV.

Creating the base image – Inkscape

I used Inkscape (free SVG editing software) to create the outlines of the zones in the building, but you could use Illustrator or any other vector graphics editor that produces SVG files. Not having used this type of software before, this is what took the most time for me. If you want to use this technique for mapping you’ll probably be able to track down a vector graphic of the geographical areas your interested in which will save you quite a bit of time.

To create the image I imported a JPG plan of the building, knocked the opacity down, and then traced over it with the Draw Bezier curves and straight lines tool (shortcut Shift+F6). Then I named each of the zones. Right-click on the path > Object Properties > Change the Id field to match the zones name in your CSV file.

A couple of tips learned the hard way:

1) Text is really odd in this software. After you’ve typed it in, you then have to convert it to paths by selecting it and hitting Ctrl+Shift+C. If you don’t do this it will show up in a random font, if it shows up at all. Even then, it doesn’t seem to line up as well in the browser as it does in the editor.

2) It’s pretty hard to get things to line up. I just couldn’t seem to get the snapping to work. In the end I found a solution was to draw the shapes quite roughly then use the Edit path by nodes tool (shortcut F2), select a vertex or a edge, and manually enter the XY values to match up with the adjacent zone.

Data wrangling – Python

The Python module does the real work. It needs you to have Python installed and BeautifulSoup which is used to parse the XML (SVG files are a form of XML – try opening one up in a text editor to see if you haven’t done so before).

You’ll need to change one or two things. First, you’ll need to set the folder variable to the directory where you want to save the finished file. Second, you may want to change the formula which decides what colours correspond to what values.

My data had quite a lot of small values and a few larger ones so I used an exponential formula. If your data is more evenly distributed then a linear formula might work better.

Python code

import csv
import math
import tkSimpleDialog
import tkMessageBox
import tkFileDialog
from BeautifulSoup import BeautifulSoup

# Set this to the directory you want to save the file in
folder = 'C:/Directory/Path/Goes/Here/'
# Set up colour scale
colours = ["#FFFFFF",
           '00FF00', '33FF00',
           '66FF00', '99FF00',
           'CCFF00','FFFF00',
           'FFCC00', 'FF9900',
           'FF6600', 'FF3300']
# Set up style for zone outlines
path_style="stroke:#000000;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1;fill:"
# Open files to work with
csvFile = tkFileDialog.askopenfilename(filetypes=[("CSV", ".csv")], title="Open")
reader = csv.reader(open(csvFile,'r'), delimiter=",")
svgFile = tkFileDialog.askopenfilename(filetypes=[("SVG", ".svg")], title="Open")
svg = open(svgFile, 'r').read()
# Create dictionary
energyRecords = {}
for row in reader:
    try:
        room = row[0]
        kWh = row[1]
        energyRecords[room] = kWh
    except:
        pass
# Parse xml with BeautifulSoup
soup = BeautifulSoup(svg, selfClosingTags=['defs','sodipodi:namedview'])
paths = soup.findAll('path')
# Get kWh/m2/yr value for zone
for p in paths:
    if ('path'in p['id']): # not a named zone
        continue
    else:
        if (energyRecords[p['id']] == ''):
            kWh = ''
        else:
            kWh = int(energyRecords[p['id']])
# Set colour for zone
    if kWh == '':
        colour_class = 0
    else:
        # Logarithmic formula goes from colour_code 1 at 10 kWh/m2 to colour_code 10 at around 500 kWh/m2
        # You may want to set your own rule here to best display your data distribution
        colour_class = min(int(math.floor(2.3423 * math.log(kWh) - 4.3404)), 10)
    colour = colours[colour_class]
    p['style'] = path_style + colour

# Output the edited SVG file
outputFile = tkSimpleDialog.askstring("Save", "Enter a name for your file")
f = open(folder + outputFile + ".svg", "w")
f.write(soup.prettify())
f.close()

tkMessageBox.showinfo("Close", "SVG file created.")

The result


And that’s it. Just run the Python code, either from an IDE or from the command line, pick your CSV and SVG files when prompted, enter a name for your file and you’re done.

You can view your finished SVG file in a web browser, which means you can easily upload it and share it around.

“My location” function for Excel

Posted on by Jamie Bull

If you downloaded the example file I posted earlier on today, as well as a few updates to the simple versions of the Gmaps and geolocation functions for Excel, you may have noticed that one of the places on the clickable list of locations was “My location”.

This works using Wikimedia’s geoiplookup service. When I visit http://geoiplookup.wikimedia.org I get the following:

Geo = {"city":"Erith","country":"GB","lat":"51.483299","lon":"0.200000",
"IP":"188.223.229.118","netmask":"24"}

This code module downloads the response from Wikimedia and parses it into an array.

Function GeoIP()
' Developed by Jamie Bull at oCoCarbon - www.ococarbon.com
' Uses geoiplookup.wikimedia.org to get the location of the local IP address
' Requires a reference to Microsoft XML, v6.0
Dim myRequest As XMLHTTP60
Dim Json As String
Dim tmpArray1 As Variant
Dim tmpArray2 As Variant
Dim i, n As Long
    On Error GoTo exitroute
    Set myRequest = New XMLHTTP60
    ' Get Json response
    With myRequest
        .Open "GET", "http://geoiplookup.wikimedia.org"
        .Send
    End With
    Json = myRequest.responseText
    ' Parse Json
    n = InStr(Json, "{") + 1
    Json = Mid(Json, n, Len(Json) - n)
    tmpArray1 = Split(Json, ",")
    ReDim tmpArray2(UBound(tmpArray1), 1)
    For i = 0 To UBound(tmpArray1)
        tmpArray2(i, 1) = Replace(Split(tmpArray1(i), ":")(1), """", "")
        tmpArray2(i, 0) = Replace(Split(tmpArray1(i), ":")(0), """", "")
    Next
    GeoIP = tmpArray2
exitroute:
    Set myRequest = Nothing
End Function

We can then query that array with a number of UDFs:

=My_City() returns the city.
=My_Country() returns the country.
=My_Location() returns the city and country.

=My_Lat() returns the latitude.
=My_Lng() returns the longitude.
=My_LatLng() returns the latitude and longitude.

=My_IP() returns the IP address.

' UDFs to return values
Function My_City()
    My_City = GeoIP(0, 1)
End Function

Function My_Country()
    My_Country = GeoIP(1, 1)
End Function

Function My_Location()
    My_Location = GeoIP(0, 1) & ", " & GeoIP(1, 1)
End Function

Function My_Lat()
    My_Country = GeoIP(2, 1)
End Function

Function My_Lng()
    My_Country = GeoIP(3, 1)
End Function

Function My_LatLng()
    My_Country = GeoIP(2, 1) & ", " & GeoIP(3, 1)
End Function

Function My_IP()
    My_IP GeoIP(4, 1)
End Function

The result from this is not particularly accurate. It will locate your general area reliably, unless you’re accessing the internet from behind something that masks your real IP address, but it won’t give you a precise location.

If I can figure out how to get the results of the code on this page here into Excel then I’ll make an update. This is using local wifi signals to locate your machine, just like your smartphone does. Unfortunately the code only works in Firefox or Chrome. The default browser object in Excel is Internet Explorer so it’s not simple to run.

It’s implementing some code from Samy Kamkar.

Google Maps and Excel download

Posted on by Jamie Bull

I was asked for a downloadable file with the G_LATLNG by Filipe who was having trouble getting the function working. Since I posted that, I’ve done a bit of further work to improve it. The workbook you can download caches results from G_LATLNG and G_DISTANCE queries to reduce the amount of time spent polling Google. This also helps to avoid hitting the buffers in terms of how many queries they allow you – 2,500 per day, but they also seem to have soft limits over shorter time periods.

Download example file.

There are a couple of other new features. G_LATLNG now takes an optional second argument allowing you to return just the latitude or longitude. The syntax is =G_LATLNG(Location, optionNum). If you enter optionNum as 0 you get both (this is the default if you don’t specify), 1 you get just the latitude, 2 just gives you the longitude.

The WebBrowser worksheet shows you the difference between a regular Google Map and a Google Static Map. Click on locations in the list and it will update the map automatically. You can play around with the parameters and see what they do. You can also replace the places in the Locations table with your own ones.

Put a Google map in your spreadsheet

Posted on by Jamie Bull

To top off the recent Gmaps and Excel posts, I thought I’d show you how to embed a map in a worksheet. This involves three main steps.

1) Inserting the web browser object
The first step is really simple. Just go to the developer tab on the ribbon and click the “More controls” icon – it’s the one that looks like a crossed hammer and spanner/wrench. Select the Microsoft Web Browser from the list of ActiveX controls, and then click and drag on the worksheet to place your web browser object.

2) Generating the URL to show your desired map
Probably the simplest thing to show in your browser is an embedded map with a postcode. This is the easiest way to show a marker on the map.
The simplest format for the URL is as follows:

http://maps.google.co.uk/maps?q=your_postcode_goes _here&output=embed&iwloc=near

Once your map is showing, you can interact with it just like a regular Google map page, zooming in and switching between map modes. You can even go into Street View or Google Earth.
There are loads of other parameters you can add to fine tune the results but this is as simple as it gets. You can find more info here, here or here for things like adjusting zoom level, showing hybrid maps and so on.

The limitation of this approach is it only allows you to show one marker. If you want to show more than one you can display a static map, at the cost of no longer being able to interact with it. The API for this is pretty simple. The basic format of the URL is as follows:

http://maps.google.com/maps/api/staticmap?&center=your_location&zoom=19&size=525x330&maptype=roadmap&
markers=color:red|label:A|your_latitude,your_longitude&markers=color:green|label:B|your_latitude,your_longitude&
sensor=false

Again, there’s plenty you can do to jazz up the display including replacing roadmap with satellite or hybrid.

3) Adding the VBA code to navigate to your URL
This might seem to be the most complicated part if you’re not familiar with VBA, but it’s actually pretty simple and really worth having a go at. Adding a link to a webpage such as a Google map can make a really big difference to the appearance of your spreadsheet.

There are two parts to the code, a sub to control and a function to ping Google and make sure you have a live connection to the internet. There’s nothing worse than showing your fancy new dashboard to a client, only to see an Internet Explorer error page. Checking the connection first means that you can hide the web browser to reveal your own error image sitting behind it.

Sub ConnectToUrl()
If bPing("74.125.79.94") Then 'Ping the IP address of google.co.uk.
    With ActiveSheet.WebBrowser1
        .Visible = True
        .Silent = True
        .Navigate ActiveSheet.Range("$B$4").Value
    End With
Else
    ' Hide the browser object to show your own error message
    ActiveSheet.WebBrowser1.Visible = False
End If
End Sub
Function bPing(sHost) As Boolean
Dim i As Integer
Dim oPing As Object, oRetStatus As Object
For i = 1 To 2 ' Attempt up to twice to allow for connection hiccups.
    Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
            ("select * from Win32_PingStatus where address = '" & sHost & _
            "' AND Timeout = 100")
    For Each oRetStatus In oPing
        If IsNull(oRetStatus.statuscode) Or oRetStatus.statuscode <> 0 Then
            bPing = False
        Else
            bPing = True
            Exit Function
        End If
    Next
Next
End Function

Embodied energy and embodied carbon of water

Posted on by Jamie Bull

A lot of people find this blog through the post about the embodied carbon of tap water so I thought I’d do a quick follow-up post.

I found the water industry body, Water UK’s sustainability reports covering the period from 2001/2002 up to 2010/2011. This gave me all the data I need to update the old post, and also to put the figures in some sort of context.

Click on the table for a larger view.

I’ve added sparklines to the table of data to show the change in the indicators over time. It is a shame that several of the indicators used by Water UK have been discontinued and new ones have come in in their place which makes for some difficulties in comparisons. It’s interesting to note just how static annual household water use has been over the past 10 years, but unfortunately that indicator has now been phased out, in preference for a return to average daily use in metered and un-metered homes.

The embodied energy of water has varied quite a lot over the past decade, falling by 28% between 2003 and 2007, before climbing back up a little in recent years. Embodied carbon is down slightly, but shows no sign of a continuing decline. Water UK attribute the recent rise to an increase in the carbon factor of electricity, which makes up the majority of fuel use in the water industry.

The tables show that my previous rough calculations were not too far out, but certainly worth updating. The figure I arrived at before was 0.59 gCO2/litre. The Water UK figures are between 30% and 46% higher with the latest figures coming out at 0.79 gCO2/litre. The question this raises is where did my previous calculation go astray?

Postcode finder

Posted on by Jamie Bull

Now that you have the G_LATLNG function for your Excel sheets, what is that good for? Well you can use your geocoded value to display your location on a map. I’ll get to that later on, but for now you can also use it to look up postcodes, thanks to a file I obtained from Nearby.org. I’ve repackaged it here along with a function which will tell you what postcode area a given latitude/longitude pair lies in.

The postcode data given excludes the final two letters – just returning SW8 1, not SW8 1XX for example. I can’t guarantee that it will get it right every time. The calculation just tells you which is the closest centre point of a postcode area, so oddly-shaped areas may turn up some errors. I’ve been using it for running through a large dataset and sorting for particular London Boroughs though, and it seems pretty effective for that task.

The file attached contains the postcode data as well as the demo table shown above. The UDF called POSTCODE is also in there if you want to use it in your own spreadsheets.

Download Geocoder and postcode finder.

Google geocoding for Excel

Posted on by Jamie Bull

As with the previous post, I’ve been writing some code lately using Google tools. This post is a rewrite of some heavier code for geocoding location data. The aim is to get latitude and longitude which is then used to display points on a Google Map (more on that in a later post). It’s used as part of a dashboard which can show data about various buildings to give an obvious cue to the user as to which one they are currently viewing.

The function for Excel takes a single argument, a place name or postcode. It then grabs the latitude and longitude from Google and returns them to 4 places separated by a comma and a space as follows:

51.4862, -0.1229

I’ve named this UDF G_LATLNG and the syntax for using it is =G_LATLNG(Input).

Note that the Google Maps API terms of service state that you can only use the API when displaying a Google Map, so using this code without doing so is at your own risk. Look out for a future post here on how to do that.

The table shows the same ways of inputting data are available as with G_DISTANCE.

Function G_LATLNG(myInput As Variant) As String
' Requires a reference to Microsoft XML, v6.0
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim latNode As IXMLDOMNode
Dim lngNode As IXMLDOMNode
    G_LATLNG = 0
    On Error GoTo exitRoute
    ' Check and clean inputs
    If WorksheetFunction.IsNumber(myInput.Value2) Or IsEmpty(myInput) Then Exit Function
    myInput = Replace(myInput, " ", "%20")
    ' Read the XML data from the Google Maps API
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?address=" _
        & myInput & "&sensor=false", False
    myRequest.send
    ' Make the XML readable using XPath
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ' Get the latitude and longitude node values
    Set latNode = myDomDoc.SelectSingleNode("//result/geometry/location/lat")
    Set lngNode = myDomDoc.SelectSingleNode("//result/geometry/location/lng")
    G_LATLNG = Application.WorksheetFunction.Round(latNode.Text, 4) & ", " _
        & Application.WorksheetFunction.Round(lngNode.Text, 4)
exitRoute:
    ' Tidy up
    Set latNode = Nothing
    Set lngNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

You’ll need to install this as a code module in your workbook, and to set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).

Google Maps distance function for Excel

Posted on by Jamie Bull

I’ve been writing a few bits of code lately which have required using Google tools. I thought I’d write up some notes here for my information than anything else but may come in handy for others too. This post is a nifty tool for tracking the distance travelled by building materials from source to site. Used in conjunction with the carbon factors per tonne km from Defra, this allows a decent estimate for the transport stage of a lifecycle carbon footprint assessment.
The custom function for Excel works by entering two place names – or postcodes – and finding the shortest route between them using Gmaps route planner. I’ve called the UDF G_DISTANCE and the syntax for using it is =G_DISTANCE(Origin, Destination). It returns a value of the distance between the two locations in miles. If any kind of error occurs then the function returns 0.

This table shows the various ways you can use the function. You can also mix and match the types of input parameter you use. For example you may the site location precisely – either by latitude and longitude or postcode, but only know the city or even the country of origin of the materials. That’s fine – the function should still give you the right answer, or at worst return zero.

Two note of warning:
1) The Gmaps terms of service state that you are only allowed to use the API when displaying a map, so using this without doing so is at your own risk (hence the map above…).
2) Gmaps will generally give you a road-based route which may not be the most appropriate. For example cork from Portugal would probably be shipped rather than delivered all the way by HGV. If anyone has a better function for these cases please get in touch.

Function G_DISTANCE(Origin As String, Destination As String) As Double
' Requires a reference to Microsoft XML, v6.0
' Draws on the stackoverflow answer at bit.ly/parseXML
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    ' Check and clean inputs
    On Error GoTo exitRoute
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    ' Read the XML data from the Google Maps API
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    ' Make the XML readable usign XPath
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ' Get the distance node value
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
exitRoute:
    ' Tidy up
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

As ever, you need to install this as a code module in the workbook you want to use it in. It also requires that you set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).

MS Word macro for subscript/superscript formatting

Posted on by Jamie Bull

Mel Starrs has just posted a handy time-saving tip over at Elemental which explains how to use MS Word’s autocorrect feature to ensure that your CO2 subscript always comes out right. It’s a handy approach and saves your work from the annoying critic who ignores your carefully crafted argument while pointing out every last formatting error.

My contribution here is from the point of view of someone who edits quite a lot of other people’s reports as well as writing my own, in which case you can’t rely on the autocorrect method. Instead I hunted around on the net for a way of correcting it automatically using a macro. Eventually I found what I was looking for on G Mayor’s site . It needed a bit of adaptation to do exactly what I wanted but it’s now a real time saver. It covers:

  • m2
  • m3
  • R2
  • CO2
  • NO2
  • SO2
  • H2O

Just download and unzip the file from the link below, then import it to your Normal template in MS Word.

FormatFormulae.

To add the macro to the Normal template open up MS Word and press Alt-F11 to open the VBA editor window —>  Ctrl-R to show the project explorer (if it isn’t already showing) —> right click the Normal template —> Import file —> browse for the FormatFormulae.bas file —> Open, and you’re done.

It’s so handy that I also decided to add it to the Quick access bar for easy access (MS Word 2007 and 2010 only).

To add a button to the Quick Access Toolbar in MS Word 2010, first click on the down arrow at the right hand end of the row of icons that includes the Save icon, then select More commands —> Choose commands from —> Macros. Then find Normal.FormatFormulae.CommonFormulae in the list that appears and use the Add >> button to move it across to the Quick Access Toolbar.

Next you can add an icon so you can find it easily. I’ve hijacked the π symbol which normally serves as the insert formula button but you can add whatever makes sense to you. Just click Modify and select an icon from the options that appear.

If you’re confident messing about with VBA for Word, the code is commented to show you how to adapt it to cover any other subscript/superscript formulae that you use regularly.