Implement a dynamic "lookback" period?

C

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
Hi,

I have a time series data set and I am trying to perform calculations based on different flags/conditions. However, I need the macro user to be able to input a "lookback" period which will cause the if statement used to determine that flag/condition to check if the condition was true at any time during the "lookback" period - how best can I implement this lookback period? Thanks

Example:

Dim data_set As Variant
data_set = Sheets("Data").Range("A4:AV75617")
Dim i As Long
For i = Sheets("Control").Range("A8") To UBound(data_set, 1) 'A8 contains the value the user has inputted for the lookback period (number of prior periods in time series in which to check if condition is true)
indicator_1 = data_set (i, 15)
If indicator_1 > 0 Then 'here is my problem - I need indicator_1 to be a range from i to i - [lookback period] rather than a point value and then I need the flag to be true if that range is > 0 (i.e., if any value in that range is > 0)
flag_1 = True
End If 'I then go on to use the flag_1 in other if statements
 
Sort by date Sort by votes
Re: How to implement a dynamic "lookback" period?

Add .value to the end of Data_Set = Sheets("Sheet1").Range("A2:G13") or remove the () from the dim line if you are going to use the rewritten version.
 
Last edited:
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Re: How to implement a dynamic "lookback" period?

Thanks; unfortunately this did not produce the desired result. The code provided appears to say if the three conditions are True then "position exists" and don't do the returns calc. What I am actually trying to do is as follows:
- There are three flags, two of which have "lookback periods" (flags 1 and 3) - i.e., they are "on" or =1 in the current row (current i) if they were true in any of the previous rows in the lookback period (these "lookbacks" are the loops from j to)
- If the three flags are "on" or =1 then the "action_flag" is turned on and a calculation is done
- The above works fine but I am now trying to add a condition that if the "action_flag" was on in the lookback rows (2 previous rows in this case) then it should not be switched on (because I am trying to avoid triggering the action flag in consecutive periods by implementing a condition that if it was already on in either of the last 2 periods then don't switch it on again)

Recopying previous code for convenience:

Code: 
Sub test_loop()
Dim i As Long
Dim data_set As Variant
data_set = Sheets("Sheet1").Range("A2:G13")
lookback = 2
For i = lookback + 1 To UBound(data_set, 1)
    
    For j = i - lookback To i       'This is the loop with the flag that has a lookback period - flag_one based on trigger_one
        trigger_one = data_set(j, 2)
        If trigger_one > 0 And IsNumeric(trigger_one) Then
            flag_one = 1
        Exit For
        End If


    Next j
    
    For j = i - lookback To i       'This is the loop with the flag that has a lookback period - flag_one based on trigger_one
        trigger_three = data_set(j, 4)
        If trigger_three > 0 And IsNumeric(trigger_three) Then
            flag_three = 1
        Exit For
        End If
    Next j
    
    
    If flag_one = 1 Then
        flag_one_count = flag_one_count + 1
        total_one_value = total_one_value + data_set(i, 5)
    End If
    If flag_three = 1 Then
        flag_three_count = flag_three_count + 1
    End If


    trigger_two = data_set(i, 3) 'This is the flag with no lookback period - flag_two based on trigger_two




    If trigger_two > 0 And IsNumeric(trigger_two) Then
    
        flag_two = 1
        flag_two_count = flag_two_count + 1
    End If
    


'This part not working - I am trying to implement a check to see if the "action flag" below (combination of flags 1-3) was already triggered in the lookback period; in which case I don't want it to switch on in this period
    For k = i - lookback To i - 1
        If flag_one = 1 And flag_two = 1 And flag_three = 1 Then
        existing_position = 1
        Exit For
        End If
    Next k


    If flag_one = 1 And flag_two = 1 And flag_three = 1 And existing_position <> 1 Then   'If both flag_one (which has a lookback period) and flag_two (which does not have a lookback period) are equal to one then do the following
        Action_Flag = 1
        Action_Flag_Count = Action_Flag_Count + 1
        total_returns = total_returns + data_set(i, 5)
    End If
    
'///////////////////////////////
'Reset Values for the next loop|
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
trigger_one = 0
trigger_two = 0
flag_one = 0
flag_two = 0
trigger_three = 0
flag_three = 0
existing_position = 0
Next i




Sheets("Sheet1").Range("F17") = flag_one_count
Sheets("Sheet1").Range("F18") = flag_three_count
Sheets("Sheet1").Range("F19") = flag_two_count
Sheets("Sheet1").Range("F20") = Action_Flag_Count
Sheets("Sheet1").Range("F21") = total_returns / Action_Flag_Count




End Sub

[TABLE="width: 329"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]row_count[/TD]
[TD]flag_1[/TD]
[TD]flag_2[/TD]
[TD]flag_3[/TD]
[TD]returns[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]83%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]82%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]69%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]74%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]89%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]73%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]71%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
because I am trying to avoid triggering the action flag in consecutive periods by implementing a condition that if it was already on in either of the last 2 periods then don't switch it on again)
Click to expand...

