Activating Datastream Add-in in Excel

Sometimes the Datastream add-in gets de-activated.
Result is that excel doesn't show a Datastream TAB.


Here is how to fix it.

Start up Excel
Click File / Bestand

Click add-ins / invoegtoepassingen

Is Advance Office 2007 active?

No?

Go to box [Excell add-ins] and click GO / Start

Next pop up screen :

1. is Advance Office 2007 mentioned but not boxed? 

Click the box

Click OK

2. Is it not there?

Click Browse / Bladeren button

Wind explorer opens

Go to My computer > double click C:\

Go to Program Files / programmas

Double click

Look up folder Datastream > double click 

Double click sub folder Datastream Advance 

Select bottom most file (636K)

Click OK

The Datastream TAB is restored

Same path in pictures

 

Excel tips for data

Here are some common Excel functions you might run into when using datasets from our databases.


Autofilter
Often overlooked, but very useful
Go to the Data (gegevens) TAB en press this icon: 

Select the entire column in one Go.
Type in CTRL-Shift-arrow down ↓ simultaneously

Straight up to the first cell
(CTRL-Home)
Straight down top the last cell
(CTRL-end)


Solving the ##### when columns are too narrow.
(applies to all columns in one single go)
Select the entire work sheet with CTRL-A or by clicking the arrow symbol top left
Double click column border to the right of the specific column

Calculating difference between two dates (handy in event studies> time window)
Take two ultimate dates
Dutch  (language setting excel)
=datumverschil(A1;A2;"y") Years

=datumverschil(A1;A2;"m") months
=datumverschil(A1;A2;"d") days
English (language setting excel)
=datedif(A1;A2;"y") years
=datedif(A1;A2;"m") months
=datedif(A1;A2;"d") days

Subtracting or adding days to a date:
=(A1-30) or =(A1+30)

Changing data into text :
=text(A1;"dd-mm-jj" )
Dutch Excel
=tekst(A1;"dd-mm-jj" )

Adding or retrieving add-ins for Excel:
Step by step. Click the Excel symbol

Select Excel options (bottom of the pop-up menu)
Select invoegtoepassingen (add-ins)
When  you can't find the needed ad-in select add-ins en pres start (go)

A list pops up:

If the add-in isn't there, choose the Browse button (bladeren)

An explorer windows opens where the desired add-in should be present.
If not, things get more complicated.

Combining cells


Concatenate cells (in Dutch samenvoegen)
=CONCATENATE(A1," ", B1)

Dutch Excel
TEKST.SAMENVOEGEN
=TEKST.SAMENVOEGEN(A1,"",B1)

Same effect, other formula:

Given a table with two data columns
=A1&B1
e.g.

The formula is visible behind the ƒx symbol and has to be filled in in Cell C1

With a space between the data:
=A1&" "&B1&"

NOTE: WHEN THIS DOESN'T WORK BECAUSE EXCEL CONFLICTS, TRY DOWNLOADING YOUR FILE IN EXCEL AND REPAIR THE CODES BY ADDING LOST ZEROS, IF THAT HAPPENS!!!!  
See below

Completing company identifiers (CUSIP)
What if you got company dentifiers, and they lost the zeros preceeding the code?
That happens in some computers (guess it's a settings thing, since it doesn't always happen) when saving as Excel, and open in Excel... Excel considers those zeros worthless.


Example.
GVKey codes are used in the Wharton (WRDS) databases to identify companies. Thoese number consist of 6 numbers. Often the preceding 00 , to make of the six numbers of companies with smaller GVkeys, are removed.
Same happens with e.g. CUSIPs.

Checking how many numbers you need for CUSIP is to count the longest CUSIP codes. In this case 9.
Since CUSIP consist of
 company    issue    check digit
123456         78            9
we can conclude that the most right digit in the codes are the check digits.
In other words, we need to add zeros.
In order to get back 9 digit CUSIP codes we can add the missing zeros with formula

=REPT("0";9-LEN(cell nr))&cell nr

GVKEY:
=REPT("0";6-LEN(cell nr))&cell nr
LEN stands for length

