Tutorial 11 Connecting to External Data Microsoft Excel

Tutorial 11: Connecting to External Data Microsoft ® Excel® 2013 Enhanced

Objectives XP • Import data from a text file • Work with connections and external data ranges • Define a trusted location • Understand databases and queries • Use the Query Wizard to import data from several tables • Edit a query © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 2

Objectives XP • Import tables from Access for use with a Pivot. Table • Manage relationships involving multiple tables • Create a Web query • Retrieve financial data using the WEBSERVICE function • Access data from an XML document • Work with XML data maps © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 3

Visual Overview: Retrieving Text Data © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 4

Visual Overview: Retrieving Text Data © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 5

Exploring External Data Sources XP • Data that is stored in locations other than the Excel workbook are known as data sources • Data source files can be stored in a variety of formats, for example: – Historical data, long-term trends (text files) – Current trend information (databases) – Up-to-the-minute data (the Internet) © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 6

Exploring External Data Sources © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 7

Importing Data from Text Files XP • Text files – Simple, widely used format for storing raw, unformatted data (text and numbers) – Useful for sharing data across software programs and computer systems • Understanding Text File Formats – Use a delimiter (space, comma, or tab) to separate columns of data – Use fixed-width text file to start each column at the same location © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 8

Importing Data from Text Files © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 9

Importing Data from Text Files XP • Starting the Text Import Wizard – Determines if data is fixed-width or delimited format and, if delimited, what delimiter is used – Steps of Text Import Wizard • On the DATA tab, click From Text in the Get External Data group • In the Import Text File dialog box, select the file and click Import; the Text Import Wizard – Step 1 of 3 opens © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 10

Importing Data from Text Files © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 11

Importing Data from Text Files XP • Specifying the Starting Row – Can specify the file type: • Delimited – characters separate each field • Fixed-width – fields are aligned in columns with spaces between each field – Can specify starting row other than first row – Indicate it data has headers – Click Next button to go to Text Import Wizard – Step 2 of 3 © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 12

Importing Data from Text Files © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 13

Importing Data from Text Files XP • Editing Column Breaks – Text Import Wizard needs to know where each column begins and ends – The point at which one column ends and another begins is the column break • Delimited format: delimiter determines column breaks • Fixed-width format: wizard guesses locations of column breaks © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 14

Importing Data from Text Files © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 15

Importing Data from Text Files XP • Formatting and Trimming Incoming Data – In Step 3 of 3, format the data in each column – General format is applied by default – To specify a format: • Select a column in the Data preview box • Click the appropriate option button in the Column Data format section • Indicate if a column should not be imported © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 16

Importing Data from Text Files © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 17

Importing Data from Text Files XP • Click Finish to import the selected data • Data can then be used to create a chart © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 18

Exploring External Data Ranges and Connections XP • When data is imported into a worksheet, it is stored within an external data range – Each external data range is given a defined name based on the data source filename – Assigned name can be edited • Excel can update (refresh) data ranges and connections manually or automatically • Importing data creates a connection between the workbook and the text file © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 19

Exploring External Data Ranges and Connections © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 20

Exploring External Data Ranges and Connections XP • Excel supports other properties for external data ranges, which include: – Keep the contents of the external data range current by reimporting or refreshing the data – Require the user to enter a password before data is refreshed – Define whether the refreshed external data range retains or replaces the formatting and layout – Define whether Excel inserts or overwrites cells when new rows are added to the data range © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 21

Exploring External Data Ranges and Connections XP • Viewing a Data Connection – A data connection is like a pipeline from a data source to one or more locations in the workbook – The same data connection can link multiple data ranges, Pivot. Tables, or Pivot. Charts – When you import external content Excel establishes a connection between a data range and that data source © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 22

Exploring External Data Ranges and Connections XP • Viewing a Data Connection (con’t. ) – To see where a data connection is being used, you can view that connection’s properties © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 23

Exploring External Data Ranges and Connections XP • Modifying Data Connection Properties – You can modify data connection properties © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 24

Defining a Trusted Location XP • Once a trusted location is defined, Excel will access the connection to the data source without prompting for confirmation that the connection is secure © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 25

Visual Overview: Excel Databases and Queries © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 26

Visual Overview: Excel Databases and Queries © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 27

An Introduction to Databases XP • A database is a structured collection of data • Databases are commonly used as the data sources for Excel workbooks • A database is divided into separate tables • Each table is arranged in columns and rows; also referred to as fields and records – A field stores information about a specific characteristic of a person, place, or thing – A record is a collection of fields © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 28

