Thursday, August 16, 2012

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!