Help: Sum daily data monthly across multiple years and ids

[Marina Jenny]

Dear Statalist community,

I am really a STATA beginner and tried to find a solution here to my problem but couldn't an answer.

So what I have is for multiple ids (TickerSymbols) daily data that I have to sum monthly over a period of 12 years (for most of the ids/Tickersymbols) and that over around 19 ids/Tickersymbols.
So I basically need to sum values that are in a field "stdpcalc" for each month and also each TickerSymbol (seperately) so i.e. 03.01.07 - 31.01.07 and 01.02.2007 - 28.02.2007 and so on until 31.12.2019. This I will have to do for a total of 16 TickerSymbols (DIA as an example). Because I will need the sum of each month of the values in the field stdpcalc in a new field to be able to use it for further calculations.

I found in this forum a command like:

bys TickerSymbol1 (date): egen total1 = total(stdpcalc ) if inrange(date,date[1],date[20])


but with that I will get the data for one month for the respective TickerSymbol correctly but then I would need to do that manually for each month over 12years for each TickerSymbol and I think there must be an easier way to calculate that more automatic.

I am super stuck so I hope somebody can help me - any help is greatly appreaciated!


Thanks

[Ali Atia]

Assuming your daily dates are already date variables, which it looks like they are from the screenshot, you can convert them into monthly and go from there:

generate month = mofd(date)
bys TickerSymbol month: egen total1 = total(stdpcalc)

[Raymond Zhang]

If the daily dates are strings,you can use the command below.But just as @All Atia said,your dates maybe already date variables.

gen month=substr(date,3,2)
bys TickerSymbol1 month :egen total1 = total(stdpcalc )

[Raymond Zhang]

Also,you can use the command extrdate,

extrdate mo month=date
bys TickerSymbol1 month:egen total1 = total(stdpcalc )

btw,the command collapse may also be helpful to you.

collapse (sum) stdpcalc,by(TickerSymbol1 month)

[Marina Jenny]

I will try this out just now, thank you all so much already for your fast responses!!

[Marina Jenny]

Your codes worked super good, thanks tons!! Have all an awesome day