Over the years we have been asked for a way to set trailing stop alerts on
your stocks. It is a feature we always wanted to provide, but the software was
not ready for it. Now with Personal Stock Streamer 7 extensions, the system can
be written much easier and in a much more powerful and flexible way than would
have been possible before.
In general there are a number of techniques for setting trailing stops, but
for this example we will use the simplest stop loss strategy:
- At the moment the trailing stop is set, the high water mark is calculated as
the higher of the purchase price and the most recent trading price.
- When the ticker is updated, compare the current stock price to the high
water mark. If the stock price is higher, then adjust the high water mark
upward. If the stock price is lower by the set percentage (default 20%), raise
an alert.
Implementing more complicated trailing stop techniques is left as an exercise
for the reader. Of course we appreciate all contributions. :-)
In order to make the extension fairly efficient, we will use the custom
attribute support in the scripting object model to keep track of several values
that we need. This will prevent unnecessary recalculations.
- _TrailingStopMark attribute is the current high water mark.
- _TrailingStopPct attribute is the threshold percentage.
- _TrailingStopAlertAction attribute is the name of the alert action to run
when the ticker hits the threshold.
- _TrailingStopMemo attribute is a string that shows the stop mark and
percentage in one string. This is used as the visual feedback for display in
the Trailing Stop column.
It is important to note that we will use the new custom column support in
Personal Stock Streamer 7.1 to create the Trailing Stop column. This column is
created by the extension but is not enabled in the view by default, so if you
want to see whether a trailing stop is set for a ticker you will need to
manually enable this column.
As with previous extensions, this extension will repeat the familiar pattern
of having a handler class with several functions to handler the various events
from the application, followed by initialization code that runs when the
extension is loaded. During initialization we have to perform several
tasks:
- Create an instance of the handler class, and register it as the target for
the application events we want to respond to.
- Add two menu items to the Tools menu that allows users to set and clear
trailing stops for a ticker.
- Create a custom column for visual feedback so that users can see which
tickers have a trailing stop set.
The initialization code is fairly straightforward so there is no reason to
cover it in this article. Look at the source code for details.
The handler class is a little more complicated. When the user selects to set
a trailing stop, the handler must ask the user for some additional parameters
(such as the stop percentage and alert action). The only way to do this within
the script object model is to display an HTML form in a small window and handle
the form submit event in our handler to read those parameters back. This means
that the actual setting of the trailing stop can not be done in the menu
handler. The only real tricky part in this section is that in order to get the
form to post correctly back to our class, we must set the form method as "post"
and the target as "x". This little trick is necessary because of how our
particular HTML browser control works.
Set selectedTickers =
Application.ActiveDocument.Selection.Tickers
If (selectedTickers.Count > 0) Then
Set
wndManager = Application.GetObject("WindowManager")
Set wndBrowser =
wndManager.CreateBrowserWindow(300, 140, me)
...
form = "<body bgcolor=white><form name=form1
method=post target=" + Chr(34) + "x" + Chr(34) + ">"
...
form = form + "</form></body>"
wndBrowser.SetHTML(form)
wndBrowser.Title = "Set Trailing
Stop"
Note that when we create the browser window above, we set "me" as the handler
object. This means that when the user clicks the OK button to submit the form
we must handle the response in the OnFormSubmitted method, which is where we can
pull the parameters and set the trailing stops for the selected tickers.
public Function OnFormSubmitted ( form )
If
(form.Value("submit") = "OK") Then
SetMarks form.Value("pct"),
form.Value("act")
End If
...
The SetMarks function does most of the work of setting the trailing stops.
It iterates through the selected tickers, gets the default stop mark for each
one (which is automatically calculated from the last purchase price or last
trading price) and sets the properties. Since the high water mark is calculated
automatically, a future enhancement to this extension would be to allow the user
to override this value in case a different high water mark is desired.
The work of checking the high water mark is done in the OnTickerUpdated
method of the handler class. Since this method is called for every ticker that
is updated, the first thing we must do is check whether a trailing stop is set
for the ticker. Since the GetProperty call will fail if the custom attributes
have not been set for this ticker, we must make sure to set the error handling
appropriately so that our script will not fail.
dStopMark = 0
dStopPct = 0
On Error Resume
Next
dStopMark = CDbl(ticker.GetProperty("_TrailingStopMark"))
dStopPct =
CDbl(ticker.GetProperty("_TrailingStopPct"))
On Error Goto 0
At this point, if the dStopMark and dStopPct variables are non-zero, then a
trailing stop has been set for the ticker. Now what we need to do is get the
current price of the ticker and compare it to the high water mark. If it is
higher, we move the high water mark, and if it is lower then we calculate the
percentage difference and compare it to the threshold value. We generate the
alert only if the threshold has been reached.
dPrice = ticker.GetProperty("Price")
If
(dPrice > dStopMark) Then
dStopMark = dPrice
ticker.SetProperty "_TrailingStopMark", dStopMark
...
Else
dDiffPct = ((dStopMark - dPrice) / dStopMark) * 100
If (dDiffPct
>= dStopPct) Then
RunAlertAction
ticker.GetProperty("_TrailingStopAlertAction"), ticker
End If
End
If
The RunAlertAction method does the work of actually firing the alert, which
involves looking up the desired alert action and running it through the alert
manager.
Set manager =
Application.GetObject("AlertManager")
If Not manager Is Nothing
Then
For Each action in manager.Actions
If
action.GetProperty("Name") = name Then
action.Run2 ticker,
"Trailing Stop"
Exit For
End If
Next
End
If
Note that we use the new Run2 method of the alert action object, which allows
us to specify the attribute to which the alert is related and will cause the
little yellow alert bell to be displayed in the Trailing Stop column as visual
feedback that the trailing stop alert has fired.
The full source of the Trailing Stop extension is available here.
subscribe to this feed with an RSS reader.