Office: Hilfe: Berechnung von Spreads (kompliziert)

Hallo ihr Lieben,
ich habe eine etwas komplizierte Frage, die ich so nicht im Forum gefunden habe. Ich hab meine Fall etwas gegliedert für ein bisschen Übersicht.

Ausgangssituation:
Ich habe die besten Bids und Quotes, als auch stattgefundene Trades von ein paar Aktien für einen gewissen Zeitraum. Die Trades finden dabei logischerweise nicht an festen Tagen statt sondern zufällig.

Meine Aufgabe:
Ich muss den Relative Effective Spread mit Midpoint für jede Aktie über einen gewissen Zeitraum berechnen.

Formel Relative Effective Spread:
[Preis des letzten Trades – Midpoint] / Midpoint

- Midpoint ist dabei die Summe des Best Bid und Ask geteilt durch 2

Bild als Beispiel:




Meine Frage an Euch:
Wenn ich z.B. den Spread für Zelle F80 berechnen will, dann soll Excel automatisch überprüfen, wann zuvor ein Trade stattgefunden hat. Für Zelle F80 müsste das dann Zelle F76 sein.

Dann soll Excel ebenfalls in der Formel...
nach den letzten Bid und Ask Preisen (und davon die Summe bilden und durch 2 Teilen) suchen die vor dem Trade in Zelle F76 geposted wurden.

In dem Fall also:
((J75-L75)/2

Im Endeffekt lautet die mathematische Formel ausgehend von Zelle F80:
(F76 - ((J75+L75)/2)) geteilt durch ((J75+L75)/2)

Da die Trades eben in unterschiedlichen Abständen stattgefunden haben, muss die Formel wie ich annehme durch Abfragen oder ähnlichem erweitert werden.

Ich hoffe Ihr könnt mir irgendwie Helfen. Ich habe 30 solcher Tabellen mit jeweils ca. 1000 Zellen. Es wäre ein Ding der Ewigkeit die Formel manuell einzugeben für jeden Trade.
Vielen Dank

 

Hallo,

in der beiliegenden Excel-Datei habe ich in einem allgemein Codemodul eine VBA-Funktion integriert, die den "Relative Effective Spread" berechnet.
Im Arbeitsblatt "Tabelle1" habe ich die Daten aus Deinem JPG-Bild eingetippt und in Spalte N in den Zeilen, die das Wort "Trade" in Spalte F enthalten, jeweils die Funktion angewendet.
Im Gegensatz zu Deinem Formbelbeispiel bezieht sich mein Makro auf die Spalte G (zB. G76), um den Trade-Price zu ermitteln, und nicht auf F76.
In den Spalten J und L sucht mein Makro solange zurück, bis es jeweils auf den ersten Bid-Price bzw. Ask-Price stößt.

Die Formel, die sich genau auf Dein Formelbeispiel bezieht, befindet sich somit in Zelle N80 und lautet:
=RelEffSpread(F80;JETZT())
1) Der 1.Parameter (zB. F80) muss sich stets auf die Spalte F auf eine Zelle mit dem Eintrag "Trade" beziehen. Von dieser Zelle ausgehend, wird nach der letzten Zelle mit "Trade"-Eintrag gesucht. Die Zelle rechts davon wird dann als Trade-Price genommen und die jeweils letzten davorliegenden Werte aus den Spalten J bzw. L als Bid-Price bzw. Ask-Price.
2) Der 2.Parameter (zB. JETZT()) der Funktion ist optional; er sollte aber verwendet werden. Er dient dazu, eine Wiederberechnung der Funktion zu erzwingen, falls die Werte, die für die Berechnung nötig sind, sich geändert haben sollten. Wird dieser Parameter weggelassen, erfolgt keine automatische Neuberechnung im Falle von geänderten Werten.

 

Vielen, vielen Dank. Das hat mir sehr geholfen. Jetzt kann ich im Prinzip die Daten aus den anderen Datensätzen in die angehängte Excel Datei einfügen und sozusagen als Template benutzen.