Jump to content
slekcin

Doppelte Positionen mit Excel anzeigen lassen

Recommended Posts

slekcin
Posted

Hallo, 

 

ich war leider noch nie wirklich gut mit Excel und stehe vor folgender Aufgabe. Ich würde gerne die Doppelten Positionen des Vanguard Dev. World und des Vanguard Dev. Europe Ex UK oder mit UK und deren Gewichtung anzeigen lassen. Dazu habe ich mir die Daten von der Vanguard Seite geladen und in eine Excel gepackt. Gibt es eine einfache Möglichkeit, dass Excel erkennt, dass z.B. Nestle im World 0,69% hat und es die Zeile auch im Europe gibt mit 4,5% und dann die Werte nebeneinander schreibt ? Das würde mir verdammt viel Zeit ersparen, da ich sonst die % Werte der Positionen manuell eintragen müsste. Damit könnte ich dann verschiedene Szenarien mit x% World und x% Europe durchspielen. Die Datei mit den Daten habe ich mal mit angehängt. Vielen Dank :)

Positionen_ETF.xlsx

Share this post


Link to post
stagflation
Posted · Edited by stagflation

Dafür gibt's die Pivot-Funktion! :)

 

In Deiner Excel-Tabelle stehen die Werte für die beiden ETFs noch nebeneinander:

Name               ETF1          Name              ETF2
Microsoft Corp.    2,91          Nestle SA         4,527
Apple Inc.         2,88          Roche Holding AG  3,413
Amazon.com Inc.    1,8497        Novartis AG       2,71
Facebook Inc.      1,0922        SAp Se            1,822

Kopiere die zweite Tabelle unter die erste, damit Du nur noch eine Tabelle hast. Die Prozent-Angaben für die beiden Tabellen sollten aber in zwei unterschiedlichen Spalten stehen.

Name               ETF1       ETF2
Microsoft Corp.    2,91
Apple Inc.         2,88
Amazon.com Inc.    1,8497
Facebook Inc.      1,0922
...
Nestle SA                     4,527
Roche Holding AG              3,413
Novartis AG                   2,71
SAp Se                        1,822

Markiere die gesamte Tabelle und rufe "Pivot Tabelle erstellen" auf. Für die Zeilenfelder wählst Du "Name" und für die Datenfelder SUMME( ETF1 ) und SUMME( ETF2 ). Dadurch erhältst Du folgende Tabelle:

Name                            ETF1       ETF2
1&1 Drillisch AG                0,0019     0,0150
3i Group plc                    0,0289        
3M Co.                          0,1988        
77 Bank Ltd.                    0,0012        
a2 Milk Co. Ltd.                0,0168        
A2A Spa                         0,0056     0,0410
AAC Technologies Holdings Inc.  0,0099        
Aalberts NV                     0,0098     0,0550
ABB Ltd.                        0,0915     0,5900
...

Anmerkungen:

  1. Die Aggregierung klappt natürlich nur, wenn die Namen der Firmen in beiden Tabellen exakt gleich geschrieben sind. Weil Namen manchmal unterschiedlich geschrieben werden, wäre es besser, als Aggregierungs-Kriterium nicht den Namen, sondern die WKN oder ISIN zu verwenden - die gibt es in Deiner Tabelle jedoch nicht.
  2. Einzelne Firmen sind in den ETFs mehrfach vorhanden (Beispiel: "Sodexo SA"). Durch die Summe-Funktion wird automatisch die Summe berechnet.
  3. Ich habe das mit LibreOffice Calc durchgerechnet  - es kann also sein, dass die Bezeichnungen in Excel etwas anders sind.

 

Share this post


Link to post
slekcin
Posted · Edited by slekcin

Vielen Dank für deine Hilfe. Ich hatte auch schon an die Pivot Funktion gedacht aber noch nie damit gearbeitet und es dann irgendwie mit Summewenn gerechnet. Wenn ich die ganzen % Werte in eine lange Tabelle packe, bekomme ich so ja auch direkt den Gesamtanteil einer Firma im Portfolio. Gibt es eine Möglichkeit, dass Excel/LibreOffice versteht, wenn ich in meiner Ausgangstabelle z.B. ETF1 in Spalte 1+2 eintrage (Spalte 1 Name, Spalte 2 %), ETF2 in spalte 3+4 und ETF 3 in Spalte 4+5 ? Oder müssen alle Namen in Spalte 1 und alle % in Spalte 2 ? Nur wegen der Übersicht. 

 

Mit den WKN hast du auch recht. Vanguard gibt die leider nicht an. 

 

Die Ausführung ist nicht schön aber ich habe die Gewichtung der Positionen mit 50% World und 20% Eur. Ex UK herausgearbeitet. Fehlt noch das Einpflegen des EM ETFs :D

 

 

PositionenETFNeu.xlsx

Share this post


Link to post
yggde
Posted
vor 12 Stunden von slekcin:

Hallo, 

 

ich war leider noch nie wirklich gut mit Excel ...

SVERWEIS macht das ganz easy.

Du fügst diese formel "=SVERWEIS(A8;sheet1!J:K;2;FALSCH)" in zelle c8 ein, markierst dann bereich c8 bis c2192 und drückst STRG+U (in Excel), dann siehst Du überall da die Prozenztzahlen aus EUR ex UK
=>ident. Firmennamen vorausgesetzt

 

Share this post


Link to post
stagflation
Posted · Edited by stagflation
vor 3 Stunden von yggde:

SVERWEIS macht das ganz easy.

 

SVERWEIS ist einfacher als die Pivot-Funktion - liefert aber möglicherweise (je nachdem, was man will) auch nicht die gewünschten Ergebnisse:

  • es zeigt nicht die Positionen von ETF2, die es in ETF1 nicht gibt
  • es zeigt nicht die gewünschten Zahlen, wenn einzelne Positionen mehrfach in den ETFs vorhanden sind (Beispiel: "Sodexo SA"), weil es in diesem Fall keine Summe bildet.

Share this post


Link to post
yggde
Posted
vor 2 Stunden von stagflation:

 

SVERWEIS ist einfacher als die Pivot-Funktion - liefert aber möglicherweise (je nachdem, was man will) auch nicht die gewünschten Ergebnisse:

  • es zeigt nicht die Positionen von ETF2, die es in ETF1 nicht gibt
  • es zeigt nicht die gewünschten Zahlen, wenn einzelne Positionen mehrfach in den ETFs vorhanden sind (Beispiel: "Sodexo SA"), weil es in diesem Fall keine Summe bildet.

Wenn mehrfach je Liste dann würde ich es so machen:

Erstellen einer Liste aller Firmennamen  durch zusammenkopieren der Firmenname in eine Spalte
Dubletten entfernen
mit summewenn die Werte je ETF zusammenaddieren
voilá

Share this post


Link to post
stagflation
Posted · Edited by stagflation
vor 18 Stunden von slekcin:

Die Ausführung ist nicht schön aber ich habe die Gewichtung der Positionen mit 50% World und 20% Eur. Ex UK herausgearbeitet. Fehlt noch das Einpflegen des EM ETFs :D

 

Deine Excel-Datei mit der Pivot-Tabelle sieht doch schon sehr gut aus! Bei den Quelldaten hast Du gleich noch eine Umrechnungstabelle hinzugefügt, in der die prozentualen Anteile der ETFs eingerechnet werden. Gute Idee! :)

 

Du könntest in diese Umrechnungstabelle eine weitere Spalte "Summe_ETF" hinzufügen und bereits dort die Summe aus ETF1 und ETF2 berechnen. In der Pivot-Tabelle nimmst Du diese Spalte als drittes Datenfeld "Summe( Summe_ETF )" mit auf. Dann musst Du auf dem Arbeitsblatt mit der Pivot-Tabelle nicht noch eine Summenspalte außerhalb der Pivot-Tabelle hinzufügen - und die Filterfunktion der Pivot-Tabelle funktioniert auch besser.

 

Eine Erweiterung um den dritten ETF sollte relativ einfach sein, oder? Du hättest in der Umrechnungstabelle dann 5 Spalten: Name, ETF1, ETF2, ETF3, SUMME_ETF.

 

vor 18 Stunden von slekcin:

Gibt es eine Möglichkeit, dass Excel/LibreOffice versteht, wenn ich in meiner Ausgangstabelle z.B. ETF1 in Spalte 1+2 eintrage (Spalte 1 Name, Spalte 2 %), ETF2 in spalte 3+4 und ETF 3 in Spalte 4+5 ? Oder müssen alle Namen in Spalte 1 und alle % in Spalte 2 ? Nur wegen der Übersicht.

Ich habe das gerade mal getestet. In LibreOffice Calc kann man durchaus mehr als einen Bereich markieren. Mann kann dann aber keine Pivot-Tabelle erstellen. Das geht offenbar nur, wenn man nur einen zusammenhängenden Datenbereich auswählt. Möglicherweise geht es in Excel.

Share this post


Link to post
slekcin
Posted · Edited by slekcin

Hat alles soweit gut funktioniert. Vielen Dank nochmal. Ich habe jetzt noch den EM Markets ETF dazu gepackt, wobei ich hier nicht auf Doppelungen wegen unterschiedlicher Schreibweise geachtet habe. Mich interessieren eig. eh nur die Top Holdings. Bei Südkorea kommt es bei mir z.B. ja zu Überschneidungen (FTSE und MSCI Mix). Ich habe jetzt auch mal noch die Branchen zwischen dem reinen World und einem 50/20/30 Mix verglichen. Dazu habe ich aber alle Daten von iShares genommen, weil Vanguard und iShares die Branchen unterschiedlich zuordnet (siehe: https://www.de.vanguard/web/cf/professionell/de/produktart/detailansicht/etf/9505/EQUITY/portfolio und https://www.ishares.com/de/privatanleger/de/produkte/251850/ishares-msci-acwi-ucits-etf). Bei Financials gibt Vanguard 20,8% and wohingegen iShares nur 15,9% Financials listet. Würde mich wundern, wenn die so weit von einander abweichen ? 

 

Die Idee von yggde habe ich ehrlich gesagt noch nicht getestet. 

PositionenETFNeu.xlsx

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...