Quantcast
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Modeling greeks in Excel

Discussion in 'Options' started by Ryan Simmen, Jun 15, 2016.

  1. Ryan Simmen

    Ryan Simmen Well-Known Member

    Does anyone know how to replicate the TOS Volatility Smile, OV, or ONE greek calculations in Excel? I'm familiar with RTD, but not exactly sure how these models are calculated.

    I'm also considering downloading historical data and building my own backtesting software with plans to eventually give back to the community.

    -Ryan
     
  2. I don't know if it will do the job for you, but you might check out Peter Hoadley's Excel addin.
    Website is http://www.hoadley.net/options/options.htm

    Also, historical options data is available, but it is not cheap.
     
  3. Ryan Simmen

    Ryan Simmen Well-Known Member

    I've heard of Hoadley, how accurate do you think his greeks are? Also, where does everyone recommend that I download clean historical data? I'm considering purchasing from LiveVol via datashop.cboe.com or possibly ivolatility.com
     
    Last edited: Jun 16, 2016
  4. uwe

    uwe Well-Known Member

    Thanks, I didn't know about http://datashop.cboe.com - pricing seems reasonable. Does anybody have experiences with data quality?
     
  5. Capt Hobbes

    Capt Hobbes Well-Known Member

  6. Ryan Simmen

    Ryan Simmen Well-Known Member

    Thanks, I understand all this, but to be more specific I suppose what I'm asking is how can I best model the horizontal and vertical skew like OV, ONE and TOS Volatility Smile?
     
  7. stevegee58

    stevegee58 Well-Known Member

    The basic algorithm for the IV smile is to take the option chain prices and calculate the IV for each option (based on the mid for example). Then you take the IV samples do a parabolic least square fit on them. The resulting curve is the IV smile.
    Once you have the smile you can predict future IV based on strike distance from ATM assuming the smile's basic shape hasn't changed. If the VIX changes you can theoretically shift the smile curve up and down based on it.
    This is easy to do in software like a C++ program but I don't know how to do it in Excel.
     
  8. Ryan Simmen

    Ryan Simmen Well-Known Member

    Ah okay so you're suggesting using a single point in time to construct the curve. I thought CEV models leveraged historical prices and this is what TOS, ONE and OV implement in their algorithms. Do you feel that this simpler calculation is more accurate / predictable?
     
  9. stevegee58

    stevegee58 Well-Known Member

    Today's the first day I've ever heard of CEV so I can't speak to how good it is. I stick with simpler algos that I understand in my own programs and they're adequate.
    BTW I'll bet TOS doesn't use CEV but the curve-fit IV smile for their risk graph.
     
  10. N N

    N N Well-Known Member

    This is a somewhat educated guess, tbh I've never done this, I believe modelers utilize historical data to generate a model of movement of the IV curve based on scenario analysis. What if market is down 5% and VOL up 20%. Using correlation, principal component analysis, they can get a ballpark 'estimate' of how the curve shifts based on the what-if scenarios...

    Also the curve can shift in two ways, sticky by strike (movement along the smile) and sticky by delta (movement of the whole smile itself) . That matters a lot when modeling for IV shifts.

    I don't think it is that difficult for someone with a bit of programming, the challenge becomes if you want to update the model real-time and with new information I think. But this is simply my best guess.
     

Share This Page