Index Vergleich Sortierung 2 Bedingungen

[LeonP]

Hallo Leute,

für meine Masterarbeit muss ich eine Zeitreihenanalyse machen. Das Problem ist das chronologische ordnen der unterschiedlichen Variablen der jeweiligen Unternehmen (Price, Earnings, etc.).

Ich habe einen raw Datensatz (siehe Anhang) in dem für jedes Unternehmen i zu jedem Zeitpunkt t für jede Variable v eine eigene Zeile erstellt wurde. Für meine Analyse möchte ich aber für jede Variable v einen eigenen Reiter erstellen wo jedes Unternehmen i eine einzelne Spalte ist und jeder Zeitpunkt i entweder einen Wert oder null ausspuckt (also einfach in eine simple Matrixform). Die Unternehmen können an ihrem eindeutigen CUSIP identifiziert werden. Leider hat die Ursprungsdatei um die 1 Mio. Einträge, daher kann ich es nicht manuell machen und mit Index/Vergleich bzw. SVerweis mit Hilfsspalte habe ich es nicht hinbekommen.

Könnt ihr bitte einem Amateur aushelfen?

Vielen herzlichen Dank und liebe Grüße
Leon

[DL]

Hallo Leon

Füge eine Pivot-Tabelle ein.
Berichtslayout auf Tabellenformat ändern.
Teilergebnisse nicht anzeigen.
CUIP und Company Name in die Zeilen.
Price - Close - Monthly in den Wertebereich und auf Summe ändern.
Data Date - Security Monthly in die Spalten. In aktuellen Versionen wird automatisch gruppiert. Das kann man aber rückgängig machen.

[gmg-cc]

Moin Leon,
ich bin davon überzeugt, dass diene Kommilitonen verstehen, was du willst. Es ist ja euer Fach-Jargon.
Ich habe nicht verstanden, was du nun genau erreichen willst. Ein schönes Stichwort ist in deinem Text, so ziemlich mittig: "simpel".

Beispiel: Im Excel-Sprachgebrauch sind Variablen eher in Makros/VBA angesiedelt. Ansonsten wäre ein Wunschergebnis von einigen Zeilen hilfreich. - Ich könnte mir aber vorstellen, dass du mit einer PivotTable weiter kommst.

[LeonP]

Hallo Detlef,
Hallo Jürgen,

erstmal vielen vielen herzlichen Dank für Eure Hilfe, und das am heiligen Sonntag! 

Mit einer Pivottabelle hat es wunderbar geklappt. Hier ein Screenshot vom Ergebnis!!

Ihr habt einem armen Studenten den *rsch gerettet!

[LeonP]

Hi Leute,

wollte jetzt nicht einen extra Thread aufmachen, leider ist nun wieder ein "neues" Problem aufgekommen. Kurze Erklärung:

1. Ich habe einerseits monatliche Daten (zB. Preise) und andererseits Quartalsdaten (zB. Anzahl ausstehende Aktien)
2. Ich möchte diese miteinander multiplizieren (Ergebnis = Marktwert Unternehmen)
3. Um den korrekten monatlichen Marktwert zu ermitteln, soll Excel jeweils in den Monaten 1, 2, 3, die Anzahl der ausstehenden Aktien aus der Vorperiode (Q4 2012) nehmen und ab Monat 4 dann die Anzahl ausstehender Aktien Q1 2013.
4. Zusätzlich soll die Formel noch die Namen aus den jeweiligen Reitern "Preise" und "Anzahl ausstehender Aktien" vergleichen und nur dann multiplizieren wenn diese Übereinstimmen (Sichereitsmechanismus).

Ich habe es über eine Index Vergleichsfunktion probiert, kam aber zu keinem Ergebnis...

Vielen vielen herzlichen Dank!

P.S.: Anbei ein Ausschnitt aus der Tabelle

[DL]

Hallo Leon

Wenn die Reihenfolge der Name in allen Tabellen identisch ist - kann man durch sortieren erreichen - dann in D2:

Code: [Auswählen]

=INDEX('Number of Shares'!$1:$1048576;0;
   VERGLEICH("Q"&AUFRUNDEN(MONAT(EDATUM(D$1;-3))/3;0)
   &" "&JAHR(EDATUM(D$1;-3));
   'Number of Shares'!$1:$1;0))*'Price monthly'!D2

[LeonP]

Hi Detlef!

Erstmal vielen Dank für die Antwort. Leider klappts mit der Formel nicht so wie erhofft. Entweder #NV oder 0er Wert. Evtl könntest du dir es freundlicherweise noch mal angucken? Gerne schicke ich dir auch mein Excelfile. Ist nur etwas zu groß für den Upload hier

