************************************************************* * * * Kapitel 10: Excel Add-Ins, der Excel-Solver * * und das Data Analysis Tool Pak * * * ************************************************************* Ein Excel Add-In ist im Wesentlichen zusaetzlicher, externer, VBA-Code, um zusaetzliche Funktionalitaeten zur Verfuegung zu stellen, die in der Standard-Version von Excel nicht ent- halten sind. Man koennte sie etwa vergleichen mit den Tool- boxen in Matlab oder den Packages in R. Wir haben gesehen, dass es in VBA keinen eigenen Datentyp fuer komplexe Zahlen gibt und hatten deshalb in week5.xlsm den benutzerdefinierten Datentyp cdouble definiert. Weiterhin hatten wir dort fuer diesen Datentyp die 4 Grundrechenarten Plus, Minus, Mal, Geteilt sowie die komplexe Exponential- funktion definiert. In einem VBA-Projekt moechte man typischerweise nur solchen Code stehen haben, der auch wirklich gebraucht wird; da das bei komplexen Zahlen nicht immer der Fall ist, macht es da also Sinn, ein Excel Add-In "ComplexNumbers-AddIn.xlam" zu generieren, was dann also gegebenenfalls geladen werden kann, wenn man denn mit dem Datentyp cdouble rechnen moechte. -> generate and install Add-In "ComplexNumbers-AddIn.xlam" (see also Chapter 22 of Urtis_ExcelVBA) fuer Demonstrations-Zwecke weiter unten: - packe ebenfalls eine Test-Funktion (unabhaengig von cdouble) in das AddIn, diese Test-Funktion soll dann auf einem sheet und in VBA benutzt werden - aendere den Projekt-Namen von "VBAProject" etwa zu "ComplexNumbers" (ist weiter unten beschrieben wie das geht) Bei der Erzeugung und Benutzung eines Add-Ins sind also folgende Dinge zu beachten: 1) Das Add-In wird erzeugt, indem man mit einem ueblichen .xlsm file anfaengt und das dann am Ende als .xlam file speichert. 2) Das .xlam file besteht im Wesentlichen nur noch aus VBA- Code, die Excel-Mappe mit den einzelnen worksheets ist nicht mehr sichtbar (aber im Projekt-Explorer noch vor- handen). 3) Bevor man das Add-In in einer neuen Excel-Mappe benutzen kann, muss es installiert werden. Ueber Developer->Add-Ins bekommt man ein "Add-Ins available" Fenster. Dort ist das neue Add-In zunaechst nicht aufgelistet; ueber den Button Browse muss man die Directory auswaehlen, in der man das .xlam file gespeichert hat, dann Doppel-Klick auf das file und es erscheint in dem "Add-Ins available" Fenster. 4) WICHTIG: Hat man die Schritte 1-3 ausgefuehrt, ist etwa die Funktion MyTestFunction(x) auf einem Excel-sheet verfuegbar, aber sie kann noch nicht in VBA benutzt werden, sie ist dort noch nicht bekannt. Der Datentyp cdouble ist ebenfalls noch nicht bekannt. Damit die Sachen in VBA benutzt werden koennen (also nicht nur in Excel), muss man noch "eine Referenz hinzufuegen": Das geht folgendermassen: Im VBA-Editor, aktiviere das Modul (also in das Modul- Fenster klicken), in dem der Datentyp cdouble benutzt werden soll. Dann, in der VBA-Menueleiste, gehe zu Tools->References und waehle "ComplexNumbers" aus. Dabei haben wir bei der Generierung des Add-Ins darauf geachtet, dass der VBA-Projekt Name anstatt des default Eintrags "VBAProject" zu "ComplexNumbers" umbenannt wurde, sonst haetten wir in der Liste "available references" jetzt nach dem Namen "VBAProject" suchen muessen. Den VBA Projekt Namen kann man wie folgt aendern: Im VBA Editor, im Projekt Explorer, right mouse click auf den Namen "VBAProject" und dann "VBAProject Properties" auswaehlen. In dem Feld "Project Name" kann man dann den Namen aendern. ------------------------------------------------------------- * * * Excel Solver und Analysis ToolPak * * * ------------------------------------------------------------- In der Standard Version von Excel sind 2 Add-Ins enthalten, die man bei Bedarf laden kann: Der Excel-Solver und das Analysis ToolPak. Der Excel-Solver enthaelt eine Reihe von Routinen zum numerischen Loesen von Optimierungsproblemen oder etwa zur Nullstellenbestimmung von Funktionen (was man ja als Optimierungsproblem f(x)^2 -> min schreiben kann). So steht dort etwa der Simplex-Algorithmus zum Loesen von linearen Optimierungsproblemen zur Verfuegung. Das Analysis ToolPak, von dem es offensichtlich eine Version fuer Excel-Funktionalitaeten und eine weitere fuer VBA-Funk- tionalitaeten gibt, liefert Hilfsmittel fuer statistische Aufgaben. Wir schauen uns zwei Beispiele an: Beispiel 1) Bestimmung der impliziten Volatilitaet mit Hilfe des Excel-Solvers Beispiel 2) Erzeugung von normal-verteilten Zufallszahlen mit Hilfe des Analysis ToolPaks Beispiel 1) In week4.xlsm hatten wir die Black-Scholes Formeln fuer die Preise von Call- und Put-Optionen in der Funktion BlackScholesPrice(S0,K,T,call/put,vol,r) implementiert. Zu einem am Markt beobachteten Preis price_market ist die implizite Volatilitaet definiert als diejenige Volatilitaet, die man in die Black-Scholes Formeln einsetzen muss, damit der Black-Scholes Preis gleich dem Markt Preis wird. Also: ! BlackScholesPrice(S0,K,T,call/put,impvol,r) = price_market impvol kann mit dem Solver bestimmt werden: Zunaechst ueber Developer/Add-Ins das Solver Add-In laden. Das Solver-Fenster ueber Data-Tab -> Solver oeffnen. -> now set Solver settings and calculate solution. Beispiel 2) Wir wollen 1000 normalverteilte (mean=0, stddev=1) Zufallszahlen generieren, auf einem sheet ausgeben, ein His- togramm erzeugen und mit der analytischen Normalverteilung vergleichen: Zunaechst das Analysis ToolPak ueber Developer/ Add-Ins laden. Dann laesst sich ueber Data-Tab -> Data Analysis ein Data Analysis Fenster oeffnen, in dem man etwa "Random Number Generation" auswaehlen kann. Damit lassen sich Zufalls- zahlen zu verschiedenen Verteilungen erzeugen. Schaltet man den Makro-Recorder ein, kann man sich auch den entsprechenden VBA-Code geben lassen. -> do the actual calculation.