Liebe Newsgroup,

Ich komme schon wieder mit einer kleinen Frage... Ich entwickle z.Z.
gerade eine Funktion, die mir einigen finanzmathematischen "Kram"
ausspuckt. Soweit so gut. Dieser Funktion übergebe ich einen Wert,
welcher für die Berechnung des Resultats benötigt wird.

public function kor(tage as double) as double
kor = WorksheetFunction.Correl(Range(Cells(15, 9), Cells(15 + tage,
9)), Range(Cells(15, 3), Cells(15 + tage, 3)))
end function

Das ganze funktioniert auch. Das Problem ist nun aber, dass ich diese
Korrelationsberechnung nicht nur fix für die Bereiche 3 (Spalte C) und
9 (Spalte I) durchführen möchte, sondern für die Spalte, in der Tage
steht(den ich also der Funktion übergebe) und Spalte 3 (fix).

Oder anders ausgedrückt: mit dem Aufruf der Funktion vola(z.B. B2 -->
=10), soll die Korrelation im Range B15:B25 und C15:C25 ausgerechnet
werden. Rufe ich vola(z.B. F2 --> =5) auf, soll die Korrelation im
Range F15:F20 und C15:C20 berechnet werden.

So wie die Funktion jetzt angelegt ist, ist ja der Zellbezug fix. Er
reagiert nur auf den Übergabewert als Variable. Wie kann ich das
angehen?
Kann ich die Adresse von tage ermitteln und in 2 Variablen (Zeile,
Spalte) speichern und dann weiterverwenden?
Wie geht das?

Besten Dank für Eure Hilfe und freundliche Grüsse

Marco

Hallo Marco,

> public function kor(tage as double) as double
> kor = WorksheetFunction.Correl(Range(Cells(15, 9), Cells(15 + tage,
> 9)), Range(Cells(15, 3), Cells(15 + tage, 3)))
> end function
>
> Das ganze funktioniert auch. Das Problem ist nun aber, dass ich diese
> Korrelationsberechnung nicht nur fix für die Bereiche 3 (Spalte C) und
> 9 (Spalte I) durchführen möchte, sondern für die Spalte, in der Tage
> steht(den ich also der Funktion übergebe) und Spalte 3 (fix).
>
> Oder anders ausgedrückt: mit dem Aufruf der Funktion vola(z.B. B2 -->
> =10), soll die Korrelation im Range B15:B25 und C15:C25 ausgerechnet
> werden. Rufe ich vola(z.B. F2 --> =5) auf, soll die Korrelation im
> Range F15:F20 und C15:C20 berechnet werden.
>
> So wie die Funktion jetzt angelegt ist, ist ja der Zellbezug fix. Er
> reagiert nur auf den Übergabewert als Variable. Wie kann ich das
> angehen?
> Kann ich die Adresse von tage ermitteln und in 2 Variablen (Zeile,
> Spalte) speichern und dann weiterverwenden?
> Wie geht das?
Gib die Adresse der Bezugsspalte als String-Parameter ein:

Public Function kor(TagAdresse As String)
Set TagBereich = Range(TagAdresse)
nTage = TagBereich.Value
nZeile = TagBereich.Row()
nSpalte=TagBereich.Column()
kor = WorksheetFunction.Correl(_
Range(Cells(nZeile, nSpalte), Cells(nZeile+nTage, nSpalte)), _
Range(Cells(nZeile, 3), Cells(nZeile+nTage, 3)))

Die Funktion rufst Du dann so auf:
wert=kor("F10")
und in Zelle F10 muss die Anzahl der Tage stehen.

Gruß
Bernhard Sander

On 2 Nov., 14:20, Bernhard Sander <off...*kein.spam> wrote:
> Hallo Marco,
>
>
>
>
>
> > public function kor(tage as double) as double
> > kor = WorksheetFunction.Correl(Range(Cells(15, 9), Cells(15 + tage,
> > 9)), Range(Cells(15, 3), Cells(15 + tage, 3)))
> > end function
>
> > Das ganze funktioniert auch. Das Problem ist nun aber, dass ich diese
> > Korrelationsberechnung nicht nur fix für die Bereiche 3 (Spalte C) und
> > 9 (Spalte I) durchführen möchte, sondern für die Spalte, in der Tage
> > steht(den ich also der Funktion übergebe) und Spalte 3 (fix).
>
> > Oder anders ausgedrückt: mit dem Aufruf der Funktion vola(z.B. B2 -->
> > =10), soll die Korrelation im Range B15:B25 und C15:C25 ausgerechnet
> > werden. Rufe ich vola(z.B. F2 --> =5) auf, soll die Korrelation im
> > Range F15:F20 und C15:C20 berechnet werden.
>
> > So wie die Funktion jetzt angelegt ist, ist ja der Zellbezug fix. Er
> > reagiert nur auf den Übergabewert als Variable. Wie kann ich das
> > angehen?
> > Kann ich die Adresse von tage ermitteln und in 2 Variablen (Zeile,
> > Spalte) speichern und dann weiterverwenden?
> > Wie geht das?
>
> Gib die Adresse der Bezugsspalte als String-Parameter ein:
>
> Public Function kor(TagAdresse As String)
> Set TagBereich = Range(TagAdresse)
> nTage = TagBereich.Value
> nZeile = TagBereich.Row()
> nSpalte=TagBereich.Column()
> kor = WorksheetFunction.Correl(_
> Range(Cells(nZeile, nSpalte), Cells(nZeile+nTage, nSpalte)), _
> Range(Cells(nZeile, 3), Cells(nZeile+nTage, 3)))
>
> Die Funktion rufst Du dann so auf:
> wert=kor("F10")
> und in Zelle F10 muss die Anzahl der Tage stehen.
>
> Gruß
> Bernhard Sander- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

Hallo Bernhard

Besten Dank für die Hilfe! Klappt bestens!
Einziger Wehrmutstropfen ist, dass nun das z.B. "F10" auch beim
verschieben (kopieren) der Zelle so bleibt. Wäre der Aufruf mit kor
(F10) möglich, würde sich das F10 beim drag&drop ja automatisch an die
neue Zelle anpassen. Nun muss ich das halt per hand anpassen (ca. 200
Zellen...) :-)

freundliche Grüsse
Marco

Grüezi Marco

Marco Schmid schrieb am 02.11.2009

> Ich komme schon wieder mit einer kleinen Frage... Ich entwickle z.Z.
> gerade eine Funktion, die mir einigen finanzmathematischen "Kram"
> ausspuckt. Soweit so gut. Dieser Funktion übergebe ich einen Wert,
> welcher für die Berechnung des Resultats benötigt wird.
>
> public function kor(tage as double) as double
> kor = WorksheetFunction.Correl(Range(Cells(15, 9), Cells(15 + tage,
> 9)), Range(Cells(15, 3), Cells(15 + tage, 3)))
> end function
>
> Das ganze funktioniert auch. Das Problem ist nun aber, dass ich diese
> Korrelationsberechnung nicht nur fix für die Bereiche 3 (Spalte C) und
> 9 (Spalte I) durchführen möchte, sondern für die Spalte, in der Tage
> steht(den ich also der Funktion übergebe) und Spalte 3 (fix).
>
> Oder anders ausgedrückt: mit dem Aufruf der Funktion vola(z.B. B2 -->
> =10), soll die Korrelation im Range B15:B25 und C15:C25 ausgerechnet
> werden. Rufe ich vola(z.B. F2 --> =5) auf, soll die Korrelation im
> Range F15:F20 und C15:C20 berechnet werden.
>
> So wie die Funktion jetzt angelegt ist, ist ja der Zellbezug fix. Er
> reagiert nur auf den Übergabewert als Variable. Wie kann ich das
> angehen?
> Kann ich die Adresse von tage ermitteln und in 2 Variablen (Zeile,
> Spalte) speichern und dann weiterverwenden?
> Wie geht das?

