Script not Running

akin · Jun 23rd 2021

Pls, I am new to VBA. I copy this script below from a book on how to calculate standard deviation on a data in a sheet and record in separate column.

The problem I am having is that the script is not running. Please, what could be the reason? Or rather help me modify it. Thanks.

The script:

Code

Dim WindowlLength As Integer
Dim StdDevRange As Range


WindowLength = 20


'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 = WindowLength + 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - _
WindowLength - 1, "A") Then
Set StdDevRange = Range("G" & (DataRow - _
WindowLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / _
(Application.WorksheetFunction.StDev(StdDevRange) _
* Cells(DataRow - 1, "E"))
End If

Display More

rollis13 · Jun 23rd 2021

Just to add missing crosspost link: LINK

akin · Jun 23rd 2021

Thanks. But I don't understand.

**royUK** · Jun 23rd 2021

Read the Forum Rules and you will understand

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

Also,

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!

Read this to understand why we ask you to do this

Cross Posting

**royUK** · Jun 23rd 2021

Where I have you put the code?

Read the Forum Rules then attach an example of the workbook.

akin · Jun 23rd 2021

I am sorry for that. I am desperate for quick answer. It will not repeat itself. The cross post is in VBAexpress. Thanks

**royUK** · Jun 23rd 2021

Thanks. Attach an example of the workbook

akin · Jun 23rd 2021

Thanks. This is it.

**royUK** · Jun 23rd 2021

There's no code in the attached workbook.

I've added the code but it just goes into and endless Loop. Are you sure that you have copied it correctly?

akin · Jun 24th 2021

I am very sorry for that.

You are right. That is not the whole code; I copied the first aspect. The code is too long and I did not what to stress you, thinking that if one worked I will be able to manipulate my way through the rest. The whole code is:

Code

"Sub spikes ( )
Dim WindowlLength As Integer
Dim StdDevRange As Range


WindowLength = 20


'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 = WindowLength + 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - _
WindowLength - 1, "A") Then
Set StdDevRange = Range("G" & (DataRow - _
WindowLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / _
(Application.WorksheetFunction.StDev(StdDevRange) _
* Cells(DataRow - 1, "E"))
End If


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


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 Iteration Index = 0 Then
Cells (SummaryRow, "A") = “Ticker"
End If
SummaryRow = SummaryRow + 1


For DataRow = 2 To LastDataRow
If Cells (DataRow, "G") <> “ " Then
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"

Display More

And workbook to apply it to is attached as first attachment

After the vba script has been run, it will produce a table similar to the one in second attachment.

In all, this script set to achieve on the workbook:

1. Align record in the Merged worksheet (that consist five different data each consisting 252 rows) of data base on date and remove unmatched ticker or row of data

2. Remove blank row of data

3. Create extra columns for calculation like 'price change', 'log of price change', 'standard deviation' and 'price spike'

4. Finally, produce the summary table as seen in the attached table

I really appreciate your kind gesture. Once again, I am sorry for my early attitude. Thanks.

**royUK** · Jun 24th 2021

Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jun 24th 2021

I really don't understand what you are trying to do.

akin · Jun 24th 2021

Noted, sir. I now saw the symbol;I was actually looking for it.

I am trying to analyse some American stock options historical price using standard deviation method to pick stock with highest price move as can be seen in the summary table attached.

I copied the script from a book I am reading on it. When I tried to apply the script, it was not running with the workbook (the sample I attached).

Please help me with it for any modification needed. Thanks.

Script not Running

Participate now!

Share