Excel-Portfolio-Verwaltung incl. automat. Kursaktualisierungen

[juro]

Aufgrund mehrfacher Nachfrage bzgl. technischer Umsetzung der in diesem Unterforum geführten Musterportfolios in Excel 2007 werd ich in nächster Zeit mal ein paar Tips u. Tricks sowie eine einfache Excel-Musterdatei reinstellen - frühestens nächste Woche.

[jpjg]

Aufgrund mehrfacher Nachfrage bzgl. technischer Umsetzung der in diesem Unterforum geführten Musterportfolios in Excel 2007 werd ich in nächster Zeit mal ein paar Tips u. Tricks sowie eine einfache Excel-Musterdatei reinstellen - frühestens nächste Woche.

Gute Idee. Warte gespannt.

[lander]

Das wuerde auch mir sehr weiterhelfen!

[juro]

Zuerst mal ein paar Infos wie man die Daten der jeweiligen Fonds, Währungen, etc. nach Excel bringt u. diese dann dort per Knopfdruck aktualisieren kann:

 

 

Auf Onvista die jeweilige WKN eingeben bzw. andersweitig suchen (zB Währungen) - u. dann untenstehende Anweisungen befolgen.

 

 

 

Dann in Excel unter dem Reiter Daten:

 

 

EDIT: erst mit "ok" bestätigen - dann erst importieren!

 

 

 

 

Jetzt sind die Daten schonmal in Excel u. können per Knopfdruck jederzeit aktualisiert werden.

 

Dieser Prozess muss wiederholt werden bis alle Fonds etc. in einer jeweils separaten Tabelle in derselben Excel-Datei vorliegen.

 

Dies ist die Basis für die weitere Vorgehensweise.

[juro]

Wollte grade eben die bereits angesprochene Muster-Excel2007-Datei hochladen mit 744 KB.

 

Kam die Fehlermeldung: Du bist nicht berechtigt diese Datei hochzuladen - auch nach Umstellung auf Flash hats nicht funktioniert. Ist in Klärung - evtl. liegts an den zahlreichen Kommentaren in der Datei bzw. automatischen Kursaktualisierungen.

 

 

Zu der Excel-Datei:

 

Hab nur das nötigste integriert - will heissen ein Portfolio aus 2 Fonds + Cash-Anteil. Dazu eine Benchmark (Carmignac Patrimoine). Bei mehreren Fonds ists analog hierzu.

 

Hab extra Sonderfälle wie zB Managed Futures berücksichtigt, die nur 1 x pro Woche Kurse stellen. Ebenso den Cash-Anteil der gesondert behandelt werden muss.

 

Alles ist mit Kommentaren innerhalb der Excel-Datei dokumentiert. So ist das mE am besten zu verstehen. Eine Doku macht mE keinen Sinn - viel zu kompliziert u. aufwendig.

 

 

Bitte erst sorgfältig mit der Excel-Datei befassen u. dann erst Fragen stellen!!

 

 

Für einen Aussenstehenden ist die Datei auf den ersten Blick nicht ganz leicht zu verstehen - Excel-Kenntnisse sollten in jedem Falle vorhanden sein. Denke aber, dass das machbar ist, wenn das Prinzip erst einmal verstanden ist. Das beste ist wahrscheinlich einfach rumzuprobieren u. auf die einzelnen Zellen / Formeln doppelklicken.

 

Bei Kursaktualisierungen wird der Kurs immer oben angehängt - deshalb müssen die Anteile sowie CASH in separater Tabelle verwaltet werden u. per SVERWEIS hergeholt werden.

 

Das gleiche gilt für MF, die nur unregelmässige Kurse liefern - hier wird in einer separaten Tabelle ein kleiner Trick angewendet.

 

 

Die Datei wurde nach bestem Wissen u. Gewissen erstellt u. geprüft. Viel Spass - sofern das Hochladen noch klappt.

[juro]

Anbei die Datei .xlsx für Excel 2007:

 

 

excel_musterportfolio01.xlsx

 

 

EDIT: Datei .xls für ältere Excel-Versionen in #9

[Marcise]

Ok., jetzt hats gefunzt. Anbei die Datei:

 

excel_musterportfolio01.xlsx

 

