GATS
Global Agricultural Trade System
“The Basics”
Training Manual
June 22, 2009
Contents
Exercise 1. What is the dollar value of all shrimp imported to the U.S. in 2006?
Exercise 3: Configuring the Standard Query to automatically execute the saved ethanol imports query.
Exercise 4: Retrieving the Ethanol Saved Selections via Access Saved Selections
The GATS home page provides the entry point into the application in addition to some key at-a-glance features that bring some of the most important information to forefront. Features include easy access to the Standard and Advanced Query, Announcements, Next Release information, Agricultural Trade Totals, and a description of the primary GATS features.
The Standard Query interface has been designed with ease of use in mind. It leverages the appearance that users have grown accustomed to with the U.S. Trade system. It allows the user to generate data with the fewest number of clicks and presents all selection options on a single screen. You will likely find that this will be the interface of choice for retrieval of data from the GATS system.
The Standard Query interface is broken into up to 11 different areas. These areas will vary slightly depending on other options selected. For instance, when the FAS U.S. Trade data source is selected, the Reporters area is hidden as the reporter for this data source is always the United States. Similarly, when U.S. States is selected, the product type is always Exports, as import values are not reported.
Since there are significant interdependencies between each of these areas, it is highly suggested that you make your selections in the order that they are presented. If you fail to do so, you may need to reselect some options.
1. Data Source – The Data Source area allows the user to select the source of data to be analyzed. Selection options are:
· FAS U.S. Trade
· U.S. Customs Districts
· U.S. States
2. Product Type – The Product Type selection area allows the user to define if they wish to report on Imports, Exports, and other possible product types depending on the selected Data Source. Possible values are defined as follows:
|
Data Source |
Exports |
Imports – Consumption |
Imports – General |
Re-Exports |
|
FAS U.S. Trade |
a |
a |
a |
a |
|
U.S. Customs Districts |
a |
a |
a |
a |
|
U.S. States |
a |
|
|
|
3. Product Group – Products Groups define commonly reference groupings of agricultural commodities. The mechanism of grouping and a comprehensive listing of the groupings are outside the scope of this document. It is important to note that the listing of available groupings for selection is dependent upon the Data Source and Product Type selected.
4. Reporters – only valid for Data Sources other than FAS U.S. Trade. The reporters area lists the valid reporters and groupings of Reporters for a given Data Source. There are also some options at the top of the selection area to select either all Reporters for a given Data Source or all grouping levels within a given Data Source.
5. Partners – The Partners selection area allows the user to select the entity that the reporter has conducted trade with. In addition to selecting a specific entity, the user may also select all partners, world total or other groupings depending on the Data Source.
6. Products – The products selection area allows selection of specific commodities, aggregates, aggregate groups, groups, and categories for reporting. The products listing corresponds to the Product Group selected. Note for some of the Product Groups, a more enhanced interface exists that allows you to select a product level and then expand that level for more refined selection. For example, when using the HS-4 through HS-10 product groups, the system allows you to filter based on the chapter (HS-2) headings.
7. Commodity Search – Allows the user to search for a specific commodity by either commodity name, or code. The results that match the users search criteria will be displayed in the product selection area. To clear the search results and return to the default products view, click the clear button.
8. Statistics – Two statistics can be returned at any given time from the GATS system. Value is the default selection. Unit Values are also available from the Value selection box. Adjacent to the Value selection box, you may select the units of the value field. Available selections are: Dollars, Thousands, Millions, and Billions. GATS defaults to displaying values in Thousands. You may also choose to analyze quantity data. To do so, click on the Quantity selection box and select Quantity. Then select the appropriate unit of measure from one of the four units of measure options available. Each unit of measure is determined by the underlying commodity or commodities. They can be found in the data definitions area of GATS.
9. Dates – Four selections determine the time increment of data returned in addition to the granularity and layout of the data.
Time series allows you to select if you would like monthly, quarterly, or annual data. GATS offers four time series options:
· Annual Series – The time period is one market year.
· Monthly Series – The time period is one month.
· Quarterly Series – The time period is three month increments within the market year.
· Two Year Series – GATS returns data for the market year selected and the prior market year, in addition to the last month of the market year selected and the last month of the market year prior to the market year selected.
Market Year allows you to select the start and end month of the market year.
Year Range allows you to select the starting year and ending year of data that you would like to be returned from the system.
Query End Month – select the month of the last market year for which you would like data.
10. Settings – The settings area provides further customization to the data you wish to retrieve through up to six different options
Precedence allows you to group your results in any permutation of reporter (when available), partner, or product.
Include – you may opt to include all results, or only the top x results, where x is from 5 to 50 in increments of 5.
Order By – Sorts your results with respect to either the rank in the returned data, the commodity code, or the description.
In Detail allows you to return either just the summary level information you requested, or will optionally return the next level of detail for one of either reporter, partner, or product. For instance, if you select a product in detail, the query will automatically return the children product groupings one level beneath.
The Ag Only selection returns only results relating to agricultural products. This setting is hidden when the selected product group contains only agricultural products.
Calculation selection allows you to select one of six predefined statistical and arithmetic calculations to perform on the returned data. A detailed discussion about the calculation methodologies is available by clicking the calculation formulas link on any result set including one of the calculations. The available options are: Average, Average Period Growth, Compound Period Growth, Period Growth, Period on Period Growth, and Total by Row. To select multiple calculations at the same time, you must use the Advanced Query interface.
11. User Saved Selections – The user saved selections area facilitates rapid data retrieval for queries that are frequently needed. You must be logged in with your eAuth account to see and use this interface.
With this interface, you may save a frequently used query, run an existing query, load an existing query for further refinement, or delete a query that is no longer needed. You may also flag a query to run with the most recent data. By doing so, you will override your end date range with the end date of the most current trade data stored in GATS.
Exercise 1. What is the dollar value of all shrimp imported to the U.S. in 2006?
A. Open Internet Explorer and navigate to the GATS staging site at http://www.fas.usda.gov/gats/
B. Select Analytics >> Data Analytics >> Standard Query from the top menu.
C. Follow these steps to find the answer:
1. Leave the default selection of “FAS U.S. Trade” as the Data Source
2. Select “Imports – Consumption” from the Product Type
3. Leave the default selection of “BICO (HS-10)” for the Product Group
4. For the Partner selection, leave the default “World Total”
5. To select shrimp, scroll down on the Product Selection area and click the “Shrimp” listing under “Seafood Products”. Speed tip – you may also click anywhere inside the product selection area and begin typing “s-h-r” and you should automatically jump to the shrimp listing. Note that this will not work on product selection listings that have an indented hierarchy.
6. In the statistics area, leave “Value” selected, but change units to “Dollars”
7. For the date selection, the default “Annual” series should be selected. The market year start and end month settings should also remain with Jan – Dec values. Change the year range to 2007-2007. Leave the end month set to “Dec”.
8. Leave the settings area as its default (the default calculation will be cleared automatically as it is invalid with a one year date range).
9. Click “Retrieve Data”
When finished, your screen should look like this:
Users who login with a valid eAuth account are provided additional enhancements over the publicly available GATS features.
After selecting the Login link from the User Settings Menu, the user is presented with the standard eAuth login screens:
You must click I Agree on this menu to continue.
Please enter a valid eAuthentication usernames and password combination.
After logging in, you are returned to GATS with a login successful message:
You are now logged in to GATS.
Now that you have logged in, you may save any of your Standard Queries (and other queries) for future use so that you do not have to manually reselect all of the criteria each time.
After making your selections, you may enter a name for the selection in the text box (to the left of the save button) and then click the save button to save the query.
In the future, you may select the saved query from the dropdown list and either load, run, or delete it.
A. Open Internet Explorer and navigate to the GATS staging site at http://www.fas.usda.gov/gats/
B. Select Analytics >> Data Analytics >> Standard Query from the top menu.
C. Follow these steps to find the answer:
1. Leave the default selection of “FAS U.S. Trade” as the Data Source.
2. Select “Exports” from the Product Type.
3. Change the Product Group to Harmonized (HS-4).
4. Select Canada from the Partners Selection area.
5. In the Product Selection Area (top pane), select Chapter 22, Beverages, Spirits, and Vinegar and then click the “List Selected Chapters” button.
6. In the bottom pane of the Product Selection Area, select 2207 – Ethyl Alcohol
7. For the date selection, select Quarterly series. The market year start and end month settings should remain with Jan – Dec values. Change the year range to 2007-2009. Leave the end month set to “Apr”.
8. Enter a name for the Saved Selection and click Save.
9. Now navigate away from the Standard Query page by clicking on the “GATS Home” link in the main navigation area.
10. Now return to the Standard Query page. In the User Saved Selections selection box, select the name that you saved the query under.
11. Finally, click the Run button. Your query will be displayed similar to the following
You may make revisions and re-run the query at any time. If
you would like to save your updates, click the Save button. The query will
always return the date ranges you have selected, unless you click the “Display
most recent data” checkbox. This will load the most current data available
when the query is run in the future.
Configuring User Preferences
Note: You must have completed the preceding steps for logging into GATS and the creation of a saved selection to perform these options.
The user preferences page allows you to customize GATS to work in the manner most suitable to your needs.
From this screen, you are able to select a default query for each page of the GATS application and select if you would like that query to automatically execute when the page is loaded.
The Default Criteria Type allows you to specify if GATS should load one of your Saved Selections, a Manual query (that is entered on this screen), or none. The saved selections option will load a saved selection that has been already created. The manual query utilizes the common query interface used throughout the rest of the GATS application which will be discussed later in this document.
The Select Page selection box specifies which page you would like to configure.
The Start Saved Selection selection box allows you to specify which Saved Selection you would like loaded. It is disabled by default and only enabled when Saved Selections is the chosen Default Criteria Type.
Auto-Execute radio buttons indicate whether the criteria should be loaded and immediately run, or whether the criteria should be only loaded so that you may make further refinements before running the query.
Exercise 3: Configuring the Standard Query to automatically execute the saved ethanol imports query.
A. Open Internet Explorer and navigate to the GATS staging site at http://www.fas.usda.gov/gats/
B. Login to GATS
C. Select User Settings >> Preferences from the top menu.
D. Follow these steps to automatically run the saved selection when you navigate to the Standard Query Page:
1. Select the “Saved Selections” Default Criteria Type.
2. Select the “Standard Query” page from the “Select Page” selection box.
3. In the “Start Saved Selection” selection box, select the query that you created in the prior exercise.
4. Click the “Display Most Recent Data” checkbox to override the date selections saved in your query with the most recent data available.
5. Set the Auto-Execute feature on by clicking the “On” radio button.
6. Click the Save button to save your preferences.
7. Return to the Standard Query screen by navigating to Analytics >> Data Analytics >> Standard Query. Note that the Saved Selection is automatically executed and the data is displayed at the bottom of the screen.
The Access Saved Queries feature of GATS allows you to submit a query asynchronously and return later to retrieve the data is CSV format.
To process data asynchronously, you may either select an existing Saved Selection, or manually select new query criteria via the advanced interface on the left. The advanced query interface will be discussed in detail in a later chapter.
You may select the option to send email notification when your data has been processed if you so desire. You must also select either “Standard Selection” or “Saved Selection” to indicate whether you would like to run a saved selection, or if you will select your criteria from the left. Finally, selecting “Most Recent Data” returns the most recently loaded data regardless of the query end range defined in the Saved Selection.
Exercise 4: Retrieving the Ethanol Saved Selections via Access Saved Selections
A. Open Internet Explorer and navigate to the GATS staging site at http://www.fas.usda.gov/gats/
B. Login to GATS
C. Select Analytics >> Data Analytics >> Access Saved Queries from the top menu.
D. Follow these steps to submit your job for execution and retrieve the results.
1. Select the Saved Selection you created in the earlier exercise.
2. Select the “Saved Selection” checkbox to indicate that you are running a saved selection.
3. Select the “Most Recent Data” checkbox to have the system return the most current data
4. Click the “Submit Requests” button to submit the job for processing. Your job will be submitted for processing.
5. Click the “Refresh/Check Request Status” button until the status of your job says “Completed”.
6. Now click the “Download” button to retrieve the data, which should automatically open in Microsoft Excel.
Advanced Query
The Advanced Query interface provides an alternative interface to retrieve the same data as the standard query, with some additional features. The Advanced Query interface is the base interface for the majority of the GATS application, including all the visual analytics features and the executive summaries features.
The Advanced Query interface takes all the existing areas described in the Standard Query interface of this document and reorganizes them into tabs along the left side of the screen. There are up to seven tabs available:
1. Current User Selections
2. Settings
3. Reporters/Customs Districts
4. Dates
5. Partners
6. Products
7. User Saved Selections
It should be noted
that there are differences in the capabilities of this tab versus the
corresponding areas of the Standard Query interface. First, where the Standard
Query provides the ability to select a single calculation, multiple
calculations can be selected from this interface. Second, this interface
offers an additional calculation option, “Highlight Range Variance” that allows
you to set a threshold level and that the system will use to indicate when the
data exceeds this threshold by color coding the data in either green (when the
value exceeds the threshold in a positive direction) or red (when the data
exceeds the threshold in the negative direction).
