Ordnance Survey FAQ and Excel Mapping Routines

Excel Conversion Code

 

Routines for Microsoft Excel ™ are available which will perform a number of functions including:

  • Conversion between OS references, eastings and northings, WGS84 latitude and longitude, and OS datum latitude and longitude.
  • Distance between OS references.
  • Compute variation (true, grid or magnetic).
  • Compute range and bearing between points
  • 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.
  • Generate a kml file for displaying data on Google Earth
  • Give locations of postcodes/zipcodes
  • Show HELP information

The routines are available here:

 

Download OSGB.xla – the addin osgb.xla which holds the code and…

Download OSGBExamples.xls – the example workbook OSGBExamples.xls which includes an installer for the addin.

Download both files.

You will need to allow macros in Excel, then run the example workbook and allow it to install the addin.

Full examples are given in the examples workbook, including a worked example of a distance/bearing calculator.  Once installed, the addin will subsequently be available automatically to all workbooks.

The routines have been developed under Excel 2003 and have had limited testing with Excel 2007 and  Excel 2010.

Accuracy of these routines is 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.  I have found that they agree, invariably to the exact metre, with the conversions performed by my old Garmin 38 GPS receiver, and Garmin confirm that the accuracy of their conversions is about 1 metre.  This meets my recreational needs though I am now two generations beyond the Garmin 38!

The routines have not been designed, written or validated for professional work, though 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 used by those involved in coordinating rescue services, for weather studies on Arran, butterfly studies in North Wales, tracking basking sharks off Western Scotland and managing features on the national electricity grid!   I have also received an unsolicited email from Ordnance Survey describing earlier versions of this code as a fine piece of work.

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.

 

Phil Brady © 2012