Close Price in OptionMetricsProcessing.R

[xiaoqyaoyao]

The OptionMetrics Manual describes the close field in secprd as "If this field is positive, then it is the closing price for the security on this date. If it is negative, then it is the average of the closing bid and ask prices for the security on this date. In case there are no valid bid or ask for the day, the record does not appear in the table at all." So I think there should be an abs function for close in this script.

[tomz23]

Thanks! Will check when we update data and code early this year.

[chenandrewy]

I took a look, finally. It turns out we don't need to worry about negative close values because the query filters for volume > 0. I took a quick look at the output and didn't find any negative close values in 1999. The query is below

  ## download daily data with lots of filters
  res <- dbSendQuery(
    wrds
    ,paste0("
  select a.secid, a.date, a.close 
  ,b.optionid, b.cp_flag, b.strike_price, b.impl_volatility "
  ,"from optionm.secprd"
  ,year
  ," as a left join optionm.opprcd"
  ,year
  ," as b   
  on a.secid = b.secid and a.date = b.date
  where (b.strike_price != 'NaN') and (b.impl_volatility != 'NaN')
  and (b.exdate - a.date >= 10) and (b.exdate - a.date <= 60)
  and (
    (b.cp_flag = 'C' and b.strike_price/1000/a.close > 0.95 and b.strike_price/1000/a.close < 1.05)
    or
    (b.cp_flag = 'P' and b.strike_price/1000/a.close < 0.95 and b.strike_price/1000/a.close > 0.80)
    )
  and a.volume > 0
  and b.impl_volatility > 0.03 and b.impl_volatility < 2.0
  and (b.best_bid+b.best_offer)/2 > 0.125
  and b.open_interest > 0 and b.volume != 'NaN'    
  and extract(day from a.date) >= 23
    ")
  )    
  tempd <- dbFetch(res, numRowsToPull)

Using year = 1999, I get the following: