Measuring Illiquidity

[Sartaj Hussain]

Below is appended an example data set in which pr represents daily closing price of a stock and st is for daily number shares traded in a stock.

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock str10 month float pr long st
1 "3M India Ltd." "31-05-2002"  282.7    75
1 "3M India Ltd." "03-06-2002" 275.25   125
1 "3M India Ltd." "04-06-2002"  288.5  2020
1 "3M India Ltd." "05-06-2002"  282.6   270
1 "3M India Ltd." "06-06-2002"  284.9    20
1 "3M India Ltd." "07-06-2002" 276.05    52
1 "3M India Ltd." "10-06-2002"      .     .
1 "3M India Ltd." "11-06-2002"  287.9   715
1 "3M India Ltd." "12-06-2002"    285   420
1 "3M India Ltd." "13-06-2002"  278.7   350
1 "3M India Ltd." "14-06-2002"    283    50
1 "3M India Ltd." "17-06-2002"  287.9   165
1 "3M India Ltd." "18-06-2002" 294.95   911
1 "3M India Ltd." "19-06-2002"  294.5   190
1 "3M India Ltd." "20-06-2002"    294   372
1 "3M India Ltd." "21-06-2002"    284   137
1 "3M India Ltd." "24-06-2002" 290.05   650
1 "3M India Ltd." "25-06-2002"  289.1   200
1 "3M India Ltd." "26-06-2002"  282.5    50
1 "3M India Ltd." "27-06-2002"    290   110
1 "3M India Ltd." "28-06-2002" 285.15   440
1 "3M India Ltd." "01-07-2002"  287.2   500
1 "3M India Ltd." "02-07-2002" 285.05  3370
1 "3M India Ltd." "03-07-2002"    295  2595
1 "3M India Ltd." "04-07-2002"  324.5  9425
1 "3M India Ltd." "05-07-2002"  318.3  2368
1 "3M India Ltd." "08-07-2002"    318  1967
1 "3M India Ltd." "09-07-2002"  323.6   421
1 "3M India Ltd." "10-07-2002"  313.4   995
1 "3M India Ltd." "11-07-2002"    302   925
1 "3M India Ltd." "12-07-2002"  301.7  1790
1 "3M India Ltd." "15-07-2002"    295   180
1 "3M India Ltd." "16-07-2002"    300   685
1 "3M India Ltd." "17-07-2002"    292   320
1 "3M India Ltd." "18-07-2002" 295.85   200
1 "3M India Ltd." "19-07-2002"  303.5  1771
1 "3M India Ltd." "22-07-2002"    291   880
1 "3M India Ltd." "23-07-2002" 302.75  1859
1 "3M India Ltd." "24-07-2002" 299.45   980
1 "3M India Ltd." "25-07-2002"    301   535
1 "3M India Ltd." "26-07-2002"    300  2067
1 "3M India Ltd." "29-07-2002"  301.7  1167
1 "3M India Ltd." "30-07-2002"    300  1480
1 "3M India Ltd." "31-07-2002"    300  1943
1 "3M India Ltd." "01-08-2002"    300   351
1 "3M India Ltd." "02-08-2002"  302.9   276
1 "3M India Ltd." "05-08-2002" 303.75   121
1 "3M India Ltd." "06-08-2002"    304   310
1 "3M India Ltd." "07-08-2002"  306.8   276
1 "3M India Ltd." "08-08-2002"    300   140
1 "3M India Ltd." "09-08-2002" 300.75   350
1 "3M India Ltd." "12-08-2002"  300.4    30
1 "3M India Ltd." "13-08-2002"    300   312
1 "3M India Ltd." "14-08-2002"    300   650
1 "3M India Ltd." "16-08-2002"  304.1   100
1 "3M India Ltd." "19-08-2002"    300   248
1 "3M India Ltd." "20-08-2002"    303   115
1 "3M India Ltd." "21-08-2002" 301.95   499
1 "3M India Ltd." "22-08-2002"  302.1    50
1 "3M India Ltd." "23-08-2002"  303.4   375
1 "3M India Ltd." "26-08-2002"      .     .
1 "3M India Ltd." "27-08-2002"    305   590
1 "3M India Ltd." "28-08-2002" 305.25   175
1 "3M India Ltd." "29-08-2002"    305   100
1 "3M India Ltd." "30-08-2002"    316   595
1 "3M India Ltd." "02-09-2002"    315  1001
1 "3M India Ltd." "03-09-2002"  313.5    64
1 "3M India Ltd." "04-09-2002"    315  1165
1 "3M India Ltd." "05-09-2002"    315   347
1 "3M India Ltd." "06-09-2002"  310.4   239
1 "3M India Ltd." "09-09-2002"  306.5   115
1 "3M India Ltd." "11-09-2002"    310   632
1 "3M India Ltd." "12-09-2002"      .     .
1 "3M India Ltd." "13-09-2002"    310  1345
1 "3M India Ltd." "16-09-2002"    310 26000
1 "3M India Ltd." "17-09-2002" 328.35 26389
1 "3M India Ltd." "18-09-2002"    325   692
1 "3M India Ltd." "19-09-2002" 320.05   704
1 "3M India Ltd." "20-09-2002"  316.9   110
1 "3M India Ltd." "23-09-2002" 315.35   390
1 "3M India Ltd." "24-09-2002"  312.6   670
1 "3M India Ltd." "25-09-2002"  314.4   371
1 "3M India Ltd." "26-09-2002" 316.75   100
1 "3M India Ltd." "27-09-2002"  315.1   900
1 "3M India Ltd." "30-09-2002"    315   730
1 "3M India Ltd." "01-10-2002"    311    10
1 "3M India Ltd." "03-10-2002"    315   376
1 "3M India Ltd." "04-10-2002"    312   190
1 "3M India Ltd." "07-10-2002"      .     .
1 "3M India Ltd." "08-10-2002"  311.8   175
1 "3M India Ltd." "09-10-2002"  315.1    10
1 "3M India Ltd." "10-10-2002"    315   200
1 "3M India Ltd." "11-10-2002"    315   780
1 "3M India Ltd." "14-10-2002"    315    26
1 "3M India Ltd." "16-10-2002"    315   900
1 "3M India Ltd." "17-10-2002"    315   620
1 "3M India Ltd." "18-10-2002"    315   270
1 "3M India Ltd." "21-10-2002"    314   375
1 "3M India Ltd." "22-10-2002"    313  1260
1 "3M India Ltd." "23-10-2002"  313.2   205
end

