aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--sql/Makefile12
-rw-r--r--sql/date.c139
-rw-r--r--sql/dawn.sql18
-rw-r--r--sql/serenitas.c238
4 files changed, 259 insertions, 148 deletions
diff --git a/sql/Makefile b/sql/Makefile
index d14811d3..b9bde6cd 100644
--- a/sql/Makefile
+++ b/sql/Makefile
@@ -1,12 +1,12 @@
INCLUDE_DIR != pg_config --includedir-server
INSTALL_DIR != pg_config --pkglibdir
-serenitas_date.so: date.o
- gcc -lcds -shared -o serenitas_date.so date.o
+serenitas.so: serenitas.o
+ gcc -lcds -shared -o serenitas.so serenitas.o
-date.o: date.c
- gcc -fPIC -I $(INCLUDE_DIR) -c date.c
+serenitas.o: serenitas.c
+ gcc -fPIC -I $(INCLUDE_DIR) -c serenitas.c
.PHONY: install
-install: serenitas_date.so
- -cp serenitas_date.so $(INSTALL_DIR)
+install: serenitas.so
+ -cp serenitas.so $(INSTALL_DIR)
diff --git a/sql/date.c b/sql/date.c
deleted file mode 100644
index 4f0f80ee..00000000
--- a/sql/date.c
+++ /dev/null
@@ -1,139 +0,0 @@
-#include "postgres.h"
-#include "fmgr.h"
-#include "executor/spi.h"
-#include "utils/date.h"
-#include "utils/builtins.h"
-#include "isda/dateconv.h"
-#include "isda/busday.h"
-
-PG_MODULE_MAGIC;
-
-static inline TDate next_business_day(TDate date, long method, const char* cal) {
- TDate r;
- if (JpmcdsBusinessDay(date, method, cal, &r) != SUCCESS) {
- return -1;
- } else {
- return r;
- }
-}
-
-TDate _previous_twentieth(TDate d, bool roll, const char* cal) {
- TMonthDayYear mdy;
- if (JpmcdsDateToMDY(d, &mdy) != SUCCESS) {
- return -1;
- }
- if (mdy.day < 20) {
- if(mdy.month == 1) {
- mdy.month = 12;
- mdy.year -= 1;
- } else {
- mdy.month -= 1;
- }
- }
- mdy.day = 20;
- int mod = mdy.month % 3;
- if (mod != 0) {
- mdy.month -= mod;
- if (mdy.month <= 0) {
- mdy.month += 12;
- mdy.year -= 1;
- }
- }
- TDate r;
- if (JpmcdsMDYToDate(&mdy, &r) != SUCCESS) {
- return -1;
- }
- if (roll) {
- return next_business_day(r, JPMCDS_BAD_DAY_FOLLOW, cal);
- } else {
- return r;
- }
-}
-
-// postgresql represents dates as number of days since 2000-01-01
-// TDate are integers since 1601-01-01
-static inline TDate TDate_from_DateADT(DateADT d) {
- return d + 145731;
-}
-
-static inline const char* cal_from_currency(const char* curr) {
- static const char default_cal[] = "NONE";
- static const char us_cal[] = "/usr/share/cds/US";
- if (strcmp(curr, "USD") == 0) {
- return us_cal;
- } else {
- return default_cal;
- }
-}
-PG_FUNCTION_INFO_V1(cds_accrued);
-
-Datum cds_accrued(PG_FUNCTION_ARGS) {
- DateADT d = PG_GETARG_DATEADT(0);
- float8 coupon = PG_GETARG_FLOAT8(1);
- bool include_cashflow = PG_GETARG_BOOL(2);
- char* currency = text_to_cstring(PG_GETARG_TEXT_PP(3));
- const char* cal = cal_from_currency(currency);
- TDate date = TDate_from_DateADT(d) + 1;
- TDate date1 = next_business_day(date, JPMCDS_BAD_DAY_PREVIOUS, cal);
- if (date1 == -1)
- elog(ERROR, "Please set up the US calendar in /usr/share/cds/US");
-
- TDate date_prev = _previous_twentieth(date1, true, cal);
-
- if ((date_prev == date) && include_cashflow) {
- date_prev = _previous_twentieth(date - 1, true, cal);
- }
- pfree(currency);
- PG_RETURN_FLOAT8((date - date_prev) / 360. * coupon);
-}
-
-
-PG_FUNCTION_INFO_V1(test);
-
-Datum cds_enrich(PG_FUNCTION_ARGS) {
- if (SPI_connect() == SPI_ERROR_CONNECT) {
- elog(ERROR, "pomme");
- }
- const text* redindexcode = PG_GETARG_TEXT_PP(0);
- DateADT maturity = PG_GETARG_DATEADT(1);
- float8 traded_level = PG_GETARG_FLOAT8(2);
- DateADT trade_datge = PG_GETARG_DATEADT(3);
- char* sql_query = "SELECT index, series, tenor::text, coupon, issue_date, indexfactor/100, "
- "version, cumulativeloss "
- "FROM index_desc "
- "WHERE redindexcode=$1 AND maturity=$2";
- int ret;
- uint64 proc;
- int nargs = 2;
- Oid argtypes[2] = {TEXTOID, DATEOID};
- char nulls[2] = " ";
- Datum values[2];
- values[0] = PointerGetDatum(redindexcode);
- values[1] = DateADTGetDatum(maturity);
- ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
- proc = SPI_processed;
- short series, version;
- int coupon;
- char *index, *tenor;
- TDate issue_date;
- double factor, cumulativeloss;
- if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
- SPITupleTable *tuptable = SPI_tuptable;
- TupleDesc tupdesc = tuptable->tupdesc;
- bool isnull;
- HeapTuple tuple = tuptable->vals[0];
- index = text_to_cstring(DatumGetTextPP(SPI_getbinval(tuple, tupdesc, 1, &isnull)));
- series = DatumGetInt16(SPI_getbinval(tuple, tupdesc, 2, &isnull));
- tenor = text_to_cstring(DatumGetTextPP(SPI_getbinval(tuple, tupdesc, 3, &isnull)));
- coupon = DatumGetInt32(SPI_getbinval(tuple, tupdesc, 4, &isnull));
- issue_date = TDate_from_DateADT(DatumGetDateADT(SPI_getbinval(tuple, tupdesc, 5, &isnull)));
- factor = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 6, &isnull));
- version = DatumGetInt16(SPI_getbinval(tuple, tupdesc, 7, &isnull));
- cumulativeloss = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 8, &isnull));
- }
- char* yc_query
- SPI_finish();
- pfree(index);
- pfree(tenor);
- PG_RETURN_NULL();
-}
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 67faa28c..e54ec115 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -340,8 +340,17 @@ UPDATE
CREATE OR REPLACE FUNCTION cds_enrich()
RETURNS TRIGGER
AS $$
-from serenitas.analytics.api import CreditIndex
-trade = TD["new"]
+BEGIN
+IF NEW.swap_type = "BESPOKE" THEN
+RETURN NEW;
+ELSE
+IF TG_OP = "INSERT" AND (NEW.upfront IS NOT NULL AND NEW.traded_level IS NOT NULL) THEN
+ RETURN NEW;
+IF NEW.traded_level IS NOT NULL THEN
+ NEW.upfront = upfront_from_level(NEW.security_id, NEW.maturity, NEW.traded_level, NEW.trade_date, NEW.currency);
+$$ LANGUAGE plpgsql;
+
+END IF;
if trade["swap_type"] == "BESPOKE":
return
if TD["event"] == "INSERT" and all([trade["upfront"], trade["traded_level"]]):
@@ -1933,9 +1942,12 @@ return imm_date(p_date)
$$ LANGUAGE plpython3u;
CREATE OR REPLACE FUNCTION cds_accrued(date, double precision, bool, text DEFAULT 'USD') RETURNS double precision
-AS '$libdir/serenitas_date', 'cds_accrued'
+AS '$libdir/serenitas', 'cds_accrued'
LANGUAGE C STRICT;
+CREATE OR REPLACE FUNCTION upfront_from_level(text. date, double precision, date, text) RETURNS double precision
+AS '$libdir/serenitas', 'upfron_from_level'
+LANGUAGE C STRICT;
CREATE MATERIALIZED VIEW factors_history AS
WITH temp AS (
diff --git a/sql/serenitas.c b/sql/serenitas.c
new file mode 100644
index 00000000..a85894ce
--- /dev/null
+++ b/sql/serenitas.c
@@ -0,0 +1,238 @@
+#include "postgres.h"
+#include "fmgr.h"
+#include "executor/spi.h"
+#include "lz4.h"
+#include "utils/date.h"
+#include "utils/builtins.h"
+#include "utils/numeric.h"
+#include "utils/rel.h"
+
+#include "isda/bastypes.h"
+#include "isda/dateconv.h"
+#include "isda/busday.h"
+#include "isda/ldate.h"
+#include "isda/cdsone.h"
+#include <stdbool.h>
+
+PG_MODULE_MAGIC;
+
+static inline TDate next_business_day(TDate date, long method, const char* cal) {
+ TDate r;
+ if (JpmcdsBusinessDay(date, method, cal, &r) != SUCCESS) {
+ return -1;
+ } else {
+ return r;
+ }
+}
+
+TDate _previous_twentieth(TDate d, bool roll, const char* cal) {
+ TMonthDayYear mdy;
+ if (JpmcdsDateToMDY(d, &mdy) != SUCCESS) {
+ return -1;
+ }
+ if (mdy.day < 20) {
+ if(mdy.month == 1) {
+ mdy.month = 12;
+ mdy.year -= 1;
+ } else {
+ mdy.month -= 1;
+ }
+ }
+ mdy.day = 20;
+ int mod = mdy.month % 3;
+ if (mod != 0) {
+ mdy.month -= mod;
+ if (mdy.month <= 0) {
+ mdy.month += 12;
+ mdy.year -= 1;
+ }
+ }
+ TDate r;
+ if (JpmcdsMDYToDate(&mdy, &r) != SUCCESS) {
+ return -1;
+ }
+ if (roll) {
+ return next_business_day(r, JPMCDS_BAD_DAY_FOLLOW, cal);
+ } else {
+ return r;
+ }
+}
+
+// postgresql represents dates as number of days since 2000-01-01
+// TDate are integers since 1601-01-01
+static inline TDate TDate_from_DateADT(DateADT d) {
+ return d + 145731;
+}
+
+static inline const char* cal_from_currency(const char* curr) {
+ static const char default_cal[] = "NONE";
+ static const char us_cal[] = "/usr/share/cds/US";
+ if (strcmp(curr, "USD") == 0) {
+ return us_cal;
+ } else {
+ return default_cal;
+ }
+}
+PG_FUNCTION_INFO_V1(cds_accrued);
+
+Datum cds_accrued(PG_FUNCTION_ARGS) {
+ DateADT d = PG_GETARG_DATEADT(0);
+ float8 coupon = PG_GETARG_FLOAT8(1);
+ bool include_cashflow = PG_GETARG_BOOL(2);
+ char* currency = text_to_cstring(PG_GETARG_TEXT_PP(3));
+ const char* cal = cal_from_currency(currency);
+ TDate date = TDate_from_DateADT(d) + 1;
+ TDate date1 = next_business_day(date, JPMCDS_BAD_DAY_PREVIOUS, cal);
+ if (date1 == -1)
+ elog(ERROR, "Please set up the US calendar in /usr/share/cds/US");
+
+ TDate date_prev = _previous_twentieth(date1, true, cal);
+
+ if ((date_prev == date) && include_cashflow) {
+ date_prev = _previous_twentieth(date - 1, true, cal);
+ }
+ pfree(currency);
+ PG_RETURN_FLOAT8((date - date_prev) / 360. * coupon);
+}
+
+static inline void get_TCurve(const char* buf, uint32_t length, TCurve* curve) {
+ if (LZ4_decompress_safe(buf, (char*)curve, length, 512) < 0) {
+ elog(ERROR, "error during decompression");
+ }
+}
+
+double calc(TDate today, TDate start_date, TDate end_date, double recovery, double fixed_rate, const char* calendar, const TCurve* yc, double val, bool calc_upfront) {
+ TDate cash_settle_date;
+ JpmcdsDateFromBusDaysOffset(today, 3, calendar, &cash_settle_date);
+ TDate step_in_date = today + 1;
+ TStubMethod stub_type = {0, 0}; //f/s
+ TDateInterval ivl = {.prd = 3, .prd_typ='M', .flag=0}; // 3 months
+ double result;
+ if (calc_upfront) {
+ JpmcdsCdsoneUpfrontCharge(today,
+ cash_settle_date,
+ today, // benchmark_start_date
+ step_in_date,
+ start_date,
+ end_date,
+ fixed_rate,
+ true, // pay accrued on default
+ &ivl,
+ &stub_type,
+ JPMCDS_ACT_360,
+ JPMCDS_BAD_DAY_FOLLOW,
+ calendar,
+ yc,
+ val,
+ recovery,
+ true, // pay accrued at start
+ &result);
+ } else {
+ JpmcdsCdsoneSpread(today,
+ cash_settle_date,
+ today, // benchmark_start_date
+ step_in_date,
+ start_date,
+ end_date,
+ fixed_rate,
+ true, // pay accrued on default
+ &ivl,
+ &stub_type,
+ JPMCDS_ACT_360,
+ JPMCDS_BAD_DAY_FOLLOW,
+ calendar,
+ yc,
+ val,
+ recovery,
+ true, // pay accrued at start
+ &result);
+ }
+ return result;
+}
+
+
+PG_FUNCTION_INFO_V1(upfront_from_level);
+
+Datum upfront_from_level(PG_FUNCTION_ARGS) {
+ if (SPI_connect() == SPI_ERROR_CONNECT) {
+ elog(ERROR, "something wrong happened");
+ }
+ const text* redindexcode = PG_GETARG_TEXT_PP(0);
+ DateADT maturity = PG_GETARG_DATEADT(1);
+ float8 traded_level = PG_GETARG_FLOAT8(2);
+ DateADT trade_date = PG_GETARG_DATEADT(3);
+ const char* currency = text_to_cstring(PG_GETARG_TEXT_PP(4));
+ char* sql_query = "SELECT index, coupon, issue_date, indexfactor/100, cumulativeloss "
+ "FROM index_desc "
+ "WHERE redindexcode=$1 AND maturity=$2";
+ uint64 proc;
+ int nargs = 2;
+ Oid argtypes[2] = {TEXTOID, DATEOID};
+ char nulls[2] = " ";
+ Datum values[2];
+ int ret;
+ values[0] = PointerGetDatum(redindexcode);
+ values[1] = DateADTGetDatum(maturity);
+ ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
+ proc = SPI_processed;
+
+ int coupon;
+ char *index;
+ TDate issue_date;
+ double factor, cumulativeloss;
+ if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
+ SPITupleTable *tuptable = SPI_tuptable;
+ TupleDesc tupdesc = tuptable->tupdesc;
+ bool isnull;
+ HeapTuple tuple = tuptable->vals[0];
+ index = text_to_cstring(DatumGetTextPP(SPI_getbinval(tuple, tupdesc, 1, &isnull)));
+ coupon = DatumGetInt32(SPI_getbinval(tuple, tupdesc, 4, &isnull));
+ issue_date = TDate_from_DateADT(DatumGetDateADT(SPI_getbinval(tuple, tupdesc, 5, &isnull)));
+ factor = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 6, &isnull));
+ cumulativeloss = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 8, &isnull));
+ } else {
+ SPI_finish();
+ elog(ERROR, "something wrong happened");
+ }
+ int16 curve_type = 532;
+ sql_query = "SELECT curve FROM rate_curves WHERE effective_date=$1 AND curve_type=$2";
+ argtypes[0] = DATEOID;
+ argtypes[1] = INT2OID;
+ values[0] = DateADTGetDatum(trade_date);
+ values[1] = Int16GetDatum(curve_type);
+ ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
+ proc = SPI_processed;
+ bytea* buf;
+ Datum tmp;
+ TCurve* curve;
+ if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
+ SPITupleTable *tuptable = SPI_tuptable;
+ TupleDesc tupdesc = tuptable->tupdesc;
+ bool isnull;
+ HeapTuple tuple = tuptable->vals[0];
+ tmp = SPI_getbinval(tuple, tupdesc, 1, &isnull);
+ if (isnull) {
+ pfree(index);
+ SPI_finish();
+ elog(ERROR, "no curve for that date");
+ } else {
+ buf = DatumGetByteaPP(tmp);
+ uint32 data_length = VARSIZE_ANY(curve);
+ char *raw_data = VARDATA_ANY(curve);
+ curve = (TCurve*)malloc(512);
+ get_TCurve(raw_data, data_length, curve);
+ }
+ } else {
+ pfree(index);
+ SPI_finish();
+ elog(ERROR, "no curve for that date");
+ }
+ SPI_finish();
+ double recovery = 0.4;
+ double upfront;
+ upfront = calc(TDate_from_DateADT(trade_date), issue_date, TDate_from_DateADT(maturity), recovery, coupon / 10000, cal_from_currency(currency), curve, traded_level, true);
+
+ pfree(index);
+ pfree(curve);
+ PG_RETURN_FLOAT8(upfront);
+}
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 06:16:46 +0000