Excel Macro

Original: 2005.12.15

Last Update: 2016.10.14

Excel Macro for Data Preparation

As I described in A Note on Rural Household Survey, running regressions in econometrics software is NOT necessarily the most difficult part of the empirical analyses. In many cases, to design a field survey or to clean up inconsistent data entries are much more difficult and tedious than hitting keyboards to run regressions in a package software. Other examples of time-consuming work are: 1) to arrange the original data entries properly for running regressions in a package software, 2) to calculate summary statistics from the original data files (nowadays, most package software do this 2) properly).

Excel macro (VBA) can be of great help in tackling such tedious works. It is not only time saving, but also free from mistakes. Please find below the examples what I have learnt through my “try and MANY errors”. Most of the examples are originally based on

Watanabe, Hikaru (2003), Excel VBA: Practical Examples [EXCEL VBA: Jitsuyou Sample Collection], Tokyo: Softbank Publishing Company.

The Internet Explorer may block the download of the example files below. In such cases, please click the “approve” at the message just below the tool bar.

1) PanelID_161014.txt (code in a text file)

PanelID_161014.xlsm (example: Excel 2013 file)

Listed on October 14, 2016

This macro was developed for preparing a data for panel analyses. More specifically, from an ID for one year data, this panel makes paned IDs. Suppose we have a column showing ID of our sample in a year

' firm1

' firm 2

' firm3

This macro makes (if two-periods data)

'firm1

'firm2

'firm3

In other words, this macro inserts a row and copy the content of the original row into a new row.

Before initiating this macro, you need to choose the first row indicating the name (label) of the variable (column: in my case this is always row 12).

2) countHHmembers_141012.txt (code: in a text file), exampleCountHHmembers_141012.xlsm (Excel 2013 file)

Listed on November 12, 2014 (revised on:)

This macro counts the number of HH members (or employees, etc.) in each household (firm, etc). The household members should be indexed by an ID. In the example excel file, in the sheet “base”, there are 477 individuals (indexed by HH ID) over the 112 households. For example,

HH ID1

HH ID2

HH ID3

This macro produces

HH ID1 3

HH ID2 1

HH ID3 4

In the sheet “number”.

To try this macro, a) go to ‘Base’ sheet (sheet1), b) in either column B or C, from row 12, Shift + Ctrl + Down. You can cover the all the entries in that column (this is to count the number of observations), c) go to Tool Bar, c) Choose “Macro” then run, 4) you will find the result in the sheet “number”.

3) sum-up-090605.txt (code: in text file), example-sumup-090605.xls (Excel 2003 file)

Listed on June 5, 2009 (revised on:)

This is a macro for summing up plural entries (harvest, sales) for each counting unit (respondent, household, etc). In the current example, there were 58 respondents (farmers) but numbered from 1 to 114. Each farmer sold their production (rice) for several times (max 8 times), and sales were recorded in each time. We would like to sum up the sales of each farmer.

To try this macro, a) go to ‘Base’ sheet (sheet1), b) go to Tool Bar, c) Choose “Macro” then run, 4) you will find the result in sheet “Preparatory”.

4) Countpanel-051113.txt (code: in text file), Example-countpanel-051113.zip (Zipped Excel 2003 file)

Listed on December 15, 2005 (revised on July 10, 2007)

This is a macro for counting time-series observations of a unbalanced panel (or number of each tree species counted in a forest). It can be utilized, for example, to pick up the panel units with more than 3 observations. To try this macro, a) in sheet1, choose the area from B2 to the end of the data. You can do it easily a) put cursor on cell B3 (Firm Index), b) while pushing “control”, push “shift” and “*”. c) go to Tool Bar, d) Choose “Macro” then run, 4) you will find the result in sheet “number”.

5) Numbering-051219.txt (code: in text file), Example-numbering-051219.xls (Excel 2003 file)

Listed on December 20, 2005

This is a macro for putting flag (ID Number) for the entries. I made this macro to put species ID for the trees measured in forest inventory. This macro assumes that you have already sorted the entries in ascending or descending order. To try this macro, a) in sheet 1, choose the area from C3 to the end of the data (the entry in C3 “p” is the title of the column. The entries are botanical names of trees in the data), b) go to Tool – Macro then run, c) you will find the result in Column 4.

6) Delete-080629.txt (code: in text file), Example-delete-080628.xls (Excel 2003 file)

Listed on June 28, 2008

This is a macro to delete a specific entry, e.g. na, from the selected area. This macro assumes that you have already selected the area from which you would like to delete a specific entry. To try this macro, a) When you open the file, you will be inquired whether to activate Macro. Please activate it, b) in sheet 1, choose the area, for example, from D13 to E360, c) go to Tool – Macro then run DelNA, d) you will find the input box asking the specific entry you would like to delete: “Please indicate the entry you would like to Delete.”, e) Enter “na”, then choose OK, f) you will see the result.

7) Extract-copy-080629.txt (code: in text file), Example-extractcopy-080628.xls (Excel 2003 file)

Listed on June 28, 2008

This macro makes a new file with the columns, which may be over several sheets, extracted from the original file. It automatically attaches appropriate name to the new file.

Suppose that you have a big data on labor inputs of rice farmers. Labor inputs and wage payments are separately listed for each type of workers: operator, hired workers, exchange workers, etc. The data is so big that it is listed over 2 sheets. You would like to have a new file which shows only the data on food provided to each type of workers. This macro is to make such a new file. You need to put “1” for row 2 of the columns you would like to extract. In Example-extractcopy-080628.xls, you will see “1”, for example AA2 of LaborI sheet.

To try this macro, a) When you open the file, you will be inquired whether to activate Macro. Please activate it, b) in sheet 1 (LaborI), choose the cell which contains the information you would like to use in new file. In the example file, it is cell D4 (ClearingUndergrowth: process of rice farming), c) go to Tool – Macro then run FoodCopy1, d) It takes some time. Please be patient, e) Yow will find a new file named ClearingUndergrowth2001-food-080421.xls in the same path as the original file.

8) Fillgap-081225.txt (code: in text file), Example-fillgap-081225.xls (Excel 2003 file)

Listed on December 25, 2008

This is a macro to fill gaps between the entries. To try this macro, a) When you open the file, you will be inquired whether to activate Macro. Please activate it, b) In sheet 1, choose the area (one time, one column), for example, from F6 to F1359, c) go to Tool – Macro then run fillblank1.