WRDS SEC

SEC indices and filings on WRDS

Data
WRDS SEC Analytics Suite: wrdssec.wciklink_names, forms, wrds_forms; filings on the WRDS server (/wrds/sec/wrds_clean_filings)
Example
10-K filings of Apple (AAPL) and Wells Fargo (WFC), 2015 to 2021
Author
Qingyi (Freda) Song Drechsler

Download notebook (.ipynb)

This set of code demonstrates how to access various SEC indices and filings on WRDS SEC tool.

Import Python packages and establish connection with WRDS server.

#################################
# Acessing WRDS SEC Filings     #
# Qingyi (Freda) Song Drechsler #
# Date: December 2021           #
#################################

import wrds
import os
import pandas as pd
import numpy as np

# establish connection
conn = wrds.Connection()
Loading library list...
Done

If you don’t already know the data component of the WRDS SEC filings, you may want to explore which tables are part of this library. The most commonly used tables include:

# explore table components within wrds sec library
conn.list_tables(library='wrdssec')
['_qvards_',
 'admin',
 'admins',
 'adminstatecountry',
 'attachmentstab',
 'auditopinionseries',
 'authorizedparticipant',
 'authorizedparticipants',
 'bow_1993',
 'bow_1994',
 'bow_1995',
 'bow_1996',
 'bow_1997',
 'bow_1998',
 'bow_1999',
 'bow_2000',
 'bow_2001',
 'bow_2002',
 'bow_2003',
 'bow_2004',
 'bow_2005',
 'bow_2006',
 'bow_2007',
 'bow_2008',
 'bow_2009',
 'bow_2010',
 'bow_2011',
 'bow_2012',
 'bow_2013',
 'bow_2014',
 'bow_2015',
 'bow_2016',
 'bow_2017',
 'bow_2018',
 'bow_2019',
 'bow_2020',
 'bow_2021',
 'bow_2022',
 'bow_2023',
 'bow_2024',
 'bow_2025',
 'bow_cossim',
 'bow_filingsummary',
 'bow_wordsummary',
 'broker',
 'brokerdealer',
 'brokerdealers',
 'brokerdealerstatecountry',
 'brokers',
 'brokerstatecountry',
 'ccoemployers',
 'ccostatecountry',
 'chars',
 'chiefcomplianceofficer',
 'chiefcomplianceofficers',
 'cisdf',
 'claimfiled',
 'classid',
 'closedendmanagementinvestment',
 'collateralmanager',
 'collateralmanagers',
 'commonstock',
 'commonstocks',
 'contact',
 'contractsecurities',
 'contractsecurity',
 'coveredbyinsurancepolicy',
 'creditlineused',
 'credituser',
 'custodian',
 'custodians',
 'custodianstatecountry',
 'depositor',
 'depositors',
 'depositorstatecountry',
 'derivatives',
 'dforms',
 'director',
 'directors',
 'divdinarrears',
 'divestment',
 'divestments',
 'dividendsinarrear',
 'dividendsinarrears',
 'document',
 'document_info',
 'documents',
 'edgarsubmission',
 'exchangeseriesinfo',
 'exchangetradedfund',
 'exhibits',
 'feederfunds',
 'fforms',
 'filenumberinfo',
 'filenumbers',
 'filer',
 'filerinfo',
 'financialsupportseriesinfo',
 'flags',
 'footnotes',
 'foreigninvestment',
 'foreigninvestments',
 'form',
 'forms',
 'fundlendsecurities',
 'fundtypes',
 'generalinfo',
 'header',
 'idemnityprovider',
 'idemnityproviders',
 'includeallclassesflag',
 'indexfundinfo',
 'interfundborrowing',
 'interfundborrowingdetails',
 'interfundlending',
 'interfundlendingdetails',
 'investmentadviser',
 'investmentadviserhired',
 'investmentadvisers',
 'investmentadviserstatecountry',
 'investmentadvisersterminated',
 'investmentadviserterminated',
 'investmentadviserterminatedstate',
 'issuercredentials',
 'items8k',
 'items8k_list',
 'legalproceedingseriesinfo',
 'lineofcredit',
 'lineofcreditdetail',
 'lineofcreditdetails',
 'lineofcreditinstitution',
 'lineofcreditinstitutions',
 'locationbooksrecord',
 'locationbooksrecords',
 'longtermdebtdefault',
 'longtermdebtdefault1',
 'longtermdebtdefaults',
 'managementinvestmentquestion',
 'managementinvestmentquestionseri',
 'masterfeederfundinfo',
 'masterfunds',
 'missing_forms',
 'nonderivatives',
 'notifications',
 'notregistrantseparateinsuranceac',
 'nsar_answers',
 'nsar_headers',
 'nsar_remainders',
 'officestatecountry',
 'paymentdividendseries',
 'paymenterrorseries',
 'paymenttoagentmanagers',
 'previousproceedingterminated',
 'pricingservice',
 'pricingservices',
 'pricingservicestatecountry',
 'principalstatecountry',
 'principaltransaction',
 'principaltransactions',
 'principalunderwriter',
 'principalunderwriters',
 'principalunderwriterstatecountry',
 'publicaccountant',
 'publicaccountants',
 'publicaccountantstatecountry',
 'registrantfamilyinvcomp',
 'registrantheldsecurities',
 'registrantheldsecurity',
 'registrantinfo',
 'registrantseparateinsuranceaccou',
 'releasenumberinfo',
 'releasenumbers',
 'relyonruletypes',
 'reporting_owners',
 'reportseriesclass',
 'repurchasesecurities',
 'rightsofferingfund',
 'rightsofferingfunds',
 'rightsofferingtypes',
 'rptincludeallclassesflag',
 'rptseriesclassinfo',
 'sec_form_descriptions',
 'sec_item_scores',
 'sec_item_scores_trans',
 'sec_item_words_filings',
 'sec_item_words_freq',
 'secondaryofferings',
 'securityagentidemnity',
 'securityexchange',
 'securityexchanges',
 'securitylending',
 'securitylendings',
 'securitymatterseriesinfo',
 'securityrelateditem',
 'securityrelateditems',
 'seriescik',
 'seriesciks',
 'seriesinfo',
 'seriesinfo1',
 'seriesinfo2',
 'seriesinfo3',
 'seriesinfo4',
 'seriesinfo5',
 'seriesinfo6',
 'seriesinfo7',
 'sharedcredittype',
 'shareholderserviceagentstatecoun',
 'shareholderservicingagent',
 'shareholderservicingagents',
 'sharesoutstanding',
 'sharesoutstandings',
 'signature',
 'signatures',
 'smallcustodian',
 'smallcustodians',
 'smallcustodianstatecountry',
 'smallinvestmentadviser',
 'smallinvestmentadviserhired',
 'smallinvestmentadvisers',
 'smallinvestmentadviserstatecount',
 'smallinvestmentadviserterminated',
 'smallsubadviser',
 'smallsubadviserhired',
 'smallsubadvisers',
 'smallsubadviserstatecountry',
 'smallsubadviserterminatedstateco',
 'smalltransferagent',
 'smalltransferagents',
 'smalltransferagentstatecountry',
 'solecredittype',
 'sponsor',
 'sponsors',
 'sponsorstatecountry',
 'subadviser',
 'subadviserhired',
 'subadvisers',
 'subadviserstatecountry',
 'subadvisersterminated',
 'subadviserterminated',
 'subadviserterminatedstatecountry',
 'swingpricingdetail',
 'table1',
 'table2',
 'table3',
 'terminatedseries',
 'terminatedseriesinfo',
 'terminatedsmallinvadviser',
 'terminatedsmallinvadvisers',
 'terminatedsmallsubadviser',
 'terminatedsmallsubadvisers',
 'transferagent',
 'transferagents',
 'transferagentstatecountry',
 'trustee',
 'trustees',
 'trusteestatecountry',
 'uitadmin',
 'uitadmins',
 'uitadminstatecountry',
 'unitinvestmenttrust',
 'valuationmethodschange',
 'valuationmethodschanges',
 'valuationmethodschangeseries',
 'wciklink_cusip',
 'wciklink_gvkey',
 'wciklink_names',
 'wciklink_ticker',
 'website',
 'websites',
 'wncen_cef_fund',
 'wncen_cef_longtermdebtdefault',
 'wncen_cef_rightsoffering',
 'wncen_cef_securityrelateditems',
 'wncen_etf_aps',
 'wncen_etf_fund',
 'wncen_fund',
 'wncen_fund_admin',
 'wncen_fund_broker',
 'wncen_fund_brokerdealer',
 'wncen_fund_custodian',
 'wncen_fund_feederinfo',
 'wncen_fund_foreigninv',
 'wncen_fund_invadv',
 'wncen_fund_masterinfo',
 'wncen_fund_pricingservice',
 'wncen_fund_prtransaction',
 'wncen_fund_seclending',
 'wncen_fund_shareclasses',
 'wncen_fund_shservagent',
 'wncen_fund_subadv',
 'wncen_fund_transferagent',
 'wncen_reg_accountant',
 'wncen_reg_director',
 'wncen_reg_locbookrec',
 'wncen_reg_underwriter',
 'wncen_uit_admins',
 'wncen_uit_contractsecurities',
 'wncen_uit_depositors',
 'wncen_uit_divestments',
 'wncen_uit_fund',
 'wncen_uit_nrsi',
 'wncen_uit_registrantheldsec',
 'wrds_13f_holdings',
 'wrds_13f_link',
 'wrds_13f_summary',
 'wrds_forms',
 'wrds_forms_reg',
 'wrds_nlpsa',
 'wrds_vc_form1a',
 'wrds_vc_form1k',
 'wrds_vc_form1z',
 'wrds_vc_formc',
 'wrds_vc_formd',
 'wrds_vc_relatedpersonslist']

