Datastream Navigator Update in June 2013

The Datastream Navigator is the internet-based browser that allows you to search and select specific time series on all sorts of subjects. These include company annual report data, stock data, futures, commodities, etc. One of the major changes will involve the first selection step: choosing a specific data category before searching for series within the category. In the new version of the Navigator you can search immediately and you can narrow down the search result using the data categories as a filter option on the left side of the menu. It will be similar to current filter options when you search within a data category. An overview of the main changes can be downloaded here.


Copying tables from PDF to Excel

I usually copy tables from a pdf document by selecting the tables with the mouse and then right-clicking to copy-paste it in another program (like Excel). Depending on the form and type of PDF document this method sometimes works fine. At other times the result is unusable. If this method does not work well you could try a new free tool which has become available: PDF to Excel. This tool may help extract data from tables in PDF documents.

I tested it out on 10 PDF documents and met with mixed results. Sometimes it works fine, but at other times the result was not good. I have seen it be succesful, however, when a regular copy-paste option did not work.
The main reasons for the different results probably have to do with the underlying documents that were converted into PDF. More recent PDF documents are usually easier to copy from then older PDF documents. Also, older scanned documents can be very tricky. I found that the tool also can work well with secured PDF documents. I suggest you give it a try and judge for yourself:


Audit Analytics & companies in trouble

One of the databases that allows you to investigate companies that are in trouble is Audit Analytics (AA). One specific variable in the databases of AA allows you to select companies that are bankrupt or in liquidation, etc. The variable is called “Termination Status“. According to the Data dictionary this variable: “Indicates whether the registrant has formally de-registered with the SEC. We indicate the cause when possible, 2 = Went Private, 3 = Bankruptcy, 4 = Merger/Acquisition, 5 = Termination, 6 = Liquidation“. The variable can be found in the Auditor Opinion part database at search Step 3 in Wharton under the first box with company information/variables.

If you use the Wharton version of AA you can find companies (that are in trouble) only through using the option at Step 2 “Search entire database“. Unlike many databases the AA database can not be searched using a conditional statement at step 2 when choosing this search option. This means that you will have to download some basic company data on all companies for a certain time period and include the variable “Temination Status” in the download (NB: do not choose the Wharton download option Excel/XLS/XML if you think you may get more then 65,000 rows in the output as the download will be cut short at this point. You can choose tab-delimited text because these text files can be opened in Excel).

Afterwards, when you have opened the text file in Excel you can use the filer option to make visible only companies with the status 3 and 6 (and maybe 5, if necessary). See below an example screenshot.

After using the filter to get only bankrupt companies you can use the codes in the column COMPANY_FKEY to do another search in the same database and then look up information on these companies only. The COMPANY_FKEY is the same code as mentioned at Wharton search Step 2: Company Key (CIK). NB: If you have many codes (and many duplicates) you can copy this column to a new Excel sheet and (in Excel 2007) go to the tab Data (or Gegevens) and use the option in the Excel ribbon to remove duplicates. Then copy the codes and create a text file which can be uploaded at search Step 2 (in Wharton): Upload a file containing company codes.

Next at step 3 you indicate which variables are of interest when investigating companies in trouble. For example: it may be intresting to know if there was a Going Concern issue for these companies.


How to get a list of companies in trouble

In some cases it is interesting to investigate companies that have gone bankrupt or are in liquidation. For example: it may be interesting to have a look at the accounting report section of the annual report to see if there is a going concern paragraph in the years prior to company going bankrupt. Or it may be interesting to look at different financial items from the profit-los account or balance sheet, for instance the leverage ratio. The diference between both types of status are as follows:

Bankruptcy is a legal status of a person or other entity that cannot repay the debts it owes to creditors. In most jurisdictions, bankruptcy is imposed by a court order, often initiated by the debtor.” (Wikipedia)

Liquidation is the process by which a company (or part of a company) is brought to an end, and the assets and property of the company are redistributed. Liquidation is also sometimes referred to as winding-up or dissolution.” (Wikipedia)

Some databases offer variables that allow you to select these types of companies. In the European database Amadeus there is the option in the search Menu called Status. Clicking the option in the Menu allows you to select companies of this type. See example:

Compustat alo offers a variable that indicates whether a company is in trouble: Bankruptcy or Liquidation. The variable that indicates this is:

  •  STALT in the Fundamental Annual database (both Global and North America)
  •  STALTQ in the Fundamental quarterly database (both Global and North America).

For a company in trouble the variable will give as output: TL. See example:

Other financial databases may also include this type of information on a company. If you can not immediately ind it, try looking for variables with terms in the name like: status, active, inactive, bankrupt/bankruptcy, (in) liquidation.