Do you mean for example that if you are on row 10 of the array with a lookback period of 2 then you want to check if conditions were met when on row 8 and 9 as well and do nothing if they were? ie check 6,7,8 and then check 7,8,9
 
Last edited:
Upvote 0
Re: How to implement a dynamic "lookback" period?

Yes; so the lookback period is being used for two things -

1) to determine whether flags 1 and 3 should be switched on (they are turned on if triggers 1 and 3 were hit within the lookback period); and
2) to determine whether the "action flag" should be switched on assuming flags 1-3 have all been hit - in this case what I am trying to do is as you say in this example check rows 8-9 to see if the action flag was already activated and if so then don't activate in this row otherwise do activate

By way of example, in the sample data set below - with out the lookback period being used for part 2) above the "action flag" would be hit twice in rows 6 and 8. However, what I am trying to do is ensure it is only hit in row 8 (because in the loop for row 8 the code should "see" that it was already hit in row 6 and therefore not hit it again):

[TABLE="width: 329"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]row_count[/TD]
[TD]trigger_1[/TD]
[TD]trigger_2[/TD]
[TD]trigger_3[/TD]
[TD]returns[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]83%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]82%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]69%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]74%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]89%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]73%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]71%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13%[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
Yes; so the lookback period is being used for two things -

1) to determine whether flags 1 and 3 should be switched on (they are turned on if triggers 1 and 3 were hit within the lookback period); and
2) to determine whether the "action flag" should be switched on assuming flags 1-3 have all been hit - in this case what I am trying to do is as you say in this example check rows 8-9 to see if the action flag was already activated and if so then don't activate in this row otherwise do activate

By way of example, in the sample data set below - with out the lookback period being used for part 2) above the "action flag" would be hit twice in rows 6 and 8. However, what I am trying to do is ensure it is only hit in row 8 (because in the loop for row 8 the code should "see" that it was already hit in row 6 and therefore not hit it again):
Click to expand...

Let's say rows 6 and 8 meet the conditions meaning that the macro does stuff for 6 but not 8. What if conditions are met on 10 ? Since the loop would only go as far back as 7 and then trigger when looking at 8, do you want nothing to be done since conditions were met on 8?
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

No - in that case because it wasn't actually triggered on 8 (conditions were met but it was triggered on 6 so didn't trigger on 8) if conditions are then met on 10 I need it to trigger (a sufficient period has elapsed from when it last triggered). Thanks
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
No - in that case because it wasn't actually triggered on 8 (conditions were met but it was triggered on 6 so didn't trigger on 8) if conditions are then met on 10 I need it to trigger (a sufficient period has elapsed from when it last triggered). Thanks
Click to expand...

I rewrote to use booleans rather than compare numbers, but this works properly as far as I can tell. Monitor the immediate window after running and comment out the debug.print lines in the final macro.

Code: 
Sub test_loop()


Dim i As Long, Flag_1_Count As Long, Flag_2_Count As Long, Flag_3_Count As Long, Action_Flag_Count As Long


Dim Trigger_1 As Boolean, Trigger_2 As Boolean, Trigger_3 As Boolean, Action_Flag As Boolean, _
Position_Exists As Boolean, Data_Set() As Variant, Hold_Triggers As Object, Minimum_Row_Requirement As Long


Set Hold_Triggers = CreateObject("Scripting.Dictionary")


Dim Intermediate_Action_Flag As Boolean


Data_Set = ThisWorkbook.Sheets("Sheet1").Range("A2:G22").Value


lookback = 2


