算年报披露日后30个交易日股票回报率均值，要怎么编程？

大家也都知道不同的上市公司年报披露日是不同的，股票只在非周末非节假日交易；
我现在就是需要计算每家上市公司年报披露日后30个交易日股票回报率的均值，这在stata或python中怎么实现？
我举个栗子：表1是每家上市公司年报披露日期
                     表2是每家上市公司2018-1-19至2018-6-22每天的股票回报率

  从表1中可知，000001股票的披露日是2018-3-15，我们需要从表2中找到000001，找到2018-3-15这个日期，然后向后数30个交易日，计算平均值。

表1

表2

得到平均值是0.0012672，就是表1第三列所求

Here is a solution, change the file location before using these codes.
-------------------------------------------
*Pre-requisition:
ssc install rangestat
ssc install egenmore
*Merge two datasheets from excel format, assuming no duplicates in each datasheet. Keep only stock identifiers are inclusive in the two sheets.
clear all
import excel "C:\Users\Downloads\1.xlsx", sheet("Sheet1") firstrow
drop in 1
save "C:\Users\Desktop\masterfile.dta", replace
import excel "C:\Users\Downloads\2.xlsx", sheet("Sheet1") firstrow clear
drop in 1
merge m:1 Stkcd using "C:\Users\Desktop\masterfile.dta"
keep if _merge==3
drop _merge
*Convert data information from string type to time variable that Stata can recognises
gen date1 = date( Annodt , "MDY")
gen date2=date( date ,"MDY")
format date1 date2 %td
*Sort all observations by stock identifier and date
sort Stkcd date2
*For each stock, assgin serial number of date (datenum), obtain target date (target) as the indicator of earning announcement date, then have the time window before/after the date
by Stkcd: gen datenum = _n
by Stkcd: gen target = datenum if date2 == date1
egen td = min(target), by(Stkcd)
gen diff = datenum-td
*Obtain rollowing-window mean of ILLIQ for each stock, time interval set as 30 days. Check if "000001" matches as mannual outcome.
destring ILLIQ,replace
rangestat (mean) ILLIQ , interval( diff 1 30) by(Stkcd)
replace Annodt日后30交易日股票回报率平均值= ILLIQ_mean
keep if Stkcd=="000001" & target!=.
----------------------------------------------------
Comments welcome

kyoukai 发表于 2018-7-5 16:17
Here is a solution, change the file location before using these codes.
---------------------------- ...

完美运行啦，厉害厉害！十分感谢热心帮助！不过最后一句命令直接  keep if target!=. 就可以了吧？

bu妥先生 发表于 2018-8-5 15:43
完美运行啦，厉害厉害！十分感谢热心帮助！不过最后一句命令直接  keep if target!=. 就可以了吧？

这是为了只留下你给出数据的那个结果方便你比对。你自己用的时候删掉就行了。

kyoukai 发表于 2018-8-6 09:23
这是为了只留下你给出数据的那个结果方便你比对。你自己用的时候删掉就行了。

Nice！谢谢啦~

正好需要，非常感谢