Publishing using spreadsheets

To produce 360Giving data in a spreadsheet, it is possible to start with an empty spreadsheet and construct the column titles (and any additional sheets), using the information given below. However, for many people, the starting point is the spreadsheet template described below.

Excel (.xlsx)

For convenience we provide a 360Giving Spreadsheet Template that can be used directly, or adapted to your needs.

The template is a multi-sheet spreadsheet, and each sheet is described below.

Many data producers will be able to fit all the information about a single grant on one row of a spreadsheet. In fact most data producers do exactly that, and provide a single sheet with many individual grants.

Where data producers have more complex information, for example where a grant has many beneficiary locations, we call this a One to many relationships. Information about how to create data with One to many relationships is described below.

The 360Giving Spreadsheet template consists of a ‘grants’ sheet which contains the most common data fields.

The Additional fields section provides details of all other possible fields that can be reported. (These are derived from the 360Giving JSON Schemas ).

Grants Sheet

The main ‘grants’ sheet includes sections for:

  • Basic information about the grant;
  • Planned dates for the grant;
  • Planned dates of the activity;
  • Details of the recipient organisation;
  • Details of the funding organisation;
  • The location of beneficiaries;
  • Details of the grant programme funding is from;
Title Description Type Required
Identifier The unique identifier for this grant. Made up of your 360Giving prefix, and an identifier from your records. See the 360Giving Grant identifier guidance for details. string True
Title A title for this grant activity. This should be under 140 characters long. string True
Description A short description of this grant activity. string True
Currency The currency used in amounts. Use the three-letter currency code from ISO 4217 eg: Use GBP for Pounds Sterling. string True
Amount Applied For Total amount applied for in numbers (do not include commas or currency symbols such as £). If you have provided detailed transaction information on a separate table, this should equal the sum of all the application transactions for this grant. number False
Amount Awarded Total amount awarded in numbers (do not include commas or currency symbols such as £). If you have provided detailed transaction information on a separate table, this should equal the sum of all the award transactions for this grant. number True
Amount Disbursed Total amount disbursed (paid) to this grantee when this record was last updated (in numbers: do not include commas or currency symbols such as £)). If you have provided detailed transaction information on a separate table, this should equal the sum of all the disbursement transactions for this grant. number False
Award Date When was the decision to award this grant made. The date should be written as YYYY-MM-DD, or in full date-time format. string True
URL A URL (Web Address) where further information about this grant can be found. This could point to the website of the recipient organisation, or might link to further details on the funders website. uri False
Planned Dates:Start Date All events should have a start date. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:End Date An end date for the grant. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:Duration (months) The duration of the grant, in months. Must be in number format. number False
Recipient Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Recipient Org:Name Organisation name string True
Recipient Org:Charity Number Registered charity number, if applicable. string False
Recipient Org:Company Number Registered UK company number, if applicable. string False
Recipient Org:Street Address Building number and street name. string False
Recipient Org:City City or town. string False
Recipient Org:County County string False
Recipient Org:Country Country string False
Recipient Org:Postal Code Postal code (please try and provide a post code whenever possible) string False
Recipient Org:Description A short description of this organisation and its area of work string False
Recipient Org:Web Address A web address for the Organisation uri False
Beneficiary Location:Name A name for this location. string False
Beneficiary Location:Country Code The ISO Country Code of the location of this activity. string False
Beneficiary Location:Latitude The latitude of a point location number False
Beneficiary Location:Longitude The longitude of a point location number False
Beneficiary Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Beneficiary Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Funding Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Funding Org:Name Organisation name string True
Funding Org:Department The department or sub-unit of this organisation making or receiving the grant. string False
Grant Programme:Code An identifier for this grant programme. string False
Grant Programme:Title The title of this grant programme. string False
Grant Programme:URL A web link to more details of this grant programme. uri False
From An Open Call? Was this grant made as the result of an open call for applications? Values should be ‘Yes’ or ‘No’ string False
Last Modified When was information on this grant last updated? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False
Data Source A web link pointing to the source of this data. This may be an original 360Giving data file, a file from which the data was converted, or an organisation website. uri False

Additional fields

The main ‘grants’ sheet only includes the most common information used by most data publishers. For many people this is enough.

The other sheets in the 360Giving Spreadsheet Template provide the details of all the possible fields that can be reported. These sheets serve a dual purpose:

  1. As a way to add more information to our ‘grants’ sheet

    The column titles in the extra sheets provide a handy mapping from the JSON Schema to a more human readable form, showing us all of the possible fields available in the 360Giving Data Standard.

    You can use any of these column titles on your main ‘grants’ sheet if you wish.

  2. As a way of providing information about One to many relationships

If, when creating your data, you only need a few additional fields from the additional sheets, you can simply copy them from one sheet to another.

If you have additional data to report that does not fit any of the columns provided in the spreadsheet, it is okay to create your own column titles in order to report it.

Hint

Naming your own columns.

