|
PureBytes Links
Trading Reference Links
|
If working your way with Excel you should have a look at this page.
Best regards.
G.G.
http://home.earthlink.net/~jfritch/
--
--End--
Old telephone books make ideal personal address books. Simply
cross out the names and addresses of people you don't know. ;-)
<x-html><HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="Generator" CONTENT="Microsoft Word 97">
<TITLE>Technical Analysis of Stock Data with Excel Visual Basic</TITLE>
<META NAME="Template" CONTENT="C:\Program Files\Microsoft Office\Templates\Web Pages\Web Page Wizard.wiz">
<META name="description" content="Technical analysis of stock data with Excel Visual Basic to calculate indicator values, charts, and trading signals.">
<META name="keywords" content="technical analysis, indicators, stock data, securities, stock trading, trading signals, excel, visual basic, exponential, moving average, ema, bollinger bands, standard deviation, least squares, parabolic stop and reverse, psar, trendlines, trend line, convergence, divergence, macd, relative strength index, rsi, stochastics, directional movement, adx, average true range, atr, commodity channel index, cci, on balance volume, obv, chaikin, cumulative money flow, cmf, rate of change, roc, beta, quotes plus, open, high, low, close, volume, charts, candlesticks">
</HEAD>
<BODY TEXT="#000000" LINK="#0000ff" VLINK="#800080" BGCOLOR="#ffcc99">
<B><FONT SIZE=4><P ALIGN="CENTER">Technical Analysis of Stock Data with Excel Visual Basic</P>
</B></FONT><P>If you presently do any type of programming (e.g., scan writing) to evaluate securities or trading strategies, you might consider doing such programming in a platform that gives you total control and is almost certain to have continued support with future versions providing full backward compatibility to earlier versions. I am not aware of any technical analysis platform that provides either the flexibility or the likelihood of continued support that Microsoft Excel Visual Basic does. </P>
<P>Stock data or software vendors can be here today and gone tomorrow or can undergo changes sufficient to warrant termination of the use of their products. Consequently, one should be careful about making any significant investment of method development, time, or emotion in a particular vendor's product. Such investment could hold one in bondage to a vendor when any objective outside observer could plainly see it's time to cut one's losses and leave. With Excel Visual Basic as a platform for technical analysis, you will not be held hostage to any stock data or software vendor's proprietary, peculiar, unpredictable, or cryptic scan language or data format. </P>
<B><U><P>The Excel 97 workbook GM.xls</B></U> is an output file from an Excel Visual Basic program and conveys some idea of what Excel Visual Basic can do in the way of technical analysis. If you agree with the "Terms of Use" below, examine GM.xls by downloading the corresponding self-extracting file (GM.exe, 638 KB). GM was selected arbitrarily to demonstrate calculation of the most common technical indicators as well as charting of Bollinger bands and trades based on parabolic stop and reverse. Double-click on the downloaded GM.exe from Windows Explorer and then open GM.xls with Excel 97. When you open it, you might be prompted with a dialog box warning that the workbook contains macros which could be virus infested. Click "Enable Macros" to continue. If you are running good anti virus software, you might want to select the option of not having this warning repeated. After you have opened GM.xls, read the "Read Me" worksheet for further instructions and information.</P>
<B><U><P>The Excel Visual Basic program</B></U> in Excel 97 workbook TA.xls:</P>
<UL>
<LI>Identifies stocks satisfying specified technical indicator criteria and </LI>
<LI>Tabulates and charts indicator values and prices over a specified time period for specified individual stocks.</LI></UL>
<B><U><P>The Visual Basic Code in TA.xls</B></U> is completely read and write accessible. If you agree with the "Terms of Use" below, get TA.xls by downloading the corresponding self-extracting file (TA.htm (the corresponding html translation readable by web browsers), both of which are also included in the self-extracting file TA.exe. Just double-click on the downloaded TA.exe from Windows Explorer to unzip these three files, which will be placed in a subdirectory folder "Programs" automatically created in a directory folder "Visual Basic for TA" automatically created in the same folder from which TA.exe underwent self-extraction. (If these directory folders and files already exist, you will be prompted from an MS-DOS window for permission to overwrite the files.)</P>
<P><B>TA.xls Versions and Bugs</B></P>
<B><U><P>Stock Data Providers</U>.</B> I believe standards for this service.</P>
<B><U><P>I can be reached</B></U> by e-mail at http://www.onelist.com/. </P>
<B><U><P>Terms of Use</U>.</B> Individuals may use any of the materials provided here for their own personal purposes without obligation. But any use is totally at their own risk. No warrantee, express or implied, is made on anything contained, provided, or referred to herein. Any use for commercial purposes or redistribution in whole or part (including but not limited to posting on a web site) is prohibited without express prior written consent of the author, John R. Fritch. Such express written consent is hereby granted only for redistribution in whole or part from one member to another member of the "Quotes-Plus" ONElist mailing list. </P>
<P>All contents of this web site and all materials provided therefrom, including but not limited to the Excel workbooks and programming code therein, are copyright © 1998-1999 John R. Fritch, 4334 Five Points Road, Corpus Christi, TX 78410, or by other author expressly specified in a different copyright notice. All rights reserved. </P>
<table align="left">
<td align="left" bgcolor="#ffcc99" width=120>
<img src="http://home.earthlink.net/cgi-bin/counter.pl?";>
</td>
</tr>
</table>
</center>
</P></BODY>
</HTML>
</x-html>From ???@??? Fri Mar 19 08:14:26 1999
Received: from listserv.equis.com (204.246.137.2)
by mail02.rapidsite.net (RS ver 1.0.2) with SMTP id 21901
for <neal@xxxxxxxxxxxxx>; Fri, 19 Mar 1999 11:11:59 -0500 (EST)
Received: (from majordom@xxxxxxxxx)
by listserv.equis.com (8.8.7/8.8.7) id XAA05910
for metastock-outgoing; Fri, 19 Mar 1999 23:16:30 -0700
X-Authentication-Warning: listserv.equis.com: majordom set sender to owner-metastock@xxxxxxxxxxxxx using -f
Received: from freeze.metastock.com (freeze.metastock.com [204.246.137.5])
by listserv.equis.com (8.8.7/8.8.7) with ESMTP id XAA05907
for <metastock@xxxxxxxxxxxxxxxxxx>; Fri, 19 Mar 1999 23:16:27 -0700
Received: from darius.concentric.net (darius.concentric.net [207.155.198.79])
by freeze.metastock.com (8.8.5/8.8.5) with ESMTP id IAA09119
for <metastock@xxxxxxxxxxxxx>; Fri, 19 Mar 1999 08:15:06 -0700 (MST)
Received: from newman.concentric.net (newman [207.155.198.71])
by darius.concentric.net (8.9.1a/(98/12/15 5.12))
id KAA13239; Fri, 19 Mar 1999 10:03:34 -0500 (EST)
[1-800-745-2747 The Concentric Network]
Received: from [206.173.232.104] (ts027d44.sjc-ca.concentric.net [206.173.232.104])
by newman.concentric.net (8.9.1a)
id KAA03212; Fri, 19 Mar 1999 10:03:30 -0500 (EST)
X-Sender: jehardt@xxxxxxxxxxxxxxxxxxx
Message-Id: <v04003a02b3180daaba4a@[206.173.232.104]>
In-Reply-To: <01BE7180.BD47BB60.ay286@xxxxxxxx>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Date: Fri, 19 Mar 1999 07:02:25 -0800
To: <metastock@xxxxxxxxxxxxx>
From: Joseph Ehardt <jehardt@xxxxxxxxxxxxxx>
Subject: RE: NYSE Composite with New Volume Indicator
Sender: owner-metastock@xxxxxxxxxxxxx
Precedence: bulk
Reply-To: metastock@xxxxxxxxxxxxx
X-Loop-Detect: 1
X-UIDL: 5947c8a401e3c104348e526f8f3b507f.07
John Sellers wrote:
Since the NYSE Composite Index represents all the companies listed on the
exchange, the official NYSE volume data seems like a good match with that
index. And you are right, they do publish the advancing, declining, and
unchanged volume data for the entire exchange, but it might be only the OBV
(On Balance Volume) of each equity accumulated together, and if that is the
case (I have been browsing the NYSE Exchange site to see if that is the
case without obtaining an answer), it would misrepresent what investor
money is actually doing. So I made an attempt to perceive volume in a more
meaningful way. In another message about to be posted, I have attached two
charts comparing the official exchange data and my allocated volume data to
show the differences.
Volume data for the S&P 500 is not published, and I'm not up to the task of
nightly compiling the volume of each of the 500 equities into a total
volume figure for that index, which I could then use in my cumulative
allocated volume algorithm. Surrogates might be used, such as the volume
for the S&P Futures contracts or for Spyders (tradable entity, S&P500
Depositary Receipts), but these also are independent items that can be used
as hedging instruments and the like, so volume in a contract or equity
would not be valid to reflect activity in the index itself. For that,
component volume is needed, and it requires too much work.
At the same time, the idea of CAV could be applied directly to the trading
volume of the mentioned entities. That is, I could use price and volume
data for Spyders, or I could use price and volume data for a particular
futures contract, and from that data make the appropriate chart of each.
And as far as what kind of weighting to use for an index, I would probably
favor capitalization weighted index, because it correlates to what money is
actually doing -- 100 shares of Intel are worth a lot more that 100 shares
of National Semiconductor, and for me being able to translate price *
volume into money would be important. How is money behaving? Is it going
into an instrument or being removed from it?
Joe
John wrote:
>Sounds interesting do you use the total NY volume as opposed to volume for
>the Composite which as far as I know is not available. Also I suppose one
>could use the advances and declines for the NY total to approximate that of
>the Composite.
>
>Another approximation would be to generate the S&P index with volume. But
>what type of index calculations would you suggest to be the better. I know
>of five types within my IRL software and they are Average Price Index,
>Price Weighted Index, Market Weighted Index, Equal Dollar Weighted Index
>and Portfolio Weighted Index. Just guessing I would say the fourth one may
>be better. The volume for the index I believe is calculated by summing the
>individual volumes of each stock included within an index. The prices are
>calculated by different formulas for each of the indices. As the titles
>imply the weighting is in general is accomplished to some extent in each of
>their own formulas.
>
|