Usage 1: Query Data by Keywords

Occasionally, researchers want to zoom in on certain filings that contain keywords. Taking the insider filings for instance, researchers might want to focus on any insider transaction that is related to Rule 10b5-1. One can parse out the insider filings that contain this keyword by using the code below.

# Note: there are several supplemental WRDS SEC libraries:
# e.g.: wrdssec_insiders
# use sql language to query data

# extract first 1000 observations from insider table
# that contains '10b5' in text column
footnotes = conn.raw_sql("""
    select * from wrdssec_insiders.footnotes
    where position('10b5' in text)>0
    LIMIT 1000;
    """)

The “where” statement in the rawl_sql block focuses on records whose “text” column contains the keyword “10b5”. Notice the command “LIMIT 1000” in the raw_sql statement limits the output to first 1000 records that match the keyword criteria.

And here is the output of the text column of the first record that meets the criteria.

# print out one text entry to inspect
print(footnotes.iloc[0].text)
The sales reported on this form were effected pursuant to previously given instructions intended to comply with Rule 10b5-1given on 4/6/06.

The stock option exercise reported in this Form 4 was effected pursuant to a Rule 10b5-1 trading plan adopted by the reporting person on December 3, 2018.

Usage 2: Query Filing Index by Ticker

This part of the code enables researchers to look up a company’s CIK using TICKER, and it further looks up the file name path that point to the actual filings on Edgar’s website. WRDS SEC server also mirrors this file path structure.

# Find CIK using company's ticker
ticker = 'AAPL', 'WFC'
begDate = '01/01/2015'
endDate = '12/31/2021'

cikdf = conn.raw_sql(f"""
    select distinct ticker, cik
    from wrdssec.wciklink_names
    where ticker in {ticker}
    """)

This raw_sql block looks up the CIKs for two tickers, AAPL and WFC, using the linking table wciklink_names prepared by WRDS research team. The table below shows the content of the cikdf dataframe.

cikdf
ticker cik
0 WFC 0000072971
1 WFC 0000105598
2 AAPL 0000320193

Next, we can use the CIK information to query these two companies’ Edgar filing path. One can also impose date range conditions to focus on certain years’ filings.

# get the fname and iname information
# for AAPL and WFC
# during 2015 to 2021 fdate period

df = conn.raw_sql(
    f"""
    select distinct a.cik, a.fdate, a.findexdate, a.lindexdate, a.form,
    a.coname, a.fname, a.iname, a.source, b.ticker
    from wrdssec.forms as a
    inner join
    wrdssec.wciklink_names as b
    on a.cik=b.cik
    where b.ticker in {ticker}
    and a.fdate between '{begDate}' and '{endDate}'
    """,
    date_cols=['fdate', 'findexdate', 'lindexdate'],
)