The following image shows codes without and with added zeros, plus the formula

IMPORTANT SETTINGS for the formula
Go to control panel > set language to English (USA) and region United States
Under TAB language > additional settings
List separator ;
or else the formula won't work
Additional additional settings (for Europeans)
Decimal symbol ,
Digit group symbol (thousands and such) .

Close with Apply and OK


RUNNING INTO PROBLEMS?

That happens to some people, depending on the computer they are using.
I get it . . .
If you get this in view, you'll get a hard time to use concatenate.
To be precise: you see a little green triangle in the top left of your sells > there is a hidden apostrophe... and it needs to be removed . . .

How to solve this:

1. make a selection of all codes
2. Copy to Notepad
3. Select all
4. Paste as number
5. It should be OK now.
6. This also (partially) happen to your CUSIP codes.
7. Repeat the same action
8. Note: I ran into another problem > the preceding zeros were removed in all cases where the green triangle happened
9. Use the REPT formula to repair this


Removing or adding the ninth digit?

As for removing the ninth digit from CUSIP:

Open your Tab delimited TXT file (using the wizard) in Excel

Create a new column ; select first cell in that column 

Click formula button in the command rule ƒx

Type in LEFT (enter) and select from options

A pop up appears.

Click in the first cell of the CUSIP column

Next rule: type in number 8 saying you only want to keep the first 8 digits

Drag down result of double click the + in right lower corner of the cell

Note: I am certain there are more catches lurking to meddle your results.

I guess it is recommended to download as many identifiers and information on the issues as you can find.

Don't forget: the number of available stock exchanges in CRSP is limited (NASDAQ, NYSE, AMEX). Compustat might cover more. Download stock exchange code as well.

Second: when you want to download from CRSP and venture to data from Fundamentals Annual, keep in  mind that both databases cover different entities.

Difference between two values (i.e. simplified return)
=(B2-B1)

Return (in finance) link
=(B2-B1)/B1

Converting dates.
When you got a row of dates that aren't recognised as dates.
For instance Compustat output format dates.

Make values from dates.

Best is to choose 07/25/1984 as it converts easily with the VALUE function (WAARDE).
Working with Dutch Excel (images)

The used table is an out file from Compustat with the date notation 25/07/1984
Wizard opening Text files in Excel: here

Once the value (waarde) function is selected, the next screen appears:

Select the first cell that needs to be converted.
The result looks like this: drag the cell downwards in order to apply all cells

The values have to be converted into text with formula
Convert values into text
=text(A1;"dd-mm-jj" )
Dutch Excel
=tekst(A1;"dd-mm-jj" )

Once the dates have been converted they can be used in other applications without problem.

Calculating return with Excel

In one of my posts, a couple of years ago, I told something about Return, expected return and abnormal return.: This post
In this post I am going to focus on caes where return can't be downloaed, but (stock) prices can.
In Excel you can calculate the return yourself.

Today's stock prices minus yesterday's stock price, divided by yesterday's stock price

(Px-Px-1)/ Px-1 = return or

(Px-Px-1)  = return
   Px-1

Working from a table with stock, this is what you do:


Price day in i.e. cell B8 minis price in cell B7 divided by the price in cell B7
See pic

< enter>

Now double click the + in the right bottom of the screen
Result:

Now what if we want to compare this to the returns of AEX?
After downloading the Index prices simply copy the column with the Ahold returns next to the AEX returns. It will keep the formula but calculates the AEX returns. Neat.

Databases with returns data

Datastream (see examples above)

Compustat North America: Security daily > TRFD > Total return factor Daily (TRFD is monthly in the monthly securieties datbase)
Not in the other N. Am. databases.

CRSP - Daily Extract with Time Window does provide event studies with return data. (PERMNO codes only, which have to be drawn from other datasets through CRSP/Compustat merged, making it possible to work with ticker, Cusip, CIK and sector indicators like SIC and NAICS)

Compustat Global does not not, so method above mentioned might come in handy. 
Download the price data in the security daily database

Note: I am going to explore this subject further.

