Chart Tutorial

Here is a brief tutorial explaining how I made the chart in my last post.

First, open CelNav.xlsm and click the “Data Series” tab to get to the Data Series calculator. Then, select the date and time you would like to start the calculation, the body, the number of iterations and the interval. Press the “Calculate” button.

Above, I have calculated the data for the Sun, starting from May 12th, 1993 at 0900 UT and for each hour thereafter for 24 hours. We are going to need two columns of this data for our chart: the Julian date and the calculated altitude. I have labeled these columns “1.” and “2.” For now, we only need the JD in column “1.”

Select all of the cells in column “1.” and press [Ctrl] + [C] to copy them. Open a new Excel workbook, select the cell where you would like to paste the data and press [Ctrl] + [Shift] + [V] to open the “Paste Special” dialogue.

Press [V] or click the radio button to select “Values” and press “OK”. (This will prevent Excel from pasting any formulas, formatting or anything else we don’t need.) The JD values will be pasted into the new sheet.

Next, select all of the cells immediately to the right of the data you just pasted.

Type the following formula:

=A1:A25-2415018.5

… and press [Ctrl] + [Shift] + [Enter]. This will fill all of the selected cells with the formula automatically. (If necessary, change the cell references [“A1:A25”] to match the range your data is actually in.) The purpose of this formula is to convert the JD into the date format that Excel uses. This is similar to the JD, but the starting date is January 1st, 1900. Excel cannot make sense of dates before then, so you will need to use a workaround if your data is from before that date. Take note of the first and last numbers (34101.38 & 34102.38). These will be important later.

With the new column still selected, right-click anywhere in the selection and click “Format Cells”.

On the “Number” tab, select the date and/or time format you would like. I have chosen to use a custom format which shows the two digit day, a dash and the hours and minutes. You can create whatever format you like by typing the format code into the “Type:” field. Typing “ddd” will give you the day of the week, “yy” or “yyyy” will give you the 2 or 4 digit year, respectively, and so on. Experiment and see which format you prefer. The options are practically limitless. A real-time preview of the format you are creating will be shown in the “Sample” field.

Once you have the date/time formatted, go back to the Data Series calculator and select and copy all of the cells in the Hc column (labeled “2.” in the first image). Paste them as before, using the “Paste Special” option. Repeat the calculation for each body you would like to include in the chart. Paste the calculated altitudes in the columns next to the data you already pasted in the new sheet. (For simplicity, I recommend calculating all data for the same period and interval.) For this tutorial, I will only be using the Sun and Jupiter.

Now we can start making the chart!

Select all of the cells you would like to include in the chart. In this case, we want the date and time, but not the JD - so we leave the first column out. We also make sure to select all of the calculated altitude columns. If your data is in non-adjacent columns, don’t worry. You can add or remove data from the chart at any time.

Click the “Insert” tab and select a “scatter” plot. I like the smoothed lines with no markers on the upper right, but you can choose whatever kind of chart you like. You should get something like this:

In the next screenshot, I have already resized the chart so that it is easier to see.

Right-click the x-axis and set the minimum and maximum to the desired values. Remember those two numbers that I said would be important? Those are the numbers you need to make the chart start and end exactly with the data. Here you can also set the units to something that makes sense to humans. I chose 1/24 of a day … or 1 hour.

Click the y-axis and configure it, as well. Since this is going to be a visibility chart, I chose a min of 0 and a max of 90.

In order to approximate the visibility of Jupiter, we will delete all of the altitudes which correspond to a time when the Sun is above the horizon. To do this, we simply select the cells containing the data and press [Delete].

Now we will very roughly approximate the brightness of the sky. Click the “Insert” tab, select “Shapes” and click the rectangle. Draw the shape anywhere on the chart by clicking and draging. You can resize it at any time.

Right-click the shape and select “Format Shape”. Select “Gradient” for the fill (1., below) and 0° for the angle (2.). You can add or remove steps as desired (3.) and set their transparency (4.) In this chart, I have set all of the steps to black. The outer two have a transparency of 90%, and the inner two are set to 50%. I have also selected no border (5.)

Align the edges of the shape with the top and bottom of the chart, and with the times that the Sun sets and rises. Adjust the positions of the steps so they make sense. In the final chart, the Moon rose about halfway through the night, so I chose to brighten the sky at that time. Again, this is just a very rough approximation.

All that’s left to do now is some minor formatting. Right-click each chart element you want to format and change it to your liking. Below, I have simply filled the plot area with a light blue and the chart area with a light grey.

Once you have finished, you can select the chart and copy it to the clipboard using [Ctrl] + [C]. From there, you can paste it as an image wherever you like.

I hope this tutorial will help you make really neat and interesting charts of your own. There’s almost no limit to the types of charts you can make. If you have any questions or if there is some additional feature you would like to see added, please let me know in the comments. Thank you!

-Shawn

Custom Charts / Graphs

You can make custom charts like this with the data series calculator. (Click image for larger version.)

The chart above shows an approximation of which major bodies were visible and at what time for a given location over a 24 hour period starting at 0900 UT on May 12th-13th, 1993. On the left, we can see that the Moon set just before local noon on the 12th. Sunset (the very beginning of the shaded region) was at about 2220 UT and astronomical twilight ended around 0030 UT on the 13th. The Moon rose at about 0350 UT , brightening the sky a little with its half-illuminated disc. Sunrise was at about 0810 UT.

All four of the navigational planets were visible at some time during the night. Mars and Jupiter were at moderately high altitudes after astronomical twilight and before the Moon rose. That should have made for some decent viewing through quality binoculars.

A chart like this can easily be made for any location on earth and for any time period. It only took a few minutes to make using the data series calculator. If you would like to see a detailed tutorial on how I made it, let me know in the comments. There are also many more types of charts you can make!

- Shawn

Welcome

Welcome to my new website. I will be posting new versions of my software here as I add new features and fix bugs. I also hope to be adding more software and other resources in the future. Please see the contact page if you would like to request a feature, report a bug or if you have any questions.

I would like to thank Jean Meeus, Daniel R. Strebe and all of the folks at Pareto Software, NASA and the IERS for making their excellent work available.

- Shawn