********************************************* * * * Excel-Funktionen und VBA-Funktionen * * * ********************************************* Es gibt Excel-Funktionen oder auch Worksheet Functions, die man auf einem Tabellenblatt (engl.: worksheet) mit der Syntax =functionname(parameters) benutzen kann. Eine Uebersicht ueber alle worksheet functions bekommt man, wenn man auf das fx-Symbol, das Insert Function Symbol, klickt. Etwa: -> Math & Trig -> Engineering (Bessel-Funktionen, komplexe Zahlen) -> Date & Time Wir wollen eine Benutzer-definierte Funktion schreiben, die man dann mit der Syntax =BlackScholesPrice(S0,K,T,call,sigma,r) auf einem Tabellenblatt benutzen kann. Diese Funktion soll den Black-Scholes Preis einer Standard-Kauf-Option (engl.: call-option) oder einer Standard- Verkaufs-Option (engl.: put-option) berechnen. Der Name dieser Funktion, BlackScholesPrice, wird dann ebenfalls im Insert Function Dialog-Fenster (klick auf fx-Symbol) sichtbar sein, unter der Kategorie Benutzer definiert oder User Defined. Damit Ihnen diese Aufgabe auch sinnvoll erscheint, machen wir zunaechst etwas finanzmathematischen Hintergrund: =================================================================== Finanzmathematischer Einschub: Was sind Optionen oder Derivate? Fuer eine Option braucht man zunaechst mal ein der Option zugrunde liegenden Basiswert oder das sogenannte underlying. Das muss ein liquide handelbares asset sein wie etwa eine Aktie, ein Aktienindex, ein Waehrungswechselkurs oder Roh- stoffe wie Oel, Gold oder Silber. Sei S_t der Preis dieses underlyings zur Zeit t. Eine Option hat eine Laufzeit (maturity), etwa 1 Jahr, 3 Jahre oder auch nur 1 Monat. Die Laufzeit wird typischerweise mit dem Buchstaben T bezeichnet. Eine Option mit Basiswert S_t ist dann, rein mathematisch gesprochen, einfach eine Auszahlungsfunktion Options_Auszahlung = f(S_T) wobei die Funktion f voellig beliebig sein kann. Das heisst, der Verkaeufer der Option f (typischerweise eine Bank) ver- pflichtet sich, bei Faelligkeit T der Option dem Kaeufer der Option den Betrag f(S_T) auszuzahlen. Haeufig gehandelte Optionen sind Standard-Kauf und Standard- Verkaufs-Optionen, sogenannte Call- und Put-Optionen. Diese besitzen einen sogenannten Ausuebungspreis oder Strike K als Parameter (typischerweise ist der gleich dem momentanen Aktien- preis S_0) und die Auszahlung ist dann definiert durch f_call(S_T) := max{ S_T - K , 0 } f_put(S_T) := max{ K - S_T , 0 } Da man also fuer eine solche Option in der Zukunft bei t=T potentiell Geld bekommen kann, haben diese Optionen schon jetzt bei t=0 einen positiven Wert, der Kaeufer muss der Bank bei t=0 den Optionspreis bezahlen. Es ist nun ein fundamentales Resultat der Finanzmathematik, dass solche Optionen ueberhaupt einen im folgenden Sinne eindeutig definierten Preis=price(f) haben: Ist jemand bereit, fuer die Option f mehr als price(f) zu zahlen oder bietet eine andere Bank diese Option fuer einen Preis kleiner als price(f) an, so kann die Bank eine Handelsstrategie in dem der Option zugrunde liegenden Basiswert S_t aufsetzen, so dass das Gesamt-Portfolio aus ver- oder ge-kaufter Option und Handels- strategie immer einen positiven Gewinn erwirtschaftet, egal, ob das underlying steigt, faellt oder neutral bleibt. Diese Tatsache wird auch als das no-arbitrage pricing principle bezeichnet. Wie das genau funktioniert, werden wir uns ein bischen auf dem naechsten Uebungsblatt 4 anschauen (heute haben wir das Uebungsblatt 3). Im Fall von Standard Call- oder Put-Optionen wird jetzt dieser eindeutig festgelegte Preis durch die Black-Scholes Formeln gegeben: price(f_call) = S_0 N(d1) - K exp(-rT) N(d2) (1) price(f_put) = -S_0 N(-d1) + K exp(-rT) N(-d2) (2) mit N(d) := int_{-infinity}^d exp(-x^2/2) dx/sqrt(2 pi) und d1 = [ log(S_0/K) + (r+sigma^2/2)T ] / [ sigma*sqrt(T) ] d2 = d1 - sigma sqrt(T) Dabei sind: r = jaehrlicher Zinssatz (etwa 2%) sigma = jaehrliche Volatilitaet des underlyings (etwa 20%) S_0 = underlying Preis jetzt (etwa 12000 fuer den DAX) K = Ausuebungspreis oder Strike der Option (etwa = S_0) T = Laufzeit der Option in Jahren (etwa 0.25 fuer 3 Monate Laufzeit) Konkrete Preise von Call- und Put-Optionen auf den DAX findet man etwa auf den Seiten der Deutschen Termin Boerse unter http://www.eurexchange.com/exchange-en/products/idx/dax/DAX--Options/17252 Ende Finanzmathematischer Einschub ===================================================================== In der Funktion BlackScholesPrice(S0,K,T,call,sigma,r) sollen also die Formeln (1) und (2) bereitgestellt werden. ' ' now let's do the actual coding ' Schauen wir uns jetzt die Funktionen an, die in VBA vordefiniert sind. Dazu oeffnen wir den VBA-Editor mit Alt-F11 und rufen dann mit der Taste F1 die VBA-Hilfe-Seiten auf. Dann klicken wir auf Visual Basic for Applications Language Reference -> Visual Basic Language Reference -> Functions und scrollen etwas herunter und klicken dann etwa auf Math Functions. Man stellt fest, dass das deutlich weniger Funktionen sind als die- jenigen, die im Insert Function Dialog-Fenster als worksheet functions zur Verfuegung gestellt werden. Etwa fehlt die FACT(n)-Funktion fuer Fakultaeten oder die COMBIN(n,k)-Funktion fuer Binomial-Koeffizienten oder die ASIN(y)-Funktion fuer die Umkehrfunktion arcsin vom Sinus. Scrollt man zu Derived Math Functions, bekommt man immerhin einen Vorschlag, wie man den arcsin durch die VBA-Funktion Atn(), den arctan, ausdruecken kann. Die meisten worksheet functions, aber nicht alle, koennen mit der Syntax (als Beispiel fuer die COMBIN(n,k)-Funktion) result = Application.WorksheetFunction.Combin(10,2) oder result = Excel.WorksheetFunction.Combin(10,2) auch in VBA benutzt werden. Die Syntax result = WorksheetFunction.Combin(10,2) scheint auch zu funktionieren. Allerdings sind worksheet functions, die man in VBA benutzt, deutlich langsamer als VBA functions. Etwa ist die Funktion Excel.WorksheetFunction.Ln() deutlich langsamer als die VBA Funktion log(), die beide dasselbe Resultat liefern. Dazu werden wir in der Uebung ein Beispiel betrachten.