In Progress

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;


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

Skills: Visual Basic, Excel VBA, Excel Macros

See more: extract website export csv, retail pro extract data export, mysql extract sql export import, fccs export data, file transfer activity marketing cloud, import activity marketing cloud, tracking extract marketing cloud, know used 150 300 words writing assigment, automatically cut words textarea, automatically change words word, world export input com, extract world warcraft armory, used iphone in uae online, what is microsoft access used for in business, most used fonts in logo design, what are graphs used for in science, what is php used for in web development, what is php used for in web design, used furniture in carmel indiana, export ssrs report to excel automatically

About the Employer:
( 4 reviews ) Huntingdon Valley, United States

Project ID: #25641740

Awarded to:


NOTE : I HAVE EXPERTISE IN VB/VBA AND .NET FRAMEWORK. With respect to this project I would like to present myself as a candidate for your consideration. I have more than 12 years of IT experience. I have successfully More

$125 USD in 5 days
(3 Reviews)

13 freelancers are bidding on average $166 for this job


Hi, Would you like to see a quick sample of your extracted & refined export list from Comtrade before you award the project? You can consider it professionally done. I can continue telling you about how highly experie More

$150 USD in 3 days
(24 Reviews)

Greetings from Easton PA, USA.. {smile} Highly experienced (25+ years) MsACCESS / MsEXCEL / MsWORD / SQL / VBA / VB.Net Business Information, Programming, and Database EXPERT with GURU / MENTOR level technical credent More

$504 USD in 7 days
(29 Reviews)

Hello, I’ve carefully gone through your job posting “Extract World Export DataExtract”. I'm an expert in Advanced Excel VBA programming techniques. I am very much interested in your project with all of your requireme More

$140 USD in 7 days
(36 Reviews)

Hi there, I have read your work scope and I can provide the data following your work flow process. I understand you want the 2018 data for all the chemicals listed in [login to view URL] downloaded and tables created an More

$200 USD in 5 days
(9 Reviews)

Hello, I am an independent, experienced excel / vba expert. I can help with this task with a quick turn-around. Looking to hearing from you. Kind regards Anis D.

$100 USD in 7 days
(15 Reviews)

Hello, I am excellent Mathematician. I can work full time in your time zone and I am sure that I can satisfy your requirements with my skill and experience. I've read your description carefully and I am interested in y More

$140 USD in 2 days
(6 Reviews)

Greeting! My name is Diwakar Garg and I am a software engineer (MCA). I have 10 Yrs. Exp. in MS Excel VBA Macro & Access (Macro & SQL) which is used for automation. I would love to have the opportunity to discuss your More

$30 USD in 7 days
(1 Review)

Hi dear sir/madam, I'm very active and interested to work with you. My time is up to you. I have many experiences in this case and you could award your project to me and enjoy it. With best regards

$230 USD in 1 day
(2 Reviews)

I assure you that I am fully able to perform this task .As I have been working in this field for a long time in offline mode also. Therefore sir please assign this task to me and I would be grateful to you.

$35 USD in 1 day
(0 Reviews)

Hello I am Aliz from Romania. I am a computer teacher with good English so I can handle your job well.

$140 USD in 7 days
(0 Reviews)

Hi Sir/Ma'am, Hope you are doing good. i am having around 11 years of IT experience which Includes in the area of VB Programming, ,Advanced VBA ,Database development,Design,Maintenance,Management along with worked on More

$225 USD in 7 days
(0 Reviews)

Hello!!! I have 10 years of experience in data warehousing and have been involved in various data analysis tasks. I am also skilled and have 8 years of experience in excel VBA and macros. I can do this job. Please rec More

$140 USD in 7 days
(0 Reviews)