It’s time to finish up our full script for our Section 16 data scraper. In the first three parts of this series we completed a base script that would go out to the SEC’s website, download all of the filings from a particular CIK, cache all of the Section 16 filings, find all of the reported transactions, put those transactions into a CSV file, and then read all of those transactions and figure out which of those transactions are current holdings. This week we are going to finish up by creating a dialog and adding our script functionality into the dialog.
Friday, April 12. 2019
LDC #131: Building a Section 16 Data Scraper, Part 4: Dialog
Scott has recently finished up a series talking about creating dialog boxes and data controls within dialog boxes. You can read his series starting with Part 1 here. Because a lot of the information I would normally cover here has been explained in detail in his series, today’s post is going to be a lot more about the code changes we have made to allow our script to work within the confines of a dialog.
The major points for this week include: changing our main function to set up the hook for our real function so that we can access the dialog through the ribbon, adding our dialog resource to the script, and adding all of the requisite functions so that we can have the dialog access all of the script functions we have been creating in the last few posts. In addition we have a few changes that have been made in the data we collect so that it is a better user experience.
Before we look at the finished product we need to take a moment and talk about what we want to get out of this script. Going in to this project my requirements were to create a script that would take a CIK and give back a list of current holdings for that CIK. My eventual goal was to take this groundwork and use it later to create a more comprehensive functionality to ease the creation of Section 16 filings in GoFiler. As we went on through the weeks my requirements and end goals changed as my understanding on Section 16 changed. With that in mind there are a few important points to remember about this script.
First off, this script has given me some insight as to how typos within filings can haunt you for the rest of time. The way that we are checking for holdings starts with new filings and checks older filings against the security name, issuer CIK, and ownership type. If a typo is made in the way that a security is named, that filing will show up as a current holding even though it should not be.
Secondly, there are a lot of people who do not understand the SEC’s requirements for Section 16 filings. In my attempt to find good examples of the script working correctly I continued to stumble across people who were not meeting all of the requirements, and so were not good examples. However, the fact that I struggled to find a good example means that there is a widespread problem with correctly filing Section 16.
Finally, this script as it currently is will only help with the creation of Form 5 holding documents. You can take the output of the script and paste it into any Section 16 document, but this will copy the transaction data to the clipboard, so all of the numbers and dates will have to be updated manually. In the end, the biggest use of this script is to easily look up a reporting owners current holdings and issuers, but additional work will have to be done to allow easy creation of filings.
Here’s this week’s final script:
/*****************************************************************************************************************
Section 16 Data Collector
-------------------------
Revision:
01-04-19 JCK Initial creation - get filings from SEC
02-08-19 JCK Part 2 - Store information
03-29-19 JCK Part 3 - Refactoring and getting holdings
04-12-19 JCK Part 4 - Dialog Creation
Notes:
-Stage 4
(c) 2019 Novaworks, LLC. All Rights Reserved.
*****************************************************************************************************************/
#define COL_TORH 0
#define COL_AORD 1
#define COL_TDATE 2
#define COL_TTYPE 3
#define COL_DORI 4
#define COL_NUMTRANS 5
#define COL_NUMOWNED 6
#define COL_CIK 7
#define COL_NAME 8
#define COL_DND 9
#define SCRIPT_INI GetScriptFolder()+"ReportingOwnerHoldings.ini"
#define CACHE_BASE AddPaths(GetScriptFolder(), "Cache\\")
handle hSheet;
handle file;
string s16filings[]; //Table of all filings
string s16holdings[][]; //Table of current holdings
string currentholding[][]; //Current Holding
string cache; //Location of cache
string c_cik; //CIK of Reporting Owner
boolean copyAll;
boolean is_section_16 (string type);
void add_transaction (string issuer);
void add_holding (string issuer, string date);
int populate_filings ();
void get_current_holdings ();
void parse_filings ();
int run (int f_id, string opts);
int ro_reload_data ();
int ro_action (int c_id, int c_action);
int ro_load ();
int ro_validate ();
int setup() {
string fnScript;
string item[10];
int rc;
/* ** Add Menu Item */
/* * Define Function */
item["Code"] = "EXTENSION_RO_HOLDINGS"; /* Function Code */
item["MenuText"] = "&Retrieve Holdings"; /* Menu Text */
item["Description"] = "<B>Retrieve Holdings</B>\r\rRetrieve and List Current Holdings";
item["Class"] = "Extension"; /* Add to Main Ribbon */
/* * Check for Existing */
rc = MenuFindFunctionID(item["Code"]); /* Look for existing */
if (IsNotError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
/* * Registration */
rc = MenuAddFunction(item); /* Add the item */
if (IsError(rc)) { /* Was already be added */
return ERROR_NONE; /* Exit */
} /* end error */
fnScript = GetScriptFilename(); /* Get the script filename */
MenuSetHook(item["Code"], fnScript, "run"); /* Set the Hook */
return ERROR_NONE;
}
int main() {
string s1;
int rc;
/* * Processing from GoFiler */
s1 = GetScriptParent(); /* Get the parent */
if (s1 == "LegatoIDE") { /* Is run from the IDE (debug) */
rc = MenuFindFunctionID("EXTENSION_RO_HOLDINGS"); /* Look for existing */
if (IsError(rc)) { /* No every hooked */
setup(); /* Add to the menu */
} /* end no function */
else { /* Was hooked */
MenuDeleteHook("EXTENSION_RO_HOLDINGS"); /* Cause to run hook from development */
s1 = GetScriptFilename(); /* Get the script filename (us) */
MenuSetHook("EXTENSION_RO_HOLDINGS", s1, "run"); /* Set the Hook */
} /* end already defined */
MessageBox('i', "Hook running on IDE"); /* Status */
} /* end IDE run */
return ERROR_NONE; /* Return value (does not matter) */
}
int run(int f_id, string mode) {
//creation variables
int count; //Counting Variable
int max;
int rc;
string s1;
handle hCB;
//If not preprocess leave
if (mode != "preprocess") {
return ERROR_NONE;
}
//Clear globals
ArrayClear(currentholding);
copyAll = false;
//Get old CIK
c_cik = GetSetting("Settings", "CIK");
//Start Dialog
rc = DialogBox("RODLG1", "ro_");
//If CIK entered, store it
if (c_cik != "") {
PutSetting("Settings", "CIK", c_cik);
}
//If close button hit leave
if (rc == ERROR_CANCEL) { return ERROR_CANCEL; }
//Get how many selected
max = ArrayGetAxisDepth(currentholding, AXIS_ROW);
count = 0;
//Set CSV heading
s1 = "Type of Entry, Title of Security, Transaction Date, Transaction Code, Transaction Acquired Code, Transaction Acquired, Transaction Disposed Code, Transaction Disposed, Transaction Shares, Transaction Post, Ownership Form\n";
//Go through all selected
while (count < max) {
//If copy all - set as holding
if (copyAll) {
s1 += "Holding,";
}
else {
s1 += currentholding[count][COL_TORH] + ",";
}
s1 += "\"" + currentholding[count][COL_NAME] + "\",";
if (!copyAll) {
if (currentholding[count][COL_TORH] == "Transaction") {
s1 += currentholding[count][COL_TDATE] + ",";
}
else {
s1 += ",";
}
s1 += currentholding[count][COL_TTYPE] + ",";
if (currentholding[count][COL_AORD] == "A") {
s1 += currentholding[count][COL_AORD] + ",";
s1 += currentholding[count][COL_NUMTRANS] + ",";
s1 += ",,";
}
else {
s1 += ",,";
s1 += currentholding[count][COL_AORD] + ",";
s1 += currentholding[count][COL_NUMTRANS] + ",";
}
s1 += currentholding[count][COL_NUMTRANS] + ",";
}
else {
s1 += ",,,,,,,";
}
s1 += currentholding[count][COL_NUMOWNED] + ",";
s1 += currentholding[count][COL_DORI] + "\n";
count++;
}
//Create clipboard
hCB = ClipboardCreate();
if (IsError()) {
rc = GetLastError();
MessageBox('x', "Cannot access clipboard (0x%08X).", rc);
return ERROR_NONE;
}
//Put CSV on clipboard
ClipboardSetCSV(hCB, s1);
//Close handle
CloseHandle(hCB);
return ERROR_CANCEL;
}
#beginresource
#define RO_REFRESH 103
#define RO_LIST 101
#define RO_CIK 102
#define ROREFRESH 103
#define RO_HOLDINGCOPY 104
#define ROHOLDINGCOPY 104
RODLG1 DIALOGEX 0, 0, 539, 195
EXSTYLE WS_EX_DLGMODALFRAME
STYLE DS_MODALFRAME | DS_3DLOOK | WS_POPUP | WS_VISIBLE | WS_CAPTION | WS_SYSMENU
CAPTION "Retrieve Reporting Owner Holding"
FONT 8, "MS Sans Serif"
{
CONTROL "OK", IDOK, "BUTTON", BS_PUSHBUTTON | BS_CENTER | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 480, 12, 50, 14
CONTROL "Cancel", IDCANCEL, "BUTTON", BS_PUSHBUTTON | BS_CENTER | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 480, 168, 50, 14
CONTROL "Copy All", RO_HOLDINGCOPY, "BUTTON", BS_PUSHBUTTON | BS_CENTER | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 480, 32, 50, 14
CONTROL "Refresh Holdings", RO_REFRESH, "BUTTON", BS_PUSHBUTTON | BS_CENTER | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 169, 11, 63, 14
CONTROL "Reporting Owner CIK:", -1, "static", SS_LEFT | WS_CHILD | WS_VISIBLE, 12, 14, 72, 8, 0
CONTROL "", RO_CIK, "edit", ES_LEFT | WS_CHILD | WS_VISIBLE | WS_BORDER, 84, 12, 72, 12, 0
CONTROL "", RO_LIST, "data_control", LBS_MULTIPLESEL | LBS_NOTIFY | WS_CHILD | WS_VISIBLE | WS_BORDER | WS_VSCROLL | WS_TABSTOP, 12, 36, 460, 148, 0
CONTROL "Configuration", -1, "static", SS_LEFT | WS_CHILD | WS_VISIBLE, 5, 3, 46, 9, 0
CONTROL "Information", -1, "static", SS_LEFT | WS_CHILD | WS_VISIBLE, 5, 24, 40, 9, 0
CONTROL "Frame1", -1, "static", SS_ETCHEDFRAME | WS_CHILD | WS_VISIBLE, 48, 7, 424, 1, 0
CONTROL "Frame2", -1, "static", SS_ETCHEDFRAME | WS_CHILD | WS_VISIBLE, 44, 28, 428, 1, 0
}
#endresource
int ro_load() {
/* * Set Up List */
DataControlSetColumnPositions(RO_LIST, 50, 95, 140, 300, 370, 420);/* Set the positions */
DataControlSetColumnHeadings(RO_LIST, "Derivative?", /* Derivative? */
"Date", /* Date */
"CIK", /* CIK */
"Name", /* Name */
"Securities Reported", /* securities */
"Direct or Indirect"); /* D or I */
DataControlSetColumnFlags(RO_LIST, DS_CC_SORT, DS_CC_SORT, /* Set the sort */
DS_CC_SORT, DS_CC_SORT, /* Set the sort */
DS_CC_SORT, DS_CC_SORT); /* Set the sort */
//Set button names
EditSetText(IDCANCEL, "Close");
EditSetText(IDOK, "Copy");
//Clear global
ArrayClear(currentholding);
//Set CIK text if exists
if (c_cik != "") {
EditSetText(RO_CIK, c_cik);
ro_reload_data();
}
return ERROR_NONE;
}
int ro_load_list(){
int count;
int max;
int printed;
string s1;
//Reset data control
DataControlResetContent(RO_LIST);
DataControlSetSortColumn(RO_LIST);
count = 0;
max = ArrayGetAxisDepth(s16holdings, AXIS_ROW);
printed = 0;
//Print out all of our current holdings
while (count < max) {
if (s16holdings[count][COL_NUMOWNED] != "0") {
s1 = s16holdings[count][COL_DND] + "\t";
s1 += s16holdings[count][COL_TDATE] + "\t";
s1 += s16holdings[count][COL_CIK] + "\t";
s1 += s16holdings[count][COL_NAME] + "\t";
s1 += s16holdings[count][COL_NUMOWNED] + "\t";
s1 += s16holdings[count][COL_DORI];
DataControlAddString(RO_LIST, s1);
DataControlSetRowData(RO_LIST, printed++, count);
}
count++;
}
return ERROR_NONE;
}
int ro_reload_data() {
string test[];
int rc;
string s2;
//Get text
c_cik = EditGetText(RO_CIK);
//Zerofill
c_cik = FormatString("%010s", c_cik);
//Set text with zeroes
EditSetText(RO_CIK, c_cik);
//Lookup CIK
test = EDGARLookupCIK(c_cik);
rc = GetLastError();
//Is CIK Valid?
if (rc == ERROR_EOD) {
MessageBox('x', "CIK is not valid.");
return ERROR_EOD;
}
//Get cache
cache = AddPaths(CACHE_BASE, c_cik);
//create cache
CreateFolders(cache);
if (IsFolder(cache) == FALSE) {
AddMessage('x', "Unable to create document cache.");
return ERROR_CANCEL;
}
populate_filings();
//Parse through filings to find all transactions
parse_filings();
//Parse through transactions and holdings to find current number
get_current_holdings();
//Put current holdings in dialog
ro_load_list();
}
int ro_action(int c_id, int c_action) {
int ix;
//Refresh CIK
if (c_id == RO_REFRESH) {
ro_reload_data();
return ERROR_NONE;
}
//Double click posts OK
if (c_id == RO_LIST) {
if (c_action == DCN_DOUBLE_CLICK) {
ix = DataControlGetSelectCount(RO_LIST);
if (ix == 0) { return ERROR_NONE; }
DialogPostOK();
return ERROR_NONE;
}
}
//Copy all button
if (c_id == RO_HOLDINGCOPY) {
copyAll = true;
DialogPostOK();
return ERROR_NONE;
}
return ERROR_NONE;
}
int ro_validate() {
int ix[][];
int count;
int iy;
int currow;
//Check how many rows selected
count = DataControlGetSelectCount(RO_LIST);
//If copy all, get how many holdings total
if (copyAll) {
count = ArrayGetAxisDepth(s16holdings, AXIS_ROW);
}
//If not selected
if (count == 0) {
MessageBox('x', "Select at least one item in the holdings list to continue.");
return ERROR_SOFT | RO_LIST;
}
//Go through all selected data
ix = DataControlGetSelectList(RO_LIST);
iy = 0;
while (iy < count) {
if(copyAll) {
currow = iy;
}
else {
currow = ix[iy][0];
}
//Put selected data into array
currentholding[iy][COL_TORH] = s16holdings[currow][COL_TORH];
currentholding[iy][COL_AORD] = s16holdings[currow][COL_AORD];
currentholding[iy][COL_TDATE] = s16holdings[currow][COL_TDATE];
currentholding[iy][COL_TTYPE] = s16holdings[currow][COL_TTYPE];
currentholding[iy][COL_DORI] = s16holdings[currow][COL_DORI];
currentholding[iy][COL_NUMTRANS] = s16holdings[currow][COL_NUMTRANS];
currentholding[iy][COL_NUMOWNED] = s16holdings[currow][COL_NUMOWNED];
currentholding[iy][COL_CIK] = s16holdings[currow][COL_CIK];
currentholding[iy][COL_NAME] = s16holdings[currow][COL_NAME];
currentholding[iy][COL_DND] = s16holdings[currow][COL_DND];
iy++;
}
return ERROR_NONE;
}
//Returns true if S16 file type, false if anything else
boolean is_section_16(string type) {
switch (type) {
case "3":
return true;
case "4":
return true;
case "5":
return true;
case "3/A":
return true;
case "4/A":
return true;
case "5/A":
return true;
}
return false;
}
void parse_filings() {
int count, max, rc;
string date, cik;
string parser;
//Get number of filings
count = ArrayGetAxisDepth(s16filings) - 1;
//Create data sheet
hSheet = DataSheetCreate(2, 10);
//Set the header row
DataSheetSetCellText(hSheet, 0, COL_TORH, "Transaction or Holding");
DataSheetSetCellText(hSheet, 0, COL_AORD, "Acquisition or Disposition");
DataSheetSetCellText(hSheet, 0, COL_TDATE, "Transaction Date");
DataSheetSetCellText(hSheet, 0, COL_TTYPE, "Transaction Type");
DataSheetSetCellText(hSheet, 0, COL_DORI, "Direct or Indirect Ownership");
DataSheetSetCellText(hSheet, 0, COL_NUMTRANS, "Number of Securities Transacted");
DataSheetSetCellText(hSheet, 0, COL_NUMOWNED, "Number of Securities Owned");
DataSheetSetCellText(hSheet, 0, COL_CIK, "Issuer CIK");
DataSheetSetCellText(hSheet, 0, COL_NAME, "Security Name");
DataSheetSetCellText(hSheet, 0, COL_DND, "Derivative or Non-Dirivative");
//Open progress bar
ProgressOpen("Reading Filings");
ProgressSetPhaseCount(count);
max = count;
//Go through filings
while (count >= 0) {
//Update progress
ProgressSetPhase(max-count);
ProgressSetStatus(FormatString("Reading file %d of %d", max-count, max));
ProgressUpdate(max-count, max);
//AddMessage("Parsing file %s", s16filings[count]);
date = "";
//Create SGML parser
file = SGMLCreate();
//Set parse to this file
rc = SGMLSetFile(file, s16filings[count]);
//Check for errors
if (rc != ERROR_NONE) {
AddMessage("Error on Setting File %s: 0x%08X", s16filings[count], rc);
}
//Get first element
parser = SGMLNextElement(file);
rc = GetLastError();
//Check for issuer CIK
while (parser != "<periodOfReport>" && rc == ERROR_NONE) {
parser = SGMLNextElement(file);
rc = GetLastError();
}
//Get CIK
date = SGMLNextItem(file);
//Check for issuer CIK
while (parser != "<issuerCik>" && rc == ERROR_NONE) {
parser = SGMLNextElement(file);
rc = GetLastError();
}
//Get CIK
cik = SGMLNextItem(file);
if (rc != ERROR_NONE) {
AddMessage("Error on retrieving CIK: 0x%08X", rc);
}
//Until end of file
while (rc == ERROR_NONE) {
//check if element is beginning of transaction
if (parser == "<nonDerivativeTransaction>" || parser == "<derivativeTransaction>") {
//Add transaction
add_transaction(cik);
}
if (parser == "<nonDerivativeHolding>" || parser == "<derivativeHolding>") {
//Add transaction
add_holding(cik, date);
}
//Next element
parser = SGMLNextElement(file);
rc = GetLastError();
}
//Done with SGML parser
CloseHandle(file);
//Next file
count--;
}
//Export data to CSV
rc = DataSheetExport(hSheet, AddPaths(cache, c_cik + ".csv"));
if (rc != ERROR_NONE) {
AddMessage("Error on Creating CSV: 0x%08X", rc);
}
CloseHandle(hSheet);
//AddMessage("Finished Parsing files from CIK: %s", TEST_CIK);
ProgressClose();
}
void add_transaction(string issuer) {
string aord, date, ttype, dori, numtrans, numowned, secname, dnd;
string parser;
int rc;
//Initialize variables
aord = "";
date = "";
ttype = "";
dori = "";
numtrans = "";
numowned = "";
secname = "";
dnd = "";
//Get next element
parser = SGMLNextElement(file);
//Check for end of the transaction
while (parser != "</nonDerivativeTransaction>" && parser != "</derivativeTransaction>" && rc == ERROR_NONE){
if (parser == "<securityTitle>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
secname = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
secname += SGMLNextItem(file);
}
secname = ReplaceInString(secname, "</value>", "");
}
if (parser == "<transactionDate>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
date = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
date += SGMLNextItem(file);
}
date = ReplaceInString(date, "</value>", "");
}
if (parser == "<transactionCode>"){
ttype = SGMLNextItem(file);
}
if (parser == "<transactionShares>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
numtrans = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
numtrans += SGMLNextItem(file);
}
numtrans = ReplaceInString(numtrans, "</value>", "");
}
if (parser == "<transactionAcquiredDisposedCode>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
aord = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
aord += SGMLNextItem(file);
}
aord = ReplaceInString(aord, "</value>", "");
}
if (parser == "<sharesOwnedFollowingTransaction>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
numowned = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
numowned += SGMLNextItem(file);
}
numowned = ReplaceInString(numowned, "</value>", "");
}
if (parser == "<directOrIndirectOwnership>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
dori = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
dori += SGMLNextItem(file);
}
dori = ReplaceInString(dori, "</value>", "");
}
parser = SGMLNextElement(file);
rc = GetLastError();
}
//If non-derivative
if (parser == "</nonDerivativeTransaction>") {
dnd = "Non-Derivative";
}
else {
dnd = "Derivative";
}
//Insert a row underneath the header row
DataSheetRowInsert(hSheet, 1);
//Insert all of the data
DataSheetSetCellText(hSheet, 1, COL_TORH, "Transaction");
DataSheetSetCellText(hSheet, 1, COL_AORD, aord);
DataSheetSetCellText(hSheet, 1, COL_TDATE, date);
DataSheetSetCellText(hSheet, 1, COL_TTYPE, ttype);
DataSheetSetCellText(hSheet, 1, COL_DORI, dori);
DataSheetSetCellText(hSheet, 1, COL_NUMTRANS, numtrans);
DataSheetSetCellText(hSheet, 1, COL_NUMOWNED, numowned);
DataSheetSetCellText(hSheet, 1, COL_CIK, issuer);
DataSheetSetCellText(hSheet, 1, COL_NAME, secname);
DataSheetSetCellText(hSheet, 1, COL_DND, dnd);
}
void add_holding(string issuer, string date) {
string aord, ttype, dori, numtrans, numowned, secname, dnd;
string parser;
int rc;
//Initialize variables
dori = "";
numowned = "";
secname = "";
dnd = "";
//Get next element
parser = SGMLNextElement(file);
//Check for end of the transaction
while (parser != "</nonDerivativeHolding>" && parser != "</derivativeHolding>" && rc == ERROR_NONE){
if (parser == "<securityTitle>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
secname = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
secname += SGMLNextItem(file);
}
secname = ReplaceInString(secname, "</value>", "");
}
if (parser == "<sharesOwnedFollowingTransaction>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
numowned = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
numowned += SGMLNextItem(file);
}
numowned = ReplaceInString(numowned, "</value>", "");
}
if (parser == "<directOrIndirectOwnership>"){
while (parser != "<value>") {
parser = SGMLNextItem(file);
}
dori = SGMLNextItem(file);
while (SGMLGetItemType(file) != SPI_TYPE_TAG){
dori += SGMLNextItem(file);
}
dori = ReplaceInString(dori, "</value>", "");
}
parser = SGMLNextElement(file);
rc = GetLastError();
}
//If non-derivative
if (parser == "</nonDerivativeHolding>") {
dnd = "Non-Derivative";
}
else {
dnd = "Derivative";
}
//Insert a row underneath the header row
DataSheetRowInsert(hSheet, 1);
//Insert all of the data
DataSheetSetCellText(hSheet, 1, COL_TORH, "Holding");
DataSheetSetCellText(hSheet, 1, COL_TDATE, date);
DataSheetSetCellText(hSheet, 1, COL_DORI, dori);
DataSheetSetCellText(hSheet, 1, COL_NUMOWNED, numowned);
DataSheetSetCellText(hSheet, 1, COL_CIK, issuer);
DataSheetSetCellText(hSheet, 1, COL_NAME, secname);
DataSheetSetCellText(hSheet, 1, COL_DND, dnd);
}
int populate_filings() {
//creation variables
string files[]; //Files from SEC
string name; //Name of file
boolean s16Check; //Placeholder boolean
int rc; //Error Checker
string base; //String for name storage
int count;
int numfiles;
int max;
ArrayClear(s16filings);
//Get the archive list
files = EDGARFetchArchiveList(c_cik);
rc = GetLastError();
//Check is CIK exists
if (rc == (ERROR_REMOTE | ERROR_FILE_NOT_FOUND)) {
MessageBox("CIK is not valid.");
return ERROR_CANCEL;
}
//If too many filings
if (rc == ERROR_OVERFLOW) {
files = EDGARFetchArchiveList(c_cik, FALSE, 0, 0, TRUE);
}
//Initialize needed variables
max = ArrayGetAxisDepth(files);
count = 0;
numfiles = 0;
//Open progress bar
ProgressOpen("Getting Files");
ProgressSetPhaseCount(max-1);
//While there are still files to go through
while (count < max) {
//Update progress
ProgressSetPhase(count);
ProgressSetStatus(FormatString("Getting file %d of %d", count, max));
ProgressUpdate(count, max);
//Get filename
base = ReplaceInString(GetFilename(files[count]), ".txt", ".xml");
//Check if already downloaded
if (DoesFileExist(AddPaths(cache, base)) == FALSE) {
//Open archive
file = EDGARArchiveOpen(files[count]);
//Make sure a S16 filing
s16Check = is_section_16(EDGARArchiveGetDocType(file, 0));
//If s16
if (s16Check) {
if (EDGARArchiveGetDocFileType(file, 0) == FT_XML) {
//Set name of XML file
name = AddPaths(cache, EDGARArchiveGetProperty(file, "accession_number") + ".xml");
//Get XML file and save it locally
EDGARArchiveGetDocFile(file, 0, name);
//Add to the list
s16filings[numfiles++] = name;
}
}
CloseHandle(file);
}
else {
//Add cached file to list
s16filings[numfiles++] = AddPaths(cache, base);
}
count++;
}
//Close progress
ProgressClose();
return ERROR_NONE;
}
//Get current holdings
void get_current_holdings() {
int count;
int numholdings;
int countholdings;
int max;
int intcount;
string aord, date, ttype, dori, numtrans, numowned, secname, cik, type, dnd;
string filings[][];
boolean found;
//Clear the array
ArrayClear(s16holdings);
//Get all transactions from filings
filings = CSVReadTable(AddPaths(cache, c_cik + ".csv"));
//Set a counter
max = ArrayGetAxisDepth(filings, AXIS_ROW);
count = 1;
//Currently at 0 holdings
numholdings = 0;
//Go through all transactions
while (count < max-1) {
//get all of the values
type = filings[count][COL_TORH];
aord = filings[count][COL_AORD];
date = filings[count][COL_TDATE];
ttype = filings[count][COL_TTYPE];
dori = filings[count][COL_DORI];
numtrans = filings[count][COL_NUMTRANS];
numowned = filings[count][COL_NUMOWNED];
cik = filings[count][COL_CIK];
secname = filings[count][COL_NAME];
dnd = filings[count][COL_DND];
//set counter to 0
countholdings = 0;
//Not found in current holdings
found = false;
//Go through current holdings
while (countholdings < numholdings && found == false) {
//Same CIK?
if (s16holdings[countholdings][COL_CIK] == cik) {
//Same security name?
if (CompareStringsNoCase(s16holdings[countholdings][COL_NAME], secname) == 0 ) {
if (s16holdings[countholdings][COL_DORI] == dori) {
//Found already reported
found = true;
}
}
}
countholdings++;
}
//If not reported yet
if (found == false && TextToInteger(numowned) > 0) {
//Set holding
s16holdings[numholdings][COL_TORH] = type;
s16holdings[numholdings][COL_AORD] = aord;
s16holdings[numholdings][COL_TDATE] = date;
s16holdings[numholdings][COL_TTYPE] = ttype;
s16holdings[numholdings][COL_DORI] = dori;
s16holdings[numholdings][COL_NUMTRANS] = numtrans;
s16holdings[numholdings][COL_NUMOWNED] = numowned;
s16holdings[numholdings][COL_CIK] = cik;
s16holdings[numholdings][COL_NAME] = secname;
s16holdings[numholdings][COL_DND] = dnd;
numholdings++;
}
count++;
}
}
If last week our script was getting large at almost 500 lines our script this week is definitely large as it is just over 800 lines of code in total. So we’re not going to go through every single line, but I’m going to highlight any of the overall changes that we have made in order to accommodate our new dialog.
#define COL_DND 9
#define SCRIPT_INI GetScriptFolder()+"ReportingOwnerHoldings.ini"
#define CACHE_BASE AddPaths(GetScriptFolder(), "Cache\\")
handle hSheet;
handle file;
string s16filings[]; //Table of all filings
string s16holdings[][]; //Table of current holdings
string currentholding[][]; //Current Holding
string cache; //Location of cache
string c_cik; //CIK of Reporting Owner
boolean copyAll;
boolean is_section_16 (string type);
void add_transaction (string issuer);
void add_holding (string issuer, string date);
int populate_filings ();
void get_current_holdings ();
void parse_filings ();
int run (int f_id, string opts);
int ro_reload_data ();
int ro_action (int c_id, int c_action);
int ro_load ();
int ro_validate ();
The first thing to notice here in our globals is that we have added a few new functions. Run(), ro_reload_data(), ro_action(), ro_load(), and ro_validate(). We have also gotten rid of the ability to pass some variables to our main script functions as those are now stored in newly declared global variables. In addition, we are defining our cache base so that we can easily change where the script is looking later on. Finally, we have declared a new column, COL_DND, where we will store whether a row is a derivative or non-derivative transaction.
Our main function is now completely different as it no longer is the driving force behind the script:
int main() {
string s1;
int rc;
s1 = GetScriptParent();
if (s1 == "LegatoIDE") {
rc = MenuFindFunctionID("EXTENSION_RO_HOLDINGS");
if (IsError(rc)) {
setup();
}
else {
MenuDeleteHook("EXTENSION_RO_HOLDINGS");
s1 = GetScriptFilename();
MenuSetHook("EXTENSION_RO_HOLDINGS", s1, "run");
}
MessageBox('i', "Hook running on IDE");
}
return ERROR_NONE;
}
This turns into a commonly seen function: create a hook if we are running from the IDE. If we currently running from the IDE and the script has been hooked previously, delete the old hook and make a new hook. This allows us to run the script from the IDE, making changes at will, and still be able to run the hooked function. It also means the script file cannot be an untitled document.
int setup() {
string fnScript;
string item[10];
int rc;
item["Code"] = "EXTENSION_RO_HOLDINGS";
item["MenuText"] = "&Retrieve Holdings";
item["Description"] = "<B>Retrieve Holdings</B>\r\rRetrieve and List Current Holdings";
item["Class"] = "Extension";
rc = MenuFindFunctionID(item["Code"]);
if (IsNotError(rc)) {
return ERROR_NONE;
}
rc = MenuAddFunction(item);
if (IsError(rc)) {
return ERROR_NONE;
}
fnScript = GetScriptFilename();
MenuSetHook(item["Code"], fnScript, "run");
return ERROR_NONE;
}
We have also created a setup function. This means that we can put the script into the extensions folder of GoFiler and this script will be run upon the application starting. Our script will create a menu function and add it to our Tools ribbon. We then add in a hook to the function and set it towards our run() function. And speaking of our run() function, let’s take a look at that next:
int run(int f_id, string mode) {
//creation variables
int count; //Counting Variable
int max;
int rc;
string s1;
handle hCB;
//If not preprocess leave
if (mode != "preprocess") {
return ERROR_NONE;
}
//Clear globals
ArrayClear(currentholding);
copyAll = false;
//Get old CIK
c_cik = GetSetting("Settings", "CIK");
//Start Dialog
rc = DialogBox("RODLG1", "ro_");
This is the first half of the function. We start by checking to see what mode we are in. If we are not preprocessing we leave function immediately. Next we see if a CIK is listed in our settings file. We will put the current CIK into the settings file when the dialog gets closed to that we can repopulate the CIK field when the dialog reopens. This is just a nice way to make a user’s life easier. We open the dialog and wait until the dialog is closed.
//If CIK entered, store it
if (c_cik != "") {
PutSetting("Settings", "CIK", c_cik);
}
//If close button hit leave
if (rc == ERROR_CANCEL) { return ERROR_CANCEL; }
//Get how many selected
max = ArrayGetAxisDepth(currentholding, AXIS_ROW);
count = 0;
//Set CSV heading
s1 = "Type of Entry, Title of Security, Transaction Date, Transaction Code, Transaction Acquired Code, Transaction Acquired, Transaction Disposed Code, Transaction Disposed, Transaction Shares, Transaction Post, Ownership Form\n";
//Go through all selected
while (count < max) {
//If copy all - set as holding
if (copyAll) {
s1 += "Holding,";
}
else {
s1 += currentholding[count][COL_TORH] + ",";
}
s1 += "\"" + currentholding[count][COL_NAME] + "\",";
if (!copyAll) {
if (currentholding[count][COL_TORH] == "Transaction") {
s1 += currentholding[count][COL_TDATE] + ",";
}
else {
s1 += ",";
}
s1 += currentholding[count][COL_TTYPE] + ",";
if (currentholding[count][COL_AORD] == "A") {
s1 += currentholding[count][COL_AORD] + ",";
s1 += currentholding[count][COL_NUMTRANS] + ",";
s1 += ",,";
}
else {
s1 += ",,";
s1 += currentholding[count][COL_AORD] + ",";
s1 += currentholding[count][COL_NUMTRANS] + ",";
}
s1 += currentholding[count][COL_NUMTRANS] + ",";
}
else {
s1 += ",,,,,,,";
}
s1 += currentholding[count][COL_NUMOWNED] + ",";
s1 += currentholding[count][COL_DORI] + "\n";
count++;
}
//Create clipboard
hCB = ClipboardCreate();
if (IsError()) {
rc = GetLastError();
MessageBox('x', "Cannot access clipboard (0x%08X).", rc);
return ERROR_NONE;
}
//Put CSV on clipboard
ClipboardSetCSV(hCB, s1);
//Close handle
CloseHandle(hCB);
return ERROR_CANCEL;
}
Once the dialog is closed we check to see if the CIK is filled out. If it is we will put the CIK in our settings file. Then we check to see if the dialog was closed with the “Copy” or the “Close” button. If the “Copy” button was pressed we want to place the selected holdings onto the clipboard as CSV. First we figure out how many rows are selected, which allows us to go through our loop. The first thing to do is set the headings to values that will work for having both derivate and non-derivative filings. Then we go through each selected holding. We are going to have to buttons on our dialog: “Copy All” and “Copy.” We have to plan for both modes. For “Copy All” we are going to change all of our holdings to be holdings on the clipboard. If “Copy” is pressed, we take the value of the last report (transaction or holding). We then go through and add all of our values to the string for each selected row. Finally we create a clipboard, add the CSV to the clipboard, and close the clipboard handle. This means that if a user clicks the “Copy” or “Copy all” buttons they will get the holdings given to them in a manner that allows them to paste the holding into a Section 16 filing.
Now it is time to talk about our dialog. Our dialog looks something like this:
It consists of two important features: an input to put in a CIK to lookup and a data control where we put our list so that the user can see it. We also have a Copy, Copy All, and a Close button on the side. When the dialog is first referenced using the DialogBox() function the load function is called. In this case the function name is ro_load():
int ro_load() {
/* * Set Up List */
DataControlSetColumnPositions(RO_LIST, 50, 95, 140, 300, 370, 420);/* Set the positions */
DataControlSetColumnHeadings(RO_LIST, "Derivative?", /* Derivative? */
"Date", /* Date */
"CIK", /* CIK */
"Name", /* Name */
"Securities Reported", /* securities */
"Direct or Indirect"); /* D or I */
DataControlSetColumnFlags(RO_LIST, DS_CC_SORT, DS_CC_SORT, /* Set the sort */
DS_CC_SORT, DS_CC_SORT, /* Set the sort */
DS_CC_SORT, DS_CC_SORT); /* Set the sort */
//Set button names
EditSetText(IDCANCEL, "Close");
EditSetText(IDOK, "Copy");
//Clear global
ArrayClear(currentholding);
//Set CIK text if exists
if (c_cik != "") {
EditSetText(RO_CIK, c_cik);
ro_reload_data();
}
return ERROR_NONE;
}
Here we set up the list on the dialog. We are setting up six columns, so we need to set the positions, the headings, and the flags for each column. We change the text on the cancel button from “Cancel” to “Close”, and we change the “OK” button to say “Copy.” We clear the currentholding array so that if the script has been run before there is no chance of accidentally reading values that should not exist for the current CIK. Finally, if the CIK from our settings file exists, we set the CIK edit control to have the CIK in it, and then we call the ro_reload_data() function. This means that we will be running the equivalent of last weeks script before the dialog fully opens so that the data in the dialog is correct as soon as it opens up.
int ro_reload_data() {
string test[];
int rc;
string s2;
//Get text
c_cik = EditGetText(RO_CIK);
//Zerofill
c_cik = FormatString("%010s", c_cik);
//Set text with zeroes
EditSetText(RO_CIK, c_cik);
//Lookup CIK
test = EDGARLookupCIK(c_cik);
rc = GetLastError();
//Is CIK Valid?
if (rc == ERROR_EOD) {
MessageBox('x', "CIK is not valid.");
return ERROR_EOD;
}
//Get cache
cache = AddPaths(CACHE_BASE, c_cik);
//create cache
CreateFolders(cache);
if (IsFolder(cache) == FALSE) {
AddMessage('x', "Unable to create document cache.");
return ERROR_CANCEL;
}
populate_filings();
//Parse through filings to find all transactions
parse_filings();
//Parse through transactions and holdings to find current number
get_current_holdings();
//Put current holdings in dialog
ro_load_list();
}
Here is our equivalent to last week’s main function. We get the value from the CIK from the Edit control and we pad the left of the string with zero’s until our string is ten characters long. We then we use the EDGARLookupCIK() function to see if the CIK is a valid CIK. If the return code from the lookup is an ERROR_EOD, the CIK is not valid, and we let the user know before we exit the function. If the CIK is valid, we check to see if the cache exists, create it if it does not exist, and then we go through our main workflow: get the filings from the SEC, put them into the cache, get the data out of them, and then get the holdings from there. Then we call the re_load_list() function, which will put our data into the list.
int ro_load_list(){
int count;
int max;
int printed;
string s1;
//Reset data control
DataControlResetContent(RO_LIST);
DataControlSetSortColumn(RO_LIST);
count = 0;
max = ArrayGetAxisDepth(s16holdings, AXIS_ROW);
printed = 0;
//Print out all of our current holdings
while (count < max) {
if (s16holdings[count][COL_NUMOWNED] != "0") {
s1 = s16holdings[count][COL_DND] + "\t";
s1 += s16holdings[count][COL_TDATE] + "\t";
s1 += s16holdings[count][COL_CIK] + "\t";
s1 += s16holdings[count][COL_NAME] + "\t";
s1 += s16holdings[count][COL_NUMOWNED] + "\t";
s1 += s16holdings[count][COL_DORI];
DataControlAddString(RO_LIST, s1);
DataControlSetRowData(RO_LIST, printed++, count);
}
count++;
}
return ERROR_NONE;
}
The sole purpose of this function is to refresh the list in our data control with the values from our holdings table. So the first thing that we have to do is reset the content and reset the column sorting. Next we get the number of holdings in our table and we go through the table row by row. If a holding has a number greater than zero we want to add the holding to the data sheet. We add the values to a string separated by tab characters, and then we add the string to the data control, followed by telling the data control that we are finished with that row. After the loop finishes all of the values have been added to the data sheet and will be shown on the dialog. The next thing to talk about then is the action function:
int ro_action(int c_id, int c_action) {
int ix;
//Refresh CIK
if (c_id == RO_REFRESH) {
ro_reload_data();
return ERROR_NONE;
}
//Double click posts OK
if (c_id == RO_LIST) {
if (c_action == DCN_DOUBLE_CLICK) {
ix = DataControlGetSelectCount(RO_LIST);
if (ix == 0) { return ERROR_NONE; }
DialogPostOK();
return ERROR_NONE;
}
}
//Copy all button
if (c_id == RO_HOLDINGCOPY) {
copyAll = true;
DialogPostOK();
return ERROR_NONE;
}
return ERROR_NONE;
}
Our action function is pretty simple as we only care about three things: if the refresh button is pressed, if the user double clicks on a list item, or if the “Copy All” button is pressed. If the refresh button is pressed we call the ro_reload_data() function, which will refresh the dialog with new data. If the list control is double clicked we first check to see if a value was clicked. If it was, we tell the dialog to post an IDOK value. This is the equivalent of clicking on the “OK” button, which will close the dialog. Finally, if the user clicks on the “Copy All” button, we set a global flag to be true and ask the dialog to post IDOK. Speaking of clicking on the “OK” button, we have one last new function to look at which gets called whenever we click the “OK” button:
int ro_validate() {
int ix[][];
int count;
int iy;
int currow;
//Check how many rows selected
count = DataControlGetSelectCount(RO_LIST);
//If copy all, get how many holdings total
if (copyAll) {
count = ArrayGetAxisDepth(s16holdings, AXIS_ROW);
}
//If not selected
if (count == 0) {
MessageBox('x', "Select at least one item in the holdings list to continue.");
return ERROR_SOFT | RO_LIST;
}
//Go through all selected data
ix = DataControlGetSelectList(RO_LIST);
iy = 0;
while (iy < count) {
if(copyAll) {
currow = iy;
}
else {
currow = ix[iy][0];
}
//Put selected data into array
currentholding[iy][COL_TORH] = s16holdings[currow][COL_TORH];
currentholding[iy][COL_AORD] = s16holdings[currow][COL_AORD];
currentholding[iy][COL_TDATE] = s16holdings[currow][COL_TDATE];
currentholding[iy][COL_TTYPE] = s16holdings[currow][COL_TTYPE];
currentholding[iy][COL_DORI] = s16holdings[currow][COL_DORI];
currentholding[iy][COL_NUMTRANS] = s16holdings[currow][COL_NUMTRANS];
currentholding[iy][COL_NUMOWNED] = s16holdings[currow][COL_NUMOWNED];
currentholding[iy][COL_CIK] = s16holdings[currow][COL_CIK];
currentholding[iy][COL_NAME] = s16holdings[currow][COL_NAME];
currentholding[iy][COL_DND] = s16holdings[currow][COL_DND];
iy++;
}
return ERROR_NONE;
}
Validate checks to see if any values are selected before the dialog closes. We get the row selection from the Data Control and if the value is less than zero we know there is nothing selected. If that is the case, we throw a soft error, which stops the dialog from closing but does not stop the rest of the script from running. If there is a value selected we fill out the currentholding array with the values from the list and return. This means our main function will have access to these values when we come back to it. If the “Copy All” button was pressed, however, we want to make sure that validate copies all of the current holdings. So if copyAll is set, instead of checking to see if any values are selected, we go through every single value and copy it into the currentholding array.
The last change to talk about is a small change to our addtransaction() and addholding() functions. As mentioned earlier we needed to store whether or not a transaction was a derivative or non-derivative, so we have added a small section to each function:
//If non-derivative
if (parser == "</nonDerivativeTransaction>") {
dnd = "Non-Derivative";
}
else {
dnd = "Derivative";
}
This variable is then put into the csv of transactions in its own column, COL_DND. This is the excerpt from the transaction function, but the code changes for the holding function looks the same except we compare parser against </nonDerivativeHolding> instead.
We have done it. We took an idea (creating a script that scrapes Section 16 holdings from the SEC’s website) and we have made it a reality. Each week we broke down an individual portion of the script and conquered the entire thing from start to finish, even though the initial task sounds difficult. In the end, we have even added the functionality to a dialog in order for users to be able to do several CIK’s and get the data in a form that is easy to read and understand. There are more improvements that could be made, such as additional validation and functionality with the data now that we have it, but this was a good example of taking a large task and breaking it down into smaller, more manageable, portions. The entire script, resource and all, is available above.
 Joshua Kwiatkowski is a developer at Novaworks, primarily working on Novaworks’ cloud-based solution, GoFiler Online. He is a graduate of the Rochester Institute of Technology with a Bachelor of Science degree in Game Design and Development. He has been with the company since 2013. |
Additional Resources
Legato Script Developers LinkedIn Group
Primer: An Introduction to Legato
Quicksearch
Categories
Calendar
Feeds
