Support Board
Date/Time: Sat, 23 Nov 2024 05:43:10 +0000
[Programming Help] - Challenging Spreadsheet operation
View Count: 209
| [2024-09-02 18:17:44] |
| owendonk - Posts: 54 |
|
Hello, I'm trying to take a set of values and extract subsets that meet certain criteria. Specifically, I want to input the values in the range K3:AU3, remove repeat values and values below 15000, and identify all subsets with a range < 30. If multiple subsets include the same number, only include the subset with the most numbers and the smallest range. Then I want to output the highest and lowest values of each subset to formula columns so I can autodraw a rectangle for each range. Here is an example in python that mostly works. I may need to write it in ACSIL and make a custom study, but I don't know C++. Any help is greatly appreciated. The output of this python is [19164.75, 19145.55] [19593.975, 19600.969401041668, 19611.313802083336, 19590.625] [18367.946, 18369.6645] [19690.438802083336, 19695.675]. Ideally this would all be done in the spreadsheet with spreadsheet logic. data = [17346, 18735.525, 19164.75, 19593.975, 20983.5, 14136.5, 16752.054, 17560, 18367.946, 20983.5, 17346, 18369.6645, 18685.875, 19002.0855, 20025.75, 19690.438802083336, 19759.219401041668, 19532.188802083336, 19442.719401041668, 19848.688802083336, 19373.938802083336, 19600.969401041668, 19611.313802083336, 19590.625, 19621.658203125, 63.27272920927062, 59.38859038604226, 19145.55, 19255.575, 19475.625, 19695.675, 19805.7] # Remove values smaller than 10000 data = [x for x in data if x >= 10000] # Remove duplicate values unique_data = [] for x in data: if x not in unique_data: unique_data.append(x) data = unique_data # Identify subsets with range smaller than 30 subsets = [] used_numbers = set() for i in range(len(data)): if data in used_numbers: continue subset = [data] for j in range(i + 1, len(data)): if data[j] not in used_numbers and max(subset + [data[j]]) - min(subset + [data[j]]) <= 30: subset.append(data[j]) if len(subset) > 1: subsets.append(subset) used_numbers.update(subset) # Filter subsets to include only the one with the most numbers and smallest range if multiple subsets have the same numbers unique_subsets = {} for subset in subsets: subset_key = tuple(sorted(subset)) if subset_key not in unique_subsets or (len(subset) > len(unique_subsets[subset_key]) or (len(subset) == len(unique_subsets[subset_key]) and (max(subset) - min(subset) < max(unique_subsets[subset_key]) - min(unique_subsets[subset_key])))): unique_subsets[subset_key] = subset # Output all unique subsets that meet the criteria for subset in unique_subsets.values(): print(subset) Date Time Of Last Edit: 2024-09-02 18:19:06
|
| [2024-09-06 20:14:33] |
| ForgivingComputers.com - Posts: 960 |
|
Definitely beyond the capabilities of the Sierra Spreadsheets, but not for ACSIL.
|
| [2024-09-07 00:15:27] |
| ondafringe - Posts: 283 |
|
That's an interesting problem. So you have a bunch of numbers strung horizontally along the same row, from K3 to AU3. The first two requirements seem pretty straight forward and can be solved with one formula copied to multiple cells, but how do you determine which numbers to include in a subset (I can't interpret python)? And you indicated you want to remove all numbers less than 15,000, but your example used 10,000. Was that a mistake, or does that number need to be thought of as a variable, instead of a constant? Date Time Of Last Edit: 2024-09-12 15:49:56
|
To post a message in this thread, you need to log in with your Sierra Chart account: