AstroFunctions Excel Spreadsheet for Astronomical functions.

99 Astronomical Functions/constants in Excel!
I often find myself needing to calculate a Julian date, or find out local conditions for observing such as moon phase, altitude etc. so I have created a spreadsheet that has all the functions already included, including many examples of their use.

I can't take all the credit for this as some functions have been found from the internet, others have come from books such as ‘Astronomical Algorithms’ by Jean Meeus and another personal favourite ‘Practical Astronomy With Your Calculator’ by Peter Duffett–Smith. However I have tried to standardise the inputs especially around dates so each function uses the same formats.

This is an ongoing project of mine so will never be fully complete so check back here for updates! If you have something you want to add please contact me and I'll endevour to add it!

Also note the spreadsheet has a lot of examples but these are just worksheets I have created for my own purposes, you can delete all but the help worksheet, all the functions will be retained, or if you prefer, update and modify these examples.

Source code is not protected, so if you want to tweek a function, check how it is calculated or re-use in a different language/application feel free to do so.

Usage

Just like normal functions in Excel, these Astronomy functions don't require any knowledge of the maths involved, you simply enter the function in a cell include the parameters for the function (see the help tab) and the result appears, for example what to know the field of view of your CCD? just type
'=CCD_FoV(sensor_mm,FocalLength_mm)'
where sensor_mm is the size of your CCD in mm and FocalLength_mm is the focal length of your 'scope in mm.

In general I have standardised parameters, for instance all dates and times are in days and fractions of a day from 1st Jan 2000 (and there are functions to convert to civil dates and times or Julian dates etc.

Functions include

Constants

total of 20 constants with their dimentions and links to their source, all constants start with an underscore, so need to use the speed of light in a calculation just type '_C' in your formua for the speed of light in metres per second

MagLimit(diameter in mm, "Author")
CCD_FoV(CCD dimension in mm, telescope focal length in mm)
ExposureIncrease(Mag increase)
ExitPupil('Scope Aperture, Scope Focal Length , Eyepiece Focal Length)
Coma_Eyepiece(F_Ratio, Off_axis_Angle)
Coma_CCD(F_Ratio, Focal Length in mm, CCD Dimention in mm)
FoV_by_Drift(declination of object in degrees decimal, time to cross field in seconds
LightThroughput(Aperture, obstruction, No_of_SurfaceA, SurfaceAEfficiency, No_of_SurfaceB, SurfaceBEfficiency, No_of_SurfaceC, SurfaceCEfficiency, aperturecomparison)
EquivalentContrastAperture(CentralObstruction, Aperture)
NewtSecondarySize(Focal_Ratio, Secondary_to_FocalPlane, Optional Illuminated_Diameter)
NewtSecondaryOffset(Primary_Diameter, Fully_Illuminated_Field_Diameter, Secondary_Diameter, Focal_Length)
AiryDiskmm(F_Ratio, Optional wavelength_nm)
AiryDiskArcSec(F_Ratio, F_Length, Optional wavelength_nm)
CCDMinFocalLength(Aperature_mm, Pixel_Size_Microns, Optional Wavelength_mm)
CCDMinFocalRatio(Pixel_Size_Microns, Optional Wavelength_mm)
FullIlluminationDueToFocuser(Aperture_mm, F_ratio, Focal_Point_to_base_mm, Focuser _neck_width_mm)
RealFoVfromEyepieceFoV(Focal_Length_of_Scope, Focal_Length_of_eyepiece, eyepiece_Field_of_view)
RealFoVfromEyepieceFieldStop(Focal_Length_of_Scope, Eyepiece_Field_Stop)
FocalLengthfromCCDImage(Object_Size_pixels, Pixel_size_microns, Object_Size_ArcSec, optional binning)
CriticalFocusZone(F_Ratio, Optional Wavelength_nm)
DustDonutDistance(PixelSize_microns, F_Ratio, donut_Size_pixels, Optional binning)
StarTrail(Focal_Length_mm, Exposure_Length_s , Optional Dec_of_Object_degrees)
StarTrailPixel(Focal_Length_mm, Exposure_Length_s , Pixel_Size_microns, Optional Dec_of_Object_degrees)

Astronomical

Obliquity(days after j2000, index)

Sun

SunRise(days after j2000, Latitude, longitude, index,altitude)
CarringtonRotation(DaysafterJ2000.0)
Sun(days after J2000, index)
SunSize(Days after J2000)
Sun_RelativeSunspotNo(No of sunspot groups, no of total spots)
SunLongitude(DaysafterJ2000, index)

Moon

Moon(days after j2000, index)
tMoon(days after j2000.0, Latitude, Longitude, Index)
MoonAge(DaysAfterJ2000)
MoonPhase(DaysAfterJ2000)
MoonPhaseSearch(Days afterJ2000,index)
MoonPhaseDate(DaysafterJ2000,Index)
rMoon
MoonSize(Days after J2000, observers Latitude, observers longitude)
MoonPerigee(days after j2000, index)

The examples are real life examples where I have wanted to do something and have created a new sheet to do it.

Planet Visibility

Here is an example (included in the spreadsheet) of creating a graph in Excel to show the rise and set of all the planets along with sunrise, twilight and sunset. If you know how to use excel you can quickly create this sort of graph with AstroFunctions

Visualise Planet visibility

Here is a further example (included in spreadsheet) that shows the altitude of all the planets, Sun and Moon for a 24hr period, it is very easy to see what is up when and if it is rising or setting. If you have a basic understanding of excel and a basic understanding of Astronomy you can create this sort of graph in a matter of minutes, you do not need to understand the Maths involved!

This graph only needs a few functions, here is how I did it.
1) Calculate the number of days after Jan1st 2000 using 'days2000'
2) Calculate the RA and Dec of the object in question using 'planet'
3) convert position to altitude using 'altaz'
4) get Excel to do this for every hour of the day
5) get Excel to graph the results

