Would you like to download financial, sales or stock market information from a Web site and then plug it directly into a spreadsheet on your computer? Better yet, would you like all the data refreshed automatically every time you open the spreadsheet file? Just a year or so ago, you would have had to go through a series of tedious steps to perform such downloads. But now, with the Web Query function in Excel XP, a built-in wizard will walk you through the entire setup.

Why, you may ask, would you want to do that? There are many reasons. Consider just a few of them.

• Up-to-date corporate financial statements or other financial data often can be found on a Web site, so anyone wishing to analyze that information in greater depth conveniently can download the data into an instantly updated spreadsheet.
• Employees who travel or work at home can access their companies’ Web pages loaded with data such as sales and financial and inventory information and download the fully formatted data directly into spreadsheet files for in-depth analysis.
• Suppliers and their customers that need to keep in close touch can access each other’s delivery, sales and inventory data as a way to synchronize records and the transfer of goods.
• Publicly held businesses can track the market value of securities they hold automatically. The posting of the securities’ market value is required under FASB Statement no. 115, Accounting for Certain Investments in Debt and Equity Securities. Tracking the information manually is tedious, but this computer tool can do it with a few mouse clicks.
• Individual investors can keep track of their portfolios by downloading the latest stock prices into spreadsheets for analysis.
• Currency exchange and money market rates, which are in constant flux, can be fed directly into formulas or charts and refreshed to keep spreadsheets updated automatically.

DYNAMIC DATA
What makes this Excel feature especially valuable is that it provides an opportunity to create a spreadsheet with dynamic rather than static data. An ordinary spreadsheet is static—that is, the information changes only if you open it and manually enter new data. Of course, if you “link” your spreadsheet to another file—another spreadsheet, a database or a word processor file—any changes in that linked file automatically will be reflected in your spreadsheet file, converting it into a dynamic file. Web Query links your computer files with a remote Web site that is continually updated.

Some businesses post their current financial statements on their home pages in an Excel format. Individual investors or security analysts can link to those sites, download the latest data into their own spreadsheets and analyze the information at leisure.

In addition, many companies post password-protected sales and inventory data on Web sites, linking traveling sales staff with the home office to keep all data synchronized. In fact, some businesses even provide access to such password-protected sites to suppliers and corporate customers so they, too, can synchronize their data.

HOW IT WORKS
Web “queries” can be embedded in an Excel template so the spreadsheet automatically pulls in external data. Excel includes sample queries that work without modification, but if you have special needs, you can modify a query easily.

The Trading Stock sheet can accommodate any number of stocks or mutual funds. Simply insert a row within the created range, copy the formulas and input the appropriate stock or mutual fund symbol.

To build this template, follow these steps:

• Let's make this slick! Create an Excel file with four worksheets.  Use separate worksheets for the worksheet description (description), the trading stock summary (trading stock), the stock prices (stock prices) and your chart (chart).  Use conditional formatting (green for gains, red for losses) in Trading Stock worksheet.  Also add some color to make the screen more user friendly [See my examples below].  Label the first worksheet Description and simply explain what this workbook is for and how it works. You want someone who is using this workbook for the first time to have a clear understanding on what the purpose of the workbook is.  Label then next worksheet Trading Stock and the other worksheet Stock Prices. You will need to move your chart to another worksheet labeled Chart.  On the Trading Stock sheet, type in the column headings and format the columns as indicated below and add the ticker symbols for the stocks you want to monitor starting in cell A2 (see example below). Pick three stocks that have your first initial as the first letter in the ticker symbol.  Pick three stocks that have your last name, first initial as the first letter of the stock symbol.  For exam, Joe Smith would select three stock ticker symbols starting with J and three stock ticker symbols starting with S. You can find the ticker symbols here for S&P 500 companies.  If your first or last name starts with a Z, use X,Y, or Z to pick your three stocks. Enter however many shares you would like.

  Then,

  • On the Stock Prices sheet, place your cursor in cell A1. Click on the Data button on the toolbar and click again on Existing Connections.   A menu of sample Web queries is displayed. Select MSN Money Central Investor Stock Quotes.
  • By default, the results of the Web Query will be placed in a range whose upper left corner is the cell you were in when you started the Import Data process.  Select cell A1 in the existing worksheet if not already selection, click OK.
  • The following box will pop up, click on the red arrow to define the location of the parameter values.
  •  
  • 
  • Now click on the Trading Stock page tab and highlight the range where the stock symbols are placed.
  • 
  • Press ENTER, select use this value/reference for future refreshes and then click OK.

   

• Current stock prices for the companies in the stock symbol column are now automatically pulled in, as shown below and the trading stock worksheet should be updated as well (your prices will be different as these are from a prior stock closing).  If you want to run your data query on a periodic basis, just go to the Ribbon bar and click data, Refresh all.
• Note that these stock price quotes are delayed 20 minutes for you day traders!

 

 

• 
• Now copy and paste the prior closing stock prices into your Purchase Price column in the Trading Stock worksheet.  You can do this by selecting the prices from the Stock Prices worksheet, select Home, then copy, move the cursor to cell C3 on the Trading Stock worksheet, then select Paste Special and then select Values.  This transfers the values and not the formulas.  This is a handy tool to know in future work with EXCEL. 
• 
• You will then need to add formulas in the Today's Price column to "carry" the value from the Stock Price Download Worksheet to column E.  You can use "=" or "+" to grab the value from the other worksheet, just like linking formulas within a worksheet.  This is an efficient way to transfer data between worksheets within your workbook file. Here you are retrieving a number from cell D4 on the Stock Price Download worksheet.  Just hit "=+" then click on cell D4 of the Stock Price Download worksheet and your formula should look like this.  Copy this formula down to pickup up the other stock prices.  This value MUST change as stock prices change.  DO NOT type it in. This is a dynamic update.
• 
• I'll leave it to you to figure out the formulas to generate the purchase cost, market value, and gain/loss values. Your Trading Stock worksheet should look like this (Add conditional formatting for gains / losses for your totals) when you are finished. 

Next, create a column chart to reflect the cost, value, and gain/loss on each stock.  Your chart should look something like this.  I'll let you figure out how to do this one.  The tricky part will be setting and naming the data ranges.  Remember to use Help if you have any questions before you for assistance. 

Now save the workbook. As it is saved, the query is automatically embedded into the file. The next time you wish to generate the investment report, just open the spreadsheet and refresh the data.  

It can’t get much easier—a dynamic link between a simple spreadsheet and a live Internet data source.

As you can see, there are many places to use this handy tool. Once you get started, you’ll probably find other uses to make your work more efficient and effective.

This is a great example of downloading data from an external database into Excel for analysis.  This could be from a general ledger system, a third party database, or any other source of decision useful data that is available. 

When completed, save the final version of the workbook as Lastname_Firstname_EXCEL_1 and e-mail before the beginning of class on the due date noted above.  Make sure you use the correct subject line.  You will be graded on the over presentation quality of your workbook and related calculations (30 points), graph (10 points), that the web query works (9 points), and having the correct subject line (1 point). Lastname_Firstname_Homework, or for John Doe it would be Doe_John_Excel_Project_1. 

I will update your stock prices when I grade this project to ensure your web queries and calculations are working.