Automated Data Analysis Using Excel: 1st Edition (Paperback) book cover

Automated Data Analysis Using Excel

1st Edition

By Brian D. Bissett

Chapman and Hall/CRC

480 pages | 255 B/W Illus.

Purchasing Options:$ = USD
Paperback: 9781584888857
pub: 2007-06-15
$83.95
x
eBook (VitalSource) : 9780429150432
pub: 2007-06-15
from $41.98


FREE Standard Shipping!

Description

Because the analysis of copious amounts of data and the preparation of custom reports often take away time from true research, the automation of these processes is paramount to ensure productivity. Exploring the core areas of automation, report generation, data acquisition, and data analysis, Automated Data Analysis Using Excel illustrates how to minimize user intervention, automate parameter setup, obtain consistency in both analysis and reporting, and save time through automation.

Focusing on the built-in Visual Basic® for Applications (VBA) scripting language of Excel®, the book shows step-by-step how to construct useful automated data analysis applications for both industrial and academic settings. It begins by discussing fundamental elements, the methods for importing and accessing data, and the creation of reports. The author then describes how to use Excel to obtain data from non-native sources, such as databases and third-party calculation tools. After providing the means to access any required information, the book explains how to automate manipulations and calculations on the acquired data sources. Collecting all of the concepts previously discussed in the book, the final chapter demonstrates from beginning to end how to create a cohesive, robust application.

With an understanding of this book, readers should be able to construct applications that can import data from a variety of sources, apply algorithms to data that has been imported, and create meaningful reports based on the results.

Reviews

… this book shows the reader, in a step-by-step fashion, how to construct a useful automated data analysis application that is useful in both industrial and academic types of sets. Using this book, the reader should be able to construct applications that can import data from various sources, apply algorithms to data that have been imported, and create meaningful reports based on the results. The book could be used as a reference by researchers, engineers, and graduate students who use Excel to handle their data sets.

Technometrics, February 2010, Vol. 52, No. 1

Automated Data Analysis Using Excel explains how to conduct and automate a wide range of data and analysis tasks using VBA. It includes a CD-ROM with data examples and code that is very useful, especially to the new programmer … Overall, I consider this a very useful reference for the ambitious programmer wishing to fully harness the power of Excel.

—Madhumita (Bonnie) Ghosh-Dastidar, The RAND Corporation, The American Statistician, May 2009, Vol. 63, No. 2

Table of Contents

INTRODUCTION

Purpose of This Text

Evolution of the Spreadsheet

Should I Be Using Excel? What Other Options Exist?

Scope of Coverage

Projects Which Lead to This Text

Before Beginning

Odd Behavior in Excel to Watch Out For

The Top Productivity Hindrances in Excel

Final Thoughts

ACCESSING DATA IN EXCEL: A MACRO WRITERS PERSPECTIVE

Introduction

The Workbook

The Worksheet

Ranges in Worksheets

Using Explicit Referencing

Rows and Columns

Searching Worksheets-Using Find

Copying, Clearing, and Deleting Data

Sorting Data

Deleting Rows and Columns

Summary

METHODS OF LOADING/SAVING DATA IN EXCEL

Introduction

Using the Standard Open File Dialog Box to Load a File

Using the Standard Save As Dialog Box to Save a File

Automatically Opening Files and Templates

Importing Data to a Worksheet

Automatically Saving Files and Templates

Allowing the User to Browse for a Directory

Setting the Starting Directory for a User to Browse From

Using the Windows Registry to Save Settings

Determining Subfolders of a Chosen Folder

Determining Files within a Chosen Folder

Practical Strategies for Dealing with Large Amounts of Data

Creating Database "Friendly" Files

Obtaining Drive, Directory, and File Information

Summary

CONTROL AND MANIPULATION OF WORKSHEET DATA

Introduction

Scope and Use of Variables in VBA

Operating in Excel's Environment from VBA

Utilizing Arrays to Store Data

Passing Parameters By Value or By Reference

Array Looping Structures

Using Object Variables

An In-Depth Look at Worksheets

Extraction of Data Using Landmarks and Looping Structures

Summary

THE UTILIZATION OF FUNCTIONS FROM WITHIN VBA

Introduction

Creating and Utilizing a VBA Function in Code

Handling Errors in VBA Functions

Adding a Function to a Worksheet Cell Using VBA Code

Creating Additional Built in Functions for Excel

Dynamic Formatting of Worksheets Using Functions

Applying Dynamic Formatting Using VBA

Using the Macro Recorder to Capture a Process

Creating a Linear Regression Tool Using the VBA Analysis Toolpak

Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak

Summary

DATA MINING IN EXCEL

Introduction

The Terrible Truth about Colors in VBA

Form Reuse in VBA Projects

The RefEdit Control and Its Associated Problems

Highlighting and Coloring Cell Fonts and Backgrounds

Creating a Highlight If Tool

Creating a Color Font If Tool

Creating a Copy If / Move If Tool

Creating an Extract If Tool

Creating a Windowing Tool

Linear and Nonlinear Mapping

Automatically Loading and Extracting Data from Complex Directory Structures

Summary

CREATING CUSTOM REPORT WORKSHEETS

Introduction

Use of Templates when Creating Custom Reports

Preparation of Dual View Reports

Executing Calculations upon Changing Views

Analysis within Report Worksheets

Basic Formatting Techniques

Automatically Emailing Reports

Summary

INTRODUCTION TO MICROSOFT ACCESS

Introduction

Elements of a Relational Database

Connecting to an MS Access Database

Queries: How to Retrieve Information in Databases using SQL

Using the Microsoft ODBC Add-In for Microsoft Excel (XLODBC.XLA)

Constructing a Database Query Tool

Using Data Access Objects (DAO)

Elements in the DAO Architecture

Summary

FROM EXCEL TO ACCESS AND BACK AGAIN

Introduction-DAO vs. SQL

Using Pointers in Dynamic Database Algorithms

Concepts in Database Alteration and Management

Creating New Tables in Access from Excel

Adding and Removing Fields in Access Tables from Excel

Adding Records to Specific Fields in Database Tables

Deleting Records in Databases Using Bound Controls

Compacting Databases Using VBA

Returning the Results of a Remote Access Database Query to an Excel Worksheet

Summary

ANALYSES VIA EXTERNAL APPLICATIONS

Introduction

Setting Up a Matlab ActiveX Server from Excel

Matrix and Vector Building

Defining Matrices and Vectors in Matlab from Excel

Using Matlab to Perform More Advanced Forms of Regression

The Inner Workings of the Multiple Linear Regression Example

Interfacing Excel and Origin to Perform More Complex Analyses

Excel to Origin Dynamic Data Exchange (DDE) Example

Interfacing Excel and Origin Using COM (Component Object Model)

Example: Creating a COM Tool to Perform Curve Fitting Using Origin from Excel

Opening and Plotting Excel Workbooks in Origin for Superior Graphics

Summary

AN EXAMPLE ADA APPLICATION WHICH GENERATES A REPORT

Introduction/Problem Definition

A Quick Word on Six Sigma

Dealing with the Raw Data

Process Analysis

The Final Report

Saving the Final Report

Summary of the Final Application

APPENDIX A: Advanced Browse for Directory Code Listing

APPENDIX B: The Menumakr Shareware Application

APPENDIX C: Regression Analysis and Best-Fit Lines

APPENDIX D: Built-In Constants for Microsoft Visual Basic for Applications

APPENDIX E: Excel VBA ODBC Functions (XLODBC.XLA)

APPENDIX F: Scope of Variables in Visual Basic for Applications

APPENDIX G: LONG Integer to a Hexadecimal Constant

APPENDIX H: Use of the SendKeys Statement in Excel VBA

About the Series

Chapman & Hall/CRC Data Mining and Knowledge Discovery Series

Learn more…

Subject Categories

BISAC Subject Codes/Headings:
COM021030
COMPUTERS / Database Management / Data Mining
MAT029000
MATHEMATICS / Probability & Statistics / General
TEC009000
TECHNOLOGY & ENGINEERING / Engineering (General)