Cruisers & Sailing Forums (https://www.cruisersforum.com/forums/)
-   Navigation (https://www.cruisersforum.com/forums/f121/)
-   -   Almanac and Celestial Navigation Calculator (https://www.cruisersforum.com/forums/f121/almanac-and-celestial-navigation-calculator-245458.html)

SeanPatrick 23-01-2021 05:14

Almanac and Celestial Navigation Calculator
 
All,


Here are some links to a spreadsheet I've been working on:


Zipped spreadsheet.


Unzipped spreadsheet.



***NOTE: This is a beta version and is not intended for use in actual navigation. It is only guaranteed to work in current versions of Microsoft Excel.***

Try it out and let me know what you think. If you could test it with some actual sight data, that would be excellent! If you find any bugs, please let me know here or see the "Instructions" tab in the spreadsheet.



Current list of features:

  • Perpetual almanac which lists basic data necessary for celestial navigation
  • Sky chart showing traditional nav bodies above the horizon
  • Data series calculator for comparison/analysis
  • DR and sight reduction calculator
  • Position log
  • Great circle route calculator including internal database of ~15,500 cities worldwide
  • Internal delta-T values for dates between 500 BC and AD 2030
  • Custom delta-T value fields for dates outside the above range - or whenever
  • Only 2MB




Some stats:

  • Total Sheets: 18
  • Cells with data: 180,868
  • Tables: 4
  • Formulas: 8,373
  • Charts: 2
  • Macros: 26
  • Days I've been working on it so far: 237




Don't worry about the macros, they just make the buttons work. If you want, you can open Visual Basic and see everything they do.


Screenshots:




https://i.imgur.com/rJm2ysLh.png


https://i.imgur.com/Btg3DGJh.png


https://i.imgur.com/1ojLG07h.png


https://i.imgur.com/aeMVDW1h.png


https://i.imgur.com/fPOCglch.png


https://i.imgur.com/kKHI9aEh.png


https://i.imgur.com/yzqJYrgh.png




The data series calculator can be used to make charts like this for comparison/analysis:


https://i.imgur.com/N6pK4S9h.png




Anyway, please try it out and let me know what you think. Thanks!


Regards,
Sean

markcouz 23-01-2021 05:22

Re: Almanac and Celestial Navigation Calculator
 
Amazing effort, I may have to dig out the old sextant from under a pile of other random stuff and eventually learn to use it!

SeanPatrick 23-01-2021 06:16

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by markcouz (Post 3326347)
Amazing effort, I may have to dig out the old sextant from under a pile of other random stuff and eventually learn to use it!


Thanks! The destination is already set for you on the map screen. ;)

sailor1924 23-01-2021 07:11

Re: Almanac and Celestial Navigation Calculator
 
I'm amazed by your effort, and jealous of skills. It gives me incentive to dust off the old sextant.
Fifty years ago I started to learn celestial navigation at the Hayden Planetarium in NYC. I didn't finish the course and have forgotten everything I learned at the time.
I've always wanted to relearn but I fear I'm over the hill to pursue it. Your lovely program gives me some hope.
Thank you.

SeanPatrick 23-01-2021 07:14

Re: Almanac and Celestial Navigation Calculator
 
Just found a new bug: the data series calculator gives incorrect distances for stars. :facepalm: That will be fixed in the next version.

double u 23-01-2021 07:51

Re: Almanac and Celestial Navigation Calculator
 
I always found the idea of combining a traditional, "handicraft"-type of skill like celestial with computers highly hilarious.

jlblan2 23-01-2021 09:53

Re: Almanac and Celestial Navigation Calculator
 
Great job and really appreciate the amount of effort you put into it.

Smokeys Kitchen 23-01-2021 09:59

Re: Almanac and Celestial Navigation Calculator
 
This looks great! Always wanted to take the shots and plug it into a good tool.

Wondering if the updated version(s) will be on the same link? Haven't looked at the link yet - but will there be a note regarding the versions?

Thanks again

Cpt Mark 23-01-2021 10:05

Re: Almanac and Celestial Navigation Calculator
 
I have looked for something like this before. Thank you. Will play with it when I get a chance. Buried in projects now.

gerson 23-01-2021 10:21

Re: Almanac and Celestial Navigation Calculator
 
Wonderful but i use:
https://stellarium-web.org/
stellarium.org
and there is a lot of almanacs in the market
why your is better?

Russ 23-01-2021 10:34

Re: Almanac and Celestial Navigation Calculator
 
Great Spread Sheets but how do I download to actually USE it.
Sorry not enough of a computer geek
Russ

Rigdaddy 23-01-2021 10:40

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by Smokeys Kitchen (Post 3326486)
This looks great! Always wanted to take the shots and plug it into a good tool.

Wondering if the updated version(s) will be on the same link? Haven't looked at the link yet - but will there be a note regarding the versions?

Thanks again

My question also, I would like to keep up with your efforts.

Kamen 23-01-2021 11:06

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by Russ (Post 3326535)
Great Spread Sheets but how do I download to actually USE it.
Sorry not enough of a computer geek
Russ

The spreadsheet is stored on DropBox (a cloud storage service). When you click on the link, by default it opens in the DropBox Excel viewer. There should be a download button in the top right corner, right next to the Sign Up button. It looks like a down arrow with a horizontal bar below. Click that and then select "Direct download" from the menu.

Kamen

Oronsay 23-01-2021 11:30

Re: Almanac and Celestial Navigation Calculator
 
Way cool. I've been dreaming of a tool like this for years. Amazing effort. I will give it a try over the next few weeks, and let you know if I find any bugs.

SaltyMetals 23-01-2021 11:42

Re: Almanac and Celestial Navigation Calculator
 
Bravo Sean! Impressive work. I did the Yachtmaster Ocean theory, most oif which was Celestial navigation and was drawn to the course by its title "The Study of Heavenly Bodies"
From the little that i remember from 30 years ago i notice one of your screenshots is for details of the moon but surely it should be "lower limb" ?
Andrew

Jono as 2234 23-01-2021 12:51

Re: Almanac and Celestial Navigation Calculator
 
Brilliant. It brings back memories of vomiting below as one tried to work the sight and how next to impossible it was too get anything like an accurate sight when a small boat was jumping around.
Great effort to

picklesandjesse 23-01-2021 12:53

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by Russ (Post 3326535)
Great Spread Sheets but how do I download to actually USE it.
Sorry not enough of a computer geek
Russ

I'm with you Russ.

grantmc 23-01-2021 14:06

Re: Almanac and Celestial Navigation Calculator
 
I think this is a fantastic effort and great work. Thanks too for making it public domain. I'll definitely get out the old sextant and give it a try.


Quote:

Originally Posted by double u (Post 3326408)
I always found the idea of combining a traditional, "handicraft"-type of skill like celestial with computers highly hilarious.

Your perception has validity, but in reality the trigonometry involved is significant. So even the most studious navigators will almost always use a calculator (slide rules till about 1975).

The alternative of course is to bypass the maths altogether and just use sight reduction tables, which I have always thought of as cheating. This is the method most people will have learnt since the second world war.

So for myself, whether using a spreadsheet or tables, they both have the equivalent cheat factor and hilarity.

StoneCrab 23-01-2021 14:24

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by gerson (Post 3326515)
Wonderful but i use:
https://stellarium-web.org/
stellarium.org
and there is a lot of almanacs in the market
why your is better?

Gerson, your comment is rather negative.

I appreciate the stellarium link but don't understand why you would be dismissive of anyone's efforts to share their work with us.

I doubt that there are many on CF that could put together any type of nav program, so lets support his efforts. This is exceptional!

Apollo366 23-01-2021 15:11

Re: Almanac and Celestial Navigation Calculator
 
I too applaud Sean's efforts, very good job and should be very user friendly too.

In the 70's & 80's I used sight reduction tables, Pub 249 version, and other table to arrive at Noon position or LOP, and twice stars. Took too much time away from sailing the boat AFAIC.

Will love to try out nest sunny day here.

Wayne

MIRELOS 23-01-2021 15:14

Re: Almanac and Celestial Navigation Calculator
 
amazing work and a great idea

StoneCrab 23-01-2021 15:20

Re: Almanac and Celestial Navigation Calculator
 
Is it even possible anymore to purchase a current Nautical Almanac?

I tried last year and couldn't find anyone selling them. I called the local planetarium for a referral and they told me that the pro's just use a computer program.

I think this spreadsheet will be a useful tool.

Orion Jim 23-01-2021 15:26

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by StoneCrab (Post 3326764)
Is it even possible anymore to purchase a current Nautical Almanac?

I tried last year and couldn't find anyone selling them. I called the local planetarium for a referral and they told me that the pro's just use a computer program.

I think this spreadsheet will be a useful tool.

One of many sources https://www.landfallnavigation.com/n...anac-3695.html

Apollo366 23-01-2021 15:37

Re: Almanac and Celestial Navigation Calculator
 
Hi Sean, I just tried using the Great Circle calculator and you don't have Hiva Oa or Nuka Hiva, Fr Polynesia in the data base.
As these are the two most common destinations leaving Balboa, Panama for the So Pacific, can you include them or show me how to add them please?

I haven't looked, but are all the other common Coconut Milk Run destinations included?

Many thanks,
Wayne

Apollo366 23-01-2021 15:45

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by StoneCrab (Post 3326764)
Is it even possible anymore to purchase a current Nautical Almanac?

I tried last year and couldn't find anyone selling them. I called the local planetarium for a referral and they told me that the pro's just use a computer program.

I think this spreadsheet will be a useful tool.

You might also look for a Perpetual or 5 yr Almanac at nautical book stores, mine is on the boat in St. Croix so I can't give you any other info just now.

Besides Nautical Bookstores are totally awesome place to visit & browse!

Wayne
Still Landlocked..

StoneCrab 23-01-2021 15:45

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by Orion Jim (Post 3326770)

Thanks Jim.

surazo 23-01-2021 16:14

Re: Almanac and Celestial Navigation Calculator
 
thank SeanPatrick .Huge effort .We will wait the new version .Thanks

hzcruiser 23-01-2021 18:17

Re: Almanac and Celestial Navigation Calculator
 
3 Attachment(s)
Great job, Sean! One can tell how much time and sweat went into it!



I tried the beta 0.24 it with "Numbers" and "Open Office" on a Mac M1. Numbers could open it and seems to work, OO crashes on every attempt.



Attachment 231031

Attachment 231032

Attachment 231033

Midnight Son 23-01-2021 18:27

Re: Almanac and Celestial Navigation Calculator
 
Downloaded and opened with libre office. No problem. I will subscribe to the thread for updates.:thumb:

Best wishes all

Invictus69 23-01-2021 19:16

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by SeanPatrick (Post 3326344)
All,



Anyway, please try it out and let me know what you think. Thanks!


Regards,
Sean




Thanks for sharing - great job and I hope all will appreciate!
Until some years ago I have been occasionally using Starpath Perpetual Almanac (www.starpath.com), mostly for double checking the GPS chartplotter :)

Curious if someone can share experiences or comparison between the two.
:thumb:

SeanPatrick 23-01-2021 23:41

I'm humbled.
 
Wow! What a great response! I would have been happy if just one person thought my spreadsheet might be useful. I cannot tell you all how happy it makes me to hear that multiple people are now thinking about dusting off their sextants or learning celestial for the first time! And thank you, everyone, for the kind words.


I just got settled in after a long day, but I'm going to respond to each and every question asked. But first: I'm going to fix that bug I found this morning and add those two cities that Apollo366 asked for. (Both should be easy fixes, fortunately.)


Be back soon ...


Sean

SeanPatrick 24-01-2021 01:59

Update!
 
Okay, here is the next version:


Zipped spreadsheet.



Unzipped spreadsheet.


(Mods: would it be possible to update the links in the first post, please? Thank you!)



Bug fixes/changes:


  • Fixed a bug causing the "Data Series" tab to display incorrect distances to some stars. (The workbook doesn't even calculate stellar distances.)
  • By request: Added Hiva Oa and Nuku Hiva, Fr. Polynesia to the city database.
  • Added unit indicators to the data series table headers.
  • Clarified the note about stellar magnitudes.
  • Added some credits to the "Instructions" tab.


Also, I apologize for the trouble some people have had downloading the workbook. I need to find a better [easier] file sharing service. (Any suggestions?) But, as Kamen said: you may need to click the little arrow at the top right of the screen before the download button becomes visible.


https://i.imgur.com/Xr86U7Dh.png




Don't feel bad. I even had trouble figuring it out myself. :facepalm:




Q&A to follow shortly ...

SeanPatrick 24-01-2021 04:13

Q&A Time!
 
I'd like to start with one question in particular, because I think it is the best question I've gotten so far:

Quote:

Originally Posted by gerson (Post 3326515)
Wonderful but i use:
https://stellarium-web.org/
stellarium.org
and there is a lot of almanacs in the market
why your is better?

My answer is: It isn't. Stellarium is an awesome program and I use it all the time. But AFAIK, Stellarium won't do all the things that my spreadsheet does. Nor will my sheet do what Stellarium does. And you are correct that there are a lot of other almanac programs out there. Some of my favorites are: Teacup Navigation by Roger Farley, AstroNav by NavSoft, Navigational Algorithms by Andres Ruiz González (also on mobile) and MICA by the USNO. Some of those are free, some aren't - but I think they are all well worth the price. So ... why mine?

Maybe a little background would help: My absolute favorite almanac program of all time was ICE - the USNO's Interactive Computer Ephemeris. It ran on DOS and was geared mainly toward navigation, with (IIRC) a little extra astronomy stuff. Well, ICE has long since been replaced by MICA and another program (the name of which escapes me at the moment). Now, I can still run ICE today using DOSBOX or similar, but the delta-T values are so far off that it is just not worth it. Hence the inclusion of a user-editable delta-T tab in my spreadsheet.

My next favorite program is MICA, and it still outputs accurate data today. Unfortunately, the one and only vendor of the program (Willmann Bell) has closed. Hopefully a new vendor will be found soon. Besides that, MICA is sort of the opposite of ICE in that it seems mainly geared toward general astronomy, with navigation taking a back seat. It is incredibly powerful, but not my ideal. And all of the other programs are great, too. But I always think I would like to see things done a little differently. So ... I made my own. Eventually I plan to turn it into an app (I'm learning how to do that now) and maybe a PC program too ... a command line program. :)

Bottom line is: there is absolutely no reason not to use Stellarium (unless it really does have errors in some proper motions as has been discussed on NavList) or any other program or method - as long as you like it. There is also no reason you cannot use them all. Except the USNO website (another great resource) which is down for an indeterminate amount of time. :(

Tell you what: if you are not completely satisfied with my spreadsheet, I will give you a full refund! ;) Seriously, though: thank you for giving me the chance to explain a little about my motivation.

Cheers!


Quote:

Originally Posted by double u (Post 3326408)
I always found the idea of combining a traditional, "handicraft"-type of skill like celestial with computers highly hilarious.

I do too. It always feels like cheating to me (like grantmc said) in this context ... or almost even (dare I say) pointless. (And I'm not being flippant.) The idea being that you're planning to use celestial when all the electronics don't work. I have shelves upon shelves of navigation books. I even have a 1781 printing of the Tables Requisite. However, sometimes I want to reduce a round of sights quickly, or maybe go back over a bunch of old sights, or even just use the incredible computing power available to play around and see what time the Sun set in Timbuktu ... in 1502.

But there's also another thing to consider: celestial as a backup. Frank Reed has made an app which instantly allows you to check if your GPS is inaccurate for whatever reason. All you need is a sextant, his app and maybe a dodgy GPS signal. Or you could just use it to practice your sextant technique, it's great for that, too. That's another thing: tools like this are great for learning and checking to see if you're doing it right.

Anyway, point is: there are some good reasons for it. But I still haul out the tables from time to time. ;)


Quote:

Originally Posted by Smokeys Kitchen (Post 3326486)
Wondering if the updated version(s) will be on the same link? Haven't looked at the link yet - but will there be a note regarding the versions?

Unfortunately no, not for the time being. I apologize. I will work on a better delivery method. I do plan on setting up a web page soon with other resources.


Quote:

Originally Posted by Russ (Post 3326535)
Great Spread Sheets but how do I download to actually USE it.
Sorry not enough of a computer geek
Russ [et al.]

Apologies. Please see my previous post.


Quote:

Originally Posted by SaltyMetals (Post 3326596)
From the little that i remember from 30 years ago i notice one of your screenshots is for details of the moon but surely it should be "lower limb" ?
Andrew

I'm not sure where you saw that, but it is entirely possible. There are in fact times when you need to use the upper limb. Certain arrangements of the celestial bodies, cloud covering the lower limb, doing a backsight, etc. These conditions are admittedly the exception, though.


Quote:

Originally Posted by StoneCrab (Post 3326764)
Is it even possible anymore to purchase a current Nautical Almanac?

It may not be in the near future, who knows? With the USNO site down and Willmann Bell closing their doors, I wonder. But, as Apollo366 mentioned: there are some great long-term almanacs available. I highly recommend this one:


Long Term Almanac 2000-2050: For the Sun and Selected Stars with Concise Sight Reduction Tables, 2nd Edition


Quote:

Originally Posted by Apollo366 (Post 3326776)
Hi Sean, I just tried using the Great Circle calculator and you don't have Hiva Oa or Nuka Hiva, Fr Polynesia in the data base.
As these are the two most common destinations leaving Balboa, Panama for the So Pacific, can you include them or show me how to add them please?

I haven't looked, but are all the other common Coconut Milk Run destinations included?

Many thanks,
Wayne

Done. The data was copied almost verbatim from Pareto, so I don't know what all cities are in there. I just checked a few to see if it all worked. It's trivial (for me) to add new ones. The sheet is [supposed to be] hidden and locked because I didn't really anticipate adding to it much. Maybe I'll unlock and unhide it in a future version, IDK.


Quote:

Originally Posted by hzcruiser (Post 3326865)
I tried the beta 0.24 it with "Numbers" and "Open Office" on a Mac M1. Numbers could open it and seems to work, OO crashes on every attempt.

Yeah ............ I'm not too happy about the fact that LibreOffice and Numbers can simply bypass the password protection I carefully put in place to keep anyone from copying months of my hard work. But the blame for that rests squarely on Microsoft's shoulders and no one else's.

Thank you for testing it, though!


Quote:

Originally Posted by Invictus69 (Post 3326898)
Until some years ago I have been occasionally using Starpath Perpetual Almanac (www.starpath.com), mostly for double checking the GPS chartplotter

Curious if someone can share experiences or comparison between the two.

I haven't tried Starpath's so I don't know. But I do know they are a great school, so I imagine whatever they are using is at least as good as my spreadsheet.


Anyway, I hope that answered some questions. If I missed any, please let me know. I will try to answer as many future questions as I can, too.

Cheers!

Sean

SeanPatrick 24-01-2021 05:20

Tips
 
By the way, it just struck me that it might not necessarily be immediately obvious that some of the cells that are automatically filled can also be filled manually. This includes:


  • The DR, date and the dip and IC on the "Sight Reduction" tab, so you can use your own DR calculation or sights from different dates.
  • The "Log", so you can add other types of fixes/entries.
  • The departure and destination on the "Course" tab, so you can plot a course to and from any two points on Earth, not just the cities in the database.



Also, a tip for Excel in general: If you want to change the value in a cell, but don't want to select another cell (like [Enter] and [Tab] will do), you can hit [Ctrl]+[Enter]. This will change the data in the cell, but the same cell will remain selected.

surazo 24-01-2021 10:57

Re: Almanac and Celestial Navigation Calculator
 
Sean . Really great and amazing work .Thanks to share it with us .Normally we choose to use one program or app and know how to use in a better way and sometime have another as a back up .My guess is to use your app or data sheets as a main and to return and re-learn how to use and old sextant friend .
Thanks again
Hernan

Apollo366 24-01-2021 15:18

Re: Almanac and Celestial Navigation Calculator
 
Thank you Sean, works very well, I'll check for other So Pac destinations in the following weeks & let you know if any more are needed.

BTW what was your source of destinations input again? I didn't recognize your reference.

Apollo Wayne

Lodesman 24-01-2021 15:40

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by StoneCrab (Post 3326764)
Is it even possible anymore to purchase a current Nautical Almanac?

Goto the sources: https://bookstore.gpo.gov/products/n...anac-year-2021

or: https://www.admiralty.co.uk/publicat...utical-almanac

Admiralty has an e-pub option.

SeanPatrick 24-01-2021 23:22

Re: Almanac and Celestial Navigation Calculator
 
Quote:

Originally Posted by Apollo366 (Post 3327471)
BTW what was your source of destinations input again? I didn't recognize your reference.


Pareto Software, LLC.



https://simplemaps.com/data/world-cities


It is also noted on the "Instructions" tab, and at the bottom of the waypoint list on the "Course" tab.


Cheers!

SeanPatrick 25-01-2021 03:54

Re: Almanac and Celestial Navigation Calculator
 
Here's another example of the type of things you can do with the data series calculator:


https://i.imgur.com/rx8sgwJ.png




This chart of the recent conjunction of Jupiter and Saturn was made using the formula:

acos(sin(dec.1) ˇ sin(dec.2) + cos(dec.1) ˇ cos(dec.2) ˇ cos(GHA1 - GHA2 ))

Hint: To convert a Julian Date to an Excel date, simply subtract 2415018.5 from the JD and format it as a date and/or time. Note, however, that Excel itself does not recognize dates before January 1st, 1900.

SeanPatrick 27-01-2021 16:15

Re: Almanac and Celestial Navigation Calculator
 
New website:


https://www.cooknavigation.com


All times are GMT -7. The time now is 18:57.

Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2021, vBulletin Solutions, Inc.


ShowCase vBulletin Plugins by Drive Thru Online, Inc.