Web Scraping With Excel

Overview

This New Horizons exclusive class was created to address a variety of real world business issues. The Internet has vast amounts of information. Many projects require using dynamic data found on the Internet (i.e., retail price comparison of competitors or stock quotes). By "scraping" the source sites, you can easily capture thousands of records, and then query and arrange those records into a desired format. Best of all, the entire process can be automated once set up.

By using the tool and techniques presented in this class, the student will be able to scrape and format large amounts of data from public websites using only a standard version of Microsoft Excel. Our easy to learn methods require no additional software.

Prerequisites

Microsoft® Office Excel 2003 Introduction to VBA or equivalent knowledge

Outline

CHAPTER 1 - EXCEL 2003 IMPORT WEB QUERY

• Introduce Web Import Concept
• Introduce the 2 Class Projects
• Introduce Import External Data Feature
• Import Stock Market Quotes From Excel
• Import From The Web Page
• REVIEW LAB: Import New Horizons Kansas City Course Schedule

CHAPTER 2 - IMPORT DATA FROM THE “WEB”

• Plan The Import
• Import The Data
• Move The Data
• Plan The Format
• Calculate
• Save as a Query
• REVIEW LAB: Mapping Bid Project.xls

CHAPTER 3 - FORMAT AND CALCULATE IMPORTED DATA

• Change Font of Entire Document
• Add Rows And Column
• Format Headers
• Sort
• Calculate
• REVIEW LAB: Mapping Bid Project

CHAPTER 4 - ORGANIZE IMPORTED DATA

• Move One Sheet
• Move Multiple Sheets
• Move and Break Link
• REVIEW LAB: Mapping Bid Project

CHAPTER 5 - ADVANCED FUNCTIONS

• ISError Function
• IF Function
• REVIEW LAB: Mapping Bid Project

CHAPTER 6 - VBA INTRO – AUTOMATE IMPORT TASKS

• Create A Macro
• Change Font and Size Macro
• Add Rows and Columns Macro
• Edit a Macro
• Format Headers Macro
• Sort Macro
• Calculation Macro
• Auto-Fit Columns Macro
• Create A Master Macro
• REVIEW LAB: Mapping Bid Project

CHAPTER 7 - SHARE IMPORTED DATA WITH ACCESS

• Set Up the Access Database
• Setup the Share of Imported Data With Access
• Copy Data To Access
• REVIEW LAB: Mapping Bid Project

CHAPTER 8 - VBA IN EXCEL FOR AUTOMATION TO ACCESS

• Review Some Key Terms of VBA
• Create the Code in Excel
• Test The Code
• Edit the Code
• REVIEW LAB: Mapping Bid Project

CHAPTER 9 - VBA IN ACCESS FOR AUTOMATION TO EXCEL

• Create a Form in Access
• Create The Code
• Test Code – Send Data To Excel
• REVIEW LAB: Mapping Bid Project

Date

August 14, 2008

Cost

The cost for this one day class is $275.

Enrollment in this course is limited. If you would like more information regarding this course or would like to enroll, please contact us today.