Extract World Export DataExtract

Instructions for Extracting & Refining Exports from Comtrade Steps to be taken described below: 1) Comtrade contains export and import data and its link is [login to view URL] 2) Commodity code is provided in [login to view URL], in this file MKTC is the market code in ChemPlan, MKTN is the market name in ChemPlan, Code is Commodity Code in Comtrade, Unit_Value, Avg and Note will be explained later. 3) To extract the export data from Comtrade: under Data Query select Express Selection, for Commodity Code select one or more Codes from [login to view URL] file; pick all for both Reporter and Partner Codes; select 2018 for Year; pick Export for Trade Flow and submit; for few number of records a file will be downloaded; for a large number of records, there will be no download; in either case select direct download to download the data in csv (Excel like) format with name Comtrade_Trade_Data**.csv. 4) Important fields in the Comtrade_Trade_Data**.csv file are: Reporter Code=Code for Exporting Country; Partner Code=Code for Importing Country; Commodity Code; Supplementary Quantity=the same as Netweight=Weight of the Shipment in Kg; Value=Value of the Shipment in $. 5) Now create 6 more columns in item (4) as: Exporting Country; Region; Importing Country; Region; Commodity Name; Unit_Value. Get Exporting Country, Importing Country, both Region values from Country vs Region file. Get Commodity Name from [login to view URL] shown as MKTN value. 6) Filter all the records with Partner Code=0; Delete these records; Unit_Value is Value/Netweight; Now sort Unit_Value small to large. There might be some records division by zero. For these records, replace Netweight with Supplementary Quantity. If Supplementary Quantity is zero, replace Netweight with Value/Avg in [login to view URL] file. Avg is the average value of Unit_Value between lower and upper limits. Now filter Unit_Value Between lower limit and upper limit in [login to view URL] file. For all the commodities, Lower Limit <=Unit_Value<=Upper Limit. After all the changes, this file becomes a file like Polyacetal Exports.xlsx. Note: Reduce the decimals to two for unit_value. 7) At this point a master export file should be created that has all the codes and descriptions. This file should be used to extract export data for all commodities automatically. In other words, by providing the commodity code and Lower and Upper limits, the export file should be created. These export file (or files) should be provided to us for review and corrections. 8) After our two days review, the Unit_Value should be calculated for regions comprising: Reg 1=LAT+CAN+USA+MEX; Reg 2=AFR+AME+WEU+EE; Reg 3=APC+AN+JAP 9) Now divide the Netweight by 10^6 so the numbers become Kt (kilo ton). The Netweight should be imported to the files in [login to view URL] after unzipping this file to a file like PA81_sales_flow_polyacetal18. These are the rules for importation: a) There is a single company in a single country in A81_sales_flow_polyacetal18 file - all the exports are allocated to this company and the exports for the company’s region will be equal to out – exports to all other regions. b) There are several companies in one country: the export value is allocated to different companies based on capacity and again the exports to the same region will be dealt with like (a) c) There are several countries in one region: the regional exports are allocated based on the capacity of the company. And once again, the exports to the same region will be dealt with like (a) Note: There is a Note column in Export-Import.doc. When one commodity code refers to several MKTNs, total exports should be allocated according to values in Note column. For example, Butyl Acrylate is 0.645 of the export. And for Butene-1, export should be multiplied by 0.558. Skills Required Visual Basic Excel VBA Excel Macros