From given data set, I want to generate Amihud’s measure of illiquidity which is the ratio of absolute value of daily returns divided by daily dollar volume traded in the security averaged over a period of month. Formulaically, it is defined as:

Where R_i,d is the return of stock i on day d measured as a decimal; VOLD_i,d is the dollar volume of stock i traded on day d, calculated as the closing price of the stock times the number of shares traded on the given date, measured in millions of dollars; and D is the number of the days in a month. Since, the distribution of illiq is highly skewed, for this reason; a log transformed version of illiq is generated.

The example dataset and explanation of the illiq measure follows the code for the given purpose. Before I may use it, I want to get it confirmed whether the given code is correctly specified as per above definitions.

* Change date to STATA format
gen date1=date(month,"DMY")
format date1 %td
drop month
rename date1 date
gen mdate= mofd(date)
format mdate %tm
* Calculate Returns, Volume and filter data
bysort stock_id (date) : gen rt =((pr[_n]-pr[_n-1])/pr[_n-1])
gen vol=st*pr
replace vol=. if vol==0
* Calculate Amihud's measure of illiquidity
egen validobs=count(rt), by(stock mdate)
bysort stock_id mdate: egen ret_div_vol = sum(abs(rt/vol))
bysort stock_id mdate: gen illiq = ret_div_vol*(1/validobs)*1000000
collapse(mean) illiq validobs, by(stock_id stock mdate)
replace illiq=. if validobs<15
drop validobs
gen illiq_ln=ln(1+illiq)

[Clyde Schechter]

Not quite.

