Stop Loss -- Stocks

Location: http://www.mvps.org/dmcritchie/excel/stoploss.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

The question

Here is what I am trying to do. Each day enter the new closing price in column B for each company. As long as the closing price goes up, the stop loss price will also go up accordingly representing 90% of the closing price. The decision column would then read "HOLD". However if the closing price starts to decline, I don't want the stop loss price to decline accordingly, but rather to stay at whatever highest level it has reached UNTIL or IF the closing price reaches or goes below this highest level stop loss price. At that point, I want a "SELL" signal So basically, the stop loss price never will go down….only go up or stay the same as the previous day.

Example Spreadsheet

	A	B	C	D	E
1	COMPANY	CLOSING PRICE	BETA	STOP LOSS SELL PRICE	DECISION
2	CSCO	53.50	90%	48.15	hold
3	BBBY	36.06	90%	32.45	hold
4	xxxx	79.00	80%	63.20	hold
5	yyyy	108.00	80%	112.00	SELL

The coding

Don't know how fool proof this is, values should be entered one at a time. But I did attempt to make column C so you could use the fill-handle to change the percent, only because I got an error if this was done. If you paste values in you will probably have to use F2 then Enter. Here is the macro to try out.

The following is an EVENT macro.

Right-click on the sheetname, view code, and paste code in. You will want to format your columns.

If you are change your Beta your Stop Loss Prices will change current to the change of the Beta.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Then Exit Sub
  Dim entry As Range
  If Target.Column = 3 Then
    On Error Resume Next
    For Each entry In Intersect(Range("C:C"), Selection)
      entry.Offset(0, 1).Value = 1 * entry.Value * entry.Offset(0, -1).Value
      entry.Offset(0, 2).Value = "'--"  'changed percent
    Next entry
    Exit Sub
  End If
  If Target.Column <> 2 Then Exit Sub
  If IsNumeric(Target) = False Then Exit Sub
  If IsEmpty(Target.Offset(0, 1)) Then Target.Offset(0, 1).Value = 0.9
  If IsEmpty(Target.Offset(0, 2)) Then
     Target.Offset(0, 2).Value = Target.Offset(0, 1).Value * Target.Value
     Target.Offset(0, 3).Value = "'---"  'Just entered purchase
  End If
  If Target.Value < Target.Offset(0, 2).Value Then
     'can also be enhanced by Conditional Formatting
     Target.Offset(0, 3) = "SELL"
  End If
  If Target.Value * Target.Offset(0, 1).Value >= Target.Offset(0, 2).Value Then
     Target.Offset(0, 2) = Target.Offset(0, 1).Value * Target.Value
     Target.Offset(0, 3) = "hold"
    'Target.Offset(0,5) =  Target.value   'if you want to see high water mark
  End If
End Sub

Stocks page.

This page was introduced on May 19, 2002.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments

Stop Loss -- Stocks

The question

Example Spreadsheet

The coding

Related