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