How to query the Sky-Tortoise Westminster database

The Westminster money database was published by Sky and Tortoise and brings together money going to parties, to all-party parliamentary groups and directly to MPs.

You can look up your MP or even just your postcode on the two websites and the methodology explains how it works. It points out that the data is restricted to the current Parliament.

It only includes donations, payments, members, all-party parliamentary groups and parties that were made or have operated from 19 December 2019 onwards.

Crucially, it says that the three sources behind the database

will be updated on an ongoing basis. Every time a new register or version of is published, the data will be collected, cleaned, tested and then added to the database.

Simon Willison pulled out a SQLite database last year which we can query in detail. There is a datasette instance online but below I’ve used the standalone database he produced, by querying it with the online query tool SQLIME.

It looks like his database is over a year old, but he’s supplied the code and tools, in his walkthrough, to make a fresh scrape of the Sky-Tortoise database. Below we use his old database — until I figure out how to run his code.

To run the below queries, just download the standalone database, then go to SQLIME and load the database using “Open File” on the top right.

What’s in the database? Seven tables are explained on the datasette.

Payments to people

The payments table is the main attraction here, with over 10,000 rows but the tables for the All-Party Parliamentary Groups (APPGs) are worth a look too.

SELECT COUNT(*) FROM payments 
WHERE description LIKE '%opera%';

In the declared payments there are 39 records that mention ‘rugby’, 44 involving ‘opera’, 328 involving ‘flight’ and 616 involving ‘tickets’. There are 40 involving ‘family’, 34 with ‘wife’ and eight with ‘husband’.

SELECT * FROM payments 
WHERE description LIKE '%use of%'

The phrase ‘use of’ shows us what people borrow or get to use: offices, venues and meeting rooms, airport lounges, holiday homes abroad, cars, planes and software. There are 180 locations listed for visits: Gavin Williamson and Alun Cairns have been to Somaliland, for example.

SELECT payments.date, payments.description, payments.entity, payments.value, 
payments.purpose_of_visit, members.name, parties.name AS 'party name'
FROM payments
JOIN members ON payments.member_id = members.id
JOIN parties ON members.party_id = parties.id
WHERE destination_of_visit LIKE '%somaliland%'

Visits to Israel and Palestine are dominated by the parties’ Friends of Israel groups

SELECT COUNT(payments.destination_of_visit) AS 'freq', payments.destination_of_visit, payments.entity, ROUND(SUM (payments.value), 0) as 'sum'
FROM payments
JOIN members ON payments.member_id = members.id
JOIN parties ON members.party_id = parties.id
WHERE destination_of_visit LIKE '%israel%' OR '$palestine$'
GROUP BY entity
ORDER BY freq DESC;

Keir Starmer and Ed Davey are the only non-Tories in the top ten for total money received. Theresa May declared almost three million pounds (Tortoise wrote this up) and Boris Johnson declared just over one million.

SELECT members.name, parties.name AS 'party name', SUM(ROUND(payments.value, 0)) AS totaled FROM payments 
JOIN members ON members.id = payments.member_id
JOIN parties ON parties.id = members.party_id 
GROUP BY member_id ORDER BY totaled DESC LIMIT 10;

The biggest single payment declared was by Theresa May at £400,000 for half a dozen talks.

SELECT * FROM payments ORDER BY value DESC;

Boris Johnson’s biggest donations were

SELECT payments.entity, ROUND(payments.value, 0) AS value, members.name 
FROM payments
JOIN members ON members.id = payments.member_id
WHERE name = 'Boris Johnson' 
ORDER BY value DESC;

The £12,000 to Boris Johnson by Rupert Murdoch was for a trip to the US.

SELECT payments.description, payments.entity, payments.value, members.name 
FROM payments
JOIN members ON members.id = payments.member_id
WHERE entity LIKE '%murdoch%'; 

Finally for now, there are over 2,000 different entities that have donated money.

SELECT COUNT(DISTINCT entity)
FROM payments;

The total amount given by each person or organisation ranges from £1.8 million paid by the Withers law firm to a £76 paid by the Cardiff legal firm William Graham Law.

SELECT entity, ROUND(SUM(value), 0) AS total_val
FROM payments
GROUP BY entity
ORDER BY total_val DESC;

This money could be a political donation (to a politician, a party, or an APPG) in money or in terms of resources like office space or flights, or it could be a payment for work (there are lots of publishers in the list). We can focus on Qatar (total of £281, 129).

SELECT entity, ROUND(SUM(value), 0) AS total_val
FROM payments
WHERE entity LIKE '%doha%' OR entity LIKE '%qatar%'
GROUP BY entity
ORDER BY total_val DESC;

Or betting companies.

There is also £10,000 from The Tote and small sums from Sports Information Services (data for betting companies), Flutter entertainment, Star Sports (South Asian TV), Bet365, Betfred and Genting casinos.

The horse racing sector appears quite frequently too, with a combined value of £101,000 from The Jockey Club, The Turf Club, British Racing’s Horse Welfare Board, various racecourses (Ascot, Chester, Ffos Las, Goodwood and Jockey Club), Racecourse Media Group, the Racecourse Association and Arena Racing.

We can ask who’s working at GB News or receiving appearance fees.

