Teesmouth Bird Club - Sightings Recordings Spreadsheet - Documentation

Purpose

Records of sightings of wild birds in Cleveland are collated by members of the Teesmouth Bird Club to produce the annual "Cleveland Bird Report" and are also used as a major reference for club reports commissioned to describe the populations of species at notable locations and how those populations have changed over a number of years.

Currently the sightings records are held and managed by a number of members using different methods and recording systems. Teesmouth Bird Club wishes to significantly improve the accuracy and completeness of their reports by:

·         Increasing the number of people submitting sightings records;

·         Issue a single recognised list of species names to be used (referenced to BOURC);

·         Issue a single recognised list of sighting location names (and grid references) to be used;

The spreadsheet has been designed to allow and encourage new and archived sightings records to be easily entered whilst ensuring consistent species and location references.

Where do you get a copy and where do you send your records?

The spreadsheet (current version: TBC_template_1v0.xls) is available for download from the Teesmouth Bird Club website (www.teesmouthbc.com).

The spreadsheets should be completed and submitted monthly to the County Recorder: ("Tom Francis" mot.francis@ntlworld.com). 

There is no prescribed file naming convention, but ideally the spreadsheet should be saved with a filename that identifies the period covered and the name of the recorder, e.g. 201109_Fred_Bloggs.xls.

How do I use the Spreadsheet?

When you open the spreadsheet you will be presented with 6 (six) worksheet tabs:

·         “MyRecords”. This is where all of your sightings will be entered. See below.

·         “TBC Site List”. The recognised list of sighting location names. Try to use a name from this list and only add a new location if you are certain that your location is not included as a different name.

·         “TBC Species List”. The recognised list of species names. This is actually the complete BOURC species list, but the worksheet also indicates whether the species has been previously officially recorded in the county. You should not (need to) modify this worksheet.

·         “BOURC List”. Included for reference only.

·         “Revision List”. Included for reference only.

·         “My Recorders”. This is where new recorder names will be automatically added. If you wish to ensure your recorder names remain consistent across different spreadsheets than you can “copy-and-paste” the contents of this worksheet before you begin entering sightings records.

“MyRecords” Worksheet

This is the main worksheet and, for the majority of recorders, it will be the only one which they will enter data into or even look at.

“MyRecords” Personal Information

Please enter your name and contact details so that the County Recorder can recognise who has sent in the records and how to contact you if there are any questions raised by your sightings.

“MyRecords” Data Field Names

Row 8 of the “MyRecords” worksheet gives the data field names:

·         Date, Recorder, Site and Species. I hope that these are self-explanatory. If not they will become more obvious as you use the spreadsheet.

·         Count. Also obvious except that this must be an exact number. If the number is approximate then please enter an explanation in the Comments/Notes field.

·         Comments/Notes. Used to contain further explanation if available (e.g. age, sex, special conditions, approximate count, direction if flying etc.)

·         BOURC and YYYYMM are fields which are used for data sorting and are automatically completed by the Data Entry Form (See below).

How do I enter “MyRecords” Data?

The answer is either:

·         Manually/directly into the worksheet cells (not advised unless Macros are disabled on your computer) or,

·         Using the Data Entry Form which is strongly preferred as the form ensures correct identification and spelling of sites and species by automatically linking to and taking data from the other worksheets

What is the Data Entry Form?

From the “MyRecords” worksheet press the “Data Entry and Sorting” red button and the form should appear. You will see a database or record card view that displays one row of the spreadsheet containing the data fields described above.

Data Security and the use of Macros

Depending upon the security settings on your computer you may have been presented with a warning or error message when you opened the spreadsheet or when you pressed the “Data Entry and Sorting” button advising that the spreadsheet contains Macros and asking if you should enable them. The Data Entry Form runs as a Macro and must be enabled before it can be used.

How do I use the Data Entry Form?

As mentioned above the form view and allows you to enter or change one record (one row of the spreadsheet) at a time.

Selecting a record

You can select any record between row 9 and up to the first blank row (which is where a new record will be added).

You can pre-select the row to view by selecting it on the “MyRecords” worksheet before pressing the “Data Entry and Sorting” red button. This is very handy for editing a record.

You will see the worksheet row number at the top left of the form and you can move between records by pressing the “Next” or “Prev”(ious) buttons. You can also move directly to any row by entering the row number directly.

Finally, and most usefully for entering new data, press the “New” button and you will be taken to the first blank row ready to enter your data.

Entering Record Data

Date

Enter the date of the sighting. The field will accept many data formats and will reject most gibberish.

No. Days

Generally you will use the default of 1. But if you are entering data after the event and you know a bird (or the same number of birds) to have been present for a number of days then this is a quick way to enter this information. Please note that if you enter a value other than 1 then the form will actually enter a number of records, one for each day starting from the value within the “Date” field.

Name of Recorder

Usually this will be your name, but you can enter the names of other recorders if you are entering their data.

All new names entered in this field will be added to the “My Recorders” worksheet.

This field is a list box and pressing the down arrow on the right will display the list of Recorder names already held in the “My Recorders” worksheet. You can now select the Recorder name directly if it exists. If you enter a new name you will be prompted to confirm that you wish to add it to the list.

When creating a new spreadsheet, especially when entering archive data from record cards, you may wish to retain the Recorder Names from a previous spreadsheet. Simply cut-and-paste the “My Recorders” worksheet from the old spreadsheet and all names will be transferred.

Select a Site

Simply start to enter the site name, or even just a part of it in the left-hand window and you will see in the right-hand window all (actually only up to the first 10) names and grid references taken from the “TBC Site List” worksheet that contain the letters you have typed. The more letters you type, the smaller the list.

When you see the site name you want in the right-hand window then select it and it will be copied to the left.

When the left-hand window is a valid name, i.e. it exists in the right-hand window then a tick appears in the small box between the windows and the record can be submitted.

Select a Species

As for selecting a site name above, start to enter the species name, or even just a part of it in the left-hand window and you will see in the right-hand window all (actually only up to the first 10) names (with scientific names) taken from the “TBC Species List” worksheet that contain the letters you have typed. The more letters you type, the smaller the list.

When you see the site name you want in the right-hand window then select it and it will be copied to the left.

When the left-hand window is a valid name, i.e. it exists in the right-hand window then a tick appears in the small box between the windows and the record can be submitted.

Keep Species Name with New Record

If you are entering data from record cards where the data has been sorted by species check this box to retain the species name when you create a new record. You will not then have to re-select the species name for the next record.

Comments

Enter any comments relating to the sighting. This is a free text field and will not be used in any automatic sorting functions

Count

Enter the number of birds seen.

Validating and Submitting Record Data

It is important to remember that the data shown on this form is temporary. It will be lost as soon as the form is closed unless it has already been submitted to the worksheet, i.e. the contents of the data fields actually entered into the corresponding row of the “MyRecords” worksheet.

The form insists the data is valid before the data can submitted to the worksheet. During data entry you will have noticed the tick boxes for Site and Species names (if not, then you won’t be able to submit the data).

If all data fields contain valid data then pressing the “Data Valid?” button will result in a tick being shown in the check box to the right. The tick may already be shown if the previous data entered had prompted a background validation check.

If the tick is shown then the next button will show “Submit New Record” or “Update Old Record”, as appropriate and will become bold (active). Press the button and the data will be written to the worksheet.

You can also delete the record on the rare occasions you wish to do so.

Worksheet Validation and Sorting

This section of the form becomes most useful for the County Recorder and Report Compilers when they have combined the records from a number of spreadsheets and recorders.

The primary purpose is to allow the “MyRecords” worksheet to be sorted according to the contents of the data fields, e.g. by species followed by date allows the profile of the number of birds of a given species to be brought together without manually searching through the records.

Number of Records

The form calculates this number for you to indicate the number of rows of data present

Validate Records

The form insists that all records are valid before they are sorted. This ensures that the “cut-and-paste” performed by the Compilers did not introduce any erroneous records and checks any data manually entered is valid.

Pressing this button begins a process where every record in the “MyRecords” worksheet is checked for valid Date, Site, Species and Recorder.

Any erroneous data values will be highlighted on the Data Entry Form.

As every record is validated the BOURC field is populated (overwritten) with the index number taken from the “TBC Species List” and the YYYYMM field is populated (overwritten) with the numeric value of the year*100 + month. These two calculated fields allow the “MyRecords” data to be sorted according to the BOURC list order and by month.

When all data records have been validated then they can be sorted

Sorting Order Window and “Sort MyRecords” Button

The window above the “Sort MyRecords” button shows the order in which the data will be sorted. The default order is:

BOURC            Ascending

YYYYMM            Ascending

Date            Ascending

Site            Ascending

Recorder            Ascending

This will result in all records of Mute Swan being brought together and moved to the top of the list. Followed by all records of Bewick’s Swan, and so on.

Within this grouping of each species the records will be sorted in date order, then by Site name.

Ascending or Descending?

The default sorting order is Ascending, e.g. starting with the oldest record for Mute Swan. The order can be reversed for each field individually. This could then bring the latest record for a Mute Swan to the top of the list.

Changing the Sort Order

To change the order of the data sorting first select the field in the window and then move it up or down using the appropriate button on the right. Similarly press the Ascending or Descending button to change the sort direction of that field.