Warum übergibst Du nicht einfach die Bereiche als Parameter an die
Funktion?
Mit BEREICH.VERSCHIEBEN() kannst Du dich dabei auch quasi 'frei' bewegen
und die Bereiche von Werten in Zellen abhängig machen.

Denn dein Aufbau hier hat einen weiteren - IMO wesentlichen - Nachteil:

Wenn sich nach der Eingabe deiner Funktion ein Wert z.B. in der Spalte C
ändert bleibt das Ergebnis der Funktion statisch erhalten bis Du eine
komplette Neuberechnung auslöst oder die Zelle mit F2+Return 'behandelst'.
Du hast also ein 'unstabiles' Berechnungs-System, von dem Du nie so ganz
sicher weisst, ob die Werte denn nun auch aktuell sind.


Mit freundlichen Grüssen
Thomas Ramel

--
- MVP für Microsoft-Excel -
[Vista Ultimate SP-1 / xl2007 SP-1]

On 2 Nov., 18:58, Thomas Ramel <t.ra...*MVPs.org> wrote:
> Grüezi Marco
>
> Marco Schmid schrieb am 02.11.2009
>
>
>
>
>
> > Ich komme schon wieder mit einer kleinen Frage... Ich entwickle z.Z.
> > gerade eine Funktion, die mir einigen finanzmathematischen "Kram"
> > ausspuckt. Soweit so gut. Dieser Funktion übergebe ich einen Wert,
> > welcher für die Berechnung des Resultats benötigt wird.
>
> > public function kor(tage as double) as double
> > kor = WorksheetFunction.Correl(Range(Cells(15, 9), Cells(15 + tage,
> > 9)), Range(Cells(15, 3), Cells(15 + tage, 3)))
> > end function
>
> > Das ganze funktioniert auch. Das Problem ist nun aber, dass ich diese
> > Korrelationsberechnung nicht nur fix für die Bereiche 3 (Spalte C) und
> > 9 (Spalte I) durchführen möchte, sondern für die Spalte, in der Tage
> > steht(den ich also der Funktion übergebe) und Spalte 3 (fix).
>
> > Oder anders ausgedrückt: mit dem Aufruf der Funktion vola(z.B. B2 -->
> > =10), soll die Korrelation im Range B15:B25 und C15:C25 ausgerechnet
> > werden. Rufe ich vola(z.B. F2 --> =5) auf, soll die Korrelation im
> > Range F15:F20 und C15:C20 berechnet werden.
>
> > So wie die Funktion jetzt angelegt ist, ist ja der Zellbezug fix. Er
> > reagiert nur auf den Übergabewert als Variable. Wie kann ich das
> > angehen?
> > Kann ich die Adresse von tage ermitteln und in 2 Variablen (Zeile,
> > Spalte) speichern und dann weiterverwenden?
> > Wie geht das?
>
> Warum übergibst Du nicht einfach die Bereiche als Parameter an die
> Funktion?
> Mit BEREICH.VERSCHIEBEN() kannst Du dich dabei auch quasi 'frei' bewegen
> und die Bereiche von Werten in Zellen abhängig machen.
>
> Denn dein Aufbau hier hat einen weiteren - IMO wesentlichen - Nachteil:
>
> Wenn sich nach der Eingabe deiner Funktion ein Wert z.B. in der Spalte C
> ändert bleibt das Ergebnis der Funktion statisch erhalten bis Du eine
> komplette Neuberechnung auslöst oder die Zelle mit F2+Return 'behandelst'.
> Du hast also ein 'unstabiles' Berechnungs-System, von dem Du nie so ganz
> sicher weisst, ob die Werte denn nun auch aktuell sind.
>
> Mit freundlichen Grüssen
> Thomas Ramel
>
> --
> - MVP für Microsoft-Excel -
> [Vista Ultimate SP-1 / xl2007 SP-1]- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

