Almanac and Celestial Navigation Calculator

[Sean C]

All,

Here is a link to the latest version of my Excel almanac. I have added a data series calculator, sight reduction calculator, position log, great circle route planner and map. (It's really just a consolidation of several different spreadsheets.) A few bugs from the last version have also been squashed.


The new data series calculator can be used to make charts like the following for data comparison or analysis:


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


I would greatly appreciate it if everyone could test it out to make sure everything works. Nothing rigorous. I'd just like to know if it all functions as intended. I've been having a little trouble locking some parts of the spreadsheet while still keeping it functional. But I think I've got it solved - for now. Not the most elegant solution, but maybe I can improve on it in the future.

Thanks!

Shawn


PS
Don't let the warning about macros scare you. Neal knows where I live ... seriously.



(https://www.dropbox.com/s/4e2i1qypnocx6t4/CelNav%20%28Beta_v023%29.zip?dl=0)Link to the zipped spreadsheet. (https://www.dropbox.com/s/n2g4ghhkdkkhuj9/CelNav%20%28Beta_v024%29.zip?dl=0)


(https://www.dropbox.com/s/578elbor21g747y/CelNav%20%28Beta_v021%29.xlsm?dl=0)Link to the unzipped spreadsheet. (https://www.dropbox.com/s/5vuauwg32fw6wzn/CelNav%20%28Beta_v024%29.xlsm?dl=0)


***NOTE: This spreadsheet is only guaranteed to work in current versions of Microsoft Excel.***


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




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


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




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




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


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


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


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

[vienna]

Yeah, hey...

...like, do you know the way to San Jose...?...





<O>

[Catfish]

Hello Nathaniel umm Shawn,
i always wanted to ask you since you advertised "ask me" ... seems to be a nice tool ! :up:

[Skybird]

Its the first chess program I have ever seen that gives out the moves in Longitudes and Latitudes. :hmmm:

[Sean C]

Hello Nathaniel umm Shawn,
i always wanted to ask you since you advertised "ask me" ... seems to be a nice tool ! :up:


Please feel free to ask me anything about celestial navigation. I really enjoy teaching people about it!


I should probably ask Neal to change my forum name. I chose it as a nod to Nathaniel Bowditch, the editor of The American Practical Navigator (now mostly known simply as Bowditch). But it is probably a bit confusing ... and obscure.


Hey Neal, if you're reading this: can you please go ahead and change my forum name to something like "Sean C"? That's what I use on some other forums. I'll send you a PM, too. Thanks in advance!

[Sean C]

Its the first chess program I have ever seen that gives out the moves in Longitudes and Latitudes. :hmmm:


Pffft. I'm terrible at chess. And anything involving strategy. Like Steel Beasts. :oops:

[Sean C]

By the way, there's no reason this spreadsheet can't be used with a flight sim like MSFSX or whatever as well. The route planner includes a database of about 15,500 cities worldwide (many nowhere near water) and will plot the shortest route between any two.


The sight reduction calculator should work with aerial observations, as well.


There are a couple of minor bugs I have left to kill. (See the bottom of the instructions tab.) And some more features I want to add.


I was thinking of making a special :subsim: edition, too.

[ET2SN]

By the way, there's no reason this spreadsheet can't be used with a flight sim like MSFSX or whatever as well.

Want me to make the intros for you over at Flightsim.com? :yep:
I know folks who will use if they know it exists. :Kaleun_Cheers:

BTW, is it possible to download the zipped version?

[Sean C]

Want me to make the intros for you over at Flightsim.com? :yep:
I know folks who will use if they know it exists. :Kaleun_Cheers:

BTW, is it possible to download the zipped version?

You should be able to save the zip file to your PC and extract it. I'll have to see if there's a problem when I get home.

By all means, let the folks at flightsim.com know about it. I'll try to get over there and post a link, too. Thanks!

[ET2SN]

You should be able to save the zip file to your PC and extract it. I'll have to see if there's a problem when I get home.

By all means, let the folks at flightsim.com know about it. I'll try to get over there and post a link, too. Thanks!

Get in touch with Tom Gibson from "Cal Classics" on the FS2004 forum. :up:
This is a "can't lose" deal for the classic airliner crowd. :yeah:

[August]

I've done those calculations by hand back in the day. My left eyelid still twitches every time I think of it!

[Sean C]

is it possible to download the zipped version?


Well, I'm home. I must admit: it took me a while to figure out how to download the file myself. Apparently you might need to "open the sidebar" (see attached pic) before you see the download button. Maybe I should use another file sharing service. :hmmm:


Also, here is a link to the unzipped spreadsheet (https://www.dropbox.com/s/w19g9xeeg4qpflk/CelNav%20%28Beta_v020%29.xlsm?dl=0). I also attached it to the first post.




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

[Sean C]

FYI: It seems my spreadsheet may lose functionality or output incorrect data if opened with a program other than MS Excel.


First post updated.

[Sean C]

Bug fixes/changes:


There was a bug causing some leap year dates to be calculated incorrectly. I fixed it ... I think.
Updated the delta-T values and extended the table into the past just a little. o.O
The notes on the Almanac tab did not reflect the changes. I polished them until they did.


Should be even more accurate now. (Still only guaranteed to work on Excel, sorry.)


See my sig or the first post for the link.


Cheers!


PS
I am going to have that Dionne Warwick song stuck in my head forever now. Thanks, vienna.

[Skybird]

All nice and well, but the engine still cannot play the Subsim Defence, Skybird variation. :hmph:








:O:

[Sean C]

Links have been updated in the first post. Or, see my sig.



Bug fixes/changes:




Fixed the bug preventing the sight reduction cells from clearing correctly ... I think.
Delta-T values are now interpolated for dates between table entries. This will have the most noticeable effect on really old dates which cannot be completely trusted anyway. :06:
Added a note about compatibility issues. We're just not right for each other. (LibreOffice, not you - you're cool.)
Discovered the "Application.ScreenUpdating" VBA object. The spreadsheet generally runs more smoothly after a liberal application to the macros (as you do).
Added a nifty sky chart showing the positions of all visible traditional navigational bodies for the currently selected time and location which isn't quite what I intended, but looks pretty cool nonetheless.

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


I still want to add Sunrise/set/twilight times. And maybe even some other calculators such as "Time Sights" and "Lunars". We'll see ...


Enjoy!


Sean C.

[Sean C]

Bug fixes/changes:



Fixed a long forgotten bug causing DRs to be calculated incorrectly when crossing the IDL.
Fixed the bug causing the "Send to Log" button to overwrite manual entries.
Added a "Data Series" tab which calculates data for a specified period and interval.
Created a new bug by doing the above. (See "Instructions" tab.)
Added a bar below the sky chart which indicates day/twilights/night.
Added notes about stellar positions/magnitudes on the "Instructions" tab.
Updated my contact email address. Please do not send mail to the previously listed one. Thank you.





See the first post or my sig for the link.




The new data series calculator can be used to make charts like the following for data comparison or analysis:


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



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




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

[Sean C]

Just found a new bug: the data series calculator gives incorrect distances for stars. https://www.cruisersforum.com/forums/images/smilies/facepalm.gif That will be fixed in the next version.

[Sean C]

New version is available at my new website:


https://www.cooknavigation.com




Bug fixes/changes:




Fixed a bug causing the data series tab to display incorrect distances to some stars.
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.



Cheers!

[Sean C]

New version posted at my website (https://www.cooknavigation.com).


Changes:


v 0.2.6


Added printable bodies list
Reformatted notes on "Precession and Stars" tab



https://images.squarespace-cdn.com/content/v1/6011157c6360d66d1e39e503/1612265359694-0M6X6COFDEXXY2ZQULS0/ke17ZwdGBToddI8pDm48kFAH8DpZmQzoJQrc2JPKSaMUqsxRUq qbr1mOJYKfIPR7LoDQ9mXPOjoJoqy81S2I8N_N4V1vUb5AoIII bLZhVYy7Mythp_T-mtop-vrsUOmeInPi9iDjx9w8K4ZfjXt2djW1f7o29y7wd1Udq8xtwSV tU7GkhTVzOEg3589618wSCjLISwBs8eEdxAxTptZAUg/cnss7a.png

[Sean C]

New version at my website (https://www.cooknavigation.com).


beta v 0.2.7 Bug fixes/changes:


Fixed a bug causing the intercept "away/toward" cell for sight #10 to remain empty
Updated the cities database from ~15,500 cities to Pareto's current [free] offering: 26,570 cities (+ the two suggested cities)




*** The spreadsheet is now also in the "Utilities (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745)" section of the SubSim downloads area. ***



And here's an animation I made showing "Xerxes' Eclipse" on Oct. 2nd, 480 BC:




https://static1.squarespace.com/static/6011157c6360d66d1e39e503/t/601e70015690494e7eb2b0db/1612607518046/XerxesEclipse2.gif

[Capt Codetrucker]

Hi Sean C - Just a simple word of gratitude for your work and kind generosity. Thank you.

[Sean C]

Hi Sean C - Just a simple word of gratitude for your work and kind generosity. Thank you.


You're very welcome!


By the way, I just added my PostScript plotting sheets to my site. I'll upload them here too, if there's any interest in them.


Cheers!

[Sean C]

New version available in the downloads (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745) section.



v 0.2.8 - 2021, Feb 28




Increased the number of decimals of azimuth included in the data series to a ridiculous precision.
Fixed a bug causing the spreadsheet to display wildly inaccurate values for the equation of time under certain circumstances.

[Sean C]

New version available in the downloads (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745) section.


v 0.3.1 - 2021, Oct 31


Added rise, set, transit and twilight calculator.
Increased precision of azimuths on the printable list to 0.1°.
Added button to Data Series calculator to get the date and time from the Almanac.
Added ecliptic longitude of Sun, Moon and planets to the Data Series calculator to facilitate determination of the times of equinoxes, conjunctions, etc.
Corrected a minor error in the calculation of Ha of the LL of the Moon and Sun at extremely low altitudes.
Added PA correction to the Sun.
Updated delta-T table.
Extended visibility chart to -5°.
Minor formatting changes.

[Sean C]

Apologies, but I just found a bug ... of course. And it's a bug I thought I had already squashed. Maybe I'll get it right this time ... https://www.cruisersforum.com/images/smilies/rolleyes.gif [Link (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745)]

v 0.3.2 - 2021, Nov 2



Fixed a bug [again] causing the spreadsheet to display incorrect values for the equation of time under certain circumstances.

[Sean C]

I have added the option to the sky chart to display all 1,535 stars in the basic FK5 catalogue (see screenshot, below). As with the navigational stars, corrections for proper motion have been included. There is a check-box below the chart to show or hide them. These will not be selectable in the almanac or sight reduction calculator. But if you hover your mouse over them, the calculated azimuth and altitude will be displayed. So, they can be used for navigation in a pinch ... you'll just have to plot the LOPs manually. Maybe I will change this in the future.

I have also tweaked the map alignment. Waypoints shouldn't be plotted off the edge of the map anymore [mostly], and the waypoints, DR and fix should be plotted a bit more accurately. (Although at this scale I admit it's hard to see the difference.)

As always, the new version is available in the downloads (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745) section. Cheers!

v 0.3.3 - 2021, Nov 7



Added the option to the sky chart to display all 1,535 stars in the basic FK5 catalogue. (This increased the size of the spreadsheet from 2.5MB to 3.7MB.)
Tip: You can hover your mouse over any star to see its calculated azimuth and altitude.
Fixed a bug causing the almanac date and time to change when using the data series or rise/set calculators. (Almanac date and time will still change when using the sight reduction tab.)
Improved map alignment. Waypoints and DR/Fix will now be plotted more accurately.
Minor formatting changes.



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

[Sean C]

New version available in the downloads section (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745):

v 0.3.4 - 2022, Oct 24
· Added day of the week calculation on Almanac tab.
· Added the option to display the celestial equator and ecliptic to the sky chart.
· Added the ability to increment time on the Almanac tab.
· Updated the delta-T values.
· Moved the "Auto-Fill" button for the date of celestial sights and added an arrow to hopefully clarify the button's purpose.

[Sean C]

New version available in the downloads section (https://www.subsim.com/radioroom/downloads.php?do=file&id=5745):


v 0.3.5 - 2023, September 10
· Fixed a very old bug causing the LMST/LAST to exceed 24h.

v 0.3.6 - 2024, October 9
· Updated Delta-T values and extended the predictions to 2035.
· Corrected an error in the Delta-T tables which caused all BC years to be off by one year. The error resulted in a very minor mis-calculation of the position of all celestial bodies, most notably: the Moon.
· Updated the text above the Almanac output fields and printable list to indicate whether the year is BC or AD. This text will now also show the Julian calendar year for BC years. (I.e. it will not display a year "zero".)
· Fixed a bug which caused the times of rise, set & transit of the Moon to be calculated incorrectly under certain circumstances.