ShipPlotter Monitoring - Upload Guide

ShipPlotter.xls by Neal Arundale

Graphically Monitoring Shipplotter Data

Upload Guide

I would welcome any comments at

Refresh is the frequency any files will be uploaded, set to 0 stop polling and uploading.
Graph Tick Upload to start uploading the graph to a web server. The graph may be either jpg or gif.
List. Tick Upload to start uploading the list of ships either in html or xml
URL Path is Server + directory where any uploades files will be placed. For simplification the actual filenames are fixed..
Username & Password are those required to access the webserver

Tabs

Graph contains the graph to be uploaded.
List contains all the individual ships data available to upload.
Html contains the html table layout and the html header data (see below).
Xml will contain the Xml layout.

Uploading the graph
In the graph line - Tick Process, Upload & select File Type
Set up Server with Url Path, Username & Password
Set the Refresh Rate (which is also the upload interval)
To get a "clean" image on your webpage, you should not allow the browser to resize the image of the graph. The graph image created by Excel will be the same size
as the graph on the spreadsheet. Check the properties of sp_graph.gif on your PC and force the browser to display the image the same size.
If you want a larger or smaller image on your webpage alter the size on the speadheet. A GIF image seems to be "cleaner" than a jpeg.

Uploading the List of Ships
In the List line - Tick Process, Upload & select File Type
Ensure Server and Reftresh rate are set as above

Files that Will be Created
All files will be created in your default excel directory (probably My Documents)

File Name	Type	Uploaded	Purpose
sp_graph	.gif (or jpg)	Yes	contains the graph image
sp_list	.html	Yes	contains the current list of ships
sp_ftp	.dat	No	contains the ftp commands that are used at the DOS prompt
sp_ftp	.log	No	contains the responses from DOS as the commands are executed
sp_excel	.log	No	for debugging, contains system and excel details
sp_log_1	.log	No	for debugging, contains session details
sp_log_2	.log	No	for debugging, as above output alternates between both files

Changing the Graph
The format of the graph is governed entirely by Excel - the only settings I play with are the ranges of the data plotted and generate the "Time Stamp". Excel may do funny things if there is no data at all on the graph.
You can change the format of the graph, move the axes alter the sizes of the text displayed and most other things at will.
If you've never played with Excel graphs it may take a bit of getting used to. In principle you left click on the graph to bring it into focus, then right click on the part of the graph you wish to change, you will be presented with a list of options that you can change. Moving and resizing both the graph and the content within the graph is a similar procedure.
This size of the graph uploaded will be the same as that on the worksheet, Don't move the location of the graph to it's own sheet - it won't get updated. Don't let the browser resize the image - it won't make a very good job of it. In your html code, fix the image size in pixels to the size of the created image file.
Gif produces a smaller file and a clearer image.
The graph will be entirely autoscaling - provided you have not altered any of the Excel default settings, this includes axes , axes labels, titles, plot area etc.

Changing the List
Don't delete, change the order or insert new columns.
You can alter the format of the columns provided the format is compatible with the format output by Shipplotter.
You may hide columns to remove columns that are of no interest to you. I have hidden one or two which you can tell from the lettering of the columns. To unhide select the columns either side, right click & unhide. To hide select the column, richt click & hide. The data is displayed as received from Shipplotter with no formating, excepting two additional columns.
Position which is calculated from the Latitude and Longitude and Type which is calculated from the Type No returned by Shipplotter.

Changing the HTML file
The Html worksheet generates the html page which you may format as you wish.
As a minimum you can output just an html table containing only the columns you wish.
For technical reasons (ask Bill Gates!) the formatting will not be the same as on the List worksheet - it is preset in the VBA code.
The example below is the page that will be output if you make no changes to the html worksheet.

Click on the example for the current list

How to Change the HTML file
The colouring is for explanatory purposes only

If your ISP allows you to use Server Side Includes (SSI's), you can include the table within an existing html page, by deleting all of the lines except line 13 (which would become line 1).
A lines and cells are output exactly as entered on the sheet to the html file called sp_list.html which is the uploaded to the web server.
The exception is the line that contains the tag <sp_list> ,this is the placeholder for the html table containing the list of ships which will be generated from the "List" sheet.
In each subsequent cell is the header for each column in the table (eg Name). The header text must be exactly as in the header on the List sheet - the easiest way to ensure this is the case is to enter the formula refering to the "source" column (eg =List!$B$1) as shown in the example above. You can include or exclude as many columns as you wish from the "List" sheet.
The lines left white (1-4,11-12,14-15) are "normal" html tags to set-up the page.
The green lines set the style of the text for the header line and the rest of the table. The table is output with a class of sp_list, and the header with a class of sp_list_header as below.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head>
<title>ShipPlotter Monitoring</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">

</style>
</head>
<body>
<table class="sp_list">
<tr class="sp_list_header">
<td>Name</td><td>Position</td><td>Course</td><td>Speed</td><td>Heading</td><td>Type</td><td>Length</td><td>Destination</td><td>Date & Time</td><td>Range</td><td>Bearing</td>
</tr>
<tr><td>Humber Coastguard   </td><td>54°7'5.0"N, 0°5'12.4"W</td><td>0°</td><td>.0kn</td><td>0°</td><td>Shore Station</td><td>0m</td><td>                    </td><td>2008-04-14 13:48:00UTC</td><td>14.0nm</td><td>128°</td></tr>
<tr><td>PRIDE OF ROTTERDAM </td><td>53°44'25.4"N, 0°16'40.6"W</td><td>102°</td><td>.0kn</td><td>102°</td><td>Passenger</td><td>214m</td><td>EUROPOORT-HULL VV   </td><td>2008-04-14 13:45:23UTC</td><td>31.6nm</td><td>172°</td></tr>
<tr><td>ARCO HUMBER         </td><td>54°13'5.5"N, 0°10'7.5"E</td><td>309°</td><td>10.7kn</td><td>511°</td><td>UnderWater ops</td><td>107m</td><td>TEES                </td><td>2008-04-14 13:48:00UTC</td><td>20.2nm</td><td>97°</td></tr>
</table>
</body>
</html>

Web Server Upload Process
A Dos window is spawned to execute the command file ftp.dat
A log of the session is created in ftp.log
ftp.dat should look something like:-
open my.ftp.domain
mywebserver
mypassword
binary
cd /myaisdirectory/
put sp_graph.gif
close
bye
ftp.log should look something like:-
ftp> Connected to my.domain.open my.domain
220 (vsFTPd 2.0.1)
User (my.domain:(none)):
331 Please specify the password.

230 Login successful.
ftp> ftp>
binary
200 Switching to Binary mode.
ftp> cd /myaisdirectory/
250 Directory successfully changed.
150 Ok to send data.
226 File receive OK.
ftp: 37318 bytes sent in 0.06Seconds 621.97Kbytes/sec.ftp> close
221 Goodbye.
ftp> bye
There should be no errors reported in the log file

Xml worksheet
If someone would care to send the Xml file that they upload to update their website, I'll do the code to generate the appropriate Xml file.

Restrictions
As I use Windows 98 and Excel 97 with ShipPlotter v 10.9, I do not know for certain it will work OK on later platforms, but I believe I have not used any "features" that are not upwards compatible. There are diffences in the pivot charts between 97 & 2000.

Bugs
I would welcome any comments at
Although I have produced it for my own use, I will try any fix any problems but can't guarantee a fix by return! I'm NOT a professional software engineer.

VBA Introduction

Why VBA ?
The programs I have seen to extract the data from the ShipPlotter program into spreadsheets use VBScript to write out a file which is then imported into a spreadsheet, or the script writes the data directly into a spreadsheet..

Visual Basic (VB) comes in three flavours. VBS is basically a subset of VB whereas VBA has almost all of the features of VB plus access to the components of the host application. As my end goal was to graphically display message rate, number of ships and maximum range, it made sense to use VBA rather than VBS. A side benefit was the ability of VBA to reschedule itself at a time in the future, whereas VBS only has a weak wait function. In addition the debugging features in VBS are minimal but VBA comes with a good debugging editor (VBE).

Visual Basic Editor
First stop the scheduler , if you don't you may get an unexpected copy of Excel startup later.
You only need to install VBE if you want to change the VBA code.
With Excel 97/2000 installing VBE is an option when Excel is installed (I don't know about later versions).
When invoked by Tools > Macro > Visual Basic Editor (when in Excel) you should see a screen something like

If you wish to change the format or data reported on I suggest you create a new module & worksheet (even if you copy an existing one), you can keep a separate copy of your module by exporting it. If I change the existing code to fix bugs etc, you will simply be able to import your code module and eveything will continue to work ok.
As my initial interest was to display the message rate etc graphically, this is where most of my effort has been spent, the other sheets I've done were mainly to demonstrate the feasability of the techniques employed rather than to produce relevant data. I've also used more than one method of achieving the same end result for the same reason.

Link to my
Aerial Page &
VHF horizon Page
See also
Beyond the Horizon Propagation