merging Compustat annual data and daily security data

[max tailler]

Dear Statalist members,

I need to merge daily security data to Fundamentals annual data. I have tried several ways to merge , but I could not come up with the results until now.
I am using a dataset form the WRDS database. I have annual data for my firm (gvkey). I want to calculate the market to book Ratio. Unfortunately, the annual dataset has no variable to calculate the market value of a firm. So I have to take the daily closing Prices (prccd). In order to get the variable prccd into my annual dataset, I have to merge these datasets.
Then, I used the following code to merge the datasets:

*Merge*
use fundamental , clear
keep if fic =="UK"
gen month = month(datadate)
gen year = year(datadate)
save"UK/fundamental ", replace
********************************
use security_file,clear
keep if fic =="UK"
gen month = month(datadate)
gen year = year(datadate)
save"UK/security_file", replace
********************************
use "UK/fundamental ",clear
merge 1:1 gvkey datadate using "UK/security_file"
drop if _merge==2
drop _merge
save "UK/combined",replace

doing this, I always receive the following error code:

variables gvkey year month do not uniquely identify observations in the using data
r(459);

Can somebody help?
Thanky you in advance

[William Lisowski]

If this is your error message

variables gvkey year month do not uniquely identify observations in the using data
r(459);

the your merge command was not what you typed in post #1, it was

merge 1:1 gvkey year month using "UK/security_file"

The question is, what is your objective? You want to calculate the market to book ratio. But you have the book value only annually and you have daily closing prices.

So among the year's worth of daily closing prices, how to you want to turn those 250+ daily values into a single market value? Or is it something else you want to do?

The other was of resolving your error message is to

merge 1:m gvkey year month using "UK/security_file"

which says you have 1 observation of each gvkey/year/month in your fundamental dataset but multiple observations in your security dataset.

[max tailler]

First thank you for your reply, I need to calculate market to book ratio (Tobin's q )
this is right for duplicator
destring gvkey,replace
gsort gvkey fyear
duplicates drop gvkey fyear , force
xtset gvkey fyear

[William Lisowski]

duplicates drop gvkey fyear , force

This seems unlike to give the results you need for your calculation.

If you are talking about applying this logic to your daily closing prices, what will happen is that exactly one of the days will be retained and all the others will be dropped. And you don't know which day will be retained with its closing price.

My question stands: you have 250 or more days of closing prices for each security in each fiscal year. Which one do you want to retain to calculate Tobin's Q for that security in that fiscal year? or do you want an average? or what?

What you have done ignores the question and simply chooses a day with no justification for the day chosen.

You need to decide you want, not leave it to Stata to choose.

[max tailler]

I have 250 days of closing price for each security in each fiscal year
gvkey iid datadate
001932 01W 02jan1991
001932 01W 03jan1991
001932 01W 04jan1991
001932 01W 07jan1991
001932 01W 08jan1991
001932 01W 09jan1991
001932 01W 10jan1991
001932 01W 11jan1991
001932 01W 14jan1991
001932 01W 16jan1991
001932 01W 17jan1991
001932 01W 18jan1991
001932 01W 21jan1991
001932 01W 22jan1991
001932 01W 23jan1991
001932 01W 24jan1991
001932 01W 28jan1991

[max tailler]

this is Tobin’s q who i measure