Question

Using Excel to Construct event dummy (or binary) variables to represent three international events :

(i) The Asian financial crisis – one from Aug 1997 to Jul 1998, zero otherwise.

(ii) The Global Financial Crisis (GFC) – one from Feb 2007 to Feb 2009, zero otherwise.

(iii) Covid-19– one from Jan 2020 to Mar 2020, zero otherwise.

Answer 1

Assumption:

Variable for the Asian financial crisis – A

Variable for the Global financial crisis - G

Variable for Covid 19 - C

There can be three such variables that can be used for the three different events. We will construct them here in two ways using excel.

Method 1: Using Data validation on the question - Did the event "Event Name" happen/impact the variable?

If Yes - Variable set to 1

Else Variable Set to 0

Method 2: Using Formulae: Three variables for the events will be set to 1 if the year lies between the range specified (if this is confusing, it will be explained further), else the variable will be set to 0.

Implementation: Method 1 -

Data validation for each event has been entered as per the screenshot of excel:

The formula for variables A, G, C will set the variable to 1 respectively if there is an impact of Asian Crisis/ Global Crisis or Covid 19 and set the variable to 0 otherwise as shown below:

Formula used in G5, G6 and G7 is given here. It is a nested if which sets variable to 1 if yes is populated for corresponding variable, 0 if no is populated for the corresponding variable and leaves it blank otherwise-

---------

Method 2: Dummy based on year of occurance.

This will assign each variable as 0,1 based on the date specified. If the date falls  in the duration in which a particular event occured, that variable will be set to 1. For example, if the date is 1/Jan/2020, A and G will be set to 0 and C will be set to 1.

The representation is as shown-

The formulae used for the "if" computes- if the date falls between the start and end dates specified in columns H and I (see above image) and set the values to 0 and 1 as given below.

These dummies can be further used for any form of analysis.