Houston Excel SIG - Sept 8, 2004

Our Next Excel Meeting is: Wednesday, September 8, 2004
News,Tips and Puzzlers: Revised: 07-Sept-2004 and Tips of the Month
Note: for our Meeting on October 13, we will need a volunteer to lead the SIG. Stephen Rasey will likely be unavailable.

Info on Office 2003 Service Patch 1 - Information, Links, and Opinions (Revised 1-Sept)
Windows XP Service Pack 2 - the major security upgrade (Revised 1-Sept.)
"Blue Screen of Death" seen on many of Intel's newest systems.

6:30-7:00 pm

General Questions and Problems from the Audience

Puzzler: How do I average the lowest 10 numbers from a range of 20? - Solution from Tom Oglivy on microsoft.public.excel.programming

7:00 - 8:00

In July's meeting we created an Excel workbook with a web query to fetch data from the barchart.com sigtop and sigbot sheets and to save the data into a spreadsheet database by retrieval date. (Download: webQuery4.zip 196KB)

In August, we create an Excel Workbook to fetch historical stock prices for hundreds of Stocks from yahoo.com. This included VBA code to automate the retrieval of hundreds of stocks. (DownLoad: YahooHistorical6.zip 32KB)

In September, we will combine those two workbook datasets to compare whether the barchart.com signal sheets performed better or worse than the general market.

The techniques we show include:

Importing Excel tables into Access

Make an Access Query to list for each signal (Ticker and date), the Stock Price Change for the next 21 days.

Create a PivotTable and Pivot Chart with the Query as the Data source. View in the Pivot the Signal Performance Averages and Standard Deviations over the following 21 days.

Set up the Pivot Chart to see the results.

Notes are now on the website.
The WebQuery5.xls and YahooHistorical.xls webquery workbooks are already posted. The Access Database and the Signals workbook can be zipped down to 3.5 MB. I will create an abbreviated version of the database and pivot table and post it later. If you want a full copy, come to the SIG meeting.

8:00

Break - Discuss Ideas for the One Hour App

8:10
at 9:00

Comparing two Lists in Excel

What is common? What exists in only one list?
Making a union of lists.
Data Input Validation.
If time permits: (and I do not think it will.)
Woody's Technique 35: Building Self-Verifying Spreadsheets.
Woody's Technique 37: Ripping through Lists.

• Puzzler: Avg Low 10 of 20 • Buy/Sell Signal Analysis • Buy/Sell Signal P.2 • Buy/Sell Signal P.3 • Tip Linked Validation •

News, Tips, and Puzzlers:

9-Sept

Rasey's Spotfire Hurricane Tracks as it applies to Hurricane Ivan as of 20040908

Case Study the WiserWays Spotfire Hurricane Historical Tracker. It was to predict whether 2002 Hurricane Lili was a danger to Houston.
Total time to prepare the data, database and Spotfire Application: 4 hours.
See October 2002 meeting for other notes.

7-Sept

Bob Umlas, Excel MVP, is leading a FREE 1-hour seminar on the web on Excel tips & tricks.
Tuesday, 9/21, from 3-4PM est. To sign up, go to http://www.iil.com, click on the orange box on the left side saying "try a free webinar", then click "Microsoft Excel 'Tips and Tricks'" then click the link to sign up. (From the Excel-L Developers mail list).

5-Sept

For the Sept 8 meeting: Notes on Combining the Signals recorded in web Query workbook with Historical Prices from Yahoo.
Excel Bug: You cannot change the Pivot Chart Data series colors and have them stay.

3-Sept

MSDN: How to Install Visual Studio Tools for the Microsoft Office System
BLOG: Peter Tor (MS) - Office Development, Security, and Randomness. http://weblogs.asp.net/ptorr/ (found on ms.public.vsnet.vstools.office 3-Sep-04)
PowerPoint: epic software group, inc. Ten Steps to Producing a Successful Multimedia CD-ROM Presentation.

1-Sept

More notes on Digital Certificates
Woody's Office Watch on Office 2003 Upgrade: upgrade Win XP SP2 first. Wait a few weeks for Office 2003 SP1 upgrade.

31-Aug

Notes on Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security

Access Jet Database Hotfix: Description of the Jet 4.0 Database Engine post-837001 hotfix package: July 21, 2004
If you are using Jet 4.0 and are experiencing problems in replication or in linked tables, read up on this hotfix.   If you are not experiencing problems MS asks you to wait for the service pack release for Jet 4.0.

Enter, Ctrl-Enter, Ctrl-Shift-Enter: Select several cells, say B2:B20.    In the active cell (B2), enter the formula =A2.
If you press the Enter key, only B2 gets the formula.
If you press Ctrl-Enter, you enter the relative reference:   B2 gets =A2, B3 gets =A3, ... B20 get =A20.
If you press Ctrl-Shift-Enter, you enter an array reference:    B2:B20 all get {=A2}

Ctrl-Enter will paste into only the visible cells selected in an autofiltered range.

Ctrl-Shift-Enter will paste the array into the entire selected range, including the hidden cells.

If you Select Visible Cells, Ctrl-Enter pastes into all selected cells, Ctrl-Shift-Enter pastes into only the first connected group of selected cells.

Link: Application Level Events http://www.cpearson.com/excel/AppEvent.htm
Link: Excel97 Events: http://www.cpearson.com/excel/events.htm

Puzzler: I have a table with a lot of blank cells in a column. What is the easiest way to fill the blanks with the last recorded value higher in the column?

Click for Solution

29-Aug

New Excel SIG Page: Tips Sept 2004

28-Aug

Links: Chris Kunicki: Random "Deep Thoughts" on Microsoft Office and related subjects
http://blogs.officezealot.com/chris/ (blog with 132 posts starting Nov 2003)
24-Aug blog on Comment Spam and a .NET tool .TEXT to successfully fight it.

List of Visual Studio Tools for Office Webcasts
New Excel SIG Page: Links to VSTO Examples and Information

26-Aug.

Puzzler: I have a workbook with many Pivot Tables or External Data Sources. What is the easiest way to update them?

Click for Solution

Tip: Working with two List Boxes (one for select from, one for selected)
From Dave Peterson: Selecting Multiple Items in Listbox microsoft.public.excel.programming

25-Aug.

Macro Tip: Auto_Open special name for a macro. You don’t need to do the Workbook_Open event. Auto_Open is not in the help. It is an legacy function from early excel versions. Auto_Close works, too.

Macro Tip: When doing a copy and Paste, try to use range.copy(rngDestination) so that you leave the clipboard available for another process. Unfortunately you have to use the clipboard for PasteSpecial.

Links:
Dirk Goldgar, http://www.datagnostics.com/ from microsoft.public.access.moduelsdaovba

Chip Pearson's Page on Excel Events: http://www.cpearson.com/excel/events.htm

24-Aug.

Windows XP SP2 news. - Will be released to Windows Update service on 25-Aug.   What to do if a program stops working under SP2.

Book: Brigham/Houston; Fundamentals of Financial Management: Concise, Third edition. - Excellent web site!
- - Example Excel Downloads -- Practice Quizes
Trying a FrontPage Table of Contents Page on the site.   A little buggy, but a net benefit helpful

23-Aug.

Microsoft Office Online Help: Property Definition Index

Create and run an SQL-Server Stored Procedure from an Access frontend

SQLServer Connection String Example:
Puzzler: How do I average the lowest 10 numbers from a range of 20?

Application.FileSearch: Advice: Stay away from it.   It appears not to work in Office 2000+ and later.   It is slow and always returns no files found. Several hits on newsgroups report similar trouble.
So HOW DO YOU use VBA to search for files using wildcards?    Nothing in FileSystemObject supports this.

22-Aug.

FileSystemObject - Notes and Help references for using the Windows File System inside of VBA.

StringToHex() - A function to convert a any length string of any set of Ascii characters (including non-printing characters) into a Hex representation.   Each character is converted to a pair of base16 characters (0,1,...9,A,B...F).
Book Review(Web): Excel VBA Programming for Dummies - dicks-blog.com
Houston alternative to MS Office: SimDesk (January 22, 2003)   Houston Public Library

18-Aug.

Access Tip: You can control the Query Plan in an Access Query by the order you select the relationships in the designer.

Tip: INDIRECT() worksheet function.   And its use with dynamic ranges.

14-Aug.

Where is Microsofts's Online VBA Excel Object Reference Documentation?
http://msdn.microsoft.com/library/default.asp

Then in the Hierarchal list, choose:

+Office Solutions Development

++Microsoft Office

+++Choose the version

++++VBA Language Reference

+++++ now choose the application and page type.

Thanks to Joe Rich

You can save a bookmark to this reference only be right clicking a page, choose Properties, and copy the URL.

http://msdn.microsoft.com/library/en-us/vbaxl11/html/xltocOMMap.asp?frame=true

Why Should I Learn How to Use the Excel Object Model?   [MSDN Technical Paper.   About 29 pages]

Understanding the Excel Object Model from a .NET Developer's Perspective (Ken Getz, MSDN April 2003)

13-Aug.

Using PowerPoint in Presenter View:   show presentation on projector, see speaker notes on laptop screen.
See Woody's Office Watch for Meer Mortals. #5.06 (not yet in the archive)

12-Aug.    Future Meeting dates:

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

    Tip: Linked Validation Pull down lists - Make the validation pull-down list for Tasks depend upon the Project selected in another pull down list.

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.

New Links:

http://www.dicks-blog.com/ "A Daily Dose of Excel" - A Blog from Dick Kusleika, Omaha NB.   A ?past? Microsoft MVP.

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