Waypoint Management with Excel
 
This page documents an Excel workbook I developed for the multiple chart plotting devices and software as well as the multiple waypoint sources I was using in 2008, when I began a winter cruise to the Bahamas.  This application was developed for the specific chart plotters and waypoint sources in use on that cruise.  Subsequently nearly all of those chart plotters, and addtional ones (e.g., OpenCPN) have adopted GPX as the import and export file format.  So this application is no longer of value for those plotters.  The sole holdout is my Raymarine C120 MFD, which requires a proprietary import/Export format.  I have written another appliction that will convert GPX files to the Raymarine format (using the same utility provided by Raymarine, described below).  Consequently I haven't used this application for years.  I've kept this page active to remind me that I want to develop a more generalized waypoint management utility, but this not that utility.  In spite of what is written below, I am not planning any enhancements to this application.
In preparing to cruise the Bahamas in 2008 I purchased both the Explorer chart books and several of Pavlidus and Dodge guides.  All of these publications rely heavily on their own set of GPS waypoints to describe safe routes and anchorages.  Before entering the Bahamas I wanted to have these waypoints available in each of my chart plotting displays - Raymarine C120 MFD using Navionics Charts, SOB software using C-Map Charts, and Fugawi software using NOAA charts.
However, I didn't want to have to enter each of these waypoints individually into each of the chart plotters and I also didn't want to use any of the waypoint entry forms provided by these products as none of them are very effective for data entry - entering dozens of waypoints at the one time.
Luckily all of these products have a waypoint import capability that can use a formatted file as a basic import document.
Of course each of the products requires a different file format for the import document, with the data in different formats as well.  So I decided to create an Excel Workbook (described below) in which I would enter the waypoint data with separate worksheets that would format the waypoint data for the various import utilities.  Thus I would enter each waypoint only once and then propagate the data for each of the import formats.  I would validate the entered data in a single worksheet.
I decided to use separate workbooks for the Dodge, Explorer and Pavlidus waypoints to avoid getting them intermixed.  These waypoints are only meaningful in the context of the guide from which they were taken.  They are of no value, and potentially dangerous, if used outside of the context of the associated guide (are these waypoints on  a course for safe passage or ones to stay well off?).  To manage these waypoint workbooks I created the WaypointMaster workbook which contains tables that define the waypoint source files, the chart plotters for which the waypoints are generated and the format of the import file for each of the chart plotters.
What Does WaypointMaster Do?

Well, right now, it doesn't do much.  It is really not much more than a file manipulation and file filter utility.  The real functionality is presently in the waypoint workbooks, where I have used standard MS Excel formatting functions to reformat waypoint data for each of my chart plotters.  If I add another chart plotter (why would I do that? - because I can) I will need to create a whole new set of edits in a new set of worksheets to reformat the waypoint data into whatever format that chart plotter requires.
So actually this application is of very limited use to navigators other than myself.  Look to the section What will WaypointMaster Do In The Future, where it might have wider use.
So for now the WaypointMaster program does two things for me:

  1. It allows me to quickly generate waypoint import files for each of my chart plotters with minimal effort.
  2. It allows me to delete the waypoint sets from my chart plotters when no longer required, knowing I can quickly reload them from the files generated by this program.
What Will WaypointMaster Do In The Future?

The short answer is I don't know.  I haven't decided if I'm going to invest a lot more time in developing this application since it does, today, what I need.  However I can see several capabilities, if added, would give this program a wider applicability.
  • Provide a Waypoint data entry form to facilitate entering waypoint data into the program
  • Perform the waypoint data conversion for each chart plotter only when generating an import file for that system.  That is maintain only one copy of each waypoint, rather than the multiple copies the current workbooks maintain.
  • Maintain that single set of waypoints as a GPX file, rather than an Excel workbook
  • Provide a generalized tool to define the import format for other chart plotters.  GPXConvertVBA is an initial attempt at this tool.
  • Integration with the OffshoreNavigationVBA application to generate waypoint sets for a planned or completed voyage (Boy, is this a long way off).
The rest of this web page describes how WaypointMaster works today.  If you think this application could be of value to you send me an email and tell me what you would like to see it do.  I may just give it a shot.  If you are interested the files can be downloaded below.
Waypoint Workbooks
WaypointMaster takes as input the separate waypoint workbooks I created for each of the waypoint sources I have.  As shown in the drawing on the left, each workbook consists of 3 sets of worksheets, on set for each of the chart plotting devices I have onboard Sarah (SOB, Fugawi and Raymarine).
Within each set are separate waypoint worksheets for each of the cruising areas these workbooks cover.  In this case the waypoint workbooks cover the Bahamas.  The division of the waypoints into separate worksheets was arbitrary.  They could have been all been put into a single worksheet, one for each chart plotting system.  I have created separate regional worksheets for ease in selecting the waypoints I want displayed on each chart plotter.  The SOB worksheets are the masters.  The other worksheets are derived from the data I enter into the SOB sheets.  This is done with normal MS Excel formatting and computing functions, no Visual Basic for Applications (VBA) is used in these workbooks.  Once I created these workbooks I really didn't need WaypointMaster to generate the input files for each chart plotter.  I could do that just by saving each worksheet in the appropriate format using a standard Excel processes.  However that is a time-consuming process of repeating the same steps over and over.  I figured that is something computer programs are good for, so I developed WaypointMaster in VBA to handle those processes.
WaypointMaster Process Flow
The diagram on the right depicts the process flow used by WaypointMaster to create the waypoint import files for each chartplotter.  The next section shows the detail steps in a series of screen captures from the program.
As described above, the input to Waypoint Master are the waypoint workbooks.  At the start each workbook I intend to use is loaded into the Waypoint File List.  The chart plotters for which waypoints files are to be created are specified from the chart plotter list.  I have selected SOB and Fugawi (bold, underlined).  From the Waypoint File List I have selected only the Pavlidus workbook.  This populates the Waypoint Sets list with the Pavlidus waypoints for SOB and Fugawi..  If I had selected All in the chart plotter list, the Pavlidus waypoints in Raymarine format would have been added to the Waypoint Sets.
From the Waypoint Sets list I selected the areas for which I want waypoints, in this case just the Exumas.  This selection puts the SOB and Fugawi waypoint sets in the Output Waypoint Sets list.  When I click on the Create Files button the program saves this data into files that are compatible with the SOB and Fugawi chart plotters in the file folder specified.  The output files created can be directly imported into SOB and Fugawi.
WaypointMaster Program Screen Captures
The screen capture on the left shows the Tables worksheet in the WaypointMaster workbook.  This worksheet defines the chart plotting systems that are supported by the workbook (SOB, Fugawi, and Raymarine) and the formats of the import files created by the workbook (CSV and Archive.xls).  The meaning of the cells in this worksheet are explained in the documentation below.
Thus I have four Excel Workbooks, one each for the Dodge, Explorer and Pavlidus waypoints and the WaypointMaster workbook that contains the VBA code that creates the import files for each defined chart plotter.  I've populated the three waypoint workbooks with the waypoints for each of the areas of the Bahamas I plan to cruise.  All four of the workbooks can be downloaded from the link below.
WaypointMaster Download
Waypoint Data Entry

For the basic entry of waypoint data I decided to use one of the chart plotter formats to minimize the number of conversions required in the workbook.  I chose the SOB format for several reasons.

1. The entry of data could be accomplished with the minimum keystrokes'

2. SOB uses C-Map charts which have plots for the Explorer waypoints so I can quickly check to see if the Explorer waypoints were entered correctly

The screen capture on the left shows the SOBExumas worksheet in the Pavlidus waypoints workbook.  I set up a separate tab for each of the major cruising areas in the Bahamas.  The choice of these worksheets is arbitrary.  I could have put all of my Bahamas waypoints in a single worksheet, however I find it more convenient to manage these waypoints as sets.

The SOB waypoint import utility accepts free form waypoint files so it requires headers above each column to identify the contents.  Fugawi also accepts free form files, but uses a different technique to identify the columns.

I have highlighted the Adderly Cut waypoint to show the SOB format (click on the screen capture to view it at full resolution).  SOB requires the latitude and longitude to each be entered as a single field with both degrees and minutes specified.  The minutes are separated from the degrees by a space.  Also a "N" or "S" qualifier must be entered with the latitude.  The longitude format is the same with a "E" or "W" qualifier.  Although not required by SOB, I have rigidly formatted all of the waypoint coordinates with the same number of significant digits.  This allows me to scan the coordinate columns to quickly find most typo errors.

I have added columns for the date and time, which are the current time in the computer at the time I last opened or modified this workbook.  The date and time will be imported into the chart plotters to identify how current the waypoints are in each plotting system.

In a Comments column I have identified the source of the waypoints, Pavilidus waypoints for the Exumas.
 
I have entered all of the Pavlidus waypoints for the Exumas in this worksheet.  I have set up the worksheets for Fugawi and Raymarine to re-format this data in accordance with their import specifications.
Fugawi Waypoint Worksheet
On the right is a screen capture of the Fugawi worksheet with the same Exumas waypoints as entered into the SOB worksheet.  Click on the image to view it at full resolution.
Fugawi requires the latitude and longitude coordinates each in a single field with only degrees and decimal degrees specified.  So I've used a few standard Excel formulas to reformat the latitudes and longitudes in the SOBExumas worksheet to the required Fugawi format.  Fugawi also doesn't use a qualifier (N/W, E/W) for the latitude and longitude, rather it uses signed numeric values for the coordinates.  North latitude and east longitude are positive numbers, south latitude and west longitude are negative numbers.  I have created columns with the N/S and E/W qualifiers, but they are used by the formulas that reformat the SOB latitude and longitude to determine if the value should be positive or negative.  These columns should not be imported into Fugawi.
Notice in each worksheet I have a column that I use to identify the source of the waypoint, in this case I used the source name of PavExumas to identify these waypoints as Pavlidus waypoints for the Exumas.  Each of the chart plotting system has its own internal method for organizing the waypoints, but including an identifier as a comment or description in the waypoint entry helps to quickly identify the waypoint source.
Raymarine Waypoint Worksheet
This is the Exumas waypoint worksheet created for my Raymarine C120 MFD. The C120 is a standalone chart plotter and therefore the waypoint import utility is a great deal less flexible than those for the PC-based chart plotters.  The Raymarine requires a fixed format input file and there are several import steps (some involving hardware) for this product than for the others.
This worksheet is derived from the worksheet Raymarine provides as part of its Waypoint Transfer Utility. 
The purple row at the top of the sheet is the header row specifying the mandatory order and format of the columns.
Raymarine wants the latitude and longitude coordinates in three separate fields or columns - degrees, minutes, and qualifier (N/S, E/W).  So I have set up this worksheet to reformat the coordinates entered in the SOB worksheet to that required by Raymarine.  The C120 uses a Group parameter to help organize the waypoints.  I have inserted the PavExumas source ID as the group name for these waypoints.
The time and date fields in this worksheet are the current date and time.
Creating Waypoint Import Files
Well now I have a bunch of waypoints in several Excel Workbooks, how do I get them into each of the chart plotting systems I use on Sarah?  None of my chart plotting products will accept the waypoint workbooks as a waypoint import file.  For each product I must save the data in a unique file format before introducing it to the product.  Both Fugawi and SOB accept files in the comma-separated-values (CSV) format.  Raymarine requires that the waypoints be recorded in the special Excel workbook provided with the Raymarine Waypoint Transfer Utility. 
Therefore I need to be able to save each of these worksheets into the file format required for each chart plotting system's import utility.  For this purpose I've programmed the "Create Waypoint Files" userform into the workbook, which is activated by clicking on the 'Waypoint" command bar at the bottom of the Waypoint Master workbook.
There are four windows in this form.  The leftmost window contains the list of active waypoint source workbooks.  The other windows are populated with data by the import file creation process. At the top of form is a drop-down list of the Chart Plotters supported by this form.  The first step in the process is to populate the Open Waypoints Files list.
Clicking on the Add button opens a File Open dialogue box that can be used to select the waypoint source workbooks, which are highlighted by the red ellipse in the screen capture above.
The screen capture on the left shows the now populated Open Waypoints Files list.
Selecting one of the workbooks in the list activates the Chart Plotters drop-down list showing the list of Chart Plotters for which import files can be generated.  You can select one of the Chart Plotters or use the "All" item to select them all.
When I select the "ALL" option the Waypoint Sets list is populated with all of the Pavlidus waypoint sets I've created in the PaylidusWaypoints workbook.  There are three copies of each of the sets, one for each of the chart plotting systems.
With the Waypoint Sets list populated the Output Sets list can now be created.  This can be down by selecting an individual file using the -> button.  This moves the set from the Waypoint Sets list to the Output Sets list.
The Output Sets list specifies which waypoint sets will be used to create chart plotter import files.
Alternatively the => button can be used to move all of the sets to the Output Sets list.
Waypoint sets can be removed from the Output Sets list either individually using the <- button ...
... or all sets can moved back to the Waypoint Sets list using the <= button.
In the screen capture above I have selected the Pavlidus waypoints for the Exumas for all three of my Chart Plotters.  Now I'm ready to create the import files for each of these Chart Plotters.
First I need to establish the drive and folder where the import files will be stored.   That folder is specified in the text block in the bottom right of the form.  To change the folder click on the Browse button, which calls up a Folder dialogue box.  In the screen capture above I have selected a folder named "Pavlidus" to contain the import files for these waypoints.
Now I can click on the Create Files button and the import files are created.  In the screen capture above, the CSV files for both SOB and Fugawi have been created and are shown in the Created Files list.  The Raymarine import file creation requires the generation of a blank workbook from a template I downloaded from the Raymarine website.  The form opens a file dialogue box in which I have specified the RaymarineWaypointArchive template.
In the screen capture above the import file creation process is complete and the three import files are shown in the Created Files list.
Now the Tables worksheet in the WaypointMaster workbook contains the names of the waypoint source workbook names and paths in the SourceFiles column and the name and path for the Raymarine template file is captured in the OutputFile column.  The next time this workbook is opened those files will automatically be used without requiring additional input.
Using Windows Explorer I have inspected the contents of the Pavlidus folder in the screen capture above.  The import files for Raymarine, Fugawi, and SOB are shown.   
When I open the PavSOBExumas.csv file with Notepad I can see all of the waypoints in CSV format.  This file is ready for import into SOB.
 The screen capture on the left shows the Fugawi CSV waypoint file for the Exumas.
