Registered charities in England and Wales

Data for registered and removed charities in England and Wales

This extract is of all registered and removed charities, the full requirement is specified elsewhere. There are 15 output files, each corresponding to a table in the extract. The files are BCP files (extension ‘.bcp’) which, due to embedded characters in the data, have the following delimiters:

@**@ for columns and

*@@* for rows.

In addition, the files have been compressed using WinZip with a name like RegPlusExtract_month_year

 

Table definitions

These are defined below:

Table/Column Type nulls Description/notes
extract_acct_submit account submission   Account submission history for a main registered charity
regno integer null registered number of a charity
submit_date smalldatetime null date submitted
arno char(4) not null annual return mailing cycle code
fyend varchar(4) null Charity’s financial year end date (may be blank)
extract_aoo_ref area of operation reference   Description of an area of operation
aootype varchar(10) not null A (wide), B (LA), C (GLA/met county), D (country), E (continent)
aookey integer not null Values if aootype=A are: 1 England/Wales; 2 England; 3 Wales; 4 London.
aooname varchar(255) not null name of an area of operation
aoosort varchar(100) not null for searches, “City of” removed from aooname
welsh varchar(1) not null Flag: “Y” or blank
master integer null may be blank. If aootype=D then holds continent; if aootype=B then holds GLA/met county
extract_ar_submit AR submission   Annual Return submission history for a main registered charity
regno integer null registered number of a charity
arno char(4) not null annual return mailing cycle code
submit_date smalldatetime null date submitted
extract_charity Charity   One per registered/removed charity (including subsidiaries)
regno integer null registered number of a charity
subno integer null subsidiary number of a charity (may be 0 for main/group charity)
name varchar(150) null main name of the charity
orgtype varchar(10) null R (registered) or RM (removed)
gd nvarchar(max) null Description of Governing Document
aob varchar(max) null area of benefit – may not be defined
aob_defined interger null area of benefit defined by Governing Document (T/F)
nhs varchar(1) not null NHS charity (T/F)
ha_no integer null Housing Association number
corr varchar(255) null Charity correspondent name. NULL if charity status is “Removed”
add1 varchar(35) null address line of charity’s correspondent. NULL if charity status is “Removed”
add2 varchar(35) null address line of charity’s correspondent. NULL if charity status is “Removed”
add3 varchar(35) null address line of charity’s correspondent. NULL if charity status is “Removed”
add4 varchar(35) null address line of charity’s correspondent. NULL if charity status is “Removed”
add5 varchar(35) null address line of charity’s correspondent. NULL if charity status is “Removed”
postcode varchar(8) null postcode of charity’s correspondent. NULL if charity status is “Removed”
phone varchar(400) null telephone of charity’s correspondent. NULL if charity status is “Removed”
fax integer null fax of charity’s correspondent. NULL if charity status is “Removed”
extract_charity_aoo area of operation   Area of operation for a charity
regno integer null registered number of a charity
aootype varchar(10) not null A B or D
aookey integer not null up to three digits
welsh varchar(1) not null Flag: “Y” or blank
master integer null may be blank. If aootype=D then holds continent; if aootype=B then holds GLA/met county
extract_class classification table   classification details
regno integer null registered number of a charity
class varchar(10) not null classification code for a charity (multiple occurrences possible)
extract_class_ref reference table   classification reference
classno varchar(10) not null classification code
classtext varchar(65) null description of a classification code
extract_financial financial history   Financial history for a main registered charity
regno integer null registered number of a charity
fystart smalldatetime null Charity’s financial year start date
fyend smalldatetime null Charity’s financial year end date
income numeric(12,0) null
expend numeric(12,0) null
extract_main_charity main charity table   One for every main registered charity
regno integer null registered number of a charity
coyno varchar(50) null company registration number
trustees varchar(1) not null trustees incorporated (T/F)
fyend varchar(4) null Financial year end
welsh varchar(1) not null requires correspondence in both Welsh & English (T/F)
incomedate smalldatetime null date for latest gross income (blank if income is an estimate)
income numeric(12,0) null
grouptype varchar(3) null may be blank
email varchar(400) null email address
web varchar(400) null website address
extract_name name table   Main old and working names for a charity
regno integer null registered number of a charity
subno integer null subsidiary number of a charity (may be 0 for main/group charity)
nameno integer not null number identifying a charity name
name varchar(255) null name of a charity (multiple occurrences possible)
extract_objects objects table   charity objects. Records with sequence numbers
regno integer null registered number of a charity
subno integer null subsidiary number of a charity (may be 0 for main/group charity)
seqno varchar(4) null Sequence number (in practice 0-20)
object varchar(max) null Description of objects of a charity
extract_partb Annual Return Detail table   Row for each AR for each registered main charity
regno integer null registered number of a charity
artype char(4) not null annual return mailing cycle code
fystart datetime not null Charity’s financial year start date
fyend datetime not null Charity’s financial year end date
inc_leg varchar(max) null Legacies
inc_end varchar(max) null Endowments
inc_vol varchar(max) null Voluntary Income
inc_fr varchar(max) null Activities generating funds
inc_char varchar(max) null Charitable activities
inc_invest varchar(max) null Investment income
inc_other varchar(max) null Other Income
inc_total varchar(max) null Total Incoming resources
invest_gain varchar(max) null Gains/loss on investments
asset_gain varchar(max) null Revaluations of fixed assets
pension_gain varchar(max) null Gains/loss on Pension Fund
exp_vol varchar(max) null Voluntary income costs
exp_trade varchar(max) null Fundraising Trading costs
exp_invest varchar(max) null Investment Management costs
exp_grant varchar(max) null Grants to institutions
exp_charble varchar(max) null Charitable Activities costs
exp_gov varchar(max) null Governance costs
exp_other varchar(max) null Other resources expended
exp_total varchar(max) null Total Resources expended
exp_support varchar(max) null Support costs
exp_dep varchar(max) null Depreciation
reserves varchar(max) null Reserves
asset_open varchar(max) null Total fixed assets (at start of year)
asset_close varchar(max) null Total fixed assets
fixed_assets varchar(max) null Fixed Investments Assets
open_assets varchar(max) null Fixed Investments Assets (start of year)
invest_assets varchar(max) null Current Investment Assets
cash_assets varchar(max) null Cash
current_assets varchar(max) null Total Current Assets
credit_1 varchar(max) null Creditors – within one year
credit_long varchar(max) null Creditors – Long Term/Provision
pension_assets varchar(max) null Pension Assets/Liabilities
total_assets varchar(max) null Total Net Assets/Liabilities
funds_end varchar(max) null Endowment funds
funds_restrict varchar(max) null Restricted funds
funds_unrestrict varchar(max) null Unrestricted funds
funds_total varchar(max) null Total funds
employees varchar(max) null Employees
volunteers varchar(max) null Volunteers
cons_acc varchar(max) null Consolidated accounts (True/False)
charity_acc varchar(max) null Charity only accounts (True/False)
extract_registration registration table   registration details & removals
regno integer null registered number of a charity
subno integer null subsidiary number of a charity (may be 0 for main/group charity)
regdate smalldatetime null date of registration for a charity
remdate smalldatetime null Removal date of a charity – Blank for Registered Charities
remcode char(3) null Register removal reason code
extract_remove_ref removal reference   Reference for remcode in extract_registration
code char(3) null Register removal reason code
text char(25) null Removal reason description
extract_trustee trustee table   trustee names
regno integer null registered number of a charity
trustee varchar(255) null Name of a charity trustee

Table Build Scripts

These are included on the CD in the file “TableBuildScripts.zip” and are the scripts required to build the extract tables on a SQL Server platform. The first command in each script specifies the database to be used, amend the database name to match your own environment..