This week, we’re taking a look at automating the subject company lookup on SC13D/G filings. This is another request from a client, but I think it provides a good opportunity to look at automation of a pretty simple task that otherwise might be forgotten and overlooked by a busy operator. This script will trigger when a user opens a project file (a .GFP file), if and only if the project being opened is of document type SC 13D or SC 13G. These are the best kinds of tasks to automate, because they are specific, and easily forgotten by an operator. If the document opened meets these criteria, the CIK of the subject company will be looked up, and the name of the subject company will be looked up on the EDGAR system and entered into the name field. This is very useful if you have an automated system that is creating .GFP files (not very hard, it’s a pretty simple file format), but you cannot guarantee that system has access to the most current names of companies. For example, if the system generating the GFP files has an outdated database of names, you would want to make sure that the subject company is replaced with current information on file open. This is where our script comes in.
Friday, February 07. 2020
LDC #166: Forcing a Subject Company Name Lookup For 13D/G Filings
Keep in mind that when looking at this script, we’re using absolute positions within the project file to pull things like the CIK and the name value out. This means that if GoFiler ever changes these position values, this script will need to be updated to reflect the new positions. Generally though, the only reason that would happen though is if the SEC has changed the form due to an EDGAR release. Because EDGAR changes can be drastic, even if we used a more robust method of reading the values it could stop working and would need to be reviewed.
The script uses our basic setup / run / main function template we’ve used in many scripts, so we’re going to focus only on the run function, as the other two are the same as in every other script like it.
/****************************************/
int run(int f_id, string mode){ /* Run */
/****************************************/
-- Variable Declarations Omitted --
if (mode=="postprocess"){ /* if after project opened */
edit_window = GetEditWindowHandle(); /* get handle to edit window */
filename = GetEditWindowFilename(edit_window); /* get filename of opened project */
if (GetExtension(filename)!=".gfp"){ /* if it's not a gfp file */
return ERROR_NONE; /* return */
} /* if it is a GFP file */
dataview = DataViewGetObject(edit_window); /* get datasheet of project */
type = DataViewCellGetText(dataview,5,2); /* get filing type */
log = LogCreate("Look Up Company Name"); /* create a log file */
AddMessage(log,"Looking up CIK for form: %s",type); /* add log message */
FindInString(type,"SC 13"); /* check for SC 13 position */
if(GetLastError()==ERROR_EOD){ /* check if it's a not SC 13 filing */
return ERROR_NONE; /* not a SC 13 filing, exit */
} /* continue if it's an SC 13 filing */
Our function is going to start off by first checking to make sure we’re running in postprocess mode. We want it to trigger only after a file has been opened in GoFiler. If we’re in postprocess, we can grab the handle to the edit window with GetEditWindowHandle, and get the name of the file that is currently open. If the filename does not end in the “.gfp” extension, we can return, because there’s nothing else to check. If it does, we can get the Data View Object, and use DataViewCellGetText to get the text of the cell containing the document type. In this case, it’s at position row: 5, column: 2. We can tell what positions are what by opening the file “eis.xds”, in the Templates folder of the GoFiler installation directory. This template file is used as the basis for most GoFiler projects, and should list most of the fields we want to look up. Using this as a reference will give us the row/column coordinates for pretty much anything we’ll want to look up or set in our project file. If the document type we read contains the text “SC 13D”, then we know we’re looking at a file we want to check. Otherwise, we can just return without error and normal program execution can continue.
cik = DataViewCellGetText(dataview,159,2); /* get CIK of subject company */
AddMessage(log,"CIK: %s",cik); /* add log message */
if(cik == ""){ /* if no CIK exists */
return ERROR_NONE; /* return without running lookup */
} /* */
name = DataViewCellGetText(dataview,161,2); /* get name of subject company */
AddMessage(log,"Old Name: %s",name); /* add log message */
edgar_response = EDGARLookupCIK(cik); /* look up on CIK */
if (IsError()){ /* if error on lookup */
rc = GetLastError(); /* check the last error */
MessageBox('x',"CIK: %s\rError code: %s", /* display error code message */
cik,FormatString("%0x",rc)); /* display error code message */
LogDisplay(log); /* display the log */
return ERROR_EXIT; /* return error */
} /* if not an error */
Assuming we’re working on a 13D or 13G, we can then run the DataViewCellGetText function again to get our subject company CIK, and we can add it to our log file. If the CIK is empty, we can just return because there’s nothing to look up. Otherwise, we can get the subject company name, add it to our log, and use the EDGARLookupCIK function to look up the CIK on the SEC’s company database. If it’s an error, we can display an error message, and display our log. An error might be triggered here if the SEC doesn’t have the company name on record, or if the company database simply isn’t responding normally. In either case, if we have an error during the lookup we need to exit the script here.
name = edgar_response[0]; /* get the name from the response */
AddMessage(log,"New Name: %s",name); /* add message */
DataViewCellSetText(dataview,161,2,name); /* set the name */
LogDisplay(log); /* display the log */
} /* */
return ERROR_NONE; /* return */
} /* */
If we got a response from the SEC successfully, we can get the name of the company from the response, log our new name, and use the DataViewCellSetText function to set the name into our project file. We can then display our log file, and return.
This is a pretty quick and simple example of how we can leverage Legato to look information up from the SEC. The EDGARLookupCIK function is very powerful, we can just give it any CIK and it will query the SEC’s database for the company name, series and class information, address information, and other data points. Using it to get just the name is a bit overkill, but it certainly gets the job done. Here is the full script, including the run and setup functions to hook it onto the file open function.
//
// GoFiler Legato Script - Force Company Name Lookup
// ------------------------------------
//
// Forces lookup of a company name when a SC-13D/SC-13G GFP File is opened
//
// Revised 02/07/2020
//
// (c) 2015 Novaworks, LLC. All rights reserved.
//
int run(int f_id, string mode);
int setup();
/****************************************/
int setup(){ /* Setup */
/****************************************/
string fnScript; /* script name */
/* */
fnScript = GetScriptFilename(); /* get script filename */
MenuSetHook("PSEUDO_OPENED_PROJECT", fnScript, "run"); /* set hook on project open */
return ERROR_NONE; /* */
} /* */
/****************************************/
int run(int f_id, string mode){ /* Run */
/****************************************/
handle log; /* log file */
handle edit_window; /* edit window handle */
handle dataview; /* data view handle */
string cik; /* cik to lookup */
string name; /* company name */
string edgar_response[]; /* response array back from SEC */
int rc; /* return code */
string filename; /* filename of project file */
string type; /* get the type of the project */
/* */
if (mode=="postprocess"){ /* if after project opened */
edit_window = GetEditWindowHandle(); /* get handle to edit window */
filename = GetEditWindowFilename(edit_window); /* get filename of opened project */
if (GetExtension(filename)!=".gfp"){ /* if it's not a gfp file */
return ERROR_NONE; /* return */
} /* if it is a GFP file */
dataview = DataViewGetObject(edit_window); /* get datasheet of project */
type = DataViewCellGetText(dataview,5,2); /* get filing type */
log = LogCreate("Look Up Company Name"); /* create a log file */
AddMessage(log,"Looking up CIK for form: %s",type); /* add log message */
FindInString(type,"SC 13"); /* check for SC 13 position */
if(GetLastError()==ERROR_EOD){ /* check if it's a not SC 13 filing */
return ERROR_NONE; /* not a SC 13 filing, exit */
} /* continue if it's an SC 13 filing */
cik = DataViewCellGetText(dataview,159,2); /* get CIK of subject company */
AddMessage(log,"CIK: %s",cik); /* add log message */
if(cik == ""){ /* if no CIK exists */
return ERROR_NONE; /* return without running lookup */
} /* */
name = DataViewCellGetText(dataview,161,2); /* get name of subject company */
AddMessage(log,"Old Name: %s",name); /* add log message */
edgar_response = EDGARLookupCIK(cik); /* look up on CIK */
if (IsError()){ /* if error on lookup */
rc = GetLastError(); /* check the last error */
MessageBox('x',"CIK: %s\rError code: %s", /* display error code message */
cik,FormatString("%0x",rc)); /* display error code message */
LogDisplay(log); /* display the log */
return ERROR_EXIT; /* return error */
} /* if not an error */
name = edgar_response[0]; /* get the name from the response */
AddMessage(log,"New Name: %s",name); /* add message */
DataViewCellSetText(dataview,161,2,name); /* set the name */
LogDisplay(log); /* display the log */
} /* */
return ERROR_NONE; /* return */
} /* */
/****************************************/
int main(){ /* main */
/****************************************/
setup(); /* set the function up */
return ERROR_NONE; /* */
} /* */
 Steven Horowitz has been working for Novaworks for over five years as a technical expert with a focus on EDGAR HTML and XBRL. Since the creation of the Legato language in 2015, Steven has been developing scripts to improve the GoFiler user experience. He is currently working toward a Bachelor of Sciences in Software Engineering at RIT and MCC. |
Additional Resources
Legato Script Developers LinkedIn Group
Primer: An Introduction to Legato
Quicksearch
Categories
Calendar
Feeds
