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

Habilidades: Visual Basic, Excel VBA, Excel Macros

Ver más: 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

Información del empleador:
( 4 comentarios ) Huntingdon Valley, United States

Nº del proyecto: #25641740

12 freelancers están ofertando el promedio de $166 para este trabajo


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 Más

$504 USD en 7 días
(29 comentarios)

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 Más

$150 USD en 3 días
(24 comentarios)

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 Más

$140 USD en 7 días
(36 comentarios)

Hi. I have written many automation Excel VBA macros. Ready to write your project. Will complete within 2 days

$90 USD en 2 días
(28 comentarios)

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 Más

$200 USD en 5 días
(9 comentarios)

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 en 7 días
(15 comentarios)

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 Más

$140 USD en 2 días
(6 comentarios)

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 Más

$125 USD en 5 días
(2 comentarios)

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 en 1 día
(0 comentarios)

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

$140 USD en 7 días
(0 comentarios)

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 Más

$225 USD en 7 días
(0 comentarios)

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 Más

$140 USD en 7 días
(0 comentarios)