An Introduction to Databases XP • Excel can retrieve data directly from most database programs © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 29

An Introduction to Databases XP • Different tables are connected through database relationships; fields common to each table are used to match records in different table • A one-to-one relationship is one in which one record in a table is matched to exactly one record from a second table • A one-to-many relationship is one in which one record is matched to one or more records in a second table • Relational databases are ones in which tables can be joined through the use of common fields © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 30

An Introduction to Databases © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 31

Retrieving Data with Microsoft Query XP • Create a query to extract specific information from a database • A query contains a set of criteria that specify what values to retrieve and in what order • Queries are written in a language called SQL or Structured Query Language • Microsoft Query uses a wizard containing a collection of dialog boxes that guide you through the entire query process © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 32

Retrieving Data with Microsoft Query XP • To start Microsoft Query Wizard: – Click the From Other Sources button in the Get External Data group on the DATA tab – Click From Microsoft Query © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 33

Retrieving Data with Microsoft Query XP • Connecting to a Data Source – There are several types of data sources from which you can retrieve data – You can define your own data source by clicking <New Data Source> in the list of databases – Once connected to the data source, the Query Wizard – Choose Columns dialog box opens © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 34

Retrieving Data with Microsoft Query © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 35

Retrieving Data with Microsoft Query XP • Choosing Tables and Fields – Choose table and fields to include in the query © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 36

Retrieving Data with Microsoft Query XP • Filtering and Sorting Data – Determine whether to retrieve all records or to filter data to retrieve only records that satisfy particular criteria © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 37

Retrieving Data with Microsoft Query XP • Filtering and Sorting Data (con’t) • Specify how to sort the data by values in fields © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 38

Retrieving Data with Microsoft Query XP • The Query Wizard – Finish dialog box provides three options: – Return (import) the data into the Excel workbook – Display the results of the query in Microsoft Query – Save the query to a file © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 39

Retrieving Data with Microsoft Query XP • Editing a Query – Add new columns to a worksheet – Change the sort order options – Revise any filters – Edit a query by editing the definition of the connection; Query Wizard will restart – Modify the query definition as you walk through the steps of the wizard © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 40

Importing Data from Multiple Tables into a Pivot. Table XP • Import multiple tables from a database directly into Excel without using Microsoft Query • If tables are related through a common field, Excel will automatically include the relationship • The entire table will be imported because the query is not defined © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 41

Importing Data from Multiple Tables into a Pivot. Table © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 42

Importing Data from Multiple Table Into a Pivot. Table XP • To import a table: – Click the From Access button in the Get External Data group on the DATA tab – Select from the Select Data Source dialog box © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 43

Importing Data from Multiple Table Into a Pivot. Table XP • Set up and format the Pivot. Table and slicer © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 44

Exploring the Data Model and Power. Pivot XP • The data model is a database built into Excel that provides database tools • Data model database contents are immediately available to Pivot. Tables, Pivot. Charts, and other Excel features • The data model is constructed from different tables related by common fields • Installing the Power. Pivot Add-In – Interact with the data model using Power. Pivot – an add-in for Excel 2013 that provides tools for performing advanced data analysis and modeling © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 45

Exploring the Data Model and Power. Pivot XP • Installing the Power. Pivot Add-In (con’t) – With Power. Pivot, you can: • Apply filters to tables stored in the data model • Rename tables and fields within the data model • Define and manage the relationships among data tables joined by common fields • Format data values that will be used in Pivot. Tables and other Excel reports • Create calculated fields based on data fields from multiple data sources • Create advanced data structures and models © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 46

Exploring the Data Model and Power. Pivot XP • Adding a Table to the Data Model – The POWERPIVOT tab contains the commands from working with the data model – Use the Add to Data Model command to add Excel tables to the data model – Power. Pivot displays the contents of the data model in a separate window from the Excel workbook window © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 47

Exploring the Data Model and Power. Pivot © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 48

Exploring the Data Model and Power. Pivot XP • Viewing the Data Model – View the contents of a data model in Data view and in Diagram view – Data view shows the contents of each database table in the data model on a separate tab – Diagram view shows each table as an icon and relationships between tables are indicated by connecting arrows – To see which field joins two tables, click the arrow connecting the tables © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 49

Exploring the Data Model and Power. Pivot © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 50

Exploring the Data Model and Power. Pivot XP • Managing Relationships in the Data Model – To match records of one table to that of other tables, create a relationship between tables – When creating a relationship, identify a field in each table that will be used to match records © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 51