A 3rd example (included in spreadsheet) using Excel's graphs to plot the planet and Moon positions in a map like presentation.

1) Inset top left- a pictorial representation of the Sun Earth and Moon
2) Inset top right - a pictorial representation of the inner solar system
3) Main Graph - a pictorial plan view of the solar system to scale
4) Inset bottom - a edge on view of the solar stystem to scale.

These Graphs needed the following functions...
1) calculate the number of days after Jan1st 2000 using 'days2000'
2) Calculate the rectangular coordinates for a given date and time for each planet using 'rPlanet'
3) calculate the rectangular coordinates to a given date and time the position of the Moon using 'rMoon'
4) create scatter charts of the data and format the charts to 'look nice'

OK so you can go on forever thinking of ways to represent the planets, here is one I like that shows the relative apparent sizes of the planets and their relative distances to Earth.

1) use 'Planet' to calculate the Earth Planet Distances
2) use Planet Size to measure the Planet's angular size
3) use a bubble graph to show them on the screen pictorially and to scale

Telescope and CCD Details

Here I have calculated (example included spreadsheet) some simple details such as limiting visual magnitude, resolution and CCD field of view, again the functions take away the hard work you just simply create the spreadsheet you need using built in astronomy functions, for instance if you want to know the limiting visual magnitude of your next 400mm telescope acording to the standard model you simply type '=MagLimit(400,"Standard")'
It's that simple! Spreadsheet example updated to include exit pupil and power for a number of my eyepieces

Work out if that new CCD and Telescope are a good match

Exo- Planets & Variable Stars

The thing with any periodic variable star, is the periods don't always coinside with when the star is above the horizon and when it's dark at a convienent time to observe! Rather than check each eclipse/transit/event manually and convert the list of julian dates a couple of functions in AstroXLS and you will have a list of times the transit occurs when you are able to observe, just leaving weather! This really does take the tedium out of planning.

Planning for HD209458 took 2 functions, two filters and about 5 minutes, the trick is to move in methodical steps.
1) convert the julian date predictions into dates and times using 'JDaytoDATE'
2) calculate the altitude for the object at the date and time using 'AltAz'
3) Filter results that are too low in altitude
4) Filter objects too late or early for you.

One additional calculation, 'AtmosExtinction', provides you with a guide to how you should expect you atmospheric extinction to affect your results.

Save time planning Astronomical Observations

Conjunctions of the Planets

An example in the spreadsheet is predicting planetary conjunctions for the year, the functions provided would allow you to predict these with with a high level of accuarcy but here I have simply plotted the right assention position, the planets all follow (more or less) the same line in the sky called the ecliptic so if we plot the RA of each planet for the year where the lines cross or come very close it is very likely there will be a loose conjunction and I have circled these. This whole exercise took about 15 minutes, the trick for success is as always taking small simple steps to your goal and spreadsheets are great for this.

Solar Eclipses

An example in the spreadsheet shows how to simulate a solar eclipse, again this is simple when broken down into a number of steps.
1) Calculate the days after Jan 1st 2000 using 'Days2000' all functions use this date format.
2) Calculate the Suns position using 'Sun'
3) calculate the Moons position using 'tMoon' (this gives the topo centric position of the Moon
4) Calculate the Sun and Moon size using 'SunSize' and 'MoonSize'
5) for fun calculate the angular seperation using 'angularseperation'
6) Chart using a bubble chart
7) add a couple of spin objects to easily change the minutes of the day to dynamically change your chart in real time.

