Stock Backtester Question

[LakeDog]

Trying to do a stock backtester for excel. Have the data, the indices and even the entry and exit as well as the stop-loss in the spreadsheet. The issue I'm having is that the Exit and Stop-loss points, because the criteria extends for several rows, I get a value for multiple rows and I only need the first one. I've tried to add the IF statement that excludes if the cell prior is "0" but that only works for one cell, then the third one has the value again.

Looking for any options and thoughts.

Also, if there are any templates out there that I could learn from, would appreciate it.

Thanks!!!

 

[offthelip]

I use this simple macro which is very easy to use. It does multiple entry and exits and puts the profit in percentage terms in a column of your choice. It needs a column for entry triggers and a column for exit triggers ( TRUE/FALSE)
You just set the numbers in the macro to tie up with those. You set the start and end rows as well:
It assumes that the stock prices are in column B , however you can change this by changing the line indicated in the comments
Note I have also done this sort of macro which includes the stoploss and multiple exit terms and trailing stoploss, you can make it a sophisticated as you want.

Sub adaptema()
' Set all these numbers to the columns and rows you want 
buycol = 23
sellcol = 24
profitcol = 25
firstrow = 4
LastRow = 98
' ***********
bytrig = Range(Cells(firstrow, buycol), Cells(LastRow, buycol))
selltrig = Range(Cells(firstrow, sellcol), Cells(LastRow, sellcol))
Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = ""


Profit = Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol))
' closep are the close prices, change the column number to change this from Column B
closep = Range(Cells(firstrow, 2), Cells(LastRow, 2))
Longf = False
For I = 1 To (LastRow - firstrow)
 If Not (Longf) Then
   If bytrig(I, 1) Then
     buyp = closep(I, 1)
     Longf = True
   End If
  Else
'    If bytrig(i - 5, 1) Then
    
    If selltrig(I, 1) Then
     Profit(I, 1) = 100 * (closep(I, 1) - buyp) / buyp
     Longf = False
     End If
  End If
Next I
Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = Profit


End Sub

 

[LakeDog]

Thanks offthelip, I'm not so hot with macros, but part of this is to do it for fun and learn, so I'll dive in and give it a try. I'll see if I can interpret each line first and then apply it.

Your help and suggestion are greatly appreciated!!