#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 "isda/cerror.h" #include 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 int get_TCurve(const char* buf, uint32_t length, TCurve* curve) { if (LZ4_decompress_safe(buf, (char*)curve, length, 512) < 0) { return -1; } else { return 0; } } 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; int success; if (calc_upfront) { success = 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, false, // pay accrued at start &result); } else { success = 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, false, // pay accrued at start &result); } return result; } PG_FUNCTION_INFO_V1(upfront_from_level); Datum upfront_from_level(PG_FUNCTION_ARGS) { 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); char* currency = text_to_cstring(PG_GETARG_TEXT_PP(4)); char* sql_query = "SELECT index::text, 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); if (SPI_connect() == SPI_ERROR_CONNECT) { elog(ERROR, "something wrong happened"); } 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, 2, &isnull)); issue_date = TDate_from_DateADT(DatumGetDateADT(SPI_getbinval(tuple, tupdesc, 3, &isnull))); factor = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 4, &isnull)); cumulativeloss = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 5, &isnull)); elog(INFO, "%s %d %d %f %f", index, coupon, issue_date, factor, cumulativeloss); } else { pfree(currency); elog(ERROR, "something wrong happened"); } SPI_freetuptable(SPI_tuptable); 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(currency); elog(ERROR, "no curve for that date"); } else { buf = DatumGetByteaPP(tmp); uint32 data_length = VARSIZE_ANY_EXHDR(buf); const char *raw_data = VARDATA_ANY(buf); curve = (TCurve*)malloc(512); if (get_TCurve(raw_data, data_length, curve) == -1 ) { free(curve); pfree(currency); elog(ERROR, "error during decompression"); } } } else { pfree(currency); 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.0, cal_from_currency(currency), curve, traded_level, true); pfree(currency); free(curve); PG_RETURN_FLOAT8(upfront); }