Bruyneel: Using Excel Vlookup to match data from datasets

Mark Bruyneel has made a blog items solving the problem of matching result sets (two work sheets within excel) coming from different parts of a database of from two databases. It should save a lot of complex work and frustration (in a worst case one item at a time)

Quote:

Many people use the Excel Vlookup function to merge data from 1 or more sheets and have it presented in a single table for use later. This can be to create a single overview with all the data together from several downloads from 1 or more databases. Alternatively Vlookup can be used to match company identifiers from more than one database as a preparation for an event study. 

Blog Item

Youtube has got a special channel dedicated to Tips and tricks in excel: Excellsfun. The following movie shows
 Vlookup for beginners

Datastream Event Studies: calculating destination cells in Request Table

Datastream offers Request Table for event studies, which enables you to work with different Time Series per fund/equity (etc.) Just like in my former post can working with (very) large work sets be very tedious (not to mention complicated) when you are interested in event studies.

This is what you are supposed to do: (old method)

First of all: create a new work sheet (right mouse click)

Having done that, the next step is making your destionation cells (where to put your request table data, you don't want them on your menu)

This pops up:

Go to the new work sheet and select a destination cell > next screen:

Press OK





Back in the menu it looks like this.

Imagine having to repeat this 800 times?

Here comes the time saver: (new method)

Take a new Excel work sheet, and put in this formula:
 ="=Blad1!$cell"&rij() (Dutch);
="=Sheet1!$cell"&row() (English)

Drag down (with the + at the right bottom corner of the cell)

Copy the result into your Request Table

Voila, loads of time saved.

If you want to investigate more than one data type, you can change the formula.
For instance, like this: (5 rows)
="=Sheet1!$A"&5*ROW()

In the Dutch language version of Excel the function would be:
="=Blad1!$A"&5*RIJ()
(the data destination shifts 5 rows)

*) many thanks to Aart Bijkerk, who pointed this out to me.

Datastream Event studies: Calculating dates for Request Table

I have always found it complicated and tedious to do an event study in Datastream. Not only does the request table not understand dates coming from Excel, working with large work sets makes the request table destination dsimply annoying. Needless to say I tried to avoid it.

Calculating dates in Excel
For instance event dates using formulas =(cell number+X) and =(cell number-X), where X is the number of days that has be added or subtracted. The next step would've been copying the dates into a new work sheet and paste it as a number, next convert the numbers into dates save the sheet as a .TXT file, close and open the .TXT files as text. Those dates would finally be understood by the Request table menu.

Calculated dates around the event date
Formula =(cell number+30) or =(cell number-30) > =(A1+30) and =(A1-30)
Use the + in the right bottom of the corner to drag the calculated date

Examples of the old method

Using calculated dates (blog Mark Bruyneel)
Specific event studies (blog Mark Bruyneel)
On the VU Blackboard an instructive video can be found

Since my work demands explaining the process to our students, I was very happy to learn from a very bright student - Aart Bijkerk - that things can be made considerably easier.
Using the Excel formula
=Tekst(cell number;"dd-mm-jjjj") (Dutch)
or =Text(cell number;"dd-mm-yyyy") (English)
the dates are ready made for the request table and can be copied into Request table without copying and saving as TXT.

Important:
!! Copy the dates and PASTE (right click) paste special (values) into the Request Table menu !!

Data Destination is next

See also: Datastream Event Studies: calculating destination cells in Request Table (link)

Datastream: request table using Excel

In Datastream's Time Series menu it's only possible to generate one single Time Series.
Datastream features ' Request Table ' , to circumvent that and create multiple Time Series.
This blog explains with a simple example what to do.

Example workset:

(copy, CTRL-C)

Time Series menu:


After starting Request Table, paste the workset into the proper fields. It's is also possible to press the Navigator button and create a table from scratch.



Next: adjust all fields to the required preferences (like the time series)
Click the buttons above the columns to select.

Update Yes/NO
Request Type: Static (S), Time series (TS), Time series list (TSL), Company accounts (CAF)
Format: (output) things like headers, row titles etc.


Datatypes, functions, CA (for CAF)
Now, here is the trick: for each item it is possible to create its own time series
(included start date and end date)
Frequency: Daily, weekly, monthy, quarterly and yearly
Example: (note the different Time series)


Next it is time to set the Data Destination.
This demands some rather complicated actions.
Just follow the instructions.

First: creating a new Work sheet (the destination of your search)
Right Mouse click > insert new work sheet
(might differ per Excel version)


Now, press this button:


Another screen pops up:


Click the new work sheet TAB and click the first top cell

Press OK

Repeat untill all funds are done:


Finally, press this button:


All data is transported to the new work sheet.
Result: