This is how you build your own time series data using the power of Excel

In some cases, the data is not just there. But, when it is about lacking time series data, you should not throw the towel in before reading this post.

Let us start from a practical example. In this case we want to retrieve information about analysts following a particular company.

Currently active analysts is easy to find from both Eikon and Datastream, but time series data is often what one needs for the sake of academic purposes.

The challenge is that first it seems that there is no time series data for I/B/E/S (analyst information). This is “formula builder” with tab “time series” selected.

However, under “real time / fundamental” data one can see that the database does contain information about analyst start and end date.

At first, one could imagine that the problem is solved by simply selecting interesting data items. Unfortunately, in this case Excel will produce a single list of entries that is as such of little use.

Here is one way to solve this problem.

  1. Retrieve analyst codes including expired ones.

What you get is a list of analysts who analyze or have analyzed the selected company.

 

2. Go again to formula builder. Now you pick start date, go to “Parameters & Quick Functions” and select “Reference a cell”.

Naturally, you refer to the first cell with analyst code (in this case D5). Before clicking “insert”, go to “Layout”. In “Layout”-window remove title by dragging and change destination cell to be the next one from you reference cell (in this case reference is D5 and destination should be E5). After this click “ok” and “insert”.

In case you get number as output, then change type of cell to date.

3. Once you select the data containing cell, you notice that this has an excel-formula containing commands from Refinitiv.

You want to copy this formula to the entire column, but first you need to remove the dollar sign from the reference cell (marked here with red color). Modify this:

=TR(“NOKIA.HE”;”TR.AnalystCreateDate(ExclExpired=FALSE AnalystCode=#1)”;;E5;$D$5)

To this:

=TR(“NOKIA.HE”;”TR.AnalystCreateDate(ExclExpired=FALSE AnalystCode=#1)”;;E5;$D5)

With this change the reference cell will adjust appropriately.

4) As a final step, repeat the procedure for “Analyst end date”, “first name” and “last name” and ensure your error free work by giving a thought about whether your table makes sense.

In this case, it makes sense that Mikael Rautanen started analyzing Nokia in 2010 and has no end date, because he is still active.