If you are anything like me, you may like looking in the past to see where you have been, which will hopefully allow you to figure out where you are going in the future. The more that I immerse myself in programming, the more documentation that I end up creating, just in order to know where I have been and what I have done before. As I program, I like to create documentation as I go along. It makes the process easier in the long run. Sometimes, however, I find myself having to pick up a project and having to create documentation from the start. It would be easier if someone had been keeping records all along, but if I have to know where I’ve been, I need to go through the whole process from the beginning.
Friday, October 19. 2018
LDC #107: All Our Filings In a Row
Keeping track of EDGAR filings is similar. If you keep track of filings that you’re doing as you go along, you have a running count of filings. Sometimes things change, though. Maybe you need to change the way that you’re storing filings, or have to move systems. GoFiler does a pretty decent job keeping track of filings using its mailbox feature, but if you want to search through filings or filter them out, your best bet is to export the data.
Currently there is no way to do so in GoFiler, so today we are going to write a quick script to export all of our previous filings to a CSV file. In order to do that, we’re going to go through each entry in the mailbox, get the properties, record them, and then write everything out to a file that has been chosen by the user.
Let’s take a look:
int setup() {
string fnScript;
string item[10];
int rc;
item["Code"] = "ENUM_MAILBOX";
item["MenuText"] = "&Export Mailbox to CSV";
item["Description"] = "Export Mailbox\r\rPrint the mailbox to a CSV file";
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;
}
int run(int f_id, string mode){
string data[1000];
string filenames[10000];
handle output;
string info;
string mailbox;
string gfc_ini;
string destfile;
string responsecode;
string test;
int num_files;
int ix;
if (mode!="preprocess"){
return ERROR_NONE;
}
gfc_ini = GetApplicationDataLocalFolder()+GetApplicationName()+" Settings.ini";
mailbox = GetSetting(gfc_ini,"EDS","Mail");
filenames = EnumerateFiles(mailbox+"\\*.txt");
num_files = ArrayGetAxisDepth(filenames);
destfile = BrowseSaveFile("Save CSV Output File","CSV Files (*.CSV)|*.csv");
if(IsError(GetLastError())){
return ERROR_CANCEL;
}
if(GetExtension(destfile)!=".csv"){
destfile+=".csv";
}
output = CreateFile(destfile,FO_WRITE);
ProgressOpen("Writing Mailbox to File");
WriteLine(output,"Accession Number, Company Name, Form Type, Result, Test or Live, Confirming Copy, Receipt Date, Acceptance Date, Filing Date, Number of Documents\r\n");
for(ix = 0; ix<num_files; ix++){
ProgressSetStatus("Reading mailbox entry number %d",ix+1);
ProgressUpdate(ix,num_files);
info = FileToString(AddPaths(mailbox,filenames[ix]));
data = EDGARResponseProps(info);
if (ArrayGetAxisDepth(data)>0){
test = "LIVE";
if (data[4]=="1"){
test = "TEST";
}
switch (TextToInteger(data[3])){
case EM_RESULT_TEST_FAIL:
responsecode = "Test Filing Failed";
break;
case EM_RESULT_TEST_PASS:
responsecode = "Test Filing Passed";
break;
case EM_RESULT_TEST_PASS_XBRL_FAIL:
responsecode = "XBRL Truncated";
break;
case EM_RESULT_LIVE_FAIL:
responsecode = "Live Filing Failed";
break;
case EM_RESULT_LIVE_PASS:
responsecode = "Live Filing Passed";
break;
case EM_RESULT_LIVE_PASS_XBRL_FAIL:
responsecode = "Live Filing Passed/XBRL Fail";
break;
}
WriteLine(output,"\""+data[0]+"\",\""+data[1]+"\",\""+data[2]+"\",\""+responsecode+"\",\""+test+"\",\""+data[5]+"\",\""+data[6]+"\",\""+data[7]+"\",\""+data[8]+"\",\""+data[9]+"\"");
}
}
ProgressSetStatus("Writing CSV file.");
CloseFile(output);
ProgressClose();
return ERROR_CANCEL;
}
int main() {
setup();
return ERROR_NONE;
}
Much of this we have gone over before a few times, so I will be quickly going through some of the basics.
int setup() {
string fnScript;
string item[10];
int rc;
item["Code"] = "ENUM_MAILBOX";
item["MenuText"] = "&Export Mailbox to CSV";
item["Description"] = "<B>Export Mailbox</B>\r\rPrint the mailbox to a CSV file";
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;
}
int main() {
setup();
return ERROR_NONE;
}
The setup and main functions form the building blocks of the script. If run through the IDE, the script will set itself up. Otherwise, if the script is located in the extensions folder of GoFiler, it will run the setup function when the application starts. The setup function defines a new menu function called “ENUM_MAILBOX”, which is placed into our Tools ribbon with the label “Export Mailbox.”. This new menu function is a placeholder to which we can hook in our script. We set up a hook on the function that calls the run function. Now the building blocks are done and we can dive into the meat of the script.
int run(int f_id, string mode){
string data[1000];
string filenames[10000];
handle output;
string info;
string mailbox;
string gfc_ini;
string destfile;
string responsecode;
string test;
int num_files;
int ix;
if (mode!="preprocess"){
return ERROR_NONE;
}
gfc_ini = GetApplicationDataLocalFolder()+GetApplicationName()+" Settings.ini";
mailbox = GetSetting(gfc_ini,"EDS","Mail");
filenames = EnumerateFiles(mailbox+"\\*.txt");
num_files = ArrayGetAxisDepth(filenames);
destfile = BrowseSaveFile("Save CSV Output File","CSV Files (*.CSV)|*.csv");
if(IsError(GetLastError())){
return ERROR_CANCEL;
}
if(GetExtension(destfile)!=".csv"){
destfile+=".csv";
}
output = CreateFile(destfile,FO_WRITE);
ProgressOpen("Writing Mailbox to File");
WriteLine(output,"Accession Number, Company Name, Form Type, Result, Test or Live, Confirming Copy, Receipt Date, Acceptance Date, Filing Date, Number of Documents\r\n");
First off we define all of the variables that we need in order to keep track of all of the filings. Then we retrieve some more required information. We get the location of the settings ini using the GetApplicationDataLocalFolder function and then retrieve the location of the mailbox from the ini file using the GetSetting function. Next we put all of the text files in the mailbox location using and put the names into our filenames array. We then get the number of files by checking the array depth and we store that number for later use.
Eventually we ask the user for a location and filename to save our .csv file using the BrowseSaveFile function. We check to see if the user exits the dialog without clicking OK and cancel the operation if they do. We then also check to see if the destination file has a “.csv” extension with the GetExtension function. If it doesn’t, we add it so that the file will work when we are finished. The last bit of preparation that we do is to create the file and open it for writing with the CreateFile function. We then open a progress box to inform the user that we’re starting, and then we put the header row into the CSV file with the WriteLine function. At this point we have now gathered all of the initial information and performed starting tasks, so we can dive into the main loop now.
for(ix = 0; ix<num_files; ix++){
ProgressSetStatus("Reading mailbox entry number %d",ix+1);
ProgressUpdate(ix,num_files);
info = FileToString(AddPaths(mailbox,filenames[ix]));
data = EDGARResponseProps(info);
if (ArrayGetAxisDepth(data)>0){
test = "LIVE";
if (data[4]=="1"){
test = "TEST";
}
switch (TextToInteger(data[3])){
case EM_RESULT_TEST_FAIL:
responsecode = "Test Filing Failed";
break;
case EM_RESULT_TEST_PASS:
responsecode = "Test Filing Passed";
break;
case EM_RESULT_TEST_PASS_XBRL_FAIL:
responsecode = "XBRL Truncated";
break;
case EM_RESULT_LIVE_FAIL:
responsecode = "Live Filing Failed";
break;
case EM_RESULT_LIVE_PASS:
responsecode = "Live Filing Passed";
break;
case EM_RESULT_LIVE_PASS_XBRL_FAIL:
responsecode = "Live Filing Passed/XBRL Fail";
break;
}
WriteLine(output,"\""+data[0]+"\",\""+data[1]+"\",\""+data[2]+"\",\""+responsecode+"\",\""+test+"\",\""+data[5]+"\",\""+data[6]+"\",\""+data[7]+"\",\""+data[8]+"\",\""+data[9]+"\"");
}
}
At the beginning of each loop, we update the status to say how far into the process we are. We then take the current filename and add it to the mailbox path to get the full path to the current file, which is put into a string. Then we take that string and run it through the EDGARResponseProps function. This function takes an accession notice message text and returns an array full of named data values. We are putting all ten of the values from that function into this CSV list. Most of them can be put in as is, but a couple we want to format. We assume filings are live unless the function returns TRUE on the test flag, in which case we are putting the “TEST” text into the output. The other formatting we do is to check the status of the filing, which has six possible outcomes. Since this value is stored as an int, the easiest thing to do is to create a switch statement where we make the responsecode variable a human readable string value. All of this information gets put into CSV format and added to the output file.
ProgressSetStatus("Writing CSV file.");
CloseFile(output);
ProgressClose();
return ERROR_CANCEL;
}
After the loop is done running, we set the status to say we’re almost done, close the file, close the progress bar, and return out of the function. We return ERROR_CANCEL here because this is a preprocess on a menu function that does not really exist. We do not want to have GoFiler fail to find a nonexistent menu function, so even though the process is successful, we still return a cancel.
There we have it, a quick and simple function to write all of our mailbox entries to a single portable file. It allows us to create a human readable picture of all of the filings that we have done in the past and allows you potentially to run Excel functions on the resultant data. Now we have a full picture of what we have done in the past and can therefore make predictions for the future.
 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
