Excel Cartographic Code
Routines for Microsoft Excel ™ are available which will perform a number of functions including:
- Import data from a gpx file. These files are now the standard interchange mechanism for waypoints, routes and tracks for gps devices.
- Compact gpx data by removing duplicated or redundant points. Huge savings are possible without significant loss of detail.
- Export data to a gpx file for download to a gps device or for display on a mapping application.
- Generate a kml file for displaying data on Google Earth
- Give locations of postcodes/zipcodes
- Convert between GB Ordnance Survey references, eastings and northings, WGS84 latitude and longitude, and OS datum latitude and longitude.
- Compute angular variations (true, grid or magnetic north).
- Compute range and bearing between points
- Show HELP information
The routines are supplied in an addin. An examples workbook is also supplied which demonstrates the use of all the functions and also includes routines to install, remove or check the status of the addin.
For versions of Windows Excel 2007 or later or for MAC Excel 2016/2019 please use these.
– osgb.xlam – the addin which holds the basic code and …
– osgbExamples.xlsm which has examples and includes an installer.
For early versions of Excel 1997~2003 which do not include the ‘ribbon’ interface you will need the deprecated code first published in 2012.
– the addin osgb.xla which holds the code and…
– the example workbook OSGBExamples.xls which includes an installer for the addin.
Download the two relevant files.
You will need to run the example workbook, allow macros, allow it to install the addin, accept the licence conditions, then self-edit all the functions in the workbook. For MAC, further steps, including a simple manual one is required.
Once installed, the addin will subsequently be available automatically to all workbooks.
Full examples are given in the examples workbook.
The ribbon versions have been developed under Windows/Excel 2016 and MAC Excel 2016 and 2019. The deprecated routines were developed under Excel 2003 and have been tested with (Windows) Excel 2007, Excel 2010 and Excel 2016 though the ribbon version is recommended.
Accuracy of these routines is to a couple of metres – well within that of a consumer quality GPS, even without selective availability, well within one’s ability to read a 1:25000 map, and within the limits of stability of paper maps.
The routines have been used by a borough in Kent for plotting their drains, have been used for microlight championships, are recommended by the British Atmospheric Data Centre, have been used by those involved in coordinating rescue services, for weather studies on Arran, butterfly studies in North Wales, bird migration, tracking basking sharks off Western Scotland, in London bio-conservation projects and managing features on the national electricity grid! I use them for plotting walks with both Garmin Basecamp and Google Earth. I have also received an unsolicited email from Ordnance Survey describing earlier versions of this code as a fine piece of work.
The routines are provided ‘as is’ with no warranty. They are free for personal non-commercial use.
The latitude/longitude to OS reference conversion routines use the algorithms published by a former colleague at Swansea University, the late Dr Derek Maling, though I must claim credit for any errors in implementation.
I am greatly indebted to Ian Baines for his considerable help, perseverance and encouragement in porting OSGB and developing the additional necessary code for MAC. Without his help (and his computers!) it would not have been possible.