### 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.

#### 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.

#### 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.

#### 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.

#### 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.

#### 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.

#### 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

#### 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.

#### 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.

#### 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.

#### 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. 🙂

Thanks a lot. its an excellent application. but Moon rise and set times in e.g. Draconids 2011 are not correct. could you please help

LikeLike

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

LikeLiked by 1 person

thanks a lot. Great Job. 👍👍👍

LikeLike

Very big grat work ! compliment .

LikeLike

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

LikeLike

Can condivide with you a my work .

http://scambiolink.superweb.ws/foglioexcelastronomia.html

download http://scambiolink.superweb.ws/acceptNOTE.php

LikeLike

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.

LikeLike

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

notRA,Dec2. 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

LikeLike

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.

LikeLike

Thanks for letting me know! There is now a new version with the correct values

LikeLike

I keep getting data may be lost error when I open d/load.

LikeLike

sorry I don’t know why that would be, i’ve downloaded it and it works fine for me. I have also updated a new version so you could try that.

LikeLike

This is a mammoth effort! It’s incredible since I did not come across this when doing my own programming with ASCOM and AAplus! This is a really great work.

LikeLike

Are you still supporting this software in 2017?

LikeLike

No not really it is more ‘copy left’ than supported – I do occasionally tinker but am very busy at work…

LikeLike

Can anyone assist? When changing longitude in the year planner, excel freezes. Is there an undocumented bug? I am using MSOffice2013.

LikeLike

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.

LikeLike

Simon,

Superb piece of work.

I’m a budding astrologer and this work of yours shortens my time to learn celestial bodies.

Thanks,

Sri

LikeLike