Hallo Thomas

Das stimmt zwar (und funktioniert auch) für eine Formel. Meine
Funktion soll aber aufgrund eines Wertes in einer anderen Zelle
entscheiden, zu welchem Benchmark es diese Beta-Funktion (oder kor-
Funktion) berechnen soll. Das habe ich mit einer Select Case-Anweisung
gelöst (Unterscheidung von ca. 10 Fällen). Mit der BEREICH.VERSCHIEBEN
Funktion wird dies meiner Meinung nach sehr unübersichtlich.
Die "Aktualisierung"-Problematik habe ich über die
Application.Volatile Funktion gelöst. Das ganze Programm ist aber
immer noch ein wenig Stückwerk, es braucht definitiv noch weitere
Verbesserungen...

freundliche Grüsse
Marco

Hallo Marco,

Mit Application.Volatile hast Du diese Problematik nicht gelöst,
sondern lediglich umgangen.

Am saubersten ist es, wenn Du alle Bereiche, von denen Deine Funktion
abhängt, als Parameter an sie übergibst. Du kannst die verschiedenen
Funktionalitäten ja uch entkoppeln, indem Du eine Funktion zur
Überprüfung und Bereitstellung der Parameter erstellst und dann eine
oder mehrere, die Dein Beta oder Korr etc. ausrechnen.

Viele Grüße,
Bernd

Marco Schmid schrieb:

> Application.Volatile Funktion gelöst. Das ganze Programm ist aber
> immer noch ein wenig Stückwerk, es braucht definitiv noch weitere
> Verbesserungen...
Na dann poste doch Deine UDF's mal, dann könnten wir ja mal kucken...

Andreas.

Grüezi Marco

On 3 Nov., 10:29, Marco Schmid <artho...*gmail.com> wrote:
>
> Das stimmt zwar (und funktioniert auch) für eine Formel. Meine
> Funktion soll aber aufgrund eines Wertes in einer anderen Zelle
> entscheiden, zu welchem Benchmark es diese Beta-Funktion (oder kor-
> Funktion) berechnen soll. Das habe ich mit einer Select Case-Anweisung
> gelöst (Unterscheidung von ca. 10 Fällen). Mit der BEREICH.VERSCHIEBEN
> Funktion wird dies meiner Meinung nach sehr unübersichtlich.

Nicht unübersichtlicher als mit harten Bezügen innerhalb der Funktion.
Wenn Du BEREICH.VERSCHIEBEN() elegant einsetzt wird dies genauso
transparent wie sonstwas.

> Die "Aktualisierung"-Problematik habe ich über die
> Application.Volatile Funktion gelöst.

Dem ist beileibe nicht so, denn damit wird die Funktion auch dann
aktualisiert (und zwar jedesmal) wenn irgendwo sonst in Excel eine
Berechnung ausgelöst wird, die deine Korr-Berechnung gar nicht
tangiert.
Du produzierst damit einen gewaltigen und unnätigen Overhead, das ist
alles.... ;-)

> Das ganze Programm ist aber immer noch ein wenig Stückwerk, es braucht
> definitiv noch weitere Verbesserungen...

Ich schliesse mich Andreas' Bitte um die Offenlegund des Codes (am
besten zusammen mit einer Demo-Mappe) an.


--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für MS-Excel -

On 5 Nov., 14:34, Thomas Ramel <thomas.ra...*gmail.com> wrote:
> Grüezi Marco
>
> On 3 Nov., 10:29, Marco Schmid <artho...*gmail.com> wrote:
>
>
>
> > Das stimmt zwar (und funktioniert auch) für eine Formel. Meine
> > Funktion soll aber aufgrund eines Wertes in einer anderen Zelle
> > entscheiden, zu welchem Benchmark es diese Beta-Funktion (oder kor-
> > Funktion) berechnen soll. Das habe ich mit einer Select Case-Anweisung
> > gelöst (Unterscheidung von ca. 10 Fällen). Mit der BEREICH.VERSCHIEBEN
> > Funktion wird dies meiner Meinung nach sehr unübersichtlich.
>
> Nicht unübersichtlicher als mit harten Bezügen innerhalb der Funktion..
> Wenn Du BEREICH.VERSCHIEBEN() elegant einsetzt wird dies genauso
> transparent wie sonstwas.
>
> > Die "Aktualisierung"-Problematik habe ich über die
> > Application.Volatile Funktion gelöst.
>
> Dem ist beileibe nicht so, denn damit wird die Funktion auch dann
> aktualisiert (und zwar jedesmal) wenn irgendwo sonst in Excel eine
> Berechnung ausgelöst wird, die deine Korr-Berechnung gar nicht
> tangiert.
> Du produzierst damit einen gewaltigen und unnätigen Overhead, das ist
> alles.... ;-)
>
> > Das ganze Programm ist aber immer noch ein wenig Stückwerk, es braucht
> > definitiv noch weitere Verbesserungen...
>
> Ich schliesse mich Andreas' Bitte um die Offenlegund des Codes (am
> besten zusammen mit einer Demo-Mappe) an.
>
> --
> Mit freundlichen Grüssen
>
> Thomas Ramel
> - MVP für MS-Excel -

Hallo zusammen!

Hier mal der momentane Code:

Public Function beta(Tage As Double, benchmark As Integer) As Double

Application.Volatile

Dim msciworld As Integer
Dim spi As Integer
Dim smi As Integer
Dim sli As Integer
Dim dax As Integer
Dim dowjones As Integer
Dim sp500 As Integer
Dim nasdaq As Integer

msciworld = 3
spi = 218
smi = 217
sli = 219
dax = 220
dowjones = 221
sp500 = 222
nasdaq = 223


Dim i As Integer
i = 9

Select Case benchmark 'Auswahl welcher Benchmark verwendet werden
soll 1=SPI; 2=MSCI World; 3=DAX; 4=SMI; 5=SLI; 6=Dow Jones; 7=S&P 500;
8=Nasdaq

Case 1
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, spi), Cells(16 + Tage - 1, spi))) /
(WorksheetFunction.StDevP(Range(Cells(16, spi), Cells(16 + Tage - 1,
spi))) * WorksheetFunction.StDevP(Range(Cells(16, spi), Cells(16 +
Tage - 1, spi))))

Case 2
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, msciworld), Cells(16 + Tage - 1,
msciworld))) / (WorksheetFunction.StDevP(Range(Cells(16, msciworld),
Cells(16 + Tage - 1, msciworld))) * WorksheetFunction.StDevP(Range
(Cells(16, msciworld), Cells(16 + Tage - 1, msciworld))))

Case 3
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, dax), Cells(16 + Tage - 1, dax))) /
(WorksheetFunction.StDevP(Range(Cells(16, dax), Cells(16 + Tage - 1,
dax))) * WorksheetFunction.StDevP(Range(Cells(16, dax), Cells(16 +
Tage - 1, dax))))

Case 4
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, smi), Cells(16 + Tage - 1, smi))) /
(WorksheetFunction.StDevP(Range(Cells(16, smi), Cells(16 + Tage - 1,
smi))) * WorksheetFunction.StDevP(Range(Cells(16, smi), Cells(16 +
Tage - 1, smi))))

Case 5
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, sli), Cells(16 + Tage - 1, sli))) /
(WorksheetFunction.StDevP(Range(Cells(16, sli), Cells(16 + Tage - 1,
sli))) * WorksheetFunction.StDevP(Range(Cells(16, sli), Cells(16 +
Tage - 1, sli))))

