Off-Shore Navigation Calculations using MS Excel

I have long studied and been interested in the mathematical side of small boat navigation. As I learned more on this subject I was aware that many of these navigation calculations could be programmed into a computer. At the time I was a mainframe computer systems engineer, developing telecommunications software for the Air Force working for Univac (now Unisys), a former Air Force meteorologist and mathematics major in college. In the late 70s, early 80s there was no practical way to bring the calculating power of the computer on board a cruising sailboat.

Then pocket computers became available in the early 80s. I purchased a Radio Shack pocket computer in 1982 and decided to see if it could be used for some of the navigational calculations. One of the initial calculations I programmed on this computer was a celestial sight reduction. This program initially just performed the arithmetic of a sight reduction, the purpose being to eliminate arithmetic errors. A Nautical Almanac and sight reduction tables were still required. Then I implemented the equations to reduce a sight without the use of tables. Now I could reduce a sight to a DR position rather than an assumed position that fit the reduction tables. Next I found a source to produce an ephemeris of Sun for the next decade ( into mid 1990s). That was great, I could do all of the calculations necessary to produce an intercept distance and azimuth for the Sun sight without the use of a Nautical Almanac nor a set of sight reduction tables.

Then I stopped sailing off-shore for nearly a decade. When I resumed off-shore sailing in 2001 I wanted to bring my navigation programs up-to-date. By then GPS was a standard instrument on all boats, whether they cruised off-shore or not. So celestial navigation was no longer the principal navigation tool of the off-shore sailor. Still the subject fascinated me and I believed it was a necessary backup to electronic navigation. Of course a computer based celestial navigation system is not really a backup to electronic navigation, but for me this was really an intellectual exercise - not necessarily a practical solution - so I resumed developing this application using the personal computers of the 2000s. By this time I had jettisoned most of the software development tools on my personal computer. The only real program development tool I still had was MS Excel and Visual Basic for Applications (VBA). I started to convert my old Radio Shack Basic programs to VBA.

The initial result of this development was the Off Shore Navigation workbook template. This workbook contained worksheets to reduce Sun, Moon, Star, and Planet sights and also a number of navigation functions (e.g. Great Circle Route planning). This seemed to be a good solution, but it had many shortcomings.