You counted the number of observations as those for which rt is not missing. But, in the actual calculation, you can also have a missing value if vol is missing. In general, when calculating an unweighted mean, rather than counting out the denominator and totaling the numerator separately, it is safer to just use the -egen, mean()- function. Now, you apparently want that count of valid observations separately anyway, so you can exclude those that are based on too few. But then that calculation needs to be changed. So I think what you need is:

* Change date to STATA format
gen date1=date(month,"DMY")
format date1 %td
drop month
rename date1 date
gen mdate= mofd(date)
format mdate %tm
* Calculate Returns, Volume and filter data
bysort stock_id (date) : gen rt =((pr[_n]-pr[_n-1])/pr[_n-1])
gen vol=st*pr
replace vol=. if vol==0
* Calculate Amihud's measure of illiquidity
egen validobs=total(!missing(rt, vol)), by(stock mdate)
bysort stock_id mdate: egen ret_div_vol = mean(abs(rt/vol))
bysort stock_id mdate: gen illiq = ret_div_vol*1000000
collapse(mean) illiq validobs, by(stock_id stock mdate)
replace illiq=. if validobs<15
drop validobs
gen illiq_ln=ln(1+illiq)

As an aside, I am not a fan at all of the ln(1+x) transform. If it is conventionally used for this purpose in your field, then I suppose you have no choice but to do likewise, but really from a mathematical and statistical point of view it is toxic.

[Sartaj Hussain]

Thanks a lot. Can you suggest me alternative instead of ln(1+x). Moreover, what are the drawbacks of using a natural logarithmic transformation. Well, I am using log transformation to minimise effect of outliers in the illiq series.

[Clyde Schechter]

I have no problems with use of ln(x) as a logarithmic transformation in many settings. It is the use of ln(1+x) that I find problematic. If your variable has no zero or negative values and if the relationship to whatever predictors you are using is log-linear, than the use of ln(x) is fine. Another alternative, which can be used when there are zeroes or negative values, is to use Poisson regression of the untransformed variable instead of linear.

There are also other transformations that reduce the skewness of data, such as square root (only if everything is non-negative) or cube root.

Finally, I'll just make the point that outliers aren't necessarily bad and they aren't necessarily overly influential. Sometimes, even often, they should just be left alone.

[Marco Errico]

Dear Clyde Schechter


I am calculating the same illiquidity variable and I noticed this post on the blog.

Given that I'm trying to replicate a paper, in this case the illiquidity measure has to be calculated "as the the quarterly mean of the Amihud (2002) illiquidity measure (i.e., daily absolute stock return divided by US$ trading volume)"