Case 6
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, dowjones), Cells(16 + Tage - 1, dowjones))) /
(WorksheetFunction.StDevP(Range(Cells(16, dowjones), Cells(16 + Tage -
1, dowjones))) * WorksheetFunction.StDevP(Range(Cells(16, dowjones),
Cells(16 + Tage - 1, dowjones))))

Case 7
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, sp500), Cells(16 + Tage - 1, sp500))) /
(WorksheetFunction.StDevP(Range(Cells(16, sp500), Cells(16 + Tage - 1,
sp500))) * WorksheetFunction.StDevP(Range(Cells(16, sp500), Cells(16 +
Tage - 1, sp500))))

Case 8
beta = WorksheetFunction.Covar(Range(Cells(16, i), Cells(16 + Tage -
1, i)), Range(Cells(16, nasdaq), Cells(16 + Tage - 1, nasdaq))) /
(WorksheetFunction.StDevP(Range(Cells(16, nasdaq), Cells(16 + Tage -
1, nasdaq))) * WorksheetFunction.StDevP(Range(Cells(16, nasdaq), Cells
(16 + Tage - 1, nasdaq))))

Case Else
beta = 0

End Select

End Function


Wie ihr seht habe ich in dieser Version das i manuell auf 9 gestellt.
In einer Endversion soll das i dann durch die Spalte ersetzt werden,
in der die beta-Funktion aufgerufen wird. Die Daten beginnen in jeder
Spalte in der 16.Zeile. Der Bereich soll auf den Beginn der
Datenreihen (16.Zeile) bis dem in der Funktion übergebenen Wert
berechnet werden. Naja, ihr seht ich bin Neuling in der Programmierung
einer Funktion... :-)

Besten Dank für Eure Hilfe und freundliche Grüsse
Marco

On 9 Nov., 09:25, Marco Schmid <artho...*gmail.com> wrote:

> Wie ihr seht habe ich in dieser Version das i manuell auf 9 gestellt.
> In einer Endversion soll das i dann durch die Spalte ersetzt werden,
> in der die beta-Funktion aufgerufen wird. Die Daten beginnen in jeder
> Spalte in der 16.Zeile. Der Bereich soll auf den Beginn der
> Datenreihen (16.Zeile) bis dem in der Funktion übergebenen Wert
> berechnet werden. Naja, ihr seht ich bin Neuling in der Programmierung
> einer Funktion... :-)
Von uns hat auch keiner gleich alles auf einmal gewußt und wir lernen
immer noch. .-)

Also Du gehst das ganze zu kompliziert an, ich vereinfache das mal auf
einen Benchmark:

Function Beta(Kurs As Range, Benchmark As Range, Tage As Long) As
Variant
Dim Data1 As Range, Data2 As Range

'Den Teilbereich referenzieren
Set Data1 = Range(Kurs.Cells(1, 1), Kurs.Offset(Tage, 0))
Set Data2 = Range(Benchmark.Cells(1, 1), Benchmark.Offset(Tage, 0))

'Falls ein Fehler bei der Berechnung auftritt
On Error Resume Next
Beta = WorksheetFunction.Covar(Data1, Data2) / ( _
WorksheetFunction.StDevP(Data2) * WorksheetFunction.StDevP( _
Data1))
'Ggf. #WERT zurückgeben
If Err Then Beta = CVErr(xlErrValue)
End Function

An diese Function übergibst Du nun den kompletten Kurs-Bereich, also
z.B. so
=Beta(I16:I1000;C16:C1000;100)
für den MSCI-World und erhälst das 100-Tage-Beta.

Um die anderen Benchmarks zu erhalten gibt es 2 Möglichkeiten:

1.) Du schreibst in 7 weitere Zellen die entsprechenden Formeln (wie
oben) und holst Dir ggf. mit einer weiteren Formel (=INDEX(...)) aus
den Beta-Formeln den gewünschten Wert.

2.) Du erweiterst die Function und übergibst alle Bereiche und wählst
innerhalb der UDF via Select aus auf welchen Bereich Du Data2 setzt.

Andreas.