Excel-Portfolio-Verwaltung incl. automat. Kursaktualisierungen

[sparfux]

Na hat denn keiner meine Maximum Drawdown Excel-Funktion mal ausprobiert? Wenn ich mich schon mit Visual Basic abquäle

[juro]

Danke für die Arbeit, sparfux!

 

Werds morgen mal ausprobieren.

[Dagobert]

Danke für die Arbeit, sparfux!

 

Werds morgen mal ausprobieren.

 

dito, schliesse mich an und sobald ich's getestet habe gibt's feedback

[sparfux]

na da bin ich ja beruhigt :-

[Norbert-54]

Hallo Sparfux

 

Du hast sehr schön den Kern der Maxdrawdown Berechnung heraus isoliert. Endlich habe ich auch den Zweck von XLA Dateien verstanden. Das wird meine Arbeit in Zukunft etwas vereinfachen.

Dein Modul bekomme ich zwar in meine Excel (2007) Dateien heinein, kann es aber (noch) nicht ausführen. Es liegt wohl daran dass ich die "function" Anweisung nicht aktiviert bekomme. Es müsste jedoch dasselbe Ergebnis herauskommen wie bei meinem Makro.

 

Grüße

Norbert

[sparfux]

mojn, mojn,

 

ja na klar habe ich das basierend auf Deinem Makro gemacht. Ist eine gute Vorlage.

 

Probiere mal foldegndes zum Installieren:

 

- beliebige Excel-Tabelle öffnen

- Extras--->Add-Ins-->durchsuchen--> maximumdrawdown.xla

- ggf noch Häckchen setzen.

 

Ich hatte es mit Deiner Tabelle als Referenz auch ausprobiert. Es sollte natürlich das gleiche rauskommen.

[juro]

In VBA kenn ich mich nicht besonders aus.

 

Im Geschäft mit der alten Excel-Version hatte ich schon desöfteren aufgezeichnete Makros als .xla im entsprechenden Add-In-Verzeichnis abgespeichert, sodass das Makro in jeder Excel-Tabelle ausführbar war.

 

Habs jetzt auch in Excel 2007 hingekriegt:

=> In Excel2007 ganz oben linds auf die "Schaltfläche Office" klicken

=> unten den Button "Excel-Optionen" anklicken.

=> links auf den Reiter "Add-Ins" klicken

=> dann unten "Verwalten: Excel-Add-Ins" einstellen

=> Klick auf "Gehe zu"

=> es öffnet sich neue Maske, hier den Button "Durchsuchen" anklicken

=> Es öffnet sich ein Pfad - in dieses Feld die .xla-Datei reinkopieren

=> dann in der Add-In-Auswahlbox dieses Add-In mit Häckchen aktivieren.

 

 

Soweit so gut - jetzt brauch ich weitere Unterstützung.

 

Das Makro müsste ja jetzt mit <Strg>+<a> in jeder x-beliebigen Tabelle ausführbar sein.

 

Wenn ich das Makro jetzt ausführen will, dann wird mit obiger Tastenkombination lediglich die Tabelle markiert.

 

Dadurch, dass das Makro jetzt nicht mehr in der Datei drin ist hab ich beim Öffnen der Excel-Datei nicht mehr die Möglichkeit, Makros zu aktivieren - ist das überhaupt noch erforderlich in diesem Fall?

 

 

@Sparfux bzw. Norbert:

- funktioniert das Makro genauso wie Norbert seins, d.h. man muss den Cursor an der Stelle positionieren ab wo der MaxDrawdown berechnet werden soll?

- wenn das Makro nach beiden Seiten funktionieren soll - also von oben als auch nach unten wie weiss das Makro was ich will?

- Wie bringe ich das Makro zum laufen?

- muss der oberste Wert ebenfalls leer sein wie bei Norberts Makro?

 

Weiss jemand wo es bei mir hakt?

 

 

Ich stell mal eine Beispieldatei rein zur Berechnung von MaxDrawdowns - für diese sollen jeweils die MaxDrawdowns berechnet werden:

 

Mappe1.xls

 

Mit Norberts Makro funktioniert es einwandfrei wie folgt:

Cursor auf unterste Zelle - Makro ausführen: Der Maxdrawdown wird eine Zelle rechts oberhalb der Daten ausgewiesen.

[sparfux]

Ich werde aus Deinen Ausführungen nicht so ganz schlau. Sagst Du, dass die Funktion, die ich geschrieben habe, nicht funktioniert?

 

Wenn ich Dein Beispielfile öffne und die Funktion verwende, geht es bei mir einwandfrei.

 

Zeile G1. sollte -4,84% zeigen

 

Ich füge das File mal bei.

 

Meines ist kein Makro sondern eine Funktion. In einem beliebigen Feld kannst Du die Funktion benutzen. Beispielsweise:

 

=maxdd(A1:A20,1) sollte dir den max. Drawdown der Reihe A1...A20 liefern. Die 1 als zweiter Parameter sagt, dass der neuste Wert am Anfang steht.

 

man muss keine Tastenkombination drücken.

Mappe1-spf.xls

[juro]

Hi sparfux,

 

dass die Berechnung funktioniert war mir stets klar - wollte nur die Ursache wissen warum es bei mir nicht funktioniert bzw. ob jemand Tips, dass es auch bei mir klappt.

 

Dank deiner zuletzt eingestellten Datei mit der integrierten Formel sowie den zusätzlichen Erläuterungen bin ich dahinter gekommen wie das ganze funktioniert.

 

Zuerst konnte der Pfad in der Formel nicht gefunden werden wo das .xla gespeichert ist - da ich es in einem anderen Pfad hinterlegt hatte.

 

 

Nach Umstellung auf den richtigen Pfad funzt die Funktion jetzt einwandfrei - u. ich muss sagen eine klasse Sache.

 

Durch die Formel kann man gerade bei vielen Fonds das Ganze vollständig automatisieren bzw. den Bereich in einer Formel ändern u. dann rüberziehen.

 

Die Funktion hab ich bei Zeitreihen von über 10 Jahren getestet - die Berechnungen stimmen.

 

Klasse ist ausserdem, dass man nach beiden Richtungen rechnen kann, je nachdem wie die Daten sortiert sind.

 

 

Spitze Arbeit, sparfux!

 

Bin sicher nicht der einzige, dem das weiterhilft. Danke für die Arbeit.

[sparfux]

Kein Problem. Ich habe eher Dir und Norbert-54 danken. Wenn Du nicht Norbert zum Erstellen des Makros motiviert hättest, hätte ich auch nie eine Funktion daraus gemacht

 

Ich kann das für meine Excel-Portfolio-Verwaltung auch sehr gut gebrauchen. Bisher hatte ich den Max. Drawdown nur ab und an manuell berechnet. Jetzt geht das mir vollautomatisch.

 

Du kannst Änderungen des Bereiches (z.B. wenn täglich neue Kurse dazukommen) auch automatisch vornehmen. Ich mache das in etwa so:

 

Sagen wir die Kursreihe ist in B5 ... Bn.

 

In A1 schreibe ich den Startwert der Zeitreihe: 5

in A2 den letzten Wert der Reihe: =Anzahl(B:B )-1 (-1 weil B1 durch maxDD auch einen Wert hat)

 

in B1 kann man dann den maxDD mit der indirekt Funktion automatisch berechnen lassen

=maxdd(Indirekt("B"&$A$1&":B"&$A$2);1)

[Norbert-54]

Hallo Sparfux,

 

Auch von mir ein ein dickes Kompliment. Klasse Sache. Funktionen werde ich in Zukunft häufig verwenden.

 

Ich habe Deine Funktion auch ausprobiert und noch eine etwas gefunden:

 

Ist der neueste Wert unten, funktioniert´s einwandfrei.

 

Wenn der neueste Wert oben ist, rechnet Deine Funktion offenbar dennoch in derselben Orientierung, wenigstens bei mir, und rechnet daher die Ergebnisse von der falschen Seite her.

 

Ich füge die entsprechende Datei an. Beim Tabellenblatt "Von Unten": auf die unterste Kurszelle und STRG+a. Beim Tabellenblatt "Von Oben" auf die oberste Kurszelle und STRG+b.

 

Versuche mal in "Von Unten" auf die oberste Kurszelle zu gehen und STRG+a drücken, du siehst dann was ich meine (vorher die Inhalte in Spalten C bis H löschen). Du siehst dann wieder die Übereinstimmung mit deinen Ergebnissen (allerdings sind es die Falschen).

 

Grüße

Norbert

Maxdrawdown_Beides.xls

[Norbert-54]

Noch ein Zusatz: fügt man in die Zelle "=MaxDD(Bx:By)" die ";1" ein ist es gerade anders herum.

 

Norbert

[sparfux]

Hi Norbert.

 

habe das File gerade mal ausprobiert. Meiner Meinung nach passt alles.

 

Beim "Von Unten"-Sheet musst Du in EIN Feld =maxdd(B11:B676;1) eingeben, um zu den 58,67% zu kommen, im "Von Oben" Sheet =maxdd(B11:B676).

[juro]

Hi Norbert.

 

habe das File gerade mal ausprobiert. Meiner Meinung nach passt alles.

 

Beim "Von Unten"-Sheet musst Du in EIN Feld =maxdd(B11:B676;1) eingeben, um zu den 58,67% zu mommen im "Von Oben" Sheet =maxdd(B11:B676).

 

 

Hatte heute mittag die Funktion ebenfalls von beiden Seiten ausprobiert:

 

Steht das aktuelle Datum unten, so muss die Formel lauten: =maxdd(B11:B676;0)

Steht das aktuelle Datum oben, so muss die Formel lauten: =maxdd(B11:B676;1)

 

In beiden Fällen kommt bei mir das selbe raus.

 

 

Ursprünglich hatte mich ja .xla verwirrt, das ich bisher nur von Makros kannte - deshalb stand ich auch auf dem Schlauch, darauf muss man erst mal kommen.

Ne gute Sache Funktionen per .xla in jeder beliebigen Excel-Tabelle ausführen zu können.

[juro]

Kein Problem. Ich habe eher Dir und Norbert-54 danken. Wenn Du nicht Norbert zum Erstellen des Makros motiviert hättest, hätte ich auch nie eine Funktion daraus gemacht

 

Ich kann das für meine Excel-Portfolio-Verwaltung auch sehr gut gebrauchen. Bisher hatte ich den Max. Drawdown nur ab und an manuell berechnet. Jetzt geht das mir vollautomatisch.

 

Du kannst Änderungen des Bereiches (z.B. wenn täglich neue Kurse dazukommen) auch automatisch vornehmen. Ich mache das in etwa so:

 

Sagen wir die Kursreihe ist in B5 ... Bn.

 

In A1 schreibe ich den Startwert der Zeitreihe: 5

in A2 den letzten Wert der Reihe: =Anzahl(B:B )-1 (-1 weil B1 durch maxDD auch einen Wert hat)

 

in B1 kann man dann den maxDD mit der indirekt Funktion automatisch berechnen lassen

=maxdd(Indirekt("B"&$A$1&":B"&$A$2);1)

 

Norbert brauchte ich gar nicht motivieren - hab ihn nur gefragt ob er eine Idee zur Umsetzung hätte u. dann hat er spontan seine Hilfe angeboten.

 

Den MaxDrawdown manuell zu berechnen ist selbst bei kurzen Zeitreihen recht mühselig u. aufwendig, da es sich ja um einen prozentualen MaxDrawdown handelt - u. da können absolute Zahlen oft täuschen. Das gleiche gilt für die Identifizierung des MaxDrawdowns bzw. des Ausgangsdatums bzw. Enddatums. Charts können da gewaltig täuschen - s. das Beispiel mit dem Carmignac Investissement ein paar Posts vorher. Ein MaxDrawdown muss nicht vom erreichten Höchststand ausgehen, sondern kann sich auch auf einen zuvor erreichten, inzwischen übertroffenen Höchststand beziehen (s. hierzu auch das erwähnte Beispiel).

 

Von demher finde ich das Berechnungs-Tool klasse.

 

Den obigen Tip werd ich mir noch näher anschauen, glaube aber dass ich den gut gebrauchen kann.

[Bärenbulle]

Herzlichen Dank, sehr interessant. Da kann ich Einiges von gebrauchen.

 

Aber mal eine Frage zu den Kursdaten. Seht Ihr die Möglichkeit einen längeren Horizont als 6 Monate heranzukommen? Finde ich ein bisschen kurz.

[juro]

Bärenbulle, schau dir mal in #4 die zweite hardcopy an, da wird erläutert, dass man aus den 6M einfach 120M machen kann - somit hat man dann stets die Kurshistorie der letzten 10 Jahre.

 

Denke, dass man mit dem bisher Geposteten sein Musterportfolio komfortabel u. insbesondere sehr flexibel verwalten kann. Es stehen auch gute Auswertungsmöglichkeiten zur Verfügung.

[wu_trax]

Hallo zusammen,

erstmal vielen Dank für die Excel-Datei.

In der Hoffnung das ganze Thema dadurch etwas besser (oder sagen wir lieber überhaupt) zu verstehen versuche ich gerade das ganze in PHP nachzuprogrammieren (außerdem bin ich eh gerade krank und hab nichts besseres zu tun...).

Bis zur Volatilität und Berechnung der Korrelationen für die einzelnen Werte bin ich, dank der Vorlage, auch schon gekommen.

 

Was ich mich gerade frage ist was mit den Ausschüttungen ist. Bei der Betrachtung der ganzen historischen Onvista-Kurse bleiben eventuell vorhandene Ausschüttungen doch völlig unberücksichtigt, oder?

 

Ein anderes Problem, das eventuell damit im Zusammenhang steht: Wenn ich zum Beispiel die beiden hier vergleiche:

iS.II-iShares EO Gvnmt Bd 3-5 / Bearer Shares ( DE000A0LGQD2 )

ISH. EB.R. JUMBO PFANDBR. ( DE0002635265 )

komme ich auf eine Korrelation von 0,85, Volatilität 3,64 bzw. 3,18, betrachtet habe ich dabei jeweils alle verfügbaren Kurse.

Schau ich mir die beiden bei comdirect an steht da eine Korrelation von 0,19 und eine Volatilität von 3,37 bzw. 2,67, wobei ich keine Ahnung habe wie die da rechnen.

[juro]

Was ich mich gerade frage ist was mit den Ausschüttungen ist. Bei der Betrachtung der ganzen historischen Onvista-Kurse bleiben eventuell vorhandene Ausschüttungen doch völlig unberücksichtigt, oder?

 

Klar, ist ja auch Sinn u. Zweck, da bei Aktien, ausschüttenden Fonds, etc. die ordentlichen Erträge (Dividenden, Zinsen) ja ausgeschüttet werden - somit haben sie im Kurs auch nichts verloren.

 

Bei Thesaurierern werden die ordentlichen Erträge wieder angelegt - die Anteile bleiben gleich.

 

Im Excel-Portfolio-Tool können die Ausschüttungen auch wieder manuell angelegt werden, indem neue Anteile gekauft werden - oder dass dieser Betrag eben zB in den Cash-Bestand fliesst. Neuanlage macht aufgrund der geringen Beträge aufgrund Transaktionskosten oftmals keinen Sinn. Deshalb kann es Sinn machen diese Beträge auf dem Cash-Verrechnungskonto zu sammeln u. wenn genug beisammen ist reinvestieren bzw. Rebalancing betreiben. Diese Möglichkeit besteht auch im Excel-Musterportfolio-Verwaltungstool.

 

 

 

Ein anderes Problem, das eventuell damit im Zusammenhang steht: Wenn ich zum Beispiel die beiden hier vergleiche:

iS.II-iShares EO Gvnmt Bd 3-5 / Bearer Shares ( DE000A0LGQD2 )

ISH. EB.R. JUMBO PFANDBR. ( DE0002635265 )

komme ich auf eine Korrelation von 0,85, Volatilität 3,64 bzw. 3,18, betrachtet habe ich dabei jeweils alle verfügbaren Kurse.

Schau ich mir die beiden bei comdirect an steht da eine Korrelation von 0,19 und eine Volatilität von 3,37 bzw. 2,67, wobei ich keine Ahnung habe wie die da rechnen.

 

Bei den Korrelationen kommt es insbesondere auch auf den Zeitraum an - bei unterschiedlichen Zeiträumen kommen auch unterschiedliche Ergebnisse raus. Die von dir gewählten Jumbos u. Anleihen mit 3-5 Jahren dürften idR eine hohe Korrelation haben - wie da comdirect auf 0,19 kommt ist mir ein Rätsel.

 

Die Volatilität ist die annualisierte Standardabweichung - auch hier kommt es auf den Betrachtungszeitraum an. In der Excel-Formel wird der bisher verfolgte kurze Zeitraum mit 260 Börsentagen annualisiert. Auch hier kommt es auf den Zeitraum an, der annualisiert wird u. was da comdirect verwendet kann ich nicht sagen.

 

Solange du nicht weisst wie comdirect rechnet wirst du deren Zahlen auch nicht nachvollziehen können.

[juro]

Anbei ein kleines Excel-Finanz-Tool (Excel 2007) zur Durchführung von finanzmathematischen Berechnungen:

 

 

finanz-excel.xlsx

 

 

 

**********

 

 

 

Berechnungen von Annuitätsraten:

 

 

 

 

**********

 

 

 

Berechnung von Sparraten:

 

 

 

 

**********

 

 

 

Berechnung des Endkapitals:

 

 

 

 

**********

 

 

 

Berechnung des Barwerts:

 

 

 

 

**********

 

 

 

Berechnung Effektivzins (interner Zinsfuss):

 

 

 

 

**********

 

 

 

Berechnung jährl. Erträge ohne Kapitalverzehr (nominal vor Inflation / real nach Inflation):

 

[juro]

Das Excel-Finanz-Tool für ältere Excel-Versionen:

 

 

finanz-excel.xls

[xyzabc]

...

Es fliesst KEIN "frisches" Geld hinzu!!! Ausschliesslich Umschichtungen!!

 

...

 

Wirklich ein sehr interessanter Thread und ein wirklich tolles Tool!

 

Allerdings hätte ich noch eine Frage: Hat jemand einen Vorschlag, wie ich das Excel Dokument anpassen könnte, damit ich auch einen Zufluss von Geld darstellen könnte, ohne alle Berechnungen zu "verfälschen"?

 

Als praktisches Beispiel ausgedrückt:

 

Kaufe heute verschiedene ETF und gebe diese inkl. Anteile ein. Nun möchte ich in ca. 3 Monaten (nachdem wieder genügend Geld auf dem Tagesgeldkonto ist) die vorhanden ETFs nachkaufen oder neue ETFs kaufen.

 

Hat jemand eine Idee?

 

Vielen Dank.

[juro]

Die Lösung heisst "zeitgewichtete Performance", so wie es auch die Fonds machen. Macht in jedem Falle Sinn.

 

Wenn ich mal die Zeit finde werd ich das Excel-Tool entsprechend anpassen. Kann aber noch eine Weile dauern.

[xyzabc]

...

Wenn ich mal die Zeit finde werd ich das Excel-Tool entsprechend anpassen. Kann aber noch eine Weile dauern.

 

Das wäre super! Bin mir nicht ganz sicher, ob ich's selbst angepasst bekomme.

[juro]

Anbei jetzt das Excel-Tool incl. Berechnung "zeitgewichteter Performance" analog wie Fonds die Performance ermitteln. D.h. dass performanceneutral Zugänge mit frischem Geld erfolgen können ohne dass sich dies performanceerhöhend auswirkt. Genauso bei Abgängen. Somit eignet sich das Tool insbesondere auch für Sparpläne um die zeitgewichtete / effektive Rendite graphisch darzustellen.

 

 

tool_zeitgewichtet.xlsx

 

Die Datei ist virengeprüft.

 

 

Das bisherige Excel-Tool wurde im Laufe der Zeit überarbeitet u. optimiert - alles unnötige wurde weggelassen (insbesondere auch die Berechnungen der Volatilitäten, Sharpe-Ratio, MaxDrawdown, annualisierte Rendite, Korrelationen - diese Punkte würden das ganze nur verkomplizieren, falls Interesse besteht im alten Tool ist es enthalten u. kann dort eingesehen werden).

 

Die Basics sollten im Thread nachgelesen werden - möchte hier nur auf die Neuerungen in der Datei eingehen.

 

 

Die Kurse werden wie gewohnt auf Knopfdruck aktualisiert (Basis Onvista) u. per SVerweis hergeholt. Die ganzen Portfolioberechnungen erfolgen in der Tabelle "aktienportfolio".

 

Hier wird auch die zeitgewichtete Performance ermittelt u. schliesslich in die Tabelle "benchmarks" übernommen. Neu ist, dass die Benchmarks in einer separaten Tabelle gepflegt werden.

 

Das "Diagramm" bezieht sich auf die Tabelle "Benchmarks".

 

Die "Anteile" werden ebenfalls in einer separaten Tabelle gepflegt u. werden per SVerweis in die Tabelle "aktienportfolio" übernommen.

 

Achtung: Alle Felder mit roter Schrift müssen ggf. entsprechend gepflegt werden.

 

***

 

Die Tabelle "cash" ist eine entscheidende Tabelle.

 

Zum einen können hier Zu- u. Abgänge beim Cash eingetragen werden sowie die Dividenden. Diese wirken sich performanceerhöhend aus.

 

Zum anderen können hier die performanceneutralen Beträge verwaltet werden - Zukauf / Verkauf mit externem Geld.

 

 

Die angeleuchteten performanceneutralen 1.002 EUR betrifft in diesem Falle der Kauf von Gilead Sciences am 22.03.2010 mit frischem Geld.

 

Diese wurden ja am 22.03.2010 mit 1.002 EUR mit frischem Geld gekauft:

 

 

Die Investition mit frischem Geld wirkt sich performanceneutral aus, da der Betrag in der Tabelle "cash" ergebnisneutral angesetzt wird. Ergebnis ist eine zeitgewichtete Performance. Transaktionskosten können entweder über den angesetzten ergebnisneutralen Betrag in der Tabelle "cash" berücksichtigt werden (wahrscheinlich die beste Lösung) oder eben durch Abgang beim Cash-Anteil (Minus-Eingabe).

 

Auch Sparpläne können auf diese Weise gut umgesetzt werden, indem zu einem Stichtag jeweils neue Anteile hinzugekauft werden (Tabelle "Anteile") u. der Betrag zzgl. Kosten dann in der Tabelle "Cash" performanceneutral eingegeben wird.

 

 

Lange Rede kurzer Sinn: Einfach ausprobieren ist besser als die beste Doku!

 

Für einen Neueinsteiger / Excel-Neuling ist das ganze wahrscheinlich auf Anhieb nur schwer nachzuvollziehen. Wenn man das Prinzip bzw. Funktionsweise mal verstanden hat ist die Datei relativ easy u. komfortbabel zu handeln m.E.

 

Für Fragen stehe ich gerne zur Verfügung - allerdings bitte erst sorgfältig einlesen im Thread bzgl. der alten Datei (Basics) bzw. erst rumprobieren. Im Thread bzw.der alten Datei sind Kommentare eingefügt bzw. entsprechend im Thread dokumentiert. Wenn dann immer noch Fragen offen sein sollten dürfen diese gerne gestellt werden. Eine ausführliche Doku bzw. weiteren Support wird es nicht geben.

 

 

***

 

EDIT: die Excel-Datei bezieht sich auf das in diesem Thread geführte Portfolio (die Werte sind fiktiv): AKTIEN-PORTFOLIO nach Fundamental-Analyse