Accounting Network News

By David Ringstrom, CPA

Many spreadsheet users often overlook a powerfully simple feature in Excel: the ability to automatically retrieve data from the Internet. In this article I'll use the Data ribbon in Excel 2007 to monitor an investment portfolio within Excel. I'll use the SUMIF function to match prices with holdings, and Conditional Formatting to highlight upward or downward movements in prices in green or red. I'll also provide instructions for Excel 2003 for those that haven't upgraded to the latest Office version.

Try it now: A free 60-day trial of Office 2007 is available for immediate download. You can instruct the installation program to leave your present version of Office intact so that you're free to work in either version.

Interactive Guide: Don't let the new user interface in Excel 2007 slow you down. Office Online offers a great tool that provides the Excel 2007 equivalent for every Excel 2003 menu command.

Create a Web Query in Excel 2007

Let's say that you own 100 shares each Microsoft, Intel, and eBay, and you'd like to track your portfolio in Excel. To do so, set up a blank workbook, and build the spreadsheet shown in Figure 1. Now follow these steps:

Figure 1: Use this sample portfolio to understand web queries.

Assign the name Portfolio to a blank worksheet. To do so, right-click on the worksheet name, and then type Portfolio and press Enter.
Assign the name Data to the second worksheet in the workbook (add an additional worksheet if necessary by choosing Insert, and then Insert Sheet from the Cells section of the Home ribbon).
Choose the Data ribbon, and then click From Web in the Get External Data section.
Enter this URL in the Address field, and then click Go:
http://moneycentral.msn.com/detail/market_quote
When the web page appears, enter these symbols in the Name or Symbol(s) field, and then click Go:
MSFT, INTC, EBAY

Be sure to type a comma and a space between each symbol.

View More: Double-click on the title bar of the New Web Query window to view the web page in full-screen mode. Double-click again to restore the default size that only covers a portion of your screen. You can also resize the window manually by dragging the right corner of the window.
Click the arrow icon adjacent to the Quotes section. The yellow arrow icon should change to a green checkbox, as shown in Figure 2.
Figure 2: The New Web Query dialog box enables you to link to specific tables.

Advanced Options: Click the Options button in the New Web Query dialog box to manage your query. I like to choose Full HTML Formatting, which in this case returns clickable links to additional information for the ticker symbols to my spreadsheet.
Click Import to display the Import Data dialog box.
Confirm that the location for your query is =$A$1, and then click Properties.
Select the Refresh Data when Opening the File option, and then click OK.
Click OK twice to complete your query. Your spreadsheet should look similar to Figure 3.
Figure 3: Web data as returned to an Excel spreadsheet.
Return to the Portfolio worksheet, and enter these formulas in the corresponding cells (remember to assign the name Data to the worksheet that contains the web query — as instructed in step 2 above):

C2: =SUMIF(Data!A:A, A2,Data!C:C)

D2: =SUMIF(Data!A:A, A2,Data!D:D)

E2: =B2*C2

F2: =D2*B2

G2: =F2/E2

SUMIF function: Many Excel users opt for VLOOKUP to return values from another table, such as the web query in this case. I prefer to use SUMIF instead — the formula in cell C2 looks at column A of the web query for a match on the ticker symbol in cell A2, and then returns the corresponding price from column C.
Use the Number section of the Home ribbon to format cells E2 and F2 as $ and G2 as a percentage.
Select cells C2:G2, and then double-click the Fill Handle in the lower-right hand corner of cell G2 to copy the formulas down through row 4.
Select cells E5 through G5, and then click AutoSum in the Editing section of the Home ribbon.
Click the arrow next to the Borders icon in the Font section of the Home ribbon, and then choose Top and Double Bottom Row to format the total lines.
Figure 4: The completed portfolio analysis with totals.
Select cells E2:G4 and then click Conditional Formatting, and then New Rule in the Styles section of the Home ribbon.
Choose use a Formula to Determine Which Cells to Format, and then enter this formula:
=$F2>=0

Absolute reference: The dollar sign is critical in the above formula — we're applying conditional formatting to three columns at once, but all need to refer to column F. You may not achieve the desired result if you omit the dollar sign in your formula.
Click the Format button, and then choose Bold and set the font color to green, and click OK twice.
Click Conditional Formatting, New Rule, and then Use a Formula To Determine Which Cells to Format. Enter this formula: =$F2<0 and then click the Format button and choose Bold and Red. Click OK twice.
Your stock portfolio spreadsheet is complete!

Change the Default Home Page for Web Queries

The New Web Query dialog box initially displays the default home page from Internet Explorer. Excel 2007 users familiar with the Windows Registry can override this default by adding this key as a String type to the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options\WebQueryHomePage

Assign the address (URL) of the desired default page as the value of this registry key, such as this page for currency exchange rates:

http://moneycentral.msn.com/investor/market/exchangerates.aspx

Excel 2003 and 2002 users can use the same key, but replace 12.0 with 11.0 and 10.0, respectively.

Create Web Queries in Excel 2003

You'll use a similar series of steps in Excel 2003 to create a web query:

Build the spreadsheet shown in Figure 1.
Assign the name Portfolio to the worksheet. To do so, right-click on the worksheet name, and then type Portfolio and press Enter.
Assign the name Data to the second worksheet in the workbook (add an additional worksheet if necessary by choosing Insert, and then Worksheet).
Choose Data, Import External Data, and then Get Web Query.
Enter this URL in the Address field, and then click Go:
http://moneycentral.msn.com/detail/market_quote
When the web page appears, enter these symbols in the Name or Symbol(s) field, and then click Go:
MSFT, INTC, EBAY

Be sure to type a comma and a space between each symbol.
Click the arrow icon adjacent to the Quotes section. The yellow arrow icon should change to a green checkbox, as shown in Figure 2.
Click Import to display the Import Data dialog box.
Confirm that the location for your query is =$A$1, and then click Properties.
Select the Refresh Data when Opening the File option, and then click OK.
Click OK twice to complete your query. Your spreadsheet should look similar to Figure 3.
Return to the Portfolio worksheet, and enter these formulas in the corresponding cells (remember to assign the name Data to the worksheet that contains the web query - as instructed in step 2 above):

C2: =SUMIF(Data!A:A, A2,Data!C:C)

D2: =SUMIF(Data!A:A, A2,Data!D:D)

E2: =B2*C2

F2: =D2*B2

G2: =F2/E2
Choose Format, Cells, and then use the Number tab to format cells E2 and F2 as Currency and G2 as a percentage with two decimal places.
Select cells C2:G2, and then double-click the Fill Handle in the lower-right hand corner of cell G2 to copy the formulas down through row 4.
Select cells E5 through G5, and then click AutoSum on the Standard toolbar.
Choose Format, Cells, and then Borders to set a single line border at the top and double-line at the bottom.
Select cells E2:G4, choose Format, and then Conditional Formatting.
Change Cell Value Is to Formula Value is for Condition 1, and then enter this formula:
=$F2>=0
Click the Format button, and then choose Bold and set the font color to green.
Click the Add button, and then change Cell Value Is to Formula Value Is for Condition 2. Enter this formula:
=$F2<0
and then click the Format button and choose Bold and Red.
Click OK, and your stock portfolio spreadsheet is complete!

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.

Excel 2007 vs. 2003: Using Web Queries to Monitor Investments