Delivery of UK, European and International trade flow information

AWARDED CONTRACT - AHDB requires information on trade flows between different countries across the globe. At the most granular level, this “Dataset” includes:

• Trade flows from two- down to ten-digit HS commodity code level.
• Trade flow information for a variety of countries across the globe (both reporter and/or partner country).
• Information on the direction of travel (import, export, total trade, and trade balance).
• Information on the port and mode of transport where possible
• Information on the State where applicable.
• The volume of any trade flow depicted in appropriate units (e.g. kilograms, tonnes, head or litres).
• The value of any trade flow in £s, USDs, €s and the reporting country’s own currency.
• The average unit price of trade flows.
• The latest monthly information as well as historical monthly, quarterly, and annual data (at least 20 years).

Web-based platform

AHDB require access, for approximately 50 team members, to a web-based platform which allows users to view and interrogate the “Dataset” outlined above. Ideally, the system will allow users to perform the below functions. The supplier should outline a) if any of the below is not possible and b) if any additional functionality is available.

• View the latest trade flow information which will be available within 2 to 3 working days of it being released by the relevant country’s Customs and Excise office or equivalent.
• View historical trade flow information in both graph and tabular format. Able to view historical data for the UK back to 1990 and for EU and non-EU countries for ideally at least 25 years.
• For any post-Brexit UK import data, we would require country of origin for GB imports and despatch for imports into NI. UK exports should be recorded as country of destination.
• Apply filters to view different commodity codes and/or countries and/or time periods.
• Apply pre-determined groupings to view different commodity codes and/or countries and/or time periods to include monthly, quarterly, annual, year ending and year to date views.
• Create and save bespoke groupings of commodity codes and countries.
• Ability to view the EU values as a collective country group within any query results
• Share bespoke groupings of commodity codes and/or countries and/or time periods with other users.
• Drill down and view the codes at the most granular level contained in a bespoke commodity grouping.
• View volume and value in one table as well as being able to toggle between volume and value of the trade flow.
• View average unit price in a table or graph for the commodity code/product grouping.
• View a list of trade data flows by commodity code, product grouping, reporting country, trade partner, port, mode of transport and State where applicable.
• Apply sorts to view data alphabetically or by ascending or descending values (volume, head, value, and unit price).
• To interchange between currencies, with the exchange rate and date clearly stated.
• Extract any data, with and without filters applied, in to an excel format. This extract will export/present data in an appropriate format e.g., in formatted tables.
• View a list of the latest trade flow information data available by country and when each country’s data was last updated and when next releases are due.
• Create and save data queries which can run automatically. If possible, when scheduled, it is desirable for the output to be emailed to AHDB.

Data extract service

In addition to the web-based platform, AHDB require the “Dataset” to be delivered via an FTP or API protocol for inclusion in our in-house database (property of AHDB). These data files should:

• Be provided in CSV format or links to the dataset via API.
• Be delivered, at a minimum, monthly (options for more frequent supply e.g., weekly should be detailed if available).
• Be delivered within 1 working day of the UK HMRC trade flow information being uploaded to the web-based platform.
• For any post Brexit UK import data we would require country of origin for GB imports and despatch for imports into NI. Exports should be recorded as country of destination.
• Contain the latest trade flow information.
• Contain any historical trade flow information, going back at least 10 years, which has been updated/amended since the last data extract service was provided (please note a complete data file is required rather than just the amends).
• Contain the following information:

o Flow direction (import or export only)

o Year

o Month

o HS commodity code

o Reporter country

o Partner country

o Value ($, € and £)

o Unit type (in appropriate units e.g. kilograms, tonnes, head or litres)

o Quantity of units