Drop all values other than month-end dates where the dates dont always end on standard monthend say there would be a value 27th May.

[Priyanka Maheshwari]

Hi,

I have a file with a date column and 5 other daily return columns. File attached.
I use the below code to get monthly return :

import delimited "C:\Users\Aakash\Desktop\AIA\Clean Data\AQR_BAB_Factors.csv", case(preserve)
gen trdate = date(Date,"MDY")
format %td trdate
tsset trdate
ascol AQR_BAB_Equity_Global, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyGlobal)
drop month_id
ascol AQR_BAB_Equity_Global_Ex_USA, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyGlobalExUSA)
drop month_id
ascol AQR_BAB_Equity_Europe, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyEurope)
drop month_id
ascol AQR_BAB_Equity_NorthAmerica, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyNorthAmerica)
drop month_id
ascol AQR_BAB_Equity_Pacific, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyPacific)

Now I need only the monthend dates. I need all last day of month even if its not standard month-end date.
How do I do that ?

I tried the below :

gen long first_of_next_month = dofm(mofd(trdate)+1)
keep if trdate + 1 == first_of_next_month

But this returns only standard month-end dates and deletes all other data.
Could someone help?

[Nick Cox]

This is evidently a question for Attaullah Shah as it's about his ascol (SSC, as you are asked to explain).

[Priyanka Maheshwari]

Could someone please reply..I am new to Stata and need to fix this soon..any help would be appreciated

[Marcos Almeida]

Please take some time to read the FAQ. This is the bedt approach to entice a helpful reply. There you’ll find how to share data/command/output, etc.

[Attaullah Shah]

I have not clearly understood your question. In my view, you want to keep only one observation in each month, and that observation should be the last available day in that month. If this is correct, so you can use the following code. Specifically, the last colored line provides the desired solution.

import delimited "C:\Users\Aakash\Desktop\AIA\Clean Data\AQR_BAB_Factors.csv", case(preserve)
gen trdate = date(Date,"MDY")
format %td trdate
tsset trdate
ascol AQR_BAB_Equity_Global, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyGlobal)
drop month_id
ascol AQR_BAB_Equity_Global_Ex_USA, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyGlobalExUSA)
drop month_id
ascol AQR_BAB_Equity_Europe, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyEurope)
drop month_id
ascol AQR_BAB_Equity_NorthAmerica, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyNorthAmerica)
drop month_id
ascol AQR_BAB_Equity_Pacific, returns(simple) keep(all) tomonth gen(AQR_BAB_MonthlyPacific)

bys month_id(Date): keep if _n == _N

There is also a blog entry on my website for ascol to convert daily returns to monthly returns with options of :

1. simple percentage returns and
2. continuously compounded or log returns.

[Priyanka Maheshwari]

Thanks Attaullah Shah . The command worked.