Toparbeit, quasi genau so mache ich es auch...

[juro]

Im folgenden ein paar kleine Erläuterungen:

 

 

++++++++++

 

 

Im der Tabelle "anteile" werden die einzelnen Anteile der jeweiligen Portfolioposition gepflegt:

 

 

Es können hier Anteile verkauft u. gekauft werden - es können Ausschüttungen in Form neuer Anteile berücksichtigt werden - Kaufkosten können anteilsmindernd berücksichtigt werden. Ebenso kann rebalanced werden. Bei Bedarf kann ggf. Verrechnung mit Cash-Anteil erfolgen. zB bei Verkauf einer Position kann dieser Anteil in Cash fliessen u. umgekehrt, etc, etc

 

Die Anteile werden dann per SVERWEIS in der Tabelle "kurse" übernommen.

 

Die Pflege in einer separaten Tabelle ist unumgänglich!! Ebens bei den Tabellen "cash" u. "man_neu". Der Grund liegt in den automatisierten Kursaktualisierungen. Hier werden neue Kurse immer oben ergänzt, d.h. in den Basistabellen (onvista-Aktualisierungen) sowie in der Tabelle "Kurse" verschieben sich jeden Handelstag die Kurse nach unten - der neueste ist dan ganz oben.

 

Somit müssen obige Daten in einer separaten Tabelle gepflegt werden u. per SVERWEIS reingeholt werden. Dann passt es! - selbst wenn ein Portfolioanteil an einem Tag mal keine Kurse liefert - per SVERWEIS wird gewährleistet, dass es für alle Positionen datumsmässig passt!

 

 

++++++++++

 

 

In der Tabelle "cash" wird der Cash-Anteil gepflegt - so eine Art Referenzkonto bzw. Ergänzung zu der Tabelle "Anteile":

 

 

 

++++++++++

 

 

In der Tabelle "man_neu" werden die unregelmässigen Kurse von Onvista tagesgenau so lange fortgeschrieben bis ein neuer Kurs gestellt wird - zB bei Managed Futures, die nur einmal in der Woche Kurse liefern:

 

 

Hier wird ein Trick angewendet wie in der Tabelle beschrieben. Dieser Trick ist notwendig. Beim Gesamtportfolio wird der Wert aus Summe der Einzelpositionen gebildet. Wird bei einer Position kein Kurs gestellt kommt #NV u. man erhält keine Ergebnisse in der Summe. Grade wenn man zB 2 solche Positionen hat die dann innerhalb der Woche zu unterschiedlichen Tagen Kurse melden bestünde die Gesamtsumme komplett aus #NV - dh das ganze wäre für die Katz.

 

 

++++++++++

 

 

In der Tabelle "kurse" erfolgt jetzt die gesamte Verwaltung u. Berechnung - so ziemlich alles wird per SVERWEIS hergeholt u. weiterverarbeitet:

 

 

Hier sollte man sich durch Doppelklick mal die einzelnen Zellen sorgfältig anschauen - die Daten kommen überwiegend aus den vorherig beschriebenen Tabellen.

 

Bei der Indexierung wird auch ein Trick angewandt ebenfalls per SVERWEIS - einfach mal anschauen. Die Indexierung kann automatisiert geändert werden zu jedem beliebigen Datum wo es Kurse bei den einzelnen Positionen gibt - ändert sich automatisch. Einfach Datum links oben abändern. Ebenfalls beim Anlagebetrag kann flexibel gewählt werden.

 

