Support Board
Date/Time: Sat, 28 Dec 2024 22:03:28 +0000
Basic Examples of How You Use INDEX, MATCH / Spreadsheet Performance Improvements Now Available
View Count: 5711
| [2015-12-16 08:10:32] |
| |
|
We want some basic examples of how you use the following functions: INDEX, MATCH, HLOOKUP, VLOOKUP We are certain there is a more orderly, logical and efficient way to accomplish the final result through a single function. ---- Refer to #20 below for information about the latest major performance improvements. 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 Date Time Of Last Edit: 2016-01-07 01:43:41
|
| [2015-12-16 15:06:36] |
| Sawtooth - Posts: 4143 |
|
In Excel, HLOOKUP and VLOOKUP require the lookup row/column to be in ascending order, hence the need for INDEX/MATCH. I will post again with examples of how I use (or would use) these functions. |
| [2015-12-16 17:27:54] |
| |
|
In Excel, HLOOKUP and VLOOKUP require the lookup row/column to be in ascending order,
This is so easy for us to change. And we also have to look at whether this is even a requirement in the SC implementation of these. These functions can easily be designed to work with data which is not in any particular order. These functions can also be given a hint flag indicating if the data is in a particular order to optimize them. However, our objective, is to create new functions which are purpose built. The objective is to create the functions that users require which are efficient, logical and orderly. The objective is not Excel compatibility. If anything, we should have a complete review of all functions, and optimize them for logic and efficiency. It only makes sense that SC Spreadsheets are implemented as logically and as efficiently as they can be and at most are "Excel like". 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 Date Time Of Last Edit: 2015-12-16 17:30:58
|
| [2015-12-18 05:07:46] |
| Sawtooth - Posts: 4143 |
|
I just now tried VLOOKUP in the New Spreadsheets and it doesn't work, but the same formula does in Excel. VLOOKUP is not as flexible/useful as INDEX/MATCH because 1) the table array needs to be in ascending order. 2) the lookup column needs to be to the left of the return column. 3) You can't use it to find, e.g. the max in a range like you can with INDEX/MATCH: =MAX(E3:INDEX(E3:E5,MATCH(1,H3:H5,0),1)) I can't think of a possibility where I'd use VLOOKUP or HLOOKUP in a spreadsheet study. I use INDEX/MATCH in these ways: 1) to return a value from an unsorted column based on a lookup value 2) to find the MAX or MIN in a range 3) to SUM or AVERAGE values in a range 4) using double INDEX/MATCH:INDEX/MATCH to do the same in a dynamic range Here is a link to two links of two examples: http://www.sierrachart.com/supportboard/showthread.php?t=31402&highlight=add I'll post again about the inefficiencies of INDEX/MATCH in the New Spreadsheets after I build some test spreadsheets. |
| [2015-12-18 08:48:36] |
| |
|
Thank you for the information. We will start work on the new functions and ask any questions if we need them. Regarding the inefficiency that you see, we just want to know if you notice an improvement with version 1341. This is all the information we need. 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 |
| [2015-12-18 13:56:50] |
| Sawtooth - Posts: 4143 |
|
Regarding the inefficiency that you see, we just want to know if you notice an improvement with version 1341
v1341 showed no noticeable improvement.
|
| [2015-12-22 02:08:12] |
| |
|
The basic problem of inefficiency with New Spreadsheets when used by the Spreadsheet Study has now been determined. The senior engineer has looked at this and has determined the cause. Basically with how data was being outputted to Spreadsheets and due to some other optimizations that were being done, all formulas were getting recalculated every chart update. We are solving this now and it will be out before the end of this month (December 2015) There is also another performance improvement we will be making, and we will be adding specialized functions to do the kind of processing you are using INDEX and MATCH for and also provide optimization flags to further improve performance. While we do not want to mislead you until the work is done, based upon what we see so far, you are going to be absolutely stunned at the massive performance improvement you will see. This will also show that New Spreadsheets have significantly higher performance than Old Spreadsheets. 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 |
| [2015-12-25 01:37:34] |
| |
|
The latest revision of 1344 has the initial performance improvements but there is more to do.
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 |
| [2015-12-27 01:10:39] |
| Sawtooth - Posts: 4143 |
|
v1344 shows no noticeable improvement.
|
| [2015-12-27 04:26:45] |
| |
|
1. There is a lot more work we are doing related to New Spreadsheet optimization. 2. The initial revision of 1344 did not have the improvements. They were not released until the second revision. So you may not have all of the improvements and there is more to release from us. 3. Depending upon how you did the test, you may not notice an improvement since the MATCH function itself has not been optimized. We are creating a new optimized function. 4. Provide us the spreadsheet you are testing as long as it is not too complicated. 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 |
| [2015-12-27 05:11:14] |
| Sawtooth - Posts: 4143 |
|
Attached are the comparison spreadsheet studies. This is a severe test using cascading formulas in row 1 referenced by an INDEX/MATCH formula in row 3 of each of 60 columns for 1000 rows. Change the value in K1 to affect the entire sheet. The New Spreadsheets sheet takes about 100 times longer to recalculate the array than the Old Spreadsheets sheet, e.g. 100 seconds vs 1 sec. |
 scss speed test 2.scss - Attached On 2015-12-27 05:08:02 UTC - Size: 160.15 KB - 439 views scwbf speed test 2.scwbf - Attached On 2015-12-27 05:09:03 UTC - Size: 56.7 KB - 380 views |