If you are adding your own column titles it is best to use simple titles and to avoid special characters which could cause problems in data reuse.

Using only lowercase and uppercase alphabetical characters (a-z and A-Z), numerical digits (0-9), colons (:), parentheses (( and )) and single spaces will help to avoid problems. Full-stops (.) are known to cause issues and should be avoided. Other characters could be used, but haven’t been fully tested in all possible situations.

Actual Dates

When did this grant activity actually take place. Dates should be in YYYY-MM-DD format. A date range can include a start date and duration in months, or a start and end date.

Title Description Type Required
Actual Dates:Title The title of this event string False
Actual Dates:Start Date All events should have a start date. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Actual Dates:End Date An end date for the grant. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Actual Dates:Duration (months) The duration of the grant, in months. Must be in number format. number False
Actual Dates:Description A description of this event string False
Actual Dates:Last Modified When was information about this event last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Planned Dates

When the recipient organisation intends this activity to take place. A date range can include a start date and duration in months, or a start and end date.

Title Description Type Required
Planned Dates:Title The title of this event string False
Planned Dates:Start Date All events should have a start date. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:End Date An end date for the grant. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:Duration (months) The duration of the grant, in months. Must be in number format. number False
Planned Dates:Description A description of this event string False
Planned Dates:Last Modified When was information about this event last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Org

Details of the funder

Title Description Type Required
Funding Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Funding Org:Name Organisation name string True
Funding Org:Department The department or sub-unit of this organisation making or receiving the grant. string False
Funding Org:Contact Name The contact person at this organisation. string False
Funding Org:Charity Number Registered charity number, if applicable. string False
Funding Org:Company Number Registered UK company number, if applicable. string False
Funding Org:Street Address Building number and street name. string False
Funding Org:City City or town. string False
Funding Org:County County string False
Funding Org:Country Country string False
Funding Org:Postal Code Postal code (please try and provide a post code whenever possible) string False
Funding Org:Phone Number Contact phone number. string False
Funding Org:Alternate Name An alternative name for this organisation (e.g. trading name) string False
Funding Org:Email The email address for this organisation. string False
Funding Org:Description A short description of this organisation and its area of work string False
Funding Org:Organisation Type A description of this organisation string False
Funding Org:Web Address A web address for the Organisation uri False
Funding Org:Last Modified When was the organisation information for this grant last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Recipient Org

Details of the recipient of this grant.

Title Description Type Required
Recipient Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Recipient Org:Name Organisation name string True
Recipient Org:Department The department or sub-unit of this organisation making or receiving the grant. string False
Recipient Org:Contact Name The contact person at this organisation. string False
Recipient Org:Charity Number Registered charity number, if applicable. string False
Recipient Org:Company Number Registered UK company number, if applicable. string False
Recipient Org:Street Address Building number and street name. string False
Recipient Org:City City or town. string False
Recipient Org:County County string False
Recipient Org:Country Country string False
Recipient Org:Postal Code Postal code (please try and provide a post code whenever possible) string False
Recipient Org:Phone Number Contact phone number. string False
Recipient Org:Alternate Name An alternative name for this organisation (e.g. trading name) string False
Recipient Org:Email The email address for this organisation. string False
Recipient Org:Description A short description of this organisation and its area of work string False
Recipient Org:Organisation Type A description of this organisation string False
Recipient Org:Web Address A web address for the Organisation uri False
Recipient Org:Last Modified When was the organisation information for this grant last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Beneficiary Location

Information about the location of beneficiaries. Further information about beneficiaries can be provided through classifications.

Title Description Type Required
Beneficiary Location:Identifier Location identifier string False
Beneficiary Location:Name A name for this location. string False
Beneficiary Location:Country Code The ISO Country Code of the location of this activity. string False
Beneficiary Location:Latitude The latitude of a point location number False
Beneficiary Location:Longitude The longitude of a point location number False
Beneficiary Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Beneficiary Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Beneficiary Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Beneficiary Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Org:Location

-

Title Description Type Required
Funding Org:Location:Identifier Location identifier string False
Funding Org:Location:Name A name for this location. string False
Funding Org:Location:Country Code The ISO Country Code of the location of this activity. string False
Funding Org:Location:Latitude The latitude of a point location number False
Funding Org:Location:Longitude The longitude of a point location number False
Funding Org:Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Funding Org:Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Funding Org:Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Funding Org:Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Recipient Org:Location

-

Title Description Type Required
Recipient Org:Location:Identifier Location identifier string False
Recipient Org:Location:Name A name for this location. string False
Recipient Org:Location:Country Code The ISO Country Code of the location of this activity. string False
Recipient Org:Location:Latitude The latitude of a point location number False
Recipient Org:Location:Longitude The longitude of a point location number False
Recipient Org:Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Recipient Org:Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Recipient Org:Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Recipient Org:Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Classifications

-

