Conditional variable matching based on several factors

[Frank de Vries]

Dear all,

In order to compute implied volatility option spread between call and put option with same duration (expiration date minus current date) and strike price, I have to match them per firm per day. In the sample I pasted one day of Apple Inc. option data, which I have already transformed and sorted. More specifically I need to:

Match call and put options with the same strike price and duration (per day per firm)
If there are several matched pairs, choose the one with shorter duration

What is the best way to tackle this?

Kind regards,

Frank

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date float(option_duration put_strike_price call_strike_price) double impl_volatility
13152 16     0 20000  1.22353
13152 16     0 30000  .731966
13152 44     0 30000  .694303
13152 16     0 32500  .722562
13152 44     0 32500  .666918
13152 16     0 35000  .698849
13152 44     0 35000  .667704
13152 16     0 37500  .727589
13152 44     0 37500   .68995
13152 16     0 40000  .855763
13152 44     0 40000  .745606
13152 16     0 42500  .869448
13152 44     0 42500  .745948
13152 16     0 45000  .946582
13152 16     0 47500  .992811
13152 16     0 50000  .992264
13152 16 20000     0  1.17801
13152 16 30000     0  .653214
13152 44 30000     0  .656343
13152 16 32500     0  .663867
13152 44 32500     0  .654865
13152 16 35000     0  .655374
13152 44 35000     0  .655782
13152 16 37500     0  .922355
13152 44 37500     0  .667146
13152 16 40000     0  .793523
13152 44 40000     0  .675841
13152 16 42500     0  .855899
13152 44 42500     0  .677556
13152 16 45000     0  .984846
13152 16 47500     0 1.104065
13152 16 50000     0  1.21526
13152 16 60000     0 1.312646
end
format %d date

[Clyde Schechter]

preserve
keep if put_strike_price == 0
tempfile calls
rename call_strike_price strike_price
rename impl_volatility call_impl_volatility
drop put_strike_price
save `calls'
restore
keep if call_strike_price == 0
rename put_strike_price strike_price
drop call_strike_price
rename impl_volatility put_impl_volatility

joinby date option_duration strike_price using `calls'

I assume that when call_strike_price is zero the option is a put, and vice versa. The above code will give you all matched pairs consisting of a put and call that have the same date, option duration and strike price.

I do not understand "per firm per day" since there is no firm variable in your data.

I also do not understand "If there are several matched pairs, choose the one with shorter duration" because all of the matches, by construction, have the same duration.

So this is probably not what you want, but perhaps it will get you started in the right direction.

[Frank de Vries]

This does help a lot, Clyde!

This was indeed just a small part of the data with only one firm. Firms are identified by their official ticker number, I should have included that variable. By duration difference i meant that for cases with same put/call strike price option's lifespan can differ (16 or 44 days in this case). For that specific strike price I need the 16 days option and not the 44 days one.

Thanks again for your quick and comprehensive reply!

[Clyde Schechter]

So it sounds like you would modify the code in #2 by inserting the firm-identifier variable between -joinby- and -date- and adding:

by firm_identifier date strike_price (option_duration), sort: keep if _n == 1

to retain only the shortest-duration matched pair among all those found for a given firm, date, and strike price combination.

[Frank de Vries]

This was very helpful! Thanks again, Clyde.