Audit Analytics update

The database Audit Analytics was recently updated and now includes more options to merge the data from it’s database(s). Previously only the options Ticker (/Best Edgar Ticker) and the CIK were available as codes representing corporate entities.

With the new update, two more codes are now available: CUSIP 9, and ISIN. These two identifiers are available in many more databases. Combining data from other databases with data from Audit Analytics is now much improved. Important: your license to the Audit Analytics databases may not as a default have these codes added. I guess that it may require an expansion of an existing license to get these codes.

Also important, is, the fact that at the moment both new codes are not always available in the WRDS version of the Audit Analytics databases. This may have to do with the fact, that the databases in WRDS are updated on a quarterly basis while the main website is updated daily.

To make it easier to work with multiple databases I updated my overview of databases (that I am familiar with) and which identification codes are available in these sources.

SPAC companies and FactSet

The database FactSet has data on SPAC companies in two separate databases: the M&A database, covering data on Mergers and Acquisitions, and the IPO database, covering initial public offerings.

1) Finding SPACs ipo data
The Idea Screening option allows you to search for this type of data under “IPOs & Follow-Ons”:

The database has the variable “Blank Check Company” that allows you to quickly select companies of that type in the database. There is a specific sub-categorie for Special Purpose Acquisition Company availabe. Since a SPAC is not always clearly distinguishable from the other two types it may be best two select them all and later decide based on additional data:

To further narrow down the result it may be necessary to include items like the “Business Description” in the output. SPAC companies are regularly called Blank Check companies and not specifically indicated as SPAC. Sometimes the Business Description will indicate a different text: “SPAC”, or “Special purpose acq co (SPAC)” or a variation on this. The name of a company often also contains keywords or terms like acq or acquisition or SPAC. A report with the output could look as follows:

2) Finding SPAC M&A data
The Idea Screening option allows you to search for this type of data under “M&A:

In this database you can find SPAC companies involved in M&A’s using the variable: “Blank Check/SPAC Acquirer”. Example:

Afterwards, when creating a custom report, it is a good idea to add variables like FactSet ID numbers / identifiers to later add additional financial data through the Excel Add-in.

3) Using the Excel add-in to find SPAC companies
Initially you could have a list of companies that was either generated using FactSet or through a second database. It is possible to find out if any listed entities are either a certain Blank Check Type company or a SPAC through variables like:

  • “Blank Check Type”
  • “Is a SPAC related entity”

SPAC companies and SDC Platinum

A special purpose acquisition company (SPAC) is a company with no activities that is formed strictly to raise capital through an initial public offering (IPO) for the purpose of acquiring an existing company. Also known as “blank check companies”. A blank check company is a publicly-traded, developmental stage company that has no established business plan. It is used to gather funds as a startup or, more likely, it has the intent to merge or acquire another business entity. SPAC companies have become popular in America the past few years and, recently, also in Europe.

Recent Dutch examples of SPAC IPO’s from the past few years:

  • Dutch Star Companies ONE NV
  • Dutch Star Companies Two BV
  • ESG Core Investments BV
  • European Biotech Acquisition Corp
  • Pegasus Acquisition Company Europe BV.

The database SDC Platinum (also part of Eikon) has data on SPAC companies in two separate databases: the M&A database, covering data on Mergers and Acquisitions, and the Global New Issue database, covering IPO data.

1) Finding SPACs ipo data
The database has the variable “Blank Check Company Flag (y/n)” that allows you to quickly select companies of that type in the database. There is no specific sub-categorie for SPAC availabe. The flag variable is available by searching in the “All Items” tab:


To further narrow down the result it may be necessary to include items like the “Business Description” in the output. SPAC companies are regularly called Blank Check companies and not specifically indicated as SPAC. Sometimes the Business Description will indicate a different text: “SPAC”, or “Special purpose acq co (SPAC)” or a variation on this. The name of a company often also contains keywords or terms like acq or acquisition or SPAC:


2) Finding SPAC M&A data
In this database you can find SPAC companies involved in M&A’s using two variables: “Acquiror is a Special Purpose Acquisition Vehicle” and, perhaps, to avoid missing anything, the variable “Blank Check Company Flag (y/n)”. Using the Logical Set Operator option you can combine both into a single search result. If it concerns M&A deals that have been completed, then it would be a good idea to add the Deal Status (= Completed or Unconditional). Example:


Afterwards, when creating a custom report, it is a good idea to add both variables also to the report, and including the deal synopsis as well as the Business description for the acquiror.

N.B.: To combine data from the IPO database and M&A databases from SDC Platinum, the best variables are either using CUSIP 6 and/or the ticker (or primary acquiror ticker) codes. Additional options (directly or indicartly useful) may be Sedol codes or ISIN codes when available (Additional information).

Return data for Fama–French model

Recently somebody asked me where to look for monthly return data for the US market. The data was needed for Fama-French factor model. It seems to me that the U.S. market factor data is available from Kenneth R. French’s website (2019). The data provided here is mainly in a TXT or CSV format and can be processed and analysed using different programs.

Some of the Fama-French data is also available through other sources, for instance, the WRDS platform (Wharton Research Data Services). The data that they provide can be found as is labled as: “Fama French & Liquidity Factors” or “Fama-French Portfolios and Factors”. Access and downloading through this platform does, however, require a personal or organizational subscription.

I do not think all data from the original Kenneth French website is available but it does have additional related data: Pastor-Stambaugh Liquidity series, Sadka Liquidity measures, and China Factors (Liu, Stambaugh and Yuan).
At the Vrije Universiteit Amsterdam we have access to it (screenshot).

S&P Indexes in Compustat no more

Recently the company behind the S&P Indices (S&P Dow Jones) has decided to withdraw their constituent data from the Capital IQ Compustat databases. This means that it is still possible to get the prices for the overall indices, but it no longer is possible to get detailed data on individual companies as they move in and out of the lists over the years. Lists like S&P 500 are heavily used in much research as a benchmark list for market developments.

I know of only one database (available to me) at the university that still allows for the generation of constituent lists at the Vrije Universiteit Amsterdam and this is the database FactSet. There is one drawback compared to the lists that used to be available through Compustat, however: where Compustat made it possible to generate a multiyear list over time, FactSet only allows you to generate a daily list.
If you need multiyear lists from FactSet you will have to download multiple lists for years and then combine them yourself.

I have created an example movie on how to download an index list and put it on Youtube:

Using FactSet requires FactSet software as well as a FactSet account. Information on this for personnel and students at the university is available here.

N.B.: The Compustat database Execucomp has annual data on executives for companies that are (or have been) part of the S&P 1500 list. A download of company codes (GVKEY, CUSIP, Ticker) could perhaps serve as a rough list on which companies are part of the index over a time period. It is only a rough estimate, as the data is based on annual SEC filings and the index changes daily. Execucomp includes data going back to 1992.

Quarterly IBES data (in WRDS)

Understanding the forecast data in the I/B/E/S/ database (Institutional Brokers Estimate System database from Refinitiv) was not as straightforward as I thought it would be. What threw me off was the way I initially looked at the data. Below you find an example download I did for 1 company (Wendy’s Co.) for 1 year. I downloaded the quarterly forecast EPS data for 2011 from the Summary Statistics, Unadjusted part database from the Unadjusted Summary collection of I/B/E/S/ databases.

The three columns that are important where it concerns understanding the data, are marked green.

  • Forecast Period End Date (SAS Format) = this date indicates on what date the calendar quarter ends for which the forecast was made
  • Forecast Period Indicator (FPI) = 6 indicates quarter 1, 7 means quarter 2 , 8 is quarter 3 and 9 is actually quarter 4
  • I/B/E/S/ Statistical Period (SAS Format) = The date when the calculated summary statistics were finalized, which could only be done when the final data on the forecast quarter had been collected. That is why monthly this date is sometimes more recent = past the actual forecast quarter.

What was confusing to me was how I needed to interpret the Forecast Period Indicator. I could not make sense of it based on the information in the I/B/E/S/ manual. After talking to the owners of the database it finally made sense:

Where the FPI indicates 6 what is meant is: the current quarter. That means that the forecasts for these rows that have the number 6 were done within the same quarter as the Forecast Period quarter (indicated in column P). Where the FPI says 7, that means that the forecast data on the calendar Period quarter is from the previous quarter. For the FPI 8 the forecast data is from two quarters back and where the FPI says 9 it is 3 quarters before the quarter indicated as the Forecast period quarter.

To make it easier to understand I have below calculated the end date of the actual quarter in which the forecasts were done based on the FPI in column G: and the Forecast quarter dates in column P. The Excel formula I used for this (column Q) is:


In column R I decided to use the Excel formula =EOMONTH() to get the actual End date of the month and Column S has a formatted date to format the date the way the date in column P is formatted. I used Excel formulat =TEXT(R2;”mm/dd/yyyy”) for this.

The result looks as follows:

The extra columns (yellow) indicate the quarter in which the forecasts were made by analysts for the calendar quarter indicated in column P.

FactSet IPO Offer Method field data

The database has a dataset on IPO and IPO-related information. Below are the current definitions of offer method typings in the field for Offer method available in FactSet for IPO’s.

1) Bookbuild Issue
Unlike the U.S. and some other larger countries where the offer price is determined based on market conditions, a bookbuild issue is where the bookrunner collects bids from potential investors on how much they are willing to pay. After the bookbuilding process has ended, the bookrunner determines the price at which the shares will be sold.

2) Best Efforts
An underwriting arrangement in which an underwriter agrees only to use its best efforts to sell the securities as the issuer’s agent. The underwriter does not purchase the securities itself and has no obligation to purchase any amount not purchased by investors.

3) Firm Commitment
An underwriting in which an investment banking firm commits to buy and sell an entire issue of stock and assumes all financial responsibility for any unsold shares

4) Direct Listing
Company raises capital in a private placement to accredited investors as the initial step in financing. Amount of capital raised varies depending on sufficient capital needed for company’s growth plans and registration and listing costs before it achieves an exchange listing (typically, within six to twelve months after the private placement offering is completed).

5) Placing
Placing is the method by which a company lists its shares on a stock exchange and raises capital by selling shares to institutional investors. These shares are not generally marketed to retail investors at the time of the issue unless the correct approvals are included in the offer documentation.

6) Offer for Subscription
Offering where a minimum level of subscription for the shares, and if this is not reached the offer is withdrawn.

The second and third methods are both considered bookbuild issues, but there is a Bookbuild issue type separately indicated. So, how do those three compare, because these methods seem to overlap in method typing?
According to FactSet helpdesk the offering type is a field that they are looking to change. One of the reasons is that they agree that the offer methods are not distinct. Also, in a given deal, there can be multiple offering types used. FactSet is going to change this in the future. At the moment they are considering offering 3 new separate fields where information is made available on the offering type. The fields already exist at the backend but are currently not available through the FactSet database. The new fields will describe:

  • how the deal was priced
  • what was the involvement from the underwriters
  • and how were the shares sold/listed on the exchange?

Using the existing structure of the database FactSet needs to choose which offering method is the best of each IPO based on data collected in those three fields. The information on the Offer method may therefore deviate considerably from what is indicated in the company prospectus.

N.B.: For example, compare the Offer method in FactSet with the actual prospectus for Basic-Fit NV. The Prospectus indicates a combination of offer methods. The filings for companies in FactSet are quickly available through the Company / Security tab by looking up the company using the name or ticker BFIT

FactSet and ESG data

At the university I get a lot of questions on the availability of microeconomic level data on subjects like corporate responsibility with regard to Social, Environmental, Governance or Economic topics. Through FactSet it is possible to get access to this type of data but it very much depends on what FactSet has available as well as what sources / datasets can be accessed through FactSet.

Examples of datasets that may be accessible through FactSet are: MSCI ESG fund metrics (Moody), and Sustainalytics (Morningstar). I am not sure if the Asset4 ESG dataset from Refinitiv (Formerly Thomson) can also be made available. In all cases it does, however, require an extra license from the company that produces and maintains these datasets.

What is available then, through the default basic license for FactSet, is the following:

  • The lastest ESG data from Sustainalytics (no history): screenshot
  • 6 years biannual ESG data from FTSE: screenshot
  • Data on Green bonds (see previous post)
  • Limited data on ETF funds with investment strategy ESG: screenshot
  • Limited Governance data on board members, board structure, committee membership, etc. (no history): screenshot

N.B.: I got the tip on the availability of FTSE ESG data in FactSet from Dries Laurs, Msc.

FactSet and historical ownership of companies

The database FactSet has a specific dataset that allows you to view and download historical shareholder data for companies. The availability of data depends on the coverage by FactSet of course. There are two levels available: detailed ownership and aggregate data.

The main interface of FactSet allows you to view the ownership data in two ways:

  1. Through the Company tab
  2. Using the Ownership tab.

1) Then company tab has a menu on the left side which includes the option Ownership.
Usually when you click the Company tab you get the last company that you looked up. If you have not yet done this before you can use the search box at the top left corner to search by name or code (ticker or other codes). When the company details appear you can select the ownership option from the menu. Several options are available. This includes equity as well as debt ownership. The data is available on the level of the instrument as well as the level of the company. Options to change the presentation are available at the top.

2)The Ownership tab works in the same way as the company tab.
Search for a company by name or code and you get the result presented. There are two levels: the top level concerns the ownership of the company itself. Below this there is a section covering the ownership by the company itself (of other companies / subsidiaries).

One of the ownership options to get shareholder data is Historical Analysis. This allows you to get multi-year data on ownership as it changes for the company for the following 5 shareholder types: Institutions, Institutions/Insiders, Mutual FUnds/ETFs, Insiders/Stakeholders, and Beneficial owners. The overview can be changed for the number of years as well as the frequency by clicking the button Dates at the top.

If you wish to download data this can be done using the green arrow button at the top right corner.

In addition to the main interface it is also possible to download some ownership data through the FactSet Excel Add-in. There appear to be only two options to download shareholder data: current data of the most recent nature (depends on company filings), and aggregate historical ownership data. You can find an example here:

Step 1: Use screening to make a selection and download this to Excel

Step 2: In Excel organize the data to get the codes in the first row of the sheet.


Step 3: Use the side bar to download Aggregate ownership data.
Important: It is usually necessary in Excel to first indicate the time period if you wish to download security level data. On company level the time period of available data is more limited.


1) If the company or ownership tabs/apps (or both) are unavailable you can add them using the following option and clicking the green Plus next to the tab/app in the list.

2) If you wish to download detailed historical ownership data for lists of companies you will have to create a template in Excel to do this or contact the FactSet helpdesk to get them to do it for you.
3) For a step by step approach for downloading data, watch the movies on FactSet here.

Stata solution to reshape FactSet data

In two previous posts I showed examples on how to reshape data from wide to long format. Both post were about Datastream, but one was about regular downloads and one about large datasets. In Stata reshaping data usually works fine but may sometimes not work very well. In some versions of Stata or in some cases the reshape command seems to work only in part and not all data is reshaped. The solution to the problem was changing the usual reshape command slightly.

1) In the example I first prepared the FactSet data as per usual. The bonds data in the example first looks as follows:

First I used Copy+Paste > transpose to change the rows and columns. Then I used the Excel formula =”Y”&TEXT(C1;”yyyymmdd”) to change the dates for Stata. Finally I copy+pasted as values to get rid of the Excel formulas. The data now looks as follows:

2) Normally, after importing this data in Stata , I would use the command:
reshape long Y, i(Code) j(origdate). In some Stata versions this is fine, but in other editions this does not work very well. The way to get this to work is now adding the word string to the command as follows:

reshape long Y, i(Code) j(origdate) string

N.B.: Afterwards I also need to do some work on the dates etc. to be able to get the data in a format to be able to work with it. Reminder: for really large downloads from FactSet you may have to reshape the data in batches because Stata versions may be limited by the number of variables it can work with. Afterwards the datasets can be merged by appending them. You can download an example do file here with the commands and an excel file to work on with this.