Importing Data Into Microsoft Excel 2007 From Databases and Web Sites

Spreadsheets contain many tools for analyzing and manipulating data. The trouble is much of the data CPAs need to analyze resides in company databases or on the Internet. So, after finding the required data, you may have to cut and paste them into a spreadsheet. This laborious and error-prone process is particularly troublesome if you are obliged to repeat it monthly or more frequently.

Wouldn’t the process be easier and more reliable if you could simply open a spreadsheet, which then would automatically pull in the latest data from a database, and perform your standard analysis? To meet that need, this article will demonstrate not only how to automate data retrieval from a database on your own PC, but also from a database on your company’s network server and from one residing on the Internet.

The following examples will use Excel 2007 and Windows commands. Earlier versions of Excel have similar data access features, but their commands differ greatly from those of Excel 2007. Note that the screenshots in this article are of maximized windows. The appearance of any of the illustrated screens will vary slightly if viewed through windows that are not maximized.

PULLING DATA FROM A LOCAL DATABASE

This process will work with any database stored on your own PC. To illustrate it, we’ll refer to a small Microsoft Access database on a server at Brigham Young University that we’ve made available to readers. Use a Web browser to download it by going to it-research.byu.edu/excel/SalesData.mdb. When you try to do this, one of two things will happen. Either you will be prompted to open or save the database’s files to your computer, or the database will open automatically in Access 2007. In either case, save a copy to your desktop.

If you do open the database, be sure to close it before proceeding. This example assumes the Access database is not open when you use Excel to import its data. Although it is possible to import data from an open Access database, the process differs slightly from the one this article describes.

Refer to Figure 1 as you perform the following steps:

1.a. Open Excel and click on the Data tab.

1.b. Click on From Access in the Get External Data group of icons in the upper left-hand portion of the screen.

1.c. Navigate to your desktop and double-click on the Sales- Data.mdb file. The SalesData.mdb database has only one table. If there were more than one table, you would be prompted to select the table you wish to import.

1.d.An Import Data screen will appear. Click on the OK button to bring the data into Excel.

It’s that easy to import data from a database into a table in your spreadsheet. The spreadsheet will look like Figure 2 except for the sales amounts in cells D2 and D3. As you’ll see below, it is easy to update the data in your spreadsheet to reflect any additions, deletions or changes to information in the database.

At this point, you can manipulate the data in the spreadsheet and perform any analysis you wish. You also can click on one of the dropdown arrows in row 1 to sort the information in the table by that particular column. Save this spreadsheet to your desktop, using the name Sales1.xlsx.

The Sales1 spreadsheet is a snapshot of the information stored in the Access database at the time you imported the data. Since company data are constantly changing, you periodically will need to update the data in the spreadsheet. To demonstrate how to update the Sales1 spreadsheet, we will modify the database data and refresh the data in the spreadsheet to show that the database changes are reflected in the spreadsheet. Follow these steps:

2.a. Close the Sales1 spreadsheet. Note that if you don’t close the spreadsheet before opening the database to modify its data, the database will open as a read-only file. That will prevent you from saving any changes you make to the data in the database.

2.b.Open the SalesData.mdb database.

2.c. Open the Sales table by double-clicking on it on the left of the Access screen.

2.d.Change the first two amounts in the Sales table to “2000” and “3000” from “20” and “30,” respectively.

2.e. Close the database.

2.f. Open the Sales1.xlsx spreadsheet.

2.g. Select the Data tab and click the Refresh All icon. See Figure 2. If you get a security warning because you are connecting to a database, simply click on OK.

As you can tell by looking at the Amount column, the data in the spreadsheet have been updated. You also may refresh the spreadsheet data by right-clicking on any cell in the spreadsheet data table and selecting Refresh on the popup menu. Note that if you modify the data in the spreadsheet, the information in the database will not change. Also, if you move the database to a new location, you will have to specify that new location when you are prompted to during the refresh process.

In addition, you can configure the spreadsheet to automatically obtain fresh data from the database every time you open the spreadsheet. Refer to Figure 3 as you do the following:

3.a. Select a cell in the range of the imported data in the spreadsheet.

3.b. Select the Data tab.

3.c. Click the arrow next to Refresh All and select Connection Properties. The Connection Properties window will open. (Figure 3)

3.d.Under Refresh control, check the box next to Refresh data when opening the file.

3.e. The next time you open the spreadsheet, you may need to click on Security Warning Options and select Enable this content.

From now on, whenever you open the spreadsheet, it will import fresh data from the database.

TAKE ONLY THE DATA YOU NEED

One of Excel’s more useful tools is the PivotTable, which enables you to summarize large amounts of data for easier analysis. Occasionally, you may want to analyze the data from a database table or query in a PivotTable without storing the unsummarized data in the spreadsheet. Refer to Figure 4 as you take the following actions:

4.a. Open a new spreadsheet.

4.b. Select the Insert tab, go to the Tables section, and click on PivotTable.

4.c. Select Use an external data source and click on Choose Connection.

4.d.Click on Browse for More at the bottom left of the Existing Connections popup screen.

4.e. Find the SalesData.mdb database on your desktop, open it by double-clicking on it, and select OK.

4.f. Create the PivotTable by checking the Sales Rep box and then the Amount box. Drag Location to Column Labels.

4.g. Save the spreadsheet as Sales2.

Remember that if the information in the database has changed, you will have to refresh it in Excel.

CONNECTING TO YOUR COMPANY’S DATABASE

Earlier we demonstrated how to connect to an Access database that is saved on your personal computer. You are likely the only user of that database. But you can also use Excel to import data from many types of databases, including those designed to support thousands of simultaneous users, thanks to Open Database Connectivity (ODBC), a protocol that most database management systems observe. To use ODBC with Excel, you must set up a connection file, which will enable Excel to download data from an ODBC-compliant database.

You can import data from your company’s database if its administrator provides you with the appropriate connection ports, user IDs and passwords. Usually, this is relatively simple and takes just a couple of minutes. Once you connect to the database, depending on the rights the database administrator has provided, you can query the database’s tables. Because you will export the database data to Excel and perform your data analysis and manipulation in your spreadsheet, your actions will not affect the data in the database.

To illustrate this, we will set up a sample ODBC Internet-based connection to a structured query language (SQL) server database. Refer to Figures 5 through 8 as you do the following:

5.a. Click on the Windows Start button, select Control Panel (use the Classic View), select Administrative Tools, and open Data Sources (ODBC). If you do not find Data Sources in Administrative Tools, consult your system administrator. You may be able to access the ODBC Data Source Administrator popup screen by opening a file, C:Windowssystem32odbcad32.exe, which brings up the ODBC Data Source Administrator screen shown in Figure 5.

5.b.Set up the connection by selecting the File DSN tab, clicking on Add (Figure 5), selecting the SQL Server driver, and clicking on Next. Note that you may need to scroll through many driver options before finding the SQL Server driver choice.

5.c. Save this connection with a name you would like to use for the data source. In the example, we used accountancy. Click on Next and then on Finish.

5.d.For Description, type in accountancy or any description you want. (Figure 6)

5.e. Enter 128.187.60.102 as the server. If you were connecting to a server with an Internet domain name, you could enter that Internet domain name instead of the network IP address of the server as described in this step. Click on Next.

5.f. Click on With SQL Server authentication using a login ID and password entered by the user. (See Figure 7.)

5.g. Enter accountancy as the login ID and demo as the password. Click on Client Configuration.

5.h. Uncheck Dynamically determine port, and identify the port as 5901. (See Figure 8.) Then click on OKand on Next.

5.i. Click on Change the default database to, enter websql, and click on Next and on Finish. You can click on Test Data Source to ensure you have successfully connected to the database. Finish by clicking on the next two OK buttons you see.

Now that you have set up a connection, you can import the database data into Excel. Do the following:

6.a. Open a new Excel spreadsheet, and save it with the name accountancy.

6.b.On the Data tab, click on Existing Connections from the Get External Data group of commands.

6.c. Under Connection files on this computer, double-click on accountancy (or whatever you named the connection in step 5.d). In some cases, the operating system configuration may make it necessary to browse for the accountancy.dsn file. Usually, it is located at C:ProgramFilesCommon FilesODBCData Sources.

6.d.Enter accountancy as the login ID and demo as the password, and click on OK.

6.e. Select the table or query that you want to connect to. (See Figure 9.) Try opening product, whose owner is Accountant. It should be near the top of the list. You may also refresh the data or open them directly into a PivotTable.

CAPTURING ONLINE DATA

Excel can also retrieve information from a Web page. First, create a new workbook in Excel, and save it as Fortune100. The first 100 companies in the Fortune 1000 list for 2008 can be found at money.cnn.com/magazines/fortune/fortune500/2008/full_list/.

Using the above address, you can import data from the Web directly into Excel. Refer to Figure 10 as you do the following:

7.a. Open the Fortune100 workbook you just created in Excel, and select the Data tab.

7.b. Click on From Web in the Get External Data group of icons.

7.c. In the Address field, type in the above Internet address, and click on Go.

7.d.Excel will display a copy of the target Web page. Notice the yellow arrows on the left side of the New Web Query window. Clicking on the yellow arrows identifies which portion(s) of the Web site will be downloaded into the spreadsheet. For those sections you choose to download from, a green check mark will replace the yellow arrow, and the data to be downloaded will be highlighted. Click on the yellow arrow nearest to the Rank and Company labels above Wal-Mart Stores, the first company listed. This will highlight and download company names, annual sales and profits.

7.e. Click on Import to begin the download. Click on OK to download the data into the current worksheet.

You can refresh these data by following the process described earlier.

Once you’ve experimented with these procedures, you’ll be comfortable trying them out in actual practice. And after you configure your spreadsheets to automatically download new data, you’ll be better able to focus on your primary objective: interpreting the latest data—wherever they originate.

Rayman D. Meservy, Ph.D., and Nicholas L. Ball, Ph.D., are assistant professors and Marshall B. Romney, CPA, Ph.D., CFE, is a full professor, all on the faculty of the Information Systems Department of Brigham Young University’s Marriott School of Management in Provo, Utah. Their e-mail addresses, respectively, are meservy@byu.edu, nick_ball@byu.edu and mbr@byu.edu.

AICPA RESOURCES

JofA article

For users of Excel 2003: “Click … and the Database Loads Into Excel,” Sept. 06, page 53

Conference

AICPA TECH+ Conference, June 14–17, Las Vegas

For more information or to register, go to www.cpa2biz.com or call the Institute at 888-777-7077.