Das OpenOffice-Forum braucht Eure HILFE und sagt DANKE!

komplexe berechnete Felderin Abfragen

BASE ermöglicht es Ihnen, Ihre Daten in einer Datenbank direkt mit OpenOffice.org zu bearbeiten.
BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Mi 4. Mär 2020, 16:45

Hallo,
wegen der Menge der Kursdaten habe ich begonnen, eine Wertpapierverwaltung von Calc auf Base umzustellen. Die Bewertung, ob ein Wertpapier zu kaufen oder verkaufen ist, geschieht auf Basis von Zinssätzen, die ich aus den Tageskursen berechne. Dazu habe ich in Calc und jetzt auch in Base viele hundert Kurse abgespeichert und eine VBA-Routine geschrieben, die mir den erforderlichen Zinssatz ausrechnet (doVerzinsungN(ISIN, Startdatum, AnzahlTage) Diese Routine habe ich in Calc in einer Tabelle mit den entsprechenden ISINs in einer Zelle (z.B. =DOVERZINSUNGN(F10;30;Gestern), in Zelle F10 steht die ISIN, um die es geht, in Zelle 'Gestern' steht das Basisdatum für die Verzinsung, die 30 heist: es soll die Verzinsung über 30 Tage gerechnet werden)

Mein Problem: Ich habe versucht, eine Base-Abfrage zu definieren, wo im Ergebnis ebenfalls die ISIN erscheint und dann ein berechnetes Feld auf Basis von doVerzinsungN zu erzeugen. Das SQL dazu sieht so aus:
SELECT "depISIN", "depGewicht", "depBezeichnung", "depWaehrung", "depTyp", "depKursziel", "depLetzteRangverbesserung", doVerzinsungN( 'depISIN', '3.3.2020', 25 ) FROM "tblDepot"

Als Ergebnis kriege ich die Meldung "Die Dateninhalte konnten nicht geladen werden, "Function unknown "doVerzinsungN".
Klar, die Funktion doVerzinsungN ist kein Feld von tblDepot, sie berechnet mittels einiger selects für die gegebene ISIN den entsprechenden Zinssatz.
Gibt es einen Weg, das Ergebnis einer relativ komplexen Rechnung wie doVerzinsungN in ein berechnetes Abfragefeld zu bringen?

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Mo 23. Mär 2020, 13:09

Hallo,
ich habe mangels besserer Ideen nun eine "schmutzige Lösung" implementiert, indem ich die komplexen Berechnungen in eine Tabelle schreibe und die Daten von dort mittels Join hole.

F3K Total
Beiträge: 893
Registriert: Mo 1. Aug 2011, 00:03

Re: komplexe berechnete Felderin Abfragen

Beitrag von F3K Total » Mo 23. Mär 2020, 18:35

Moin,
da du nicht erklärst, wie du mit deiner Routine doVerzinsungN(ISIN, Startdatum, AnzahlTage) die Zinsen berechnest, kann dir hier kaum geholfen werden, nur soviel, eine HSQL-Datenbank verfügt über zig numerische Funktionen, die im HSQL-1.8-Guide bissel runterscrollen, unter Built-in Functions and Stored Procedures schön aufgelistet sind:
Numerical built-in Functions / Stored Procedures
ABS(d)
returns the absolute value of a double value
ACOS(d)
returns the arc cosine of an angle
ASIN(d)
returns the arc sine of an angle
ATAN(d)
returns the arc tangent of an angle
ATAN2(a,b)
returns the tangent of a/b
BITAND(a,b)
return a & b
BITOR(a,b)
returns a | b
CEILING(d)
returns the smallest integer that is not less than d
COS(d)
returns the cosine of an angle
COT(d)
returns the cotangent of an angle
DEGREES(d)
converts radians to degrees
EXP(d)
returns e (2.718...) raised to the power of d
FLOOR(d)
returns the largest integer that is not greater than d
LOG(d)
returns the natural logarithm (base e)
LOG10(d)
returns the logarithm (base 10)
MOD(a,b)
returns a modulo b
PI()
returns pi (3.1415...)
POWER(a,b)
returns a raised to the power of b
RADIANS(d)
converts degrees to radians
RAND()
returns a random number x bigger or equal to 0.0 and smaller than 1.0
ROUND(a,b)
rounds a to b digits after the decimal point
ROUNDMAGIC(d)
solves rounding problems such as 3.11-3.1-0.01
SIGN(d)
returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0
SIN(d)
returns the sine of an angle
SQRT(d)
returns the square root
TAN(A)
returns the trigonometric tangent of an angle
TRUNCATE(a,b)
truncates a to b digits after the decimal point
Damit solltest du deine Berechnung hinbekommen.
Gruß R
Windows 10: AOO, LO Linux Mint: AOO, LO

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Mo 23. Mär 2020, 19:08

Hallo R.
ich rechne für verschiedene Intervalle (30 Tage, 60 Tage)den gleitenden Durchschnitt und bilde daraus ein gewichtetes Mittel. Danach bestimme ich den Zinssatz, mit dem dieses gewichtete Mittel verzinst werden müßte, um den aktuellen Tageskurs zu erhalten. Das drückt in meinen Augen gut den Trend eines Wertpapiers aus. Da ich privat mit Base erst angefangen habe (auf der Arbeit benutze ich MS-Access, wo man in die Felddefinitionen eigene Funktionen so einbauen kann, wie ich es hier angefragt habe), habe ich als Datenbank den Default - Firebird - genommen. Ich habe mir vor dem Post hier das Firebird-Gegenstück zu Deinem Listing angeschaut, einen gleitenden Durchschnitt gibt es dort nicht, d.h. mit "Bordmitteln" aus Firebird komme ich nicht weiter. Deshalb nun die "schmutzige Lösung"

F3K Total
Beiträge: 893
Registriert: Mo 1. Aug 2011, 00:03

Re: komplexe berechnete Felderin Abfragen

Beitrag von F3K Total » Di 24. Mär 2020, 18:34

Hallo BBMonster,
wie lauten die Formeln?
Ich bin sicher, auch mit Firebird-SQL, siehe Seite 294 ff. stehen alle notwendigen mathematischen Funktionen zur Verfügung.
Ein Beispiel wäre auch nicht schlecht.
Gruß R
Windows 10: AOO, LO Linux Mint: AOO, LO

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Di 24. Mär 2020, 19:31

Hallo R,
ich bin gespannt :-)
Ich rechne zunächst folgende 3 Basisgrößen:
1. Rohdatum/Zwischenwert: Berechnen, mit welchem Zinssatz sich der gleitende Durchschnitt über 30 Tage verzinst, um den aktuellen Kurs zu bekommen.
Den gleitenden Durchschnitt zu einer ISIN aus den 30 Kursen vor dem Datum 'daStartDatum' rechne ich über VBA mittels der Funktion doDurchschnitt (Code im Anhang). Die Verzinsung dieses Durchschnitts berechne ich in der Funktion doVerzinsungN. Das 'do' steht für den Datentyp des Rückgabewerts (double)
2. Rohdatum: wie 1 nur für 60 Tage
3. Rohdatum: Ich berechne den annualisierten Gewinn, den ein Wertpapier erzielt hat. Das ist der Gewinn - also (Aktueller Kurs - Kaufkurs)* Stücke/DepotwertbeimKauf - hochskaliert auf einen Jahreszins, also wenn das Wertpapier 182 Tage in meinem Besitz ist, multipliziere ich den o.a. Bruch mit 2. Dem Code für die Berechnung habe ich nicht so einfach, denn einen Teil der Logik der Berechnung des Annualsierten Gewinns bilde ich über Abfragen ab, im VBA-Code rechne ich dann den Rest aus den Abfragedaten. Den Restcode findest Du ebenfalls im Anhang in der VBA-Prozedur 'SchreibeScoreRohdaten' und zwar in der innersten DO-Schleife den CASE "AnnuZins"

