Extracting weekly returns for daily panel data with missing dates

[Erik Fyllingstudent]

I am trying to extract "close1" for the last reported value of "day_of_week" with respect to the "week_num". In other words, I am trying to extract the stocks' last close price of the week - ideally friday - but since some of these stocks are old, discontinued, and illiquid they are not reported for all days of the week. Therefore, I would like to extract the last close price of the week in order to calculate weekly returns.

Any tips? (PS: Large dataset with 1,4 million observations)

Below I have an example of the dataset.

date1	isin1	sector1	name1	close1	ret	day_of_week	friday_close	increase	weekly_return	week_num
08.03.1990	NO0005394908		Frysja Elektro	14	-.7700607	4	09.03.1990	.2299393	0	10
11.04.1990	NO0005394908		Frysja Elektro	12	-.2772484	3	13.04.1990	.7227516	0	15
09.05.1990	NO0005394908		Frysja Elektro	12	-.7994583	3	11.05.1990	.2005417	-.7189392	19
11.05.1990	NO0005394908		Frysja Elektro	14	-.7189392	5	11.05.1990	.2810608	-.7189392	19
18.05.1990	NO0005394908		Frysja Elektro	11	-.796392	5	18.05.1990	.203608	0	20
22.06.1990	NO0005394908		Frysja Elektro	10	-.8528534	5	22.06.1990	.1471466	0	25
13.04.1983	NO0005396200	Industrials	Simrad Optronics	200	-.2939858	3	15.04.1983	.7060143	.6817558	15
14.04.1983	NO0005396200	Industrials	Simrad Optronics	230	-.2950562	4	15.04.1983	.7049438	.6817558	15
15.04.1983	NO0005396200	Industrials	Simrad Optronics	230	1.385659	5	15.04.1983	2.385659	.6817558	15
18.04.1983	NO0005396200	Industrials	Simrad Optronics	210	-.5354477	1	22.04.1983	.4645523	-.7946736	16
19.04.1983	NO0005396200	Industrials	Simrad Optronics	215	-.4592903	2	22.04.1983	.5407097	-.7946736	16
20.04.1983	NO0005396200	Industrials	Simrad Optronics	205	-.5373737	3	22.04.1983	.4626263	-.7946736	16
21.04.1983	NO0005396200	Industrials	Simrad Optronics	195	1.108895	4	22.04.1983	2.108895	-.7946736	16
22.04.1983	NO0005396200	Industrials	Simrad Optronics	200	-.6107797	5	22.04.1983	.3892203	-.7946736	16
25.04.1983	NO0005396200	Industrials	Simrad Optronics	210	-.6133224	1	29.04.1983	.3866776	-.8287998	17
26.04.1983	NO0005396200	Industrials	Simrad Optronics	235	-.5259737	2	29.04.1983	.4740263	-.8287998	17
27.04.1983	NO0005396200	Industrials	Simrad Optronics	240	-.4991617	3	29.04.1983	.5008383	-.8287998	17
28.04.1983	NO0005396200	Industrials	Simrad Optronics	225	-.5126684	4	29.04.1983	.4873316	-.8287998	17
29.04.1983	NO0005396200	Industrials	Simrad Optronics	222,5	.4797208	5	29.04.1983	1.479721	-.8287998	17

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 date1 str13 isin1 str12 sector1 str17 name1 double close1 float ret byte day_of_week str11 friday_close float(increase weekly_return) byte week_num
"08.03.1990 " "NO0005394908 " ""             "Frysja Elektro "      14 -.7700607 4 "09.03.1990 " .2299393         0 10
"11.04.1990 " "NO0005394908 " ""             "Frysja Elektro "      12 -.2772484 3 "13.04.1990 " .7227516         0 15
"09.05.1990 " "NO0005394908 " ""             "Frysja Elektro "      12 -.7994583 3 "11.05.1990 " .2005417 -.7189392 19
"11.05.1990 " "NO0005394908 " ""             "Frysja Elektro "      14 -.7189392 5 "11.05.1990 " .2810608 -.7189392 19
"18.05.1990 " "NO0005394908 " ""             "Frysja Elektro "      11  -.796392 5 "18.05.1990 "  .203608         0 20
"22.06.1990 " "NO0005394908 " ""             "Frysja Elektro "      10 -.8528534 5 "22.06.1990 " .1471466         0 25
"13.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   200 -.2939858 3 "15.04.1983 " .7060143  .6817558 15
"14.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   230 -.2950562 4 "15.04.1983 " .7049438  .6817558 15
"15.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   230  1.385659 5 "15.04.1983 " 2.385659  .6817558 15
"18.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   210 -.5354477 1 "22.04.1983 " .4645523 -.7946736 16
"19.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   215 -.4592903 2 "22.04.1983 " .5407097 -.7946736 16
"20.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   205 -.5373737 3 "22.04.1983 " .4626263 -.7946736 16
"21.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   195  1.108895 4 "22.04.1983 " 2.108895 -.7946736 16
"22.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   200 -.6107797 5 "22.04.1983 " .3892203 -.7946736 16
"25.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   210 -.6133224 1 "29.04.1983 " .3866776 -.8287998 17
"26.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   235 -.5259737 2 "29.04.1983 " .4740263 -.8287998 17
"27.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   240 -.4991617 3 "29.04.1983 " .5008383 -.8287998 17
"28.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics "   225 -.5126684 4 "29.04.1983 " .4873316 -.8287998 17
"29.04.1983 " "NO0005396200 " "Industrials " "Simrad Optronics " 222.5  .4797208 5 "29.04.1983 " 1.479721 -.8287998 17
end

by isin1 week_num (day_of_week), sort: gen week_final_close = close1[_N]

Note: I am assuming that the variable close1 is the closing price variable. If I guessed wrong, replace that with the name of the correct variable.

If you are going to use that date1 variable for anything, having it as a string is not going to be useful. Ditto for friday_close. You should convert those to Stata internal format date variables using the -daily()- function to make them useful.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Erik Fyllingstudent]

Thank you Clyde Schechter!