This post is a nifty tool for tracking the distance travelled by building materials from source to site. I’ve been writing a few bits of code lately which have required using Google tools, so I thought I’d write up some notes here. This is for my information than anything else but may come in handy for others too. 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 kilometres. If any kind of error occurs then the function returns 0.

Using the function

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.

The code

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 ).

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools. The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects. This post […]

Leave a Reply

Your email address will not be published. Required fields are marked *