Using a Laptop as a Time-Activity Budget Data Recorder (or, recording data while looking through a scope)

This blog entry covers one method to efficiently record data on a laptop in the field using single-key strokes.  This method was useful for recording time activity budgets, a sampling method to assess temporal variations in an animal or population's behavior throughout a day or other time period.  The major benefits for using this technique was that it allowed me to record data while looking through a scope (see picture to the right) and it recorded data directly into an Excel spreadsheet which avoided having to re-enter data.  Other techniques, such as using tape recorders or printing calculators (I tried both), required time consuming re-copying of data into a spreadsheet for analysis.

There are two components of this technique, besides having a laptop with Excel:

1. Making a bracket to fit your laptop keyboard to execute the shortcut keys.
2. Setting up Excel with data entry macros assigned to shortcut keys.

I'll cover making the bracket first:

A. My prototype bracket was made out of cardboard and scotch tape.  Easy.

To the right is an image of the top.  It's simply multiple layers of cardboard taped together.  The layers give rigidity and raise the wrist up to make data entry easier and more ergonomic.  The bottom layer of cardboard sticks out to form the "L".  This protrusion covers and presses down both the the "Ctrl" and "Shift" keys to execute the macro shortcut keys for 1-stroke data entry.

B. This image shows the bottom of the bracket.  My finger is pointing to the stacks of cardboard that press down the control key under the "L" protrusion.  This view also shows the layers of cardboard on the top.  My final version was about twice as thick, which I found to be more comfortable.

C.  In use, the bracket sits on top of the laptop keyboard.  The bracket should be wide enough to cover the mouse pad to prevent accidental moving/clicking on any cells in the spreadsheet.  The weight of your hand on the top of the bracket is what presses down the control key. Because it's important that the bracket stays rigid, I wound up using a piece of stiff plexiglass for the bottom of the bracket so the "L" shaped protrusion wouldn't bend or crease under pressure.  The green circle shown in the photo to the right shows the placement of the bracket on the laptop.  You might see the plexiglass bottom and cleaner look from being glued rather than taped.

Next, the macro code and setting up shortcut keys:

Photo A above shows that there were 9 shortcut keys I used:  2 keys to identify the subject as Male or Female and 7 keys to depict 7 behaviors.  It's possible to program more keys, but it could get too difficult to efficiently enter data.  In my Excel sheet, I wanted the sex of the animal to be recorded in one column and the corresponding behavior it was engaged in in the column next to it.  So, for each individual, I would enter the sex immediately followed by the behavior.  If you're recording time budget data based on habitats, you can program keys to record habitat that each behavior is associated with rather than a male/female parameter.  Hitting the arrow or return/enter keys are not necessary with the macros codes below:

1)  Male/Female code in the first column

Open Excel and then press Alt+F11 to open Visual Basic.  Click on "Insert" and then  "Module".  Enter the following code into the Visual Basic module:

Sub MALE()
'
' MALE Macro
' types 'm' and goes to next column
'
' Keyboard Shortcut: Ctrl+shift+a
'
    ActiveCell.FormulaR1C1 = "m"
    ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

This macro enters an "m" in the cell that the cursor is in and then selects the adjacent cell to the right of that (sets you up to enter a behavior associated with the male).  Recall the R1C1 format is a relative cell reference code (for Row1Column1 format).  Click here for more info on the R1C1 format (opens in new page, scroll about 3/4 down the page for info). For the female macro, simply change the name of the macro (the word after the "Sub" command), switch out the "m" for "f", and the shortcut key in my example would be "ctrl+shift+z".  The text after the " ' " (single apostrophe) is for text that should not be read by the macro (for making notes in the code), so shortcut keys at this point are not actually assigned.

2) Behavior codes to be placed in the second column.

Insert another Module or add to the existing module created above:

Sub feeding()
'
' feeding Macro
' types 'feed' and goes down & to the left
'
' Keyboard Shortcut: Ctrl+sift+s
'
    ActiveCell.FormulaR1C1 = "feed"
    ActiveCell.Offset(1, -1).Range("A1").Select
End Sub

This macro enters the word "feed" (for feeding) in the cell that is currently selected and then selects the cell to the lower left, setting up the spreadsheet for the next male/female entry.  The remainder of the 6 behaviors were assigned to the keys d, f, q, w, e, and r.

3) Assigning shortcut keys.

Once all the macros have been created, you can assign shortcut keys to run the macros using the braket described above and a single key stroke.

• Under the "View" ribbon tab, click on "Macros" on the right side.  Then click on "View Macros" if necessary.
• From the list of macros, select one of the macro names and click the "Options" button on the right.
• Place your cursor in the box under where it says "Shortcut Key" and press the "Shift" and whatever letter key you want to assign to it.  I use Ctrl+Shift+key because there are very few pre-programmed shortcut keys assigned to these key combinations.  For instance, Ctrl+z for all MS Office products is the shortcut key for "undo".  By assigning the "female" macro to Ctrl+Shift+Z, you can still use the "undo" shortcut key.

4) Use of the bracket and shortcut keys:

You start by selecting the cell where you want to start recording the first datum. In this case it would be the top of the "sex" column.  When the bracket is placed on the keys so that the "L" protrusion covers the Ctrl and Shift keys, those keys are depressed when the weight of your palm is on top.  So, all you have to do is press the "a" key for male or the "z" key for female followed by a behavior (s, d, f, q, w, e, or r keys).  I was able to do this quickly (maybe one datum entry per second) without having to remove my eyes from the scope.  I worked from a lawn chair or a pickup truck.

That's it for this blog.  Please let me know if you found this useful or if you have any questions & be sure to sign my guestbook!

Replacing AOU Bird Species Codes with Common Names, Latin Names, and Family Names in Excel. Plus, Sorting Birds by Phylogenetic Order.

I produced this tool to convert AOU Bird Species Alpha Codes (standard 4-letter codes established by the American Orithological Union) into common names or scientific names in Excel spreadsheets.  It has other functionality as well, such as helping to sort bird species in taxanomic order and by family.

Download the free Excel spreadsheet "BirdMacroLibrary.xlsm" (formerly "Colin's Macro Library.xls") by clicking the button below (or get general info about my natural resources downloads here). 

The spreadsheet uses Excel macros.  To learn more about using macros in my wildlife and botany spreadsheets, click here.  Spreadsheet works with Excel versions 2003, 2007, and 2010. (Also see my pages about Excel formula usage and a software disclaimer).

The first 4 sets of instructions below use macros, the 5th item uses formulas, and the 6th describes how to copy & paste your codes into a table to have them automatically converted.  If you're not comfortable using macros, go to part 6.

1. Replace AOU Codes with Common Names in Excel

The following instructions describe how to use Colin's bird macro library for Excel spreadsheets that have AOU codes in either columns or rows.  This macro will replace the AOU codes with the bird common name without disturbing data in adjacent columns (such as number detected, plot number, etc).  If it comes across an unknown code, it'll leave the code unchanged.  Note:  You can't undo this action.

1. Download "BirdMacroLibrary.xlsm", open it and enable macros if necessary (link above describes how).
2. Open your spreadsheet with bird data if it's not already open (both the macro library and your data spreadsheet need to be open on your computer).
3. In your spreadsheet, select all the 4-letter species codes you wish to convert.
4. Press the shortcut keys "Ctrl + Shift + R"  to replace all codes with the common name.  (i.e., press the "ctrl" key down and while still holding it down, press the "Shift" and "r" keys).

You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

Macro shortcut keys can be changed under menu item "View | Macros | View Macros" (Excel 2007/2010).  On the list of macros that show up, click on the name of the macro and then click on "Options" to change the shortcut.  Note that if you change the names of tabs or the filename of the macro library, some macros will not work.

2. Create New Column or Row and with Common Names in Excel

This is a bit of a safer operation since macros cannot be undone.  These macros will create a new column (or row) and place the common name that corresponds to the species code in it, rather than replacing the original code.  The directions are the same as above, except:

• Place your cursor on the first AOU species code in the row or column.
• If data is in columns, press "Ctrl + m".  If data are in a row, press "Ctrl + Shift + M".

You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

3. Replace AOU Species Code with ANY NAME in the AOU List

1. Again, have "BirdMacroLibrary.xlsm" open at the same time as your data spreadsheet.
2. It's recommended to make a copy of the column or row that has the species codes in it.
• Example: For data in columns, click on the column header with codes in it to select the whole column.
• Press "ctrl + c" to copy the selected column.
• Right-click on the column header and select "Insert".
• Place your cursor at the top of the new column, select the cell, and press "ctrl + v" to paste the data.
3. Highlight the species codes that you wish to convert.
4. Press "Ctrl + r". 
5. An input menu will pop-up that looks like the image below.  Enter a selection and press OK.

Pop-up input box to replace AOU species code with common name, scientific name, family, etc.

  You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

4. Sort Bird Lists by Phylogenetic Order

This macro will insert a column to the left of your AOU codes and place the phylogenetic sorting order of that species in the new column.  You then manually sort all the data based on the sort order.  Data must be in AOU species codes format and must be in columns.  (See next section below about how to use the VLOOKUP function to sort birds by phylogenetic order if the data are in common name or latin name formats, or edit the macro to suit your needs).

1. Have both the bird macro library and your data spreadsheet open at the same time.
2. Click on the first (top) AOU code in the column.
3. Press "ctrl + p".  A new column will be created and the sort orders will be placed in the cells.
4. Give the new column a name, such as "Phylogenetic Order."
5. Highlight all the data, in the table to ensure associated data, such as the record date or number detected, gets sorted with the species names.
6. Use the Excel sort tool to sort by the numbers in the "Phylogenetic Order" column, from low to high.
7. You may then delete the sort order column.

5. How to Sort Birds by Family Name (and cross-reference birds with other AOU info)

This section describes how to use a "VLOOKUP" function to assign ornithological Family names to lists of birds in Excel.  This is not a macro, but can easily be made into a macro function like those above.  But learning how to use this function will aid you in matching a list of birds in any format (AOU code, common name, or latin name) with any information cross-referenced in the AOU Alpha Code list (such as Order, Family, Sub-Family, Scientific Name, etc).

BirdMacroLibrary.xlsm has to be open in order to use this function as described here.

For this example refer to the image below:

For the example of matching AOU Alpha codes with family name, look at the text in GREEN in the example (sorry to those who are red/green colorblind).  This is the same data on the "code lookup example" tab of "BirdMacroLibrary.xlsm".

The VLOOKUP function has four parameters that need to be set:

1. Lookup_value is the cell that holds the bird name (it can be an AOU Code, common name, or scientific name).  In this example, the lookup value is "A3" and refers to "NOMO," or "Northern Mockingbird."
2. Table_Array tells Excel where to find the value that you want to look up and match.  '[BirdMacroLibrary.xlsm]LIST10'!A$2:H$2169 says to look in the macro library, and specifically look at the tab "LIST10" and at cells in the row and column range A3 to H2169. (The apparent double quotes in Colin''s is actually two single quotes, which is how the code says an apostrophe is used in the name).
3. Col_Index_Num tells which column holds the information that you want to match to the value that you just looked up.  In this case, cell A3 (NOMO) was the value that was looked-up and we want to match it to Family name, which is the 10th column on the tab 'LIST10'.
4. The last parameter (range_lookup) should be "FALSE".  This means only an exact match for the value in A2 will be used.  This is important because there are bird names (especially scientific and common names) that are fairly similar and a close match may wind up matching the wrong information.

Here are the steps to use the VLOOKUP function with "BirdMacroLibrary.xlsm" to match bird names with family names and then sort and group based on family:

• In a blank cell in an adjacent column to the bird name or code, type "=VLOOKUP(..." and enter all the parameter values as described above.  Also, title this column "Family Name" or similar.
• Copy the formula in the cell down the column to all rows of data.  Since we're using relative references, the lookup value will update itself for each row or bird record.
• Family names should appear in the column with the VLOOKUP formula in it.
• Highlight the column with family names in it and do a "Copy and paste values":
• After the column with the formulas are highlighted press the following shortcut keys (one at a time, not all at the same time): "alt", "e", "s", "v".
• Finally, follow the instructions in section 3 of this guide to sort the data by phylogenetic order.  Or, use the text in RED in the example above or in the spreadsheet to use a VLOOKUP statement to assign sort order values to your data.

6. Using the Bird Code Lookup sheet to cross-reference your list of bird Alpha Codes with all available information, including separate genus and species columns.

Again, this is not a macro, but is a spreadsheet tool in "BirdMacroLibrary.xlsm".

• Open my macro library and go to the "Bird Code Lookup" tab.
• Paste your species codes in the yellow column to have all other info show up.
• Copy & paste the info you need to your own spreadsheet. Remember to paste as "values" (press the following keys in order, not at the same time: "alt," "e," "s," and "v.")  You can follow this by pasting the text formats (alt, e, s, and t) if you wish.

Page is "protected."  You can only enter info into the yellow column.  To unprotect, go to "Review" and "Unprotect Sheet" in Excel 2007/2010.

AOU Bird species code converter spreadsheet tool (not a macro).

7. Updating "BirdMacroLibrary.xlsm"
The list used in my macro library to convert AOU species codes is from the most recent update provided on the Institute for Bird Populations website (http://www.birdpop.org/AlphaCodes.htm), which is Sept 19, 2012.

The taxonomic/phylogenetic order info came from the AOU website: http://checklist.aou.org/taxa.

If you need to update the spreadsheet, go to the IBP website and download the zipped DBF file "English and Scientific Alpha Codes for North American Birds"  (a note at the top of the page tells when the most recent update was). Copy and paste the data into the "LIST10" tab on Colin's macro library.  You'll need to add phylogenetic sort code column in colum "H" for the macros to work properly.

======================================================================

That's all there is to using "Colin's macro library.xls" macros and spreadsheet tools.  A 3-part instructional blog for converting plant species codes into common names, scientific names, etc. is here.  Please leave me a comment if you have questions or feedback.

Copy & Fill Data Down Columns in Excel (Macro & non-Macro)

This is one of the features I've found useful many times over the years, so is one of the first tips that I'll introduce and will be the first macro that I'll cover on this blog.  To learn more about macros, click here.


THE NEED:


Do you ever have the same data to enter over and over and over and over again?  For instance, for a weather description column for Las Cruces (NM) in June:  Sunny, Sunny, Sunny, Sunny….

Or, how about multiple columns or a whole row of data that needs to be repeated for tens or hundreds of rows?  It's often useful and necessary that each datum (i.e. species and number detected) have all ancillary data associated with it (i.e. habitat type, plot #, date, soil type, etc).  This data format is necessary in order to use Pivot Tables, to export into SAS or other statistical software, etc.

For instance, in the example below each individual datum is a species and number detection and ancillary data are unit, habitat, plot #, and time.  To speed-up data entry, I often only enter data in rows when the value changes.  Here, I entered the unit name and habitat type only once for two avian point counts since they were the same.  I also only entered the plot number and time once for each plot. This isn't just lazy, but helps eliminate spelling errors that can cause data analysis problems later.  Now I want to copy all the unique data values and paste it so that it fills the blanks until the end of the dataset.

This entry explains how to easily copy data and fill blank cells below it in columns. I'll explain a way to do it without and with a macro.