Datastream instructor training 20190611

Panel data and request tables

This is the first post in a series. It follows the training of new members of the Business Data Service in The University of Manchester Library. They are learning Datastream through fairly informal “train the trainer” events, weekly challenges and practice time. These posts will not be polished or go through our usual Quality Assurance standards (some is in the past tense, some not; the content is important).

Trainers had all taken the standard Datastream workshop some weeks prior.

Working entirely in Excel version.

Recap some of the introductory workshop

Recap:

• Launching Datastream Advance for Office (AFO) Excel add-in.
• Concepts of Static and Time Series Requests.
• Concepts of Series and Datatypes.

Asked learners to download Tesco number of employees for 2015 to 2017.

• Recap/teach finding datatypes (Employees and similar (other sources)).
• What period is sensible? What period is possible? Look at documentation to find ‘Annual’ buried in the Notes section of Employees.
• Is this total employee numbers or full time equivalent (FTE)? How does this compare with other resources? (See our old blog post on the topic.)

Combining requests and panel data

Asked learners to download daily share price for the last month for all constituents in the FTSE 100.

• Recap/teach difference between Equities, Equity Indices and Constituent Lists.
• Recap/teach the TS for Items in List tick box, how it compares to Static Requests.
• Searching with and without a space has different results, eg FTSE100 and FTSE 100.
• Recap/teach concept of price (Price adjusted default, aka closing price).
• Start and end date can be relative, e.g. -1M for one month back, or absolute, e.g. 31/12/2017 DD/MM/YYYY (or YYYY for annual data).
• The data will download to the selected cell and cells to the right/below. Data will be overwritten if it exists. So select your starting cell before starting a request.

There are more columns than rows. Asked the learners to repeat but tick Transpose option to download the other way around. What are the benefits to each approach?

• Too many columns may not fit due to file limitations.
• Columns can be sorted, rows cannot.
• Students usually know what format they need.

Asked learners to download price and market value for the last month for all constituents in the FTSE 100. Still transpose the data.

• Can select multiple datatypes. Tick the boxes then click the blue Use link.
• Recap/teach concept of market value, aka market capitalisation, market cap (MV). Discuss relationship between share price, market value and number of shares (MV = P * NOSH ). Discuss the use of the concept of market value.
• What structure/layout do you get? The companies are grouped, i.e. the rows go Company1 P, Company1 MV, Company2 P, Company2 MV. The columns are the dates.

How would we get a different layout, e.g. Company1 P, Company2 P, Company1 MV, Company2 MV? In this case, we break the enquiry into two parts. We run the enquiry for Price, then we repeat the enquiry for Market Value starting in the first cell below but this time untick the box “Column Titles”.

• Why do we not want column titles after the first call? They would be repeated each time, breaking up the data.

This is called panel data. We are representing more than two dimensions (rows, columns, and sub-rows or sub-columns).

Students usually know what layout they need. You may need to visualise it with them. It can be helpful to write out a table on paper, such as:

      | Y1 | Y2
------|----|----
C1 V1 | a  | b
C1 V2 | c  | d
C2 V1 | e  | f
C2 V2 | g  | h

…where Y is year, C is company, V is variable/datatype and lower case letters are the values.

Learners were asked to close the document and start again, requesting price and market value for the last month for all constituents of the French CAC 40 index.

Combining more requests with Request Tables

It is possible to build up your data request in this way, but it soon becomes repetitive with risk of making errors. A Request Table will help you to construct a specific list of requests with a specific, customisable format.

Learners were asked to click the menu bar to open a new request table.

• A request table is a macro-enabled Excel workbook, you must save it as .xlsm format.
• In the body of the workbook is a table. Each row represents all the steps of a time series or static request. Each heading has buttons to fill in the selected cell.
• There is a button at the top-left Process Table. Clicking this button executes all the row requests that have Y in the first column.
• The remaining columns determine the type of request (S for static, TS for time series, TSL for time series for a list), headings, series, datatype, dates and frequency. (Ignore the rest for now).

Learner were asked to repeat the FTSE 100 enquiry from before.

• What happens? The data destination field is blank by default, it creates a new sheet for each line of the request.
• You may want to download the data into the same sheet. We can do this by modifying the data destination field.
• We built up to five variables (Price, Market Value, Earning Per Share, Price Index, …)

Basic principles of combining requests into a single sheet:

• Start small. Do the first, second and third request as normal, ie into separate sheets.
• Notice the difference in the first sheet (column titles present, no column titles in subsequent ones).
• If you transpose the first, also transpose all the others. (Same with other headings.)
• The data destination field is a standard Excel reference to sheet name and (locked) cell, e.g. Sheet1!$A$1 (sheet name then exclamation mark then cell reference with dollar signs to make sure the reference does not change if you copy/paste/move it).
• You can predict the data destination next row number, assuming your requests are all the same number of rows (except the first one). So, if you have manually put in the values for the second and third row, select them both then drag down with the black square icon at the bottom-right (Fill Down). For example, if the second destination is Sheet1!$A$11 and the third is Sheet1!$A$21, fill this pair of values down and you will automatically get rows 31, 41, 51, etc.

Using lists

The previous workshop covered the creation of custom lists of series, specifically UK banks. The list you create is saved to the PC locally. You can use the list picker icon to choose one of these local lists, it will show the filename (ending .LLT).

• Use the TS request type if you have a comma separated list of series (e.g. TSCO, SBRY, MORW).
• Use the TSL request type if you have a custom list or a predetermined Constituent List.

You can bring in company lists from other databases if they use the ISIN code, or CUSIP if you convert them. More on this next time.

Challenge 1

Download the ‘Environmental Score’ for the largest emerging markets BRICS countries.

• ‘Environmental Score’ is a percentage value to say how well a company has performed regarding its environmental responsibilities. (High is good.) It is part of the ASSET4 collection (Environmental, Social and Governance (ESG) data).
• BRICS is an acronym for Brazil, Russia, India, China, South Africa.
• Largest can be measured using Market Value (MV) time series request. Large can be measured as greater than or equal to $10 billion, or choose the largest 5.
• Choose how you want to lay out the data, and describe your working.