| [2015-12-27 07:21:52] |
| |
|
The main reason this is so slow is because the MATCH function has no optimization. The optimization will drastically increase the speed of it.
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 |
| [2015-12-27 08:07:32] |
| |
|
This is the new function that we are proposing to add: GetCorrespondingMatch(SearchColumn, SearchNumber, NearestOrExact, SearchRangeOrderingFlag, ReturnResultColumn, ReturnResultRowOffset)
SearchColumn: The column range to be searched. SearchNumber: The number to search for in the SearchColumn. NearestOrExact: 0 = exact match. 1 = nearest match. SearchRangeOrderingFlag: 1 = SearchColumn values are ascending. (Lowest numbered row has the lowest numbers. Highest numbered row has higher numbers). 0 = SearchColumn is unordered (Has negative performance impact when used). -1 = SearchColumn values are descending. (Lowest numbered row has the highest numbers. Highest numbered row has lowest numbers). ReturnResultColumn: The column range where a reference will be returned for the corresponding row where the match was found. ReturnResultRowOffset: A positive or negative row offset for the row returned by ReturnResultColumn. Also in your example Spreadsheet you are performing an exact match and according to the definition of the MATCH function we would assume that there cannot be any optimization because the elements are assumed to not be ordered. So we are not sure why Old Spreadsheets are performing such extensive searches so fast. There must be an assumption that the elements are ordered. Perhaps that is automatically detected. Column K is clearly ordered. We will have to test Old Spreadsheets and see that if Column K is randomly ordered, that it would be so fast. It does not make sense that it would be. Otherwise, there is some unknown optimization that Old Spreadsheets is using. 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 Date Time Of Last Edit: 2015-12-27 08:08:30
|
| [2015-12-27 15:45:25] |
| Sawtooth - Posts: 4143 |
|
If you change K3 to =F3, which makes it not ordered, the New vs Old is about the same. Will GETCORRESPONDINGMATCH be able to be used to like this: =AVERAGE(GETCORRESPONDINGMATCH(...):GETCORRESPONDINGMATCH(...)) to locate 2 rows and average everything in between in the ReturnResultColumn? Will GETCORRESPONDINGMATCH be able to use TRUE as a SearchNumber? What will GETCORRESPONDINGMATCH return if there is a duplicate SearchNumber in the SearchColumn? Will there be row range parameters within SearchColumn and ReturnResultColumn? GETCORRESPONDINGMATCH is lengthy. How about VGET instead. Maybe it would be easier to optimize MATCH than to build a new function. |
| [2015-12-27 18:53:03] |
| |
|
=AVERAGE(GETCORRESPONDINGMATCH(...):GETCORRESPONDINGMATCH(...)) Will GETCORRESPONDINGMATCH be able to use TRUE as a SearchNumber? What will GETCORRESPONDINGMATCH return if there is a duplicate SearchNumber in the SearchColumn?
The first corresponding match with the lowest numbered index in the range. Will there be row range parameters within SearchColumn and ReturnResultColumn? MATCH will be optimized as well. 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 Date Time Of Last Edit: 2015-12-27 21:55:36
|
| [2015-12-27 21:43:00] |
| |
|
Also when testing the Spreadsheet you provided, when we edited a cell and pressed the >> button to expand the editing window, we noticed that the spreadsheet froze for a long while apparently due to recalculations. That should not happen and we will correct that. We should have most of the work done in the next week or two. 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 |
| [2015-12-27 21:57:19] |
| |
|
This is good to know and does clearly indicate that the difference is that a binary search is being performed by Old Spreadsheets: If you change K3 to =F3, which makes it not ordered, the New vs Old is about the same.
We should also be able to improve performance even with an unordered search making New Spreadsheets much faster in this area compared to Old spreadsheets. 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 Date Time Of Last Edit: 2015-12-27 21:57:46
|
| [2015-12-27 22:01:34] |
| |
|
This is the kind of search that we will be using for the MATCH and GETCORRESPONDINGMATCH functions: https://en.wikipedia.org/wiki/Binary_search_algorithm There is a lot to develop Spreadsheets and this was something that that was not initially contemplated. Performance has always been number one, but this was an oversight and sometimes there are unexpected triggers for unnecessary calculations which renders all of the performance improvements of no value and we are determining and solving these unnecessary calculation triggers. 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 Date Time Of Last Edit: 2015-12-28 07:29:55
|
| [2015-12-30 03:44:51] |
| |
|
Regarding spreadsheet performance, today we discussed some significant design changes to internally simplify spreadsheets and improve performance. There are many changes planned and all of this will take some time. All of this will be done as soon as possible though. In the next few days, we will do our best to get the new optimized GetCorrespondingMatch function ready. We are not sure how much of a performance gain this is going to give because we also realize there is a significant amount of time doing other dependent processing. However, it is our objective to get the processing time down to an absolute minimum. So this may not happen immediately but we will do our best to get there as soon as possible. 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 Date Time Of Last Edit: 2015-12-30 03:48:54
|
| [2016-01-07 01:32:41] |
| |
|
We have been successful with a major performance improvement involving your test spreadsheet which makes extensive use of INDEX and MATCH: scss speed test 2.scss On our test system your original test spreadsheet took 26 seconds to calculate. You mentioned about 100 seconds previously, but your system may not be as fast and also we have been working on other performance improvements which are probably making a difference as well. Using the new optimized function to implement what you are doing, this now drops to under 1 second. Therefore, we have reduced calculation time by 96%. This is the new function we are testing: =GETCORRESPONDINGMATCH($K3:$K1002, L$1, 0, 1, 0, $E3:$E1002, 0) We will have a new release out tomorrow along with documentation for the function. However, keep in mind this function is still being implemented and is not likely to be fully implemented until the end of this week. At this time it supports your particular test spreadsheet scenario or scenarios involving exact matches in an array of ascending elements. The documentation for GetCorrespondingMatch is now here: http://www.sierrachart.com/index.php?page=doc/doc_SpreadsheetFunctions.html The function is designed to work with columns and not multicolumn ranges. But we can add that capability. 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 Date Time Of Last Edit: 2016-01-07 04:21:32
|
| [2016-01-07 01:40:29] |
| |
|
The basic reason we were able to make such a dramatic difference so quickly is the fact that the MATCH function was never optimized previously. It was performing a long iteration to do a search. And also using a single function purpose built for what you want, also has performance improvements. One thing we want to make clear, there has never been anything fundamentally wrong with New Spreadsheets regarding performance. The particular performance problems that existed when using the MATCH function and the full recalculations that were taking place when using the Spreadsheet Study were problems that were easily identified and addressed. We are confident with the changes we have made, and coming improvements, including enhanced error reporting to make understanding errors and locating exactly where they are in a formula much easier, that you are going to recognize New Spreadsheets as superior to Old Spreadsheets. At least in regards to speed and calculation capabilities. So what we would need from you, is further information about new functions and enhancements to existing functions to accomplish what you need in a simpler and efficient way. When it comes to visual formatting capabilities, New Spreadsheets are not expected to have all of the capabilities of Old Spreadsheets but that is not our main objective with spreadsheet functionality in Sierra Chart anyway. 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 Date Time Of Last Edit: 2016-01-07 02:15:09
|
| [2016-01-07 11:14:28] |
| |
|
The performance improvements have been released in version 1350 of Sierra Chart which is now available. The spreadsheet you previously provided has been modified to use the new function. It is attached. Let us know how long the calculations take for you now. 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 |
| scss speed test 2.scss - Attached On 2016-01-07 11:11:33 UTC - Size: 160.79 KB - 400 views |
| [2016-01-08 00:52:29] |
| Sawtooth - Posts: 4143 |
|
In the modified test spreadsheet using GETCORRESPONDINGMATCH, with an ascending ordered list in column K, when changing the formula in K1, the calculations take about 2 seconds, vs 1 second for the scwbf test spreadsheet, (using INDEX/MATCH). This is a marked improvement. However: 1) If you change the formula in K3, it takes about 90 seconds to recalculate the sheet, vs 1 second for the scwbf spreadsheet. If you open the test spreadsheet with an unsorted column K, it takes the same 90 seconds. 2) If you change K3 from =ROW(), which produces an ascending ordered list, to =F3, which produces an unsorted list, GETCORRESPONDINGMATCH returns the matching value one row above the originating value's row, and it also does not repeat the value in rows above, until the next matching value or until the current row 3. See pics. |
 Return value of GETCORRESPONDINGMATCH.PNG / V - Attached On 2016-01-08 00:42:21 UTC - Size: 34.63 KB - 483 views Return value of INDEX-MATCH.PNG / V - Attached On 2016-01-08 00:42:28 UTC - Size: 27.33 KB - 390 views |