Jupiters Satellites

Here is an example, included in the spreadsheet, for plotting the positions of Jupiters four main moons, here is how I built it.

1) convert the date to J2000 using 'days2000'
2) caculate the position of each moon using 'JupSat'
3) plot results in a bubble graph
4) repeat but this time add the optional parameter 'planview' to the jupsat function arguments.

Year Planner

This formats a year planner for any year and then adds the following astronomical phenomenon automatically

Lunar Phases

Lunar Apogee and Perigee

Elongation of Venus

Solstices and Equinoxes

Oppositions and conjunctions for Mars, Jupiter, Saturn and Uranus

mid month Sunrise and Sunset.

It requires the freeware font moonphase (details in the help)

The planner self formats using conditional formatting I have set it up to highlight Thursdays.

There is a column where you can enter your own data without fear of breaking the example.

NOTE The functions are written VBE (embedded Visual Basic) this requires that you turn on macro's when loading the file, generally Excell will warn you when a file includes a macro so you can decide if you want it to run or not. If you don't turn macros on the spreadsheet will still load but when you try to change something the cells will recalculate and you will be left with loads of #VALUE errors!

Source code is not protected so feel free to view, any protected cells are not password protected so you can fiddle if you want.
Please read the help files and study the examples.

## Table of Contents

## AstroFunctions Excel Spreadsheet for Astronomical functions.

99 Astronomical Functions/constants in Excel!I often find myself needing to calculate a Julian date, or find out local conditions for observing such as moon phase, altitude etc. so I have created a spreadsheet that has all the functions already included, including many examples of their use.

I can't take all the credit for this as some functions have been found from the internet, others have come from books such as ‘Astronomical Algorithms’ by Jean Meeus and another personal favourite ‘Practical Astronomy With Your Calculator’ by Peter Duffett–Smith. However I have tried to standardise the inputs especially around dates so each function uses the same formats.

This is an ongoing project of mine so will never be fully complete so check back here for updates! If you have something you want to add please contact me and I'll endevour to add it!

Also note the spreadsheet has a lot of examples but these are just worksheets I have created for my own purposes, you can delete all but the

help worksheet, all the functions will be retained, or if you prefer, update and modify these examples.Source code is, so if you want to tweek a function, check how it is calculated or re-use in a different language/application feel free to do so.notprotected## Usage

Just like normal functions in Excel, these Astronomy functions don't require any knowledge of the maths involved, you simply enter the function in a cell include the parameters for the function (see the help tab) and the result appears, for example what to know the field of view of your CCD? just type'

=CCD_FoV(sensor_mm,FocalLength_mm)'where sensor_mm is the size of your CCD in mm and FocalLength_mm is the focal length of your 'scope in mm.

