Charity data download - data definition

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..