Wichtig wenn Positionen hinzugefügt oder weggenommen werden: Die Nummerierung über den Fondsbeschreibungen (Nr. der Spalte muss zwingend fortlaufend sein, da Bestandteil mancher SVERWEISE - ebenso in den anderen Tabellen. Die Anfangsgewichtungen der Einzelpositionen können ebenfalls flexibel gewählt werden.

 

 

++++++++++

 

 

In der Tabelle "diagramme" erfolgen die Auswertungen auf Basis der Tabelle "kurse":

 

 

Basis sind die Indexierungen.

 

 

++++++++++

 

 

Die Kurse können jederzeit aktualisiert werden. Manuell müssen dann nur die Anteile bzw. Cash manuell gepflegt werden. Wenn sich nichts ändert - dh. keine Käufe u. Verkäufe etc., dann können die bisherigen Werte in den Basistabellen einfach im Voraus oder im Nachhinein hochkopiert werden.

 

Die Daten liegen somit in Excel bereits vor u. können ggf. weiter verarbeitet werden.

 

 

++++++++++

 

 

Auf den ersten Blick ist die Tabelle für Aussenstehende vielleicht nicht gleich zu kapieren - wenn das Prinzip mal verstanden ist aber wahrscheinlich kein Problem mehr - zumindest nicht wenn Excel-Kenntnisse vorhanden sind. Ohne wirds wahrscheinlich schwierig!?

 

Werd jetzt erst mal eine rauchen. :-

[juro]

Auf Wunsch noch die Datei als .xls für ältere Excel-Versionen - auf den ersten Blick ist die Funktionalität voll erhalten geblieben, zumindest wenn ichs in Excel 2007 ausführe:

 

 

excel_musterportfolio01_xls.xls

 

 

Die Dateigrösse hat sich hierbei jedenfalls vervielfacht u. beträgt stattliche 2,9 MB.

 

 

EDIT: Datei .xlsx für Excel 2007 mit deutlich geringerer Dateigrösse in #6

[lander]

Danke Juro, Genial! Hätte ich alleine nie hinbekommen. Jetzt arbeite ich mich mal da durch Du hast das alles sehr verständlich erklärt!

[juro]

Anbei die Excel2007-Datei, wo das Muster-Portfolio aus dem Money-Management-Thread verwaltet wird - jetzt sinds schon über 3MB (statt vorher 744KB):

 

 

moneymanagement01.xlsx

 

 

In obiger Muster-Portfolio-Datei habe ich den Gesamtwert fiktiv auf hohe 100.000 EUR erhöht um das ganze transparenter zum Nachvollziehen zu machen.

 

Das Prinzip ist im Wesentlichen das gleiche wie bei der bereits geposteten Muster-Datei - nur ist es jetzt aufgrund der Anzahl der Fonds komplexer.

 

Die Datei eigent sich evtl. auch besser als Muster, da es meist leichter ist Postionen rauszunehmen als hinzuzufügen. Evtl. kann man nicht besetzte Spalten einfach freilassen oder bereits Fonds mitaufnehmen die in der Watchlist sind - mit Anteil 0 in der jew. Basistabelle.

 

Die Datei beinhaltet die Pflege von ca. 20 Fonds incl. Benchmarks - viel mehr solltens mE auch nicht sein, da das ganze sonst ggf. unübersichtlich wird (unter normalen Umständen reicht das mE völlig aus).

 

Statt Fonds sind analog natürlich auch Einzelaktien mgl. (gleiche Vorgehensweise wie bei Fonds - nur muss man dann halt die Dividenden über die Anteile miteinrechnen - sofern man den Performance-Index abbilden will (dasselbe gilt für ausschüttende Fonds).

 

 

Achtung:

 

Vereinzelt kann es bei onista vorkommen, dass die historischen Kurse in USD geführt werden!! Kann man aber einfach überprüfen. In obiger Datei werden alle Kurse definitiv in EUR geführt!!

 

Wenn Kurse im eher seltenen Falle tatsächlich in USD geführt werden muss der Umrechnungsfaktor USD/EUR ebenfalls in die Excel-Datei integriert werden. Per SVERWEIS müssen diese historischen Währungs-Kurse dann datumsgenau den USD-Kursen gegenübergestellt u. umgerechnet werden - u. im Anschluss dann in die Tabelle "Kurse" per SVERWEIS rübergeholt werden. Sehr umständlich also. So mach ich das zB im Kernportfolio-Thread mit dem ausschüttenden ishares MSCI World.

[west263]

schöne Arbeit,zum Teil sieht es bei mir genauso aus.Seit geraumer Zeit habe Ich es versucht mit der Indexierung,habe es aber nicht hinbekommen,jetzt schon.Danke

Ein Fehler ist mir,glaube Ich,aufgefallen,bei der Indexierung und dem Gesamtportfolio.Das bezieht sich auf den Gesamtwert.Müsste es nicht der Mittelwert von Portfolio sein? Solange wie sich die Menge der Anteile nicht ändert,spielt es keine Rolle.Sowie aber neue dazukommen,schießt der Wert in die Höhe.

[juro]

schöne Arbeit,zum Teil sieht es bei mir genauso aus.Seit geraumer Zeit habe Ich es versucht mit der Indexierung,habe es aber nicht hinbekommen,jetzt schon.Danke

Ein Fehler ist mir,glaube Ich,aufgefallen,bei der Indexierung und dem Gesamtportfolio.Das bezieht sich auf den Gesamtwert.Müsste es nicht der Mittelwert von Portfolio sein? Solange wie sich die Menge der Anteile nicht ändert,spielt es keine Rolle.Sowie aber neue dazukommen,schießt der Wert in die Höhe.

 

 

Danke! Nee, das passt. Der Gesamtwert des Portfolios bzw. Indexierung hierzu bezieht sich immer TAGESGENAU auf den Wert des Gesamtportfolios! Umschichtungen sind ein reines Nullnummernspiel.

 

Die Anteile verändern sich bspw. bei Verkauf von Anteilen => Dieser Betrag fliest aber dann entweder in Cash oder wird reinvestiert unter Berücksichtigung von Kaufkosten. Somit passt es wieder.

Bei Kauf zB aus Cashbestand fliesst der Betrag aus dem Cash u. fliesst hieraus in die erworbenen Anteile - somit passt es auch wieder.

 

D.h. sobald irgendwo was abfliesst wird dies unter Berücksichtigung von Transaktionskosten wieder dem Portfolio zufliessen, etc. zB durch Kauf neuer Anteile bzw. Erhöhung Cash-Bestand.

 

Aussschüttengen beim ishares Asia Property fliessen in den Cash-Bestand.

 

Keine Sorge - werde alle Transaktionen transparent im Voraus ankündigen u. sagen wohin was ab- u. zufliesst - sodass jeder das Nachvollziehen kann u. ggf. intervenieren kann.

 

 

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

 

EDIT: Das hattest du wahrscheinlich gemeint

[west263]

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

EDIT: Das hattest du wahrscheinlich gemeint

Genau so habe Ich das gemeint,wenn nichts frisches dazu kommt,ist es ja egal wie man rechnet.

[juro]

NEU!!

 

Berechnungen folgender Kennzahlen in Excel 2007:

 

 

• Volatilitäten (annualisierte Standardabweichungen)
  
• Korrelationen
  
• Renditen (annualisiert)
  
• Sharpe-Ratio (annualisiert) - als sicherer Zins wurden 1% angesetzt
  

 

 

Die Berechnungen wurden inzwischen in die Excel-MONEYMANAGEMENT-Musterportfolio-Datei integriert - die hier ja bereits gepostet wurde.

 

 

Diese (neue) Datei soll als Musterdatei dienen:

 

berechnungen01.xlsx

 

Anmerkung: Konvertierung in ältere Excel-Versionen als Excel2007 macht keinen Sinn, da erheblicher Funktionalitätsverlust - habs ausprobiert.

 

 

+++

 

 

Die Berechnungen basieren alle auf der Tabelle "kurse" in der Excel-Datei:

 

 

Hier gab es eine kleine Verbesserung gegenüber bisher (s. hardcopy) bzw. Excel-Tabelle

 

 

+++

 

 

Zur Berechnung der Kennzahlen wurde die Excel-Datei um 4 weitere Tabellen erweitert:

 

1. verdichtung1
   
2. verdichtung2
   
3. verdichtung3
   
4. verdichtung4
   

 

+++

 

 

Erläutung zu den einzelne Tabellen s. hardcopy bzw. direkt in Excel-Datei

 

Tabelle "verdichtung1"

Keine Eingabe notwendig - da automatisiert

 

 

Tabelle "verdichtung2"

die Daten aus "verdichtung1" müssen manuell reinkopiert werden.

EDIT: Filter "#NV"

 

 

Tabelle "verdichtung3"

s. Beschreibung in hardcopy bzw. direkt in Excel-Datei.

 

 

Tabelle "verdichtung4"

Bezieht sich auf "verdichtung3" u. ist automatisiert. Die farblichen Hinterlegung bei den einzelnen Berechnungen ist automatisiert per "Bedingte Formatierung".

 

 

+++

 

 

Die Berechnung von Volatilitäten, annualisierten Renditen u. Sharpe-Ratio in Excel erfolgte mit freundlicher Unterstützung von Norbert-54 - s. hierzu damals im Kernportfolio-Thread.

[juro]

Anbei das im MM-Thread angekündigte Makro zur Berechnung des MaxDrawdowns in Excel.

 

Das Makro wurde von Norbert-54 erstellt u. kann mit <Strg>+<a> ausgeführt werden.

 

 

Hier in der angefügten Excel-Datei am Beispiel des Carmignac Investissements:

 

 

Ggf. muss oben unter Optionen das Makro aktiviert werden.

Onvista-Daten nach Excel exportiert bieten sich für das Makro gerade an - das neueste Datum muss oben stehen.

Die Zelle über dem obersten Wert muss leer sein.

Irgendeinen Wert anklicken u. das Makro ausführen - ab dieser Stelle wird der MaxDrawdown berechnet u. eine Zelle rechts über dem obersten Wert ausgewiesen (gelb markiert).

 

 

+++

 

 

Nochmal die Definition:

Der maximale Drawdown (Verlust) ist der höchste jemals aufgetretene prozentuale Abschwung nach einem zurückliegenden Höchststand.

 

Eine unter Riskomanagementsgesichtspunkten sehr interessante Kennzahl. Leider wird diese Kennzahl von den Fondsanbietern bzw. in den factsheets nicht ausgewiesen. Anders zB bei vielen Managed Futures Fonds.

 

 

 

Optisch gesehen würde man vermuten, dass der MaxDrawdown in der Finanzkrise höher war.

 

Absolut gesehen stimmt das auch, aber eben nicht prozentual gesehen - was entscheidend ist.

 

Im Tech-Crash war der MaxDrawdown ca. 5%-Punkte höher.

 

 

+++

 

 

Anbei das Makro:

 

MaxDrawdown_investissement.xls

 

 

 

EDIT:

Wenn obige Datei geöffnet ist, kann das Makro in jeder beliebigen Excel-Datei ausgeführt werden - erkennt Excel automatisch (ggf. muss das Makro in der jew. Datei aktiviert werden).

[juro]

Da es teilweise mit der Ausführung des Makros Probleme gab (bei mir funzt es nach dem Download) eine weitere Datei mit der es gehen müsste:

 

Maxdrawdown_Von Unten.xls

 

Wenn diese Datei geöffnet ist dürfte das Makro in jeder anderen geöffneten Excel-Datei funktionieren <Strg>+<a>.

Makros müssen zuvor ggf. aktiviert werden.

[Norbert-54]

Falls jemand Probleme hat:

 

Bitte die Datei aus deren Daten Ihr deren Maxdrawdown berechnen wolltet per PM an mich, wenn es nicht funktioniert. Ich sammle diese und gebe dann hier Bescheid. Wenn nötig modifiziere ich das Makro.

 

Bitte noch beachten: Das von Juro gepostete Makro funktioniert dann, wenn die ältesten Kurse unten stehen und die jüngsten oben. Die Zelle oberhalb des obersten Kurses muss leer sein.

Vor dem Start des Makros: auf den ältesten Kurs in der untersten Zelle gehen.

 

Norbert

[juro]

Norbert, danke für deinen Einsatz.

 

Kennt jemand eine Internet-Plattform wo der MaxDrawdown bei Fonds ausgewiesen wird bzw. berechnet wird? Würde mich echt brennend interessieren - mir wäre da nichts bekannt.

 

Aus Risikomanagementsgesichtspunkten ist der MaxDrawdown zusammen mit der Rendite u. ggf. Vola eine sehr interessante Kennzahl.

 

Insbesondere kann der MaxDrawdown Aufschluss über das Market-Timing geben bzw. Risikomanagement durch Asset-Allocation.

 

Auch bei Mischfonds wird ersichtlich ob ein Fonds es geschafft hat rechtzeitig umzuschichten - mE eine interessante Kennzahl.

 

 

+++

 

 

Funktioniert das Makro jetzt bei allen oder gibts teilweise Probleme?

 

Feedback hierzu wäre nicht schlecht.

[sparfux]

Habe es irgendwie nicht zum Laufen bekommen. Auf welche "erste Zelle" muss ich den Kursor stellen und wo kann ich nach Makroablauf die Werte sehen?

OK das habe ich hinbekommen. Und was wird dann angezeigt? Der max.Drawdown in %?

Wird eine spezielle Excel version vorausgesetzt (habe hier gerade Excel 2003).

 

Wäre es möglich die MaxDrawdown Berechnung in eine Formel zu quetschen? Das wäre eigentlich das, was ich suche?

 

Für das Makro: Kann man den neuesten Wert auch am Ende haben? Geht das konfigurierbar zumachen? Leider bin ich kein Visual Basic Experte.

[Norbert-54]

 

OK das habe ich hinbekommen. Und was wird dann angezeigt? Der max.Drawdown in %?

 

 

Wäre es möglich die MaxDrawdown Berechnung in eine Formel zu quetschen? Das wäre eigentlich das, was ich suche?

 

Für das Makro: Kann man den neuesten Wert auch am Ende haben? Geht das konfigurierbar zumachen? Leider bin ich kein Visual Basic Experte.

 

Sparfux,

1. es wird der max. Drawdown in Prozent ausgegeben.

2. In einer Formel in einer Zelle: wahrscheinlich geht es nicht. Wäre schön.

3. Anbei eine Datei mit den ältesten Kursen oben und mit den neuesten unten. Das VB Makro habe ich entsprechend angepasst.

 

Vorgehensweise:

- In oberste Zelle mit den Kursen gehen. STRG+a drücken.

- man kann eine beliebige andere Zelle mit den Kursen anwählen, dann beginnt die Berechnung von dieser Zelle aus.

- fügt man eine leere Zelle ein, endet die Berechnung dort

- Die Zelle unterhalb des letzten Kurses muss leer sein

- die 7 Spalten rechts neben den Kursen müssen leer sein

 

Das VB Modul ist etwas verändert:

Bei Erreichen / Überschreiten eines alten Maximums werden einige Daten in die Zellen rechts vom Kurs geschrieben (Inhalt siehe gelb unterlegte Zellen in Zeilen 1 und 2). Ganz unten dann der maximale Draw Down.

 

Norbert

 

Maxdrawdown_Von Oben.xls

[sparfux]

OK danke! Ich schaue mir das mal am WE an. Ich denke man kann in Visual Basic solche Funktionen programmieren. Leider kann ich eben kein Visual Basic.

[sparfux]

OK, ich konnte es nicht sein lassen Hier ist die MaxDD-Funktion für Excel.

 

... ich hasse objektorientierte Sprachen. Was weiss ich denn,was bei so einem Objekt alles gemacht werden kann :'(

 

Beschränkung:

- Neuster Wert am Ende

- keine Fehlerabfragen, z.B. wenn mehrdimensionale Ranges

- ich habe echt keine Ahnung von VBA :-

 

Vielleicht kann ja einer noch einen Parmater hinzufügen, der beide Varianten ermöglicht. erledigt

 

MaxDD(ersteZeile:letzteZeile; [NewestFirst])

 

Liefert den maximum Drawdown zurück. Wenn die Option "NewestFirst" weggelassen oder mit 0 angegeben wird, wird vom neusten Wert am Ende ausgegangen. Ansonsten wird der neuste Wert am Anfang angenommen.

 

Ich habe das ganze jetzt auch als Add-In abgespeichert, so dass es nach dem ersten Anklicken in jeder Tabelle zur Verfügung steht.

 

Installation:

- entzippen

- maximumdrawdown.xla ausführen

- Extras-->Add-Ins-Manager-->Maximumdrawdown mit Häckchen versehen

- benutzen

MaximumDrawdown.zip

[sparfux]

Da ist noch ein Bug bei der Indizierung drin. Hat aber m.E. keinen Einfluss auf das Endergebnis. Werde heute Abend nochmal ein Update nachschicken.

[sparfux]

so habe oben ein Update reingehangen.

 

Die Änderungen:

 

- Index-Fehler beseitigt

- jetzt funktioniert es von vorne und von hinten B)

- als Add-In einfacher zu installieren für alle Tabellen

 

Have fun. Feedback welcome.