The Archive.xls workbook created for import to the Raymarine C120 is shown in the screen capture on the right.
Now let's look at how these files created by the WaypointMaster Workbook can be imported into the different chart plotting systems.
SOB has the easiest import, so let's cover that one first
SOB Waypoint Import Utility
The transfer of a waypoint CSV file to SOB is accomplished with Windows Explorer by copying the file to the Waypoints directory under the SOBvMax directory.
In this case I am transferring the Explorer Exuma waypoints (EXSOBExumas) to SOB.
When I next start SOB and open the All Waypoints form I see the EXSOBExumas.csv file in Waypoint Files window.
When I click on that file in the window, the name is immediately changed to EXSOBExumas001.wpt.  That is all there is to importing a waypoint file, just a mouse click.
Of course these waypoints are not visible in SOB as yet.  For that to happen I must click on the Load "EXSOBExumas001.wpt" button at the bottom of the file list window.
Now all 128 waypoints in the EXSOBExumas001.wpt file have been loaded into SOB.
When I zoom to the Exumas in SOB I see the waypoints displayed.
As long as I keep these waypoints in the EXSOBExumas001.wpt file I must load them every time I start SOB.  Only the waypoints in the default.wpt file are automatically loaded by SOB.  This helps me manage these waypoints for display.  Anytime I want them to disappear I just use the Unload button in the All Waypoints form to remove them from the display.  The waypoints are still available, only a Load button click away.
Fugawi Import Utility
Now I have switched to Fugawi and opened the Waypoint Library Form.  I've created a Waypoint folder, "Explorer" to contain the imported Explorer waypoints and then click on the Import from File button in the upper left corner of the form.
The import button calls up a file open form to select the csv file to import.   I select the file EXFugawiExumas.csv and then click OK.
Fugawi, like SOB, uses a free form input file.  SOB required column headings to define each field in the waypoint records.  Fugawi uses the Import Fields form to define record format.  That's why I didn't use column headings in the Fugawi worksheet, above.
Click on each field name and move them into the right window (using the > button) in the order in which the fields occur in  the waypoint record.  In this screen capture I have defined the four fields in each waypoint record, in the order in which they occur.
Once the fields are defined click OK and the waypoints will be imported into the Explorer folder.
Now the Explorer folder has been populated with waypoints and those waypoints appear on the chart of the Exumas.
 
 
Raymarine Import Utility
The Raymarine utility requires a Compact Flash (CF) drive to be attached to the PC with a CF card inserted.  With the drive connected and a CF card in the drive I have started the utility.  There are two process options available.  The top button converts a waypoint archive file on the CF card to an Excel workbook.  I will use the bottom button to convert the Excel workbook just created to a waypoint archive file on the CF card.
First the utility allows you to specify the Excel workbook that contains the waypoints.  Again, this workbook must have the name "Archive.xls".
Next the utility requests the location of the CF card to save the converted file as a waypoint archive ("Archive.fsh").
Then the utility will crunch for a while and eventually say the conversion is complete.  The CF card can then be used to load the waypoints into the C-series (or E-series) MFD.  One limitation of the C-series waypoint handling is a name of just 16 characters.  In my waypoint workbook I have truncated the waypoint name in the Raymarine worksheets to 16 characters.  This can cause duplicate names for similar or nearby waypoints (e.g., "Ship Channel Cay, 2 nm SE of", and "Ship Channel Cay, 5 nm N of" produce the same name when truncated to 16 characters).  I have also set up workbook to insert "X-" in front of the Exlporer waypoint name and "P-" in front of the Pavlidus waypoint names.   This eliminates the possibility of a duplicate name from the two sources.  It is best to review the names in the Raymarine worksheets prior to using this utility to create the Archive.fsh.  If there are duplicates names, modify the name in the SOB (data entry) worksheet to make the names unique, then re-create the Archive.xls file.  I recommend that you not modify the derived names in the Raymarine worksheets nor the Archive.xls workbook.  If you modify the data entry (SOB) name for the waypoint the will be used in any subsequent waypoint file creation.  I you modify the Archive.xls name, it will have to be manually done every time this Archive.xls file is created.
Once the Archive.fsh file has been created on the CF card, use the standard C-series process to transfer data from the card to the MFD.  After removing any chart CF card from the MFD and inserting the waypoint CF card, use the "Data" button to call up the softkeys with the "Archive and Transfer" button, and then the "Transfer from Card" key.  Instructions for this process are in the C-Series manual as well as the manual for this conversion utility.
Notice in the Raymarine worksheet example above, I have included a Group name in each waypoint record (e.g., EXExumas, to identify Explorer waypoints for the Exumas).  The transfer process on the MFD will put the waypoints in the specified Group.  If the Group does not exist on the MFD, it will be created at transfer time.  This helps keep the waypoint organized on the MFD, but unfortunately it does not provide a lot of utility.  I had hoped that the Group name would be combined with the waypoint name to create a unique name for the record (analogous to the folder/filename in Windows).  This is not the case.  If two waypoints in separate groups have the same waypoint name, they will be duplicates and the transfer process will not load the second waypoint of the same name.  You really are limited to 16 characters.
Also, the waypoint really cannot be manipulated on the MFD as groups.  With SOB and Fugawi I can easily remove a group of waypoints from the display and then restore them with a few simple mouse clicks (Load/Unload button in SOB, Hidden checkbox in Fugawi).  There is no provision to hide a waypoint group in the C-Series.  However, since the waypoints can be transferred from the CF card by Group name, you can delete an entire Group on the MFD to remove them from the display, then use the transfer process to restore them from the CF card.  It's a little cumbersome, but it works reasonably well.
Raymarine also strongly recommends you not use any of your chart CF cards to archive and load waypoints.  A dedicated card for this purpose is recommended.