Home >> Support Board

Support Board

Date/Time: Sat, 04 May 2024 11:32:30 +0000

How to Post a Message for Support

[Programming Help] - Excel error & GetCorrespondingMatch multiple criteria selection

View Count: 1665

[2016-07-13 16:03:04] #1 [Go To First Post]
User025902 - Posts: 8
Hello, I encountered few problems I can't solve when building a Spreadsheet Study which would plot COT data to charts. 1) Do you have plans for implementing COT data into default Studies? This would remove all the problems at once and reading through forums I believe I am not the only one waiting for this important piece. 2) I am able to get it working using Excel and array functions (Ctrl+Shift+Enter for confirming them). Alas, whenever the study is recalculated SierraChart automatically rewrites the functions to their default state (plain Enter) so they stop working. Is there a solution? 3) I wanted to replicate my Excel formulas in New Spreadsheets hoping this would solve the issue. However SCH plots Date in YYYY-MM-DD format. The COT data from cftc.gov are either in YYMMDD or DD.MM.YYYY format. Now Excel doesn't have problem with this as the information is the same, only format changes. But Sierra recognizes only characters and can't connect those formats. Is there any workaround without manually changing the source date (it gets updated weekly so this would be very inconvenient)? 4) GetCorrespondingMatch function is showing #VALUE error when using different sheet in the same spreadsheet as a source value. Why? (Looking for text value, works within the same Sheet) 5) I need to get match based on 2 criteria (Market Name and Date. This match happens only once per day per market thus only one row is valid.). In Excel this is done by multiplicating both conditions in "search" parameter of MATCH function and setting "1" as lookup value: MATCH(1,(A1=C:C)*(B1=D:D),0) If match is found for both A1 and B1 on the same row C, resp. D, only then function gives the row number which is then used as row parameter for Index. By the way GetCorrespondingMatch works as it uses only one collumn for search I have hard time coming up with a solution. Any suggestions would be greatly appreciated. Thank you

[2016-07-13 17:10:14] #2 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
We will only be responding to the questions which are within the scope of our support. This is the only one: 4) GetCorrespondingMatch function is showing #VALUE error when using different sheet in the same spreadsheet as a source value. Why? (Looking for text value, works within the same Sheet) We will check on this and see what the problem is. Everything else is not within the scope of our support. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, change to the Teton service: Sierra Chart Teton Futures Order Routing

[2016-07-13 18:18:56] #3 [Go To First Post]
User025902 - Posts: 8
Thank you. So there is no way to get match based on 2 criteria using SCH's native Spreadsheet?

[2016-07-13 18:30:15] #4 [Go To First Post]
Sawtooth - Posts: 3993
2) Array functions are not supported by Sierra Chart spreadsheets. Workarounds are issue-specific, but usually involve using formulas in multiple columns or cells. 3) Sierra Chart spreadsheets use the conventional Serial DateTime format, same as Excel, where the date is an incrementing integer (starting at 1 on 1/1/1900), and the time of day is a decimal. Use formulas in spare columns to convert. 5) Use a separate column (e.g. D) to assign a 1 in the row when both A1 and B1 are TRUE, then use INDEX/MATCH to return the value in the other column (e.g. E): =INDEX(E1:E1000,MATCH(1,D1:D1000,0),1)

[2016-07-13 18:44:14] #5 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
Regarding the answer given in post #2, this is what we determined: GetCorrespondingMatch is documented that searching for a text value will return an error. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, change to the Teton service: Sierra Chart Teton Futures Order Routing

[2016-07-13 20:42:36] #6 [Go To First Post]
User025902 - Posts: 8
Thank you for the info, will try suggested workarounds tomorrow, will post the results.

[2016-08-10 18:34:38] #7 [Go To First Post]
User025902 - Posts: 8
OK sorry for the delay, here is how I overcame the issue: I used Rows AA+ for the array Excel function so the chart update wouldn't convert the formulas to standard format. Then I used regular formulas for the charting rows with arguments from those array functions. The only problem now is the speed - it takes a whole minute for the tab to recalculate.

[2016-08-10 23:16:59] #8 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
A minute is a long time. If you provide us the spreadsheet file we can test it and see if there is any improvement we can make. Follow these instructions here to attach the spreadsheet file: https://www.sierrachart.com/index.php?page=PostingInformation.php#AttachFile Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, change to the Teton service: Sierra Chart Teton Futures Order Routing

[2018-03-25 19:38:50] #9 [Go To First Post]
Bedhog - Posts: 168
Hello Team, This function is working in Excel and I need a hand to make it work in the SC Spreadsheet. =INDEX(A1:A100, MATCH(TRUE, INDEX(A1:A100<$J$48, 0 ), ) ) I am looking to return the first value in ColumnA that is less than the value stored in $J$48 Excel returns 64.95 which is correct. SC is returning #VALUE!. $J$48 = 65.04 ColumnA 65.2 65.2 65.2 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.1 65.05 64.95 64.95 64.95 64.95 64.86 64.86 64.86 64.86 64.86

[2018-03-26 01:34:31] #10 [Go To First Post]
Sawtooth - Posts: 3993
Try this example: In a Formula Column, e.g. P3: =E3<$J$48 In another Formula Column, e.g. Q3, or in a single non-repeating cell: =INDEX(E3:E1002,MATCH(1,P3:P1002,0),1)

To post a message in this thread, you need to log in with your Sierra Chart account: