HAL-PC Houston Excel SIG August 2004 Web Query; Pivot Tables; Stock Signals

Wednesday, August 11, 2004
Revised: 12-Aug-2004
WebQuery reprise, Database Queries, and Pivot Tables.

Info on Office 2003 Service Patch 1 - Information, Links, and Opinions
Windows XP Service Pack 2 - the major security upgrade (Revised 10-Aug)

6:30-7:00 pm	General Questions and Problems from the Audience
7:00 - 7:30	Review Using a Web Query. (Data, Import External Data, New Web Query) Last month, we retrieved data from barchart.com. This month we will use a website to gather historical stock prices so that we can check how well barcharts.com's top 100 lists performed against the market. We will use Yahoo's at http://finance.yahoo.com/q/hp? unless someone suggest a better one.
7:30 - 9:00 with a break at 8:00	In July's meeting we created a web query to fetch data from the barchart.com sigtop and sigbot sheets and to save the data into a database by retrieval date. That workbook is here: PastMtgs/0407/webQuery2.xls -- 257KB Excel 2003 (compatible with Excel 2000 and Excel XP) As of August 1, my capture of 4 web pages on several different dates is contained in this web query workbook. PastMtgs/0408/webQuery4.zip (197 KB zipped, Excel 2003) We will gather data from this web site over the next month. In the 7:30 to 9:00 session, we will analyze that month's of data and see if the SigTop (top Buy Signals) actually perform better than the market and whether the SigBot (top Sell Signals) perform worse than the market over the month of July. We will probably use Microsoft Query to combine the barchart signal tables and the historical prices. We will use Pivot tables, Charts, and some statistical functions.

News and Tips:

6-Aug. Future Meeting dates:

September 8, 2004 - Comparing Lists:

What is common? What exists in only one list? Making a union of lists.
Data Input Validation.
Woody's Technique 35: Building Self-Verifying Spreadsheets.
Woody's Technique 37: Ripping through Lists.

October 13, 2004 - We will need a substitute leader for this date.

5-Aug - Ideas for future meetings: (Please email me and tell me what you think)
Topics from Woody Leonard's Time-Saving Techniques for Office 2003.

Technique 34: Getting Excel Settings Right.
Technique 35: Building Self-Verifying Spreadsheets.
Technique 36: Freezing Columns and Rows.
Technique 37: Ripping through Lists.
Technique 38: Running Subtotals.
Technique 39: Creating Custom AutoFill Series.
Technique 40: Grabbing the Best with Pivot Tables.
Technique 41: Creating Pivot Charts That Work Right.
Technique 42: Setting Scenarios and Seeking Goals.
Technique 43: Using the Lookup Wizard.
Technique 63: Animating a Chart in PowerPoint.
Technique 67: Inserting Unformatted Text in Excel.
Technique 68: Printing a Bunch of Spreadsheets — Fast.
Technique 71: Creating Versatile Watermarks.

Probable topics for September:
Comparing Lists: What is common? What exists in only one list? Making a union of lists.
Data Input Validation.

New Links:

http://www.exceltip.com/ Joseph Rubin's Excel Tip Site.

http://www.vbaccelerator.com
"a site devoted to providing free, advanced source code to Visual Basic programmers since June 1998 and now also to the .NET and C# community."
and their site map: http://www.vbaccelerator.com/home/The_Site/Indexes/Site_Map/article.asp