Dark Area
Dark Area Dark Area
Dark Area
Home Technology About Us Examples FAQ Links Location Contact Us Change Header: Prev Next (9)
Dark Area
Dark Area
Dark Area

EX5 - Spreadsheets and The Web

Web Integration

Everyone today is aware of the massive software explosion associated with the worldwide web. Surprisingly, most professionals have no real daily experience with utilizing the WWW to capture and exchange data other than say, email.

A major portion of Microsofts current development in the MS Office Suite involves enhancement of Web interactivity. Starting with Excel 2000, "Web Queries" became available. A further significant advance was made in Excel 2002. Trying to extrapolate where all this will utimately wind up is almost unthinkable. The probability is that it will never settle or stabilize in our lifetime.

Engineering The Markets

One of our more interesting and rewarding projects in this area involves a retiring CEO of an engineering company in Florida. Being a distinguished electrical engineer himself and also responsible for the 401 K programs of his companies, this CEO decided a number of years ago to apply his engineering skills to the analysis of the stock market for the benefit of himself and the people that worked for him. Naturally, as an engineer, he turned to Excel.

He began to develop a stock selection model. As he progressed, the model and it's efficiency in filtering stocks grew. His ability to isolate high performing stocks even began to catch the attention of his brokerage house.

He currently filters over 10,000 stocks down to a list of about 300 using a subscription service. He then looks up about 100 vital statistics per stock. With this information, his Excel model reduces this group to sometimes less than four or five best picks.

The problem was that he ran up against a critical time barrier. The task became daunting. He was spending a tremendous amount of his available time copying down the information from the web and then transcribing it into Excel.

We developed a spreadsheet for him that exploits Excel's Web Query capability as well as Excel's enormous OLE interactivity. Excel now queries all the original information he needed. At last count, that list has swollen by at leat 400%. The information is all databased in Access immediately as it's queried. He has a complete and instantly available history of his work. Now this process takes several minutes rather than several days.

This is the navigation and control screen of his workbook.

All of the screen prints of this application shown below are displayed with his permission. This is a really super guy and a true genius!!!!

Dual Monitor Screen Print

He can either control each web query or let Excel do the entire thing for him including:
  • querying 4 different subscription sites on the web
  • downloading the current market indexes and news briefs
  • querying a list of 300 stocks with their current price, volume
  • querying a list of approximately 12 stocks showing abnormally high upside/downside volume
  • completing a complex query of approximately 100 different statistics on each of the 300 stocks
  • downloading from the web the most current daily and weekly charts on each of the 300 stocks
  • and much more
Everything but the charts is stored instantly in Access.

This is the sheet that queries the individual stocks for a long list of current statistics.

All the data queried in this application is is real time subject to the 20 minute time delay mandated by the SEC.

Dual Monitor Screen Print

Basically he can enter (or Excel can enter) any one of 10,000 stock call letters in a single cell on this sheet. Excel will then automatically query the data from the world wide web and instantly store it in his Access database with a date and time stamp. It will also will synchronize the display of weekly and/or daily charts, as well as hourly to 5 minute charts of the daily trading activity on the queried stock. With a push of a button Excel will make a copy of the chart and then catalogs the chart on a charts sheet in his workbook. His workbook typically contains screen prints of over 600 charts. A list box on this chart sheet allows him to quickly and automatically go to any of the cataloged charts.

He has several web browsers embedded directly in his Excel workbook that allows him to browse stock information with or without the assistance of Excel. Excel has an embedded plot control called "plotwon" which is an OLE ActiveX control that does the charting. The web browsers are embedded in non modal forms, (new with Excel 2002) which allows the chart forms to stay up and visible as he moves through the workbook and performs other activities (including automated ones).

Approximately 8 of the factors he uses are what we call "subjective factors ". This means that he arrives at these values by visually inspecting the chart patterns, volumes, moving averages, etc. Excel will now allow him to go down the list of stocks he tracks and will position the chart adjacent to a data entry area. He looks at the charts and assigns values to the stock. As he types (cell by cell), Excel follows behind him and writes each piece of data (along with a time stamp) to the database for safe keeping and future manipulation. This feature alone would have justified doing the project.

This person likes to retreat to a cabin deep in the Ocala National Forest on weekends,far from any internet connection. He does though, like carrying his laptop and reviewing his data and charts. Excel packages everything for him so he can do this unhindered and "uninterupted" !

Below is a masssive spreadsheet incorporating all the factors of his model.

Excel brings this information back from Access in a summarized and grouped fashion. Excel also allows him to use column and row filters to display the data in unique and manageable ways.

You are looking at it via a triple monitor desktop. It would actually require 12 monitors to see the whole sheet (3 wide X 4 high). How would you like to maintain this worksheet manually?

Triple Monitor Screen Print

One other interesting aside is this person, conducts his work on a docked laptop. His laptop display is set at a 800X600 resolution due to his poor eyesight. This means he can see only about 12% of what you see on our tri mon desktop above. Talk about looking through a keyhole!!! He recently asked us to recommend a "starter system" for him similar to our workstations. He will soon have a new Dell screamer with three 20.5" LCD flat panel displays. We can't wait to see where he wants to go from there!!!

The thing we like about this project is that not only did it save the professional time, it eliminated a critical and strategic time barrier that was keeping him from continuing to develop his market research. Thus the real benefit was not so much the time saved, but the expanded power and depth that Excel brought to the table once this barrier was removed. Also, Excel is collaborating effectively with someone of extreme intellect, wisdom, and creativity. This is truly an unbeatable combination. This represents the high ground of automation. This is definitely where we like to see things go.

He doesn't drink coffee now while Excel does the work (well maybe once in a while ..but it's a short break because Excel finishes quickly) More often than not he is now driving Excel and his new data environment relentlessly to gain a competitive edge against the general market.

Almost immediately after receiving this application, he began to ask for new data and new ways to report and filter the data. He has on several occasions since we started working with him, picked in advance the very top single stock performer for the week out of roughly a 10,000 stock population. From what we have seen ...it is paying off handsomely.

NO! .....at this time he does not want to market his spreadsheet, or give stock advice. He is quite happy keeping this all to himself.

Where To Go From Here....

Links To Other Sections Of This Site

Phone: 863.676.3248
Fax: 863.679.9254
Email: info@true-north-usa.com
true north usa LLC
225 East Stuart Avenue
Lake Wales, FL 33853-3712
Dark Area
Dark Area
...web design by F3 Computerized Solutions