VBA Noob: Wenn Quartal dann X in Matrix

  • Excel

Es gibt 12 Antworten in diesem Thema. Der letzte Beitrag () ist von LeonP.

    VBA Noob: Wenn Quartal dann X in Matrix

    Hallo liebe Leute,

    leider hat die SuFu nichts ergeben, deshalb hier meine Anfrage:

    Im Rahmen eines Uniseminar muss ich historische Daten des S&P500 sammeln und auswerten. Da sich die Zusammensetzung des Indices im Zeitablauf natürlich ändert, muss ich zuallererst (meiner Meinung nach) filtern, wann, welches Unternehmen im Index war und wann nicht.

    Die Daten habe ich mir gezogen und das jeweilige Eintritts- und Austrittsdatum in das entsprechende Quartal umgewandelt. Im nächsten Schritt, möchte ich quasi das in der Quartals-Matrix ein "X" gesetzt wird, für jedes Quartal in welchem das Unternehmen im Index enthalten war und wenn nicht, soll die Zelle leer bleiben. Leider denke ich, dass das nicht mit einer einfachen Excelformel lösbar ist und wende mich als VBA-Noob damit an euch Profis!

    Anbei ein Auszug meiner bisherigen (manuellen) Auswertung zur Veranschaulichung.


    Vielen herzlichen Dank! Ihr rettet einem armen Studenten den A****

    Viele Grüße
    Leon
    Dateien

    • SP500_Comp.xlsx

      (958,68 kB, 314 mal heruntergeladen, zuletzt: )

    LeonP schrieb:

    Leider denke ich, dass das nicht mit einer einfachen Excelformel lösbar ist
    Ich denke, dass es sogar am einfachsten mit einer Excel-Formel lösbar ist.
    Pseudocode:
    Wenn Ende(Quartal)>=StartDatum und Anfang(Quartal)<EndeDatum Dann X

    Im übrigen gibt es S&P-Unternehmen, die mehrfache Anwesenheitszeiten im Index haben.
    Mal fliegen sie raus, mal kommen sie wieder rein.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Hi Petaod,

    erstmal vielen Dank für die schnelle Rückmeldung. Leider kommt bei mir nicht das gewünschte (bzw. kein) Ergebnis raus, oder interpretiere ich deine Formel falsch?

    =WENN(UND(G2>=D2;F2<E2);"x";"")

    Zum Thema mal im Index, mal nicht: Die Daten stammen von Wharton Database Services. Ich nehme einfach die Richtigkeit an, ohne sie weiter zu hinterfragen ;)

    Hallo,

    da gibt es mehrere Punkte zu korrigieren. Das Datumsformat in D und E lässt mein Excel beim Vergleich stolpern. Dann muss in den Formeln das jeweilige Quartal berücksichtigt werden.

    Packst Du in Zellen D2 und E2 ein 'richtiges' Datum (02.01.1966 und 27.07.2000), dann sollte mit folgender Formel in H2, welche sich dann einfach ziehen lassen sollte, das gewünschte Ergebnis liefern. Willst Du die Datumsformate in D und E nicht korrigieren, dann die Formel entsprechend erweitern. (Viel Spaß dabei)

    Visual Basic-Quellcode

    1. =WENN(UND(DATUM(RECHTS(H$1;4);RECHTS(LINKS(H$1;2);1);1)>=$D2;DATUM(RECHTS(H$1;4);RECHTS(LINKS(H$1;2);1)+3;1)-1<$E2);"x";"")
    Bilder

    • Unbenannt.PNG

      13,7 kB, 1.098×115, 133 mal angesehen
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Hallo euch beiden!

    Erstmal vielen vielen herzlichen Dank für Eure Mühe! Deine Formel sieht super aus, Peterfido, funktioniert leider aber nur wenn es auch ein Enddatum gibt (also wenn Spalte E nicht leer ist). Gibt es da evtl noch eine kleine, letzte Korrektur damit sie auch funktioniert wenn Spalte E nicht leer ist?

    Edit/ Wenn ich Als Enddatum das heutige Datum einpflege klappts! Tausend Dank!

    Ein letztes Problem kommt noch auf: leider "verschiebt" sich mit der Formel alles um 1 Quartal (also Enddatum eigentlich Q2 1984 und das X ist noch bis Q3 1984 vermerkt). Wie lässt sich das beheben? :)

    Nochmals vielen Dank! Ihre rettet mich :)

    LG
    Leon

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „LeonP“ ()

    Hi Leute,

    ich habe die Formel weiter "durchforstet" und stelle fest, dass das Problem nur manchmal aufkreuzt und manchmal sogar 2 Quartale dazwischen liegen (wenn der Stichtag der letzte Tag eines Quartals ist). Könnte dieses Problem einfacher behoben werden, wenn ich in der Spalte A kein Quartal angebe, sondern ein 'richtiges' Datumformat (bspw. 31.03.1970)? Leider hat es bei meinen Gehversuchen nicht ganz geklappt...

    Nochmals vielen herzlichen Dank!

    VG
    Leon

    Ich habe nicht nachgeprüft, wie du das Datum des Quartals berechnest.

    Streng genommen gehört zu einem Quartal eh alles was >= dem Quartalsbeginn und < dem Beginn des Folgequartals liegt.
    Einen echten Timestamp für Quartalsende gibt es nicht. Der wäre am Ende immer unscharf um den Wert der verwendeten Granularität.

    Und ja: Ich würde eine Datumsangabe für die Formeln verwenden, weil Excel damit rechnen kann.
    Aus einer Quartalnummer das Datum des Quartalbeginns zu ermitteln ist ja nicht so schwer.

    LeonP schrieb:

    Die Daten stammen von Wharton Database Services. Ich nehme einfach die Richtigkeit an, ohne sie weiter zu hinterfragen
    Welche Daten werden da z.B. für Covidien veröffentlicht?
    Die waren 2008-2009 und 2012-2015 im Index.
    Da kann nicht nur ein Start- und ein Endedatum sein.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „petaod“ ()

    Danke petaod für die schnelle Antwort. Leider werde ich Dummie nicht ganz schlau aus deinem Post :D

    Könntest du mir auf die Sprünge helfen und mir sagen, wie die Formel aussehen würde wenn ich ganz normal nach einem Datumsformat formatiere? :)

    Tausend Dank!!

    Edit/ Wenn ein Unternehmen mehrfach im Index gelistet war, erfasst die Datenbank es doppelt:
    COVIDIEN PLC 02.07.2007 - 04.06.2009
    COVIDIEN PLC 01.03.2011 - 26.01.2015

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „LeonP“ ()

    Im Prinzp wie in Post #3
    In Spalte G steht der Anfang des Folgequartals.

    LeonP schrieb:

    Wenn ein Unternehmen mehrfach im Index gelistet war, erfasst die Datenbank es doppelt
    Das ergibt bei dir zwei Zeilen für dieselbe Firma.
    Ist das so gewollt?
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Hallo,

    ich habe gerade nix zum Testen hier. Passt die Formel evtl. mit dem -1 am Ende doch besser? Manchmal ist ja der erste Gedanke der Richtige...
    Ansonsten versuch die Formel zu verstehen. Evtl. findest Du dann den Fehler. Auch möglich, dass das Quartal falsch ermittelt wird. Ich habe die Formel nur in der ersten Zeile probiert und das Ergebnis sah auf den ersten Blick gut aus.
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    petaod schrieb:

    Im Prinzp wie in Post #3
    In Spalte G steht der Anfang des Folgequartals.
    Das ergibt bei dir zwei Zeilen für dieselbe Firma.
    Ist das so gewollt?


    Ich habe es gelöst. Es stimmt jetzt. Vielen herzlichen Dank!

    Nein, es ist natürlich nicht optimal, dass es mehrere Zeilen gibt, jedoch weiß ich nicht, wie ich dies optimieren könnte (Stichwort SVerweis?).