Daily stock prices into monthly variable

[Amy Moore]

Hi,

I have daily stock market data but I need to convert it to be monthly. Does anyone know the best way to do this?

I was thinking of taking the first available daily observation in each month to be the 'monthly value', but because I have stock data (and the market is closed on the weekends), I do not always have a value for the the first day of the month. For instance, sometimes the first observation for that month might be on the second day. Does anybody know what code I would use to do this?

Thanks,
Amy

[Daniel Schaefer]

Hi Amy, welcome to StataList.

it might be helpful if you provide some example data. Please use the -dataex- command to generate the example data. Trust me, this command is going to make your life easier (and your posts more likely to get a response). See:

help dataex

for more details. You might also want to take a moment to look around the forum, to get a sense of the culture here.

Thank you so much,

Daniel

[Amy Moore]

Thank you for your help, Daniel.

I have provided some example data below.

* Example generated by -dataex-. For more info, type help dataex
clear
input int DATE double CLOSE
10959 17.24
10960 18.19
10961 19.22
10962 20.11
10965 20.26
10966  22.2
10967 22.44
10968 20.05
10969 24.64
10972 26.34
10973 24.18
10974 24.16
10975 24.34
10976  22.5
10979  26.7
10980 24.72
10981 25.39
10982 25.63
10983 26.28
10986 26.44
10987 27.25
10988 25.36
10989 24.87
10990 24.32
10993 24.54
10994 24.69
10995 24.29
10996 23.77
10997 23.69
11000 24.38
11001 23.76
11002 22.05
11003 19.71
11004 20.78
11008 22.78
11009 23.89
11010 22.54
11011 23.69
11014 23.56
11015 22.69
11016 21.99
11017  21.9
11018 21.34
11021 22.03
11022 20.55
11023  19.1
11024 19.74
11025  20.3
11028 20.07
11029 21.05
11030 19.65
11031 18.81
11032 17.62
11035 18.29
11036 19.06
11037  20.1
11038 22.74
11039 20.46
11042 19.59
11043 21.01
11044 19.77
11045 18.46
11046 19.73
11049 22.84
11050 20.44
11051 22.03
11052 21.21
11053 21.76
11056  22.3
11057 21.69
11058 17.73
11059  19.8
11063 20.75
11064 20.38
11065 22.67
11066 22.46
11067 23.33
11070 24.16
11071  21.7
11072 20.86
11073 20.25
11074 22.19
11077 19.52
11078 18.43
11079 19.32
11080 17.81
11081 17.28
11084 17.65
11085 18.49
11086 18.64
11087 18.59
11088 19.81
11091 20.14
11092 19.49
11093 17.71
11094 17.27
11095  17.2
11098 16.72
11099 17.84
11100 17.18
end
format %td DATE

Best,
Amy

[Justin Niakamal]

To convert from a higher frequency to a lower, I would suggest taking the mean as opposed to the first day.

gen month = mofd(DATE)
format month %tm
collapse (mean) CLOSE, by(month)

But if you’d prefer the first day you can do this

gen month = mofd(DATE)
format month %tm
bys month (DATE): keep if _n == 1

[Amy Moore]

Thank you Justin - it worked perfectly.

I appreciate your help

[Amy Moore]

Hi Justin,

Do you know what the code would be if I wanted to use the last day in the month rather than the first day?

Best,
Faith

[Justin Niakamal]

gen month = mofd(DATE)
format month %tm
bys month (DATE): keep if _n == _N

[Amy Moore]

Thank you!