def quotes(self):
QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))
conn = sqlite3.connect('HSAsserts.db')
cursor = conn.cursor()
stocks='' #select c.Code,TradeMarket from b_code c where c.Enable=1 and c.TradeMarket notnull
for row in cursor.execute("select c.Code,TradeMarket from b_code c,v_assets a where c.Enable=1 and c.TradeMarket notnull and a.code= c.code and a.sumvollum !=0 "):
if( stocks == ''):
stocks = row[1].lower() + row[0]
else:
stocks = stocks + ',' + row[1].lower()+row[0]
qd = SinaQuote.GetQuote( stocks )
#print(qd)
sql = "INSERT OR REPLACE INTO D_LatestQuote VALUES (?, ?, ?)"
for i in range( len(qd) ):
CValue = qd.iloc[i]['C']
if CValue == 0:
CValue = qd.iloc[i]['PC']
sqltuple = (qd.iloc[i]['code'][2:] , qd.iloc[i]['datetime'].strftime('%Y-%m-%d %H:%M:%S'), str(CValue) )
cursor.execute(sql,sqltuple)
conn.commit()
conn.close()
QtGui.QApplication.restoreOverrideCursor()
QtGui.QMessageBox.information(self,self.tr('Get Quotes'), self.tr('[{0}] records updated.'.format(i)) , QtGui.QMessageBox.Ok)
def __init__(self, date, code, name, number, price, op_type): self.stock_code = code self.stock_name = name self.stock_number = float(number) if price == 0: price = SinaQuote.getLastClosePrice(code, date) self.position_flow = [ (date, number, price, op_type) ]
def calculate_profit(self, last_date, is_rongquan=False): stock_count = 0 stock_cost = 0 profit_total = 0 profit_list = [] for (date, number, price, op_type) in self.position_flow: profit = 0 if (number>0 and is_rongquan==False) or (number<0 and is_rongquan==True): if stock_cost == 0: stock_cost = price else: total = stock_count + number if total == 0: print is_rongquan for p in self.position_flow: print p print '!!! total stock number is 0, date %s stock_code %s stock_name %s number %d' % (date, self.stock_code, self.stock_name, number) stock_cost = float(stock_count*stock_cost + number*price) / (stock_count + number) stock_count += number elif (number<0 and is_rongquan==False) or (number>0 and is_rongquan==True): profit = (price - stock_cost)*(-number) profit_total += profit stock_count += number profit_list.append( (date, self.stock_code, self.stock_name, number, price, stock_count, stock_cost, profit, profit_total, op_type) ) if stock_count != 0: last_price = SinaQuote.getLastClosePrice(self.stock_code, last_date) profit = stock_count * ( last_price - stock_cost) profit_total += profit profit_list.append( (last_date, self.stock_code, self.stock_name, 0, last_price, stock_count, stock_cost, profit, profit_total, 3) ) # 操作类型3是持仓 columns = [u'日期', u'代码', u'名称', u'数量', u'价格', u'当前总持仓', u'当前成本', u'本次盈亏', u'总盈亏', u'操作类型'] return (profit_total, profit_list, columns)
def stock_in(self, date, stock_code, stock_name, stock_number, price, actual_amount): # 担保划入 #print 'stock in:', stock_name,stock_number if math.isnan(price) or price == 0: price = SinaQuote.getLastClosePrice(stock_code, date) if math.isnan(actual_amount) or actual_amount == 0: actual_amount = stock_number * price self.bank_in_money(actual_amount) self.buy_stock(date, stock_code, stock_name, stock_number, price, actual_amount)
def getValue(self, date): closePrice = SinaQuote.getLastClosePrice(self.stock_code, date) value = 0; if closePrice == -1: print "!!!error: getClosePrice %s %s error" % (self.stock_code, date) else: value = closePrice * self.stock_number return value
def add_stock_code_prefix(df, cname): df = df.reset_index() for i,row in df.iterrows(): stock_name = SinaQuote.get_sh_sz(row[cname]) if stock_name != u'': df.loc[i, cname] = stock_name del df['index'] return df
def stock_out(self, date, stock_code, stock_name, stock_number, price, actual_amount): #print 'stock out:', stock_name,stock_number if price == 0: price = SinaQuote.getLastClosePrice(stock_code, date) if actual_amount == 0: actual_amount = stock_number * price self.sell_stock(date, stock_code, stock_name, stock_number, price, actual_amount) self.calculate_nv() # !!! need this because after sell stock nv will change !!! self.bank_out_money(actual_amount)
def get_position_dataframe(self, date): position_list = [] for stock_code, s in self.stock.items(): if s.stock_number != 0: price = SinaQuote.getLastClosePrice(stock_code, date) position_list.append([date, s.stock_code, s.stock_name, s.stock_number, price, s.stock_number*price]) df = pd.DataFrame() if len(position_list) > 0: df = pd.DataFrame(data=position_list, columns=[u'日期', u'代码', u'名称', u'数量', u'价格', u'市值']) return df
def getNAV(self):
conn = sqlite3.connect('HSAsserts.db')
cursor = conn.cursor()
navs = []
#select c.Code,TradeMarket from b_code c,v_assets a where c.Enable=1 and c.NetvalueMarket ='OF' and a.code= c.code and a.sumvollum !=0
cursor.execute("select c.Code from b_code c where c.Enable=1 and c.NetvalueMarket ='OF'")
codes = cursor.fetchall() # select c.Code from b_code c where c.Enable=1 and c.NetvalueMarket ='OF'
progress = QtGui.QProgressDialog("Get Navs...", "Abort", 0, len(codes), self)
progress.setWindowTitle('PortfoliMan')
progress.setWindowModality(QtCore.Qt.WindowModal)
failedCodes=[]
for r in range(len(codes)):
fundCode = codes[r][0]
nav = SinaQuote.GetNav(fundCode)
if nav != None :
navs.append( nav )
else:
nav = SinaQuote.GetNav(fundCode) #try again
if nav != None :
navs.append( nav )
else:
failedCodes.append(fundCode) #failed again
progress.setValue(r)
QtCore.QCoreApplication.processEvents()
import time
time.sleep(0.5)
if progress.wasCanceled() :
return
progress.setValue(len(codes))
sql = "INSERT OR REPLACE INTO D_LatestNetvalue VALUES (?, ?, ?, ?)"
cursor.executemany(sql,navs)
conn.commit()
QtGui.QMessageBox.information(self,self.tr('Get Navs'), self.tr('[{0}/{1}] records updated. Failed codes {2}'.format(len(navs),len(codes),failedCodes)) , QtGui.QMessageBox.Ok)
conn.close()
with open('D_LatestNetvalue.csv', 'w', newline='') as csvfile:
fieldnames = ['Code', 'Tdate', 'Netvalue','SumNetvalue']
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writerow( dict( Code ='D_LatestNetvalue', Tdate='',Netvalue='',SumNetvalue='' ) )
writer.writeheader()
for nav in navs:
writer.writerow(dict( Code =nav[0], Tdate=nav[1],Netvalue=nav[2],SumNetvalue=nav[3] ))
def get_value(date):
total_value = 0
for line in open(argv[1]):
r = line.split(',')
#print r[1][4:-1]
stock_number = int(r[6])
stock_cd = r[1][4:-1]
last_price = SinaQuote.getLastClosePrice(stock_cd, date)
print stock_cd, stock_number, last_price
value = stock_number * last_price
total_value += value
return total_value
def addFund(self):
secID = self.cbFund.itemData(self.cbFund.currentIndex())
if secID in self._funds:
return
npNav = SinaQuote.GetHNav(secID)
#stsdate = pd.to_datetime( self.sdate.date().toPython() )
#etsdate = pd.to_datetime( self.edate.date().toPython() )
stsdate = self.sdate.text()
etsdate = self.edate.text()
npNav = npNav[npNav.fbrq >= stsdate]
npNav = npNav[npNav.fbrq <= etsdate]
npNav.adjNav = npNav.adjNav / npNav.adjNav[0] * 100
self.ax1.plot(pd.to_datetime(npNav.fbrq),
npNav.adjNav,
label=self.cbFund.currentText())
leg = self.ax1.legend(loc=0, fontsize=10, frameon=False)
leg.get_frame().set_facecolor((.94, .94, .94))
import matplotlib.dates
import datetime
xlim = matplotlib.dates.date2num([
self.sdate.date().toPython(),
self.edate.date().toPython() + datetime.timedelta(days=2)
])
if np.min(npNav.adjNav) - 5.0 < self._ylim[0]:
self._ylim[0] = np.min(npNav.adjNav) - 5.0
if np.max(npNav.adjNav) + 5.0 > self._ylim[1]:
self._ylim[1] = np.max(npNav.adjNav) + 5.0
ysticks = [100]
ys = 110
while ys < self._ylim[1]:
ysticks.append(ys)
ys = ys * 1.1
ys = 90
while ys > self._ylim[0]:
ysticks.append(ys)
ys = ys * 0.9
ysticks.sort()
self.ax1.set_yscale(u'log')
self.ax1.set_ylim(self._ylim[0], self._ylim[1])
self.ax1.yaxis.set_ticks(ysticks)
self.ax1.yaxis.set_major_formatter(mticker.FormatStrFormatter('%.1f'))
self.ax1.yaxis.grid(True, color='0.6', linestyle='-', linewidth=0.5)
self.ax1.set_xlim(xlim[0], xlim[1])
#self.canvas.fig.autofmt_xdate()
self.canvas.draw_idle()
self._funds.append(secID)
def process_5447231412(): df = normalize.parse_file(u'all.xlsx') for i,row in df.iterrows(): operation = row[u'操作'] stock_name = row[u'证券名称'] stock_code = row[u'证券代码'] if stock_name == u'申购款' and operation in [u'申', u'卖']: stock_name = SinaQuote.getNewStockName(stock_code) if stock_name == '': print 'error, new stock name not found:%s' % stock_code df.loc[i,u'证券名称'] = stock_name if operation == u'申': df.loc[i,u'操作'] = u'申购扣款' else: df.loc[i,u'操作'] = u'申购还款' df.to_excel(u'调整后.xls',encoding='gbk')
def process_1488061441(): df = normalize.parse_file(u'卓建华2014交割单1.201503050951.xlsx') for i,row in df.iterrows(): operation = row[u'委托类别'] bianhao = row[u'成交编号'] stock_name = row[u'证券名称'] stock_code = row[u'证券代码'] if operation == u'直接还款' and bianhao == u'申购扣款': df.loc[i,u'委托类别'] = u'申购扣款' elif stock_name == u'申购款' and operation == u'直接还款': stock_name = SinaQuote.getNewStockName(stock_code) if stock_name == '': print 'error, new stock name not found:%s' % stock_code df.loc[i,u'证券名称'] = stock_name df.loc[i,u'委托类别'] = u'申购扣款' df.to_excel(u'调整后.xls',encoding='gbk')
def analyze(file_name, merge_stock_operation_detail=False):
account_type = re.sub('_normalized\.xls', '', file_name)
flow_records = pd.read_excel(file_name, encoding='gbk')
SinaQuote.reset()
broker_processor = globals()['normal']
init_account = Account(None)
for i, row in flow_records.iterrows():
broker_processor.handle_row(row, init_account, i==0)
init_account.calculate_init_cash_if_no_remain_amount(flow_records)
init_account.print_position()
init_account.print_init_position()
init_account_value = init_account.calculate_init_position_value(20140101)
init_date = int(init_account.get_init_date())
print init_date
print init_account_value
print init_account.init_cash
account = Account( init_account )
account_status_data = []
#print "%d: %s" % (1, account.report_init())
process_day = datetime(2014,1,1)
init_day = datetime(init_date/10000, init_date%10000/100, init_date%100)
while process_day < init_day:
account.set_date(int(process_day.strftime('%Y%m%d')))
account.calculate_nv()
print account.get_status_data()
account_status_data.append(account.get_status_data())
process_day = compute.next_bday(process_day)
for i, row in flow_records.iterrows():
d = broker_processor.get_row_date(row)
row_date = datetime(d/10000, d%10000/100, d%100)
while row_date > process_day:
account.calculate_nv(int(process_day.strftime('%Y%m%d')))
print account.get_status_data()
#if process_day == datetime(2014,2,24) or process_day == datetime(2014,2,25):
# account.print_position()
#if int(process_day.strftime('%Y%m%d')) == 20140509 or int(process_day.strftime('%Y%m%d')) == 20140512:
#account.report_position_detail()
account_status_data.append(account.get_status_data())
process_day = compute.next_bday(process_day)
account.set_date(int(process_day.strftime('%Y%m%d')))
broker_processor.handle_row(row, account, i==0)
account.calculate_nv() # 每个操作必须重新计算净值
#print account.get_status_data(), d, row['operation'], row['actual_amount'], row['stock_code'], row['stock_name']
while process_day <= datetime(2014,12,31):
account.calculate_nv(int(process_day.strftime('%Y%m%d')))
print account.get_status_data()
account_status_data.append(account.get_status_data())
process_day = compute.next_bday(process_day)
account.set_date(int(process_day.strftime('%Y%m%d')))
df_nv_history = pd.DataFrame(data=account_status_data, columns=account.get_status_columns())
(uid, name) = get_uid_name()
out_file_name = uid + '_' + name + '_' + account_type + u'_分析结果' + datetime.now().strftime('%Y%m%d%H%M%S') + '.xls'
if merge_stock_operation_detail:
out_file_name = u'/Users/jiangbin/百度云同步盘/work/for_renjie/'+out_file_name
(profit_sum_list, profit_detail_df_list) = account.get_profit_detail(20141231)
#df_profit_detail = pd.DataFrame(data=profit_list, columns=profit_columns)
df_profit_sum = pd.DataFrame(data=profit_sum_list, columns=[u'代码', u'名称', u'累计赢利'])
df_init_position = init_account.get_init_position_dataframe()
df_final_position = account.get_position_dataframe(20141231)
#account.stock_position.get_detail_stock_flow(20141231)
buy_sell_count = account.buy_sell_count
df_overview = pd.DataFrame()
with pd.ExcelWriter(out_file_name) as writer:
df_overview = print_result_overview(account_type, df_nv_history, buy_sell_count, writer)
df_nv_history.to_excel(writer, sheet_name=u'净值历史', encoding='gbk')
df_profit_sum = add_stock_code_prefix(df_profit_sum, u'代码')
df_profit_sum.to_excel(writer, sheet_name=u'个股赢利汇总', encoding='gbk')
write_profit_detail(writer, profit_detail_df_list, merge_stock_operation_detail)
df_init_position[u'类型'] = u'期初持仓'
df_final_position[u'类型'] = u'期末持仓'
df_position_all = pd.concat([df_init_position, df_final_position])
df_position_all = add_stock_code_prefix(df_position_all, u'代码')
change_init_date(df_position_all)
df_position_all.to_excel(writer, sheet_name=u'期初期末持仓', encoding='gbk')
return df_overview
def stock_transfer(self, date, stock_code, stock_name, stock_number): # 协议转让 self.save_stock_code_name(stock_code, stock_name) price = SinaQuote.getLastClosePrice(stock_code, date) self.stock_position.minus(date, stock_code, stock_name, stock_number, price, 7) # 操作类型7是转出 # 现金不增加，份额减少 self.fund_units -= float(stock_number * price) / self.net_value
#coding=utf-8
# tpomcuvnmu
# 501038 166024 501049 （即将上市交易） 封闭2年的创新型杠杆封基163417
import pandas as pd
import numpy as np
import xlwings as xw
import SinaQuote
wb = xw.Book('Funds.xlsx')
sht = wb.sheets['FundPerf']
for i in range(2,1000):
code=sht.range('B{0}'.format(i)).value
print(code)
if code != None:
npNav = SinaQuote.GetHNav( code[2:] )
if len(npNav) > 1:
sht.range((i,4)).value = npNav.fbrq[-1]
sht.range((i,5)).value = npNav.adjNav[-1] / npNav.adjNav[-2] - 1.0
for j in range(6,20):
bdate = sht.range((1,j)).value
if bdate != None:
begNav = npNav[ npNav.fbrq ==bdate ]
if len(begNav) > 0:
sht.range((i,j)).value = npNav.adjNav[-1] / begNav.adjNav[0] - 1.0
pass
else:
break
else:
break
def pre_process(df): change_column_names(df) merge_zhaiyao_and_operation(df) #df = df.apply(remove_space, axis=0) # only for 2089526857张洁, for i, row in df.iterrows(): if row[u'operation'] in [u'基金申购拨出', u'基金赎回拨入'] and \ type(row[u'stock_code']) is unicode and row[u'stock_code'].startswith(u'AA0007'): df.loc[i, u'stock_number'] = abs(row[u'actual_amount']) if row[u'operation'] in [u'基金申购', u'基金赎回']: if type(row[u'stock_code']) is float and \ math.isnan(row[u'stock_code']): # no stock code df.loc[i, u'operation'] = u'基金其他' if row[u'operation'] == u'基金分拆': if row[u'stock_name'].endswith((u'A', u'B')): df.loc[i, u'operation'] = u'分级基金分拆' else: df.loc[i, u'operation'] = u'基金分拆-母鸡' if row[u'operation'] in [u'托管转入', u'托管转出']: #print row[u'stock_code'] if row[u'stock_code'] in [u'163113', u'165521']: df.loc[i, u'operation'] = u'分级基金－母基操作' #print row[u'operation'], row[u'stock_name'], type(row[u'stock_name']) stock_name = SinaQuote.getNewStockName(unicode(row[u'stock_name'])) if stock_name != '': df.loc[i, u'operation'] = u'新股相关' if row[u'stock_code'] == u'799999': df.loc[i, u'operation'] = u'登记指定相关' if type(row[u'operation']) in [float, int] or type(row[u'stock_name']) in [int]: print row[u'operation'],row if row[u'operation'].startswith(u'托管转入') and type(row[u'stock_name']) is unicode and row[u'stock_name'].endswith((u'A', u'B')): df.loc[i, u'operation'] = u'分级基金分拆' if row[u'operation'] == u'托管转入' and type(row[u'stock_name']) is unicode and row[u'stock_name'].startswith(u'DR'): df.loc[i, u'operation'] = u'红股入帐' if u'成交编号' in row.index: if row[u'operation'] == u'其他' and row[u'成交编号'] == u'股息差别税': df.loc[i, u'operation'] = u'股息差别税' if row[u'operation'] == u'转托' and row[u'成交编号'] == u'转托管转入': df.loc[i, u'operation'] = u'指定' if row[u'operation'] == u'托管转入' and row[u'成交编号'] == u'上市流通': df.loc[i, u'operation'] = u'新股相关' if row[u'operation'] == u'托管转出' and row[u'成交编号'] == u'上市转出': df.loc[i, u'operation'] = u'新股相关' if 'beizhu' in row.index: if row[u'operation'] == u'托管转出' and row['beizhu'] == u'托管转出' \ and (str(int(row[u'stock_code'])) not in [u'163113', u'165521']): print row[u'stock_code'], row[u'operation'], row[u'beizhu'] df.loc[i, u'operation'] = u'担保转出' if row['operation'] == u'撤指' and row['stock_number'] == 0: df.loc[i, u'operation'] = u'转存管转出' # handle special case if row[u'stock_code'] == 82046: df.loc[i, u'stock_code'] = 2046 out_file = '普通账户.xls' for o in df['operation']: if o in [u'融资利息扣款', u'融资买入', u'融资利息']: out_file = '融资融券账户.xls' return ( df.apply(process_operation, axis=1), out_file )
def buy_new_stock(self, date, stock_code, stock_name, stock_number, price, actual_amount): cd = SinaQuote.getNewStockCode(stock_code) if cd != '': stock_code = cd self.buy_stock(date, stock_code, stock_name, stock_number, price, actual_amount) self.buy_sell_count += 1
def handle_row(row, account, is_first_row):
#print row
deal_date = get_row_date(row)
deal_time = row[u'deal_time']
stock_code = norm_stock_code(row[u'stock_code'])
if stock_code == '000nan' and row['operation'] in ['buy','sell','earn stock']:
print row
stock_name = row[u'stock_name']
operation = row[u'operation']
stock_price = row[u'stock_price']
if stock_price != u'---':
stock_price = float(stock_price)
else:
stock_price = 0
stock_number = row[u'stock_number'] # number should always be positive
if stock_number != u'---':
#print stock_number, type(stock_number)
stock_number = abs(float(stock_number))
else:
stock_number = 0
deal_amount = 0
if 'deal_amount' in row.index:
deal_amount = float(row[u'deal_amount']) # optional
elif operation not in ['buy (bond repurchase)', 'sell (bond repurchase)']:
deal_amount = stock_number * stock_price
actual_amount = 0
if 'actual_amount' in row.index:
if not math.isnan(row[u'actual_amount']):
actual_amount = float(row[u'actual_amount'])
else:
actual_amount = deal_amount
remain_amount = 0
if 'remain_amount' in row.index:
remain_amount = float(row[u'remain_amount'])
else:
account.has_remain_amount_column = False
#print deal_date, deal_time, stock_code, stock_name, operation, stock_price, stock_number, actual_amount, remain_amount
if is_first_row:
init_cash = remain_amount-actual_amount
if row['operation'] == 'stock in': # 如果第一笔操作是担保转入，直接用剩余金额，不能减去发生金额
init_cash = remain_amount
account.set_init_cash(init_cash)
account.set_init_date(deal_date)
print 'first row, init_cash %f, init_date %s' % (init_cash, deal_date)
account.set_date(deal_date)
if 'remain_stock' in row.index:
remain_stock = row[u'remain_stock'] # number should always be positive
if remain_stock != u'---':
#print stock_number, type(stock_number)
remain_stock = abs(float(remain_stock))
else:
remain_stock = 0
process_init_position(account, remain_stock, stock_number, stock_code, stock_name, operation)
if operation == 'buy':
account.buy_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'buy (new stock)':
account.buy_new_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'buy (bond repurchase)':
#print '===========',row
SinaQuote.setGC001Price(stock_code, stock_number, deal_amount, actual_amount)
stock_price = SinaQuote.getGC001Price(stock_code)
#print stock_code,stock_number,stock_price
account.buy_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'sell':
account.sell_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'sell (bond repurchase)':
#print '===========',row
SinaQuote.setGC001Price(stock_code, stock_number, deal_amount, actual_amount)
stock_price = SinaQuote.getGC001Price(stock_code)
#print stock_code,stock_number,stock_price
account.sell_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'short':
account.short_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'cover':
account.cover_stock(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'direct return':
account.direct_return(deal_date, stock_code, stock_name, stock_number, stock_price)
elif operation == 'stock in':
account.stock_in(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'stock out':
account.stock_out(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation == 'fenji fund split':
stock_price = SinaQuote.getLastClosePrice(stock_code, deal_date)
actual_amount = float(stock_price * stock_number)
account.stock_in(deal_date, stock_code, stock_name, stock_number, stock_price, actual_amount)
elif operation in ['bank in money', 'bank out money', 'bank transfer']:
account.bank_transfer(actual_amount)
elif operation == 'borrow cash':
account.borrow_cash(actual_amount)
elif operation == 'return cash':
account.return_cash(actual_amount)
elif operation == 'pay interest':
account.pay_interest(actual_amount)
elif operation == 'pay tax':
account.pay_tax(actual_amount)
elif operation == 'earn interest':
account.earn_interest(actual_amount)
elif operation == 'earn stock':
account.earn_stock(deal_date, stock_code, stock_name, stock_number)
elif operation == 'earn stock interest':
if stock_number != 0 and actual_amount == 0:
account.earn_stock(deal_date, stock_code, stock_name, stock_number)
elif actual_amount != 0 and stock_number == 0:
account.earn_interest(actual_amount)
else:
print '!!!! error earn stock interest, stock_number %f deal_amount %f' % (stock_number, actual_amount)
elif operation == 'init position':
account.add_init_position(stock_code, stock_name, stock_number)
elif operation == 'init rongquan position':
account.add_init_rongquan_position(stock_code, stock_name, stock_number)
elif operation.startswith('todo:'):
print '==== todo: row %s' % row
elif operation.startswith('pass:'******'!!!! unknown operation %s row %s' % (operation, row)
import SinaQuote
import fundEM
import time
wb = xw.Book(u'D:\\yun\\百度云\\FinExcels\\Funds.xlsm')
sht = wb.sheets['Nav']
src = 0 #0 4
for i in range(3, 1000):
code = sht.range('A{0}'.format(i)).value
if code != None:
print(code)
if src == 0:
tpNav = SinaQuote.GetNav(code[2:])
else:
if i % 3 == 1:
time.sleep(0.5)
tpNav = fundEM.GetNav(code[2:])
sht.range((i, 6 + src)).value = tpNav[4]
if tpNav != None:
sht.range((i, 3 + src)).value = tpNav[1]
sht.range((i, 4 + src)).value = tpNav[2]
sht.range((i, 5 + src)).value = tpNav[3]
else:
break
#wb.save()
#wb.close()
def parse_operation(i, row, df): deal_date = row[u'成交日期'] deal_time = row[u'成交时间'] deal_mark = row[u'买卖标志'] actual_amount = row[u'发生金额'] remain_amount = row[u'剩余金额'] zhaiyao = row[u'摘要'] if re.match(match_borrow_cash, zhaiyao): df.loc[i, u'买卖标志'] = u'融资借款' elif re.match(match_interest_in, zhaiyao): df.loc[i, u'买卖标志'] = u'结息入账' elif re.search(search_bank_in_money, zhaiyao): df.loc[i, u'买卖标志'] = u'银行转入' elif re.search(search_bank_out_money, zhaiyao): df.loc[i, u'买卖标志'] = u'银行转出' elif re.match(match_hongli, zhaiyao) or re.match(match_hongli1, zhaiyao) or re.search(search_shenshu, zhaiyao): df.loc[i, u'买卖标志'] = u'红利' elif re.search(search_tax, zhaiyao): df.loc[i, u'买卖标志'] = u'股息差别税' elif re.match(match_rzrq_in, zhaiyao): result = re.match(match_rzrq_in, zhaiyao) operation = result.group(2) stock_name = result.group(3) stock_code = result.group(4) stock_number = float(result.group(5)) stock_price = float(result.group(6)) df.loc[i, u'买卖标志'] = operation df.loc[i, u'证券代码'] = stock_code df.loc[i, u'证券名称'] = stock_name df.loc[i, u'成交价格'] = stock_price df.loc[i, u'成交数量'] = stock_number if operation not in [u'融资买入', u'信用买入', u'卖券还款', u'信用卖出', u'融券卖出', u'买券还券']: print '!!!error operation %s row:\n%s' % (operation, row) elif re.match(match_new_stock, zhaiyao): result = re.match(match_new_stock, zhaiyao) #print result.groups() operation = result.group(2) stock_code = result.group(4) stock_number = float(result.group(5)) stock_price = float(result.group(6)) stock_name = SinaQuote.getNewStockName(stock_code) df.loc[i, u'买卖标志'] = operation df.loc[i, u'证券代码'] = stock_code df.loc[i, u'证券名称'] = stock_name df.loc[i, u'成交价格'] = stock_price df.loc[i, u'成交数量'] = stock_number if operation not in [u'申购扣款', u'申购还款', u'申购中签款']: print '!!!error operation %s row:\n%s' % (operation, row) elif re.match(match_rzrq_out, zhaiyao): result = re.match(match_rzrq_out, zhaiyao) operation = result.group(1) operation1 = result.group(3) stock_name = result.group(4) stock_code = result.group(5) stock_number = float(result.group(6)) stock_price = float(result.group(7)) df.loc[i, u'买卖标志'] = operation df.loc[i, u'证券代码'] = stock_code df.loc[i, u'证券名称'] = stock_name df.loc[i, u'成交价格'] = stock_price df.loc[i, u'成交数量'] = stock_number #print 'ou' + operation + stock_name + stock_code + stock_number + stock_price if operation not in [u'卖券偿还本金', u'卖出偿还本金', u'卖券偿还融资利息', u'卖券偿还融券费用', u'卖出偿还利息', u'偿还融资其它费用']: print '!!!error operation %s row:\n%s' % (operation, row) elif re.match(match_rzrq_return, zhaiyao): result = re.match(match_rzrq_return, zhaiyao) operation = result.group(1) df.loc[i, u'买卖标志'] = operation if operation not in [u'偿还本金', u'偿还融资利息', u'偿还融券费用']: print '!!!error operation %s row:\n%s' % (operation, row) elif re.match(match_rzrq_pay_interest, zhaiyao): df.loc[i, u'买卖标志'] = u'偿还融资利息' else: print '!!!! unknown row %s' % row