If I only have time to demonstrate one feature in Excel, this is it - but only if it is between 9:20am, and 4:20pm EST. Excel has pre-designed queries that can create massive portfolios in less than 10 seconds. All you need is a connection to the Internet and some stock ticker symbols. Next select DATA - IMPORT EXTERNAL DATA – IMPORT DATA and walk through the wizard. In seconds, a complete up to date portfolio is displayed that is synchronized to the stock market’s changing stock prices. With each click of the refresh button, the stock prices change before your eyes. Just add columns for the number of shares you own, and the total value of those shares – and this beats picking numbers out of the newspaper. Here is an example:

To get data from a site on the World Wide Web as shown in this example, of course you must first have access to the Internet.

Next, on the Data menu, point to “Get External Data”, and then click “Run Web Query”. Select the Web query you want to run (a Web query has an .iqy file name extension.) Click “Get Data” and the “Returning External Data” dialog box is displayed. Next click “Properties” to specify whether you want to return only the data from tables in the HTML data source or all of the information on the Web page.

The Get External Data, Run Web Query menu is shown above. The screen below shows several web query options that are included in Excel.

Choosing the multiple stock quotes option allows you to indicate the ticker symbols for the stock prices you would like to down load, and then web query returns the desired data either in your current worksheet, or in a new worksheet – dependent upon your choice. The resulting stock data as retrieved from the Internet is shown:

As Excel runs the query, you can tell the query is running because Excel displays the spinning icon on the status bar. The next step is to add a column containing the number of shares owned, as wells as an additional column to computer the total value based on shares owned, as shown below.

Once you have created your portfolio, simply click the Refresh Data button on the external Data Toolbar shown below to see the current value of your Portfolio.

There are numerous options to help you extract exactly the data you want, for example the Web Query Options box, the Parameters Box, and External Data Properties Box (all three of which are shown below) displays these options.

.

There are several key options shown above, including the ability to tie your web query to ticker symbols entered into a particular Excel cell; the ability to preserve formatting, and the ability to fill formulas (such as our calculations for total value), as more data is extracted by Excel. This is a great feature and using this technology, you can extract data out of any ODBC compliant database directly into Excel. All you need is the ODBC (Open Database Connectivity) driver for the desired database loaded on your computer, and in almost all cases, this is a free downloaded driver.

If you want to give this a try yourself, you can download my example Excel 2002 file here:

http://www.accountingsoftwareadvisor.com/excel/web-webquery.xls

Please take ten seconds to rate this Article - Thank you.