Dieses 3-er-Set Daten kombiniere ich in einer Abfrage wie folgt: "Eigentlicher Score am Tag X: (3*doVerzinsungN(ISIN, Basisdatum, 30) + 1*doVerzinsungN(ISIN, Basisdatum, 60) + 1* AnnualisierterZins(Basisdatum))/5

Die Operationen der letzten beiden Zeilen mache ich für die letzten 5 Börsentage und bilde daraus das arithmetische Mittel. Das ist dann mein finaler Score für ein Wertpapier.

Den kompletten VBA-Code findest Du im Anhang. Die Abfragen kann ich Dir nicht schicken, weil ich nicht weiß, wie ich die alle zusammen exportieren kann.
codeBase.zip
(7.8 KiB) 2-mal heruntergeladen
BBMonster

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Di 24. Mär 2020, 20:09

Hallo R,
mir ist gerade eine Inkonsistenz in meiner Antwort aufgefallen: TagX und Basisdatum ist das gleiche, sorry und Gruß BBMonster

F3K Total
Beiträge: 893
Registriert: Mo 1. Aug 2011, 00:03

Re: komplexe berechnete Felderin Abfragen

Beitrag von F3K Total » Mi 25. Mär 2020, 18:38

Hallo BBMonster,
deine Codes sehen perfekt aus, was soll ich sagen? Bin immer noch der Meinung, dass es mit SQL geht, aber das alles nachzuvollziehen, was du bereits programmiert hast, dazu habe ich weder Zeit noch Muße.
Wer so programmieren kann, der kann doch auch SQL.
Also
doDurchschnitt

