An Excel Report Form and Macro for Lunar Occultations

1. An Excel Report Form and Macro forLunar Occultations Brad Timerson - USA John Talbot - NZ

2. Some History In October 2006, Brad Timerson developed an Excel-based MP Report Form and floated the idea with a number of IOTA members and leaders who liked the concept. Dave Herald made the comment “And presumably being in Excel you could automate data extraction”. In late October, Brad sent a copy to Graham Blow and John Talbot. John, who had written a number of Excel macros before, could not refuse the challenge. John created the macro and presented a paper entitled “A Semi-Automated Loading of Occultation Results Using Excel Macros” at the first Trans-Tasman Occultation Symposium in July, 2007. Soon after, observers in Australia and New Zealand joined their North American cohorts in using the new Report Form and macro.

3. More History Shortly after the acceptance of the Excel-based MP Report Form, Brad Timerson undertook the task of creating an Excel-based Form for the reporting of Total and Grazing Lunar occultations. The Forms (2, one for each type of event) made extensive use of drop-down lists so that users didn’t need to look up the various codes used on the ILOC forms. The new Graze Form was “beta-tested” during the Regulus graze on November 3, 2007 by several observers. Changes in the Reporting and Archiving of observations caused by the demise of ILOC and the release of Occult4 by Dave Herald prompted further changes to the new Report Form. John Talbot was kept busy as he optimized the new Form and created the macro used to “read” the data into the new Archive format and Occult4.

4. The Rest of the Story… ILOC announced it would no longer process and reduce lunar occultations starting in March, 2009. IOTA announced it would do this job. Dave Herald and others undertook the job of revising the archive format to better reflect the way lunar occultations are observed and recorded at the present time. The format had to be backward and forward compatible. Eric Limburg is working to revise LOW (Lunar Occultation Workbench) to use and produce this new format. Dave Herald revised Occult4 to use and produce the new format. Dave Herald wrote a stand-alone version of the Occult4 module on reporting lunar occultations called LunarReport. The new Excel-based Report Form is now 1 of 4 ways that observers can use to report their lunar occultations. (Occult4, LunarReport, LOW, Excel Form) And so….. Let’s see what the Excel Form will do.

5. Observation page from the Wiggins’ Regulus Graze Report(“beta-test” using the draft form)

6. Wiggins’ telescope data page(“beta-test using draft form)

7. Revisions/additions by John Talbot John Talbot created the macro used to “read” the Excel-based MP Report Form and some Text-based Forms. This macro creates the OBS file used in Occult4 to analyze the results of asteroid occultation observations. He became involved in taking the Timerson Lunar Forms and: 1) Reducing the 2 Forms into one, all-inclusive Form 2) Working with Dave Herald to create terminology that is consistent with use in the Report Form, the new Archive format, and Occult4. 3) Creating a macro to “read” the new Lunar Occultation Report Form and create an Report file that is compatible with Occult4.

8. Basic Layout of the New Form Opens with 4 tabbed worksheets 1 – Directions: basic use of the Form 2 - Observer Info: information about observers, recorders, contact person 3 – Telescope Info: detailed telescope and observing site information 4 – Observations: detailed observation data --------------------------------------------------------------- 5 – Menu Tables: a worksheet, normally hidden, containing the menu items for the drop-down lists

9. New Form – Directions Top

10. New Form – Directions Bottom

11. New Form – Observers(Basic Form has room for 10 observers – more can be added.)

12. New Form – Telescopes(Basic Form has room for 10 telescopes/locations – more can be added.)

13. New Form – Observations Left(Basic Form has room for 20 observations – more can be added.)

14. New Form – Observations Middle

15. New Form – Observations Right

16. New Form – Menu Items(only shows top portion of this sheet which normally would be hidden)

17. Occult4/LunarReport – Header PageNote similarities to the Observers Info Page of the new Form.

18. Occult4/LunarReport – Sites PageNote similarities to the Telescope Info page of the new Form.

19. Occult4/LunarReport – Events PageNote similarities to the Observations page of the new Form.

20. Benefits for new Coordinator(s) • Consistent Form used by most observers (using Excel or OpenOffice) • Encourages all observers to provide complete information about an observation • No ambiguity in terminology or need to look-up codes when reporting observations • Macro “reads” Form and creates Occult compatible ASCII file • Macro has some ability to “error-trap” • Form can automatically be sent to a Local Coordinator

21. Additional Notes A Word document and matching Text file containing additional information on using the new Report Form is available. Many columns on the Form have built-in Help messages when the cursor hovers over them. Dave Herald has created a stand-alone application based on Occult called LunarReport that users familiar with that program may want to use in place of the Excel Form. Eric Limburg’s LOW (Lunar Occultation Workbench) can also be used to create lunar occultation reports in the new archive format.

22. New Excel-based Lunar Occultation Report Form is now, or will soon be, available: on the Main IOTA webpage: http://www.lunar-occultations.com/iota/iotandx.htm on Timerson’s IOTA Graze page: http://www.timerson.net/IOTA and on the RASNZ Occultation site: http://occsec.wellington.net.nz/report.htm Links for Occult, LunarReport, and LOW are also on these pages.

23. Questions/Comments Brad Timerson gratefully acknowledges the help of John Talbot, Tony George, Dave Herald, Paul Maley, David Dunham, and Patrick Wiggins.