Implement a dynamic "lookback" period?

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
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
Re: How to implement a dynamic "lookback" period?

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

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:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
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

row counttriggersflag
10
20
300
400
511
601
711
801
901
1000

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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

row counttriggersflag
10
20
300
400
511
601
711
801
901
1000

<tbody>
</tbody>

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:

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
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?

row counttriggersflag
10
20
300
411
511
601
701
800
900
1000

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
In what ways have you changed the code from post #13 ? With the data you've provided I get a count of 4.
 

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
Re: How to implement a dynamic "lookback" period?

Sorry I was missing the Exit For; thanks for the help - much appreciated
 

cfadr

New Member
Joined
Oct 17, 2019
Messages
28

ADVERTISEMENT

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:

row_countflag_1flag_2flag_3value
10000.717761
20000.261028
30000.035727
40000.460677
50010.365034
61010.756099
71000.540048
81100.339672
90100.846927
100100.683186
110000.732851
120000.648145

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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?

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
 

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
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:
row_countflag_1flag_2flag_3returns
1000
2000
301022%
401083%
501182%
611169%
710074%
811089%
901017%
1001073%
1100071%
1200013%

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,417
Messages
5,636,152
Members
416,903
Latest member
Sanjayaranj

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
Top