WWW | Community | Store | Account | Contact Us
SHAZAM Homepage
Working with Excel

There are a number of ways to work with Excel using SHAZAM Edition. Each method uses a different mechanism and one may be better than the others depending on the task at hand.

  1. Using the 'Open' Button or Menu
  2. Using the Data Connector
  3. Cut and Paste into a Data Editor

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.


2. Using the Data Connector

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.

3. Cut and Paste into a Data Editor

A simple alternative to methods 1) and 2) is simply to open the Data Editor from the Data menu and select 'New Dataset'. Enter the numbers of rows and columns of the dataset (including any header row) and click 'OK'. Click the first cell and and click the 'Paste' button (or use CTRL-V on the keyboard). To set any row as the header Row, select the row and use the 'Set As Header Row...' option from the Data menu or use the right mouse button menu.