Excel macro

To calculate implied volatility I have a macro on an Excel sheet whose origins lie in Lotus from many years ago. It was imported from Lotus to Excel.

The macro is triggered via control-a and still works perfectly.

It was written in Lotus Command language whereby the instructions are enclosed in curly brakets.

My questions are, can this easily be converted to a VBA macro; if so, would it be faster?

Please refer to the attached file. This is the procedure for call options:

The letters at left designate the named cells at right.

1. dh: Let the expiry month [exp] be the first (current) expiry month, eg Sep

2. di: Let the implied volatility [v] be the default implied volatility [iv], eg 10%

3. dj: If all the implieds (for this expiry month) are calculated, go to next expiry month (“eh” is the start of the next calculation for the following month)

4. If the call theoretical value [c_th_v] is less than the minimum value [mv] (the difference between the theoretical price and market price), go to dk

otherwise

5. If the call premium (last trade) [cp] is greater than the theoretical call [th_c] , then increase the implied volatility [v] by the increment [inc] (eg 0.1%)

or

6. If the call premium (last trade) [cp] is less than the theoretical call [th_c] , then decrease the implied volatility [v] by the increment [inc] (eg 0.1%)

7. dk: if the strike [x] (of the call premium) = the first strike [cx_1] (eg 5000), let the corresponding (first) implied volatility [cv_1] be the (newly determined) implied volatility [v].

7. dk is repeated for all strikes.

The whole process is repeated for all calls/puts for all expiries.

The result would be displayed as, eg:

Calls SEP OCT NOV
5000 17% 20% 23%
5050 18% 19% 21%

And so on.

 

what advise you are looking for ? Programming ( not me) or "rules and exceptions" for diff IV calculations scenarios ? There are a lot of them.

BTW , one of them is in par 5 ; you cannot use "last trade" to calculate IV. Last trade in illiquid options could of be a week ago and price of spot was diff then.

 

IVTrader,

“My questions are, can this easily be converted to a VBA macro; if so, would it be faster?”

So, essentially, it is a programming query.

Re “Last trade”, this corresponds to its time which corresponds to the underlying at that time. I think I’ve got all bases covered.

What “rules and exceptions “ are you thinking of? My primary interests are non-US index options - DAX,
DJ Euro Stoxx, SMI, CAC, FTSE. However, if you could tell me the most liquid US index options, I’ll certainly look at them.

There should have been an attachment. (disable the macro).See below.

Thank you for any suggestions

Grant.

 

can do the same using functions

Start Like this...

Function OptionFairValue(NowPrice as Variant,StrikePrice as Variant, Volitilty as Variant,DiffDays as Variante,CallOrPut as Bollion)

'Your Black-Scholtes Model Here


End Function

Will work like the sum or average function in excel

I use to write these...
I Just can not find them
GL
KaL

 

Kalzayani,

Thank you for the suggestion. Unfortunately, I'm insufficiently versed to implement your suggestion. But I certainly appreciate the gesture.

That's a cool name, by the way. Is it real (what origin) or a pseudonym?

Grant.