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?

cfadr said:
However, my issue is that I need the lookback period to be dynamic / based on a user input and the above code doesn't cater for that; this is why I tried to use a second loop for the lookback period but it's not working. Essentially I need some code for my initial loop to say "for each i" if any of the last "x" rows were equal to "y" then one_change_flag = 1. Where "x" is my "lookback period"?
Click to expand...

It isn't the loop, here is an amended version of the code from Post #7 that more clearly notes what each loop does. I hope you notice how similar it is to the loops you already have with the exception of it excluding the current row of the array when comparing items in the lookback period via the -1.
Code: 
Sub Generic_LookBack_V2()

Dim Generic_Data(1 To 5000, 1 To 20) As Variant, LookBack_Period As Long, Initial_Start As Long, X As Long, Condition_Met As Boolean

LookBack_Period = 500 'this can also be supplied from a range or input box

For Current_Row = LookBack_Period + 1  To UBound(Generic_Data, 1) 'loop all rows starting at the LookBack period + 1

    For X = Current_Row - LookBack_Period To Current_Row - 1 'Loop the [COLOR=#0000ff]previous LookBack_Period[/COLOR] number of Rows without including the current row in that LookBack period
[COLOR=#ff0000]            'Example 1 to 500 when Current_Row=501[/COLOR]
[COLOR=#ff0000]                    '2 to 501 when Current_Row=502[/COLOR]
        If Some_Condition Then  'example:   Generic_Data(x,15) = 200
            
            Condition_Met = True
            
        End If
    
    Next X
[COLOR=#008000]    'Your calculations go here[/COLOR]
    [COLOR=#ff0000]'Reset variables here if needed[/COLOR]
Next Current_Row

End Sub

Also your lookback period is dynamic as you've set it to be whatever this evaluates to:
Code: 
[COLOR=#333333]lookback_period = Sheets("Control").Range("F24")[/COLOR]
 
Last edited:
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Re: How to implement a dynamic "lookback" period?

Sorry I am still struggling to get my loop right for this; I have included a simplified version below and the data to illustrate where I am struggling. I have triggers and flags - triggers are either 1 or 0 in each period; flags are dependent on triggers - a flag should be 1 if the trigger was 1 in either the currrent period or any period in the lookback timeframe (previous two periods in this example). I have added an output in the below to count the number of flags so I can check if the flags are on/off the correct number of times - unfortunately the code is giving me a flag count of 8 instead of 5 as I want. Grateful if anyone can tell me where I am going wrong in the loop; I have tried various permutations but can't seem to get it right. Thanks

Code: 
Sub test_loop()
Dim i As Long
Dim data_set As Variant
data_set = Sheets("Sheet1").Range("A1:G10")
lookback = 2
For i = lookback + 1 To UBound(data_set, 1)
    For j = i - lookback To i
        trigger = data_set(j, 2)
        If trigger > 0 Then
        flag = 1
        End If
    Next j
flag_count = flag_count + flag
Next i
Sheets("Sheet1").Range("E12") = flag_count
End Sub

[TABLE="width: 197"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]row count[/TD]
[TD]triggers[/TD]
[TD]flag[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
Sorry I am still struggling to get my loop right for this; I have included a simplified version below and the data to illustrate where I am struggling. I have triggers and flags - triggers are either 1 or 0 in each period; flags are dependent on triggers - a flag should be 1 if the trigger was 1 in either the currrent period or any period in the lookback timeframe (previous two periods in this example). I have added an output in the below to count the number of flags so I can check if the flags are on/off the correct number of times - unfortunately the code is giving me a flag count of 8 instead of 5 as I want. Grateful if anyone can tell me where I am going wrong in the loop; I have tried various permutations but can't seem to get it right. Thanks

[TABLE="width: 197"]
<tbody>[TR]
[TD]row count[/TD]
[TD]triggers[/TD]
[TD]flag[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Code: 
Sub test_loop()
Dim i As Long
Dim data_set As Variant


data_set = Sheets("Sheet1").Range("A1:G10").Value


lookback = 2


For i = lookback + 1 To UBound(data_set, 1)


    For j = i - lookback To i
    
        trigger = data_set(j, 2)
        
        If trigger > 0 And IsNumeric(trigger) Then
        
            flag_count = flag_count + 1
            
            Exit For
            
        End If
        
    Next j


Next i


Sheets("Sheet1").Range("E12") = flag_count


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

Okay thanks; this works fine when the triggers are separated by at least the lookback period but how can I allow for overlap - for example if I run it on the attached then there should be only 4 flags as you can see but the code gives 6 because of the overlap - is this impossible in a loop structure / do I need to use a different method?

[TABLE="width: 197"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]row count[/TD]
[TD]triggers[/TD]
[TD]flag[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
Okay thanks; this works fine when the triggers are separated by at least the lookback period but how can I allow for overlap - for example if I run it on the attached then there should be only 4 flags as you can see but the code gives 6 because of the overlap - is this impossible in a loop structure / do I need to use a different method?
Click to expand...
In what ways have you changed the code from post #13 ? With the data you've provided I get a count of 4.
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

Sorry I was missing the Exit For; thanks for the help - much appreciated
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

Sorry to come back on this but while the second loop worked for implementing a lookback period for my first flag, I am struggling to combine it with a second flag that needs to have no lookback period. Per the below my ultimate condition needs to be if both flag one (which is in the inner loop and has a lookback period) AND flag_two (which is in the outer loop and thus has no lookback period) are equal to one. Unfortunately I can see that my structure doesn't work because I moved the flag_one_count outside the inner loop (to see if flag_one still worked after the inner loop was closed; so that it could be compared with flag_two) it doesn't give me the same / correct number as when it's in the inner loop. Do I need to move the placement of the Exit For statement?

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
If flag_one = 1 Then
flag_one_count = flag_one_count + 1
total_one_value = total_one_value + data_set(i, 5)
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
Exit For
End If
If flag_one = 1 And flag_two = 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
End If
Next i
Sheets("Sheet1").Range("F16") = total_one_value
Sheets("Sheet1").Range("F17") = flag_one_count
Sheets("Sheet1").Range("F18") = action_flag_count
End Sub

Data:

[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]value[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.717761[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.261028[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.035727[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.460677[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.365034[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.756099[/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"]0.540048[/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"]0.339672[/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"]0.846927[/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"]0.683186[/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"]0.732851[/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"]0.648145[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: How to implement a dynamic "lookback" period?

cfadr said:
Sorry to come back on this but while the second loop worked for implementing a lookback period for my first flag, I am struggling to combine it with a second flag that needs to have no lookback period. Per the below my ultimate condition needs to be if both flag one (which is in the inner loop and has a lookback period) AND flag_two (which is in the outer loop and thus has no lookback period) are equal to one. Unfortunately I can see that my structure doesn't work because I moved the flag_one_count outside the inner loop (to see if flag_one still worked after the inner loop was closed; so that it could be compared with flag_two) it doesn't give me the same / correct number as when it's in the inner loop. Do I need to move the placement of the Exit For statement?
Click to expand...

The exit for statement that you added would exit the main loop at any point trigger_two was greater than 0

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
    
    If flag_one = 1 Then
        flag_one_count = flag_one_count + 1
        total_one_value = total_one_value + data_set(i, 5)
    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
    
    End If


    If flag_one = 1 And flag_two = 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 'WHAT IS THIS ONE USED FOR ???????????????????????????????????????????????????????
        action_flag_count = action_flag_count + 1
        
    End If
    
'///////////////////////////////
'Reset Values for the next loop|
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
trigger_one = 0
trigger_two = 0
flag_one = 0
flag_two = 0


Next i


Sheets("Sheet1").Range("F16") = total_one_value
Sheets("Sheet1").Range("F17") = flag_one_count
Sheets("Sheet1").Range("F18") = action_flag_count


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

Thanks again for the help with this; when I fixed my error per the above everything worked great. However, I am now trying to add another loop to check if the "action_flag" (i.e., combination of flags 1-3) had already been triggered within the lookback period (I don't want to trigger it again if it was already hit within the lookback period.

I have added another loop per the below but it's not working (I have tried various other constructions with this loop but none working); can anyone point me in the right direction? Thanks

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

Data:
[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?

Try This:

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
    
    If flag_one = 1 And flag_two = 1 And flag_three = 1 Then
        
        existing_position = 1
        
    End If


    If 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

I've also rewritten your code to use booleans and lessened the amount of if statements.

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


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_1
        
        If Data_Set(j, 2) > 0 And IsNumeric(Data_Set(j, 2)) Then
            
            Trigger_1 = True
            
            Flag_1_Count = Flag_1_Count + 1
            
            total_one_value = total_one_value + Data_Set(i, 5)
            
            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_1
        
        If Data_Set(j, 4) > 0 And IsNumeric(Data_Set(j, 4)) Then
            
            Trigger_3 = True
            
            Flag_3_Count = Flag_3_Count + 1
            
            Exit For
        
        End If
    
    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
    
    End If


    If Position_Exists = 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
        
        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


'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
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
364
Logit
Logit
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
949
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
338
RoryA
RoryA
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
286
jsand
J

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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