I found several Wrong characters and typos, including some invisible ones, which suggests that this code was copied from some place other tha a VBA Code Module
I made one modification and added a suggested "Wend". This code now compiles. Without seeing the source Data I cannot comment on its Logic.
Note how the indentation and line spacingmakes it easier to see loops and sections.
Option Explicit
Sub spikes()
Dim WindowlLength As Integer
Dim StdDevRange As Range
Dim DataRow As Long
Dim SummaryRow As Long
Dim LastDataRow As Long
Dim ReferenceTicker As String
Dim Count As Integer
Dim Criterion As Double
Dim IterationIndex As Integer
Dim ScoreColumnName As String
Dim SummaryColumn_2 As Integer
Dim RangeString As String
WindowlLength = 20 '<---A magic number. I have no idea of its significance. SamT
'Column headings
Cells(1, "A") = "Symbol"
Cells(1, "B") = "Date"
Cells(1, "E") = "Close"
Cells(1, "F") = "Price Change"
Cells(1, "G") = "Log Change"
Cells(1, "H") = "Spike"
'Calculate price change and log of price change
DataRow = 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - 1, "A") Then
Cells(DataRow, "F") = Cells(DataRow, "E") / Cells(DataRow - 1, "E")
Cells(DataRow, "G") = Log(Cells(DataRow, "E") / Cells(DataRow - 1, "E"))
End If
DataRow = DataRow + 1
Wend
'Calculate price spikes in standard deviations
DataRow = WindowlLength + 3
While Cells(DataRow, "A") <> "" '<-----------------No Wend in sight. SamT
'If DataRow is ever less than WindowSize, this will cause a crash
If Cells(DataRow, "A") = Cells(DataRow - WindowlLength - 1, "A") Then '----Math? SamT
Set StdDevRange = Range("G" & (DataRow - WindowlLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / (Application.WorksheetFunction.StDev(StdDevRange) * Cells(DataRow - 1, "E"))
End If
'Suggested Wend
Wend '<--- Not here in original. Added by SamT just to check Compiling------------------------------------------------------------
IterationIndex = 0
For Criterion = 0.5 To 4 Step 0.5
DataRow = 2
Count = 0
ReferenceTicker = Cells(DataRow, "A")
'Create a sum for each ticker and store in column G
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = ReferenceTicker Then
If Abs(Cells(DataRow, "F")) > Criterion Then
Count = Count + 1
End If
Else
ReferenceTicker = Cells(DataRow, "A")
DataRow = DataRow - 1
Cells(DataRow, "G") = Count
Count = 0
End If
DataRow = DataRow + 1
Wend
Cells(DataRow - 1, "G") = Count
'Create summary table-add data after each pass through the file
LastDataRow = DataRow - 1
SummaryRow = DataRow + 2
ScoreColumnName = ">" & Criterion & " StdDev"
SummaryColumn_2 = IterationIndex + 2
Cells(SummaryRow, SummaryColumn_2) = ScoreColumnName
If IterationIndex = 0 Then
Cells(SummaryRow, "A") = "Ticker"
End If
SummaryRow = SummaryRow + 1
For DataRow = 2 To LastDataRow
If Trim(Cells(DataRow, "G")) <> "" Then '<---Modified by SamT----------------------------------------------------------------------------
Cells(SummaryRow, "A") = Cells(DataRow, "A")
Cells(SummaryRow, SummaryColumn_2) = Cells(DataRow, "G")
SummaryRow = SummaryRow + 1
End If
Next DataRow
'Begin next pass through the file
IterationIndex = IterationIndex + 1
Next Criterion
'Format summary table
RangeString = "A" & (LastDataRow + 4) & ":" & "I" & (SummaryRow - 1)
Range(RangeString).Select
Selection.NumberFormat = "0"
End Sub