AstroExcel

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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