General Topics
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Excel macro

Discussion in 'Trading Software' started by Grant, Sep 24, 2006.

1. ### Grant

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.

Grant.

File size:
118 KB
Views:
144
2. ### brianp5539

Hi I am brand new to this sight, my email address is brianpanicco@telkomsa.net

Vba code for implied vol for European options using B&S and Balck76 for futures options is simple enough. Email me a request and i will send you the excel VBA functions free of charge

4. ### Grant

Bob111,

Thank you for the info. The site you referenced looks, from a quick glance, a mine of excellent resources.

I think I'll be spending some time here.

Grant.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: