def update_ccy_price(data):
# Update weekly dataframe into DB
data = data.fillna(0)
schema_name = "commodity"
for index, row in data.iterrows():
sql_string = "Replace INTO currency(Dates,USD_Index,USDEUR,USDGBP,USDAUD,CADUSD,JPYUSD,CNYUSD,HKDUSD,TWDUSD,KRWUSD,THBUSD,SGDUSD,MYRUSD,BRLUSD,INRUSD, WTI) VALUES("
# Sql generation
if isinstance(index, str):
sql_string += "'"+index+"',"
elif isinstance(index, dt.datetime):
sql_string += "'"+str(index)+"',"
for ele in row:
try:
ele = float(ele)
except:
if isinstance(ele, str):
ele = "'"+ele+"'"
elif isinstance(ele, dt.datetime):
ele = "'"+str(ele)+"'"
sql_string += (str(ele)+",")
sql_string = sql_string[:-1]+")"
# DB execution
try:
db.dbExecute( schema_name,sql_string )
except:
print("CCY upload warning Index:"+str(index))
pass
return 0
def get_raw_data(self, sdate, schema_name, table_name):
""" Get data from DB and generate
data annually for SWAP and OIS
!Important that is why we have
augument_by_frequency functions
"""
if type(sdate) is dt.date:
sdate = sdate.strftime("%Y-%m-%d")
sqlstring = "Select * from " + table_name + " where sdate = \'" + sdate + "\'"
raw_data = dbEx.dbExecute(schema_name, sqlstring)
if not raw_data:
print("Worning cannot find corresponding data")
print("Current date : " + sdate)
print("Return zero...")
return 0
if table_name in ("yield_curve", "fx_curve"):
sqlstring = "Select * from curve_setting"
raw_setting = dbEx.dbExecute(schema_name, sqlstring)
all_ccy = set([ele["currency"] for ele in raw_data])
ans_book = {}
if table_name[:3].upper() == "OIS":
for ccy in all_ccy:
data = [(ele["maturity"], ele["rates"]) for ele in raw_data
if ele["currency"].upper() == ccy]
data = sorted(data, key=lambda tup: tup[0])
data = BS_TF.augument_by_frequency(data, 12)
ans_book[ccy] = data
elif table_name[:5].upper() == "YIELD" or table_name[:2].upper(
) == "FX":
for ccy in all_ccy:
Cash_Num = [ele["number"] for ele in raw_setting \
if ele["currency"].upper() == ccy \
and ele["name"].upper() == "CASH"][0]
Future_Num = [ele["number"] for ele in raw_setting \
if ele["currency"].upper() == ccy \
and ele["name"].upper() == "FUTURE"][0]
Swap_Num = [ele["number"] for ele in raw_setting \
if ele["currency"].upper() == ccy \
and ele["name"].upper() == "SWAP"][0]
data = [
ele for ele in raw_data if ele["currency"].upper() == ccy
]
data_CASH = self.get_rates_by_type(data, "cash")
data_FUTURE = self.get_rates_by_type(data, "future")
data_SWAP = self.get_rates_by_type(data, "swap")
data_SWAP = BS_TF.augument_by_frequency(data_SWAP, 12)
ans_book[ccy] = {
"cash": [data_CASH, Cash_Num],
"future": [data_FUTURE, Future_Num],
"swap": [data_SWAP, Swap_Num],
}
return ans_book
def _upload_db(self,ticker,data):
for Date, Value in data.items():
if not math.isnan(Value):
sql_string = self.__sql_format.format(Date,ticker,Value)
db.dbExecute( self.__schema_name, sql_string )
else:
msg_string = "Ticker:{},Date:{},Missing Data".format(ticker,Date)
self.__error_msg.append(msg_string)
print(msg_string)
continue
def get_other_data( self,
sdate,
schema_name,
table_name ):
""" Get data from DB and generate
data annually for Others
!NOTE we only consider data within
1 year!
"""
if isinstance( sdate, str ):
for fmt in ( "%Y-%m-%d", "%m/%d/%Y" ):
try:
sdate = dt.datetime.strptime(sdate, fmt).date()
break
except ValueError:
pass
sdate = date(sdate.year-1,1,1)
if type(sdate) is dt.date:
sdate = sdate.strftime("%Y-%m-%d")
sqlstring = "Select * from " + table_name + " where date >= \'" + sdate + "\'"
raw_data = dbEx.dbExecute( schema_name, sqlstring )
if not raw_data:
print("Worning cannot find corresponding data")
print("Current date : "+sdate)
print("Return zero...")
return 0
return raw_data
def get_panel_data():
# Get all historical price for weekly report
sql_string = "Select * from market where Dates = (select MAX(Dates) from market)"
schema_name = "commodity"
data = db.dbExecute(
schema_name,
sql_string,
)
return data
def get_historical_single(start, end, code):
# Get all historical price for weekly report
sql_string = "Select * from market where Dates >=\'"+str(start)+"\' and Dates <=\'"+\
str(end)+"\'"+" and Close > 0 and Code LIKE '%"+code+"%'"
schema_name = "commodity"
data = db.dbExecute(
schema_name,
sql_string,
)
return data
def get_histroical_ccy(start, end):
# Get all historical price in currency sector
sql_string = "Select * from currency where Dates >=\'"+\
str(start)+"\' and Dates <=\'"+ str(end)+"\'"
schema_name = "commodity"
data = db.dbExecute(
schema_name,
sql_string,
)
return data
def get_fx_spot_raw(self, schema_name):
""" This module should not be here but
currently do not have a stand alone FX
module for this project
"""
ans = {}
tb_name = "fx_spot"
sqlstring = "Select currency, rate from " + tb_name + " where sdate = \'" + self.cur_date + "\'"
raw_data = dbEx.dbExecute(schema_name, sqlstring)
for ele in raw_data:
ans[ele["currency"]] = ele["rate"]
return ans
def update_commodity_index( df ):
# Update weekly commodity index into DB
schema_name = "commodity"
for row in df.to_records():
sql_string = "Replace INTO commodity_index(Dates,name,open,high,low,close,volume,opi,r1,r2) VALUES("
# Sql generation
for ele in row:
try:
ele = float(ele)
except:
if isinstance(ele, str):
ele = "'"+ele+"'"
elif isinstance(ele, dt.date) or isinstance(ele, dt.datetime):
ele = "'"+str(ele)+"'"
sql_string += (str(ele)+",")
sql_string = sql_string[:-1]+")"
# DB execution
try:
db.dbExecute( schema_name,sql_string )
except:
pass
def update_weekly_commodity(df):
# Update weekly dataframe into DB
schema_name = "commodity"
for index, row in df.iterrows():
sql_string = "Replace INTO market(Dates,Code,Open,High,Low,Close,OPI,Vol) VALUES("
# Sql generation
for ele in row:
try:
ele = float(ele)
except:
if isinstance(ele, str):
ele = "'" + ele + "'"
elif isinstance(ele, dt.datetime):
ele = "'" + str(ele) + "'"
sql_string += (str(ele) + ",")
sql_string = sql_string[:-1] + ")"
# DB execution
try:
db.dbExecute(schema_name, sql_string)
except:
pass
def update_weekly_inventory(df):
# Update weekly dataframe into DB
schema_name = "commodity"
df = df[["Dates", "Product", "INV"]]
for index, row in df.iterrows():
sql_string = "Replace INTO inventory(Dates,Product,INV) VALUES("
# Sql generation
for ele in row:
try:
ele = float(ele)
except:
if isinstance(ele, str):
ele = "'" + ele + "'"
else:
ele = "'" + str(ele) + "'"
sql_string += (str(ele) + ",")
sql_string = sql_string[:-1] + ")"
# DB execution
try:
db.dbExecute(schema_name, sql_string)
except:
pass
def get_historical_inventory(start="", end=""):
# Get all historical price for weekly report
if start == "":
sql_string = "Select * from inventory"
else:
sql_string = "Select * from inventory where Dates >=\'"+\
str(start)+"\' and Dates <=\'"+str(end)+"\'"
schema_name = "commodity"
data = db.dbExecute(
schema_name,
sql_string,
)
return data
def get_index_all(start="", end=""):
# Get all historical commodity_index for weekly report
if start != "":
sql_string = "Select * from commodity_index where Dates >=\'"+str(start)+"\' and Dates <=\'"+\
str(end)+"\'"+" and Close > 0"
else:
sql_string = "Select * from commodity_index where Close > 0"
schema_name = "commodity"
data = db.dbExecute(
schema_name,
sql_string,
)
return data
def update_weekly_commodity( df ):
# Update weekly dataframe into DB
schema_name = "commodity"
for index, row in df.iterrows():
sql_string = "Replace INTO market(Dates,Code,Open,High,Low,Close,OPI,Vol) VALUES("
# Sql generation
sql_string += ("\'"+str(index)+"\',")
for ele in row:
try:
ele = float(ele)
except:
if isinstance(ele, str) and len(ele)>1:
ele = "'"+ele+"'"
else:
ele = 0
sql_string += (str(ele)+",")
sql_string = sql_string[:-1]+")"
# DB execution
try:
db.dbExecute( schema_name,sql_string )
except:
raise Exception("Upload Error:"+sql_string)
def sql_raw_data( sdate,
order,
schema_name,
table_name ):
""" This function is used for GUI cover page output
We can change the sdate in order to bakcdate the
historical data
"""
sqlstring = "Select * from " + table_name + " where sdate = \'" + sdate + "\'"
raw_data = dbEx.dbExecute( schema_name, sqlstring )
all_ccy = set([ele["currency"] for ele in raw_data])
ans_book = {}
for ccy in all_ccy:
data = reformat_rates( ccy, order, raw_data )
ans_book[ccy] = data
return ans_book, all_ccy
def _download_db(self, tickers, start_t, end_t):
sql_download = self.__sql_download.format(start_t,end_t)
data = db.dbExecute( self.__schema_name, sql_download )
data = [ele for ele in data if ele["ticker"] in tickers]
return data
os.path.dirname(
os.path.realpath(__file__)))
f_name = dir_path + "\\Basic Pricing.xlsm"
reader = reader.excel_reader(f_name)
inputs = reader.read_basic_mkt_input("Pricer")
cv_date = inputs["curve date"]
val_date = inputs["value date"]
source = inputs["source"]
schema_name = "Yield_Curve"
# Load libor rates curve
tb_name = "yield_curve"
sqlstring = "Select * from "+tb_name+" where sdate = '"+str(cv_date)+"'"
ans_libor = db.dbExecute( schema_name, sqlstring )
# Load fx rates curve
tb_name = "fx_curve"
sqlstring = "Select * from "+tb_name+" where sdate = '"+str(cv_date)+"'"
ans_fx = db.dbExecute( schema_name, sqlstring )
# Load fx spot
tb_name = "fx_spot"
sqlstring = "Select * from "+tb_name+" where sdate = '"+str(cv_date)+"'"
fx_spot = db.dbExecute( schema_name, sqlstring )
# Load vol cube/surface
tb_name = "vcub"
sqlstring = "Select * from "+tb_name+" where sdate = '"+str(cv_date)+"'"
vcub = db.dbExecute( schema_name, sqlstring )
def get_commodity_all():
sql_str = "Select * from market where close > 0"
schema_name = "commodity"
data = db.dbExecute(schema_name, sql_str)
return data
