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

D

d0rian

IB's streaming real-time data for Implied-Bid-Volatility and Implied-Bid-Volatility are NOT updated in real-time. (Acc to their CS: "For American style options are calculated by Binomial trees" and "updated on average every 2 minutes".)

But I pull IB's streaming data into Excel via DDE, and want my IV(Bid) and IV(Ask) #'s updated real-time, tick-by-tick -- how do I do this?

What is the formula to calculate IV, and what inputs do I need? Or would running the "binomial tree" formula in real-time that IB's data feed uses for dozens of option contracts at once be way too heavy a load and crash Excel?
 
Depends on the precision you need. If you really need tick by tick vol calculations you probably should consider dedicated hardware and proprietary software rather than excel (with all its throttling issues).

But confirm if you really need calculations that freqiently.
 
So sounds like you're saying that tick-by-tick real-time IV calculations would indeed be too much for Excel to handle? I mean...yes, I made the post hoping that it's something that I could indeed have Excel update in real-time. For instance, here's what the columns of my Excel spreadsheet look like as it streams in data via DDE:

DabYFm7.jpg


So for the 17-Strike contract:
- IV.B (the IV calculated from the current Bid of $0.50) is 26.0%
- IV.A (calc'd from current Ask of $1.85) is 67.9%

So I want to immediately see the new IV.B if the high Bid should, let's say, move to $0.75...but as described in my original post, while the DDE feed will indeed update the Bid value from $0.50 to $0.75, the IV.B value would remain at 26.0%, even though it SHOULD now be somewhere in the 30-35% range. IB only updates it every ~2 minutes, but I want to know IMMEDIATELY what the new IV values are.

It seemed to me like that should be straight-fwd, b/c the only variable changing is the Bid...but I don't know just WHERE between the existing 26.0% - 67.9% IV values the new IV.B will fall, which I guess is what I was hoping an Excel formula could calculate even if IB doesn't provide it. Or to put it another way, if we know that IV.B = 26.0% for a Bid of $0.50, and that IV.A = 67.9% for an Ask of $1.85, how can we calculate the new IV.B value when the Bid changes?

Hope that helped.
 
Yes. That won't require tick by tick which technically would involve a calculation every time the underlying trades.

I see your issue for illiquid names and that calculation won't be too intense if it's a small number of options (perhaps a thousand or so). But if you want to do all options then you prob need dedicated processing power.
 
So...what's the formula and inputs that I need? I'm confused as to whether you're saying that what I've described is something I'll be able to calculate myself in Excel or not...and if it is then just how do I do it?
 
It depends on how precise your model and needs are.

You aren't being very open with what you want to do, how many underlyings and options you are using so it's hard to give you more detailed advice.
 
Perhaps I'm not understanding your questions, then, since I tried to lay out exactly what I was asking, including pics and examples, in my earlier post.

re: precision, if I were to say that I wanted IV percentages it to 1 decimal place (e.g. 27.1%)...is that reasonable or a tall order? Zero decimals (e.g. 27%) would be fine too if that's the sticking point.

My Excel file only tracks the contracts on a single underlying -- I have it set up to pull in real-time data for the Calls and Puts for 4 expiry dates, with approx 12 Strikes for each date. So we're talking about ~96 different option contracts. Is that a lot? Not sure where the threshold is at which my hoped-for outcome (being able to calculate real-time IV's) becomes too much for Excel to handle...
 
d0rian said:
Perhaps I'm not understanding your questions, then, since I tried to lay out exactly what I was asking, including pics and examples, in my earlier post.

re: precision, if I were to say that I wanted IV percentages it to 1 decimal place (e.g. 27.1%)...is that reasonable or a tall order? Zero decimals (e.g. 27%) would be fine too if that's the sticking point.

My Excel file only tracks the contracts on a single underlying -- I have it set up to pull in real-time data for the Calls and Puts for 4 expiry dates, with approx 12 Strikes for each date. So we're talking about ~96 different option contracts. Is that a lot? Not sure where the threshold is at which my hoped-for outcome (being able to calculate real-time IV's) becomes too much for Excel to handle...
More...

Excel is fine for 96 options but you prob will have to build your own binomial or trinimial model to get within a tenth of a vol.
 
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"?
 
d0rian said:
IB's streaming real-time data for Implied-Bid-Volatility and Implied-Bid-Volatility are NOT updated in real-time. (Acc to their CS: "For American style options are calculated by Binomial trees" and "updated on average every 2 minutes".)

But I pull IB's streaming data into Excel via DDE, and want my IV(Bid) and IV(Ask) #'s updated real-time, tick-by-tick -- how do I do this?

What is the formula to calculate IV, and what inputs do I need? Or would running the "binomial tree" formula in real-time that IB's data feed uses for dozens of option contracts at once be way too heavy a load and crash Excel?
More...
Have you seen:
http://quant.stackexchange.com/ques...lity-using-the-binomial-options-pricing-model
 
You must log in or register to reply here.
Back
Top