If the numbers you need to crunch come from the Web, then you'll need an efficient way to get it into. There are several ways you can do this, and one method even allows for automatic updating, which means that the Excel worksheet will reflect the data changes and keep your small business database up-to-date. In this column, I'll show you these three ways to move data from the Web into an Excel spreadsheet without typing it yourself. Drag and Drop Web Data to Excel As goes, Excel is a great. It lets you analyze data very efficiently. Of course, the first step is to get the data into Excel, and the simplest way to move data from the Web to Excel is to copy and paste it. Select the Web data in your browser, copy it from the browser, and paste it into Excel.
Alternately you can try dragging and dropping the table from the browser into the worksheet. To drag-and-drop the data, you need to see both the browser window and the Excel sheet.
Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t. An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span).
In some cases, selecting a small amount of additional data above and below the table of data you want actually works better than trying to select the exact data in the Web page. Once you have the data in Excel, simply delete the unwanted extra cells or rows of data. Sometimes one method will work and the other will not - it's worth trying both in these circumstances. The benefit of copying and pasting data is that typically the data will appear inside Excel as it did on the Web. You may need to format it, but the data will be intact and useable. The downside is that the Excel worksheet data is static, and it will not update if the Web data changes; you will need to open the Web page again and copy the new, changed data.
Figure 1: You can copy data from the Web to an Excel worksheet or, in some cases, drag-and-drop it from a browser window into the worksheet. Get External Data The second method of grabbing data from the Web involves Excel's Get External Data feature. This method is less reliable because not all websites support it, but it will let you import live data, which means the data can be automatically updated on a regular basis.
This ensures that you always work with the most recent data. From inside Microsoft Excel, choose Data From Web, and then wait until the New Web Query dialog opens. This dialog contains a mini-browser, so click in the Address box and type the URL of the website you want to get data from. For example, to get the current U.S. Stock price, type When the website opens in the dialog box, you will see a number of small yellow and black arrow markers scattered around the page.
These yellow markers indicate selectable tables that you can add to Excel. To add a table, hold your mouse over the marker to see the outline of the table, and click to select it. A selected table will have a green marker in place of a yellow one. Figure 2: Select the tables you want to import into Excel from the website in the dialog box. When you have selected the tables that you want to import into Excel, click Import. When the Import Data dialog appears, click Properties.
You can now name this query and set some of its properties such as how often you want to refresh the data. To determine the refresh rate, change the value in the Refresh Every box. You can also specify what should happen if the number of rows in the data range changes upon refreshing the data. When you have configured your selections, click OK to close the External Data Range Properties dialog.
Next, click to set the top-left cell for the imported data. Note: make sure you have empty cells below and to the right of the top-left cell for the imported data. Click OK and wait as Excel imports the data from the website. Figure 3: Set the options for your query. Include a name and a time frame for updating its data.
The data will be updated on a regular basis according to the frequency that you selected for the update. Predefined Queries There is one final way to move Web data into Excel, and that is using a preset Web query. Here's how it works.
Click to view a new sheet and choose Data Existing Connections, and then click Browse for More. This will take you to the Office14 Queries folder where you will find some existing.iqy query files for various MSN Money Central sites relating to Investor Currency Rates, Investor Major Indices and Stock Quotes.
Select the Stock Quotes query and click Open. Select the top left cell for the imported data and click OK.
You will be prompted to enter the symbols of the stock to view, so do this and separate multiple symbols with commas. For example, type the following to view data for AOL, Google and News Corp: Aol, goog, nws Before you click OK, decide whether you always want to check these stocks or if you want the flexibility to enter any stock symbol.
If you always want to check just the named stock, enable the Use this value/reference for future refreshes checkbox, and you won't have to enter the stock symbols each time. Click OK to continue. The requested data will be imported and displayed in the worksheet. You set the properties for this query by right-clicking on the imported data area and choosing Data Range Properties. Here you can set an update frequency and other options. The.iqy files are preset query files that you use to extract data from websites. These simple text files contain three or more lines of instructions.
Some are shipped with Excel, and knowledgeable Web programmers can write their own. Figure 4: An.iqy Web query file is a simple text file containing details of the query.
The one pictured here fetches stock data from MSN Money Central. Depending on your needs and the configuration of each website, one of these methods should let you grab Web data to use in Excel without having to type it in yourself. Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, Do you have a comment or question about this article or other small business topics in general? Speak out in the. Join the discussion today!
By You can find data in many different places and bring it into Excel 2011 for Mac workbooks in whole, or as the result of a query in a query table. The tools on the External Data Sources group of the Data tab in the Office 2011 for Mac Ribbon facilitate importing and refreshing data from sources that are external to Excel. Refreshing a data table in your Excel workbook If your query table is linked to a data source, you can update the data in your workbook on demand so that it reflects the current state of the data source. On the Ribbon’s Data tab, go to the External Data Sources group and click Refresh button’s triangle to display a pop-up menu, where you can choose from the options.
Opening a CSV or other text file containing data The most common type of text file containing data is Comma Separated Values (.csv). Excel can open these straight away if you choose File→Open. If you have a text file that was saved using a character other than a comma as the column delimiter, you can open it by using the Text button on the Database tab of the Ribbon. Note that you need to be able to tell Excel which character was used as the delimiter when the file was saved.
Clicking the Text button opens a wizard that helps you establish the content boundaries of each column. Connecting to a database in Excel 2011 for Mac If you have a database you want to connect to so that you can build a query, you start by clicking the Database button. You need to take some preparatory steps before you can successfully use options in the External Data Sources group in the Data tab of the Ribbon. First, you need to install an ODBC driver on your computer.
(ODBC stands for Open Database Connectivity and is a standard that allows communication of data records between different database systems.). You must also add at least one data source to the ODBC manager before you can get data into Office. When you have a data source, you launch Microsoft Query, an Office application that visually builds the query for you and brings the results of your query into Excel. Importing HTML data On the Ribbon’s Data tab, go to the External Data Sources group and click HTML to display an Open dialog. From this dialog, you can open a Web page that you saved from a Web browser. Excel imports the data in the Web page.
Importing from FileMaker Pro On the Ribbon’s Data tab, go to the External Data Sources group and click the FileMaker button to display a Choose a Database dialog where you can choose a FileMaker Pro file to use as your data source.