SELECT members.name, payments.entity, ROUND(SUM (payments.value), 0) as total_val, payments.description AS 'sample desc'
FROM payments
JOIN members ON members.id = payments.member_id
WHERE entity LIKE '%GB News%'
GROUP BY name
ORDER BY total_val DESC; 

A next step might be to cross reference these entities with the lobbying register

People

We can use the payments table to see which MPs make the most declarations (rather than who declared the most money). The top ten are

SELECT members.name, parties.name AS party_name, COUNT(*) AS nb
FROM payments
JOIN members ON members.id = payments.member_id
JOIN parties ON parties.id = members.party_id
GROUP BY member_id
ORDER BY nb DESC
LIMIT 10; 

We can use the same syntax to see who is in the most APPGs

SELECT members.name, parties.name AS party_name, COUNT(*) AS nb
FROM member_appgs
JOIN members ON members.id = member_appgs.member_id
JOIN parties ON parties.id = members.party_id
GROUP BY member_id
ORDER BY nb DESC
LIMIT 20; 

What are the APPGs with DUP members?

SELECT members.name, parties.name AS party_name, COUNT(*) AS nb 
FROM member_appgs JOIN members ON members.id = member_appgs.member_id 
JOIN parties ON parties.id = members.party_id
WHERE party_name == 'Democratic Unionist Party' 
GROUP BY member_id ORDER BY nb DESC;

What are the APPGs that Caroline Lucas is a member of?

SELECT members.name, parties.name AS party_name, appg_name
FROM member_appgs
JOIN members ON members.id = member_appgs.member_id
JOIN parties ON parties.id = members.party_id
WHERE members.name LIKE '%Lucas%';

All-party Parliamentary Groups

There are almost 500 different APPGs that appear in the appg_donations table (498). As of April 2024 there are 445 APPGs, so some have disappeared.

SELECT COUNT(DISTINCT appg_name)
FROM appg_donations; 

Which APPGs have received the most money in donations?

SELECT appg_donations.appg_name, appg_donations.entity, ROUND(SUM (appg_donations.value), 0) as total_val
FROM appg_donations GROUP BY appg_donations.appg_name
ORDER BY total_val DESC; 

Who’s in the AI group?

SELECT member_appgs.appg_name, member_appgs.role, members.name, parties.name AS 'party name'
FROM member_appgs 
JOIN members ON members.id = member_appgs.member_id
JOIN parties ON parties.id = members.party_id
WHERE appg_name LIKE '%artificial%'
ORDER BY party_id;

Who’s giving it money? It’s received about £300,000 up to March 2022

SELECT date, entity, value
FROM appg_donations
WHERE appg_name LIKE '%artificial%' 

This has come from a group called Big Innovation Centre, which just gathers donations from 15 different bodies. These are British Standards Institution; Capita; CMS Cameron McKenna Nabarro Olswang; Deloitte; Dufrain; Osborne Clarke; PwC Blue Prism; Creative England; Megger Group Limited; Microsoft; Omni Telemetry Ltd; Oracle; Osborne Clarke LLP; Rialto.

While the Chair of the AI APPG Stephen Metcalfe appears in the payments table for an unrelated £13,000 payment, he doesn’t appear when the APPG receives money, it seems.

Where do the members of the AI APPG get money, aside from donations to the AI APPG?

SELECT COUNT(DISTINCT payments.entity) AS ents, ROUND (AVG (payments.value), 0) AS avg, members.name FROM payments 
JOIN members ON members.id = payments.member_id 
WHERE 
name = 'Stephen Metcalfe' OR
name = 'Chris Green' OR
name = 'Patrick Grady' OR
name = 'Sir Mark Hendrick' OR
name = 'Justin Madders' OR 
name = 'Darren Jones'
GROUP BY members.name
ORDER BY ents DESC; 

Who are these 18 groups giving money to MPs who are in the AI APPG?

SELECT DISTINCT payments.entity, ROUND(SUM(payments.value), 0) AS val, members.name 
FROM payments 
JOIN members ON members.id = payments.member_id 
WHERE name = 'Stephen Metcalfe' OR name = 'Chris Green' OR name = 'Patrick Grady' OR name = 'Sir Mark Hendrick' OR name = 'Justin Madders' OR 
name = 'Darren Jones'
GROUP BY entity
ORDER BY name DESC;

Note that Chris Green shares three of his financial sources with other members of the AI APPG. These sources (Betting and Gaming Council, YouGov, Ipsos Mori) only appear once.

So in fact these MPs’ AI APPG receives donations from:

• British Standards Institution;
• Capita;
• CMS Cameron McKenna Nabarro Olswang;
• Deloitte;
• Dufrain;
• Osborne Clarke;
• PwC Blue Prism;
• Creative England;
• Megger Group Limited;
• Microsoft;
• Omni Telemetry Ltd;
• Oracle;
• Osborne Clarke LLP;
• Rialto.

And separately, as individuals, they receive money from

• Anderson Group Services;
• BBC;
• Betting and Gaming Council;
• Darren Jones;
• GMB Union;
• IX Wireless;
• Ipsos MORI;
• Ministry of Foreign Affairs, Qatar;
• Newcleo;
• Plexal (City);
• Savanta ComRes;
• Trade[s] Union Congress;
• Unite;
• Yonder Consulting;
• YouGov.

Finally there will be donations to their parties (Conservative, Labour, SNP, Independent).