And the output dataframe df contains the following information:

df.head()
cik fdate findexdate lindexdate form coname fname iname source ticker
0 0000072971 2017-11-30 2017-11-30 2017-12-29 424B2 WELLS FARGO & COMPANY/MN edgar/data/72971/0001193125-17-357194.txt edgar/data/72971/0001193125-17-357194-index.htm 3.0 WFC
1 0000072971 2020-06-30 2020-06-30 2020-06-30 424B2 WELLS FARGO & COMPANY/MN edgar/data/72971/0001387131-20-006052.txt edgar/data/72971/0001387131-20-006052-index.htm 3.0 WFC
2 0000072971 2016-04-11 2016-04-11 2016-06-30 8-K WELLS FARGO & COMPANY/MN edgar/data/72971/0001193125-16-537020.txt edgar/data/72971/0001193125-16-537020-index.htm 3.0 WFC
3 0000072971 2017-02-13 2017-02-13 2017-03-31 5 WELLS FARGO & COMPANY/MN edgar/data/72971/0001127602-17-005331.txt edgar/data/72971/0001127602-17-005331-index.htm 3.0 WFC
4 0000072971 2015-03-19 2015-03-19 2015-03-31 8-K WELLS FARGO & COMPANY/MN edgar/data/72971/0001193125-15-098544.txt edgar/data/72971/0001193125-15-098544-index.htm 3.0 WFC

Usage 3: Get Actual Filing Content

For textual analysis projects involving sentiment, readability, topic modelling and many more advanced tasks, researchers need to access the actual SEC filings themselves. WRDS SEC pre-cleans all the SEC filings and has them stored on server for easy access.

# extract form information
# for AAPL and WFC
# from 2015 to 2021

form = conn.raw_sql(
    f"""
    select distinct a.*, b.ticker
    from wrdssec.wrds_forms as a
    inner join
    wrdssec.wciklink_names as b
    on a.cik=b.cik
    where b.ticker in {ticker}
    and a.fdate between '{begDate}' and '{endDate}'
    """,
    date_cols=[
        'fdate',
        'findexdate',
        'lindexdate',
        'rdate',
        'secdate',
        'secpdate',
    ],
)
form.head()
fdate accession form rdate secadate secatime secpdate findexdate lindexdate fsize doccount regcount cik coname fname wrdsfname ticker
0 2018-10-18 0001387131-18-005525 424B2 NaT 2018-10-18 16:32:21 2018-10-18 2018-10-18 2018-10-18 93925.0 2.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0001387131-18-005525.txt 000007/72971/0001387131-18-005525.txt WFC
1 2020-04-06 0001387131-20-003742 424B2 NaT 2020-04-06 14:31:44 2020-04-06 2020-04-06 2020-04-06 108885.0 2.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0001387131-20-003742.txt 000007/72971/0001387131-20-003742.txt WFC
2 2018-05-02 0001193125-18-147961 FWP NaT 2018-05-02 10:42:55 2018-05-02 2018-05-02 2018-05-02 121802.0 3.0 2.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0001193125-18-147961.txt 000007/72971/0001193125-18-147961.txt WFC
3 2017-10-17 0000320193-17-000063 4 2017-10-15 2017-10-17 18:47:42 2017-10-17 2017-10-17 2017-10-20 7931.0 1.0 2.0 0000320193 APPLE INC edgar/data/320193/0000320193-17-000063.txt 000032/320193/0000320193-17-000063.txt AAPL
4 2015-02-05 0000072971-15-000261 SC 13G/A NaT 2015-02-05 12:23:59 2015-02-05 2015-02-05 2015-03-24 18648.0 1.0 2.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-15-000261.txt 000007/72971/0000072971-15-000261.txt WFC

One might notice that under the form column, there are various types of SEC filings, such as Form 3, 4, and many others, in addition to the commonly used 10-K, 10-Q and 8-K filings. As a matter of fact, for the two companies in this example, there are over 40 types of SEC filings.

