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 Ship Log feature, which creates and maintains a position log for a passage. It is integrated with the Celestial Navigation features. However, 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 it's not quite ready for prime time.
I have recently (November, 2010) moved my development system from Winxp/Excel2003 to Win7/Excel2007. I will continue to provide an Excel2003 version of the program, but I am no longer testing the program in that environment except when a bug is discovered. I am now creating the Excel2003 version of the program by using the SaveAs feature in Excel2007.

Version 11.0 of the application for Office 2007-2013 can be downloaded from the links below. This update provides a tool to import Ship Logs from Excel workbooks not created by this program.

OffShoreNavigation for Office 2007-2013

With version 11.0 I am no longer posting an Office 2003 version for download. I don't believe there is any interest in this version, and I believe any users with Office 2003 can download the utility provided by Microsoft to use the 2007 version. You can still download version 10 in the Office 2003 version at the bottom of this page.
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 a sub-folder of that used for the application. That sub-folder must be named "Help". The zip file should generate the Help sub-folder, but this may vary for different extract progrrams. The zip file includes both Excel 2003/xp and 2007 versions. The program will automatically select the Help workbook version based on the version of Excel running. There is really no difference between the Help worbook versions as there are no Macros in either version. I have maintained seperate Help workbook as Excel 2003 requires an add-in to load an Excel 2007 workbook. Also I may add macros to these workbooks in the future.

OffshoreNavigation Help
The Help files are current for version 9.96

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

I am in the process of updating the user manual to the current state of the program. This update is incomplete, but the information is up to date enough to accurately cover the operation of the program. You can download current version of the user manual by clicking here.

I have also started to create a series of tutorial videos on using the OffShoreNavigationVBA program. Links to those videos are provided below.

Celestial Navigation Videos

Ship Log Videos

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 time of Moon Rise/Set/Meridian Passage 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.
Generates a printed copy of the Ship Log.
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 has not been used on an actual passage.
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.

OffShoreNavigation for Office 2003 (version 10.01a)

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 reduce to an Intercept and Zenith Directioin and calculate an Estimated Position (EP).

Here is a link to a video that demonstrates the use of Linear Regression Analysis in the program.