How to use scan to obtain cumulative value of a function

Community Support
,
1

https://learninghub.kx.com/forums/topic/how-to-use-scan-to-obtain-cumulative-value-of-a-function

Hello everyone, I am working on the backtest of a trading strategy with volume-based signals. The strategy is quite complex and starts with tick-by-tick data analysis, looking for certain volume reversal patterns. Specifically, I am filtering significant levels where, if a reversal signal occurs, I take it into consideration. The levels are taken from a daily volume profile, and all levels above a certain volume (3000 volumes in the case of 6E) are considered significant levels. I am summarizing and sharing a sample dataset with the levels, where for each day (column "values") there is a list of all significant levels for that specific day.

EXAMPLE DATASET ON 6E FUT

tab: ([] date: (2024.01.02; 2024.01.03; 2024.01.04; 2024.01.05; 2024.01.08; 2024.01.09; 2024.01.10; 2024.01.11; 2024.01.12; 2024.01.15; 2024.01.16; 2024.01.17; 2024.01.18);
           high: (1.10785; 1.09985; 1.1004; 1.1031; 1.101; 1.09965; 1.10035; 1.10405; 1.10155; 1.09955; 1.0979; 1.09115; 1.09325);
            low: (1.09725; 1.0926; 1.09475; 1.0908; 1.09535; 1.0941; 1.0953; 1.0958; 1.09645; 1.0961; 1.08895; 1.0871; 1.0872);
         levels: (1.0981 1.09815 1.09835 1.0984 1.0986 1.0987 1.09875 1.0988 1.09885 1.0989 1.09895 1.099 1.0991 1.0993 1.09935;
                  1.09385 1.0939 1.09395 1.0948 1.09485 1.0949 1.09495 1.095 1.09505 1.0951 1.09515 1.0952 1.09525 1.0953 1.09535;
                  1.0974 1.09745 1.0975 1.09755 1.0976 1.09765 1.0977 1.09775 1.0978 1.09785 1.0979 1.09795 1.098 1.09805 1.0981;
                  enlist 1.0977;
                  1.09695 1.0977;
                  1.0956 1.0958 1.09635 1.0964 1.09645 1.0965 1.09655 1.0966 1.09665 1.09695 1.097 1.09705 1.0971 1.09715 1.0972;
                  1.097 1.0975 1.0976 1.09765 1.0977 1.0997 1.09975 1.0998 1.09985 1.09995;
                  1.0973 1.09735 1.0974 1.09745 1.0975 1.09755 1.0978 1.09785 1.0979 1.09795 1.0987 1.09885 1.099 1.09905 1.0991;
                  1.09835 1.0991 1.0995 1.10035 1.1004 1.10055 1.10085;
                  enlist 1.0977;
                  1.0897 1.08975 1.0898 1.08985 1.0899 1.08995 1.09 1.09005 1.0901 1.09015 1.0902 1.09025 1.0903 1.09035 1.0904;
                  1.08825 1.0883 1.08835 1.0884 1.08845 1.0885 1.08855 1.0886 1.08865 1.0887 1.08875 1.0888 1.08885 1.0889 1.08895;
                  1.0881 1.08815 1.0882 1.08825 1.0883 1.0884 1.08845 1.0885 1.08855 1.0886 1.08865 1.0887 1.08875 1.0889 1.08895));

Starting from this point, I would like to calculate in a new column a cumulative list that carries forward significant values over time, so they remain available even, for example, months after being generated, as long as the price hasn’t touched them.

I created a function that checks whether, day by day, the daily high and low have touched the levels in the list. If the levels are not touched (i.e., outside the high and low range), they should remain. 

filterLevels:{[row]
    // Define the conditions
    wHigh:{[x;y] x > y};
    wLow:{[x;y] x < y};
    // Get the levels, high, and low from the row
    high: row`high;
    low: row`low;
    prev_levels: row`prev_levels;
    // Determine matches
    matchesHigh: wHigh[prev_levels; high];
    matchesLow: wLow[prev_levels; low];
    // Filter the elements that match the conditions
    list_matched_high: prev_levels where matchesHigh;
    list_matched_low: prev_levels where matchesLow;
    // Combine the lists and remove duplicates
    final_list: distinct list_matched_low, list_matched_high;
    // Return the sorted final list
    :asc final_list;
}
tab: update prev_levels: prev levels from tab;[
tab: update filtered_levels: filterLevelsByPercent each tab from tab;

I can calculate this day by day, applying the function I mentioned. What I would like to achieve is a column with cumulative values day after day, following the described logic. I have tried various approaches, and the correct one should be using "SCAN"

(,)function[dataset]
How do I apply the function using this logic? So, to check the high and low and generate a column with cumulative values?

2

To create the filtered_levels column from the "tab" table I used update filtered_levels:{x where not x within (y;z)}'[prev levels;low;high] from `tab which reproduces your screenshot.

Then I wasnt sure what you meant by cumulative? Did you mean that you wanted to carry forward

the unique filtered_levels values for each day, like a forward fills (except applied to a list)?

You could use scan to do this using the below

update cumulative:{distinct x,y} scan filtered_levels from tab

3

Thank you for your response. My problem is that in the case of 2024.01.04, the low is below the levels 1.09385, 1.0939, 1.09395, so the cumulative list should remove those values from the 2024.01.05, according to the function's logic. However, if those levels were not crossed upwards or downwards, they should be maintained. This is what I'm not able to achieve.

To recap:

The column of cumulative values must accumulate the list of levels, but when high or low exceeds a certain level, it must be removed from the list. If a level generated on the first trading day is never touched, then it must remain in the 'cumulative' list until it is touched.

4

Ok so you are saying that you need to cary forward distinct values and apply additional rules to the result? For this you could try using the form v[x;y;z], see https://code.kx.com/q/ref/accumulators/#ternary-values This will allow you to pass in the columns filtered_levels, low and high when accumulating, so that you can apply additional rules. Below is a simple example using a mock table to demonstrate

q)t:( filtered_levels:((5.0;6.0;8.0;12.0;13.0);(7.0;10.0;11.0);(4.0;5.0;7.0;8.0;9.0)); low:5.0 7.0 4.0;high:13.0 11.0 9.0 )

q)v:{[x;f;l;h] c:distinct x,f;c where c within (l;h)}

q)update cumulative:v[();filtered_levels;low;high] from t

Here the "v" function is not just joining consecutive filtered_levels (and taking the distinct), but also applying logic to the result using low and high values.

5

thank you very much for your time.

Like this is what i need:

v:{[x;f;l;h] c:distinct x,f;c where not c within (l;h)};

For example, here (from the photos uploaded), the levels (above 3000 volumes, previously filtered) are no longer reached for some time, so they must remain in the cumulative list of naked levels. The first time the price crosses these levels, they should be removed from the cumulative list.

Why do I want to identify these levels? Because I want to “trigger” potential reversal signals only if they occur within a range (with a margin) around these naked levels.

Thank you again for your guidance and your time.

Giulio