Easier Access to 8-K Details Using Pandas

I received a message from Antonis Kartapanis. Antonis is an accounting faculty member at TAMU who uses directEDGAR for some of his research. He wanted to identify which of a set of filers in his sample made 8-K filings based on specific reason codes. He started off with the intention of running a search using our platform but then he wondered if it would not just be easier to pull what he needed from the database we just made available.

Antonis downloaded the database and after scanning the code wondered if it could be simplified by using Pandas rather than the CSV module. While I am aware of Pandas I have not used it as I just haven’t taken the time to test whether it would benefit us in production. Loading a set of CIKs from a file and then writing the results back is much more direct with Pandas as it handles the CSV file interactions ‘under the hood’. Since I did not want to take the time to start poking at Pandas I asked Antonis if he would share the code. He was kind enough to do so. I have pasted it below. Much simpler – it looks like at some point I need to invest in learning Pandas.

# Import relevant libraries
import pandas as pd
import sqlite3
# Connect to the database
conn = sqlite3.connect(r'C:\Path\To\SQLITE\8Kmeta.db')

#The following reads in a CSV file that has a column of CIKs and just one other data column - we want the CIKs

# To use the CIKs in the query we need a tuple object

input_df = pd.read_csv(r'C:\Path\to\CIK_FILE\data_CIK_test.csv')

cik_tuple = tuple(input_df['CIK'].unique()) # No need to list a given CIK twice, so let's keep one instance of each

# SQL query to execute
sql_query = """SELECT *  FROM eightKAcceptance
               WHERE  ITEM_4_01 = 'YES'
               and upper(ACCEPTANCE_TIME) != 'NOTAVAIL'
               and  date(SEC_FILING_DATE) > date('2007-05-01')
               and  date(SEC_FILING_DATE) < date('2011-07-05')  
               and CIK in {}""".format(cik_tuple)

# Execute query and close connection
output_df = pd.read_sql_query(sql=sql_query, con=conn)
conn.close()

# Output Data
output_df.to_csv(r'C:\Path\To\Output\test_8K_new.csv', index = False)

Leave a Reply