Create monthly returns in panel data using the first and last observation

[Steyn Poppel]

Hi all,

I have this panel dataset of closing prices for 50 stock different stocks, divided over time into 63 periods.
Every period contains 28 daily observations. Now I want to calculate the returns per stock for every period of 4weeks with only using the first and the last observation of a period.
How could this be done?

This is how the dataset looks like

date       close   id periodvar
16-05-2013     118.76   8 1
17-05-2013     123.02   8 1
--
10-06-2013     106.35   8 1
11-06-2013     108.9     8 1
12-06-2013     108.15    8 1
16-05-2013     2.92      24 1
17-05-2013     3.19      24 1
--
11-06-2013     2.4      24 1
12-06-2013    2.38       24 1 

and then the second period begins and so on. During the periods, more stocks are added.

Thanks for your advise/help!

[Attaullah Shah]

By every 4 weeks, do you mean monthly returns? If your data is daily and you want to convert it to monthly, then you need to create a month identifier, reduce the data frequency to monthly, and then find returns. See the following example,

* Load example data
use http://fintechprofessor.com/stocks.dta

* create month identifier
gen mofd = mofd(date)
format mofd %tm
bys symbol mofd: keep if _n == _N

* create panel numeric id
encode symbol, gen(id)

* declare panel data
tsset id mofd

* create returns
gen returns = ln(close / L.close)
list in 1/10
     +----------------------------------------------------------+
     | symbol        date    close      mofd     id     returns |
     |----------------------------------------------------------|
  1. |   AABS   17jan2013   105.75    2013m1   AABS           . |
  2. |   AABS   27feb2013      101    2013m2   AABS   -.0459573 |
  3. |   AABS   28mar2013      101    2013m3   AABS           0 |
  4. |   AABS   10apr2013      101    2013m4   AABS           0 |
  5. |   AABS   29may2013   110.25    2013m5   AABS      .08763 |
     |----------------------------------------------------------|
  6. |   AABS   28jun2013      105    2013m6   AABS   -.0487902 |
  7. |   AABS   30jul2013      105    2013m7   AABS           0 |
  8. |   AABS   30aug2013    97.85    2013m8   AABS   -.0705247 |
  9. |   AABS   25sep2013       98    2013m9   AABS    .0015318 |
 10. |   AABS   31oct2013       97   2013m10   AABS   -.0102565 |
     +----------------------------------------------------------+

Note, you can also consider collapse command.

You can also consider ascol (can be downloaded from SSC) for converting data frequency from daily to weekly, monthly, quarterly or yearly frequency. ascol can convert both stock prices and stock returns. Here is a blog post on how to convert simple and log returns from a higher frequency to lower frequency with ascol. The above steps are simplified with ascol

* install ascol
ssc install ascol
use http://fintechprofessor.com/stocks.dta

* Convert stock prices to a monthly frequency with ascol
ascol close, panel(symbol) time(date) price tomonth

* create panel numeric id
encode symbol, gen(id)

* declare panel data
tsset id month_id

* generate log returns
gen returns = ln(month_close / L.month_close)

list in 1/10
     +--------------------------------------------------+
     | symbol   month_id   month_c~e     id     returns |
     |--------------------------------------------------|
  1. |   AABS     2013m1      105.75   AABS           . |
  2. |   AABS     2013m2         101   AABS   -.0459573 |
  3. |   AABS     2013m3         101   AABS           0 |
  4. |   AABS     2013m4         101   AABS           0 |
  5. |   AABS     2013m5      110.25   AABS      .08763 |
     |--------------------------------------------------|
  6. |   AABS     2013m6         105   AABS   -.0487902 |
  7. |   AABS     2013m7         105   AABS           0 |
  8. |   AABS     2013m8   97.849998   AABS   -.0705247 |
  9. |   AABS     2013m9          98   AABS    .0015318 |
 10. |   AABS    2013m10          97   AABS   -.0102565 |
     +--------------------------------------------------+

[Clyde Schechter]

So the first thing is that you need a numeric Stata internal format date variable. Perhaps what you have already is: because of the way you posted your data this important fact is unknowable. The code below assumes it is a string variable and converts it. Skip this part of the code if you already have a numeric Stata date variable.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date float close byte(id periodvar)
"16-05-2013" 118.76  8 1
"17-05-2013" 123.02  8 1
"10-06-2013" 106.35  8 1
"11-06-2013"  108.9  8 1
"12-06-2013" 108.15  8 1
"16-05-2013"   2.92 24 1
"17-05-2013"   3.19 24 1
"11-06-2013"    2.4 24 1
"12-06-2013"   2.38 24 1
end

//    CREATE NUMERIC STATA INTERNAL FORMAT DATE VARIABLE
rename date _date
gen date = daily(_date, "DMY")
assert missing(date) == missing(_date)
format date %td
drop _date

//    CALCULATE RETURN
by id periodvar (date), sort: gen return = (close[_N] - close[1])/close[1]

Note: The return here is calculated as a proportion of the starting value. If you want it in percentages, multiply the result by 100.

Added: Crossed with #2, where it is assumed that you want calendar month returns rather than 4 week periods. The code here relies on your "periodvar" variable for identifying the periods over which you want your return calculated. Pick whichever reflects your needs.

[Attaullah Shah]

Please also note the Clyde uses simple percentage returns whereas I calculate log or continuously compounded returns. Both the methods are common in the calculation of stock returns.

[Steyn Poppel]

Both many thanks for your input!

[Arthur AJ]

Hi all, since this question is about calculating returns, can anyone please explain to me how the results will be different based on the 4 commands below:

1- returns = (close - l.close)/l.close
2- returns = log(close[_n])-log(close[_n-1])
3- return = (close[_n] - close[1])/close[1]
4- returns = ln(close / L.close)



Thank you !

[Clyde Schechter]

So, first there are some syntax differences:

In order for l.close to be defined, you must have -xtset- or -tsset- the data. Moreover, L.close is only when the immediately preceding value in the data set is exactly 1 time unit before the current time. Otherwise L.close will be missing value, and so will anything calculated from it.

By contrast, close[_n-1] is always defined (except in the first observation) and refers to the value of close at the last time that happens to be in the data, whenever that is.

l.close (or L.close, they are the same) is the same as close[_n-1] if there are no gaps in the regular time series of the data. But if the time series is not regularly spaced or has gaps, then close[_n-1] refers to the preceding close in the series, whenever that may happen to be. So with irregularly spaced series or series with gaps, the returns you calculate using anything involving close[_n-1] will refer to different periods of time (and, depending on your data, the time periods may or may not be known.)

Formula 1 is the definition of period return and is the one you should always use with regular periodic data.

Formulas 2 and 4 are approximations that are only usable if the return is very small. The approximation gets worse and worse the larger the return is. Since logarithms are computationally expensive to calculate, they are both lousy approximations and harder to calculate than the actual formula (#1), so there really is no sensible reason to ever use them. They are zombies.

Formula 3 is the correct formula for the cumulative return from the start of the series. By the way, you can save a few keystrokes here and just type it as (close - close[1])/close[1]. The [_n] is not needed there.

[Arthur AJ]

Dear Clyde, thank you very much for such a detailed answer.

The data I am using is for stock market firms, some of the days are missing (.) and there are some weekends. So below are my commands:

xtset Company_id Date, daily
qui tsfill, full
qui drop if close == .
qui carryforward CompanyName close, replace
qui gen returns = (close - l.close)/l.close

Is this the right way to use formula 1?

Thank you !