Code: Alles auswählen

if nResults = nAnzahlTage then
	doDurchschnitt = doSumme/nResults
else
	doDurchschnitt = -doSumme/nResults
end if
Hier bildest du nix weiter als eine Summe, dann einen Quotieten, kein Problem mit SQL...
doGlDurchschnitt

Code: Alles auswählen

doGlDurchschnitt = doDurchschnitt(sISIN, daStartDatum, nAnzahlTage)
if doGlDurchschnitt > 0 then doVerzinsungN = (doStartkurs/doGlDurchschnitt - 1)/nAnzahlTage * 365
if doGlDurchschnitt < 0 then doVerzinsungN = (doStartkurs/(-doGlDurchschnitt) - 1)
geht mit SQL doch ohne Probleme.
Es handelt sich um Subtraktion, Multiplikation und Division!
Statt IF nimmt man CASE WHEN ...

Code: Alles auswählen

Aktueller Kurs - Kaufkurs)* Stücke/DepotwertbeimKauf 
und wieder nur Subtraktion, Multiplikation und Division
SchreibeScoreRohdaten

Code: Alles auswählen

	case "AnnuZins"		
' Score berechnen
doDepotwertTagX	= doKursTagX * nVirtStuecke
if (doDepotwertTagX = 0) or (daDatumTagX - daVirtKaufdatum = 0) then
oScore = 0
else
doScore = doDepotwertTagX/doVirtDepotwertKauf - 1
' nun diesen Score noch skalieren, damit der Wert bei kurzen Laufzeiten nicht extrem gehebelt wird
if (daDatumTagX - daVirtKaufdatum >= 10) then
doScore = doScore * 365/(daDatumTagX - daVirtKaufdatum)
else
doScore = doScore/36.5
end if
end if
			case else 
...und wieder nur Subtraktion, Multiplikation und Division

Code: Alles auswählen

Dieses 3-er-Set Daten kombiniere ich in einer Abfrage wie folgt: "Eigentlicher Score am Tag X: (3*doVerzinsungN(ISIN, Basisdatum, 30) + 1*doVerzinsungN(ISIN, Basisdatum, 60) + 1* AnnualisierterZins(Basisdatum))/5
... und nochmal ...Addition, Multiplikation, Division.
ICH SAGE IMMER NOCH: Das läßt sich auch mit SQL machen!
Gruß R
Windows 10: AOO, LO Linux Mint: AOO, LO

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Do 26. Mär 2020, 20:44

Hallo R.
das Firebird-Handbuch kannte ich noch nicht. Ich schaue es mir an. Bin immer noch skeptisch, ob ein gleitender Durchschnitt geht. Wenn ich das Handbuch durch habe gebe ich Bescheid.
Vielen Dank fürs Feedback. :)
BBMonster

BBMonster
Beiträge: 15
Registriert: Sa 16. Feb 2019, 17:26

Re: komplexe berechnete Felderin Abfragen

Beitrag von BBMonster » Fr 27. Mär 2020, 16:48

Hallo R.
beim Versuch, den gleitenden Durchschnitt zu ermitteln scheitere ich vermutlich an etwas ganz banalem.
Ich versuche eine Abfrage zu erzeugen, die mittels DATEADD mir die Kurse der letzten 30 Tage aus meiner Tabelle tblKurse liefern soll, laut Handbuch würde ich sagen:
select * from "tblKurse" where "kurDatum" > DATEADD('DAY', -30, CURRENT_DATE)
später baue ich dann einen Mittelwert drum und sehe dann weiter.
Leider kriege ich die folgende Fehlermeldung :
firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 68
*,
caused by 'isc_dsql_prepare
Für mich sieht das so aus, als will er das Komma nicht.
Da ich das ganze speichern will, mache ich diese Abfrage in der SQL-Sicht einer Standardabfrage.
Kannst Du mir weiterhelfen?
Viele Grüße und danke
BBMonster

Antworten