Business data induction 2022–04–04

Analysis of one BoardEx table, starting to cross reference

This week, we reviewed the BoardEx homework challenge:

Find the UK’s highest paid CEO in the last five years

The question may not be well defined yet. You may have some scope to adjust it depending on what the database offers. We are using a fictional example which will help colleagues to help students to use BoardEx. We will build up, looking at one table in BoardEx with the documentation, then learn how to cross reference one row of the output in a second request in a different BoardEx table. Later, we will link tables together, multiple rows together.

Starting point: Identify all the relevant tables which may contain the fields of interest. Remember to then check the ‘Variable Descriptions’ tabs.

Identify relevant tables

BoardEx is split into four regions. Today, just look at United Kingdom. (How are regions determined? Look at WRDS BoardEx overview docs.)

BoardEx — Individual Profile Employment

  • Who (DirectorID, DirectorName, RoleName)
  • Where (CompanyID, CompanyName, ISIN, Sector, HOCountryName)
  • When (DateStartRole, DateEndRole)

BoardEx — Individual Profile Details

  • Who (DirectorID, DirectorName)
  • More personal details (gender, date of birth, nationality)

Consider the concept of looking up all companies together rather than selecting a single company or list of companies. There are pros and cons to searching all companies together; it may be easier to not have to search for specific companies however you will then have a larger/slower query which could take too long to run or take up too much wasted disc space.

Compensation Analysis: BoardEx — Annual Remuneration

  • Who (DirectorID, DirectorName, RoleName)
  • Where (BoardID, BoardName)
  • Choice of amounts (Currency, Salary, Bonus, TotalCompensation, TotalDirectComp)
  • Rowtype (average, total or individual figures) are they a board member?

You don’t get all the company details here (no ISIN or HOCountryName), would have to link with something else. Also, CompanyID is equivalent to DirectorID.

Perform first query

Try to query first table, UK Annual remuneration table

  1. Select dates: January 2017 to December 2021.
  2. For the choice of companies, select entire database.
  3. Choose all query fields above with Currency and TotalDirectComp.
  4. Download in Excel format.

Run the query, get 180,000+ rows, 6MB Excel document.

Start analysis in Excel

Will need to do some analysis in Excel to answer the question.

Use the Filter feature in Excel:

  • Remove rows with blank compensation.
  • Include those roles which contain ‘CEO’ in the role name.
  • Sort by descending compensation.
  • Look at the director with the highest compensation, in particular, the identifier codes for the director and the board they sit on (company).

Copy the BoardID for this company. How can you find out more about this company? Look them up in another table.

Cross reference one row to a second table

Company Profile: BoardEx — Company Profile Details

  • Where (BoardID, BoardName)
  • More company details (location, sector, ISIN, index, most recent revenue, number of employees.

We can look up these additional data fields by searching for one company using its BoardID rather than searching for the company by name. This will ensure we have the right company. (If we were looking up director information, we have to use a DirectorID since there is no way to search for directors by name.)

Perform second query

Try to query this table with one company:

  1. Select ‘Company ID’ and paste in the one company code.
  2. Choose all query variables as interested, eg Revenue, MarketCap, ISIN.
  3. Download in Excel format.

Run the query, get 1 row output. Have a look at the Excel file for details.

Challenge for next time

  1. Repeat this week’s steps to download the last five years of UK director compensation.
  2. Use the filters to select directors of interest, copy their DirectorID fields.
  3. Perform a second query in an individual details table, tell me something interesting using name, gender, age or nationality. You can copy-paste the director ID fields, either one-by-one or (more advanced) make a list in a plain text file. For example, how old are the highest earners? How far down the list do you have to go to find someone under 30, or female, or not a UK national?

There are questions which we cannot answer without joining the two tables together, such as, what proportion of the top 500 earners are female, or how many more of them are men called John than women? We will look at that next time.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store