>> I am very interested in what you are doing. I have done (am doing) similar, and find it difficult ( a huge amount of >> work) but very educational. The benefit of the things we learn by these endeavors, will likely outweigh the >> original goal of the task. Yes, I have done extremely complicated simulations for stocks but what I like and hear about options is the "non directional" which seems to open up new possibilities. The idéa is to start doing credit spread and iron condors backtests where the simulation will take in: IV, bid-ask spreads, volume, delta, theta, strike width, earnings, adjustments, stoploss, take profit, DTE, combinations of monthly options and weekly options, roll or not roll etc etc in as many different combinations as possible. I am thinking of in the million range combinations. Also make the optimization artficial month after month to find the "best" combinations in regards to predefined inputs as biggest profit and lowest draw downs where the optimization changes the trading month after month in the optimization. Have you done anything like this?
Wow! You are working on your PhD in finance, writing your PhD thesis and I am still struggling to write a junior high school term paper on economics. I feel so inadequate. What you are doing cannot be done with EXCEL. Since I only know how to do my analysis on EXCEL, I better start to learn programming in C. Best wishes.
Yes, I try to prepare for the task as much as I can, I can do it but it will take some time I beleive But it will be very interesting to do. I don't think it is possible to do this in excel, it might be a little to complex but if you know much in excel you will soon learn how to program in C.
I can do a lot in excel except iterations, i.e., do loops. If I can figure out a way to do a do loop in excel, I would be a very happy camper because it is usually very easy to import data from the internet in excel files and work on them. If anyone here knows how, I appreciate your help.
You can use VisualBasic in Excel. If you don't want to do that, this may be of use: "http://people.revoledu.com/kardi/tutorial/Excel/Iteration.html"
Never mind. I just asked Dr. Google and found a few websites that gave me a tutorial. I will find out if I can calculate IV using an iterative process and the BS Options pricing model with Excel and VB. Thank you for your suggestion.
Below is one method I used in VB, if this helps. ------- Function ImpliedCallVolatility(UnderlyingPrice, ExercisePrice, Time, Interest, Target, Dividend) High = 5 Low = 0 Do While (High - Low) > 0.0001 If CallOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2, Dividend) > Target Then High = (High + Low) / 2 Else: Low = (High + Low) / 2 End If Loop ImpliedCallVolatility = (High + Low) / 2 End Function ----------------- Function ImpliedPutVolatility(UnderlyingPrice, ExercisePrice, Time, Interest, Target, Dividend) High = 5 Low = 0 Do While (High - Low) > 0.0001 If PutOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2, Dividend) > Target Then High = (High + Low) / 2 Else: Low = (High + Low) / 2 End If Loop ImpliedPutVolatility = (High + Low) / 2 End Function