Audit Analytics through WRDS

Audit Analytics : Only North American companies

The look and feel of Audit Analytics data in Wharton is quite different, but may be welcome to those who want to stick with the WDS format.
The approach to data is different.

Yves:
Audit Analytics provides detailed research on over 150,000 active audits and more than 10,000 accounting firms.
WRDS:
Quarterly updated, so it may lag behind Yves. 

Content: over 1200 auditing companies and more than 15000 companies. Offers info on who caudits who, and  their fees.
What identifiers can be used to consult Audit analytics? (aside from looking up companies manually)
Ticker and CIK.

(Assuming you are familiar with the WRDS interface, otherwise, see bottom of this post)

Depending on subscription in WRDS, the following  are covered, Audit and compliance; corporate and legal; other independent audit.

Example: 
Audit opinion
Step 1: time scope (year)
Step 2: 
CIK or Ticker
It's possible to lookup manually company by company or use the following

TIC or CIK

• Upload a file containg company codes: when you want to investigate large(r) groups of securities.
• Preferably you obtained the codes from another part of WRDS (esp. TIC, as the contemporary ones are used in this data environment)
• For instance make a download from Execucomp (executives compensation in the US, through Compustat North America) Only Tickers.
• From Compustat either the financial output databases like fundamentals annual or securities, but also index constituents (only a limited number of indexes) CIK can be downloaded as an additional data asset. Index constituents North America http://finabase.blogspot.nl/2014/09/index-constituents-in-compustat-north.html

In the example I used a constituent of S&P 500 over years 2010-12014
Made a download in tabulated TXT and opened it through the wizard in Excel.
Next removed all duplicates. (data > funnel > remove duplicates)
Since I was going to use CIK I made a text file of the CIK codes I downloaded.
(remove all empty cells first)

For instance researching auditor fees..
Use upload a file containing company codes.
Make certain you select CIK

There are a lot of downloadable data. None of which are explained on screen.*
 Example output:

 *) manuals and explanations are found on top of the screen through link Manuals and Overviews
The Data Dictionary is extensive and large (1.4 Mb)
The data is ordered in TAB, so when you want to look up auyditor fees, just go to that tab.
Example:

I suggest you first download the dictionary and keep it at hand when selecting downloadable data.

Final remark: output is always zipped. This implies large output files.


>>>><<<<

TAQ Trades & Quotes (WRDS)

The WRDS portal offers à tic by tic database called TAQ


