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.exceladvisor.net/sub/creative/web-webquery.xls
- END -