For i = lookback + 1 To UBound(Data_Set, 1)
    
    For j = i - lookback To i       'This is the loop with the flag that has a lookback period- flag_one based on trigger_1
        
        If Data_Set(j, 2) > 0 And IsNumeric(Data_Set(j, 2)) And Trigger_1 = False Then
            
            Trigger_1 = True
            
            Flag_1_Count = Flag_1_Count + 1
            
            total_one_value = total_one_value + Data_Set(i, 5)
        
        End If
        
        If Data_Set(j, 4) > 0 And IsNumeric(Data_Set(j, 4)) And Trigger_3 = False Then
            
            Trigger_3 = True
            
            Flag_3_Count = Flag_3_Count + 1
        
        End If
        
        If Trigger_3 And Trigger_1 Then Exit For 'if both are true then exit for loop
        
    Next j
     
    If Data_Set(i, 3) > 0 And IsNumeric(Data_Set(i, 3)) Then 'This is the flag with no lookback period - flag_two based on trigger_2
   
        Trigger_2 = True
        
        Flag_2_Count = Flag_2_Count + 1
        
    End If
    
    'This part not working - I am trying to implement a check to see if the "action flag" below
    '(combination of flags 1-3) was already triggered in the lookback period;
    'in which case I don't want it to switch on in this period
    
    If Trigger_1 = True And Trigger_2 = True And Trigger_3 = True Then
        
        Position_Exists = True
        
        With Hold_Triggers
        
            If .Count = 0 Then
                .Add CStr(i), True 'store row number where conditions were met
            Else
                .Add CStr(i), Empty
            End If
        
        End With
        
    End If


    If Position_Exists And i > lookback + 2 Then
        'if conditions were met for the current row and i is greater than the look back period + 2
        With Hold_Triggers
        
            If .Count > 1 Then 'if there is more than one item in the dictionary
                               'then a comparison to prior periods is necessary
                Minimum_Row_Requirement = i - lookback 'This variable is the minimum bound of the 2 period lookback
                
                For Z = Minimum_Row_Requirement To i - 1
                
                    If .Exists(CStr(Z)) Then 'if conditions were met on this array row
                    
                        If .Item(CStr(Z)) = True Then 'if it stores the boolean True
                        
                            Intermediate_Action_Flag = True 'then do nothing on this row
                            
                            .Item(CStr(i)) = False 'store [no action] inside the dictionary ie: False
                            
                            Debug.Print "[No Action] -Array loop[" & i & "]"
                            
                            Exit For
                        
                        End If
                        
                    End If
                    
                Next Z
                
                If Intermediate_Action_Flag = False Then .Item(CStr(i)) = True 'Action will be taken for this row
                      
            End If 'end .Count > 1 Conditional
            
        End With
    
    ElseIf Position_Exists And i < lookback + 2 Then 'if not enough data but conditions were met
                                                     'then default to no action
        Intermediate_Action_Flag = True
        
        Hold_Triggers.Item(CStr(i)) = False 'store [no action] inside the dictionary ie: False
    
    End If
    
    If Position_Exists = True And Intermediate_Action_Flag = False Then 'If both flag_one (which has a lookback period) and flag_two (which does not have a lookback period) are equal to one then do the following
        
        Debug.Print "Conditions met ; [Action taken] - Array loop[" & i & "]"
        
        Action_Flag = True
        
        Action_Flag_Count = Action_Flag_Count + 1
        
        total_returns = total_returns + Data_Set(i, 5)
    
    End If
    
'///////////////////////////////
'Reset Values for the next loop|
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


Trigger_1 = False
Trigger_2 = False
Trigger_3 = False
Position_Exists = False
Action_Flag = False
Intermediate_Action_Flag = False


'flag_one = 0
'flag_two = 0
'flag_three = 0


Next i


Sheets("Sheet1").Range("F17") = Flag_1_Count
Sheets("Sheet1").Range("F18") = Flag_3_Count
Sheets("Sheet1").Range("F19") = Flag_2_Count
Sheets("Sheet1").Range("F20") = Action_Flag_Count
Sheets("Sheet1").Range("F21") = total_returns / Action_Flag_Count


End Sub
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

Found an error
Code: 
Sub test_loop_2()


Dim i As Long, Flag_1_Count As Long, Flag_2_Count As Long, Flag_3_Count As Long, Action_Flag_Count As Long


Dim Trigger_1 As Boolean, Trigger_2 As Boolean, Trigger_3 As Boolean, Action_Flag As Boolean, _
Position_Exists As Boolean, Data_Set() As Variant, Hold_Triggers() As Boolean, Minimum_Row_Requirement As Long


Dim Intermediate_Action_Flag As Boolean


