Quantiative programming tutorial VBA Excel, c++ etc.

Batman28,

I started with Lotus via DOS in 1990, now I use Excel.

But ignoring the more esoteric/specialist features and functions of today's Excel, progression isn't apparent.

To reiterate, my original Lotus could be contained on, and run from, a single floppy; my current Excel.exe file alone is c 9mb on Excel XP. And I need few, if any, of the functions.

Further, I would argue Lotus macros and the Command Language was more flexible and intuitive than anything from Excel.

Grant.
 
Quote from Grant:

Batman28,

I started with Lotus via DOS in 1990, now I use Excel.

But ignoring the more esoteric/specialist features and functions of today's Excel, progression isn't apparent.

To reiterate, my original Lotus could be contained on, and run from, a single floppy; my current Excel.exe file alone is c 9mb on Excel XP. And I need few, if any, of the functions.

Further, I would argue Lotus macros and the Command Language was more flexible and intuitive than anything from Excel.

Grant.
More...

that's interesting.. i've never had the opportunity to look at it.. i guess everyone just prefers the language they already know. it's like comparing English to Manderine.. I actually know someone who does wonders with pricing using "lingo" in Macromedia director.. apparently it's really flexible.. i guess each languages suits a different kind of personality - organisation, clarity and logic.. (btw to tell u the truth im a delphi man myself and in my view it's better than all the rest - only if borland known how to market it well..)..


p.s. here is a very good site if anyone wants to learn "lingo": http://www.vtc.com/products/directormx2004lingo.htm
 
Quote from Batman28:

thanks for all those contributing.. but pls if u do contribute, try to explain what you're doing and elaborate a little without just given the code..


Grant, your code seems to be ok, but you're using lotus!? I'm not sure how it compares, but i think this is my first time hearing someone uses it.. in terms of efficiency, flexibility and scalability, i definately recommnd excel.

this time, we are going to compare our black&scholes model with the binomial option pricing model.. we will use compare a BS call with a binomial european call.

the code for the BS is same as before. but for the binomial we creat a new function called BinomialECall:

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

Function BinomialECall(S, K, T, rf, sigma, n)
u = Exp(sigma * ((T / n) ^ 0.5))
d = Exp(-sigma * ((T / n) ^ 0.5))
r = Exp(rf * (T / n))
rn_u = (r - d) / (r * (u - d))
rn_d = 1 / r - rn_u
BinomialECall = 0
For i = 0 To n
BinomialECall = BinomialECall + Application.Combin(n, i) * rn_u ^ i * rn_d ^ (n - i) * Application.Max(S * u ^ i * d ^ (n - i) - K, 0)
Next i
End Function

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

if you don't know the binomial model, have a look at http://www.global-derivatives.com/options/european-options.php to see what we are trying to achieve with the above code.

I hope everyone's familiar with excels referencing now.. so now we're going to make things look a bit nicer.

right-click on excels tool bar, and tick the "forms" box, so we have the forms tool appear. click on "Toggle Grid" on this tool bar, which is 2nd last.

now lets add a scroll bar. right-click on the tool bar again, and this time make sure the "Control Toolbox" is ticked. when it appears, click on a "scroll bar" and drop one anywhere on your spreadsheet. now on the same tool bar (control toolbox), click "design mode", which is the first button on the left. this allows us to edit the scroll bar. right click on the scroll bar you made, and then click "properties". on this properties setting, set the "max" to 100. this is the maximum value of the scroll bar. leave the min at 0. now in "Linked Cell", type J7 so it corresponds to the price we have on our spread sheeet. now close it, and click on the design mode button again to get out of it.

lets also make a bar chart. click chart wizard on excels standard tool bar, select a bar chart type, then click next. now click on series, and click add. for the name of series one, reference it to H14 which referes to the binomial name. for its value, references it to i14. add another series in the same way for BS. u can change the colour if u like.

and now u can play around changing the price of the security conveniently with the scroll bar and see how the two different call prices change.. that again took us only 10 min.. worked attached..
More...



Batman - great thread . . .

Where did you jump for the BScall to this Eurotrash(kidding).

I followed along but then got lost b/c I thought we were inserting a scroll bar into the original BScall workbook????

I d/led this european call sheet though . . .it didn't work for right off . . I'm going to fool around for a bit.


You think you post some more stuff?
 
Batman - my file is not working correctly.

Let me post it to see if you can catch what is happening.

One thing that is really annoying is that the scroll bar does not allow the user to scroll right now. Anytime you place the mouse cursor over the sb, it just goes into that resize mode.

Thanks.
 

Attachments

Quote from uncleTom:

Batman - my file is not working correctly.

Let me post it to see if you can catch what is happening.

One thing that is really annoying is that the scroll bar does not allow the user to scroll right now. Anytime you place the mouse cursor over the sb, it just goes into that resize mode.

Thanks.
More...

hey,

i don't see anything wrong with it. the scroll bar works fine in my excel. strange. did u upload the right file? it seems to be working fine.

ive takin a little rest from et, actually from the computer too ..rest my eyes a bit lol ill hopefly put up something different soon when i can..
 
I don't know, must have been a problem within my Excel. .

I reopened the file I posted and it worked fine . . .

I'm going to build a little more on it.
 
dsss27

Did you manage to vectorize the function BlackSholes()? If so, could you post an image of the end result, ie the output/calculated figures?

Thank you.


Grant.
 
You must log in or register to reply here.
Back
Top