1 files changed, 35 insertions, 1 deletions
diff --git a/python/analytics/utils.py b/python/analytics/utils.py
index 8905000c..09145b41 100644
--- a/python/analytics/utils.py
+++ b/python/analytics/utils.py
@@ -1,12 +1,13 @@
 import datetime
 import numpy as np
 import pandas as pd
+from .exceptions import MissingDataError
 from scipy.special import h_roots
 from dateutil.relativedelta import relativedelta, WE
 from functools import partial, wraps
 from pyisda.date import pydate_to_TDate
 from pandas.api.types import CategoricalDtype
-from pandas.tseries.offsets import CustomBusinessDay, Day, QuarterBegin
+from pandas.tseries.offsets import CustomBusinessDay
 from pandas.tseries.holiday import get_calendar, HolidayCalendarFactory, GoodFriday
 
 fed_cal = get_calendar("USFederalHolidayCalendar")
@@ -153,3 +154,36 @@ def memoize(f=None, *, hasher=lambda args: (hash(args),)):
             return v
 
     return cached_f
+
+
+def to_TDate(arr: np.ndarray):
+    """ convert an array of numpy datetime to TDate"""
+    return arr.view("int") + 134774
+
+
+def get_external_nav(engine, trade_id, value_date=None, trade_type="swaption"):
+    query = (
+        "SELECT date, "
+        "nav, "
+        "(case when date < settle_date "
+        "then price * notional/100 * (2 * buysell::integer - 1) "
+        "else 0."
+        "end) as upfront FROM external_marks_deriv "
+        f"LEFT JOIN {trade_type} "
+        "ON cpty_id = identifier WHERE id=%s "
+    )
+    if value_date:
+        query += "AND date=%s"
+        r = engine.execute(query, (trade_id, value_date))
+        try:
+            date, nav, upfront = next(r)
+        except StopIteration:
+            raise MissingDataError(
+                f"No quote available for {trade_type} {trade_id} on {value_date}"
+            )
+        return nav + upfront
+    else:
+        query += "ORDER BY DATE"
+        return pd.read_sql_query(
+            query, engine, params=(trade_id,), parse_dates=["date"], index_col=["date"]
+        )