|
There are a number of ways to work with Excel using SHAZAM Professional Edition. Each method uses a different mechanism and one may be better than the others depending on the task at hand. The Data Connector offers superior file access capabilities. To illustrate these methods we analyse motor vehicle speed and flow data around the London M25 ring road. The dataset contains approximately 20,000 observations with measurements taken every 3 minutes in daylight hours over a period of 4 months.
|
|
|
|
1. Using the 'Open' Button
or Menu This is the quickest way of accessing a Microsoft Excel spreadsheet and allows viewing of the data directly in the SHAZAM data editor. The data may be edited in-cell, new variables generated and then saved back as an Excel file by clicking the 'Save' button. Alternatively the file may be saved as a SHAZAM Data file by using the 'Save As..' menu and selecting 'SHAZAM Data File' from the 'Files of Type' listbox. Once open the file may also be added to the current project and automatically read into SHAZAM. This will convert the file to a SHAZAM data file (.shd or .dat) so that it may be used with maximum speed and efficiency and make it available to be used with any commands entered in a Command Editor window as well as with the SHAZAM wizards.
Figure 1: Opening an Excel Spreadsheet from the 'Open...' Button or Menu After opening our traffic dataset of approximately 20,000 observations the dataset appears in the data editor as:
Figure 2: Traffic Speed and Flow Data Once the spreadsheet has been opened, to add it to the current project click the 'Add' button and choose a filename. The spreadsheet is now available for analysis.
Figure 3: Simple Analysis We use two SHAZAM commands to first calculate statistics on each variable and then to perform a simple OLS regression between speed and flow. Clicking the 'Run' button executes the commands producing the output in a separate window within SHAZAM. Because the GRAPH option was used Actual vs Fitted and Residual graphs are produced as separate windows. If desired, these items may be added to the current project so that they may be edited and accessed easily.
The Data Connector provides universal data access mechanisms using Microsoft® Active Data Objects (ADO) to connect to Local Machine, Network and Internet enabled databases. A default set of drivers for a number of common data sources are installed with SHAZAM Professional Edition, but additional drivers installed on the computer will be detected and displayed if supported.
Figure 4: Using the Data Connector The Connector uses a different method to open the dataset than the method of 1). Once the connection, database and table choice have been made SHAZAM opens the entire table contents and displays the default SQL (Structured Query Language) statement to do it. The SQL statement may be modifed as required to narrow or widen the data selection. Clicking the play button executes the modified statement allowing you to perform tasks such as data grouping in a fast and efficient manner.
Figure 5: Executing a Custom SQL Statement In this example we modify the SQL statement to select quarter hour averaged speeds for the first quarter hour past 8am on a Monday morning (day 2). The SQL query engine performs the quarter hour averaging of speeds (measurements in the data were taken every 3 mins over a 4 month interval), selects and groups the dataset of approximately 20,000 records to a final set of 15 records. With the Data Connector you may switch between tables, select and group data before importing it into SHAZAM with the 'Add' button to add it to the current project in the same way as for the previous method. The Data Connector provides powerful features for the grouping and extraction of data and is especially helpful for working with large datasets. SQL is the Information Technology industry standard for working with databases and SHAZAM extends that ability to enable it to be used directly with Microsoft Excel spreadsheets.
If the features available in methods 1) and 2) above are not required then access to Microsoft Excel spreadsheets may be accessed directly from the command window using a READ statement. The first row of the Excel file must contain the variable names and the names must be listed in the READ statement (the NAMES option is not available with Excel spreadsheets). Names must be 8 characters or less. The Excel worksheet must have the name "Sheet1" or be specified explicitly. The file must not be open in Excel while running the SHAZAM commands. In the case of our dataset the following statements will produce the same output as given in 1).
where 'MySheet' is the name of the sheet containing the data. Omitting the '|MySheet' requires the 1st sheet be named 'Sheet1'. |
