Home

Introduction

AstroExcel is borne from a life long love of Astronomy, Computing and Maths. My aim has been to provide astronomy related calculations in Excel using custom functions, so if you know how to use Excel then you should be able to use AstroExcel. AstroExcel won’t work using open office or other Excel compatible spreadsheet programs because to keep the usage simple I have created over 100 new functions in Excel based around my hobby of astronomy. The advantage of this approach is no knowledge of how these functions work is needed to use them, but a basic understanding of astronomy and Excel will help.

The algorithms behind the functions are well published, in many cases I have simply taken the algorithm and converted it into an excel function. In others I have taken public domain code and converted to VBE, because this is a project that started over 10 years ago some of the original sources are lost, but where possible I attribute the original source in the code, my main sources of inspiration have come from…

  • Astronomical Algorithms by Jean Meeus
  • Astronomical Formulae for calculators by Jean Meeus
  • Practical Astronomy with your calculator by Peter Duffett-Smith
  • British Astronomical Association Journal and members handbook
  • Magazines and on-line articles.

So what can you do with AstroExcel?

As with most things, what you can do is down to your imagination below are some examples of how I have used AstroExcel to enhance my knowledge of astronomy.

Plotting a partial solar eclipse

Using AstroExcel functions to calculate the position and size of the Sun and Moon and Excel’s bubble chart you can quickly plot what a solar eclipse will look like from your location, add a few spin buttons and you can control the time dynamically.

Example: Image of solar eclipse prediction
Charts produced using AstroExcel to predict viewing conditions at a specific location

Visual representation of the altitude of the Sun and Moon

Using the same AstroExcel functions as above with the addition of altitude conversion for your location you can view the rise and set of the Sun and Moon over the day.

Example: Image of Sun and Moon altitude for each hour of the day
Altitude of the Sun and Moon calculated by AstroExcel

Equation of Time

Using the just a few AstroExcel functions you can create a chart of the equation of time for your location, handy if you make sundials.

Example: Image of equation of time chart calculated using AstroExcel
Equation of time chart calculated using AstroExcel

Calculating the position of the planets

If your interest is in the solar system, then AstroExcel has a suite of functions to calculate the positions of the planets and Moon including calculating their apparent sizes and magnitudes. You can calculate the altitude of each planet for any hour of the day to produce a rise/set chart or perhaps simply plotting the positions of Jupiter’s main satellites, there are plenty of functions for these all you need to know is how to ask for example ‘what is the position of Jupiter‘ no knowledge of complicated celestial mechanics is needed to indulge your interest in astronomy.

Example: Image of a number of charts created using AstroExcel to visualise the positions of teh planets and Jupiter's satellites
Examples of charts created using AstroExcel to plot planetary positions

Equipment Calculations

If your interest is more technical, then perhaps you need to calculate how focal ratio affects exit pupil size for different eyepieces or how large the zone of critical focus is for your Halpha telescope or how many pixels of star trails you will have for a given lens pointing in a given direction for a given exposure, AstroExcel includes lots of calculations for categorising your equipment or for making decisions on the right set-up for a particular project.

Example: Image of charts showing astronomically related equipment calculations in AstroExcel
A few of the equipment calculations that can be performed using AstroExcel

Year Planner

Perhaps you want to create a year planner for your astronomical society, you’ll want to show key astronomical events such as lunar phase, apogee and perigee, solar eclipses, society events, sun rise and set, oppositions of the planets, major meteor showers etc. You don’t want to be looking these up manually every year to enter into your year planner simply use AstroExcel to calculate these for the year and then automatically reformat the chart.

Example: image of year planner produced using AstroExcel functions
Year planner created using AstroExcel functions for my local society

Radio Astronomy Antenna calculations

AstroExcel is not just for visual astronomy, because it has been guided by my interests there are functions to calculate basic Yagi designs for meteor detection

Example: Image of Yagi antenna designs with dimensions calculated in AstroExcel
Yagi antenna dimensions calculated using AstroExcel

Object visibility

If you know the RA and Dec of an object you can find its altitude at various times, if you also know when the Sun rises and sets then you can overlay onto this the hours of darkness, providing a visual view of the object visibility – I guess you could add moon age and altitude to get a real sense for if the object is visible or not but I didn’t need to go that far – do this for a whole catalogue and you will have a planner for a mammoth observing session or a Messier marathon! Here is an example of how AstroExcel can really excel at producing calculations that would take forever in a planetarium program, and provide views onto the data that would otherwise be missed. I have included the Messier catalogue in AstroExcel for reference.

Example: image of messier visibility by month and by hour
Visualising Messier object visibility using function in AstroExcel

 Annual Chart of planet visibility

Armed with the functions in AstroExcel you can really go to town, calculate the time of twilight and each of the planets rise and set times to produce the classic annual planet visibility info-graphic that you would normally only see in Sky and Telescope or the BAA annual handbook, these charts are dependent on your latitude the difference with this one is it is calculated for your exact location. It would be just as easy to create something similar overlaying moon phase and rise set times.  But it doesn’t have to be that fancy, if all you want is a simple table of rise and set time this is very easy to achieve using the built in functions in AstroExcel.

Example: Image of complex chart in AstroExcel showing how night time varies throughout the year overlaid with planet rise and set data
Annual planet visibility chart calculated using AstroExcel

Detector field of view

Choosing the correct combination of detector and telescope is important, with AstroExcel you can calculate the field of view the detector/telescope combination to help inform your choice, because it is ‘just a spreadsheet’ you can try out various combinations of detector and telescope until you get the combination that suits your application. AstroExcel has functions for detector field of view, detector resolution, minimum focal length for a detector to work optimally, the size of coma at the corners of the field, the % of the detector that is fully illuminated etc.

Example: image of chart showing the relative field of view from combinations of telescope and detector
Field of view calculations made with AstroExcel

And then some…

The functions in AstroExcel allow you to create the calculations you need for your project, the examples above are included in the spreadsheet to get you going, but how you use AstroExcel will come down to your interests, I’ve heard of people using it to correlate shark attacks with Lunar visibility, plan a cubs evening observing, help with astrology – well you get all sorts – etc. AstroExcel is not a prebuilt spreadsheet – although it does come with lots of examples – it is a framework of over 100 astronomy related functions that you use in the same way you would use ‘sum’ or ‘average’ in Excel to produce all the above and more…

What’s stopping you. 🙂

Download

Documentation

Advertisements

25 thoughts on “Home

    1. Thanks for the comment, I have now added a new function MoonRise() this will calculate the Rise, transit or Set of the Moon, I’ve tested against calsky.com and accuracy is within 1 minute.

      Note: the time returned is decimal fraction of a day (change cell format to Time) all times are UT so you have to convert to your time zone and DST.

      SimonTelescopium

      Liked by 1 person

  1. Fabulous ! A big thank you for these functions that I will try to use to help me identify time periods favorable for astronomical photograph.

    Like

  2. An extraordinary effort BUT….I am not certain of the accuracy of the altaz function. I have been attempting to add altitude and azimuth columns to the “Messier” spreadsheet but in comparing the results to what I know are correct calculations, I am coming up with significant discrepancies.

    Like

    1. I’ve checked the function and can’t find any problems, however there are a few gotchas, that have caught a few people out, I’ve listed below hopefully it is one of these and I can claim user error 🙂 and solve your problem.
      1. format of parameters for the function is altaz(days after 1/1/2000, Dec, RA, Latitude, Longitude, Index) Note Dec, RA not RA,Dec
      2. RA is in degrees not hours – this is true for all astroexcel functions – multiple hours by 15 to get degrees
      3. Atmospheric refraction is not calculated use the refraction function if you want to include this.
      4. If comparing with other programs check the RA and Dec they are using as they may be using the current epoch for the RA and Dec of an object and this will be different to the publish positions which will state an epoch usually 2000.0
      5. If comparing with other programs check date and time, AltAZ uses UT, other programs may us local time (i.e. include time zone and daylight saving)
      6. If comparing with other programs check you are using the same location

      Like

      1. i discovered the problem. It’s not with the ALTAZ function at all. The problem is that the RA degrees shown on the “Messier” tab are not correct. You must have used the wrong formula to convert. Based on the numbers you have, you must have used (Min/60)+(Hrs*15).

        The correct formula is ((Min/60)+Hrs)*15.

        Additional columns I have since added to covert the RA/DEC on the Messier page now produce correct results.

        Like

    1. Harry,

      It is saved as a Excel ’97-2003 document so shouldn’t cause problems.

      have you enabled macros – the whole spreadsheet is about 200 visual basic functions and the spreadsheet can’t run without them.

      I started developing this a long time ago and probably passed through Office 2013 at some point.

      Simon.

      Like

      1. Date Moon Rise Moon set
        05/04/2018 22:01:52 7:48:17
        06/04/2018 22:59:14 8:28:11
        07/04/2018 +23:53:14+ 9:12:50 <======= not correct
        08/04/2018 23:53:12 10:02:15
        09/04/2018 0:41:55 10:57:03
        10/04/2018 1:25:17 11:54:30

        Like

  3. Hi Simon,
    your ability with astronomy is amazing to me. I work in the financial industry and I’m doing research on how the planet movement affects the price of stocks and not only. But now is my goal.
    I’m looking for person like you that like the idea to work together on the topic. Could you be interested in it ?

    In the meantime i’m looking for a sheet in xls with planet position in degree, do you know where I can find it ?

    I hope that my proposal will intrigue you to work on it.

    Kind regards, Ciro

    Like

    1. It’s not that specially vedic astrology we’re knowing about these constilation before astronomy subject emerged. But yes you can make a transit chart. That will help using ephemaries. That will be a great help

      Like

Leave a Reply to simontelescopium Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s