Using Excel Pivot Tables to Analyse ICIJ Offshore Leaks Database

ICIJ’s online search tool allows the user to explore the Offshore Leaks database in detail, by following connections among individual nodes: offshore financial entities, their clients and officers, and their respective addresses. This information is invaluable for investigative journalists who pursue stories of individuals and their financial arrangements.

Let’s look at another approach to analysing the information contained in the database: a high-level picture that would tell us which countries are represented in the database and how strong their connections are. We will apply some very simple tools of network analysis, but first we need to modify our database to incorporate country data, which, for some reason, is provided in a separate file.

We’ve imported all four files of the database into an Excel document as four spreadsheets: Nodes, Edges, Countries and Node_countries. The last one assigns country information to nodes. We need to incorporate this information in the Nodes and Edges sheets, using VLOOKUP function, as illustrated:

Note that we’re accessing the country information (the two-letter country code) from the second column of the Node_countries spreadsheet:

Pivot Tables are a powerful tool that allow us to slice the data in a variety of ways. A Pivot Table takes data from a spreadsheet and summarises them based on the criteria we provide.

We’ll use Pivot Tables to look at the countries represented in the database. This is the setup:

The resulting table shows the number of nodes in our network by type (entity, address or individual) for each country. This setup allows us to filter the data by jurisdiction.

Just a few simple manipulations of data in the spreadsheet give us this first useful picture telling us how many individuals and entities we have in the database for each country…

… and which jurisdiction the entities are subject to:

From here, we’re well positioned to start analysing relationships among countries and jurisdictions.

Originally published on January 7, 2014

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s