diff options
| -rw-r--r-- | sql/Makefile | 7 | ||||
| -rw-r--r-- | sql/date.c | 81 | ||||
| -rw-r--r-- | sql/dawn.sql | 31 |
3 files changed, 101 insertions, 18 deletions
diff --git a/sql/Makefile b/sql/Makefile new file mode 100644 index 00000000..146cf722 --- /dev/null +++ b/sql/Makefile @@ -0,0 +1,7 @@ +INCLUDE_DIR != pg_config --includedir-server + +serenitas_date.so: date.o + gcc -lcds -shared -o serenitas_date.so date.o + +date.o: date.c + gcc -fPIC -I $(INCLUDE_DIR) -c date.c diff --git a/sql/date.c b/sql/date.c new file mode 100644 index 00000000..aa81342c --- /dev/null +++ b/sql/date.c @@ -0,0 +1,81 @@ +#include "postgres.h" +#include "fmgr.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; + } +} + +PG_FUNCTION_INFO_V1(cds_accrued); + +// postgresql represents dates as number of days since 2000-01-01 +// TDate are integers since 1601-01-01 +Datum cds_accrued(PG_FUNCTION_ARGS) { + const char default_cal[] = "NONE"; + const char us_cal[] = "/usr/share/cds/US"; + DateADT d = PG_GETARG_DATEADT(0); + float8 coupon = PG_GETARG_FLOAT8(1); + bool include_cashflow = PG_GETARG_BOOL(2); + const char* currency = text_to_cstring(PG_GETARG_TEXT_PP(3)); + const char* cal; + + if (strcmp(currency, "USD") == 0) { + cal = us_cal; + } else { + cal = default_cal; + } + TDate date = d + 145731 + 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); + } + PG_RETURN_FLOAT8((date - date_prev) / 360. * coupon); +} diff --git a/sql/dawn.sql b/sql/dawn.sql index e8f83a4f..2d920a12 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1543,15 +1543,13 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ name text, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE - days integer; eur_fx float; params text; sqlquery text; BEGIN -days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; IF strat IS NOT NULL THEN - params := '$1, $4'; + params := '$1, $3'; ELSE params := '$1'; END IF; @@ -1569,14 +1567,14 @@ SELECT index_price.duration, index_price.theta2, index_price.closeprice, index_price.closespread, (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), - -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * + -temp.notional * temp.fact * cds_accrued($1, temp.fixed_rate/100., TRUE, temp.currency) (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)', params); IF strat IS NOT NULL THEN - RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat; + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, strat; ELSE - RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx; END IF; END; $$ LANGUAGE plpgsql; @@ -1588,17 +1586,15 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ factor float, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE - days integer; eur_fx float; params text; sqlquery text; and_clause text; BEGIN -days:=days_accrued(p_date); SELECT DISTINCT ON (date) eurusd INTO eur_fx FROM fx WHERE date BETWEEN p_date - INTERVAL '3 DAYS' AND p_date; IF strat IS NOT NULL THEN - params := '$1, $5, $2'; - and_clause := 'AND strat = ltrim($5::text, ''SER_'')::strategy'; + params := '$1, $4, $2'; + and_clause := 'AND strat = ltrim($4::text, ''SER_'')::strategy'; ELSE params := '$1, NULL, $2'; and_clause := ''; @@ -1616,14 +1612,14 @@ SELECT index_price.duration2, index_price.theta2, index_price.closeprice, index_price.closespread, (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END), - -temp.notional * temp.fixed_rate/100. * temp.fact * $4 / 360 * + -temp.notional * temp.fact * cds_accrued($1, temp.fixed_rate/100, FALSE, temp.currency::text) * (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)', params); IF strat IS NOT NULL THEN - RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days, strat; + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, strat; ELSE - RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days; + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx; END IF; END; $$ LANGUAGE plpgsql; @@ -1943,11 +1939,10 @@ from dates import imm_date return imm_date(p_date) $$ LANGUAGE plpython3u; -CREATE OR REPLACE function days_accrued(p_date date) -RETURNS integer AS $$ -from dates import days_accrued -return days_accrued(p_date) -$$ LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION cds_accrued(date, double precision, bool, text DEFAULT 'USD') RETURNS integer +AS '$libdir/serenitas_date', 'cds_accrued' +LANGUAGE C STRICT; + CREATE MATERIALIZED VIEW factors_history AS WITH temp AS ( |