EDSC tips & tricks

Many students come to the EDSC asking where they can find specific financial ratios, such as Tobin’s Q, on Eikon Datastream. While not hard to find for those familiar with the Eikon Excel add-on, this might not be immediately intuitive for new users.

When a particular variable is not available in the list of Datatypes, you can use the ‘Expressions’ to find alternative variables, such as financial ratios, via the ‘Expression Picker’ or create your own using the ‘Expression Builder’.

There are two ways to access the ‘Expressions’ button:

1. From the top navigation bar on Excel, under the 'Refinitiv Eikon Datastream' ribbon.
This is a useful way to retrieve the code and view the formula. 

2. Or by using the ‘Expression Picker’ button on the Time Series Request form.
This is useful when opting to apply the code to a list of companies within a specific time/frequency.

Once you click on the ‘Expressions’ button, in order to find Tobin’s Q, click on the plus sign next to ‘Datastream’, ‘Ratios’ and ‘Equity Ratios’ on the left-hand side column of the ‘Expression Picker’ tab. 

Datastream provides two codes and their respective formulas to calculate Tobin’s Q for individual companies.

• Version 2 uses the expression code 170E and uses the following formula to calculate Tobin's Q:  X(DWEV)/X(DWTA)*1.000.
• The expression code 168E which uses the following formula to calculate Tobin's Q:   
  (X(MVC)*1000.000+PAD#(X(WC03451)~PCUR,C)+PAD#(X(WC03251)~PCUR,C)+PAD#(X(WC03051)~PCUR,C))/PAD#(X(WC02999)~PCUR,C)

The formulas and their descriptions can also be edited using the ‘Expression Builder’ to meet your data needs:

Once you are finished downloading you might end up with a lot of sheets containing formulas. If you want to replace your formulas by values in all sheets at once you can use this macro.

Make sure you don't have any filters enabled in your dataset.

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
End Sub

Sub All_Cells_In_All_WorkSheets_2()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
End Sub

Sometimes when you try to export to Excel nothing happens. Try the following solution: hit "Alt-D", go to "Legacy Terminal Defaults",  then "Setup" and edit "Download Setup". Choose your Desktop or Download folder.

Looking for the event study tool by WRDS? It's no longer available under "Subscription". Probably because some parts require access to data that's not part of our subscription.

But the "U.S. Daily Event Study: Upload your own events" option is still available. Visit https://wrds-www-wharton-upenn-edu.eur.idm.oclc.org/pages/get-data/event-study-wrds/us-daily-event-study-Upload-your-own-events/

Thirteen new ESG Scores measures have been added to Datastream.

Thomson Reuters ESG Scores are an enhancement and replacement for the existing equally weighted ASSET4 ratings. They reflect Thomson Reuters’ new strategic ESG framework. Thomson Reuters ESG Scores are calculated and available for all companies and historical fiscal periods in the ESG Global Coverage, consisting of 6,000+ public companies globally.

Below are the corresponding Datastream datatypes, titles and definitions:

Datasets in WRDS are not updated in real time. Most databases have a 1 year delay. Check the “Data Update Calendar” in WRDS to see when datasets get updated 

This calendar provides up-to-date information on the status, coverage, and frequency of updates for all datasets available on WRDS.: https://wrds-web.wharton.upenn.edu/wrds/support/update_calendar.cfm

Sometimes you see negative stock prices in CRSP. This means that there was no closing price available for that period. Instead, the bid/ask average was used. To distinguish the bid/ask averages from actual closing prices, CRSP puts a leading dash in front of the price when the bid/ask average was used. If neither price nor bid/ask average is available, Price or Bid/Ask Average is set to zero.

In this example we'll show you some of the new functions of the WRDS conditional statement builder in Compustat. We’ll use an example that shows how to select a SIC range.

First, in step 2 select “Search the entire database”

If you try to activate the conditional statement builder and you haven’t selected any variables yet, you”ll get the following error message.

So first select variables that you want to use in the conditional statement builder. In this example we select “SIC — Standard Industry Classification Code” and “CONML — Company Legal Name”

Now you can create a conditional statement. Add a rule and specify that  the company name starts with an A, add a group and add a rule where you specify that the sic code starts with a 2. Add another rule to this group and specify that the sic code starts with a 3. Within this group select “OR”. The query preview should look like this:

The end result of your query is a list of companies with sic >= 2000 and sic <=3999 (sic starts with 2 or 3) and the company name starts with an A.

When you download stock data from Datastream the closing price (datatype P) is repeated when the exchange was closed due to a holiday, like Christmas. If you don't want this 'padded data', you can use the datatype P#S to prevent the data is repeated

A comprehensive set of new fundamental datatypes is now available for Datastream Global Equity indices and equities. The data is sourced from Worldscope and based on a trailing twelve month period if applicable. The data represents the sum of the relevant item reported in the last twelve months. From the fiscal period 2002, the items are populated from the quarterly, semi-annual and trimester time series based on the availability of the underlying data. The new 4 character datatypes can be used with the Datastream Global Equity indices and also all companies covered by Worldscope on Datastream.

With these data types you can, for example, request sales for an entire industry in Datastream.

For example for the “Healthcare Industry in The Netherlands”. You can enter via “Series / Lists” HLTHCNL, for Europe: HLTHCER or for the World: HLTHCWD. With datatype you enter DWSL for the Sales.

You can for example use these Industry Codes to create an Index:

It is also possible to adjust the above industry codes so that you can request the underlying constituents in Datastream. You can do that like this:

Example for the Netherlands: LHLTHCNL
L= List
HLTHC= Industry code Healthcare
NL= Netherlands

Example for the Europe: G#LHLTHCER
G#L= Global List
HLTHC= Industry code Healthcare
ER= Europe

Example for the World: G#LHLTHCWD
G#L= Global List
HLTHC= Industry code Healthcare
WD= World