Title Description Type Required
Classifications:Vocabulary A vocabulary used for this classification. string False
Classifications:Code A codelist value in the chosen vocabulary. string False
Classifications:Title The title of this classification. string False
Classifications:Description A description of this classification. string False
Classifications:URL A web link to more details of this classification. uri False
Classifications:Last Modified When was this grant classification information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Type

-

Title Description Type Required
Funding Type:Vocabulary A vocabulary used for this classification. string False
Funding Type:Code A codelist value in the chosen vocabulary. string False
Funding Type:Title The title of this classification. string False
Funding Type:Description A description of this classification. string False
Funding Type:URL A web link to more details of this classification. uri False
Funding Type:Last Modified When was this grant classification information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Grant Programme

-

Title Description Type Required
Grant Programme:Code An identifier for this grant programme. string False
Grant Programme:Title The title of this grant programme. string False
Grant Programme:Description A description of this grant programme. string False
Grant Programme:URL A web link to more details of this grant programme. uri False
Grant Programme:Last Modified When was the link between this grant and its grant programme last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Transactions

The 360Giving Data Standard also allows for the reporting of three types of transactions:

  • commitmentTransaction
  • disbursementTransaction
  • applicationTransaction

These do not currently have nice human readable titles, but can still be added as spreadsheet columns if needed.

To create the column titles, refer to the 360Giving JSON Schema and use the JSON pointer paths as column titles. e.g. commitmentTransaction/0/id

One to many relationships

Each of the sections of additional fields above can have multiple occurrences for one grant. There are three ways of describing this in a spreadsheet.

Additional sheets

Use the other sheets in the 360Giving Spreadsheet Template. These have the columns described above, plus an extra column at the start for the Identifier of the relevant grant.

For the Funding Org: Location and Recipient Org: Location there is also an extra column for the Identifier of the relevant Funding/Recipient Org.

Numbering

You can describe multiple occurrences within the Grants sheet by having multiple columns. Use :<num>: instead of a :. This imitates JSON Pointer’s approach.

e.g. to have two related documents with their own title and web address:

Related Document:0:Title Related Document:0:Web Address Related Document:1:Title Related Document:1:Web Address
A Document http://example.com/adocument Another Document http://example.com/anotherdocument

Multiple Rows

You can place the additional information about a grant in an additional row. Use the same Identifier for the grant, and place the additional information in the relevant columns. Consuming applications will then be able to try to merge the information into a single record, so be careful not to place contradictory information in fields that cannot have more than one value (e.g. a title or description)

Field guidance

Dates and times

360Giving requires you to provide information on when a grant was awarded, and allows you to add details of when a project is taking place, and when you last updated information about aspects of the grant.

There are three different rules for validating dates:

Full dates (Award Dates and Transaction Dates)

The Award Date must provide a full date, including year, month and day in YYYY-MM-DD format (e.g. 2017-04-02 for the 2nd April 2017).

In some rare cases, an award date might also need to include the time of the grant, using a date-time format (e.g. 2017-04-02T16:45:00Z - a grant made at 4.45pm).

Hint

You can set Excel to present a date column in YYYY-MM-DD format using a custom format as described here.

Uncertain dates (Planned Dates and Actual Dates)

Other events in the lifetime of a grant, such as for when the funded activity will take place, may include less specific date information. Funders should aim to be as specific as they can be, but do not need to guess at the particular day or month when an activity will take place if they are not certain or do not yet know.

Dates in the Planned Dates and Actual Dates groups should be provided in YYYY-MM-DD format, but the day or the day can be dropped or on the year provided (e.g. YYYY-MM or YYYY).

For example, if an application only indicates that a project will start in May 2019, then the Planned Dates:Start Date value may be ‘2019-05’.

It is up to users of the data to judge how to interpret dates which only include a year, or year and month. Different applications and analysis may require different judgements.

Date-time (Last Modified dates)

All rows in a 360Giving spreadsheet, and all objects in the JSON structure, can have a Last Modified date.

If used, this must always be in full date-time format so that if multiple updates take place on a single day, consuming applications can work out which version to use.

Hint

You can set Excel to present a date column as a full date-time using the custom format of “yyyy-mm-ddThh:mm:ssZ”. If you also set the formula for the entire column to `=Now()` then this value will be refreshed automatically every time you save the file.

Conformance

In order to conform with the spreadsheet standard:

You must:

  • Read the column definitions carefully and follow the format they request - for example, formatting identifiers and dates according to the standard. Full reference information is provided below.
  • Provide an Identifier for each grant
  • Update the Last Modified date whenever the status of a grant changes

You can:

  • Remove or hide non-required columns that you are not using - although make sure you check for any hidden columns before publishing your data, and always remove rather than hide sensitive information.
  • Re-order the columns so that information is arranged in the way you want
  • Add extra columns to include information you want to share, but that is not covered by the standard. (See Additional fields).
  • Move columns in the 360Giving Spreadsheet Template between sheets.

You must not:

  • Add extra rows at the top of the table
  • Change the field names provided by the standard

CSV (.csv)

TODO