Downloading tic by tic information always results in enormous output files. It is safer to cut your downloads in 'shorter' time periodes. Keep in mind that data FROM à single day already results in many megabytes (when it's done as a .txt file)

For instance:
Consolidated quotes
 Set range. Mind my in initial remark.
Note how precise the time window can be set... (measuring one day)

Symbol = Ticker..

Output text.TXT

Note that you are obliged to compress your output file <<<< indication of output size
(zip or gzip)
This time window (1st to 2nd Agust 2014), resulted in 6,5 Mb output.
Output size can be cruitical when trying to open in a spreadsheet Program.
I believe STATA can cope with larger files.
Remark: even this limited set gave problems opening in Excel.

Output example:

TAQ short sales
content: database contains data related to SEC Regulation SHO per the terms of the SRO requirement to make Short Sale transactions data available on a monthly basis for trade dates beginning January 2005 through the pilot period ending 2007. (SROs are self-regulatory organizations in the securities industry, such as the NYSE, that create and enforce rules for its members based on the federal securities laws.)

Conditions are similar to Trades and Quotes.

WRDS CRSP Betas

Until today I didn't know you can download betas from WRDS > be it limited, it is possible
(only US and only AMEX or NASDAQ)
I'll show you how.

Note: it is helpful to be familar with Compustat, Compustat - CRSP merged and how they relate.
CRSP - Compustat

Open up the CRSP database.
Select " Stock / Portfolio Assignments"
Select Betas deciles
From WRDS database:  Each portfolio time series is based on a portfolio type defined by CRSP and contains a history of statistics and portfolio assignments for a security. The portfolio time series can be linked to CRSP index returns data to calculate excess returns of a security against its assigned index portfolio at any time during its history.

What you can do, is retrieve a work set from another database or index constituent.

Looking up a single security: Microsoft
(unfortunately WRDS CRSP doesn't say on  which Index the security is noted)

The following is very important and determines whether you get output or not.
The safest bet is, when you draw a work set from either NYSE/ AMEX or NASDAQ, to stick with that index.

Example. From CRSP / COMPUSTAT merged.


Set Time period to investigate.
Search entire database.
Be certain to download Stock Exchange Code, as you have filter you output on that very code. 
Stock exchange code, e.g. Nasdaq (see also over view codes in Compustat manual or internet or here
Stock Exchange code for NYSE / AMEX is 11
NASDAQ is 14 

Note: this can result in very large output set of which you'll have to dismiss the majority of data since you are only interested in securities from NASDAQ or NYSE/ AMEX
Example exchange codes in output

As for output size: this output set contained 13.000 plus rows
After using auto filter (data, use funnel symbol) filtering on exchange code 11 and removing duplicates (use company identifiers like CUSIP, CIK GVKEY or PERMNO, PERMCO)
10.000 plus unique securities remain.
Large output sets!
In this example I made a single file of the Permno and uploaded them into
Stock / Portfolio Assignments > Beta deciles
Output example (download as TAB delimited TXT and open with wizard int Excel)

Note: after downloading and opening a match has to be made between initial download of the filtered set and this output.

Another method is to download the Index constituent form either these indexes.
In Compustat North America the number of downloadable index constituents is imited.
i.e. NASDAQ 100
Look here
! NYSE AMEX not among them !



Be sure to download PERMNO and / or PERMCO

I tried both and came up with either  no results or regular output.

Some helpful links
Adjusting company identifiers


Finabase: Finding historical beta in Datasream
 Financial databases and research blog

Interest Rates, related instruments and databases

(*Note*: for some information links an user connection to Thomson Financial is required, e.g. to Datastream extranet)
Datastream says the following on interest rates and exchange rates:

Interest rates: 20,000+ time series updated daily, with history starting in the 1980s. 

Covered are money market instruments like treasury bills and bonds, LIBOR and other interbanks, certificates of deposits, repo rates and central bank policy rates (various currencies)

Exchange rates: 5,000+ daily rates, 20 years, 168 currencies. Against US dollar and vice versa and e.g. euro.
Datastream Extranet provides a page on this topic. (accessible to Datastream users) > link

Among them an overview (link) of Risk Free rates.
Also see this blog item: link

And a list of Interest Rates in Excel, with an overview of national, international, eurozone, LIBORS, ICAP and government and corporate bond yields: link

WRDS
The WRDS portal gives access the the FAMA French & Liquidity Factors.
Risk Free rates can be found in daily with output code RF.
From WRDS website: 
Q:  Accessing a Daily "Risk Rate Free" Series that can be Used in CAPM Models
A: The easiest way is via our Fama-French Factors page located at:Fama French Factors Daily. The 'Risk Free Rate' is labelled "RF (One Month Treasury Bill Rate)". 

FAMA daily factors

Steps1, setting time window:  (clickable calendar), mind it's daily data
Step 2, selecting the factors (RF)
Step 3, output format > tip: always do TAB delimited

As for T-Bill Bonds in WRDS
Three month Tbill:
Not in Wharton, but in OptionMetrics (not subscribed) has the Stock Option data and PHLX (Philadelphia Stock Exchange) contains Currency Option data (subscribed).
Check CBOE website (for interest rates) < not in Wharton
CBOE (Chicago Board Options Exchange) in Wharton: (subscribed) > Volatility Index (VIX) < click


3 month money market and short term US t-bill:
CRSP daily treasury (treasury daily) - calendar file  and short term rate (government) (not subscribed)
Also Federal Reserve Bank St. Louis (FRED) for interest rates. (registering is free)
Looking on the Wbsite (search field interest rate > link 3 month treasury bill market rate >

This website provides a nice overview of databases with interest rates and risk fee (link)
Another feature of this website is that it offers historic data, going back many years.
Among them: GFD, Global Financial Database.
Registering for an academic account appears to be free.

To try it out I searched for Netherlands, commodities, prices. This is a short overview of results.

At this point remarks show up you haven't got access.
I am not sure if the provided e-mail results in bills.
But I guess that this resource may be interesting for historisc economic research.

Company identifiers - Datastream

In an ideal world all data of all databases would be inter exchangeable without effort. Unfortunately that's not the case. This series of posts will provide hands on info on combining data sets between different data bases.

Why would you bother?

In cases with only a handful of company it would be no big deal to search your companies one by one, but when your work sets exceeds a significant number, say over 50, your searches would get too time consuming.

Using company names in combining data bases:

Not a good idea. Names change, and different data bases may decide to present company names differently: word order, language or abbreviated names.

Example:
Koninklijke Ahold
Datastream: Ahold Kon.
Compustat: KONINKLIJKE AHOLD NV
Amadeus: Koninklijke Ahold N.V.
Audit analytics: Royal Ahold
It's a better idea to use objective methods to identify company by, the 'company identifiers.'

 Datastream

This blog post assumes you are familiar with the basics of Datastream.
If not, then you can consult the following posts:

Balance sheet in Datastream
Event studies 1
Event studies 2
Making a data set is best done in Static Search.
See also: exporting data from Datastream into Compustat

Note: Datastream data sets can be translated ino more databases than just Compustat.

First. Search your set (e.g. a constituent of an index or all active equities on a stock exchange)
The identifiers can be found in datatypes search.
Recommended are Name, time, ISIN, SEDOL, Ticker, Cusip, dscode (datastream code)
Example. (NA is not available)

Converting downloaded Russell cusip for Wharton / Compustat
(USA companies only > download ISIN for non USA companies)
The Wharton machine has a conversion  option under the TAB Research.
(starting page)
When you got a Datastream output with Russell Cusip (USA companies) first remove the U in front of the code with function Right. First make new columns. Next click fucntion key ƒx; next search for right (find) Once found click OK to open the menu
TEXT: Click first cell with Russell CUSIP.
Num Chars: fill in 9
OK
You got CUSIP 9












Double click the + in the bottom right of the newly created code.
Select all new CUSIPs and copy into Notepad.
Save.
Press TAB Research and click option CUSIP converter
Use browse Button and select the desired output number (in my case I tested for CUSIP 6)
This works wonderfully.


Uploading CUSIP in Datastream
If you want to upload company identifiers from other databases into Datastream ISIN works best.
Wharton North America does not download ISIN (neither does SDC M&A).
From Wharton North America you can download CUSIP. These have to be converted. You can do that like this:
If you downloaded 9 digit cusip from Wharton / Compustat, and you want to use it in Datastream and / or change it into an ISIN code, you might consider doing the following.

Convert it from a Worldscope code.

CUSIP 123456789 Put U in front: U123456789
(this only works for companies that are available through Worldscope)
Method:
Make a column with U
Have your column with CUSIPS
Use function Concatenate (tekst samenvoegen) in Excel.
 = CONCATENATE ( A1, A2)

Insert new column. Type U: Double click + in right bottom corner
Use Concatenate formula:
= CONCATENATE ( A1, A2)

Double click + symbol
Upload into Datastream using the option Create list from range (the selected CUSIPS)

Perform Static search:
Find your uploaded list with the list picker
(magnifying glass symbol right beneath Select/find series button)
Datatypes: name, ISIN.
Also select DScode for your output.
Submit
Now you got ISIN

Use the ISIN for Time series data: repeat upload from range.

Also see mark Bruyneel's blog on this subject. click
Information on the create list from range option click
Same item for list picker click
Also see easy Excel tips click