In general I have standardised parameters, for instance all dates and times are in days and fractions of a day from 1st Jan 2000 (and there are functions to convert to civil dates and times or Julian dates etc.

## Functions include

## Constants

total of 20 constants with their dimentions and links to their source, all constants start with an underscore, so need to use the speed of light in a calculation just type '_C' in your formua for the speed of light in metres per second## Trignometry

DegSIN(degrees)

DegCOS(degrees)

DegTAN(degrees)

DegARCSIN(degrees)

DegARCCOS(degrees)

DegARCTAN(degrees)

## Misc.

RANGE360(degrees)

DegDecimal(degrees,minutes,seconds)

HrMinSec_to_Decimal(Hours, Minutes, Seconds)

## Calandar

jDay(Year,Month,day,Hour,Minute,seconds,Gregorian Flag)

JdaytoDate(Julian Date)

days2000(Year,Month,day,Hour,Minute,seconds,Gregorian Flag)

J2000toDate(days afterJ2000)

JDE_to_J2000(JulianDate)

J2000_to_JDE(J2000)

## Conversion, co-ordinate, and measurement

Rectangular(r,theta,phi,index)

rLength(x,y,z)

Spherical(x,y,z,index)

AngularSeperation(Object1_RA,Object1_DEC,Object2_RA,Object2_DEC)

EarthRadii_to_km(EarthRadii)

WavelengthToFrequency(Wavelength_in_Metres)

FrequencyToWavelength(Frequency_in_Hertz)

## Equipment

MagLimit(diameter in mm, "Author")

CCD_FoV(CCD dimension in mm, telescope focal length in mm)

ExposureIncrease(Mag increase)

ExitPupil('Scope Aperture, Scope Focal Length , Eyepiece Focal Length)

Coma_Eyepiece(F_Ratio, Off_axis_Angle)

Coma_CCD(F_Ratio, Focal Length in mm, CCD Dimention in mm)

FoV_by_Drift(declination of object in degrees decimal, time to cross field in seconds

LightThroughput(Aperture, obstruction, No_of_SurfaceA, SurfaceAEfficiency, No_of_SurfaceB, SurfaceBEfficiency, No_of_SurfaceC, SurfaceCEfficiency, aperturecomparison)

EquivalentContrastAperture(CentralObstruction, Aperture)

NewtSecondarySize(Focal_Ratio, Secondary_to_FocalPlane, Optional Illuminated_Diameter)

NewtSecondaryOffset(Primary_Diameter, Fully_Illuminated_Field_Diameter, Secondary_Diameter, Focal_Length)

AiryDiskmm(F_Ratio, Optional wavelength_nm)

AiryDiskArcSec(F_Ratio, F_Length, Optional wavelength_nm)

CCDMinFocalLength(Aperature_mm, Pixel_Size_Microns, Optional Wavelength_mm)

CCDMinFocalRatio(Pixel_Size_Microns, Optional Wavelength_mm)

FullIlluminationDueToFocuser(Aperture_mm, F_ratio, Focal_Point_to_base_mm, Focuser _neck_width_mm)

RealFoVfromEyepieceFoV(Focal_Length_of_Scope, Focal_Length_of_eyepiece, eyepiece_Field_of_view)

RealFoVfromEyepieceFieldStop(Focal_Length_of_Scope, Eyepiece_Field_Stop)

FocalLengthfromCCDImage(Object_Size_pixels, Pixel_size_microns, Object_Size_ArcSec, optional binning)

CriticalFocusZone(F_Ratio, Optional Wavelength_nm)

DustDonutDistance(PixelSize_microns, F_Ratio, donut_Size_pixels, Optional binning)

StarTrail(Focal_Length_mm, Exposure_Length_s , Optional Dec_of_Object_degrees)

StarTrailPixel(Focal_Length_mm, Exposure_Length_s , Pixel_Size_microns, Optional Dec_of_Object_degrees)

## Astronomical

Obliquity(days after j2000, index)

## Sun

SunRise(days after j2000, Latitude, longitude, index,altitude)

CarringtonRotation(DaysafterJ2000.0)

Sun(days after J2000, index)

SunSize(Days after J2000)

Sun_RelativeSunspotNo(No of sunspot groups, no of total spots)

SunLongitude(DaysafterJ2000, index)

## Moon

Moon(days after j2000, index)

tMoon(days after j2000.0, Latitude, Longitude, Index)

MoonAge(DaysAfterJ2000)

MoonPhase(DaysAfterJ2000)

MoonPhaseSearch(Days afterJ2000,index)

MoonPhaseDate(DaysafterJ2000,Index)

rMoon

MoonSize(Days after J2000, observers Latitude, observers longitude)

MoonPerigee(days after j2000, index)

## Time

gst

## Planet

prise(days afterj2000, planet number,latitude,longitude,index)

Planet("Days afterJ2000",Planet Number,Index)

rPlanet(J2000,planetnumber,index)

PlanetMag(J2000,PlanetNumber)

PlanetPhase(J2000,PlanetNumber)

PlanetSize(J2000,Planet Number)

JupSat(J2000,SatelliteNumber,Index,[planview])

JupiterCMlow(System1_2_3,DaysAfterJ2000)

## Radio

DipoleLength_m(Freq_MHz)YAGIelements(Frequency_MHz,No_Elements,Element)

## Meteor

OHR(ZHR,Elevation)ZHR(MeteorsObserved, DurationObserved_Hrs, RadientAltitude_Deg, PopulationIndex, LimitingMag, FRaction ofSkyObsecured)

## General

riseset

altaz

AtmosExtinction(altitude)

Refraction(Temperature in C, Atmospheric pressure in mBar, Altitude in degrees)

## Other

AsteroidSize(ApparentVisualMagnitude, SolarDIst, EarthDIst, EarthSunDist, AsteroidType, albedo, photometricslope)

## Examples

The examples are real life examples where I have wanted to do something and have created a new sheet to do it.

Planet VisibilityHere is an example (included in the spreadsheet) of creating a graph in Excel to show the rise and set of all the planets along with sunrise, twilight and sunset. If you know how to use excel you can quickly create this sort of graph with AstroFunctions

This graph only needs a few functions, here is how I did it.

1) Calculate the number of days after Jan1st 2000 using 'days2000'

2) Calculate the RA and Dec of the object in question using 'planet'

3) convert position to altitude using 'altaz'

4) get Excel to do this for every hour of the day

5) get Excel to graph the results

1) Inset top left- a pictorial representation of the Sun Earth and Moon

2) Inset top right - a pictorial representation of the inner solar system

3) Main Graph - a pictorial plan view of the solar system to scale

4) Inset bottom - a edge on view of the solar stystem to scale.

These Graphs needed the following functions...

1) calculate the number of days after Jan1st 2000 using 'days2000'

2) Calculate the rectangular coordinates for a given date and time for each planet using 'rPlanet'

3) calculate the rectangular coordinates to a given date and time the position of the Moon using 'rMoon'

4) create scatter charts of the data and format the charts to 'look nice'

1) use 'Planet' to calculate the Earth Planet Distances

2) use Planet Size to measure the Planet's angular size

3) use a bubble graph to show them on the screen pictorially and to scale

Telescope and CCD DetailsHere I have calculated (example included spreadsheet) some simple details such as limiting visual magnitude, resolution and CCD field of view, again the functions take away the hard work you just simply create the spreadsheet you need using built in astronomy functions, for instance if you want to know the limiting visual magnitude of your next 400mm telescope acording to the standard model you simply type

'=MagLimit(400,"Standard")'It's that simple!

Spreadsheet example updated to include exit pupil and power for a number of my eyepiecesExo- Planets & Variable StarsThe thing with any periodic variable star, is the periods don't always coinside with when the star is above the horizon and when it's dark at a convienent time to observe! Rather than check each eclipse/transit/event manually and convert the list of julian dates a couple of functions in AstroXLS and you will have a list of times the transit occurs

when, just leaving weather! This really does take the tedium out of planning.youare able to observePlanning for HD209458 took 2 functions, two filters and about 5 minutes, the trick is to move in methodical steps.

1) convert the julian date predictions into dates and times using 'JDaytoDATE'

2) calculate the altitude for the object at the date and time using 'AltAz'

3) Filter results that are too low in altitude

4) Filter objects too late or early for you.

One additional calculation, 'AtmosExtinction', provides you with a guide to how you should expect you atmospheric extinction to affect your results.

Conjunctions of the PlanetsAn example in the spreadsheet is predicting planetary conjunctions for the year, the functions provided would allow you to predict these with with a high level of accuarcy but here I have simply plotted the right assention position, the planets all follow (more or less) the same line in the sky called the ecliptic so if we plot the RA of each planet for the year where the lines cross or come very close it is very likely there will be a loose conjunction and I have circled these. This whole exercise took about 15 minutes, the trick for success is as always taking small simple steps to your goal and spreadsheets are great for this.

Solar EclipsesAn example in the spreadsheet shows how to simulate a solar eclipse, again this is simple when broken down into a number of steps.

1) Calculate the days after Jan 1st 2000 using 'Days2000' all functions use this date format.

2) Calculate the Suns position using 'Sun'

3) calculate the Moons position using 'tMoon' (this gives the topo centric position of the Moon

4) Calculate the Sun and Moon size using 'SunSize' and 'MoonSize'

5) for fun calculate the angular seperation using 'angularseperation'

6) Chart using a bubble chart

7) add a couple of spin objects to easily change the minutes of the day to dynamically change your chart in real time.

Jupiters SatellitesHere is an example, included in the spreadsheet, for plotting the positions of Jupiters four main moons, here is how I built it.

1) convert the date to J2000 using 'days2000'

2) caculate the position of each moon using 'JupSat'

3) plot results in a bubble graph

4) repeat but this time add the optional parameter 'planview' to the jupsat function arguments.

Year PlannerThis formats a year planner for any year and then adds the following astronomical phenomenon automatically

It requires the freeware font moonphase (details in the help)

The planner self formats using conditional formatting I have set it up to highlight Thursdays.

There is a column where you can enter your own data without fear of breaking the example.

Download at the astroexcel.wordpress.com site

NOTEThe functions are written VBE (embedded Visual Basic) this requires that you turn on macro's when loading the file, generally Excell will warn you when a file includes a macro so you can decide if you want it to run or not. If you don't turn macros on the spreadsheet will still load but when you try to change something the cells will recalculate and you will be left with loads of #VALUE errors!Source code is not protected so feel free to view, any protected cells are not password protected so you can fiddle if you want.

Please read the help files and study the examples.

Copyright © 2005-2017 Crayford Manor House Astronomical Society Dartford, Kent.Registered Charity Number 1156678. All rights reserved. No part of this website may be reproduced.

Page edited 12 times. Last edit by - SimonTelescopium on Jul 4, 2015 8:51 am. This site contains 560 pages