Implement a dynamic "lookback" period?

[cfadr]

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]

Re: How to implement a dynamic "lookback" period?

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

This is an example, not a solution to your specific problem:

Private Function Multi_IRecal(Column_Number_Reference As Long, Time_Period As Long, Arr As Variant, Optional Missing_Weeks As Long = 1) As Variant


Dim Array_Column() As Double, x As Long, i As Long, Array_Period() As Variant, Current_Row As Long, Array_Final() As Long, _
UB As Long, Initial As Long


UB = UBound(Arr, 1) [COLOR=#0000ff]'number of rows in the supplied array[Upper bound in 1st dimension][/COLOR]


ReDim Array_Period(1 To Time_Period, 1 To 1) [COLOR=#0000ff]'Temporary Array that will hold the look back period (ie the past 70 values)[/COLOR]
ReDim Array_Final(1 To Missing_Weeks) [COLOR=#0000ff]'Array that will hold calculated values[/COLOR]
ReDim Array_Column(1 To UB) [COLOR=#0000ff]'Array that stores all data in the column[/COLOR]


If Missing_Weeks = 1 Then [COLOR=#0000ff]'if only doing calculations for a single week's worth of data[/COLOR]

    Initial = UB [COLOR=#0000ff]'start at the bottom of the supplied array[/COLOR]
    
Else [COLOR=#0000ff]'start at the beginning of the missing data weeks block[/COLOR]

    Initial = UB - (Missing_Weeks - 1)

End If

For i = 1 To UB [COLOR=#0000ff]'store column of another array[/COLOR]

    Array_Column(i) = Arr(i, Column_Number_Reference) [COLOR=#0000ff]'Places Reference Column inside a 1D array [can be 2d if needed..adjust accordingly][/COLOR]

Next i
   
With WorksheetFunction

    For Current_Row = Initial To UB
    
        If Current_Row > Time_Period Then [COLOR=#0000ff]'only calculate if there is enough data since a stochastic calculation is performed[/COLOR]
        
            For x = 1 To Time_Period  [COLOR=#0000ff]'Fill the temporary array starting at variable Current Row[/COLOR]
[COLOR=#0000ff]                                      'with the previous Time_Period number of values[/COLOR]
                Array_Period(x, 1) = Array_Column(Current_Row - x)

            Next x
            
            'Array_Period now has the last Time_Period number of values
            
            Array_Final(Missing_Weeks - (UB - Current_Row)) = ((Array_Column(Current_Row) - .Min(Array_Period)) / (.Max(Array_Period) - .Min(Array_Period))) * 100
                        [COLOR=#ff0000]'ex: 2 - ( 480 - 479 ) = 1[/COLOR]                      'Stochastic calculation
        End If
    
    Next Current_Row
    
End With

Multi_IRecal = Array_Final

End Function

 

[cfadr]

Re: How to implement a dynamic "lookback" period?

Thanks for this. I was struggling to implement your specific example but thought I could achieve the desired result by having a second loop for the flags I want to have a "lookback period" for? However, I don't think this is working - my code runs okay but my flags with the lookback period aren't working correctly (the flag is triggered too many times / when it shouldn't be). I think the issue is in line 37 below; can anyone assist? (essentially the reason for the lookback period is that for these flags I want them triggered if they were "true" in any of the periods in the "lookback timeframe") Thanks

Sub combination_testing()
' On Error GoTo ErrHandler
1 Application.ScreenUpdating = False
2 Dim data_set As Variant
3 data_set = Sheets("Data").Range("A4:AV75617")
4 Dim i As Long
5 Dim sumall_combination_1y, sumall_combination_2y, sumall_combination_3y, sumall_combination_wnrs_1y, sumall_combination_wnrs_2y, sumall_combination_wnrs_3y, sumall_combination_lsrs_1y, sumall_combination_lsrs_2y, sumall_combination_lsrs_3y, sumall_combination_1, sumall_combination_2, sumall_combination_3, sumall_combination_4, sumall_combination_5, sumall_combination_6, sumall_combination_7, sumall_combination_8, sumall_combination_9, sumall_combination_10, sumall_combination_11, sumall_combination_12, sumall_combinationspx_1, sumall_combinationspx_2, sumall_combinationspx_3, sumall_combinationspx_4, sumall_combinationspx_5, sumall_combinationspx_6, sumall_combinationspx_7, sumall_combinationspx_8, sumall_combinationspx_9, sumall_combinationspx_10, sumall_combinationspx_11, sumall_combinationspx_12 As Double
6 Dim cnt_combination_1y, cnt_combination_2y, cnt_combination_3y, cnt_combination_wnrs_1y, cnt_combination_wnrs_2y, cnt_combination_wnrs_3y, cnt_combination_lsrs_1y, cnt_combination_lsrs_2y, cnt_combination_lsrs_3y, cnt_combination_prof As Long
7 cnt_combination_1y = 0: sumall_combination_1y = 0: cnt_combination_2y = 0: sumall_combination_2y = 0: cnt_combination_3y = 0: sumall_combination_3y = 0: cnt_combination_wnrs_1y = 0: cnt_combination_wnrs_2y = 0: cnt_combination_wnrs_3y = 0: cnt_combination_lsrs_1y = 0: cnt_combination_lsrs_2y = 0: cnt_combination_lsrs_3y = 0: sumall_combination_wnrs_1y = 0: sumall_combination_wnrs_2y = 0: sumall_combination_wnrs_3y = 0: sumall_combination_lsrs_1y = 0: sumall_combination_lsrs_2y = 0: sumall_combination_lsrs_3y = 0: cnt_combination_prof = 0
8 one_change_must = Sheets("Control").Range("D24")         'checking whether conditions are MUST, OTHER or EXCLUDED
9 two_change_must = Sheets("Control").Range("D25")
10 three_change_must = Sheets("Control").Range("D26")
11 ii_must = Sheets("Control").Range("D27")
12 ds_must = Sheets("Control").Range("D28")
13 cm_must = Sheets("Control").Range("D29")
14 ur_must = Sheets("Control").Range("D30")
15 g_must = Sheets("Control").Range("D31")
16 must_checks_total = Sheets("Control").Range("D21")
17 other_checks_total = Sheets("Control").Range("D22")
18 lookback_period = Sheets("Control").Range("F24")
19 ii_threshold = Sheets("Control").Range("G27")
20 cm_threshold = Sheets("Control").Range("G29")
21 ur_threshold = Sheets("Control").Range("G30")
22 g_threshold = Sheets("Control").Range("G31")
23 For i = lookback_period + 1 To UBound(data_set, 1)
24    x_member = data_set(i, 8)
25    company_name = data_set(i, 3)
26    company_name_prev = data_set(i - lookback_period, 3)
27    g_score = data_set(i, 11)
28    ur_score = data_set(i, 13)
30    cm_score = data_set(i, 12)
31          For j = i - lookback_period To i 'combination change trigger
32          one_change = data_set(j, 15)
33          two_change = data_set(j, 16)
34          three_change = data_set(j, 17)
35          ii = data_set(j, 18)
36          ds = data_set(j, 10)
37              If one_change = 1 Then    'not sure if this will work; one_change is not a single value but several values
38              one_change_flag = 1
304             one_change_flags = one_change_flags + 1
39              End If
40              If two_change = 1 Then
41              two_change_flag = 1
42              End If
43              If three_change = 1 Then
44              three_change_flag = 1
45              End If
46              If ii > ii_threshold Then
47              ii_flag = 1
48              End If
49              If ds = 1 Then
50              ds_flag = 1
51              End If
52          Next j
53         If one_change_flag = 1 And one_change_must = 1 Then
54         one_must_check = 1
55         ElseIf one_change_flag = 1 And one_change_must = 3 Then
57         one_other_check = 1
58         Else
59         one_other_check = 0: one_must_check = 0
60         End If
61         If two_change_flag = 1 And two_change_must = 1 Then
62         two_must_check = 1
63         ElseIf two_change_flag = 1 And two_change_must = 3 Then
65         two_other_check = 1
66         Else
67         two_must_check = 0: two_other_check = 0
68         End If
69         If three_change_flag = 1 And three_change_must = 1 Then
70         three_must_check = 1
71         ElseIf three_change_flag = 1 And three_change_must = 3 Then
73         three_other_check = 1
74         Else
75         three_must_check = 0: three_other_check = 0
76         End If
77         If ii_flag = 1 And ii_must = 1 Then
78         ii_must_check = 1
79         ElseIf ii_flag = 1 And ii_must = 3 Then
81         ii_other_check = 1
82         Else
83         ii_must_check = 0: ii_other_check = 0
84         End If
85         If ds_flag = 1 And ds_must = 1 Then
86         ds_must_check = 1
87         ElseIf ds_flag = 1 And ds_must = 3 Then
89         ds_other_check = 1
90         Else
91         ds_must_check = 0: ds_other_check = 0
92         End If
93         If g_score > g_threshold And g_must = 1 Then
94         g_must_check = 1
95         ElseIf g_score > g_threshold And g_must = 3 Then
97         g_other_check = 1
98         Else
99         g_must_check = 0: g_other_check = 0
100         End If
101         If acquisitions_score > acquisitions_threshold And acquisitions_must = 1 Then
102         acquisitions_must_check = 1
103         ElseIf acquisitions_score > acquisitions_threshold And acquisitions_must = 3 Then
105         acquisitions_other_check = 1
106         Else
107         acquisitions_must_check = 0: acquisitions_other_check = 0
108         End If
109         If overdistribution_score > overdistribution_threshold And overdistribution_must = 1 Then
110         overdistribution_must_check = 1
111         ElseIf overdistribution_score > overdistribution_threshold And overdistribution_must = 3 Then
113         overdistribution_other_check = 1
114         Else
115         overdistribution_must_check = 0: overdistribution_other_check = 0
116         End If
221         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev Then    'acquisitions score overall average return
222         sumall_combination_1y = sumall_combination_1y + data_set(i, 19)
223         sumall_combination_2y = sumall_combination_2y + data_set(i, 20)
224         sumall_combination_3y = sumall_combination_3y + data_set(i, 21)
225         End If
117         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev Then
118         sumall_combination_1 = sumall_combination_1 + data_set(i, 23)
119         sumall_combination_2 = sumall_combination_2 + data_set(i, 24)
120         sumall_combination_3 = sumall_combination_3 + data_set(i, 25)
121         sumall_combination_4 = sumall_combination_4 + data_set(i, 26)
122         sumall_combination_5 = sumall_combination_5 + data_set(i, 27)
123         sumall_combination_6 = sumall_combination_6 + data_set(i, 28)
124         sumall_combination_7 = sumall_combination_7 + data_set(i, 29)
125         sumall_combination_8 = sumall_combination_8 + data_set(i, 30)
126         sumall_combination_9 = sumall_combination_9 + data_set(i, 31)
127         sumall_combination_10 = sumall_combination_10 + data_set(i, 32)
128         sumall_combination_11 = sumall_combination_11 + data_set(i, 33)
129         sumall_combination_12 = sumall_combination_12 + data_set(i, 34)
130         sumall_combinationspx_1 = sumall_combinationspx_1 + data_set(i, 36)
131         sumall_combinationspx_2 = sumall_combinationspx_2 + data_set(i, 37)
132         sumall_combinationspx_3 = sumall_combinationspx_3 + data_set(i, 38)
133         sumall_combinationspx_4 = sumall_combinationspx_4 + data_set(i, 39)
134         sumall_combinationspx_5 = sumall_combinationspx_5 + data_set(i, 40)
135         sumall_combinationspx_6 = sumall_combinationspx_6 + data_set(i, 41)
136         sumall_combinationspx_7 = sumall_combinationspx_7 + data_set(i, 42)
137         sumall_combinationspx_8 = sumall_combinationspx_8 + data_set(i, 43)
138         sumall_combinationspx_9 = sumall_combinationspx_9 + data_set(i, 44)
139         sumall_combinationspx_10 = sumall_combinationspx_10 + data_set(i, 45)
140         sumall_combinationspx_11 = sumall_combinationspx_11 + data_set(i, 46)
141         sumall_combinationspx_12 = sumall_combinationspx_12 + data_set(i, 47)
142         cnt_combination_prof = cnt_combination_prof + 1
143         End If
144         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 19) > 0 Then       'combination change 1y winners
145         sumall_combination_wnrs_1y = sumall_combination_wnrs_1y + data_set(i, 19)
146         cnt_combination_wnrs_1y = cnt_combination_wnrs_1y + 1
147         End If
148         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 19) < 0 Then       'combination change 1y losers
149         sumall_combination_lsrs_1y = sumall_combination_lsrs_1y + data_set(i, 19)
150         cnt_combination_lsrs_1y = cnt_combination_lsrs_1y + 1
151         End If
152         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 20) > 0 Then       'combination change 2y winners
153         sumall_combination_wnrs_2y = sumall_combination_wnrs_2y + data_set(i, 20)
154         cnt_combination_wnrs_2y = cnt_combination_wnrs_2y + 1
155         End If
156         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 20) < 0 Then       'combination change 2y losers
157         sumall_combination_lsrs_2y = sumall_combination_lsrs_2y + data_set(i, 20)
158         cnt_combination_lsrs_2y = cnt_combination_lsrs_2y + 1
159         End If
160         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 21) > 0 Then       'combination change 3y winners
161         sumall_combination_wnrs_3y = sumall_combination_wnrs_3y + data_set(i, 21)
162         cnt_combination_wnrs_3y = cnt_combination_wnrs_3y + 1
163         End If
164         If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 21) < 0 Then       'combination change 3y losers
165         sumall_combination_lsrs_3y = sumall_combination_lsrs_3y + data_set(i, 21)
166         cnt_combination_lsrs_3y = cnt_combination_lsrs_3y + 1
167         End If
168 Next i
Application.ScreenUpdating = True
End Sub

 

[MoshiM]

Re: How to implement a dynamic "lookback" period?

Thanks for this. I was struggling to implement your specific example but thought I could achieve the desired result by having a second loop for the flags I want to have a "lookback period" for? However, I don't think this is working - my code runs okay but my flags with the lookback period aren't working correctly (the flag is triggered too many times / when it shouldn't be). I think the issue is in line 37 below; can anyone assist? (essentially the reason for the lookback period is that for these flags I want them triggered if they were "true" in any of the periods in the "lookback timeframe") Thanks

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

Is this line correct? For example let's say you had 16k rows in your array with a look back period of 1k, wouldn't this loop from 1001 to 16k ?

 

[cfadr]

Re: How to implement a dynamic "lookback" period?

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

Is this line correct? For example let's say you had 16k rows in your array with a look back period of 1k, wouldn't this loop from 1001 to 16k ?

Yes I don’t think that is my problem - I am simply starting at 1,001 in order to leave 1k rows of data before starting my calc (i.e. if I don’t leave 1,000 before then I can’t lookback 1,000?)

 

[MoshiM]

Re: How to implement a dynamic "lookback" period?

Yes I don’t think that is my problem - I am simply starting at 1,001 in order to leave 1k rows of data before starting my calc (i.e. if I don’t leave 1,000 before then I can’t lookback 1,000?)

Can you describe what your macro is supposed to do (With details) ? Adding comments to your code may help as well.

 

[MoshiM]

Re: How to implement a dynamic "lookback" period?

Does this help any?

Sub [COLOR=#ff0000]Generic_LookBack[/COLOR]()

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

Initial_Start=501

[COLOR=#0000ff]'Assume we are on row 501 of an array[/COLOR]
[COLOR=#0000ff]'if we want to find if a condition was met in rows 1 to 500 then ie: the past 500 rows then[/COLOR]

For X = Initial_Start - LookBack_Period To Initial_Start - 1 ' ie  loop 1 to 500 and check if condition is met

    If Some_Condition Then  'ex:   Generic_Data(x,15) = 200
        
        Condition_Met = True  
        
    End If

Next X

End Sub

 

[cfadr]

Re: How to implement a dynamic "lookback" period?

Okay so I have simplified down the code and added comments to indicate what exactly I am trying to do and where I think I am going wrong; grateful for any suggestions -

Sub combination_testing()            'Testing financial returns of initiation positions when certain conditions ("flags") are present. There are three "Flags" and they are "on" if their condition was satisfied at any point within the "lookback period"
' On Error GoTo ErrHandler
1 Application.ScreenUpdating = False
2 Dim data_set As Variant
3 data_set = Sheets("Data").Range("A4:AV7500") 'Loading the data set
4 Dim i As Long
5 one_change_must = Sheets("Control").Range("D24")         'first flag - if the value in D24 is 1 then this flag "must" be on for a position to be initiated, if the value is 3 then it contributes to a count of "other flags"
6 two_change_must = Sheets("Control").Range("D25")         'second flag - if the value in D25 is 1 then this flag "must" be on for a position to be initiated, if the value is 3 then it contributes to a count of "other flags"
7 three_change_must = Sheets("Control").Range("D26")       'third flag - if the value in D25 is 1 then this flag "must" be on for a position to be initiated, if the value is 3 then it contributes to a count of "other flags"
8 must_checks_total = Sheets("Control").Range("D21")       'the total number of flags that "must" be on
9 other_checks_total = Sheets("Control").Range("D22")      'the minimum number of "other flags" that must be on
10 lookback_period = Sheets("Control").Range("F24")        'the lookback period - so the flags will be "on" if their condition was satisfied within this period of time; e.g. if this is set to 4 then the loop needs to check if the conditions was satisfied in any of the last 4 periods
11 For i = lookback_period + 1 To UBound(data_set, 1)      'starting the main loop in the time series; it starts down the number of periods equal to the lookback period so that there are enough prior periods to "lookback" into
12          For j = i - lookback_period To i 	           'this was my attempt at implementing a lookback period for the flags but I don't think it has worked; I'm trying to say "for each row within the lookback period until the current row i"
13          one_change = data_set(j, 15)	  	   'this is the first condition/flag value
14	    two_change = data_set(j, 16)	 	   'this is the second condition/flag value
15	    three_change = data_set(j, 17)	 	   'this is the third condition/flag value
16              If one_change = 1 Then    		   'if the first condition equals 1 (i.e., was satisfied in any of the rows in the lookback period) Then
17              one_change_flag = 1                        'the first flag is on (i.e., equal to one) - THIS IS WHERE I THINK I AM GOING WRONG; the number of incidents of this flag being on are too many; my method of having a second loop to cater for the lookback period is just resulting in double counting (actually counting *5 - once for each period of the lookback loop and once for the main loop
18             one_change_flags = one_change_flags + 1    'I just inserted this row to check the number of flags that were being created which is how I saw it is too many 
19              End If
20              If two_change = 1 Then
21              two_change_flag = 1
22              End If
23              If three_change = 1 Then
24              three_change_flag = 1
25              End If
26              If ii > ii_threshold Then
27              ii_flag = 1
28              End If
29              If ds = 1 Then
30              ds_flag = 1
31              End If
32          Next j
33         If one_change_flag = 1 And one_change_must = 1 Then      'this part is  just saying if the first flag is on and was determined to be a "must" flag (i.e., a flag that must be present) then it contributes to the count of the flags that must be present
34         one_must_check = 1
35         ElseIf one_change_flag = 1 And one_change_must = 3 Then   'else if it is on and wasn't a "must" flag then it contributes to the count of "other" flags
37         one_other_check = 1
38         Else
39         one_other_check = 0: one_must_check = 0
40         End If
41         If two_change_flag = 1 And two_change_must = 1 Then
42         two_must_check = 1
43         ElseIf two_change_flag = 1 And two_change_must = 3 Then
45         two_other_check = 1
46         Else
47         two_must_check = 0: two_other_check = 0
48         End If
49         If three_change_flag = 1 And three_change_must = 1 Then
50         three_must_check = 1
51         ElseIf three_change_flag = 1 And three_change_must = 3 Then
52         three_other_check = 1
53         Else
54         three_must_check = 0: three_other_check = 0
55         End If
56         If (one_must_check + two_must_check + three_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_checkheck) >= other_checks_total Then   'if there are the right amount of "must" flags and enough "other" flags then add the values in the row
57         sumall_combination_1y = sumall_combination_1y + data_set(i, 19)
58         End If
59 Next i


' ErrHandler:
' MsgBox "An error occurred in line: " & Erl
Application.ScreenUpdating = True
End Sub

the data is a vertical time series (what follows is just a sample, not the exact positioning in the array) like so -

[TABLE="width: 576"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]one_change[/TD]
[TD]two_change[/TD]
[TD]three_change[/TD]
[/TR]
[TR]
[TD]31-Dec-14[/TD]
[TD]MSFT[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31-Jan-15[/TD]
[TD]MSFT[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]28-Feb-15[/TD]
[TD]MSFT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31-Mar-15[/TD]
[TD]MSFT[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[MoshiM]

Re: How to implement a dynamic "lookback" period?

Okay so I have simplified down the code and added comments to indicate what exactly I am trying to do and where I think I am going wrong; grateful for any suggestions -

Your loop seems fine assuming you want to include the current row of the array in the lookback period. This would mean that the problem is with your conditions. Your issue may be that for some of your variables, you don't change their values back to what they were originally which ensures that if it is true in any of the lookback periods then it will remain true in any of the following periods.

As an example :

16              If one_change = 1 Then             'if the first condition equals 1 (i.e., was satisfied in any of the rows in the lookback period) Then

17                  one_change_flag = 1                        'the first flag is on (i.e., equal to one) - THIS IS WHERE I THINK I AM GOING WRONG; the number of incidents of this flag being on are too many; my method of having a second loop to cater for the lookback period is just resulting in double counting (actually counting *5 - once for each period of the lookback loop and once for the main loop
18                  one_change_flags = one_change_flags + 1    'I just inserted this row to check the number of flags that were being created which is how I saw it is too many


19              End If

You set the value of one_change_flag to 1 but once you are done with the logic for that lookback period, the value for that variable is still retained and will evaluate true for every instance of 'if one_change_flag=1' following the first incident.

If this is intentional why do you need the lookback period when you can just loop the rows sequentially?

 

[cfadr]

Re: How to implement a dynamic "lookback" period?

Thanks. If the lookback period was fixed then I would not have a problem because, for example if it was fixed at 4 I would just do something like -

one_change_curr = data_set(i, 15)
one_change_prev = data_set(i - 1, 15)
one_change_prev2 = data_set(i-2, 15)
one_change_prev3 = data_set(i-3, 15)
If one_change_curr = 1 Or one_change_prev = 1 Or one_change prev2 = 1 Or one_change_prev3 = 1 Then
one_change_flag = 1

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