Automated Data Analysis Using Excel

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.

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