I have returns (return_noperc), price (prccd), volume(cshtrd, don't need to multiply by 1 million) quarter (calendar_qtr) day (datadate) and id (isin_id)

One thing that I'm trying to do is that I don't want to collapse the dataset, but still have all the variables at the end of the process.

May I kindly ask your help to calculate the variable?

Here the starting point. (Now I'm not sure whether is the correct way to approach given that I need the quarterly mean)

egen validobs=total(!missing(return_noperc, cshtrd)), by(isin_id calendar_qtr )
bysort stock_id mdate: egen illiq = mean(abs(return_noperc/(cshtrd*prccd)))

After I see that you go for collapse but I would still want to keep the variable

May I kindly ask your help?

* Example generated by -dataex-. To install: ssc install dataex
clear
input float isin_id long datadate float(calendar_qtr return_noperc) double(cshtrd prccd)
1 18994 208            .    8467  9.71
1 18995 208    .01441811  113422  9.85
1 18996 208   .015228398   43673    10
1 18997 208   -.01999999   77091   9.8
1 18998 208            0       .   9.8
1 19000 208  -.009183638      19  9.71
1 19001 208    .01956737 1059679   9.9
1 19002 208            0   27811   9.9
1 19003 208    .03232324   90052 10.22
1 19004 208   .007827856  173069  10.3
1 19005 208            0       .  10.3
1 19007 208   -.01941747   72264  10.1
1 19008 208            0   26477  10.1
1 19009 208   .014851457  139257 10.25
1 19010 208  .0009755964  253478 10.26
1 19011 208    .01364521   97146  10.4
1 19012 208            0       .  10.4
1 19014 208    .01923076   52567  10.6
1 19015 208  -.008490534  179239 10.51
1 19016 208  -.007611779   67976 10.43
1 19017 208    .02109297   15693 10.65
1 19018 208    .03098591  122169 10.98
1 19019 208            0       . 10.98
1 19021 208   -.02094713   21850 10.75
1 19022 208   .020465083  143946 10.97
1 19023 208  -.001823129  173600 10.95
1 19024 208  -.009132457   52315 10.85
1 19025 208   .012903214  319108 10.99
1 19026 208            0       . 10.99
1 19028 208  .0009099056    9844    11
1 19029 208 -.0045453925  189447 10.95
1 19030 208 -.0045662285  235581  10.9
1 19031 208   .018348617  128475  11.1
1 19032 208  -.009009046   66607    11
1 19033 208            0       .    11
1 19035 208  -.013636339    9488 10.85
1 19036 208  .0046083136  453857  10.9
1 19037 208   .009174268  129088    11
1 19038 208            0   70055    11
1 19039 208            0   39610    11
1 19040 208            0       .    11
1 19042 208   .009090947   23974  11.1
1 19043 208  .0036036344   96491 11.14
1 19044 208    .01436262  109249  11.3
1 19045 208   .008849594   76731  11.4
1 19046 208  -.008771965   22056  11.3
1 19047 208            0       .  11.3
1 19049 208   .008849594   16644  11.4
1 19050 208   .008771887  170594  11.5
1 19051 208    .06521743  244837 12.25
1 19052 208  .0032652614  670534 12.29
1 19053 208  -.031733118  188073  11.9
1 19054 208            0       .  11.9
1 19056 208  -.008403321  126812  11.8
1 19057 208  -.008474611  318770  11.7
1 19058 208 -.0042735217  382791 11.65
1 19059 208  -.034334753  113988 11.25
1 19060 208  -.012444432   55016 11.11
1 19061 208            0       . 11.11
1 19063 208   .013501326   36106 11.26
1 19064 208   .007992944  464105 11.35
1 19065 208    .00881053  109082 11.45
1 19066 208  -.013100413   72930  11.3
1 19067 208   -.02566368  138224 11.01
1 19068 208            0       . 11.01
1 19070 208   .020890055   10381 11.24
1 19071 208  -.008007087  183394 11.15
1 19072 208   -.01345297   47677    11
1 19073 208   .009090947   77198  11.1
1 19074 208  -.018018013  210935  10.9
1 19075 208            0       .  10.9
1 19077 208   .008256849   87998 10.99
1 19078 208  .0009099056  367979    11
1 19079 208    .03909094  169733 11.43
1 19080 208   .013123336   99133 11.58
1 19081 208   .001727169   69111  11.6
1 19082 208            0       .  11.6
1 19084 209  .0043102857   41712 11.65
1 19085 209   .012017232  143900 11.79
1 19086 209 -.0042408993  188349 11.74
1 19087 209  -.003407184  109267  11.7
1 19088 209  -.014529867  447459 11.53
1 19089 209            0       . 11.53
1 19091 209  -.019947996    4815  11.3
1 19092 209    .04336287   10861 11.55
1 19093 209   .016450163   22742 11.74
1 19094 209  .0008518408  208241 11.75
1 19095 209  .0034042404   65268 11.79
1 19096 209            0       . 11.79
1 19098 209  -.016115373  104799  11.6
1 19099 209    .00862069   67489  11.7
1 19100 209  -.012820475   99633 11.55
1 19101 209 -.0008658537   83979 11.54
1 19102 209 -.0034661896   54465  11.5
1 19103 209            0       .  11.5
1 19105 209   .013043515   82055 11.65
1 19106 209            0  253636 11.65
1 19107 209   .004291808  116697  11.7
1 19108 209  -.017094018   59469  11.5
1 19109 209   .008695652   45783  11.6
end
format %td datadate
format %tq calendar_qtr

[Clyde Schechter]

Well, there are a couple of things here. The code you show refers to a variable mdate that does not exist in your example data. You need to eliminate the code that created mdate, and replace all later mentions of mdate with calendar_qtr.

As for keeping all of the observations rather than going to a monthly aggregated data set, just omit the -collapse- command.