Data_Set = ThisWorkbook.Sheets("Sheet1").Range("A2:G13").Value


ReDim Hold_Triggers(1 To UBound(Data_Set, 1))


lookback = 2


For i = lookback + 1 To UBound(Data_Set, 1)
    
    For j = i - lookback To i       'This is the loop with the flag that has a lookback period- flag_one based on trigger_1
        
        If Data_Set(j, 2) > 0 And IsNumeric(Data_Set(j, 2)) And Trigger_1 = False Then
            
            Trigger_1 = True
            
            Flag_1_Count = Flag_1_Count + 1
            
            total_one_value = total_one_value + Data_Set(i, 5)
        
        End If
        
        If Data_Set(j, 4) > 0 And IsNumeric(Data_Set(j, 4)) And Trigger_3 = False Then
            
            Trigger_3 = True
            
            Flag_3_Count = Flag_3_Count + 1
        
        End If
        
        If Trigger_3 And Trigger_1 Then Exit For 'if both are true then exit for loop
        
    Next j
     
    If Data_Set(i, 3) > 0 And IsNumeric(Data_Set(i, 3)) Then 'This is the flag with no lookback period - flag_two based on trigger_2
   
        Trigger_2 = True
        
        Flag_2_Count = Flag_2_Count + 1
        
    End If
    
    'This part not working - I am trying to implement a check to see if the "action flag" below
    '(combination of flags 1-3) was already triggered in the lookback period;
    'in which case I don't want it to switch on in this period
    
    If Trigger_1 = True And Trigger_2 = True And Trigger_3 = True Then
        
        Position_Exists = True
        
        Hold_Triggers(i) = True
        
    End If


    If Position_Exists And i > lookback + 2 Then
        'if conditions were met for the current row and i is greater than the look back period + 2
        'then a comparison to prior periods is necessary
        Minimum_Row_Requirement = i - lookback - 2 'This variable is the minimum bound of the 2 period lookback
        
        For Z = Minimum_Row_Requirement To i - 1
        
            If Hold_Triggers(Z) = True Then 'if it stores the boolean True


                Intermediate_Action_Flag = True 'then do nothing on this row


                Hold_Triggers(i) = False 'store [no action] inside the dictionary ie: False


                Debug.Print "[No Action] - Loop[" & i & "]"


                Exit For


            End If
            
        Next Z
        
        If Intermediate_Action_Flag = False Then Hold_Triggers(i) = True 'Action will be taken for this row
    
    ElseIf Position_Exists And i < lookback + 2 Then 'if not enough data but conditions were met
                                                     'then default to no action
        Intermediate_Action_Flag = True
        
        Hold_Triggers(i) = False 'store [no action] inside the dictionary ie: False
    
    End If
    
    If Position_Exists = True And Intermediate_Action_Flag = False Then 'If both flag_one (which has a lookback period) and flag_two (which does not have a lookback period) are equal to one then do the following
        
        Debug.Print "Conditions met ; [Action taken] - Loop[" & i & "]"
        
        Action_Flag = True
        
        Action_Flag_Count = Action_Flag_Count + 1
        
        total_returns = total_returns + Data_Set(i, 5)
    
    End If
    
'///////////////////////////////
'Reset Values for the next loop|
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


Trigger_1 = False
Trigger_2 = False
Trigger_3 = False
Position_Exists = False
Action_Flag = False
Intermediate_Action_Flag = False


'flag_one = 0
'flag_two = 0
'flag_three = 0


Next i


Sheets("Sheet1").Range("F17") = Flag_1_Count
Sheets("Sheet1").Range("F18") = Flag_3_Count
Sheets("Sheet1").Range("F19") = Flag_2_Count
Sheets("Sheet1").Range("F20") = Action_Flag_Count
Sheets("Sheet1").Range("F21") = total_returns / Action_Flag_Count


End Sub
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

Found another possible bug ,changed variable names and and added comments (read them).

Code: 
Sub test_loop()


Dim i As Long, Flag_1_Count As Long, Flag_2_Count As Long, Flag_3_Count As Long, Action_Flag_Count As Long


Dim Trigger_1 As Boolean, Trigger_2 As Boolean, Trigger_3 As Boolean, Action_Flag As Boolean, _
Position_Exists As Boolean, Data_Set() As Variant, BOOL_Validation() As Boolean


Dim Invalidate_Action As Boolean


Data_Set = ThisWorkbook.Sheets("Sheet1").UsedRange.Value 'Range("A2:G13").Value


ReDim BOOL_Validation(1 To UBound(Data_Set, 1))


lookback = 2


For i = lookback + 1 To UBound(Data_Set, 1)
    
    For j = i - lookback To i 'This is the loop with the flag that has a lookback period- flag_one based on trigger_1
        
        If Data_Set(j, 2) > 0 And IsNumeric(Data_Set(j, 2)) And Trigger_1 = False Then
            
            Trigger_1 = True 'FLAG 1
            
            Flag_1_Count = Flag_1_Count + 1
            
            total_one_value = total_one_value + Data_Set(i, 5)
        
        End If
        
        If Data_Set(j, 4) > 0 And IsNumeric(Data_Set(j, 4)) And Trigger_3 = False Then
            
            Trigger_3 = True 'FLAG 3
            
            Flag_3_Count = Flag_3_Count + 1
        
        End If
        
        If Trigger_3 And Trigger_1 Then Exit For 'if both are true then exit "for" loop
        
    Next j
     
    If Data_Set(i, 3) > 0 And IsNumeric(Data_Set(i, 3)) Then 'This is the flag with no lookback period - flag_two based on trigger_2
   
        Trigger_2 = True 'FLAG 2
        
        Flag_2_Count = Flag_2_Count + 1
        
    End If
    
    'This part not working - I am trying to implement a check to see if the "action flag" below
    '(combination of flags 1-3) was already triggered in the lookback period;
    'in which case I don't want it to switch on in this period
    
    If Trigger_1 = True And Trigger_2 = True And Trigger_3 = True Then
        
        Position_Exists = True
        
    End If
    
    'If the above booleans are true and i is greater than the start of the parent loop +1
    
    If Position_Exists And i >= lookback + 2 Then
        
        'If conditions are met for the current row
            'Then [Evaluate if action was taken on the 2 previous loops]
            
            For Z = i - 2 To i - 1
            
                If Z = 0 Then Z = 1
                
                If BOOL_Validation(Z) = True Then 'If ANY value in the specified range of array elements is True [Action Taken]
    
                    Invalidate_Action = True 'Then do nothing for this row of the array
    
                    BOOL_Validation(i) = False 'Store [no action] inside the Boolean Array for the current parent loop Row [False]
                    
                    Exit For 'Above Condition met at least once so exit current loop
                    
                    'Technically there's no need to store False since the default value is False
                    'So it's just for illustration purposes
                    
                End If
                
            Next Z
        
        'Action will be taken for this row if Invalidate_Action from the above loop is FALSE
        If Invalidate_Action = False Then
        
            BOOL_Validation(i) = True
            
        Else 'IF Invalidate_Action = True
        
            Debug.Print "[No Action] - Row[" & i & "]"
            
        End If
    
    ElseIf Position_Exists And i = lookback + 1 Then 'If on first loop and conditions are met


        Invalidate_Action = False 'just for illustration purposes [default value at the end of each loop is false]
        
        BOOL_Validation(i) = True
        
    End If
    
    'If the 3 conditions for the current loop are met and Action wasn't taken in the two previous loops then do the below
    If Position_Exists = True And Invalidate_Action = False Then
                                                                     
        Debug.Print "Conditions met ; [Action taken] - Row[" & i & "]"
        
        Action_Flag = True
        
        Action_Flag_Count = Action_Flag_Count + 1
        
        total_returns = total_returns + Data_Set(i, 5)
    
    End If
    
'///////////////////////////////
'Reset Values for the next loop|
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


Trigger_1 = False
Trigger_2 = False
Trigger_3 = False
Position_Exists = False
Action_Flag = False
Invalidate_Action = False


'flag_one = 0
'flag_two = 0
'flag_three = 0


Next i


With Sheets("Sheet1")
    
    .Range("F17") = Flag_1_Count
    .Range("F18") = Flag_3_Count
    .Range("F19") = Flag_2_Count
    .Range("F20") = Action_Flag_Count
    .Range("F21") = total_returns / Action_Flag_Count


End With


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
310
Logit
Logit
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
548
felixstraube
F
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
925
Peter_SSs
Peter_SSs
D
  • Locked
  • Question Question
VBA to copy only matching data from 2 csv workbooks into the "Master Workbook"
Replies
1
Views
329
RoryA
RoryA
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
257
jsand
J

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top