To facilitate the entry and editing of data from the TUTI surveys, a custom program has been developed by TUTI.
The Speedit program (Survey Program for Entry
and Editing of Data Involving Travel) is an
Excel-based program, making extensive use of VBA macros. The version developed
for VISTA09 is designed so that the program itself writes much of the required
code, after looking up key parameters in various data tables describing the
survey.
The Speedit program uses separate data entry screens for the various components of the survey instrument (Household, Person, Vehicle and Stop data), drawing upon a variety of Input files, and producing a range of Output files, as shown below. The screens are designed to reflect the layout of the paper questionnaire, as much as possible, to facilitate error-free data entry.
The components of the Speedit program are as follows:
Inputs
Setup Pages – these pages enable the user to customise aspects of the program for a particular survey, including the name and dates of the survey. It is from these pages that information is obtained by the program which enables it to write the required code for the program.
Sample Data – this page contains the address data for all households in the survey sample. The data has previously been exported from the HotSpot Survey Administration program.
Coding Frames – several pages in the workbook contain the coding frames for the variables in the data entry worksheets.
Geocoding Files – a central component of Speedit is the ability to perform geocoding of locations “on-the-fly” as the data is entered. To facilitate this, a number of large databases had to be pre-assembled to enable geocoding via lookup tables. These databases include:
· Landmarks File – for VISTA09, a file of over
71,000 named and pre-geocoded locations such as shops, schools,
and other
places in the Study Area
· Address File - a file of over 1,500,000 geocoded addresses in the Study Area
· Streets File – a file of 70,000 geocoded street names in the Study Area
· Suburbs File – a file of over 15,000 town names and centroids across Australia
· Postcodes file - a file of over 2,400 postcodes and centroids across Australia
· Intersections File - a file of over 200,000 intersections in the Study Areas
Given the increased file sizes allowed in Excel 2007 (up to one million records per sheet), all these databases are held within the Speedit workbook. Some of the files (e.g. the address file) are split over more than one worksheet because of their size).
Data Entry
Start-up Screen - Starting the Speedit program presents the
start-up screen shown below. The data entry process is started by first
entering a Household ID number (HHID). This ID number is checked against the
Sample Data to check for its validity, and a household address and coordinates
are then returned to the Opening Screen. The program also checks the Household
Data output file to see if the data for this household has already been
entered, and accordingly provides options to the user as to how to proceed.
Household Form – Assuming that this is the first data entry
for this household, clicking on the large green button in Figure 4 will take
the user to the Household Form. The Household Form screen presents the same questions as
contained on the paper Household Form and in much the same layout. Data can
initially be entered by using the drop-down menus for each answer. However,
each drop-down menu also has “type-ahead” intelligence and the menu items have
been named so that usually just typing the first character of the answer will
provide the correct answer. The user can then enter data very quickly using the
keyboard and the Tab key to move from question to question. When data entry is
completed and the user clicks on the “Go to Person Form” button, an error-checking
routine is run. If any problems are detected, an error message screen appears
and allows the user to either correct the errors immediately or to proceed and
address the errors later.
Person Form – Once the data in the Household Form has been accepted, the user will
be taken to the Person Form. This form has space for up
to nine persons (not all shown here), laid out in similar format to the
paper version of the Person Form. The program already knows, however, how many
people to expect (from the Household Form) and only that many columns are
activated for person data entry. Once again, when data entry is completed and
the “Go to Vehicles Form” button (not shown) is clicked, an error-checking
routine is run before allowing the user to progress.
Vehicle Form – Once the data in the Person Form has been accepted, the user will be
taken to the Vehicle Form. This form is similar to the
Person Form and has space for up to nine vehicles (not all shown here),
laid out in similar format to the paper version of the Vehicle Form. Once
again, when data entry is completed and the “Go to Travel Form” button (not
shown) is clicked, an error-checking routine is run before allowing the user to
progress.
Travel Day Form – Once the data in the Vehicle Form has been
accepted, the user will be taken to the Travel Day Form.
This form is based on the front page of the Travel Diary booklet and accepts
information about where each person started their Travel Day, whether they
travelled on the day and, if so, when they started travelling on the day. If
they did not travel, they are asked why they didn’t travel on that day. Whether
they travelled or not determines which button then appears on the screen to
lead the user to the next appropriate page (after error-checking has been
performed).
Travel Diary Forms – Assuming that the respondent made some trips on the Travel Day, the user will be taken to the Stops Form screen (the top Figure shows the full screen and the bottom Figure shows a close-up on the area used for data entry for each Stop). The layout of this data entry screen is based on the Stops page of the Travel Diary, but with one major difference.
Question C from the paper survey (at the bottom of the first column) becomes the first entry on the Stops data entry screen (in these Figures, because there are only two persons in the household, and we are entering the data for Person 1, only the check box for Person 2 appears). The reason for this re-ordering of the questions is that if the respondent has travelled with another member of the household whose travel has already been entered in Speedit, then that data can be copied to the current screen, rather than re-entering all the data from scratch, by clicking on the Copy Stop button.
If the data is entered from scratch, then it follows the same order as shown on the Stops page of the Travel Diary, mainly via the use of drop-down menus corresponding to the answer options shown on the questionnaire page. Once the place type has been specified in Question A, the option is provided in question B for specifying the place as a Landmark (a hierarchical list of pre-geocoded locations including train stations, schools, shops, beaches and the like) which will give its coordinates automatically. If it cannot be specified as a Landmark, then the address details need to be specified and the coordinates found by clicking on the GeoCode button.
The geocoding module within Speedit uses several different types of geocoding (depending on the type of location information provided for geocoding), in decreasing order of accuracy and reliability:
• Survey Address Geocodes
• Landmarks
• Full Address & Suburb
• Full Address & Postcode
• Intersection & Suburb
• Intersection & Postcode
• Nearest Address on Street
• Random Address on Street
• Street Name & Suburb
• Street Name & Postcode
• Random Zone in Suburb
• Town Centroid
• Interstate/Overseas
The answer to the “More Stops?” question in section G determines whether a different exit button appears to move to another Stop or to go to the next part of the questionnaire. When leaving each Stop, an error-checking routine is run to detect any errors within that Stop. The Stops form is used repeatedly for each of the stops described by the respondent.
The yellow regions at the left and right of the Stops screen show some summary characteristics of the Previous and Next Stops for the current person to enable the current Stop to be better placed in context. Other features available on the Stops form are the ability to Insert a Stop before the current stop or to Delete the current Stop (both useful when editing data previously entered), to skip between Stops and Persons (using the Person and Stop menus) and to use the Navigator button to view the entire sequence of Stops for any person.
Income Form – Once all Stop data has been entered for one Person (or if that
Person made no Stops on the Travel Day), then the user is taken to the Income Form
screen. This form accepts information about the personal
income of the respondent, who filled out the Travel Diary and when it was
filled out. To move on to entering Stops data for the next member of the
household, the “Go to Travel Day Form for Next Person” button is clicked,
whereupon an error-checking routine is run for all the stops entered for this
person. After resolution of any problems identified (or leaving them for
later), the program goes to the next person or, if the last person’s travel
data has already been entered, it then runs an overall error-checking routine
for the entire household’s data.
Outputs
HH Data – the data entered for each household is stored in two formats within Speedit. Firstly, the Raw Data, corresponding exactly to the data entered on the Household Form, is recorded so that the Household Form screen can be re-created later (for editing purposes) by importing the Raw Data from an external file. Secondly, a file of Processed Data is created as an intermediate step in producing the final Household Data file for the client. When all data entry and editing has been completed for one data entry session for a household, the data can be exported to an external data file by clicking on the “Export/Clear Data” button shown on the Start-up Screen in Figure 4. This appends a time-stamped copy of the current version of that household’s Raw and Processed data onto the end of the external files of Household data. It also clears the internal data worksheets within Speedit, ready for data entry of another household. The most recent version of the Raw Data can later be imported into Speedit to re-commence data entry or editing.
Vehicle Data – using the same procedures as described above for Household data, this external file contains the Raw and Processed versions of all the previously entered Vehicle data.
Person Data – this external file contains the Raw and Processed versions of all the Person data.
Stop Data – this external file contains the Raw and Processed versions of all the Stops data.
Data Entry Log – Speedit automatically records and exports entry times for every survey entered on a given computer, including detecting time away from the computer (e.g. if entry is started before lunch, and finished afterwards). This data is collated every night to help track progress of the project, be used during the validation process, and to allow mid-project extraction of selected portions of the dataset for final analysis.
Error Sheets – at the end of each data entry screen, and after all data entry has been completed for a household, a comprehensive error check is performed. The Errors found during this check are listed on the Error Sheet. This sheet can then be used by the data enterer to make immediate corrections. Any problems that cannot be resolved are retained on this Error Sheet and exported, and can then be used as a Prompt Sheet during Clarification Calls with households, or during the Validation process.
Travel Activity
Profile – the data obtained
during the entry of the Travel Diary forms can be displayed graphically on the
Travel Activity Profile sheet, using the format proposed by Peter Jones (1978) for the HATS simulator. The Travel Activity Profile shows the
activities and travel being undertaken throughout the day by each person.
At-home activities are shown on the left for each person, travel is in the
middle, while out-of-home activities are on the right for each person. The
Travel Activity Profile is a very useful means of identifying gaps in the day
for each person, and for comparing activity profiles for persons who travel
together during the day. It is heavily used during validation of the surveys
and is also useful when re-constructing travel diaries for person aged under
five (who are not asked to fill out a travel diary), as the travel they share
with other members of the household is visually indicated on the TAP.