Deriving geostationary orbit position from 2 line elements using spreadsheet

This page explains how I calculate the orbit positions of geostationary satellites using 2 line elements as the input.

Get the source data from www.celestrak.com/NORAD/elements/ and save the page as text file. Add a date into the file name for your convenience. "Save As" using a file name like geo-12-dec-2019.txt

Note that this process below only applies to geostationary satellites.

Two line elements file should look like this, as viewed in Notepad:

2-line elements

The first processing task is to convert to 1 line text elements, looking like this:

1-line elements

Sorry I have had to squeeze this a bit sideways to make it fit on the page but it should give you the idea. The important thing is that each satellite has its data on one line and that the columns all line up perfectly. This make is easy to read the data into a spread sheet (using "Text Import") and separate the figures into columns, using fixed vertical lines, thus the need for exact alignment.

Doing the above involves removing the "new line" or paragraph marks at the end of each line, but not on the end of each third line. This is not easy.

You can do it using Microsoft Word, which I don't have, or LibreOffice Writer, which I do have. Other word processors may do the trick. Notepad++ has been suggested but I have not tried it. The problem is doing search and replace on non-printing characters like "new line", "carriage return" and possibly other embedded non-visible characters.

Using LibreOffice Writer I do this:

Open file, e.g. geo-11-nov-2014.txt

Format Page, set to landscape and 39cm wide. Do Edit, Select All then set font size=10, so you can see each long line on one line.

Do find and replace. Select Other Options and set Regular expressions ticked.

Do find and replace four times, as follows, selecting Replace All.

Find $
Replace with x<space>

Find x<space>1 Where is says <space> just input one click on the space bar.
Replace with 1 Select "Match case"

Find x<space>2
Replace with <space>2<space> Select "Match case"

Find x<space>
Replace with \n Select "Match case"

Using Notepad++ I have successfully tried the following:

Open file, e.g. geo-4-jan-2020.txt

At the end of each line you now see CRLF with black background.

Do Search and Replace 4 times. Select "Extended mode" and "Match Case".

Find \r\n
Replace with x<space>

Find x<space>1 Where is says <space> just input one click on the space bar.
Replace with 1

Find x<space>2
Replace with <space>2<space>

Find x<space>
Replace with \n

You should now have 1 line elements, with each line ending LF . That is good.

You may now need to do some manual editing to remove any errors in the original data file. Look all the way down and tidy it up. The following errors, or similar may be seen:

Check very carefully that there are gaps all the way down, except between the satellite name and the 1.

There may be a few spurious extra spaces or new line characters. A new anomaly (late 2019) is missing pairs of spaces in some of the China satellite name fields. Sometimes extra digits creep put in. Replace bad digits with spaces where possible, e.g. in last column for LUCH 5CV reads 520037. It should be <space><space> 2003 or maybe 5200. No idea where the extra 2 digits came from! In OPTUS 10 there seems to be one or more corrupt characters, following the name. If the common text name of a satellite is missing and has been substituted by, for example, by "2020-053B", then there may be spurious long lines. Manually insert a line feed as necessary.

Edit the file to make all the columns line up perfectly.

If in danger of corrupting the data, delete the relevant satellite.

Save the file as plain text, e.g. geo-11-nov-2014-one-line.txt

Now comes the exciting bit, putting the data into a spreadsheet.

Open LibreOffice Calc and import (File Open) the text file e.g. geo-11-nov-2014-one-line.txt

A pop up window will appear allowing you to parse the data into columns. Select the fixed width option. As necessary reposition the red dots and the vertical separator lines so that the separator lines fall exactly either side of the first 1 and to the immediate right of all the other columns, like so:

Parsing 2-line elements

The result will be that the figures all get put nicely into columns on the spreadsheet.

Working on the spreadsheet, insert 4 blank rows across the top.

You then need to fill these four blank rows with various informative texts and formulas, in the range A1 to AK4
Do this by opening this file, orbit-research/top-four-lines.ods.

Highlight the four rows and do Edit Copy. Edit Paste into your own spreadsheet, filling the top four empty rows/

Note there is one sample satellite in row 4. Look at the formulas in boxes T4 to AK4.

You then need to fill your working columns with formulas from the range T4 to AK4.

Highlight the area T4 to AK4 and copy the formulas all the way down to the end of your list (e.g. to about line number 517)

Highlight row 4 and delete content of the cells (Edit Delete cells) in row 4. This row will now disappear.

The calculations "may" now be near complete.

Familiarise yourself with the row number at the bottom, as this makes it easier to highlight the entire work area, e.g. A4:AK415 You can then type these letters/figures into the select box at top left of the Libre Office calc spreadsheet.

Maybe Data Sort the entire work area into alphabetical order for common satellite name. (optional)

Now have a look at the orbit positions and see where some known satellites (e.g.Eutelsat 10E) are located. Are all the positions correct?

Now refine the positions. Look in the Epoch column, E and pick a similar number to the numbers shown. Enter this in box T1. The satellites will now move around drastically. Find out the orbit position error, using a known satellite position (e.g. look at all EUTELSAT satellites with known nominal orbit positions) , and adjust box X1 value to fix the problem. I added -110.405 today. They will all move round together to correct place. Expect satellites to be +/- 0.2 deg either side of their nominal positions.

Now Data Sort the entire work area into orbit position order using column X.

Output geo orbit positions from 2 line elements

Having got a result highlight some boxes. e.g. Y3:AB422 and copy and paste into some printing process. I tend to copy into new spreadsheet using "Paste Special, Text, Numbers (or Values)" and save as text file with "Filter" set to fixed column width so it looks nice on a web page, using fixed width characters like courier. You may alternatively use html code to make a table, such as <option value>, </option> etc. You choose...

Good luck!

If you want to list the satellites in launch date order do a sort on column W or Y.

Since late 2019 some satellites in the GEO list have been shown with high inclinations, up to 60 deg. You may wish to delete these satellites from your listing.

Page started Nov 2014. Last amended 4 Jan 2021, 14 Dec 2023.