Data research using Microsoft Access

A short while ago I was asked how to get data from Audit Analytics on companies that did not have restatements. The idea of the student for the thesis was to compare Audit data from companies that had had restatements with companies that had not. The first part was not that hard: Audit Analytics has a specific restatements part database which you can search through for specific restatements.

The difficult bit was finding companies for the same period that did not have restatements. It was also important to find similar US companies that are active in the same industry (using SIC codes). My solution was using the Compustat North America database to get US companies that had had statements filed in fiscal year 2009.
Using the output from that database I used Microsoft Access to match data from Audit Analytics with output from Compustat North America. Based on the match I could find companies that did not match by looking at the CIK codes. I could then also find matching companies with similar SIC codes.
Using a list of CIK codes from that match and uploading this in Audit Analytics I could get Audit data on companies that had not filed restatements. Afterwards you can use the links within Audit Analytics to the public SEC database Edgar to see original filings.

Here you can view an example movie of this type of research. The subject of the example in the movie is: Find Audit data for US companies that have had restatements on their fiscal year 2009 and compare them with similar type US companies that have not had restatements over the fiscal year 2009.


Audit Analytics and S&P indexes

The database with Audit information offers the option to use a list of tickers or CIK numbers and then (if available) audit information on these companies. Indexes of stock exchanges are very often used as lists to do research. The S&P indexes are the most often used indexes. In the past I have blogged on some of the most often used S&P indexes like the S&P 500, S&P 400 and S&P 600 (together they form the S&P1500). The older blog posts covered Datastream, Compustat North America and the S&P websites.

Audit Analytics (AA) also offers a feature to make it easier to do research on companies that are part of the abovementioned S&P indexes. The option is available (only) through the part database “Auditor – Engagements” in the IVES website version of AA (not available in the WRDS version of AA):

The composition of indexes like those of the S&P 500 do change over time. It is therefore important to know how recent these indexes are. The small I-icon next to the selection box of these (market) indexes leads to a help (pop-up) screen that explains more about this. The indexes on offer in AA are specific to a point in time. Example pop-up:

If you want to use the last composition or historical indexes it may be a good idea to use a database like Compustat North America  or Datastream to get these. Just remember that, when using historical indexes, you may not always recognize the companies in the lists as company names and/or Tickers change!

NB: In addition to the three S&P indexes it is also possible to make use of the Russell 1000, Russell 2000 and Russell 3000 indexes.


Audit Analytics & company changes

The database with Audit information offers two options to search for information on companies, You can search by name, or you can search by company identifier. The company identifiers that can be used to search are Ticker (symbol) and CIK numbers. The CIK numbers remain unique for companies. Tickers may change over time because they identify different companies over longer periods. Companies may also change Tickers depending on how companies evolve over time.

Some of my last posts here I checked in a few database how they deal with company changes and I used as an example FORTUNE BRANDS Inc. This company was recently split into BEAM Inc and the demerged FORTUNE BRANDS HOME & SECUR Inc. I also checked the Audit Analytics database and found that:

1) Name changes are handled fine: searches on old names will find you the company under the new name. Example looking up Fortune Brands will find you BEAM :

2) Tickers changes are tricky: old tickers can no longer be used to search for a company. The old ticker FO of Fortune Brands will not lead to the company BEAM. This means that working with historical Ticker lists of indexes like the S&P 500 from databases or websites may prove tricky.


Audit Analytics versions

The database Contains information on the accounting and financial institutions in the United States of America. The database is used by:

* 18 of the top 20 public accounting firms
* 4 of the top 5 Errors And Omissions insurers
* Over 200 universities
* 7 regulatory bodies.

Audit Analytics covers over 20,000 public companies and more than 1,500 accounting firms from filings at the Security and Exchange Commission. Two versions may be used of the Audit Analytics database: the version availabe through the IVES Group company website and (if you have access to it) the version available through the WRDS portal.

