Red Flags Analysis

Background

This analysis of UK PSC data that has been mapped to BODS format shows how we can answer common questions about 'red flags' in BODS datasets. These red flags are not necessarily an indication of anything wrong but may prompt further investigation.

This analysis has not been conducted in order to identify red flags in the UK data itself, but to show an example of how such analysis could be done.

Reading in data

#import qbods library from github !wget 'https://raw.githubusercontent.com/openownership/bodsanalysis/main/qbods.py' -O qbods.py #install required libraries !pip3 install -r requirements.txt #importing libraries import qbods import os import pandas as pd import numpy as np import gc import networkx as nx #change directory to /tmp (avoid disk space issues) os.chdir('/tmp') #importing bods data from OO website qbods.getBodsData('https://s3.eu-west-1.amazonaws.com/oo-bodsdata/data/UK_PSC/csv.zip')

How many entities have foundingDate and dissolutionDate where the difference between these dates is less than a year?

Assumptions

Companies with the same name, founding date and dissolution date are likely to be unique.

Company names are consistent in the data set (other than variations in case).

note - very limited data from pre-2016. I suspect that this is due to records of companies dissolved before then not being routinely digitised.

#read in entity statements df = pd.read_csv('csv/entity_statement.csv',usecols=['foundingDate','dissolutionDate','name']) #drop any companies not yet dissolved df = df.dropna(subset=['dissolutionDate']) #make company names lowercase df['name'] = df['name'].str.lower() #group to remove duplicates df = df.drop_duplicates() #calculate years between founding and dissolution df['foundingDate'] = pd.to_datetime(df['foundingDate'],yearfirst=True) df['dissolutionDate'] = pd.to_datetime(df['dissolutionDate'],yearfirst=True) df['yearsOperating'] = (df['dissolutionDate']-df['foundingDate'])/np.timedelta64(365, 'D') #filter to companies operating for less than one year df = df[df['yearsOperating']<1] df['yearFounded'] = df['foundingDate'].dt.to_period('Y') #filter to 2017 onwards df=df[df['yearFounded']>='2017'] #filter to pre-2022 df=df[df['yearFounded']<'2022'] #return answer print('Number of companies (founded from 2017 onwards) dissolved within a year of founding - '+str(len(df.index))) df = df.groupby(by=['yearFounded'],as_index=False).size()

How many entities have ownership chains which have involved natural persons or entities in countries on the EU list of non-cooperative tax jurisdictions?

Assumptions

