What's an excel formula for tick-by-tick Implied Volatility? (IB's data isn't real-time)

d0rian said:
OK thanks. And...not that I'm not appreciative of your replies to this thread, but feels like I'm in exactly the same boat as I was at the outset: just how do I do that? How do I "build my own binomial model"?
More...
Black Scholes and/or binomial model calculate an option price given IV, t, risk free rate and dividend rate. BS can approximate binomial using continuous rate for dividend. Calculating Options using BS with Excel is quite easy as all the functions are available.

Doing the reverse, i.e., calculate IV giving option price, t, risk free and dividend rates is harder. I had to do it by an iterative process. Using Excel to do iteration is kind of cumbersome unless you program it in Fortran or C++ for example.

If you could program in C++ that would be the way to go.
 
ironchef said:
Black Scholes and/or binomial model calculate an option price given IV, t, risk free rate and dividend rate. BS can approximate binomial using continuous rate for dividend. Calculating Options using BS with Excel is quite easy as all the functions are available.

Doing the reverse, i.e., calculate IV giving option price, t, risk free and dividend rates is harder. I had to do it by an iterative process. Using Excel to do iteration is kind of cumbersome unless you program it in Fortran or C++ for example.

If you could program in C++ that would be the way to go.
More...
There are some closed-form approximations that can make the BS IV calculation easier.
 
d0rian said:
My understanding is that BS is a better option for European style options (rather than American, which is what I'm dealing with)...but would each model result with dramatically different IV figures? If not, then I'm not too picky...
More...
I think in most cases the difference is not significant unless you need decimal accuracy.
 
Martinghoul said:
There are some closed-form approximations that can make the BS IV calculation easier.
More...
Yes, I just took a quick look at the paper you posted prior. Very interesting. I need to read through it more carefully.

Thanks for the link.
 
Martinghoul said:
You should start with the simpler case which computes IV for European options using the traditional methods. There is ample literature on that, such as:
http://papers.ssrn.com/sol3/papers.cfm?abstract_id=952727
More...
Thank you for the link.

I just finished reading the paper. I understand the concepts but the math is kind of difficult for me and the closed form equation is still quite messy, a 31 parameter equation. A Tayler series expansion is easier.

I don't know how easy is to set up in Excel and calculate it real time. They also ended up needing some simple iterative "polishing" process to get the accuracy to match iterative BS.

Since the paper started with a normalized BS, what do you think if I just set up a look up table of C/IV in Excel with normalized stock/strike, then convert to the correct stock/strike if I want quick realtime IV. Doing any iterative process is difficult in Excel but a look up table is very easy.

Regards,
 
ironchef said:
Thank you for the link.

I just finished reading the paper. I understand the concepts but the math is kind of difficult for me and the closed form equation is still quite messy, a 31 parameter equation. A Tayler series expansion is easier.

I don't know how easy is to set up in Excel and calculate it real time. They also ended up needing some simple iterative "polishing" process to get the accuracy to match iterative BS.

Since the paper started with a normalized BS, what do you think if I just set up a look up table of C/IV in Excel with normalized stock/strike, then convert to the correct stock/strike if I want quick realtime IV. Doing any iterative process is difficult in Excel but a look up table is very easy.

Regards,
More...
Try this then, should be easier...

http://papers.ssrn.com/sol3/papers.cfm?abstract_id=567721
 
ironchef said:
I will take a look at this one too. If I keep this up I might as well enroll and get my PhD in finance.:vomit:

You must be teaching finance or investment at some University and a professional trader. :thumbsup:
More...
Nae, I don't teach nuthink... I just sit here, buy high, sell low, that sorta thing.
 
You must log in or register to reply here.
Back
Top