To facilitate the entry and editing of data from the TUTI surveys, a custom program was developed by TUTI.
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 was designed so that the program itself wrote much of the required code, after looking up key parameters in various data tables describing the survey.

The Speedit program used 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 were 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:


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
      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

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.


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.

  [Home]                                                                                                                                                          [Top]

© The Urban Transport Institute 2013