Visual Overview: Web and XML Connections © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 52

Visual Overview: Web and XML Connections © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 53

Creating a Web Query XP • To keep an Excel workbook in sync with data store on a website, create a query that retrieves data from a webpage • To create this web query, you need the URL of the page you are accessing – If the file is stored on a web server, it follows the format: http: //server/path/filename – If the file is stored locally, it follows the format: file: ///drive: /path/filename © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 54

Creating a Web Query XP • To open the New Web Query dialog box, click the From Web button in the Get External Data group on the DATA tab © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 55

Creating a Web Query XP • Formatting a Web Query – Can format text imported from the web query – Formatting is preserved when Excel refreshes data – Can also retrieve the webpage data and the styles • None imports text, not formatting • Rich Text Format (RTF) allows for text formatting styles but not advanced features • Full HTML retrieves all simple as well as advanced HTML formatting features © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 56

Creating a Web Query © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 57

Creating a Web Query XP • Saving a Web Query – Can save a web query to a permanent file that you share among other Office documents – The saved connection file can be loaded in any Office program © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 58

Retrieving Live Stock Quotes from the Internet. XP • Excel can be used with websites that provide stock market data – Yahoo Finance is free • Excel can retrieve data from these kinds of web services uses the following function: – WEBSERVICE(url) © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 59

Retrieving Live Stock Quotes from the Internet. XP • Stock values retrieved through the WEBSERVICE function do not act like web queries and are not treated as data connections. – They are not refreshed automatically when the workbook is reopened. © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 60

Retrieving Live Stock Quotes from the Internet. XP © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 61

Retrieving Live Stock Quotes from the Internet. XP • To enter the ticker symbols and column titles © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 62

Retrieving Live Stock Quotes from the Internet. XP • To enter the WEBSERVICE function © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 63

Retrieving Live Stock Quotes from the Internet. XP • The current stock quotes for all of the stocks listed in the Chalcedony Fund © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 64

Retrieving Data Using Office Apps. XP • Excel can be used with Office apps, which are small, specialized programs that enhance the features of Excel, Word, Power. Point, and other programs in the Office suite • Office apps can retrieve articles from online encyclopedias, insert maps, and retrieve timely stock quotes and financial information © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 65

Retrieving Data Using Office Apps. XP • Inserting and Office App – Apps are downloaded from the Microsoft Office Store; most apps are free – To download and use Office apps, you need an account with Microsoft Office; the account is free, requiring only a username and password to set up © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 66

Importing Data from XML XP • XML is a language used to create structured documents using only text • Office files are saved in an XML format called the Office Open XML © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 67

Importing Data from XML XP • Each piece of data in an XMP document is contained within an element • Data is marked by an opening and closing tag • XML documents are structured like a tree in which elements are placed within one another, descending from a common root element • The structure of the document is displayed in a data map © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 68

Importing Data from XML XP • Under this structure, the root element, document, contains six elements named title, subtitle, date, author, filename, and notes. © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 69

Importing Data from XML XP • XML author can create rules specifying: – Which elements are required – What types of values are allowed within each element • Rules are stored in a second document called the schema – Not required in XML – Are useful in ensuring the integrity and validity of XML data © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 70

Importing Data from XML XP • Editing an XML File – Before importing data, you can edit it – The first step in connecting a workbook to an XML document is to generate the data map – After Excel has generated a data map, you can use it to place XML content anywhere within the workbook © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 71

Importing Data from XML XP • Generating an XML Data Map © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 72

Importing Data from XML XP • Loading an XML Data Map – Commands to access the data map of an XML document are part of Excel Developer tools – To work with an XML data map, you must show the DEVELOPER tab on the ribbon – If the XML document has a schema file attached to it, you can load a data map without actually importing the data into the Excel workbook © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 73

Importing Data from XML XP • Binding XML Elements to a Worksheet – Using XML as a data source, allows you to attach (bind) elements to specific cells in the workbook © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 74

Importing Data from XML XP • Importing XML Data – You can import XML data directly into worksheet cells – To retrieve XML data, refresh the connection to the data source – Excel automatically places data in the correct worksheet cells – Can automatically update XML data in a workbook to reflect changes to the source XML document © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 75

Importing Data from XML © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. XP 76

Using Real-Time Data Acquisition XP • Another data source: real-time data acquisition values from measuring devices and scientific equipment • Usually requires purchase of an add-in to manage communication between Excel and the measuring device • Excel provides the RTD function to facilitate real-time data acquisition • Can save hours of data entry time © 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 77