Everyone knows how to put things in alphabetical order; it’s one of the first things we learn in grade school. Well, just because it’s easy doesn’t mean we want to do it ourselves sometimes! 13-F’s Information Table, for example, would be pretty time-consuming to have to alphabetize manually. Using the SortTable function, we can sort any table quickly and easily. While there’s no SEC requirement to do so, it makes it a lot easier for readers to view if the information table is alphabetized by Issuer Name. Legato provides powerful tools to make this mundane task simple and automatic.
Friday, August 11. 2017
LDC #47: Using Legato To Alphabetize a List
The script this week is an excerpt from code that will be deployed in GoFiler, and adds two functions to the Tools menu of the 13-F view: “Alphabetize List ASC”, and “Alphabetize List DESC”. They alphabetize the list in ascending and descending order, respectively.
Before we get into the script, a quick word on code re-use. We will have two very, very similar functions here, to sort a list alphabetically in ascending order and to sort a list in descending order. It would be very easy to simply write a function that does one of the two tasks and then write a second function by copying the first one and modifying it slightly. The problem there is that we’re restating a lot of the same code over again because most of what we create is going to be identical. Whenever possible, you want to re-use code, so if we have to go back and change something later, we only need to change it in one place. To re-use the code here, instead of writing two run functions, we have three. The generic run function here takes a third parameter, the string ord. The other two run functions, run_sort_asc and run_sort_desc, simply call the generic run function and pass an appropriate value for ord to it. This way it knows the desired sort order.
Here’s our script in its entirety.
int run (int f_id, string mode, string ord);/* call from Hook Processor */
int run_sort_asc (int f_id, string mode);/* call from Hook Processor */
int run_sort_desc (int f_id, string mode);/* call from Hook Processor */
int setup_sort_asc();
int setup_sort_desc();
int setup(){
setup_sort_asc();
setup_sort_desc();
return ERROR_NONE;
}
/****************************************/
int setup_sort_asc() { /* Set Up Cusip Lookup */
/****************************************/
string fnScript; /* Us */
string lookup[10]; /* Menu Item */
int rc; /* Return Code */
/* */
/* ** Add Menu Item */
/* * Define Function */
lookup["Code"] = "EXTENSION_ALPHABETIZE_CUSIPS_ASC"; /* Function Code */
lookup["MenuText"] = "&Alphabetize List ASC"; /* Menu Text */
lookup["Description"] = "<B>13-F Tools</B>\r\rAlphabetize the Information Table.";
lookup["Class"] = "Form13FExtension"; /* Class */
/* * Safety */
rc = MenuFindFunctionID(lookup["Code"]); /* Look for existing */
if (IsNotError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
/* * Registration */
/* o Add */
rc = MenuAddFunction(lookup); /* Add the item */
if (IsError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
/* o Hook */
fnScript = GetScriptFilename(); /* Get the script filename */
MenuSetHook(lookup["Code"], fnScript, "run_sort_asc"); /* Set the Test Hook */
return ERROR_NONE; /* Return value (does not matter) */
} /* end setup */
/****************************************/
int setup_sort_desc() { /* Set Up Cusip Lookup */
/****************************************/
string fnScript; /* Us */
string lookup[10]; /* Menu Item */
int rc; /* Return Code */
/* */
/* ** Add Menu Item */
/* * Define Function */
lookup["Code"] = "EXTENSION_ALPHABETIZE_CUSIPS_DESC"; /* Function Code */
lookup["MenuText"] = "Alphabetize List &DESC"; /* Menu Text */
lookup["Description"] = "<B>13-F Tools</B>\r\rAlphabetize the Information Table.";
lookup["Class"] = "Form13FExtension"; /* Class */
/* * Safety */
rc = MenuFindFunctionID(lookup["Code"]); /* Look for existing */
if (IsNotError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
/* * Registration */
/* o Add */
rc = MenuAddFunction(lookup); /* Add the item */
if (IsError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
/* o Hook */
fnScript = GetScriptFilename(); /* Get the script filename */
MenuSetHook(lookup["Code"], fnScript, "run_sort_desc"); /* Set the Test Hook */
return ERROR_NONE; /* Return value (does not matter) */
} /* end setup */
/****************************************/
int run_sort_asc(int f_id, string mode){ /* run ascending sort */
/****************************************/
return run(f_id, mode, "asc"); /* run ascending sort */
} /* */
/****************************************/
int run_sort_desc(int f_id, string mode){ /* run descending sort */
/****************************************/
return run(f_id, mode, "desc"); /* run descending sort */
} /* */
/****************************************/
int run(int f_id, string mode, string ord) { /* Call from run aliases */
/****************************************/
handle hwView, hDataView,hLog; /* Handles */
string msg; /* message to user */
string items[12], table[][]; /* string arrays */
int cols, rows, rc, rx, sortmode; /* ints */
/* */
/* ** Run alphabetize */
/* * Safety only preprocess */
if (mode != "preprocess") { /* Not Preprocess */
return ERROR_NONE; /* Done */
} /* end not preprocess */
/* * Query for Information */
msg = "This function will alphabetize the Information Table by "; /* Set message box text */
msg += "the Issuer Name. Continue?"; /* Set message box text */
rc = OkCancelBox('Q', msg); /* Ask User */
if (rc == IDCANCEL) { /* Error or cancel */
return ERROR_NONE; /* Exit w/error */
} /* end error */
/* * Get Information Table */
/* o View */
hwView = GetActiveEditWindow(); /* Get the active edit window */
if (IsError(hwView)) { /* Failed? */
rc = GetLastError(); /* Get the error code */
MessageBox('x', "Could not get Window View 0x%08X", rc); /* Print the error code */
return ERROR_EXIT; /* Exit w/ error */
} /* end failed */
/* o Get Data View */
hDataView = DataViewGetObject(hwView); /* Get the data view of the open gfp */
if (IsError(hDataView)) { /* Failed? */
rc = GetLastError(); /* Get the error code */
MessageBox('x', "Could not get Data View object 0x%08X", rc); /* Print the error code */
return ERROR_EXIT; /* Exit w/ error */
} /* end failed */
/* o Data */
rx = 0; /* Clear Row */
items = DataViewGetListRow(hDataView, "InfoTable", rx); /* Get first row of items */
cols = DataViewGetListColumns(hDataView, "InfoTable"); /* Get the number of cols in the gfp */
rows = DataViewGetListRows(hDataView, "InfoTable"); /* Get the number of rows in the gfp */
/* * Process Data */
hLog = LogCreate(); /* Create a log */
LogClearProperties(); /* Reset the log */
if (cols == 12) { /* Safety: has 12 columns */
while (rows > rx) { /* Iterate over all rows */
AddMessage(hLog,"Sorting Row: %s",items[0]); /* display log */
table[rx]=items; /* save new row for output */
rx++; /* incremement rowcount */
items = DataViewGetListRow(hDataView, "InfoTable", rx); /* Get the next row of items */
} /* end loop over rows */
sortmode = SORT_ASCENDING; /* set base sortmode */
if (ord == "desc"){ /* if we're descending */
sortmode = SORT_DESCENDING; /* set sort to descending */
} /* */
SortTable(table,SORT_ALPHA | sortmode, 0); /* sort the table */
DataViewSetList(hDataView, "InfoTable", table); /* Set content to view */
} /* end has 12 columns */
else{ /* Bad table? */
MessageBox('X', "No 13-F Form open"); /* Tell User */
} /* end bad table */
MessageBox("Sort completed, see Information View for details."); /* Display completion message */
LogDisplay(hLog,"Sort Information Table"); /* show finalized log */
return ERROR_NONE; /* Exit Done */
} /* end run */
int main(){
setup();
return ERROR_NONE;
}
We begin with some function declarations. We declare the generic run function, as well as our two new run functions, run_sort_asc and run_sort_desc. In addition, we have two setup functions: setup_sort_asc and setup_sort_desc. Again, these functions perform essentially the same operation but for the “Alphabetize List ASC” and “Alphabetize List DESC” menu options, respectively. Our main function simply calls the generic setup function, which then calls both setup_sort_asc and setup_sort_desc in succession. Let’s take a look at the setup_sort_asc function.
/****************************************/
int setup_sort_asc() { /* Set Up Cusip Lookup */
/****************************************/
string fnScript; /* Us */
string lookup[10]; /* Menu Item */
int rc; /* Return Code */
/* */
/* ** Add Menu Item */
/* * Define Function */
lookup["Code"] = "EXTENSION_ALPHABETIZE_CUSIPS_ASC"; /* Function Code */
lookup["MenuText"] = "&Alphabetize List ASC"; /* Menu Text */
lookup["Description"] = "<B>13-F Tools</B>\r\rAlphabetize the Information Table.";
lookup["Class"] = "Form13FExtension"; /* Class */
/* * Safety */
rc = MenuFindFunctionID(lookup["Code"]); /* Look for existing */
if (IsNotError(rc)) { /* Was already added */
return ERROR_NONE; /* Exit */
} /* end error */
/* * Registration */
/* o Add */
rc = MenuAddFunction(lookup); /* Add the item */
if (IsError(rc)) { /* Was already added */
return ERROR_NONE; /* Exit */
} /* end error */
/* o Hook */
fnScript = GetScriptFilename(); /* Get the script filename */
MenuSetHook(lookup["Code"], fnScript, "run_sort_asc"); /* Set the Test Hook */
return ERROR_NONE; /* Return value (does not matter) */
} /* end setup */
This function performs many of the same operations we’ve covered in previous blogs. We set up a string array entitled lookup to hold information about the menu item we are trying to add. We then check to see if the menu item has already been added or if the ID is otherwise in use with the MenuFindFunctionID SDK function. If there is an error, we exit. If not, we set up the menu item and the hook with the MenuAddFunction and the MenuSetHook functions respectively. Since this is the ascending menu item, we hook the item into our run_sort_asc function.
Our setup_sort_desc function operates in nearly the exact same way as its ascending counterpart, save for adding a alphabetize descending menu item and hooking that item into the run_sort_desc function. Let’s take a look at that function now:
/****************************************/
int run_sort_desc(int f_id, string mode){ /* run descending sort */
/****************************************/
return run(f_id, mode, "desc"); /* run descending sort */
} /* */
All this function does is pass our “desc” string to the generic run function as we mentioned above. The ascending version passes in the “ord” string instead. Now, with our two menu items set up and properly hooked into ascending and descending operations, let’s examine the workhorse of this script: the run function.
/****************************************/
int run(int f_id, string mode, string ord) { /* Call from run aliases */
/****************************************/
handle hwView, hDataView,hLog; /* Handles */
string msg; /* message to user */
string items[12], table[][]; /* string arrays */
int cols, rows, rc, rx, sortmode; /* ints */
/* */
First we declare the variables we need, including handles to the log and Data View, strings for messages and arrays of strings for sorting, and counting integers.
if (mode != "preprocess") { /* Not Preprocess */
return ERROR_NONE; /* Done */
} /* end not preprocess */
/* * Query for Information */
msg = "This function will alphabetize the Information Table by "; /* Set message box text */
msg += "the Issuer Name. Continue?"; /* Set message box text */
rc = OkCancelBox('Q', msg); /* Ask User */
if (rc == IDCANCEL) { /* Error or cancel */
return ERROR_NONE; /* Exit w/error */
} /* end error */
As we usually do, if the mode passed to the run function isn’t preprocess, we exit. After that we begin building a message to the user indicating that the Information Table is about to be sorted. We use the OkCancelBox function to present the user with a choice to continue. As long as the result is not IDCANCEL (the user pressing the Cancel button), we continue script execution by beginning to collect the data we need to alphabetize.
/* * Get Information Table */
/* o View */
hwView = GetActiveEditWindow(); /* Get the active edit window */
if (IsError(hwView)) { /* Failed? */
rc = GetLastError(); /* Get the error code */
MessageBox('x', "Could not get Window View 0x%08X", rc); /* Print the error code */
return ERROR_EXIT; /* Exit w/ error */
} /* end failed */
/* o Get Data View */
hDataView = DataViewGetObject(hwView); /* Get the data view of the open gfp */
if (IsError(hDataView)) { /* Failed? */
rc = GetLastError(); /* Get the error code */
MessageBox('x', "Could not get Data View object 0x%08X", rc); /* Print the error code */
return ERROR_EXIT; /* Exit w/ error */
} /* end failed */
To retrieve the Information Table, we first need a handle to the active edit window, which we can retrieve with the GetActiveEditWindow function. It’s important to check if this handle (and all handles) is valid, and the IsError and GetLastError functions can indicate if there was an error and what the error was. If there was, we report it to the user and exit. With the handle to the edit window, we can then retrieve the Data View with the DataViewGetObject function. Again, we need to check if the handle is valid and report any issues.
rx = 0; /* Clear Row */
items = DataViewGetListRow(hDataView, "InfoTable", rx); /* Get first row of items */
cols = DataViewGetListColumns(hDataView, "InfoTable"); /* Get the number of cols in the gfp */
rows = DataViewGetListRows(hDataView, "InfoTable"); /* Get the number of rows in the gfp */
Now that we have the Data View, we can begin processing the information. We set our row counter, rx, to zero. We also retrieve the first row with the DataViewGetListRow function using the hDataView handle, the “InfoTable” parameter, and the position (zero). Finally, we get the number of columns (cols) and the number of rows (rows) with the DataViewGetListColumns and the DataViewGetListRows functions, respectively.
hLog = LogCreate(); /* Create a log */
LogClearProperties(); /* Reset the log */
if (cols == 12) { /* Safety: has 12 columns */
while (rows > rx) { /* Iterate over all rows */
AddMessage(hLog,"Sorting Row: %s",items[0]); /* display log */
table[rx]=items; /* save new row for output */
rx++; /* incremement rowcount */
items = DataViewGetListRow(hDataView, "InfoTable", rx); /* Get the next row of items */
} /* end loop over rows */
sortmode = SORT_ASCENDING; /* set base sortmode */
if (ord == "desc"){ /* if we're descending */
sortmode = SORT_DESCENDING; /* set sort to descending */
} /* */
SortTable(table,SORT_ALPHA | sortmode, 0); /* sort the table */
DataViewSetList(hDataView, "InfoTable", table); /* Set content to view */
} /* end has 12 columns */
else{ /* Bad table? */
MessageBox('X', "No 13-F Form open"); /* Tell User */
} /* end bad table */
After, we create a log with the LogCreate function and clear it with the LogClearProperties function. Now we test to ensure our Information Table has twelve columns, which it must have to be correct and compliant for Form 13-F. If there are twelve columns, we proceed with our sort. If not, we alert the user of the issue and no sorting is performed.
We sort so long as the total number of rows is larger than the current row number using a while loop. First we add a message to the log using the AddMessage function to indicate which row we are currently sorting. We copy this row to our temporary table string array and increment our counter. We then retrieve the next row with the DataViewGetListRow function, store it in items, and iterate through the loop.
Once all the items are stored in the temporary table, we can sort them. We do so by setting the sort_mode parameter to the SORT_ASCENDING flag by default. If the ord parameter that has been passed into the function is “desc”, we flip the parameter to the SORT_DESCENDING value. After that, sorting is as easy as calling the SortTable function. We combine the SORT_ALPHA flag with our sortmode value. Once we do that, the DataViewSetList function sets the Data View to the new, sorted table.
MessageBox("Sort completed, see Information View for details."); /* Display completion message */
LogDisplay(hLog,"Sort Information Table"); /* show finalized log */
return ERROR_NONE; /* Exit Done */
Once we’re finished, we use the MessageBox function to alert the user, and we display the log to show how the sorting process went. After that, we return with no error.
So, just like that, we can easily sort the Information Table on form 13-F to be alphabetized in either ascending or descending order. Legato makes tasks like this simple to implement with minimal coding, and to the user, it’s as easy as clicking a menu item.