Consulting

Results 1 to 7 of 7

Thread: Script not Running

  1. #1
    VBAX Regular
    Joined
    Jun 2021
    Posts
    19
    Location

    Script not Running

    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 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

  2. #2
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    ​Just to add missing crosspost link: LINK

  3. #3
    VBAX Regular
    Joined
    Jun 2021
    Posts
    19
    Location
    Thanks. But I don't understand.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    He means that you posted this question on ozgrid.com and didn't tell anyone. That is considered rude.

    Your code, as shown in your post. won't run because it is not all there. If we can't see all the code, we can't troubleshoot it.

    Also... Why are you using DataRow in the second While Loop
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Jun 2021
    Posts
    19
    Location
    I am very sorry for that. I did not look at it that way but my desperation. Thanks.
    You are right. That is not the whole code; I copied the first aspect. The code is 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:
    "
    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
    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
    While the reason for using DataRow in the second loop, according to the writer of the script is shown in the table in the third 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.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Paul_Hossler; 06-24-2021 at 08:54 AM.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Jun 2021
    Posts
    19
    Location
    I am highly appreciative. I will try it out. Thanks alot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •