Bitcoin exchange rate data

The past year the digital currency Bitcoin has increasingly caught the eye of governments as well as the investment world. This is in part because more and more online companies as well as regular companies are accepting bitcoins as payment. Bitcoin is actually an open source peer-to-peer electronic money and payment network introduced in 2008. Some limited research on the exchange has also been done by the Academic world. More information on it is widely available through Wikipedia and the Bitcoin website.

The exchange rate of the Bitcoin to US Dollars, Euros and other currencies has been reaching extreme hights lately. It looks like the use and trading in bitcoins may be heavily influenced by speculators. If you want daily exchange rate data or even intraday data for the different exchanges or markets that handle trades, you can visit the website called Bitcoin charts.

Using this website you can call up specific data on different currencies. General and recent information is available if you select a currency + market/exchange combination. More detailed data (including intraday data) can be called up by clicking the option “view larger chart” at the top left corner of the graph:

Using the options in the tab “Pricechart” you can select the frequency, time period etc and draw a specific chart. Below the graph an option will be available to download the raw data:



Compustat Index code lookup fixed!

Compustat databases like Global and North America have great part databases that can be used to find out what the historical composition is for indexes of stock exchanges. Other databases (like CRSP and datastream) offer similar options but the Compustat option is a very easy to use one. The part databases that can be used for this, are called: Index Constituents. For the past few weeks the Code lookup option to find the codes for indexes in Compustat Global no longer worked in Wharton. The helpdesk has now reported that the problem has been fixed. Today I did a small check and I can confirm that it is now possible again to lookup the Ticker and GVKEYX codes again.


Researching dates of a listing (IPO)

Last week I did some research on IPO dates in the Databases SDC Platinum and Compustat North America. I was looking for the Original IPO (= very first exchange listing of shares at any stock exchange) dates for US companies. SDC Platinum database Global New Issue offers a simple variable to select the Original IPO records in the database: Original IPO (Yes/No). By including search variables for the security type and the nation you can, for instance, get all these dates for shares in a specific country. The variable that lists the (Original IPO) date of the issue is: Issue Date. Be sure to include the variable in your (custom) report to get it in the output.

The Compustat databases Global and North America also have a variable that lists the original IPO date. The variable is called IPODATE and the manual gives the following description: This item is the date of a company’s initial public stock offering. If the date of a company’s initial public stock offering is not available, the first trading date in the major exchange is used. This description is confusing and allows room for interpretation. After contacting the helpdesk I was told the following:

  • IPODATE has been introduced to Compustat database(s) in 2002
  • If a date is given on company that was listed before 2002 the variable IPODATE gives the date when a company was listed on a major exchange.

I did a small fact check with output from the SDC Platinum database Global New Issue and compared the Issue dates for a few US companies with the IPO dates given when I used the Compustat North America database. This limited check gave me the same IPO dates from both databases. This suggests to me that using the IPODATE variable from Compustat databases for doing research on IPO’s is a good method. More exhaustive tests may suggest otherwise. Depending on the scope and the  type of research you are doing, it may be a good idea to use the SDC Platinum database Global New Issue:

  • For large research purposes that covers many companies over a long time period (including data before 2002) SDC Platinum is the main source
  • For research covering a smaller number of companies and a smaller time frame (from 2002 to now) Compustat databases may be sufficient.

NB: Although SDC Platinum Global New Issue is a more authorative source for data it is by no means perfect. See an earlier post I did on data quality from this database.


Compustat Global index lookup problems

Compustat databases like Global and North America have great part databases that can be used to find out what the historical composition is for indexes of stock exchanges. Other databases (like CRSP and datastream) offer similar options but the Compustat option is a very easy to use one. The part databases that can be used for this, are called: Index Constituents. If you need the composition for the S&P 500 you go to the Compustat North America part database Index Constituents and use the Code lookup option at step 2 (through WRDS). For indexes of stock exchanges European coutries you could use a similar database in Compustat Global.

Unfortunately, for the past few weeks the Code Lookup option through WRDS has been redesigned and for the Index Constituents database this option no longer seems to work as it should: I can no longer search for index codes (= GVKEYX codes). I only get individual stocks when I try. The owners of Compustat are currently working to solve the problem (according to the helpdesk). The index codes, fortunately, can still be used to download historical composition data. Here you find a brief overview of some of these index codes which were provided by the helpdesk.


Identifying stocks in CRSP

Downloading data on equities from the CRSP Daily and Monthly stock databases is fairly easy if you have the option to use the database through the Wharton portal. Through the portal downloading is a seemingly easy 4-step process: 1) Select period, 2) Select stocks/equities, 3) Choose items to download, 4) Choose download format.

It can be tricky, however, to download specific stock data. To make sure that you have the right data you need to select at least the variables Share Code and Share Class (at step 3 in WRDS). Both variables can be found in the box Identifying information.

Share Class: SHRCLS describes the class of share and is generally blank. Any letter that identifies the class of stock (e.g., “A” for class A common) is contained in this field, left justified, and padded with three blank spaces.

Share Code: SHRCD is a two-digit code describing the type of shares traded. The first digit describes the type of security traded. The combination of the First Digit and the Second Digit identify the security type. A more detailed description is available here:

Other items that can be selected from the box Identifying Information can also be necessary to identify specific listings at specific exchanges: Exchange code & Primary exchange code. If you are using the CRSP security databases always be on the lookout for options where you can select identifying variables and study these carefully!. You may need them to filter the downloaded data afterwards in a spreadsheet result to clean up your dataset.


Working with lists of codes

Recently I had to work with a list of company names and codes. These codes looked as if they were Datastream codes and were suposed to indicate listings of shares. The company codes may indicate similar securities. When you have to work with a list of codes that you have not collected yourself it is always a good idea to check what type of codes they are. Depending on the type and nature (or version) of the security, some of these may need to be excluded from research (or not).

I uploaded the code list into Datastream and this way I could find out what type of securities these code match. To do this I used the following three Data Types:

  • TYPE
    Description: This datatype indicates the type of instrument requested. Countries for which available: ALL. The following list provides values returned for this datatype:
  • TRAC
    Description: The Security Type Code provides the type of share for each security as defined by the Thomson Reuters classification system.
  • TRAD
    Description: The Security Type Description provides a description of each security type (datatype TRAC) found in the Thomson Reuters Classification System.

Example screenshots:

1) Static Search in Data Stream:

2) Example search result:

In the search result you see that the codes indicate different types of equities, including: Fully Paid Ordinary Shares, Ordinary Shares, Preference Shares, and maybe more. The first two types are basically the same. Preference shares are slightly different because of voting rights. Many other databases also offer variables that can be downloaded with data (or lists) and that indicate the type of instrument or type equity data you are downloading. Always check the manual or help information to find out how a database works and how you can download what you need. For some Compustat databases I have posted what variables indicate the security type for these databases.


Companies & the Global Reporting Initiative

Over the past 10 years sustainability or corporate social responsibility have become increasingly important for companies. Many companies these days publish reports or include specific chapters of their annual reports/reviews on this subject. Companies also actively try to become part of sustainability indices or lists to show how responsibly they operate.

In the past I have posted several specific blog items on sustainability:

Since I posted the item on the GRI many companies worldwide have started using this methodology and GRI version 4 has now appeared. In the original post I referred to an excel file of approximately 3000 companies that shows an overview of the companies that have adopted the GRI methodology. This list now includes a staggering 5500 companies worldwide. The list also shows when companies started adopting the GRI method inclusing which level they used in what year. This list is updated 6 times a year and can now be found through the Sustainability Disclosure Database at the section About This Site: