Office: Bestände aus langer kauf/verkauf-Liste ermitteln

Hallo,

ich möchte derzeit eine Auswertung verschiedener Aktienportfolios machen. Dazu habe ich je Portfolio eine lange Liste mit den einzelnen Trades (d.h. Kauf/Verkauf der einzelnen Aktien).
D.h. in Spalte A habe ich jeweils das Datum der Transaktion, in B die Aktie bzw. Wertpapierkennnummer und in den anderen Spalten noch weitere Infos, was aber erstmal unwichtig ist.

Was ich nun machen möchte, wäre die Aktienbestände zu jedem Zeitpunkt zu sehen. Z.B. habe ich in meiner Liste die Aktien A, B und C die ich ständig kaufe und verkaufe. Jetzt möchte ich, dass in einer extra Tabelle (1.) sämtliche vorhandenen Aktien im Portfolio (also aus der Liste in Spalte A) identifiziert und (in einer Zeile) aufgelistet werden. Im zweiten Schritt soll dann zu jedem Zeitpunkt der aktuelle Bestand an den jeweiligen Aktien ausgegeben werden.
Ich habe das Ganze vereinfach in einem Beispiel dargestellt. Ich denke, dann wird es klar. In meinem Beispiel ist das recht einfach und übersichtlich. Die tatsächlichen Listen haben aber 20.000 Trades und von Hand die Portfoliobestände je Zeitpunkt zu bestimmen ist etwas aufwendig... ;)

Für eure Hilfe wär ich sehr dankbar. Ich komm einfach nicht drauf.


Viele Grüße

 

Hallo jep,

habe Dir mal meinen Lösungsvorschlag in Deine Tabelle eingebaut.
(Übrigens hattest Du einen Rechenfehler in den letzten beiden Zeilen.)

Die Formeln in J3 K3 L3 unterscheiden sich von denen in den Zellen darunter.
Diese drei Zellen könnten eigentlich auch händisch, ganz ohne Formeln, ausgefüllt werden.
Die Formeln ab Zeile 4 einfach nach unten bzw. nach rechts ziehen....

Hoffe Dir damit geholfen zu haben.


Edit für die Übersichtlichkeit für Mitlesende:

hier meine Formel aus J4

Code:

=WENN($B4=J$2;WENN($D4="kauf";J3+$E4;J3-$E4);J3)

 

Super soweit, vielen Dank. Hätte ich auch irgendwie selbst drauf kommen können... aber wie das manchmal eben so ist,... :)

Was mir jetzt aber noch fehlt (und das ist denke ich auch der schwerere Part), wäre im Beispiel auf der rechten Seite die Spaltenüberschriften, d.h. Aktien automatisch zu haben. In meiner Liste gibt es 50 verschiedene Aktien und es wäre irgendwie mühselig jede einzeln in die Spalten eintragen zu müssen.

Wenn Du oder sonst jemand mir da noch weiterhelfen könnte wär das absolut klasse.

Bis dahin jedenfalls nochmal vielen Dank.

 

sry, da muss ich passen.
Habe zwar etwas darüber gegrübelt, aber dafür reichen meine Formelkenntnisse nicht aus.
Mit VBA könnte ich sowas.


Hoffe ein Anderer kann Dir helfen.

 

VBA-Lösung nehme ich auch sehr gerne. :)

 

Ich hätt mir den Satz kneifen sollen... ;O)

Dafür müsste ich aber erstens wissen wieviel Vorkenntnisse Du selber in VBA hast, sprich inwieweit Du in der Lage bist den Code an Deine Originaldatei anzupassen, oder ich müsste die Originaldatei haben, bzw. zumindest den exakten und vollständigen originalen Aufbau.

Ich würde keine Gewähr übernehmen und möchte Dich darauf hinweisen, dass es hier andere gibt, die auch das besser können als ich.

Bei so einer Datenmenge würde ich persönlich sowieso VBA bevorzugen, aber da mögen schlauere Köpfe anderer Meinung sein.

 

Hab schon das ein oder andere mit VBA "programmiert" bzw. vorhandene Codes zusammengebastelt (Selber komm ich leider meist nicht drauf :/ ). Insofern wär das kein Problem und Du kannst es gerne anhand der Beispiel-Datei machen. Die originale (da sensible Daten darf ich die leider nicht weitergeben) Datei sind nicht viel anders aus bzw. kann ich dann selbst adaptieren.

Falls Du direkt weißt wie es geht und mir helfen würdest wär das wirklich super.
Vielen Dank!

 

Hallo jep,

ich habe die Makros in der angehängten Datei eingebaut.
Auch habe ich versucht sie so einfach und nachvollziehbar wie möglich zu halten.
Falls Du noch Fragen haben solltest...

Um die Bestandsliste überhaupt erst einmal zu erstellen musst Du

Code:

Private Sub BeständeManuellFortschreiben()

im Modul1 zuerst manuell aus dem VBA-Editor heraus starten.
Danach kann dieses Modul eigentlich gelöscht werden.

Hast Du Modul1 laufen lassen, dann gehe im VBA-Editor in

Code:

Tabelle1(Tabelle1)

und ändere diese Zeile

Code:

Private Sub ausgeschaltet() 'Worksheet_SelectionChange(ByVal Target As Range)

in

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Das darfst Du auf keinen Fall vorher tuen!

Danach wird die Bestandsliste jedesmal aktualisiert sobald Du eine neue Buchung in Deiner Buchungsliste eingibst und dort das Volumen einträgst (Anzahl).
Die Formeln fallen also weg.

Ich hoffe ich habe keinen Fehler übersehen und die Makros aussagekräftig genug beschriftet.
Gewähr übernehme ich keine, also teste das erstmal an einer Kopie Deiner Originaldatei.

Hoffe Dir damit geholfen zu haben.

 

schon passiert:
Die Eingabe des Volumens wurde nicht auf Zahl oder Buchstabe geprüft, und Buchstaben kann man ja schlecht addieren oder subtrahieren.

Deshalb in

Code:

Tabelle1(Tabelle1)

Zeile 28

Code:

If ThisWorkbook.Worksheets("Tabelle1").Cells(letzteZeile + 1, 5) <> "" Then

ändern in:

Code:

If ThisWorkbook.Worksheets("Tabelle1").Cells(letzteZeile + 1, 5) <> "" And _
        IsNumeric(ThisWorkbook.Worksheets("Tabelle1").Cells(letzteZeile + 1, 5)) Then

Wird jetzt etwas anderes als eine Zahl eingetippt, passiert garnichts.

Habe die Datei nochmal neu angefügt.

 

Super! Genau so wollte ich das, wobei mir der Erste Schritt schon genügt, d.h. dass es mir einfach die Bestände berechnet. Nur was mich momentan fast zur Verzweiflung bringt... ich bekomm es nicht in meiner Arbeitsmappe zum laufen

Obwohl die genau so aussieht, nur eben wesentlich umfangreicher ist. D.h. ich habe 1-5 Tabellenblätter, die GENAU so wie das Bsp aufgebaut sind nur eben 20.000 Trades haben. Hab das Tabellenblatt auch Tabelle1 benannt. Zudem das Stammdaten-Tabellenblatt eingefügt. Danach dann noch Modul1, sowie den VBA-Code in Tabelle1 (den ich wohl nicht brauche, wenn mir der erste Schritt genügt, nehme ich an) reinkopiert. Aber dann kommt ein Fehler.

Weißt Du spontan woran das liegen könnte? Wenn ich den Inhalt in das bsp-Excel kopiere, funktioniert es zum Teil. Kopiere ich 5000 Zeilen, geht es nicht (obwohl Spaltenmässig noch mehr als genug Platz ist), kopiere ich weniger funktioniert es wunderbar. --> Gibt es eine Obergrenze?


Vielen Dank jedenfalls. Hat mir bereits jetzt schon sehr sehr geholfen!

 

Hallo jep,

freut mich das ich helfen konnte.

Warum irgend etwas bei Dir nicht funktioniert... woher soll ich das wissen?

Ich kenne Deine Datei ja nicht, weiß nicht ob noch anderer Code darin ist oder sonst etwas was quer schießt.

Ab hier wirds Kaffeesatzleserei.

Deshalb noch einmal:

Fertige eine Kopie Deiner Datei an und lösche die sensiblen Daten.
Lasse aber allen Code, alle Formatierungen, alle Formeln und sonst auch alles wie es ist.
Wenn ich exakt Deine Datei habe, dann kann ich sie mir anschauen und finde vielleicht den Fehler.

Ansonsten hab ich keine Chance und bin raus.

 

Hallo jep,

ich habe nochmal nachgeschaut.

Excel 2007 hat über 16000 Spalten und soll wohl ca. 1mio Zeilen haben (frühere Versionen hatten gut 64000).
Excel 2010 (welches Du benutzt) wird wohl nicht weniger haben.
Der Datentyp integer geht bis 32.767, sollte also noch reichen.
Wenn Dir das auf die Dauer nicht reicht, dann nehme long statt integer.

Aktuell sollte also nirgendwo ein Limit erreicht sein.


Als nächstes habe ich die Beispieldatei mit 20000 "Trades" und 200 verschiedenen Aktien ausgefüllt, sowie den Bestand von meinem Makro fortschreiben lassen.
Hat etwa 3 Minuten gedauert (habe nen alten Rechner), ging aber Problemlos.
Keine Fehlermeldung und beim drüber schauen kann ich auch nichts ungewöhnliches entdecken.

 

Sorry, wenn ich etwas unpräzise war. Ich weiß Deine Hilfe sehr zu schätzen. Warum die bsp-Datei nicht mehr funktioniert weiß ich zwar nicht, aber schätzungsweise war es ein Fehler von mir, dass ich was verändert und dann abgespeichert habe. Jedenfalls habe ich grade 2-3 Datensätze durchlaufen lassen und es hat perfekt funktioniert. So werde ich es dann auch mit den restlichen machen. Möchte ja schließlich deine Zeit nicht mehr als nötig beanspruchen :)

Wegen der Fehlermeldung...
Meine Datei ist exakt gleich aufgebaut wie das Beispiel, nur eben mit realen Daten und meistens um die 6000 Zeilen. Es gibt bislang keinerlei Makros etc.. D.h. ich habe den VBA-Code aus Modul1 in das neu erstellte Modul1 kopiert und den VBA-Code von Tabelle1 in die Tabelle1 kopiert. Also sollte es doch eigentlich exakt das Selbe sein. Wenn ich nun aber den Code in Modul1 ausführe, kommt:

Laufzeitfehler 1004: Anwendungs- oder objektdefinierter Fehler

Der Debugger springt auf die Zeile:

Worksheets("Tabelle1").Range(Cells(letzteZeile + 1, 10), Cells(letzteZeile + 1, AnzahlAktien + 9)).Value = Worksheets("Tabelle1").Range(Cells(letzteZeile, 10), Cells(letzteZeile, AnzahlAktien + 9)).Value



Aber wie gesagt wär es lediglich nett gewesen, aber ich kann genauso gut mit "deiner" Datei arbeiten und die Sachen dann hin und her kopieren.

Vielen vielen Dank, das war was ich gesucht hab und hilft mir enorm weiter!
Spitzen-Forum! :)

 

Hallo jep,

ich habe leider keine Ahnung warum dieser Fehler bei Dir auftritt.
Habe bei mir verschiedenes ausprobiert, konnte ihn aber nicht reproduzieren.
Da müsste vielleicht einer der richtigen Cracks hier mal was zu sagen.

Ich gehe aber davon aus, dass Du irgendwas im Code verändert hast, oder das Deine Datei eben doch kleine Unterschiede hat.

In der Beispieldatei hast Du die Zeile eins genutzt um uns etwas mitzuteilen.
Gibt es diese Zeile überhaupt in Deiner Originaldatei?
Und wenn nicht, bist Du sicher das Du im Code alles entsprechend angepasst hast?

Die Zeile:

Code:

Worksheets("Tabelle1").Range(Cells(letzteZeile + 1, 10), Cells(letzteZeile + 1, AnzahlAktien + 9)).Value = Worksheets("Tabelle1").Range(Cells(letzteZeile, 10), Cells(letzteZeile, AnzahlAktien + 9)).Value 

Kommt 4x vor.
Jeweils 2x in Modul1 und in Tabelle1.
Dort dann einmal für eine neue und einmal für eine bereits vorhandene Aktie.

Wo und wann tritt der Fehler auf? Das ist immer noch nicht deutlich geworden.

Sie kopiert bei einer neuen Buchung (bzw. einer neuen Zeile) alle Aktienbestände der bisher letzten Zeile in die neue Zeile.


Einzige Ausnahme:
Wenn Du Modul1 startest, bei der ersten Buchung.
Dann gibt es noch keine "alte" letzte Zeile und das Makro kopiert den Inhalt der leeren Zelle "J2" in "J3".
Ist J2 bei Dir am Anfang nicht leer?

Oder ist doch irgendwas anders?

Eine andere Erklärung finde ich nicht.

 

Die Leerzeile ist auch dort vorhanden. Der Code wurde 1:1 kopiert und nicht verändert. Der Fehler tritt direkt bei der Modul1-Ausführung auf.
Keine Ahnung woran es liegen könnte; ich hab es einige Male kopiert und meiner Ansicht nach sind die Dateien absolut identisch.

Aber es ist wirklich nicht weiter tragisch, da ich "halb-manuell" genau das bekommen werde, was ich wollte. :)