#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import datetime
import sys
import os
from sqlalchemy import create_engine
import pandas as pd
import urllib
import getopt
import re
import logging
logging.basicConfig(level=logging.INFO,filename='report_runlog.log',filemode='a',
format='%(asctime)s-%(levelname)5s: %(message)s')
def usage():
print '-s, --server\t\tServer name or ip address which DB hosts'
print '-d, --database\t\tThe database name in the DB server'
print '-u, --user\t\tThe user name to access the database'
print '-p, --password\t\tThe password to access the database'
print '-t, --date\t\tThe date to search in the database, format is 20180102. default: Yesterday)'
print '-a, --account\t\tThe account to search in the database. default: "F190402","F190436"'
print '-f, --filename\t\tSpecify the report name. default: "report_now.xlsx"'
print '-h, --help\t\tprint this screen'
def getOption():
global server, dbname, user, password, in_date, accounts, filename
show_help = False
try:
opts, args = getopt.getopt(sys.argv[1:],'s:d:u:p:t:a:f:h',['server=','database=','user=','password=','date=','accounts=','filename=','help'])
except getopt.GetoptError:
show_help = True
if not opts:
show_help = True
for o, m in opts:
if o in ("-h", "--help"):
usage()
sys.exit()
else:
if o in ("-s", "--server"):
server = m
elif o in ("-d", "--database"):
dbname = m
elif o in ("-u", "--user"):
user = m
elif o in ("-p", "--password"):
password = m
elif o in ("-t", "--date"): #YYYYMMDD
in_date = datetime.datetime.strptime(m,"%Y%m%d").date()
elif o in ("-a", "--account"):
m = m.strip(',')
accounts = m.split(',')
elif o in ("-f", "--filename"):
filename = m
if show_help:
usage()
sys.exit()
def match_contract(rstr,contract_code):
reg = re.compile(rstr)
regMatch = reg.match(contract_code)
return regMatch
# Contract ID：Exchange、Product Code、Currency、Prompt Date、Strike ... Close Price
def getContract(engine):
logging.info("======== loadContracts ========")
df = pd.read_sql(('SELECT [Contract Code],[Exchange],[Product Code],[Currency],[Closing Price],[Contract Type] FROM vw_Contract_Master '), engine) # , chunksize = 10000
now = str(datetime.datetime.now().strftime("%Y"))[0:2]
c_dict = {}
for row in df.itertuples(index=True, name='ContractInfo'):
contract_code = row[1].strip().encode('ascii')
exchange = row[2].strip().encode('ascii')
product_code = row[3].strip().encode('ascii')
currency = row[4].encode('ascii')
close_price = round(row[5],4)
symbol_len = len(product_code)
contract_type = row[6].strip()
option_str = "^(?P<symbol>\w{{{len}}})(?P<strike>[0-9.]+)(?P<callput>[C|P])(?P<expiry>\d{{4}})".format(len = symbol_len)
future_str = "^(?P<symbol>\w{{{len}}})(?P<expiry>\d{{4}})".format(len = symbol_len)
try:
if contract_type == "Option":
cm = match_contract(option_str,contract_code)
prompt_date = now + cm.group("expiry").encode('ascii')
if cm.group("callput") == "P":
c_dict[contract_code]=[exchange,product_code,currency,prompt_date,((cm.group("strike")+"PUT")).encode('ascii'),close_price]
elif cm.group("callput") == "C":
c_dict[contract_code]=[exchange,product_code,currency,prompt_date,((cm.group("strike")+"CALL")).encode('ascii'),close_price]
else:
c_dict[contract_code]=[exchange,product_code,currency,prompt_date,(cm.group("strike")+cm.group("callput")).encode('ascii'),close_price]
logging.info("Not put or call option: {}".format(row))
else:
cm = match_contract(future_str,contract_code)
prompt_date = now + cm.group("expiry").encode('ascii')
c_dict[contract_code]=[exchange,product_code,currency,prompt_date,"",close_price]
except AttributeError:
c_dict[contract_code]=[exchange,product_code,currency,"","",close_price]
logging.warning("ContractID is nonstandard: {} ,save as {}".format(row,c_dict[contract_code]))
return c_dict
# Contract ID: Lots(buy)、Lots(sell)、AVGPrice(buy)、AVGPrice(sell)
def loadTrades(engine,account,date):
logging.info("======== loadTrades: {}".format(date))
df = pd.read_sql(('SELECT [Contract Code],SUM([Quantity]),SUM([Contract Price]*[Quantity])/SUM([Quantity]),[B/S] FROM vw_Trade_Movement WHERE [Client Code] = \'{}\' AND CONVERT(varchar(100),[Trade Date],23) = \'{}\' GROUP BY [Contract Code],[B/S]').format(account,date), engine)
count_row = len(df.index)
if count_row == 0:
logging.warning("There is no records in vw_Trade_Movement for: {} {}".format(date,account))
else:
logging.info("There is are {} records in vw_Trade_Movement for: {} {}".format(count_row,date,account))
t_dict = {}
for row in df.itertuples(index=True, name='TradeInfo'):
contract_code = row[1].strip().encode('ascii')
lots = row[2]
avg_price = row[3]
direction = row[4].strip().encode('ascii')
if contract_code in t_dict.keys():
logging.debug("Both sell and buy trading:{} {}".format(row,t_dict[contract_code]))
else:
t_dict[contract_code] = [0,0,0,0]
if direction == "B" :
t_dict[contract_code][0] = lots
t_dict[contract_code][2] = avg_price
elif direction == "S" :
t_dict[contract_code][1] = lots
t_dict[contract_code][3] = avg_price
else:
t_dict[contract_code] = [0,0,0,0]
logging.warning("Not sell or buy trading: {}, set to 0".format(row))
return t_dict
def loadOpenPosition(engine,account):
logging.info("======== loadOpenPosition ========")
df = pd.read_sql(('SELECT [Contract Code], SUM([Quantity(Long)])-SUM([Quantity(Short)]) FROM vw_Open_Day_Position WHERE [Client Code] = \'{}\' GROUP BY [Contract Code] ').format(account), engine)
count_row = len(df.index)
if count_row == 0:
logging.warning("There is no records in vw_Open_Day_Position for: {} ".format(account))
else:
logging.info("There is are {} records in vw_Open_Day_Position for: {} ".format(count_row,account))
o_dict = {}
for row in df.itertuples(index=True, name='OpenInfo'):
contract_code = row[1].strip().encode('ascii')
open_qty = row[2]
o_dict[contract_code] = open_qty
return o_dict
def loadClosePosition(engine,account):
logging.info("======== loadClosePosition ========")
df = pd.read_sql_query(('SELECT [Contract Code], SUM([Quantity(Long)])-SUM([Quantity(Short)]) FROM vw_Day_Closed_Position WHERE [Client Code] = \'{}\' GROUP BY [Contract Code] ').format(account), engine)
count_row = len(df.index)
if count_row == 0:
logging.warning("There is no records in vw_Day_Closed_Position for: {} ".format(account))
else:
logging.info("There is are {} records in vw_Open_Closed_Position for: {} ".format(count_row,account))
c_dict = {}
for row in df.itertuples(index=True, name='CloseInfo'):
contract_code = row[1].strip().encode('ascii')
close_qty = row[2]
c_dict[contract_code] = close_qty
return c_dict
if __name__ == "__main__":
# default test environment
server = '192.168.15.74'
user = 'TPass'
password = 'TPass1234'
dbname = '20181213'
# default parameter
in_date = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
accounts = ["F190402","F190436"]
now = datetime.datetime.now().strftime("%Y%m%d_%H%M")
filename = 'report_' + str(now) + '.xlsx'
# parameter from cmd
getOption()
logging.info("The query date is: {}".format(in_date))
logging.info("The query accounts is: {}".format(accounts))
logging.info("The result file will be: {}".format(filename))
# filename exist or not
if os.path.exists(filename):
print "The filename had been used, please choose another name "
sys.exit()
# database connection example (SERVER=192.168.15.74;DATABASE=20181213;UID=TPass;PASSWORD=TPass1234)
params = urllib.quote_plus(("DRIVER={{SQL Server}};SERVER={server};DATABASE={dbname};UID={user};PWD={password}").format(server = server, dbname = dbname, user = user, password = password))
connect_string = ("mssql+pyodbc:///?odbc_connect={}").format(params)
logging.info("The query DB is: {}".format(params))
engine = create_engine(connect_string, echo=True)
# caculate data
list_head = ["交易所","商品代碼","幣別","合約月份","選擇權序列","當日BUY成交口數","當日SELL成交口數","當日BUY平均成交金額","當日SELL平均成交金額","當日結算價LONG","當日結算價SHORT","當日留倉口數","Client Code"]
s = getContract(engine)
list_body = []
for account in accounts:
logging.info("======== product report for: {}".format(account))
t = loadTrades(engine,account,in_date)
o = loadOpenPosition(engine,account)
c = loadClosePosition(engine,account)
for key,value in t.items():
try:
refer = s[key]
except KeyError:
refer = [key,"","","","",0]
logging.debug("There is no matched referance data for: {}, set to {} ".format(key,refer))
try:
open_p = o[key]
except KeyError:
open_p = 0
logging.debug("There is no matched open position for: {}, set to 0 ".format(key))
try:
close_p = c[key]
except KeyError:
close_p = 0
logging.debug("There is no matched close position for: {}, set to 0 ".format(key))
position = open_p + (t[key][0]+t[key][1]) - (2*close_p)
if position >= 0:
tmp = [refer[0],refer[1],refer[2],refer[3],refer[4],t[key][0],t[key][1],t[key][2],t[key][3],refer[5],0,abs(position),account]
list_body.append(tmp)
else:
tmp = [refer[0],refer[1],refer[2],refer[3],refer[4],t[key][0],t[key][1],t[key][2],t[key][3],0,refer[5],abs(position),account]
list_body.append(tmp)
logging.debug("one contract had been finished: {}".format(tmp))
# write into excel
df = pd.DataFrame(list_body, columns=list_head)
df.to_excel(filename, encoding='utf-8', index=False, header=True)
logging.info("The report had been finished: {}".format(filename))
Copy the code