The main advantage of the WRDS version is, that it is easy to download data for many years in one go, using the easy 4 steps that are virtually exactly the same for each database that can be used through WRDS. You may need to study the Audit Analytics manual (= Data dictionary excel file) carefully to find out which data items are available in which part-databases.

The IVES website version, however, is more intuitive and allows for easy switching between the separate parts and clicking on to specific sections for more data. Also, it offers many links to the SEC database Edgar with its public SEC Filings (the full historical Edgar is available through LexisNexis).


Company changes and NYSE TAQ

The NYSE TAQ (New York Stock Exchange – Trade and Quote) database is available through the Wharton portal. It contains tick by tick data (millisecond-timestamped information) on stocks traded on the NYSE stock exchange. I was intrigued by the search result I got from Compustat North America and I performed a test search to find out more about how the split of Fortune Brands Inc is handled. As I expected the ticker symbols FO, BEAM and FBHS give good output results when looking for information in either the Trades or Quotes databases.

What I did not expect was that the Code lookup tool only provides part of the company information.

Both the BEAM Inc and Fortune Brands Home & Security Inc could not be found using Code Lookup. Only information on Fortune Brands Inc could be found:

Ticker symbol: FO
CUSIP: 349631101000

Ticker symbol: FOPRA
CUSIP: 349631200000

Historical ticker codes for indexes like the S&P 500 (regardless of the source) apparently work fine if you want to use them for research.


Company changes in Compustat North America

Recently I found out that it can be difficult to work using historical lists of tickers for companies when you do not use the tickers from the database itself. The example I used to find out how Compustat deals with changes in companies was the former S&P 500 company Fortune Brands: “Fortune Brands (NYSE: FO) was a holding company founded in 1969 as American Brands and later renamed in 1997 and split apart in 2011. The corporate headquarters was in Deerfield, Illinois in the United States. On October 3, 2011, it split into two publicly traded companies: Fortune Brands Home & Security (NYSE: FBHS) and Beam Inc. (NYSE: BEAM). Fortune Brands Home & Security, Inc is engaged in home and security products. Beam Inc is a premium spirits company that makes and sells branded distilled spirits products.”

If you look up the three companies the result is as follows. Compustat North America Fundamentals Annual/Security Daily, using the Code Lookup tool at step 2 (in WRDS):

Can be found through Code lookup:

  • Name: Fortune Brands Inc
    Ticker: FO
    GVKEY: 001408
    CIK: 0000789073
    CUSIP: 349631101
  • Name: Fortune Brands Inc
    GVKEY: 001408
    CIK: 0000789073
    CUSIP: 349631200
    Ticker: FO.PA

Cannot be found using code lookup:

  • Name: BEAM Inc
    Ticker: BEAM
    GVKEY: 001408
    CUSIP: 073730103
    CIK: 0000789073
  • Name: Fortune Brands Home & Security Inc
    Ticker: FBHS
    GVKEY: 188255
    CUSIP: 34964C106
    CIK: 0001519751

Searches by Tickers will give the following result in the Compustat North America Fundamentals Annual database & Security Daily databases:

  • FO and FO.PA will give no results
  • BEAM, BEAM.PA and FBHS will give you results/output.

If you check the Index Constituents for the S&P 500 composition you will find the following codes:

  • Name: S&P 500 Comp-Ltd
    GVKEYX: 000003
    TIC: I0003

When you use the codes to get the historical composition the older ticker FO for Fortune Brands is no longer there. The new ticker BEAM, however, appears to have replaced the FO ticker to allow people to get a continuous series of data (annual reports data or Security data). I do not really agree with this method because, in my view, it does do justice to the fact that the original company has changed very much and cannot be considered to be the new company. Regardless of my personal feelings about this, it is probably best to use the Constituent Index database of Compustat to get the historical composition as it has the most current Tickers. Individual code lookup searches can not always be trusted as the original company: Fortune Brands can still be found, but not the new company BEAM.