EDIT/ Funktioniert doch ab dem 6. Monat (habe dann einfach die -3 auf -2/-1/0 reduziert, dann hat's auch bis Q1 geklappt!
Tausendfachen herzlichen Dank!

Eine (vorerst) letzte Frage kommt jetzt noch bei mir auf:

Ich möchte irgendwie die Datenkonsistenz überprüfen: Es kann sein, dass Einträge aus gewissen Zellen leer sind, obwohl beide angrenzenden einen Wert enthalten. Zum Beispiel:


Number Of Shares in Q1 2000 = 50000 / Q2 2000= 0 / Q3 2000 = 50000
=> Es kann kein Market Value ermittelt werden


Ist dem so, liegt dies vermutlich an der Datenbank (Datenquelle) und ich würde diejenige Zelle gerne manuell überprüfen (sisyphos Arbeit incoming!). Gibt es eine Möglichkeit das visuelle (Farbe) oder an den Enden der Matrix mathematisch irgendwie darzustellen??

Edit 2/ Nur um das klarer zu formulieren: Die Anzahl der Aktien muss nicht zwangsläufig gleich bleiben, ich möchte eher "krasse Sprünge" zwischen den Quartalen aufzeigen.


Vielen vielen herzlichen Dank und viele Grüße
Leon

[DL]

Hallo Leon

Lade mal ein Beispiel hoch in dem du dann die Zellen manuell einfärbst um zu sehen Fälle es gibt.

[LeonP]

Hi Detlef,

anbei meine S&P Daten auf Quartalsbasis (wollte testen ob es an den monatlichen Aktienpreise liegt, hat aber zu keiner Verbesserung geführt). Leider ist die Datei zu groß für den Upload hier im Forum:

http://cloud.directupload.net/92dC

Kurze Erklärung des eigentlichen Problems:
- Zu jedem Zeitpunkt (zB. Q1 1970) stehen mir für Prices und Shares um die 800 Beobachtungen zur Verfügung (siehe gelbe Zeile am Ende von Reiter 1. Prices und 2. Shares, analog im Reiter 3. Market Value.
- Reiter 4. Timeline gibt wider wann ein Unternehmen im Index war und wann nicht (sollten immer um die 500 sein)
- Im Reiter 5. S&P Market Value, soll nun geprüft werden ob das Unternehmen im Index war oder nicht (d.h. multipliziert 3. Market Value mit 4. Timeline)

=> Hier gibt es zu wenig Beobachtungen (anfänglich um die 460), wodurch ich zuviel "Marktwert" des Indices vernachlässigen würde (= nicht gut). Ich muss jetzt quasi prüfen, woran das liegt. Möglichkeiten:

1. Ich habe einen methodischen Fehler in den Arbeitsblättern (manche Zahlenreihen sind nicht da, wo sie hingehören). Gäbe es da vielleicht eine Index(Vergleich)-Funktion mit der ich "auf der sicheren Seite" wäre?

2. Daten sind nicht konsistent.
=> Dazu habe ich im letzten Reiter ein paar Zellen rot markiert, die das oben geschilderte Problem der "starken Abweichung zwischen 2 Zeitpunkten" aufweisen. Evtl könnt ihr mir da eine Lösung für sagen?

P.S.: Entschuldigt das schlechte Deutsch, ich bin einfach von den ganzen Tagen Excel komplett Bala-Bala...

Tausend Dank, einen schönen Abend und viele Grüße
Leon

[DL]

Leider ist die Datei zu groß für den Upload hier im Forum:

http://cloud.directupload.net/92dC

Tut mir leider, aber ich lade mir jetzt nicht eine 17 MB-Datei runter.
Bitte kürze die Datei auf 20 bis 30 Datensätze.

[LeonP]

Hi Detlef,

sorry! Hier

[DL]

Hallo Leon

$C$2:$GE$66 markieren
Formel für die bedingte Formatierung:

Code: [Auswählen]

=WENN(C2+D2=0;FALSCH;WENNFEHLER(ABS((D2/C2)-1)>0,5;WAHR))Ggf. den Wert 0,5 verändern.

[LeonP]

Hallo Detlef,

langsam bin ich mit meiner empirischen Auswertung fertig. Eine letzte Formel Frage hätte ich aber noch, bitte hilf mir "noch ein letztes mal"

Und zwar habe ich meine Ratio's berechnet, jetzt möchte ich in einer Tabelle (s. Upload Reiter "Cape-Ratio Analysis") ausweisen, zu welchem Zeitpunkt der Wert minimal und warnn der Wert maximal war. Ich habe es über eine Index(Vergleich) probiert, jedoch glaube ich nicht dass das geht, weil Spalte/Zeilen vertauscht sind (s. Upload Reiter "Capem-Ratio Calculation"). Transponieren ist leider keine Option...

Könntet ihr mir vielleicht auf die Sprünge helfen?

Tausend Dank und viele Grüße
Leon

[DL]

Hallo Leon

INDEX() und VERGLEICH() sind schon richtig. Mehr kann ich dazu ohne Beispielmappe nicht sagen.

[LeonP]

Ah, dickes sorry! Anhang vergessen...

[DL]

Da sowohl der Minimum-Wert und der Maximum-Wert nicht im Blatt "Cape-Ratio Calculation" stehen ist ein echter Test nicht möglich.

Code: [Auswählen]

=INDEX('Cape-Ratio Calculation'!C$2:M$2;VERGLEICH(E3;'Cape-Ratio Calculation'!C3:M3;0))