Data Manipulation Program Creation

I have a series of data in Excel that needs to be translated and manipulated into a series of six output files. The data measures two things: country membership in international organizations and country membership in treaties. Manipulation is detailed below. I want the final output to be a program that I can re-run on my own computer using slightly different input data. I need this by the end of the week. ## Deliverables See attached data files. The guide file is called Templates for final data (1).xlsx. It contains a description of six templates. Here I outline how to create those six templates. Template 1: MN Scores by Country-Year To populate Column A, go to the Country TranslationDiplometricscsv3.0 and choose the countries in Column F titled IFs Countries (first column in country translation table). Column B will be years from 1945 to 2010. Each country will have a corresponding year (so, there will be 65 entries for "Afghanistan" corresponding to each year). Go now to Column F, titled Org 1. The first org listed is found in Column A of MetatableUpdated 9 21 2012 titled "Code". The value of F2 would be UN. The value of F3 would be EU. If the value of A4 is Afghanistan and the value of B4 is 1945 the value of F4 is taken using the following logic: IF Afghanistan is a member of the UN in 1945 (this is found by opening the IGO folder attached, finding [login to view URL], going to Longitudinal worksheet and looking up Afghanistan for 1945. It shows that they are not a member in 1945 but are a member in 1946). If Afghanistan is not a member of the group listed in F1, it receives a "null" value (the cell is left blank). If Afghanistan is a member of the Org in F1 in that given year (for example, it is a member in 1946), then it receives a value taken from IGO Weights Updated 09 21 2012. The weight that Afghanistan receives for being in the UN in 1946 is 40.98. One additional detail: Countries can be partial members of organizations. For example, Bangladesh is a partial member of the UN in 1973. They should receive half the weight score for the UN for that year. The value would be 63.37 divided by 2. This same logic applies for all 402 organizations found in the IGO folder and for all countries in the noted translation table above from 1945 to 2010. After this is completed, column E can be filled by summing the weights in the IGO Weights Updated 09 21 2012 file for the year in question (for example, if it is 1946 for a given country, the sum would be 95.47. Next, Column D can be filled by summing the Org 1...Org 402 scores for the given country in the given year. Column C is then Column D divided by Column E. Template 2: Take each IGO in Column A of Metatable and place them in Column A of Template 2. Again, see the Templates for final data file. Next, add the year in column B for each year from 1945 to 2010. Across the top, take each country in the IFs Country List column from the Country Translation file. There shold be 183 countries. To fill in this table, identify if a country is a member of the given IGO for that year. If they are a member, take the weight of that IGO from the IGO Weights Updated file and give the country in question that score. For example, the World Bank in 2010 should have a value of 41.98 for any country that is a member in that year. Template 3: Column A is each IGO. Column B is each year. Column C is the weight score for that IGO in that given year. Template 4: This is the exact same as Template 1, except with different source data. Column A and B are the same. Column F begins with the treaty names found in Treaty Weights 09 24 2012. There are 480 treaties, so F would be Treaty 1 and would extend to Treaty 480. To find out if a country is a member of a given treaty, open the UNTC Dataset. Search for the treaty in a worksheet. If a country has signed the treaty they have a score of 1. If they have ratified the treaty it is a score of 2. Take the Charter of the United Nations. Argentina has ratified the treaty in 1945. They are thus given the weighted score for that treaty in Treaty Weights 09 24 2012 for 1945. If Argentina had only signed the treaty (and had a value of 1 not 2 then it would receive half of the weights. BE CAREFUL! In the IGO membership database, a code of 2 indicates partial membership and a 1 full membership. In the treaty coding it is OPPOSITE. Fill in scores for treaty 1 through 480 for each country using this logic. Next, in Column E add up the total weights for all treaties for that given year. In Column D add up the total treaty signing for each year for each country. In Column C, divide D by E. Template 5: Exactly the same as Template 2, but replace IGOs with Treaties. Template 6: Exactly the same as Template 3, but with Treaties.