Quantiative programming tutorial VBA Excel, c++ etc.

B

Batman28

I like to start this thread as a tutorial, discussion on learning quantiative programming.. I've seen some people here messaging not knowing how to prgramme and think it's hard.. and so this is a tutorial thread on quantiative programming to show how simple it can be.. so get involve n learn and others who do pls contribute.

!!IMPORTANT: BEFORE CONTINUING, you need to make sure that the MACRO SECURITY SETTING in Excel is set to LOW to allow the macro function to operate. you do this by going to "Tools">"Macro">"Security" and then select "Low" at the bottom. and then restart Excel. if you're concerned, set it back to where it was before after you've gone through this..IMPORTANT!!

i will start with the simple options pricing for the Black-Scholes call price in Excel VBA.. since u all should have VBA.. if you don't know what the B-S formula is about, read a book..

I assume you are at least familiar with Excels spreadsheet and know what A1 or G5 means..so lets open Excel first..

in A1, type "Underlying Price"

in A2, type "Strike Price"

in A3, type "Risk Free Rate"

in A4, type "Maturity"

in A5, type "Volatility"


in C1, type "Black Scholes Call Price"


that was pretty easy. well done. now with here with the fun part.

on Excel top menu bar, click "Tools", then "Macro" and then "Visual Basic Editor".. this opens up the VB..

now here we are going to create a FUNCTION that calculates the B-S call price.

let's call this function BlackScholes. so in that clear part of the screen we start typing away our code. for now copy and past the code below into the editor: (not the ----- lines)

----------------------------------------------

Function BlackScholes(Underlying, Strike, RiskFree, expTime, Volatility)

d1 = (Log(Underlying / Strike) + RiskFree * expTime) / (Volatility * Sqr(expTime)) + _
0.5 * Volatility * Sqr(expTime)

BlackScholes = Underlying * Application.NormSDist(d1) - Strike * Exp(-expTime * RiskFree) * _
Application.NormSDist(d1 - Volatility * Sqr(expTime))

End Function

------------------------------------------

