Automated Data Analysis Using Excel (Chapman & Hall/CRC Data Mining and Knowledge Discovery Series)

This new edition covers some of the key topics relating to the latest version of MS Office through Excel 2019, including the creation of custom ribbons by injecting XML code into Excel Workbooks and how to link Excel VBA macros to customize ribbon objects. It now also provides examples in using ADO, DAO, and SQL queries to retrieve data from databases for analysis. Operations such as fully automated linear and non-linear curve fitting, linear and non-linear mapping, charting, plotting, sorting, and filtering of data have been updated to leverage the newest Excel VBA object models. The text provides examples on automated data analysis and the preparation of custom reports suitable for legal archiving and dissemination. Functionality Demonstrated in This Edition Includes: * Find and extract information raw data files * Format data in color (conditional formatting) * Perform non-linear and linear regressions on data * Create custom functions for specific applications * Generate datasets for regressions and functions * Create custom reports for regulatory agencies * Leverage email to send generated reports * Return data to Excel using ADO, DAO and SQL queries * Create database files for processed data * Create tables, records, and fields in databases * Add data to databases in fields or records * Leverage external computational engines * Call functions in MATLAB^®^ and origin from Excel Cover Half Title Series Page Title Page Copyright Page Dedication Contents Preface About the Author Acknowledgments Introduction It Isn’t 2007 Anymore, Dorothy Purpose of This Text What Differentiates This Text from Its Competitors? Evolution of the Spreadsheet Should I Be Using Excel? What Other Options Exist? Scope of Coverage Projects Which Lead to This Text Before Beginning: Setting Up the Examples Odd Behavior in Excel to Watch Out For The Top Productivity Hindrances in Excel Final Thoughts Chapter 1: Customizing Excel’s Ribbon Interface Introduction XML Code Structure for Creating Ribbons Simple Ribbon Example Creating an Add-In to Automatically Display Custom Ribbons When Excel Starts Working with Existing Ribbon Tabs, Groups, and Buttons Automating Code to Generate Large Complex Ribbons Customization of the Quick Access Toolbar Programming more Esoteric Ribbon Controls Helpful Resources for Creating Custom Ribbons Final Thoughts Chapter 2: Accessing Data in Excel: A VBA Macro Writer’s Perspective Introduction The Workbook The Worksheet Ranges in Worksheets The Union Method for Ranges Using Explicit Referencing Rows and Columns Searching Worksheets – Using Find Finding the Next Instance of an Item Finding a Specific Instance of an Item Tokenizing an Item into an Array Copying, Clearing, and Deleting Data Sorting Data Sorting Data by Absolute Value Sorting a Range within a Worksheet Deleting Rows and Columns Deleting Hidden Rows and Columns Automatically Deleting Rows When a Condition is True Finding Duplicates Summary Chapter 3: Methods of Loading and Saving Data in Excel Introduction Processing the New File Types Present in Excel 2007 and Beyond 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 Importing a Worksheet from Another Workbook – 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 Writing Information to Excel’s Status Bar Writing Log Files Summary Chapter 4: Control and Manipulation of Worksheet Data Introduction Scope and Use of Variables in Excel VBA Operating In Excel’s Environment from VBA Utilizing Arrays to Store Data Passing Paramaters 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 Chapter 5: Utilizing Functions in Excel Introduction Creating and Utilizing VBA Functions in Code Handling Errors in VBA Functions Adding a Function to a Worksheet Cell Using VBA Code Array Formulas and Overcoming the 256 Character Array Formula Limit Creating Additional Built in Functions for Excel Dynamic Formatting of Worksheets Using Functions Applying Dynamic Formatting using VBA Using the Macro Recorder to Capture Processes Using the Big Five Functions – Forecast, Trend, Growth, Linest, and Logest Creating a Linear Regression Tool Using the VBA Analysis Toolpak Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak Keeping VBA from Crashing During Intense Computations Correlation vs. Causation – Sure It Fits Well, But What Does That Mean? Summary Chapter 6: Data Mining in Excel Introduction The Terrible Truth about Colors in VBA Form Reuse in VBA Projects The Refedit Control and Its Associated Problems Creating a Refedit Control from a Textbox 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 a Windowing Tool Linear and Nonlinear Mapping Using Lookup Functions – Lookup, Vlookup, Hookup, Match, Index, etc Automatically Loading and Extracting Data from Complex Directory Structures Chapter 7: 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 Chapter 8: Introduction to Microsoft Access Introduction Elements of a Relational Database Connecting to an MS Access Database Queries: How to Retrieve Information in Database Using SQL Constructing a Database Query Tool Using Data Access Objects (DAO) to Create a New Database File from Code Elements in DAO Architecture Summary Chapter 9: From Excel to Access and Back Again Introduction 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 Returning the Results of a Remote Access Database Query to an Excel Worksheet Compacting Databases Using VBA Summary Chapter 10: 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 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 Chapter 11: An Example ADA Application 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 Index "This new edition includes some key topics relating to the latest version of MS Office, including use of the ribbon, current Excel file types, Dashboard, and basic Sharepoint integration. It shows how to automate operations, such as curve fitting, sorting, filtering, and analyzing data from a variety of sources. The book allows users to analyze data and automate the preparation of custom reports and demonstrates how to assign Excel VBA code to the new "Ribbon" user interface"-- Provided by publisher