Also see Blog item Return, expected return and abnormal return

SDC output in TEXT as opposed to XLS (excel format)

There is always the chance to lose data when saving files in XLS format, as with opening the file in a spreadsheet program like Excel, it tends to remove all 0s (zeros) preceding any given number, like for instance CUSIPS.
In the case of company identifiers this could render those data useless.
The other SDC output option is txt, but it is not tabular.

How to solve this?

In most cases the following method works, but I have noticed that data starting with two zeros will lose them. In the pic above that's ABRM, the CUSIP lost its two preceding zeros.
That's a pity.

Method in text:
Save the file and open in Excel.
The wizard will guide you.
Instead of choosing tabular your choose for fixed width.
In my example it worked, as the next pics will show you.


Search strategy > custom report

Execute

Save as












Save as text only

Close SDC

Open Excel
Open file (TXT) (make certain that you can open a text file)

Choose fixed width

Radio button text (tesk) and finish (voltooien)

Result

In most cases this works, but I have noticed that data starting with two zeros will lose them. In the pic above that's ABRM, the CUSIP lost its two preceding zeros.
That's a pity.

Of course the method still works for other data.

How to transpose a table in Excel

I didn't know. But this is how it works:

Take any table

Press CTRL* to select the entire table

Now copy CTRL-C

And paste special (plakken speciaal) . Make sure to click box Transpose (transponeren)

Press OK

Preventing embedding (refresh) your Datastream output

It can be rightout annoying when the table with downloaded data is embedded.
Not only it's hard to work the output table on other computers (it wants to refresh), but sometimes it can't be opened altogether.
Here is how to prevent it.

 Open Excel and go to TAB Datastream

Press "Options"

Choose Advance for office





Deselect the embed option.

Press OK.

Now your output shouldn't be embedded anymore.

Volatility and Volatility Indices

The financial markets are always interested in movements and trends.
One way of keeping track and predicting is measuring volatility of equities or or indices, currencies etc.

Volatility can also be calculated through standard deviation or variance between the returns (Ri) 
The highter the volatility, the more it is likely for the values to fluctuate (wildly).
Stable equities, on the other hand, show a low volatility.

Volatility "lives in the past" as it measures movements in the past.
Oppositie are options, which predict movements, in other words expected volatility.
The higher the expected volatility, the more an equity will cost.

Example: in the period preceding publication of (bi)annuals the expected volatilty will be higher (and thus more expensive).

Volatility Index
Another way of decribing volatilty is that it measures risk (fear or anxiety), be it equities, indices or markets.
A volatilty index aims at measures risk of markets.
Chicago CBOE (Chicago Board Options Exchange) deals with volatilties of S&P 500, the VIX.
VIX is quoted in percentage points and that expresses the expected movement of a 30 day period of time, which then is annualized.

Another method of measuring volatilty is using the BETA. This sets a securitie's returns (Ri) against the return of a benchmark (usually the S&P50 is used)

Datastream:
The Beta can be drawn from the Static Search Menu through ready made formulas.

The button leads to the expression picker.
Type 'volatility' and all ready made expressions will show.

Result

Wharton WRDS
Wharton Compustat contains a seperate Database called  CBOE Indexes.
The CBOE (Chicago Board Options Exchange) is a key measure of market expectations of near-term volatility conveyed by S&P 500 stock index option prices.
The New VIX is based on S&P 500 index option prices and incorporates information from the volatility "skew" by using a wider range of strike prices rather than just at-the-money series. 

First of all set your time scope. (range)
Swecond: these are the possible CBOE:

Note the possible data. It's the index as a whole, and only defaults.
Last step: out. recommended is storing as TABulated.TXT

Opening a TAB-delimited file in Excel. A wizard will guide you there. 
Take care to select it concerns a DOS (PC 8) file. The delimitation will be recognized by the wizard. 
Last, select all columns by scrolling to the right pressing SHIFT-CLICK and make certain to press radio button text.
Opening your compustat save < click  link

Units: percentage points









 Philadelphia  Stock Exchange PHLX: (money market)
Two choices: currency options and implied volatility