# inspect form distribution
formDist = form.groupby(['form'])['form'].count()
formDist
form
10-K            14
10-Q            42
11-K             7
11-K/A           1
13F-HR          28
13F-HR/A         1
25               1
25-NSE           9
3               74
3/A              3
4             1113
4/A             13
424B2         2947
424B8            4
5               34
8-A12B          14
8-K            871
8-K/A            3
ARS              1
CERT             7
CERTNYS          8
CORRESP          9
DEF 14A         14
DEFA14A         30
EFFECT           6
FWP            587
IRANNOTICE       2
NO ACT          14
POS AM           3
POSASR           4
PX14A6G         13
PX14A6N          1
S-3              3
S-3/A            3
S-3ASR           8
S-8              8
S-8 POS          1
SC 13D          13
SC 13D/A        78
SC 13G         311
SC 13G/A       683
SD               7
UPLOAD           9
Name: form, dtype: int64

If one wants to focus on certain type of filing (e.g. 10-K), the operation below filters out all the non 10-K filings and reduces the sample to 14 records.

# focus on specific filings such as 10-Ks

form10k = form.loc[form.form == '10-K']
form10k = form10k.sort_values(by=['cik', 'fdate'])
form10k.shape
(14, 17)
form10k.head()
fdate accession form rdate secadate secatime secpdate findexdate lindexdate fsize doccount regcount cik coname fname wrdsfname ticker
6459 2015-02-25 0000072971-15-000449 10-K 2014-12-31 2015-02-25 13:17:09 2015-02-25 2015-02-25 2015-03-24 133829017.0 25.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-15-000449.txt 000007/72971/0000072971-15-000449.txt WFC
3064 2016-02-24 0000072971-16-001045 10-K 2015-12-31 2016-02-24 12:12:43 2016-02-24 2016-02-24 2016-03-04 91696690.0 201.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-16-001045.txt 000007/72971/0000072971-16-001045.txt WFC
4103 2017-03-01 0000072971-17-000278 10-K 2016-12-31 2017-03-01 13:42:47 2017-03-01 2017-03-01 2017-03-01 94112884.0 201.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-17-000278.txt 000007/72971/0000072971-17-000278.txt WFC
2633 2018-03-01 0000072971-18-000272 10-K 2017-12-31 2018-03-01 12:38:37 2018-03-01 2018-03-01 2018-03-01 97208745.0 204.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-18-000272.txt 000007/72971/0000072971-18-000272.txt WFC
1825 2019-02-27 0000072971-19-000227 10-K 2018-12-31 2019-02-27 15:23:51 2019-02-27 2019-02-27 2019-02-27 95197330.0 211.0 1.0 0000072971 WELLS FARGO & COMPANY/MN edgar/data/72971/0000072971-19-000227.txt 000007/72971/0000072971-19-000227.txt WFC

Last but not least, let’s try extract the actual 10-K filings for these two companies. To do so, we will rely on the wrdsfname field in the dataframe above. This variable contains file name and location information for each filing on wrds server under the SEC specific project folder. To complete the file path, we will need to use the os.path.join command below to add on the location of the project folder.

Please note: If you are seeing an error message that “File not on server”, it is most likely due to the fact that you are not running the Python code from a WRDS Jupyter environment. To access WRDS JupyterHub, you will need to click on the “Open JupyterHub” icon after logging into WRDS. Once there, the following block of code should run smoothly.

# with wrdsfname info
# read actual filings on wrds server

path = '/wrds/sec/wrds_clean_filings'

# empty text list to store filing content
textLst = []

for item in form10k['wrdsfname']:
    wrdsLoc = os.path.join(path, item)
    try:
        with open(wrdsLoc) as f:
            textLst.append(f.read())
    except:
        print('File not found on server, ' + item)

The output list textLst should contain the body of the fourteen 10-K filings filed by these two companies in the sample period.

len(textLst)
14

And below is the partial (full content hundreds of pages long) output of the first 10-K filed by WFC on 2015/02/25 for period end 2014/12/31.

# first 3,000 characters of the first 10-K (full filing: hundreds of pages)
print(textLst[0][:3000])
<SEC-DOCUMENT>0000072971-15-000449.txt : 20150225
<SEC-HEADER>0000072971-15-000449.hdr.sgml : 20150225
<ACCEPTANCE-DATETIME>20150225131709
ACCESSION NUMBER:       0000072971-15-000449
CONFORMED SUBMISSION TYPE:  10-K
PUBLIC DOCUMENT COUNT:      25
CONFORMED PERIOD OF REPORT: 20141231
FILED AS OF DATE:       20150225
DATE AS OF CHANGE:      20150225

FILER:

    COMPANY DATA:   
        COMPANY CONFORMED NAME:         WELLS FARGO & COMPANY/MN
        CENTRAL INDEX KEY:          0000072971
        STANDARD INDUSTRIAL CLASSIFICATION: NATIONAL COMMERCIAL BANKS [6021]
        IRS NUMBER:             410449260
        STATE OF INCORPORATION:         DE
        FISCAL YEAR END:            1231

    FILING VALUES:
        FORM TYPE:      10-K
        SEC ACT:        1934 Act
        SEC FILE NUMBER:    001-02979
        FILM NUMBER:        15646876

    BUSINESS ADDRESS:   
        STREET 1:       420 MONTGOMERY STREET
        CITY:           SAN FRANCISCO
        STATE:          CA
        ZIP:            94163
        BUSINESS PHONE:     6126671234

    MAIL ADDRESS:   
        STREET 1:       420 MONTGOMERY STREET
        CITY:           SAN FRANCISCO
        STATE:          CA
        ZIP:            94163

    FORMER COMPANY: 
        FORMER CONFORMED NAME:  WELLS FARGO & CO/MN
        DATE OF NAME CHANGE:    19981103

    FORMER COMPANY: 
        FORMER CONFORMED NAME:  NORWEST CORP
        DATE OF NAME CHANGE:    19920703

    FORMER COMPANY: 
        FORMER CONFORMED NAME:  NORTHWEST BANCORPORATION
        DATE OF NAME CHANGE:    19830516
</SEC-HEADER>
<DOCUMENT>
<TYPE>10-K
<SEQUENCE>1
<FILENAME>wfc-12312014x10k.htm
<DESCRIPTION>10-K
<TEXT>
WFC-12.31.2014-10K

UNITED STATES

SECURITIES AND EXCHANGE COMMISSION

Washington, D.C. 20549

Form 10-K

Annual Report Pursuant to Section 13 or 15(d) of the Securities Exchange Act of 1934

For the fiscal year ended December 31, 2014 Commission File Number 0012979

WELLS FARGO & COMPANY

(Exact name of registrant as specified in its charter)

Delaware No. 41-0449260

(State of incorporation) (I.R.S. Employer Identification No.)

420 Montgomery Street, San Francisco, California 94163

(Address of principal executive offices) (Zip code)

Registrants telephone number, including area code: 1-866-878-5865

Securities registered pursuant to Section 12(b) of the Act:

Title of Each Class 

Name of Each Exchange

on Which Registered

Common Stock, par value $1-2/3

Warrants to purchase shares of Common Stock (expiring October 28, 2018) 

New York Stock Exchange (NYSE)

NYSE

Depositary Shares, each representing a 1/40th interest in a share of 8.00% Non-Cumulative Perpetual Class A Preferred Stock, Series J 

NYSE

7.5% Non-Cumulative Perpetual Convertible Class A Preferred Stock, Series L 

NYSE

Depositary Shares, each representing a 1/1000th interest in a share of Non-Cumulative Perpetual Class A Preferred Stock, Series N 

NYSE

Depositary Shares, each representing a 1/1000th interest in a share of Non-Cumulative Perpetual Class A Preferred Stock, Series O 

NYSE

Depositary Shares, each representing a 1/1000th interest in a share of Non-Cumulative Perpetual Class A Preferred Stock, Series P 

NYSE

Depositary Shares, each representing a 1/1000th interest in a share of 5.85% Fixed-to-Floating Rate Non-Cumulative Perpetual Class A P