| [2016-01-08 03:20:16] |
| |
|
1. We do see this and we will resolve this. 2. The function is not fully implemented and this is probably the reason for this. Also, regarding a shorter name for GetCorrespondingMatch, we can do that but we just want to focus on the functionality and performance improvements first. 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-01-13 01:06:19] |
| |
|
We will be releasing version 1354 probably today. Questions related to 1354: 1. Does GetCorrespondingMatch in 1354 meet all of your requirements? Do you need the capability for multicolumn search? In other words searching a range that is more than one column. 2. We have not done any major optimizations to MATCH when searching an ordered Range. Is there any reason why you would need to rely on MATCH any longer? 3. Do all of the performance improvements as of version 1354 make using New Spreadsheets of acceptable performance? Also, in the problem highlighted here: https://www.sierrachart.com/Download.php?Folder=SupportBoard&download=6793 The problem is the SearchRangeOrderingFlag parameter is indicating that the column is ascending when it is unordered. So it needs to be 0. There are additional performance improvements we are working on, but these are going to take a little more time and the benefit of them is just going to depend upon the data and the Formulas in the spreadsheet. So they could make a significant difference, or negligible difference. 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 Date Time Of Last Edit: 2016-01-13 02:50:31
|
To post a message in this thread, you need to log in with your Sierra Chart account: