EX5 - Spreadsheets and The Web
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:
Everything but the charts is stored instantly in Access.
- 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
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
true north usa LLC
225 East Stuart Avenue
Lake Wales, FL 33853-3712