so what is in this code? we see that the b-s function has a number of parameters shown inside the bracket.. what the hell is "d1"? well if you look at the actual formula it becomes clear (http://en.wikipedia.org/wiki/Black-Scholes).. but note how simply we type it in VBA when you compare it to the actual formula.. u can play around with it later.


so now that we have typed in our code, lets get back to our spreadsheet.

So we created a function. let's now use it.

In D1, type the following:

=BlackScholes(B1,B2,B3,B4,B5)

got an idea what the B's are? YES - they correspond to the parameters we identified in our coding for the BlackScholes function.

but look, on the spread sheet, B1 to B5 are blank. we have to fill them in now. (now you know why we created the labels in A1 to A5 earlier - so we know what data we are putting in.. nice..)

So lets fill them in. lets say that the underying price is $60. so we'll put "60" in B1. lets say the strike price is $50, so we'll put "50" in B2. lets also assume the risk free rate is 5.25%, so put "0.0525" in B3. and let's assume matury and volatility are 0.3 and 0.1 respectively. this means we type in "0.3 in B4 and 0.1 in B5.

Oh now look at D1, it gives us the call price.. it's $10.7814..how nice.

that's it! and that only took us 10 min. next time, we can look at how we can actually infer the "volatility" part of the model using a GARCH model.. or we'll do something else in C++ or Python.. I welcome everyone to join in, contribute and take part.
 
Yeah Batman, we need more of you.... Keep spreading the hope

He he he




Quote from Batman28:

I like to start this thread as a tutorial, discussion on learning quantiative programming.. I've seen some people here messaging not knowing how to prgramme and think it's hard.. and so this is a tutorial thread on quantiative programming to show how simple it can be.. so get involve n learn and others who do pls contribute.

!!IMPORTANT: BEFORE CONTINUING, you need to make sure that the MACRO SECURITY SETTING in Excel is set to LOW to allow the macro function to operate. you do this by going to "Tools">"Macro">"Security" and then select "Low" at the bottom. and then restart Excel. if you're concerned, set it back to where it was before after you've gone through this..IMPORTANT!!

i will start with the simple options pricing for the Black-Scholes call price in Excel VBA.. since u all should have VBA.. if you don't know what the B-S formula is about, read a book..

I assume you are at least familiar with Excels spreadsheet and know what A1 or G5 means..so lets open Excel first..

in A1, type "Underlying Price"

in A2, type "Strike Price"

in A3, type "Risk Free Rate"

in A4, type "Maturity"

in A5, type "Volatility"


in C1, type "Black Scholes Call Price"


that was pretty easy. well done. now with here with the fun part.

on Excel top menu bar, click "Tools", then "Macro" and then "Visual Basic Editor".. this opens up the VB..

now here we are going to create a FUNCTION that calculates the B-S call price.

let's call this function BlackScholes. so in that clear part of the screen we start typing away our code. for now copy and past the code below into the editor: (not the ----- lines)

----------------------------------------------

Function BlackScholes(Underlying, Strike, RiskFree, expTime, Volatility)

d1 = (Log(Underlying / Strike) + RiskFree * expTime) / (Volatility * Sqr(expTime)) + _
0.5 * Volatility * Sqr(expTime)

BlackScholes = Underlying * Application.NormSDist(d1) - Strike * Exp(-expTime * RiskFree) * _
Application.NormSDist(d1 - Volatility * Sqr(expTime))

End Function

------------------------------------------

so what is in this code? we see that the b-s function has a number of parameters shown inside the bracket.. what the hell is "d1"? well if you look at the actual formula it becomes clear (http://en.wikipedia.org/wiki/Black-Scholes).. but note how simply we type it in VBA when you compare it to the actual formula.. u can play around with it later.


so now that we have typed in our code, lets get back to our spreadsheet.

So we created a function. let's now use it.

In D1, type the following:

=BlackScholes(B1,B2,B3,B4,B5)

got an idea what the B's are? YES - they correspond to the parameters we identified in our coding for the BlackScholes function.

but look, on the spread sheet, B1 to B5 are blank. we have to fill them in now. (now you know why we created the labels in A1 to A5 earlier - so we know what data we are putting in.. nice..)

So lets fill them in. lets say that the underying price is $60. so we'll put "60" in B1. lets say the strike price is $50, so we'll put "50" in B2. lets also assume the risk free rate is 5.25%, so put "0.0525" in B3. and let's assume matury and volatility are 0.3 and 0.1 respectively. this means we type in "0.3 in B4 and 0.1 in B5.

Oh now look at D1, it gives us the call price.. it's $10.7814..how nice.

that's it! and that only took us 10 min. next time, we can look at how we can actually infer the "volatility" part of the model using a GARCH model.. or we'll do something else in C++ or Python.. I welcome everyone to join in, contribute and take part.
More...
 
Yo Batman, ever tried using GRRRRRRR volatility estimation model? Apparently it is very effective way of telling how useful your GARCH estimates are. Check the correlations and you will see it for yourself

Batman?

He he he
 
Quote from WinDiff:

Yo Batman, ever tried using GRRRRRRR volatility estimation model? Apparently it is very effective way of telling how useful your GARCH estimates are. Check the correlations and you will see it for yourself

Batman?

He he he
More...

lol hehehe u ok? GRRRRR?.. actually im not too concerned with the accuracy of the model or theory.. here are are purely concerned with learning quantiative programming.. plus we have GUASS, RATS, Ox, Eviews, scilab and even R and others to kick around with the arch/garch families..
 
I've had someone msg me who's had problem getting the function above to work.. find attached a copy of our work, open the VB editor from excel, look through it, and see what you did differently.. hope that helps..
 

Attachments

Batman28,

I've been creating option pricing/modelling since Lotus 1-2-3 could be contained on a single floppy.

However, I never made the transistion to VBA (in Excel). Some of my models are mothers in terms of size/detail/calculation.

I recently upgraded my pc to a dual AMD 64 Pro chip. Prior to this I had a 500 mhz (don't laugh). One sheet would take 1 min 45 seconds to calculate (now only 5 secs).

All calcs are entered directly into cells. Is VBA more efficient?

I've have tried to learn VBA but concluded that learning a foreign language would be more beneficial than a computer language.

To activate macros/calc's I'm still stuck with a Lotus imported trigger (control-a).

Here's an example I use for calculating implied volatility (IV):

We have the inputs for the model (eg Black-76 for index options) - underlying, strike, premium, rate, time.

We need to determine the unknown implied. First, let IV be 10%. This will result in premium reflecting a 10% IV.

Compare this to the market premium. If the market premium is greater than the theoretical, the IV is too low and needs to increased, and vice versa.

Macro:

{let IV,.1}~{calc}
{if A<=0.5}{quit}
{let v,v+.00125}{calc}[this is branch w]
{if A<=0.5}{quit}
{branch w}

Explanation:

Let IV be 10% and calculate.
If A (the difference between market and theoretical) is <= 0.5, quit (ie, it is solved).
[if the diff is greater] add 0.0125% to IV and calculate.
If diff is <= 0.5, quit.
[If not, go back] {branch w} and add ( a further) 0.0125% to IV.

Basically, the calc will loop until the the theoretical and market premiums converge.

Any comments appreciated.

Grant.
 
Batman28,

I've read your original post.

I use a seperate calculation for each strike, expiry, call and put.

So for example, if Sep expiry has 25 strikes for calls and puts, there are 50 seperate calcs.

There could be up to 50 strikes and 7 expiries (DJ Euro Stoxx trades out to Dec 2015 although last trades are at Dec 2010).

Multiply this by the potential indexes - DAX, Stoxx, SMI, FTSE, CAC, etc and you can see the complexity.

Would VBA reduce all this duplication and thus increase efficiency?

Grant.
 
Attached is the Black Scholes code, using Microsoft Visual Studio.NET 2003, as a text file. If you are interested, you can copy and paste the code into .Net and compile it to get the Black Scholes results as well as all the greeks.
 

Attachments

I just saw that my attachment doesn't open in a text file. If anyone wants it please PM me with your email and I can send you the code and the .exe file (ET won't allow me to attach an exe file).
 
You must log in or register to reply here.
Back
Top