Finding last day of the month

[Anthony Kira]

Hello,

I have the following code:

generate dayofmonth = day(date)
rename (THENATIVE-ZWAHLENMAYR) (return=)
reshape long return, i(date) j(Firm) string
sort Firm date
order Firm date
generate adjret = 1 * return
replace adjret = -1 * return if dayofmonth>=16

As you can see i go for the days 1 - 15 of the month long position (100%) and for the 2nd half of the month short position (-100%). Now i would like to go long for the last day of the month also (so the last day of the month should als .
The Problem here is cannot write for example

replace adjret = 1 * return if dayofmonth=30

because the last trading month could be the 29th, 30th or 31st. The last trading day for February could also be the 26th. I tried it with if-functions, but couldn't get it done.
Can someone help me with this?

[Joro Kolev]

If you do not have missing returns, in your current sort write below your block of code:

 
 replace adjret = -1 * return if _n==_N

[Nick Cox]

https://www.stata-journal.com/articl...article=dm0100 is an entire article on this question, but in your case you perhaps should work from the data you have.

You have daily data, it seems, so get the monthly date and then the last day of the month in your data is the last in each block of observations.

gen mdate = mofd(date) 
bysort mdate (date) : gen islast = _n == _N

I think Joro Kolev has a similar idea but his code will only pick up the last observation in the entire dataset.

This may need adjustment if there are missing values.

[Anthony Kira]

First, thank you both for the respond!
Second, i saw that i had a mistake in my first post.

Hello,

I have the following code:

generate dayofmonth = day(date)
rename (THENATIVE-ZWAHLENMAYR) (return=)
reshape long return, i(date) j(Firm) string
sort Firm date
order Firm date
generate adjret = 1 * return
replace adjret = -1 * return if dayofmonth>=16

As you can see i go for the days 1 - 15 of the month long position (100%) and for the 2nd half of the month short position (-100%). Now i would like to go long for the last day of the month also (so the last day of the month should also be calculated with *1 and not with *-1)
The Problem here is cannot write for example

replace adjret = 1 * return if dayofmonth=30

because the last trading month could be the 29th, 30th or 31st. The last trading day for February could also be the 26th. I tried it with if-functions, but couldn't get it done.
Can someone help me with this?

With Nick's code, it didn't quite work. I assume the problem for this is that i'm misunderstanding something. You said that i should get monthly data, this will be afterwards in my code.

generate dayofmonth = day(date)
rename (THENATIVE-ZWAHLENMAYR) (return=)
reshape long return, i(date) j(Firm) string
sort Firm date
order Firm date
generate adjret = 1 * return
replace adjret = -1 * return if dayofmonth>=16
gen adjret1 = adjret if adjret != . & adjret != 0
generate adjretadd1 = 1 + adjret1
list, sepby(Firm) abbreviate(16)
gen month = mofd(date)
format month %tm
egen double monthlyadjretadd1 = total(ln(adjretadd1)), by(Firm month)
replace monthlyadjretadd1 = exp(monthlyadjretadd1)

Now i tried Nick's code after my "replace adjret = -1 * return if dayofmonth>=16" and got this:

-----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str25 Firm int date double return float(dayofmonth mdate islast adjret)
"DISETRONIC"        12057                    . 4 396 0      .
"PUBLIGROUPE"       12057                  .07 4 396 0    .07
"FLUGHAFENZUR"      12057                    . 4 396 0      .
"SARNA"             12057                    . 4 396 0      .
"SWISSRE"           12057 .0039000000000000003 4 396 0  .0039
"METALLZUG"         12057                    . 4 396 0      .
"BELIMOHOLDING"     12057                    . 4 396 0      .
"VAUDOISE"          12057 -.026000000000000002 4 396 0  -.026
"MCHGRP"            12057                    . 4 396 0      .
"HELVETIAHDG"       12057                    . 4 396 0      .
"IMMUNO"            12057                    . 4 396 0      .
"AIRESISR"          12057                    . 4 396 0      .
"CEVALOG"           12057                    . 4 396 0      .
"LAFARGEHOLCIM"     12057                .0106 4 396 0  .0106
"MOLECULAR"         12057                    . 4 396 0      .
"BELL"              12057               -.0217 4 396 0 -.0217
"GAVAZZI"           12057                    . 4 396 0      .
"ADDEXTHERAPEUTICS" 12057                    . 4 396 0      .
"ALSOHOL"           12057                    . 4 396 0      .
"CHAMGROUP"         12057                    . 4 396 0      .
"BACHEM"            12057                    . 4 396 0      .
"ZURROSE"           12057                    . 4 396 0      .
"IMPLENIA"          12057                    . 4 396 0      .
"KERAMIK"           12057                .0217 4 396 0  .0217
"SANTHERA"          12057                    . 4 396 0      .
"LIECHTBK"          12057                .0083 4 396 0  .0083
"HENNIEZ"           12057                    . 4 396 0      .
"CIBANN"            12057                    . 4 396 0      .
"SWISSSTEEL"        12057                    0 4 396 0      0
"ScINTILLA"         12057                    . 4 396 0      .
"PANALPINAWELT"     12057                    . 4 396 0      .
"BELLEVUEGROUP"     12057                    . 4 396 0      .
"AGIECHARMILLES"    12057                    . 4 396 0      .
"UBLOXHDG"          12057                    . 4 396 0      .
"TEMENOS"           12057                    . 4 396 0      .
"SWISSLIFE"         12057                .0668 4 396 0  .0668
"ALCOPOR"           12057                    . 4 396 0      .
"AXANTIS"           12057               -.0286 4 396 0 -.0286
"MEDARTIS"          12057                    . 4 396 0      .
"SWISSCOM"          12057                    . 4 396 0      .
"MACHHITE"          12057                    . 4 396 0      .
"SNSIRION"          12057                    . 4 396 0      .
"WALLISERKTB"       12057                    . 4 396 0      .
"USTERTECH"         12057                    . 4 396 0      .
"PERFECTHDG"        12057                    . 4 396 0      .
"CIMENTPORT"        12057                    0 4 396 0      0
"ROMANDEENE"        12057                    . 4 396 0      .
"MOBIMO"            12057                    . 4 396 0      .
"SUNRISE"           12057                    . 4 396 0      .
"UBSGROUP"          12057  .005699999999999999 4 396 0  .0057
"BASELKTBK"         12057                .0002 4 396 0  .0002
"EKN"               12057                    . 4 396 0      .
"VALIANT"           12057                    . 4 396 0      .
"EDISUN"            12057                    . 4 396 0      .
"BERNABIOTECH"      12057                    . 4 396 0      .
"ACTELION"          12057                    . 4 396 0      .
"SELECTA"           12057                    . 4 396 0      .
"ROCHE"             12057                .0144 4 396 0  .0144
"VONTOBELHDG"       12057                .0179 4 396 0  .0179
"BANKSTGALLEN"      12057                    . 4 396 0      .
"FOTOLABO"          12057                    0 4 396 0      0
"GLOBUS"            12057                    . 4 396 0      .
"LUKTB"             12057                    . 4 396 0      .
"SWISSLOGHDG"       12057                    0 4 396 0      0
"SIEGFRIED"         12057                    . 4 396 0      .
"CALIDA"            12057                    0 4 396 0      0
"GAMHOLDING"        12057                .0063 4 396 0  .0063
"EDOMROTH"          12057                    . 4 396 0      .
"FORBOHDG"          12057                 .003 4 396 0   .003
"ARBONIAAG"         12057  .024399999999999998 4 396 0  .0244
"BOSSARD"           12057                    . 4 396 0      .
"LEMR"              12057                    . 4 396 0      .
"TAGHEUER"          12057                    . 4 396 0      .
"LOGITECH"          12057               -.0435 4 396 0 -.0435
"VIFORPHARMA"       12057                    0 4 396 0      0
"UNILABS"           12057                    . 4 396 0      .
"SPICEPEQ"          12057                    . 4 396 0      .
"AMAZYS"            12057                    . 4 396 0      .
"ORIORB"            12057                    . 4 396 0      .
"GROUPEMIN"         12057                    . 4 396 0      .
"ALLREALHOLDING"    12057                    . 4 396 0      .
"BANKLINTH"         12057                    . 4 396 0      .
"LIECHTGLB"         12057               -.0179 4 396 0 -.0179
"BANQUEJURA"        12057                    . 4 396 0      .
"INDUSTRYCOMM"      12057                    . 4 396 0      .
"PARTNERSGROUP"     12057                    . 4 396 0      .
"GATEGROUP"         12057                    . 4 396 0      .
"BANKSARA"          12057                    . 4 396 0      .
"VATGROUP"          12057                    . 4 396 0      .
"BURCKHARDTCMPSN"   12057                    . 4 396 0      .
"APTGAG"            12057                .0385 4 396 0  .0385
"CENTERPULSE"       12057                    . 4 396 0      .
"STRAUMANN"         12057                    . 4 396 0      .
"SYNGENTA"          12057                    . 4 396 0      .
"VONTOBELINV"       12057                    . 4 396 0      .
"SIMPLON"           12057                    . 4 396 0      .
"ASKLIA"            12057                    0 4 396 0      0
"HUBERSUHNER"       12057                    0 4 396 0      0
"ORIDION"           12057                    . 4 396 0      .
"LENZERHEIDE"       12057                    . 4 396 0      .
end
format %td date

------------------
For example: In my dataset i have 360 different firms. The 29th January 1993 is the last trading month for every firm (some have values for example 0.0037, some have .)
The variable islast then gave my only for onefirm in 29th January 1993 a 1 and for the rest 0.

With Joro's Code after mine there were 0 changes made.
Maybe to illustrate my dataset better, here it is what it looks like after my "replace adjret = -1 * return if dayofmonth>=16" command

-----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str25 Firm int date double return float(dayofmonth adjret)
"AARGBK" 12057               .0027  4  .0027
"AARGBK" 12058               .0053  5  .0053
"AARGBK" 12059               .0027  6  .0027
"AARGBK" 12060              -.0106  7 -.0106
"AARGBK" 12061                   0  8      0
"AARGBK" 12064              -.0027 11 -.0027
"AARGBK" 12065               .0134 12  .0134
"AARGBK" 12066               .0053 13  .0053
"AARGBK" 12067                   0 14      0
"AARGBK" 12068               .0053 15  .0053
"AARGBK" 12071              -.0026 18  .0026
"AARGBK" 12072              -.0026 19  .0026
"AARGBK" 12073               .0053 20 -.0053
"AARGBK" 12074 .015700000000000002 21 -.0157
"AARGBK" 12075                   0 22      0
"AARGBK" 12078              -.0052 25  .0052
"AARGBK" 12079               .0052 26 -.0052
"AARGBK" 12080              -.0077 27  .0077
"AARGBK" 12081               .0052 28 -.0052
"AARGBK" 12082               .0103 29 -.0103
"AARGBK" 12085               .0077  1  .0077
"AARGBK" 12086              -.0025  2 -.0025
"AARGBK" 12087               .0051  3  .0051
"AARGBK" 12088                   0  4      0
"AARGBK" 12089               .0101  5  .0101
"AARGBK" 12092               .0025  8  .0025
"AARGBK" 12093              -.0025  9 -.0025
"AARGBK" 12094              -.0075 10 -.0075
"AARGBK" 12095              -.0025 11 -.0025
"AARGBK" 12096                   0 12      0
"AARGBK" 12099              -.0051 15 -.0051
"AARGBK" 12100              -.0025 16  .0025
"AARGBK" 12101              -.0026 17  .0026
"AARGBK" 12102               .0051 18 -.0051
"AARGBK" 12103                   0 19      0
"AARGBK" 12106               .0025 22 -.0025
"AARGBK" 12107              -.0051 23  .0051
"AARGBK" 12108              -.0051 24  .0051
"AARGBK" 12109                   0 25      0
"AARGBK" 12110               .0128 26 -.0128
"AARGBK" 12113               .0076  1  .0076
"AARGBK" 12114                .005  2   .005
"AARGBK" 12115                   0  3      0
"AARGBK" 12116                   0  4      0
"AARGBK" 12117                   0  5      0
"AARGBK" 12120                   0  8      0
"AARGBK" 12121                   0  9      0
"AARGBK" 12122               -.005 10  -.005
"AARGBK" 12123                   0 11      0
"AARGBK" 12124              -.0075 12 -.0075
"AARGBK" 12127               .0051 15  .0051
"AARGBK" 12128                   0 16      0
"AARGBK" 12129              -.0025 17  .0025
"AARGBK" 12130               .0051 18 -.0051
"AARGBK" 12131                .005 19  -.005
"AARGBK" 12134              -.0075 22  .0075
"AARGBK" 12135               .0025 23 -.0025
"AARGBK" 12136              -.0025 24  .0025
"AARGBK" 12137                   0 25      0
"AARGBK" 12138               .0052 26 -.0052
"AARGBK" 12141               .0104 29 -.0104
"AARGBK" 12142               .0051 30 -.0051
"AARGBK" 12143              -.0026 31  .0026
"AARGBK" 12144                   0  1      0
"AARGBK" 12145               .0051  2  .0051
"AARGBK" 12148              -.0026  5 -.0026
"AARGBK" 12149               .0102  6  .0102
"AARGBK" 12150              -.0025  7 -.0025
"AARGBK" 12151               .0025  8  .0025
"AARGBK" 12152                   0  9      0
"AARGBK" 12155                   0 12      0
"AARGBK" 12156               .0025 13  .0025
"AARGBK" 12157              -.0025 14 -.0025
"AARGBK" 12158                   0 15      0
"AARGBK" 12159                   0 16      0
"AARGBK" 12162                   0 19      0
"AARGBK" 12163                   0 20      0
"AARGBK" 12164                   0 21      0
"AARGBK" 12165              -.0025 22  .0025
"AARGBK" 12166                   0 23      0
"AARGBK" 12169              -.0051 26  .0051
"AARGBK" 12170                   0 27      0
"AARGBK" 12171                   0 28      0
"AARGBK" 12172              -.0026 29  .0026
"AARGBK" 12173                   0 30      0
"AARGBK" 12176               .0051  3  .0051
"AARGBK" 12177              -.0025  4 -.0025
"AARGBK" 12178               .0026  5  .0026
"AARGBK" 12179               .0051  6  .0051
"AARGBK" 12180              -.0025  7 -.0025
"AARGBK" 12183               .0025 10  .0025
"AARGBK" 12184                   0 11      0
"AARGBK" 12185                   0 12      0
"AARGBK" 12186               .0025 13  .0025
"AARGBK" 12187                   0 14      0
"AARGBK" 12190                   0 17      0
"AARGBK" 12191               .0101 18 -.0101
"AARGBK" 12192              -.0025 19  .0025
"AARGBK" 12193                   0 20      0
"AARGBK" 12194                   0 21      0
end
format %td date

I highlighted the last trading day of january and February 1993. January has for example -.0103 and this now should be 0.0103.

[Nick Cox]

Sorry, I should have made explicit that my code needs to be applied separately by firm as well as monthly date.

[Joro Kolev]

I am sorry, I messed it up. Thanks to Nick for pointing it out.

I meant:

gen mofd = mofd(date)
bys Firm mofd (date): replace adjret = -1 * return if _n==_N

[Anthony Kira]

No problem! I will try Nick's code later, thank you very much! I will post it here if it worked or if i have another question.

@Joro Kolev: Thank you very much for you help. I tried quickly your code at the end of mine, and once again the outcome, unfortunately, is the same: 0 real changes were made

generate dayofmonth = day(date)
rename (THENATIVE-ZWAHLENMAYR) (return=)
reshape long return, i(date) j(Firm) string
sort Firm date
order Firm date
generate adjret = 1 * return
replace adjret = -1 * return if dayofmonth>=16

gen month = mofd(date)
bys Firm month (date): replace adjret = -1 * return if _n==_N

Am i not seeing something here?

Short off topic:
@Nick: A few days ago i ran into a problem and googled it and saw you solution:
I have the same dataset as above: So daily return of over 300 firms for the period of 1993 - 2017. First i tried to show descriptive statistics; so what's the return of mondays and so on and how large is the number of observations. I worked with this code

generate dayofweek = dow(date)
list, abbreviate(16)
rename (THENATIVE-ZWAHLENMAYR) (return=)
reshape long return, i(date) j(Firm) string
sort Firm date
order Firm date
bys date (dayofweek): gen returns = sum(return)
drop if returns == 0
collapse (mean) Mean = return (sd) Std_Dev = return (count) N = return, by(dayofweek)
gen t_stat = Mean/(Std_Dev/sqrt(N))

Then i realized that even if i let the dataset exactly like it is, imported it again and run it again i had some little differences. For example the n is once 210'714 and then in the next run it is 210'899. The mean and std dev also changes (very marginal)
I saw your solution with the sort stable and added it to the 5th row in the code so: sort Firm date, stable

It helped, the differences are not that large but they still are there. Do you know what the problem might be? I thought it could be something with randomization but that should not happen, unless my codes are wrong.

[Anthony Kira]

Sorry, I should have made explicit that my code needs to be applied separately by firm as well as monthly date.

Hello Nick,

I tried it with this code:

bysort Firm month: gen mfd = mofd(date) 
 bysort mdate (date) : gen islast = _n == _N

But unfourtanely it didn't work. I think the reason for that is that the mofd i get is for every month in every year for every firm the same. Then when i try to get the islast = 1 it gives me only for one firm the 1, because the mofd is the same. So i should get a mofd which is for example for the first firm in january 1993 396 and for february 1993 496. Then for the next firm it should get me for january 1993 397 and february 497 and so on. Then i think it could work that the islast is 1 for every observation.
Does someone has some advice/help?

[Nick Cox]

The first statement does no harm but can be simplified as what monthly date it is it is doesn't depend on which firm you are thinking about. and the right syntax for a daily date is the same regardless of what month it is.

 
 gen mfd = mofd(date)

It is the second kind of statement I was thinking about in #5. It needs to be

bysort firm mdate (date) : gen islast = _n == _N

But generating an indicator is not the main deal here. It is that you want to do something differently for the last observation and if _n == _N is a general handle for that.

[Anthony Kira]

Thank you Nick for the explanation and the code!