Progress 09/16/18 to 09/15/20
Outputs
Target Audience:The database concept and some preliminary output was shared on the CINTRAFOR website. We have fielded several inquiries about the database from, for example, government (U.S. Forest Service), trade associations (SEC), consultants (FEA), and industry (Weyerhaeuser). Access has been limited to CINTRAFOR personnel. This included Dr. Indroneil Ganguly, Francesca Pierobon, Kunlin Song, Cindy Chen, Fei Liu, Yingkang Liu, Olivia Jacobs, Alec Solemslie, Micah Stanovsky, and Hema Velappan. Changes/Problems: As noted earlier, initial testing uncovered some complications with Microsoft Power BI version of the database and a decision was made to transition to Tableau. A developer was hired to do the migration but had to back out for personal reasons. We were able to work out an arrangement with the original programmer (Sikka), who completed the migration and started several other functions. Data from the Global Trade Atlas (GTA) is a key component of the database. We get access to GTA through our membership in the Softwood Export Council, who get access from the USDA. In February 2020, without warning or prior consultation, USDA discontinued their contract with GTA and switched to a new supplier, Trade Data Monitor (TDM). This has been a huge and unexpected headache for us. We have found many, many differences in the data available from TDM which has led us to conclude that we cannot simply begin TDM where GTA left off because time series relying on a combination of both will be inaccurate. We are consequently going through all comparable TDA data to determine in when the data is the same, and when it is not, and in cases of inconsistent data, obtaining and loading TDM data back to 2000 to be consistent with the database organization and operation. What opportunities for training and professional development has the project provided?Although the database has great potential as an analytical tool when sufficiently tested enough to go public, there has already been substantial value as a training tool regarding international trade analysis with CINTRAFOR graduate students. Access was shared with CINTRAFOR personnel and graduate students for use with their research (see "Other Products"). We have held several training sessions, with both the Power BI and Tableau versions. The database has been a great means for them to perform hands-on and unique ways of looking at trade data. How have the results been disseminated to communities of interest?A dedicated page was created explaining the database project, how it will function, why it is valuable, and that it is funded by the McIntire-Stennis program. The web page explains that the database is not yet ready for public access, but the website features one example of the type of analysis that can be performed. Examples will be changed bimonthly. Research accomplished with the help of the database has been published (see "Products"). Although not requested, we would like to report about our next steps and the continuation of this project. The database is promising and everyone who has worked with it wants us to continue with development. Outside parties (USFS, SEC, FEA) are encouraging us to complete the project and make it available to the public, and some have hinted that they may be able to help fund the additional work needed. We are exploring opportunities for funding to continue the work. Our plan is to: Complete USA Trade data ingestion. Add StatCan via their API. Add Trade Data Monitor via their API. Resolve problems with the "Reported vs Observed" function. Move to a self-hosted Tableau to facilitate wider access. We estimate an additional $8,000 in programming expense plus faculty and student time to test the resulting changes. What do you plan to do during the next reporting period to accomplish the goals?
Nothing Reported
Impacts
What was accomplished under these goals?
The database was begun using Microsoft Power BI software, combining two sources of data, Global Trade Atlas and FAO STAT. After initial testing, access to FAO STAT data was converted to API automatic updates, eliminating the need for manual updates. Following successful testing, COMTRADE data was added through their API interface, and the combination of all three was tested. API stands for Application Programming Interface and is explained in this short video:https://www.youtube.com/watch?v=s7wmiS2mSXY. A function was added to manually record and document known or suspected data errors, including explanations of the suspicious data and how the replacement was calculated. As errors are discovered in the course of trade analysis projects, they can be entered in the database, thus reducing the need to constantly correct data obtained from original sources. Testing discovered small differences in country names across the three databases. A table ("Country Relations") was created to consolidate all forms of names for the same country, thereby reducing inconsistencies in analysis across databases. A regional analysis function was created to analyze trade by region net of interregional trade; this function works is the most popular in the database and has been particularly valuable analyzing trade with the European Union (which has an oversized impact on international trade data if not adjusted for inter-regional trade) and the Caribbean (which has an undersized impact on international trade data if not considered as a combined regional market). A function "Source Comparison" was built to compare sources of data (FAO, GTA, and COMTRADE) for inconsistencies. Testing found that sources using the same HS codes were being combined, distorting trade patterns. This error has been flagged for correction but will require further programming. A function "Reported vs. Observed" was built to compare what importing country A reported in volume and value of imports from exporting country B with what exporting country B reported in volume and value of exports to importing country A. Testing proved this function to be popular but complicated to use. It has been flagged for further work to improve the user interface. After extensive testing, we uncovered some complications with Microsoft Power BI. It merged data as expected, but was limited in our ability to manipulate, display, and visualize results. We determined to migrate the database from Power BI to Tableau, and this was the main focus of our work in the second year of the project. It took much longer than anticipated but ended with a successful result. CINTRAFOR researchers testing the Tableau database all commented on the improved user access and functionality. We began the addition of the USATrade Online API but have not yet completed the work.
Publications
- Type:
Journal Articles
Status:
Published
Year Published:
2020
Citation:
Liu, F., Wheiler, K., Ganguly, I. and Hu, M. (2020), Sustainable Timber Trade: A Study on Discrepancies in Chinese Logs and Lumber Trade Statistics, Forests 2020, 11, 205; doi:10.3390/f11020205, www.mdpi.com/journal/forests.
- Type:
Websites
Status:
Published
Year Published:
2020
Citation:
Velappan, H. and Ganguly, I. (2020), Increasing Demand for Wood Pellets in Japan and South Korea, Center for International Trade in Forest Products (CINTRAFOR), https://drive.google.com/file/d/1_41QY7ZF4NyB9dzw0PJxSeAnYXXn_pKo/view.
|
Progress 10/01/18 to 09/30/19
Outputs
Target Audience:The database has been under development for the period with limited exposure beyond the participants. Access was shared with CINTRAFOR personnel, they were given a demonstration and tutorial, and encouraged to test the database. This included Dr. Indroneil Ganguly, Francesca Pierobon, Kunlin Song, Cindy Chen, Fei Liu, Yingkang Liu, and Olivia Jacobs. Changes/Problems:A. Testing uncovered some complications with Microsoft Power BI. The program merges databases and processes as expected, but has some limitations in our ability to manipulate, display, and visualize the resulting analysis. There are also some cost and access issues that may inhibit public access to the database upon completion of the project. It was determined that a shift from Power BI to Tableau is advisable, estimated to require 80-100 hours of programming time. The transition will occur in the second year of the project. B. The developers involved in year one (Sikka and Mehra) graduated from U.W. and accepted jobs outside of Seattle. With their help, a search was undertaken in July 2019 for a replacement developer, candidates were screened and interviewed. A qualified developer was selected and began working on the transition to Tableau. Unfortunately, in mid-September the new developer encountered some personal problems and had to terminate their employment. As the reporting period came to a close, we were considering options for the transition. In the meantime, testing of the existing database in Power BI will continue. What opportunities for training and professional development has the project provided?As noted above, access was shared with CINTRAFOR personnel, they were given a demonstration and training, and encouraged to test the database. This included Dr. Indroneil Ganguly, Francesca Pierobon, Kunlin Song, Cindy Chen, Fei Liu, Yingkang Liu, and Olivia Jacobs. How have the results been disseminated to communities of interest?One of the planned activities for the year was to begin sharing analytical findings via the new CINTRAFOR website that is under development. A dedicated page was created explaining the database project, how it will function, why it is valuable, and that it is funded by the McIntire-Stennis program. The web page explains that the database is not yet ready for public access, but the website features one example of the type of analysis that can be performed. Examples will be changed bimonthly. What do you plan to do during the next reporting period to accomplish the goals?We have four main priorities for the next reporting period: A. Transition from Power BI to Tableau (see 9A below). B. Add USA Trade data through their API. (We also trying to access GTA's API but this may be cost prohibitive.) C. We have begun using the database for an analysis of trade discrepancies and the potential to signal illegal timber and related products trade, which we hope to publish within the next reporting period. D. Complete testing and begin rollout for public access.
Impacts
What was accomplished under these goals?
A. As planned, extensive testing was performed with the two initial sources of data (Global Trade Atlas and FAO STAT) for six months. FAO STAT data was converted to API automatic updates. Following successful testing, COMTRADE data was added through their API interface, and the combination of all three was tested. Addition of the USA Trade API was underway at the end of the period. Access to the GTA API requires an upgrade to the subscription which we are negotiating. FYI: API stands for Application Programming Interface and is explained in this short video: https://www.youtube.com/watch?v=s7wmiS2mSXY B. A function was added to manually record and document known or suspected data errors, including explanations of the suspicious data and how the replacement was calculated. Initial testing indicates satisfactory performance. Manual entry is underway as errors are discovered in the course of trade analysis projects. We have tentatively planned for development of a subprogram within the database to automatically search for, correct, and document errors within defined parameters, but want to get more experience using the manual system in order to establish a pattern of discernable errors. C. Testing discovered small differences in country names across the three databases. A table ("Country Relations") was created to consolidate all forms of names for the same country, thereby reducing inconsistencies in analysis across databases. D. Three powerful analytical tools were developed and tested during the period: a. A regional analysis function was created to analyze trade by region net of interregional trade; this function works well and has been particularly valuable analyzing trade with the European Union (which has an oversized impact on international trade data if not adjusted for inter-regional trade) and the Caribbean (which has an undersized impact on international trade data if not considered as a combined regional market). b. A function "Source Comparison" was built to compare sources of data (FAO, GTA, and COMTRADE) for inconsistencies. c. A function "Reported vs. Observed" was built to compare what importing country A reported in volume and value of imports from exporting country B with what exporting country B reported in volume and value of exports to importing country A.
Publications
|
Progress 09/16/18 to 09/30/18
Outputs
Target Audience:Reporting period was only two weeks and involved only the PI. Changes/Problems:
Nothing Reported
What opportunities for training and professional development has the project provided?
Nothing Reported
How have the results been disseminated to communities of interest?
Nothing Reported
What do you plan to do during the next reporting period to accomplish the goals?1. Create the database prototype using data from FAO (auto download) and GTA (manual download). 2. Begin testing with two sources of data for at least 6 months, then add two more sources of API available data, most likely USA Trade and Comtrade but to be determined. 3. Begin manually checking for errors that significantly distort reported trade patterns and use that experience to learn how to program PowerBI to automatically search for, correct, and document errors within defined parameters. 4. Begin developing analytical tools, particularly (1) the ability to analyze trade by region net of interregional trade; (2) instantaneously analyze changes in trade between for selected parties, products, and time periods; (3) compare sources of data for inconsistencies; and (4) compare bilateral trade reports for inconsistencies allowing for transit and reporting time lag. 5. Begin sharing analytical findings via the CINTRAFOR website.
Impacts
What was accomplished under these goals?
PI devoted three days to preparation of the database prototype using Microsoft Power BI populated with two data sources: (1) the Food and Agricultural Organization (FAO) database FAOSTAT, for which API automatic downloads and updates are possible (see below for more explanation about API), and (2) annual trade data from the Global Trade Atlas (GTA) which must initially be manually downloaded for the planned period (2000-2017). According to GTA website, they are developing an API interface (not yet available) that will enable automatic updates but covering only the most recent five year period. For the initial database development, we must manually download the 18 year period data using GTA's Bespoke Matrix function. API stands for Application Programming Interface and is explained in this short video: https://www.youtube.com/watch?v=s7wmiS2mSXY
Publications
|