ShipPlotter AIS Reception Analysis &
Website Upload Spreadsheet/Program
User Guide for ShipPlotter.xls by Neal
Creates a list of current ships in
CSV and/or HTML and/or XML and/or KML format
Dynamically graphs No of ships, Message rate and
Uploads any/all of above to a
The AIS data must be received by the COAA ShipPlotter
Excel 97, 2000, 2002, 2003 or 2007 must be installed.
Windows 98 or later.
Click this link, with
ShipPlotter running, open the spreadsheet & enable macros.
Excel will display a screen similar to this, but with no points on
After 5 minutes (the default setting) the first point on the graph
will be plotted and the number of messages received since startup
will be listed above the graph.
Features (Click the links to see
the current files for Scarborough)
At a user defined polling interval the COAA ShipPlotter program is
The polling scheduled is controlled within Excel eliminating the use
of the Windows task manager.
The AIS data received is displayed in a graphical format.
You can choose to list current ships in CSV and/or HTML and/or XML
and/or KML format.
Any or all of the above formats may be automatically uploaded to a
webserver of your choice.
The Graph may
optionally be uploaded to a webserver
list contains all the message data currently being decoded by the
COAA ShipPlotter program.
sign,IMO no,ETA,Beam,GPS position fwd,GPS position port,Time last
message received, Share code ais data,Range,Bearing,CPA & CTA)
list can be viewed with a browser on your PC or by users visiting
your website. If you wish to display a different subset of the ship
data received, this is done by altering the content on one sheet of
the Excel workbook. Similarly it is simple to alter the format with
a basic knowledge of HTML.
list may be used to interface with Google maps, click here to view the webpage when rendered
by Google maps. The default format I have set up will interface with
the HTML sample webpage vbship4.zip which can be downloaded from the
files section (vbscripts) of the ShipPlotter
The KML list again will interface with Google
maps, in this case you can use the Google server to combine the
ship's data with a map. This is done by just going to a link
directly in this case http://maps.google.com/maps?q=http://web.arundale.co.uk/docs/ais/data.kml?
The default format is almost as simple as it can be so that you can
see the wood from the trees.
Optionally, you will be informed when you start the program if I
have uploaded a new version of the Excel code (Win 2000 on).
If you click on the Data
tab (at the bottom on the Excel screen) you will see a list of the
ships, somthing like below.
If you click on the Summary
tab you will see a summary of the data something like this
The HTML, XML and KML tabs are used for
uploading the data to a website, for more information go to AIS Data Analysis Website Upload
ShipPlotter > Options
There are a number of options which affect the way you will
see the data being received. These are simply changed by selecting
the options box from the Excel menu bar.
The Options box will be displayed
||Refresh is the
frequency ShipPlotter will be polled and the Graph updated,
set to 0 stop polling
Graph Range sets the
number of points that will be displayed on the time axis of
the graph. By default the last 500 entries are kept so you
can change this to display a larger number of points
Message Rows are the
number of rows of data that will be displayed above the
graph. The default is the last 5.
Max Ship Range is
the maximum range ships ais data will be loaded into the
spreadsheet from the Shipplotter data. This is of use
when TCP messages are being received by shipplotter but you
are only interested in ships within a certain range from
your home location. If set to 0 all ships are loaded.
Check for Updates if
Excel will not check each time it is started whether there
is an updated version on my server.
ShipPlotter > Controls
There are a choice of the following options
scheduler - if you have stopped it
scheduler - if running
the data obtained from ShipPlotter
ShipPlotter > Help
Will display this page
ShipPlotter > About
Will display a message box similar to the one below. If you
wish to report a bug, this is very helpful.
Uploading to a website
Detailed instructions may be obtained from the page AIS Data Analysis Website Upload
Changing the Layout of the
You should not change the layout of the Graph or Data worksheets.
You may change the layout of the Summary Sheet, or add other Sheets
including Graphs, that link to any other worksheet.
Changing the Summary
The Summary sheet is a normal Excel pivot table linked to the Data
worksheet. I have included it as a "sample"
Click on any cell within the
Right click and select wizard
Click on Layout
Drag the Headings into the
table & Finish
If you double click on any
total you will get a new sheet created containing only the
rows off the Data
worksheet making up
You can use pivot tables to summarise any data held on an Excel
worksheet. It is an extremely powerful way of not only listing
totals but also splitting values into ranges and graphically showing
the result. For example you can simply plot a Graph of the Speeds of
ships in bands of say 5kn and the graph will be updated each time
the worksheet is updated. Or you could plot a Graph of Range vs
For more information I suggest you Google Pivot Tables.
Changing the VBA Code
See here more information
about altering the VBA code