The worksheets were not integrated. I couldn't use a Moon and Sun sight to produce an Estimated Position (EP) or Fix. The worksheets stood alone.
The workbook with the navigation data also contained all of the VBA code for the calculations. The size of this file was nearly 4MB slowing down the loading and saving of the navigation data.
The entry of data was not programmatic, that is you had to position the cursor to the cell for each data item.
Many of the future developments I wanted to implement (integration with a Ship's Log) would be difficult and awkward with the existing workbook format.

So I finally abandoned the workbook template methodology, and converted to a single UserForm that included all of the calculations of the former workbook template. The name of this new application is OffShoreNavigationVBA, which is just a standard Excel Workbook with a lot of VBA code. The first released version of the program was 8.9c. The program was released only for MS Office 2003/xp.
With version 9.1b (released 7/5/2009) I have added the initial implementation of the Ship Log feature, which creates and maintains a position log for a passage. It is currently minimally integrated with the Celestial Navigation features. Also the Ship Log function has not been tested on an actual passage. I'm sure there are still several bugs in it. I'm testing it with log data from previous passages, but I know it's not quite ready for prime time. I'm including the Ship Log in the current release so that users can see how it works. Maybe I'll get someone to debug it for me on an actual passage, before my next voyage.
I have recently been able to test the program under Office 2007 and can now provide a version of the program that works in that environment on my netbook. I have not done extensive testing of the application in this environment, but I have exercised all features in one manner or another and have fixed any problems that I encountered. For the foreseeable future I will continue to do all development in the Office 2003 environment because VBA documentation in Office 2007 is almost non-existent. The only difference between the Office 2003 and Office 2007 workbooks and templates is that I have created the Office 2007 version by saving the Office 2003 version using the Excel 2007 conversion filter add-in to Excel 2003. That appears to be necessary to direct the VBA code to the Office 2007 object libraries. When I loaded the Office 2003 version in Excel 2007 and then saved it as an Excel 2007 workbook with Macros the VBA code was still looking for the Office 2003 object libraries, which do not exist in my Office 2007 environment. There may be a simpler way to convert the Excel 2003 files within the Office 2007 environment, but I haven't been able to find it.

Version 9.91b of the application for Office 2003 can be downloaded from the link below.

OffShoreNavigation for Office 2003

Version 9.91b of the application for Office 2007 can be downloaded from the link below.

OffShoreNavigation for Office 2007

With version 9.88 I am no longing including the Help files in the program download zip files. The download files were just getting too large. So now the Help files can be downloaded from the link below. This zip file should be extracted into the same folder used for the application. It will generate a sub-folder, Help, and put all of the files in that folder. This single version should work in both the Office 2003 and Office 2007 environments as there are no macros.

OffshoreNavigation Help

The links are to zip files (an example shown above with WinZip) which contains the following files

OffShoreNavigationVBA.xls - the current version of the application as a MS Excel Workbook with macros
SightReduction.xlt - the template workbook for the files to store sight reduction data.
ShipLog.xlt - the template workbook for the Ship Log features of the program

If you just want to view the old documentation (version 9.6) click here

Here are some thoughts on what this program can do, may do in the future, and how I use it.

What Does It Do

With release 9.90 the application can do the following:

Calculate sight reductions of the Sun, Selected Stars, Jupiter, Venus, Mars and Saturn without the use of an external Nautical Almanac or sight reduction tables. Enter the sight data (time, altitude, corrections) and the program will produce the azimuth and intercept LOP for the sight and an Estimated Position (EP - where intercept intersects the LOP).
Calculate sight reductions of the Moon using data from the Nautical Almanac.
Calculate a FIX or an Esitmated Position (EP) from two to four Sight Reductions
Create an EP log entry from a single sight reduction
Collect the sight reduction data in a separate workbook for later review and analysis
Provide twilight, sunrise, sunset and Local Apparent Noon (LAN) times for any date and location
Create a Day Almanac report for the above celestial bodies for a range of dates. The format of this report is similar to the Nautical Almanac daily pages. If one wishes to have a printed Nautical Almanac on board, the program will produce one for planned voyage instead of the entire year. It will also produce the report across the end of a year (December – January - you don't need two almanacs on board for a winter cruise). With version 9.53 the Day Almanac can be printed in either the standard data format of the program (see the documentation) or in a formate closer to that of the Nautical Almanac.
Creates and updates a Ship Log calculating a DR position between fixes.
Performs Linear Regression Analysis of Sun Sights to minimize errors in the sextant altitudes. (See Note 1)
Provides a toolset for route planning, either waypoint routes or great circle routes
Integates routes with the Ship Log.
Apply current set & drift to the Ship Log DR records.
Provides documentation via a context sensitive Help process.
Provides tools to Import/Load previously created Routes, Ship Log workbooks, and Sight Reduction workbooks.

What Do I Plan for It To Do?

Calculate sight reductions for the Moon without the need for a Nautical Almanac or sight reduction tables. The Nautical Almanac data is the current hang up. The algorithms to calculate GHA and DEC for the moon are a bitch.
Calculate current set and drift from two position fixes.
Generate analysis and recall reports on the Ship Log data.
Generate route waypoints from the Ship Log workbook and integrate that with my Waypoints Management Tool or use a similar tool to create routes that can be imported into my chart plotting systems.
Continue to expand the tool set for navigation to include solutions to common or not-so common navigation problems.

How Do I Use It?

I started to create this application set for really three reasons.

To see if I could do it.
As a learning tool for the underlying disciplines
Possibly to actually use it when planning and navigating an ocean passage.

Well I think I’ve proven to myself I can do it, and I’ve learned a lot from the past and on-going development. I’ve also learned a lot about MS Office/Excel VBA in the process. Now I intend to use it in practice. I have actually used an earlier, stand alone version of the Ship Log on two trans-Atlantic passages, and it proved very effective. It was particularly useful as a check on the paper log we maintained for those passages. That version, and this one, derives a set of values from the log data entered, which can be easily checked for consistency. If the program calculates a distance of over 20nm in one hour between log entries, something is wrong. A single digit error in the log is visually hard to detect, but can produce significant errors in the derived position. On these passages we never really maintained a DR log as we had continuous GPS position data. All of the log entries were in fact GPS fixes. So the real value for those passages was the error checking and having an electronic record of the passage at the end of the voyage.

I also had the earlier, stand alone version of the sight reduction routines on board, but never got around to taking any sights. Don’t know why, except GPS/Chart Plotter navigation makes going “Old School” pretty difficult. With watch keeping, cooking, cleaning, monitoring the weather, maintaining radio schedules, fixing the inevitable equipment failures, and occasionally sleeping it’s hard to find time for Celestial Navigation when you can just glance at the GPS and see your coordinates and then glance at the chart plotter and see your position graphically. Ultimately one of the results I hope to achieve with the program is the elimination of the tedious and time consuming sight reduction process so that I might find time to actually practice taking sights. The real skill in Celestial Navigation is not the process of the reduction, but the process of taking the sight, which requires a lot of practice while at sea. Hopefully this program will allow me to take the sights, then quickly see the results and compare them with what the GPS position. That practice, when the sight derived position is not required, may make me ready for the time when it is.

Caution

This application has had very limited testing and anyone using it for Navigation are doing so with no assurance from myself that the program is free of errors. In deed I believe there are a number of errors that I have not been able to detect and thus not fix. At this time I am aware that the ephemeris for Venus is not very accurate (+/- 0.5 minute of arc different from the values in the Nautical Almanac) for dates more than two decades from Epoch 2000. I haven't found the problem yet. The ephemeris for the other planets seem to be reasonably accurate (+/- 0.1 minute of arc difference from the NA). The Venus ephemeris data produced for 2008 appears to be within that reasonable accuracy range. The Sight Fix feature (using up to four (4) sight reductions to generate a position fix) has received very limited testing. The Ship Log feature is definitely not ready for operational use.
I am very interested to hear from other navigators who have tested this program in their environment and have found errors or problems in using the program. I will try to fix any problem that I am made aware of.

What I'm trying to caution here is not the usual CYA caveats issued by commercial navigation software, but the very real knowledge that none of this software has really been thoroughly tested. On my next off-shore passage I plan to make extensive use of this program, but with the knowledge that the results produced may not be reliable.

Note 1: The Linear Regression Analysis would normally be performed on mid-morning and mid-afternoon Sun sights, when the motion of the Sun is essentially linear (for the accuracy of celestial navigation). The analysis would be performed on a series of sights taken in succession during these periods. If all sights were accurate they should be linear on an altitude/time plot. The purpose of the Linear Regression Analysis is to identify the outliers and find the sight or sights that are likely the most accurate and use one of them to calculate an Estimated Position (EP).