A person is 'in' a country if their 'registered' address is in that country (UK PSC data doesn't record other address types).

An entity is 'in' a country if their 'registered' address is in that country (UK PSC data doesn't record other address types) or if they were incorporated in that country.

This includes companies that have been dissolved / interests that have ended

Company numbers have been used in place of entity statement IDs as statement IDs are not unique to each company

Technical note - UK PSC data only has one level of reporting - there are no 'component' statements and there's no overarching OOC that lists all the component statements included. With properly formatted BODS data it would be easy to use that overarching OOC statement to get the countries of every component entity / person.

Here we have used networkx to construct a graph of the ownership networks. This required more time and expertise than would have been needed for a complete set of BODS data.

#list of country codes countrylist = ['AS','AI','aS','VG','CR','FJ','GU','MH','PW','PA','RU','WS','TT','TC','VI','VU'] #read in entity statements entity_st = pd.read_csv('csv/entity_statement.csv', usecols=['_link','statementID','incorporatedInJurisdiction_code']) #filter to incorporated in country list entity_bl = entity_st[entity_st['incorporatedInJurisdiction_code'].isin(countrylist)] entity_bl = entity_bl.drop(['_link','incorporatedInJurisdiction_code'],axis=1) entity_bl = entity_bl['statementID'].values.tolist() entity_st =entity_st.drop(['incorporatedInJurisdiction_code'],axis=1)

#get company numbers for UK companies df_chunk = pd.read_csv('csv/entity_identifiers.csv',usecols=['_link_entity_statement','id','scheme'],chunksize=1000000) chunk_list = [] for chunk in df_chunk: #filter to company house number only chunk = chunk[(chunk['scheme']=='GB-COH') | (chunk['id'].str.startswith('https://opencorporates.com/companies/gb/',na=False))] chunk = chunk.drop(['scheme'],axis=1) chunk['id'] = chunk['id'].str[-8:] chunk = chunk.drop_duplicates() chunk_list.append(chunk) #free up memory del chunk gc.collect()

#join chunks iden = pd.concat(chunk_list) iden = iden.drop_duplicates() #merge with entity statements iden = iden.merge(entity_st,left_on=['_link_entity_statement'],right_on=['_link']) iden = iden.drop(['_link','_link_entity_statement'],axis=1) #free up memory del entity_st gc.collect()

# read in OOC statements ooc = pd.read_csv('csv/ooc_statement.csv',usecols=['subject_describedByEntityStatement','interestedParty_describedByEntityStatement']) # filter to company owning company ooc = ooc[ooc['interestedParty_describedByEntityStatement']!=''] #merge ooc and ent statements to get link number for subject ooc = ooc.merge(iden, how='left', left_on=['subject_describedByEntityStatement'],right_on=['statementID']) #merge ooc and ent statements to get link number for owner ooc['interestedParty_describedByEntityStatement'] = ooc['interestedParty_describedByEntityStatement'].astype('uint64') ooc = ooc.merge(iden, how='left', left_on=['interestedParty_describedByEntityStatement'], right_on=['statementID'],suffixes = ['_subject','_interestedparty']) ooc = ooc.drop(['statementID_interestedparty','statementID_subject'],axis=1)

#fill missing IDs with statement ID ooc['id_interestedparty'] = ooc['id_interestedparty'].fillna(ooc['interestedParty_describedByEntityStatement']) #tidy columns ooc = ooc.drop(['subject_describedByEntityStatement','interestedParty_describedByEntityStatement'],axis=1) #drop duplicates ooc = ooc.drop_duplicates() #create networkx graph G = nx.from_pandas_edgelist(ooc, source='id_subject',target='id_interestedparty',create_using=nx.DiGraph) #free up memory del ooc gc.collect()

#read in person statements person_st = pd.read_csv('csv/person_statement.csv', usecols=['_link','statementID']) #read in person addresses person_ad = pd.read_csv('csv/person_addresses.csv', usecols=['_link_person_statement','country']) #filter to country list only person_ad = person_ad[person_ad['country'].isin(countrylist)] #drop countries person_ad = person_ad.drop(['country'],axis=1) #drop duplicates person_ad = person_ad.drop_duplicates()

#merge to get ID for people in country list person_st = person_st.merge(person_ad,left_on=['_link'],right_on=['_link_person_statement']) #drop link columns person_st = person_st.drop(['_link','_link_person_statement'],axis=1) #create list of people IDs person_bl = person_st['statementID'].tolist() #free up memory del person_ad gc.collect()

# read in OOC statements df_chunk = pd.read_csv('csv/ooc_statement.csv',usecols=['subject_describedByEntityStatement','interestedParty_describedByPersonStatement'],chunksize=1000000) chunk_list = [] for chunk in df_chunk: # filter to person owning company chunk = chunk[chunk['interestedParty_describedByPersonStatement']!=''] chunk_list.append(chunk) #join up chunks ooc = pd.concat(chunk_list)

#merge with country list IDs ooc['interestedParty_describedByPersonStatement'] = ooc['interestedParty_describedByPersonStatement'].astype('uint64') ooc = ooc.merge(person_st,left_on=['interestedParty_describedByPersonStatement'],right_on=['statementID']) ooc = ooc.drop(['statementID'],axis=1) #free up memory del person_st del chunk gc.collect() #merge with iden to get CH numbers ooc = ooc.merge(iden, left_on=['subject_describedByEntityStatement'],right_on=['statementID']) ooc = ooc.drop(['statementID','subject_describedByEntityStatement'],axis=1) #free up memory del iden gc.collect()

#create networkx graph H = nx.from_pandas_edgelist(ooc, source='id',target='interestedParty_describedByPersonStatement',create_using=nx.DiGraph) #free up memory del ooc gc.collect() #combine both graphs R = nx.compose(G, H) #free up memory del G, H gc.collect()

#create a set of all entity IDs with a connection to a entity or person in country connected_entities = set() bl=entity_bl+person_bl for x in bl: try: connected_entities.update(nx.ancestors(R,x)) except: pass

#create a set of all entity IDs with a connection to a entity or person in country owned_entities = [] for x in bl: try: owned_entities.append(nx.ancestors(R,x)) except: pass

chain_length = pd.DataFrame(columns=['entity','BO','distance']) for (x,y) in zip(bl,owned_entities): for i in y: try: newrow = {'BO':x,'entity':i,'distance':len(nx.shortest_path(R,source=i,target=x))-1} except: pass chain_length.loc[len(chain_length)]=newrow chain_length_count = chain_length.groupby(by=['distance']).count().reset_index()

print('There are '+str(len(entity_bl))+' companies and '+str(len(person_bl))+' people located in EU list of non-cooperative tax jurisdictions countries')

#convert set to dataframe connected_entities = pd.DataFrame(connected_entities) connected_entities = connected_entities[connected_entities[0].str.len()==8] connected_entities = connected_entities.drop_duplicates() print('There are '+str(len(connected_entities.index))+' companies registered with Companies House that are owned (indirectly or directly) by a company or person located in a listed country')

The majority of ownership relationships were direct - the longest chain between a company and the owner in a listed country contains 12 edges.

How many entities declare that they have no beneficial owners?

In properly formatted BODS data this would be identified in the primary OOC statement under interestedParty -> unknown -> noBeneficialOwners.

The PSC register itself does include an option for companies to declare they have no beneficial owners. For example - https://find-and-update.company-information.service.gov.uk/company/01382560/persons-with-significant-control

However, we don't have an indicator for this in our mapping of the PSC data. The example above does have an entity statement in the data set but no OOC statement.

df = pd.read_csv('csv/person_statement.csv',usecols=['statementID','personType'])

df.groupby(by=['personType']).count()

df_chunk = pd.read_csv('csv/entity_identifiers.csv',usecols=['_link_entity_statement','id','scheme'],chunksize=5000000) chunk_list = [] for chunk in df_chunk: #filter to company house number only chunk = chunk[chunk['scheme']=='GB-COH'] chunk = chunk.drop(['scheme'],axis=1) chunk_list.append(chunk) iden = pd.concat(chunk_list)

print(iden[iden['id']=='04906854'])

df_chunk = pd.read_csv('csv/entity_statement.csv',chunksize=2000000) for chunk in df_chunk: chunk = chunk[chunk['_link']==5331961] if not chunk.empty: print(chunk)

ooc = pd.read_csv('csv/ooc_statement.csv',usecols=['subject_describedByEntityStatement','interestedParty_describedByEntityStatement','interestedParty_describedByPersonStatement'])

print(ooc[ooc['subject_describedByEntityStatement']==16558284086462596647])

print(ooc[ooc['subject_describedByEntityStatement']==7478522944795189817])

df = pd.read_csv('csv/person_names.csv',usecols=['fullName'])

df[df['fullName'].str.contains('Unknown')]

How many entities are there where their beneficial owners are unknown?

In properly formatted BODS data this would be identified in the primary OOC statement under interestedParty -> unknown and we have functions in qbods to summarise this data.

In this data set we could theoretically iterate over the OOC statements, construct an ownership chain and identify where chains don't have an end-point that is a person. This also might not work depending on the reporting thresholds of the country.

eg - Live Nation Limited is a UK company owned by a US parent company. We don't have any information on who owns Live Nation Entertainment Inc.

https://register.openownership.org/entities/59b927b267e4ebf340072a1a/graph

This is what that looks like in the mapped PSC data

Further investigation

Investigate PSC data at source to understand what is within the scope of unknown beneficial owner.

We could look into the mapping code to understand how the incoming data is being handled

The company number for Live Nation UK is 0380556

This corresponds to several entity statements as Live Nation UK is also an owner of several other companies.

print(iden[iden['id']=='03805556'])

These are the relevant statementIDs for the entities

ent = pd.read_csv('csv/entity_statement.csv',usecols=['statementID','_link'])

print(ent[ent['_link'].isin([199965,2400753,6748085])])

ooc = pd.read_csv('csv/ooc_statement.csv',usecols=['subject_describedByEntityStatement','interestedParty_describedByEntityStatement'])

There's one OOC statement listing Live Nation UK as a subject - the interested party entity statement relates to the US parent company

print(ooc[ooc['subject_describedByEntityStatement'].isin([12284762247333522994,1715278187371176618,10428407949385890365])])

print(ent[ent['statementID']==14713919030763430437])

print(iden[iden['_link_entity_statement']==199980])

There's no ownership statement with the parent company as a subject. Therefore we don't know who the beneficial owners of Live Nation UK are.

print(ooc[ooc['subject_describedByEntityStatement']==14713919030763430437])

ent_ad=pd.read_csv('csv/entity_addresses.csv',usecols=['_link_entity_statement','country'])

print(ent_ad[ent_ad['_link_entity_statement']==199980])

We could iterate over the OOC statements until the chain either reaches a person or reaches a dead end but that would be quite complicated. Having properly formatted BODS data would make this process relatively simple.

How many entities are there where their beneficial owners are unknown?

ent = pd.read_csv('csv/entity_statement.csv',usecols=['statementID','name','_link']) ooc = pd.read_csv('csv/ooc_statement.csv',usecols=['subject_describedByEntityStatement'])

ent[ent['_link']==5737536]

ooc[ooc['subject_describedByEntityStatement'].astype('str').str.contains('14933364169')]

ent[ent['statementID'].isin(ooc['subject_describedByEntityStatement'])==False]

iden = pd.concat(chunk_list)

iden.head()

iden[iden['id']=='09490140']

How many entities have complex ownership chains? (e.g. 5+ observable layers of ownership)

This could be done more efficiently using properly formatted BODS data. You would take the overarching OOC statement and count how many components are listed then use the relevant component statements to construct the actual chain.

Statement IDs are not unique to each entity. One entity can have multiple entity statements. To mitigate this when joining up the ownership chains I have used company numbers where possible. Where a company number wasn't available (e.g foreign companies) I have used the statement ID.

Not all UK companies had a company number under the 'GB_COH' scheme in the identifiers table. Where possible the company number has been taken from the open corporates link in the same table.

End Date in interests is up to date

Layer of ownership - a company owned directly by a person has 1 layer of ownership

Company A has '5 layers of ownership'

Company A -> Company B -> Company C -> Company D -> Company E -> Person A

#read in OOC interests interests = pd.read_csv('csv/ooc_interests.csv',usecols=['_link_ooc_statement','endDate']) #remove OOC interests that have ended interests = interests[interests['endDate'].isna()] #remove duplicates interests = interests.drop_duplicates() #merge with OOC to get only active statements ooc = ooc.merge(interests, left_on=['_link'],right_on=['_link_ooc_statement']) ooc = ooc.drop(['_link','_link_ooc_statement','endDate'],axis=1) #release memory del interests gc.collect()

#read in ent statements ent = pd.read_csv('csv/entity_statement.csv',usecols=['statementID','_link']) #merge ooc and ent statements to get link number for subject ooc = ooc.merge(ent, left_on=['subject_describedByEntityStatement'],right_on=['statementID']) #merge ooc and ent statements to get link number for owner ooc['interestedParty_describedByEntityStatement'] = ooc['interestedParty_describedByEntityStatement'].astype('uint64') ooc = ooc.merge(ent, left_on=['interestedParty_describedByEntityStatement'], right_on=['statementID'],suffixes = ['_subject','_interestedparty']) ooc = ooc.drop(['subject_describedByEntityStatement','interestedParty_describedByEntityStatement'],axis=1) #release memory del ent gc.collect()

#get company numbers for UK companies df_chunk = pd.read_csv('csv/entity_identifiers.csv',usecols=['_link_entity_statement','id','scheme'],chunksize=5000000) chunk_list = [] for chunk in df_chunk: #filter to company house number only chunk = chunk[(chunk['scheme']=='GB-COH') | (chunk['id'].str.startswith('https://opencorporates.com/companies/gb/',na=False))] chunk = chunk.drop(['scheme'],axis=1) chunk['id'] = chunk['id'].str[-8:] chunk = chunk.drop_duplicates() chunk_list.append(chunk) iden = pd.concat(chunk_list) iden = iden.drop_duplicates()

#merge company numbers with OOC statements ooc = ooc.merge(iden, how='left',left_on=['_link_subject'],right_on=['_link_entity_statement']) ooc = ooc.merge(iden, how='left',left_on=['_link_interestedparty'],right_on=['_link_entity_statement'],suffixes=['_subject','_interestedparty']) #free up memory del iden gc.collect()

#tidy columns ooc = ooc.drop(['_link_subject','_link_interestedparty','_link_entity_statement_subject','_link_entity_statement_interestedparty'],axis=1)

print(ooc.head())

#fill missing IDs with statement ID ooc['id_interestedparty'] = ooc['id_interestedparty'].fillna(ooc['statementID_interestedparty'])

ooc = ooc.drop_duplicates()

ooc = ooc.drop(['statementID_subject','statementID_interestedparty'],axis=1)

levels = ooc i = 0 while i < 3: levels = levels.merge(ooc,left_on=['id_interestedparty'],right_on=['id_subject'],suffixes=['_x','']) levels = levels.drop(['id_subject'],axis=1) i += 1

print(levels.head())

print('Theres are '+str(len(levels['id_subject_x'].drop_duplicates().index))+' companies with ownership chains of 5 or more levels')

.css-15w88e5{color:var(--chakra-colors-fg-neutral-primary);font-weight:inherit;letter-spacing:-0.09px;}Red Flags Analysis

Background

Reading in data

How many entities have foundingDate and dissolutionDate where the difference between these dates is less than a year?

How many entities have ownership chains which have involved natural persons or entities in countries on the EU list of non-cooperative tax jurisdictions?

How many entities declare that they have no beneficial owners?

How many entities are there where their beneficial owners are unknown?

How many entities are there where their beneficial owners are unknown?

How many entities have complex ownership chains? (e.g. 5+ observable layers of ownership)

Red Flags Analysis