def GetDataFrame(wb_file,Sheets_i,N,M):
wb = xw.books(wb_file) #open your workbook
#Specify the value of the cell of the worksheet
Data=wb.sheets[Sheets_i].range((1,1),(N,M)).value
Data=pd.DataFrame(Data)
Data=Data.dropna(how='all',axis=1)
Data=Data.dropna(how='all',axis=0)
return Data
def getConv(file):
#Import conversion table from within excel MQ, set index to PANEL, drop extraneous col
sh = xw.books(file).sheets['CONVERSIONS']
conv=pd.DataFrame(sh.range('A1:Y10').value)
conv.columns = conv.iloc[0]
conv.set_index('PANEL', inplace=True)
conv.drop(['PANEL'], inplace=True)
print(conv)
return conv
def statement_SBI():
print("Statement Module")
wbSt = xw.books("Tally Import Utility V_6.1.1.xlsm")
wsSt = wbSt.sheets['bnkimp']
# Removing old data from Excel Sheet
nRws = int(wsSt.range('l1').value)
wsSt.range((2, 1), (nRws + 1, 12)).value = ""
# Going to Statement page
browser.find_element_by_xpath('//*[@id="navbar"]/div[1]/a[1]').click()
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[2]/div[2]/div[2]/h3/a').click()
browser.find_element_by_xpath('//*[@id="fromDate"]').click()
browser.find_element_by_xpath(
'/html/body/div[3]/div[1]/table/thead/tr[2]/th[1]').click()
tHead = browser.find_element_by_xpath(
'/html/body/div[3]/div[1]/table/tbody')
print(tHead.text)
i = 0
while i == 31:
print("Step1")
datePicker = tHead.text
print(datePicker)
print('Printed')
browser.find_element_by_xpath('//*[@id="toDate"]').click()
browser.find_element_by_xpath('//*[@id="view"]').click()
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/form/div/div[2]/div[4]/ul/li/a').click()
rStatus = browser.find_element_by_id("accountNo").is_selected()
print(rStatus)
browser.implicitly_wait(200)
browser.find_element_by_xpath(
'//*[@id="demo3"]/div/div/div[3]/table/tbody/tr/td[1]/label')
rStatus = browser.find_element_by_id("accountNo").is_selected()
print(rStatus)
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/form/div/div[2]/div[5]/input[1]').click()
browser.find_elements_by_xpath(
'//*[@id="contentDiv"]/div[1]/div[2]/div[6]/div[6]/div[3]/table')
rows = len(
browser.find_elements_by_xpath(
'//*[@id="contentDiv"]/div[1]/div[2]/div[6]/div[6]/div[3]/table/thead/tr'
))
cols = len(
browser.find_elements_by_xpath(
'//*[@id="contentDiv"]/div[1]/div[2]/div[6]/div[6]/div[3]/table/thead/tr/th'
))
for rs in range(1, rows + 1):
for cl in range(1, cols + 1):
data = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div[1]/div[2]/div[6]/div[6]/div[3]/table/thead/tr['
+ str(rs) + ']/td[' + str(cl) + ']')
data = data.text
wsSt.range(rs + 3, cl + 10).value = data
def test_Eta_vector(self):
# Collect data specific to test
file_name = r'Feuille_de_calcul_ALM(Working).xlsm'
sheet_name = 'Smith Wilson extrapolation'
wb = xw.books(file_name)
# Backup existing values
backup_maturities = xw.sheets[sheet_name].range('B16:B50').value
backup_coupon_yields = xw.sheets[sheet_name].range('C16:C50').value
# Values required for testing
maturities = np.asarray([1, 2, 3, 5])
coupon_yields = np.asarray([1.0, 2.0, 2.6, 3.4])
## Replace values for testing
xw.sheets[sheet_name].range('B16:C50').value = None
xw.sheets[sheet_name].range('B16:B50').value = maturities.reshape(4, 1)
xw.sheets[sheet_name].range('C16:C50').value = coupon_yields.reshape(
4, 1)
wb.save()
sm = Smith_Wilson(file_name=file_name, exec=False)
sm.UFR = 0.042
sm.alpha = 0.1
sm.num_instrument = 4
## Execute function
# C matrix
C_output = sm.Cmatrix(sm.swap_rate_lists['Coupon_yields'])
C_output = C_output[:, [0, 1, 2, 3, 4]]
# W matrix
W_output = sm.Wmatrix()
maturities_tab = np.asarray([1, 2, 3, 4, 5])
W_output = W_output[np.ix_(maturities_tab - 1, maturities_tab - 1)]
# Get UFR => mu
sm.get_UFR(0.042)
# eta vector
eta_vector_output, C = sm.get_eta_vector(
sm.swap_rate_lists['Coupon_yields'])
## Test
eta_vector = np.matrix('57.79, -33.5, 11.40, -5.47')
eta_vector_output_rounded = eta_vector_output.round(decimals=0)
eta_vector_rounded = eta_vector.round(decimals=0)
eta_vector_rounded = np.asarray(eta_vector_rounded)
identical_mat = np.allclose(eta_vector_output_rounded,
eta_vector_rounded)
xw.sheets[sheet_name].range('B16:C50').value = None
xw.sheets[sheet_name].range('B16:B50').value = np.asarray(
backup_maturities).reshape(len(backup_maturities), 1)
xw.sheets[sheet_name].range('C16:C50').value = np.asarray(
backup_coupon_yields).reshape(len(backup_coupon_yields), 1)
self.assertTrue(identical_mat)
def select_item(event):
wb1 = (listbox1.get(listbox1.curselection()))
ww = wb1.split('.')[0]
def window_enum_handler(hwnd, resultList):
if win32gui.IsWindowVisible(
hwnd) and win32gui.GetWindowText(hwnd) != '':
resultList.append((hwnd, win32gui.GetWindowText(hwnd)))
def get_app_list(handles=[]):
mlst = []
win32gui.EnumWindows(window_enum_handler, handles)
for handle in handles:
mlst.append(handle)
return mlst
appwindows = dict(get_app_list())
for value in appwindows.values():
if ww in value:
ww2 = value
ww1 = win32gui.FindWindow(None, ww2)
win32gui.SetForegroundWindow(ww1)
if (wb1[-1] != 'b' and wb1[-1] != 'B'):
t = xw.books(wb1)
ttt = t.__str__().replace('<Book ', '').replace('>', '')
kk = '<Sheet ' + ttt
ss = xw.sheets
ss1 = pd.DataFrame(ss)
ss1[0] = ss1[0].astype('str').str[0:-1]
ss1 = ss1[0].tolist()
ss1 = ss1.__str__().replace('Sheets([', '').replace('])', '').replace(kk, '') \
.replace("['", '').replace("']", '').replace("', '", ',')
ss1 = list(ss1.split(','))
listbox2.delete('0', 'end')
for i in ss1:
listbox2.insert(END, i)
else:
t = xw.books(wb1)
listbox2.delete('0', 'end')
with open_workbook(t.fullname) as wb:
for sheetname in wb.sheets:
listbox2.insert(END, sheetname)
def getBasicInfo():
# 获取账户基本信
daily_ = [f for f in getFil(strDat) if '日环比' in f]
wb = xw.books(daily_[0].split('.')[0])
sht = wb.sheets['P4P消费']
cntC = sht['A1'].current_region.columns.count
cntR = sht['A1'].current_region.rows.count
lis = sht[0, :cntC].value
# region, user
region = sht[9:cntR, lis.index('区域')].value
user = sht[9:cntR, lis.index('用户名')].value
port = sht[9:cntR, lis.index('端口')].value
return region, user, port
def test_Cmatrix(self):
# Collect data specific to test
file_name = r'Feuille_de_calcul_ALM(Working).xlsm'
sheet_name = 'Smith Wilson extrapolation'
wb = xw.books(file_name)
# Backup existing values
backup_maturities = xw.sheets[sheet_name].range('B16:B50').value
backup_coupon_yields = xw.sheets[sheet_name].range('C16:C50').value
# Values required for testing
maturities = np.asarray([1, 2, 3, 5])
coupon_yields = np.asarray([1.0, 2.0, 2.6, 3.4])
## Replace values for testing
xw.sheets[sheet_name].range('B16:C50').value = None
xw.sheets[sheet_name].range('B16:B50').value = maturities.reshape(4, 1)
xw.sheets[sheet_name].range('C16:C50').value = coupon_yields.reshape(
4, 1)
wb.save()
sm = Smith_Wilson(file_name=file_name, exec=False)
sm.UFR = 0.042
sm.alpha = 0.1
sm.num_instrument = 4
## Execute function
C_output = sm.Cmatrix(sm.swap_rate_lists['Coupon_yields'])
C_output = C_output[:, [0, 1, 2, 3, 4]]
## Test
C = np.matrix(
'1.01,0,0,0,0; 0.02,1.02,0,0,0; 0.026, 0.026, 1.026, 0, 0; 0.034, 0.034, 0.034, 0.034, 1.034'
)
identical_mat = np.array_equal(C_output.round(decimals=2),
C.round(decimals=2))
xw.sheets[sheet_name].range('B16:C50').value = None
xw.sheets[sheet_name].range('B16:B50').value = np.asarray(
backup_maturities).reshape(len(backup_maturities), 1)
xw.sheets[sheet_name].range('C16:C50').value = np.asarray(
backup_coupon_yields).reshape(len(backup_coupon_yields), 1)
self.assertTrue(identical_mat)
from xlwings import Book, Sheet, Range, Chart
import xlwings as xw
import time
book1 = xw.Book()
sht = xw.books('book1')
#wrt = xw.books('book1').sheets[1]
Symbols = [
'', '', '"NFO_BANKNIFTY18NOVFUT"', '"NFO_BANKNIFTY18OCTFUT"',
'"NFO_BANKNIFTY18SEPFUT"', '"NFO_NIFTY18NOVFUT"', '"NFO_NIFTY18OCTFUT"',
'"NFO_NIFTY18SEPFUT"', '"NSE_HDFCBANK-EQ"', '"NSE_INDUSINDBK-EQ"',
'"NSE_KOTAKBANK-EQ"', '"NSE_AXISBANK-EQ"', '"NSE_ICICIBANK-EQ"',
'"NSE_SBIN-EQ"', '"NSE_CANBK-EQ"', '"NSE_YESBANK-EQ"',
'"NSE_BANKBARODA-EQ"', '"NSE_FEDERALBNK-EQ"', '"NSE_PNB-EQ"',
'"CDS_USDINR18SEPFUT"', '"CDS_USDINR"'
]
x = len(Symbols)
print(x)
for i in range(2, ):
sht.range(
'B' + str(i)
).value = '=RTD("pi.rtdserver", ,' + Symbols[i] + ', "TradingSymbol")'
sht.range(
'C' +
str(i)).value = '=RTD("pi.rtdserver", ,' + Symbols[i] + ', "Last")'
sht.range(
from stat import S_ISREG, ST_CTIME, ST_MODE
import sys
import xlwings as xw
pathwb = r'F:\\Documents\\AI PROJECTS\\exceltest.xlsx'
dir_path = r'F:\\Documents\\test folder'
# get all entries in the directory
entries = (os.path.join(dir_path, file_name)
for file_name in os.listdir(dir_path))
# Get their stats
entries = ((os.stat(path), path) for path in entries)
# leave only regular files, insert creation date
entries = ((stat[ST_CTIME], path) for stat, path in entries
if S_ISREG(stat[ST_MODE]))
# seperate date from the list
datesc = []
filenamesc = []
for x in entries:
datesc.append(x[0])
filenamesc.append(x[1])
# excel
wbstatus = xw.books('status.xlsx')
ws1 = wbstatus.sheets['tab1']
ws2 = wbstatus.sheets['tab2']
ws3 = wbstatus.sheets['tab3']
ws1.range('A2').options(transpose=True).value = datesc
ws1.range('F2').options(transpose=True).value = filenamesc
def main():
'''
主程序
Raises
------
Exception
DESCRIPTION.
Returns
-------
None.
'''
try:
# 输入，检查
strDat = check(input('输入目标月度(如当前为5月: 202005)'))
#
for n, f in enumerate(getFil(strDat)):
print(n, f)
if n == 0:
if os.path.exists(os.path.join(list(os.walk(PATH))[0][0], f)):
xw.Book(os.path.join(list(os.walk(PATH))[0][0], f))
else:
raise FileNotFoundError('文件不存在: %s' % f)
else:
if os.path.exists(os.path.join(list(os.walk(PATH))[1][0], f)):
xw.Book(os.path.join(list(os.walk(PATH))[1][0], f))
else:
raise FileNotFoundError('文件不存在: %s' % f)
#
if 'GP Analysis' in f:
wb1 = xw.books(f)
calc_sz(wb1, strDat)
#
data_sz = getSZ(wb1, strDat)
elif 'P4P & KA Schedules' in f:
wb = xw.books(f)
# rate
## open book rate file
bookRate = list(
filter(lambda x: 'book rate' in x, getFil(strDat)))[0]
xw.Book(os.path.join(list(os.walk(PATH))[1][0], bookRate))
rate = getRate(xw.books(bookRate.split('.')[0]), strDat)
# 换算hk gp
calc_hk(wb, strDat, rate)
#
data_hk = getHK(wb, strDat)
elif 'P4P+KA' in f:
target = f
# update basicInfo
writeTo(strDat)
# update spending
result(data_sz, data_hk, target, strDat)
except ValueError:
wb.close()
wb1.close()
raise
except Exception as e:
print(e)
else:
wb.save()
wb1.save()
def pending_ent():
wb = xw.books("Online Cheque.xlsm")
ws = wb.sheets['current']
nRws = int(ws.range("b1").value)
print("Pending Cheques Module", nRws)
wb = xw.books("Online Cheque.xlsm")
ws = wb.sheets['Current']
nRws = int(ws.range('k1').value)
# Removing old data from Excel Sheet
ws.range((4, 11), (nRws + 4, 18)).value = ""
browser.find_element_by_link_text('Reports').click()
browser.find_element_by_link_text('Pending Transactions').click()
browser.find_element_by_id('startDate').click()
browser.find_element_by_xpath(
'/html/body/div[3]/div[1]/table/thead/tr[2]/th[1]').click()
trows = browser.find_element_by_class_name('datepicker-days')
trows.find_element_by_xpath(
'/html/body/div[3]/div[1]/table/tbody/tr[1]/td[1]').click()
browser.find_element_by_xpath(
'/html/body/div[1]/div[1]/div/div[2]/div/div[1]/form/div/div[3]/input[1]'
).click()
browser.find_element_by_xpath(
'/html/body/div[1]/div[1]/div/div[2]/div/div/div[2]/div/div')
rows = len(
browser.find_elements_by_xpath(
'//*[@id="contentDiv"]/div/div/div[2]/div/div/div[3]/table/tbody/tr'
))
cols = len(
browser.find_elements_by_xpath(
'//*[@id="contentDiv"]/div/div/div[2]/div/div/div[3]/table/thead/tr/th'
))
for rs in range(1, rows + 1):
for cl in range(1, cols + 1):
data = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div/div[2]/div/div/div[3]/table/tbody/tr['
+ str(rs) + ']/td[' + str(cl) + ']')
data = data.text
ws.range(rs + 3, cl + 10).value = data
# for changing date
for rs in range(1, nRws - 1):
chDate = str(ws.range(rs + 3, 14).value)
left = chDate[0:2]
left = int(left)
nRight = chDate[8:10]
nLen = len(chDate)
if int(left) > 12 and nLen < 11:
nYr = chDate[6:10]
nMn = chDate[3:5]
nDt = chDate[0:2]
oUtput = '|' + nDt + '-' + nMn + '-' + nYr
fDate = oUtput[1:11]
ws.range(rs + 3, 14).value = fDate
elif int(nRight) < 13:
nYr = chDate[0:4]
nMn = chDate[5:7]
nDt = chDate[8:10]
oUtput = '|' + nDt + '-' + nMn + '-' + nYr
fDate = oUtput[1:11]
ws.range(rs + 3, 14).value = fDate
print("Done")
def MQtoCSV():
mqVersion = 'MultiQuoter2018v0' #This is the filename for current MQ
#designate sheet to pull from
sh = xw.books(mqVersion).sheets['MULTIQUOTER']
#Pull header data in format of Field: Value:
headerKeys = [
'Customer', 'Substrate', 'Paint', 'Color', 'RibH', 'PanW', 'QQID',
'PriceOR', 'Tax', 'Delivery', 'Rep'
]
headerLocs = [
'C3', 'C5', 'C6', 'C7', 'C8', 'C9', 'D10', 'H2', 'H3', 'H4', 'H5'
]
headerVals = []
for i in range(len(headerLocs)):
temp = sh.range(headerLocs[i]).value
headerVals.append(temp)
headerVals.append(now)
headerKeys.append('timeStamp')
dfHeader = pd.DataFrame({'Field': headerKeys, 'Value': headerVals})
print(dfHeader)
#--Pull item list data in format of line: Qty, Code, Comments, etc...
#--non-pan List:
#Count num of active rows
numRows = 30
for i in range(numRows):
loc = ('B' + str(i + 17))
if pd.isnull(sh.range(loc).value):
numRows = i
break
#initialize temp columns...
itemQty = []
itemCode = []
itemComment = []
itemPriceOR = []
itemColorOR = []
itemMetOR = []
#Run FOR loops to build each column...
for i in range(numRows):
loc = ('B' + str(i + 17))
itemQty.append(sh.range(loc).value)
for i in range(numRows):
loc = ('C' + str(i + 17))
itemCode.append(sh.range(loc).value)
for i in range(numRows):
loc = ('K' + str(i + 17))
itemComment.append(sh.range(loc).value)
for i in range(numRows):
loc = ('M' + str(i + 17))
itemPriceOR.append(sh.range(loc).value)
for i in range(numRows):
loc = ('O' + str(i + 17))
itemColorOR.append(sh.range(loc).value)
for i in range(numRows):
loc = ('Q' + str(i + 17))
itemMetOR.append(sh.range(loc).value)
#Build the DF
dfItems = pd.DataFrame({
'itemQty': itemQty,
'itemCode': itemCode,
'itemComment': itemComment,
'itemPriceOR': itemPriceOR,
'itemColorOR': itemColorOR,
'itemMetOR': itemMetOR
})
#Reorder
dfItems = dfItems[[
'itemQty', 'itemCode', 'itemComment', 'itemPriceOR', 'itemColorOR',
'itemMetOR'
]]
print(dfItems)
#--PAN List:
#Count num of active rows
numRows = 1
for i in range(numRows):
loc = ('B' + str(i + 14))
if pd.isnull(sh.range(loc).value):
numRows = i
break
#initialize temp columns...
panQty = []
panCode = []
panComment = []
panPriceOR = []
panColorOR = []
panPanOR = []
panMetOR = []
#Run FOR loops to build each column...
for i in range(numRows):
loc = ('B' + str(i + 14))
panQty.append(sh.range(loc).value)
for i in range(numRows):
loc = ('C' + str(i + 14))
panCode.append(sh.range(loc).value)
for i in range(numRows):
loc = ('K' + str(i + 14))
panComment.append(sh.range(loc).value)
for i in range(numRows):
loc = ('M' + str(i + 14))
panPriceOR.append(sh.range(loc).value)
for i in range(numRows):
loc = ('O' + str(i + 14))
panColorOR.append(sh.range(loc).value)
for i in range(numRows):
loc = ('Q' + str(i + 14))
panMetOR.append(sh.range(loc).value)
#Build the DF
dfPans = pd.DataFrame({
'panQty': panQty,
'panCode': panCode,
'panComment': panComment,
'panPriceOR': panPriceOR,
'panColorOR': panColorOR,
'panMetOR': panMetOR
})
#Reorder
dfPans = dfPans[[
'panQty', 'panCode', 'panComment', 'panPriceOR', 'panColorOR',
'panMetOR'
]]
print(dfPans)
#CUTLIST from CUTLIST sheet
#Designate appropriate sheet...
shCut = xw.books(mqVersion).sheets['CUTLIST']
#Everything will be run in an A and B list
# A list for first panel data, B list for 2nd
#Count num of active rows
numRowsA = 200
numRowsB = 200
for i in range(numRows):
loc = ('C' + str(i + 9))
if pd.isnull(shCut.range(loc).value):
numRowsA = i
break
for i in range(numRows):
loc = ('I' + str(i + 9))
if pd.isnull(shCut.range(loc).value):
numRowsB = i
break
print(numRowsA)
print(numRowsB)
#NOTE for some reason, this is not working...
#initialize temp columns...
cutNumA = []
cutFtA = []
cutInA = []
cutNumB = []
cutFtB = []
cutInB = []
#Run FOR loops to build each column...
for i in range(numRowsA):
loc = ('C' + str(i + 9))
cutNumA.append(shCut.range(loc).value)
for i in range(numRowsA):
loc = ('D' + str(i + 9))
cutFtA.append(shCut.range(loc).value)
for i in range(numRowsA):
loc = ('E' + str(i + 9))
cutInA.append(shCut.range(loc).value)
#FOR loops for list B
for i in range(numRowsB):
loc = ('I' + str(i + 9))
cutNumB.append(shCut.range(loc).value)
for i in range(numRowsB):
loc = ('J' + str(i + 9))
cutFtB.append(shCut.range(loc).value)
for i in range(numRowsB):
loc = ('K' + str(i + 9))
cutInB.append(shCut.range(loc).value)
#Build the DF. Must use separate DF's for each A and B
#Otherwise, python doesn't seem to like arrays of differing lengths
#probably bad practice, but changing the col names to cutANum to sort
#out better in the csv, but leaving naming convention otherwise unchanged
dfCutA = pd.DataFrame({
'cutANum': cutNumA,
'cutAFt': cutFtA,
'cutAIn': cutInA
})
dfCutB = pd.DataFrame({
'cutBNum': cutNumB,
'cutBFt': cutFtB,
'cutBIn': cutInB
})
#Reorder
dfCutA = dfCutA[['cutANum', 'cutAFt', 'cutAIn']]
dfCutB = dfCutB[['cutBNum', 'cutBFt', 'cutBIn']]
print(dfCutA)
print(dfCutB)
#--write to csv--
#Set filename
cust = sh.range('C2').value
address = sh.range('C11').value
filename = str(cust) + ' - ' + str(address) + ' - ' + str(now) + '.csv'
path = os.path.dirname(sys.argv[0])
path = os.path.dirname(path)
filePath = path + '\\Quotes\\' + filename
print(filePath)
#try to concat DF's together...
dfAll = pd.concat([dfHeader, dfPans, dfItems, dfCutA, dfCutB])
#to csv
dfAll.to_csv(filePath, encoding='utf-8', index=False)
#import win32com.client as win32
#import pandas as pd
from xlwings import constants
start = time.perf_counter()
today = datetime.datetime.today()
# 输入
# 1.N:前1日=1；前2日=0；前3日=-1...依次类推
#
N = 8
# 修改 AVG.日期
Yesterday = today-datetime.timedelta(N)
# 直接引用邮件中文件
wb1 = xw.books('P4P 消费报告2019.06.30.xlsx')
sht1 = wb1.sheets['P4P消费']
sht20 = wb1.sheets['搜索点击消费']
sht30 = wb1.sheets['新产品消费（除原生广告）']
sht40 = wb1.sheets['原生广告']
# 1 读取值
# 1.1 基本信息区域
row10 = 10
row11 = sht1[0, 0].current_region.rows.count
range10 = sht1['A' + str(row10) + ':AC' + str(row11)] # 消费报告基本信息区域
# 1.2 数据区域
# 1.2.1 首列
def getRange(self, wb_file, sheet, rangoin):
wb = xw.books(wb_file)
last_row = wb.sheets[sheet].range(rangoin[0]).end('down').row
rango = wb.sheets[sheet].range(
f"{rangoin[0]}:{rangoin[1]}{last_row}").value
return rango
#%% Import Libs
import pandas as pd
pd.options.mode.chained_assignment = None
import numpy as np
import psycopg2
from psycopg2.extras import RealDictCursor
# from pony.orm import *
import xlwings as xw
import datetime
# def main():
# wb = xw.books('Mockup_port.xlsb')
# port_list = wb.sheets('Portfolios').range('port_list').value
# wb.sheets('Portfolios').range('P1').value = port_list
wb = xw.books('Mockup_port.xlsb')
port_list = tuple(wb.sheets('Portfolios').range('port_list').value)
# wb.sheets('Portfolios').range('P1').value = np.asarray(port_list).reshape(-1,1)
bg_date = wb.sheets('Main').range("B1").value
ed_date = wb.sheets('Main').range("D1").value
conn = psycopg2.connect(user="******",
password="******",
host="10.56.35.166",
port="5433",
database="PortAnalytic-2016-17v14")
# cursor = conn.cursor()
cursor_bos = conn.cursor(cursor_factory=RealDictCursor)
cursor_pa = conn.cursor(cursor_factory=RealDictCursor)
#Nifty50MW.xlsx
#Nifty50MW.xlsx
#Optionswatch.xlsx
from xlwings import Book, Sheet, Range, Chart
import xlwings as xw
import time
App = xw.App()
sht = xw.Book(r'C:\Users\admin\Documents\GitHub\RemotePC-CC\Marketwatch\Sept\watchlist.xlsx').sheets[0]
wrt = xw.books('Book1').sheets[0]
i = 1
wrt.range('A1').value = 'Trading symbol'
wrt.range('B1').value = 'LTP'
wrt.range('C1').value = 'Bid qty'
wrt.range('D1').value = 'Bid rate'
wrt.range('E1').value = 'Ask rate'
wrt.range('F1').value = 'Ask qty'
wrt.range('G1').value = 'LTQ'
wrt.range('H1').value = 'Volume traded today'
wrt.range('I1').value = 'Open interest'
wrt.range('J1').value = 'Total bid qty'
wrt.range('K1').value = 'Total ask qty'
wrt.range('L1').value = 'LTT'
wrt.range('M1').value = 'LUT'
while(1):
def result(sz, hk, f, strDat):
'''
将sz, hk的gp汇总写入指定表单P4P
Parameters
----------
sz : TYPE
DESCRIPTION.
hk : TYPE
DESCRIPTION.
f : TYPE
DESCRIPTION.
strDat : TYPE
DESCRIPTION.
Raises
------
ValueError
DESCRIPTION.检查表头是否符合规范
Returns
-------
None.
'''
try:
wb = xw.books(f)
sht = wb.sheets['P4P']
cntR = sht[1, 0].current_region.rows.count
cntC = sht[1, 0].current_region.columns.count
# 获取表头
lis = sht[1, :cntC].value
col = lis.index('用户名') + 1
# target file
df2 = pd.DataFrame(sht[2:cntR, :col].value, columns=sht[1, :col].value)
# merge sz
df3 = pd.merge(df2,
sz[['账户名称', 'p4p spending /1.06', 'GP_']],
left_on='用户名',
right_on='账户名称',
how='left')
# merge hk
df4 = pd.merge(df3,
hk[['用户名', "+HK Sales(RMB)", 'HK GP(RMB)']],
on='用户名',
how='left')
df4.fillna(0, inplace=True)
# write to
## 参数检查 （表头）
header = [
'Revenue ' + getM(strDat) + ' HK',
'Revenue ' + getM(strDat) + ' SZ', 'GP ' + getM(strDat) + ' HK',
'GP ' + getM(strDat) + ' SZ'
]
if header[0] not in lis:
raise ValueError('检查[%s]表头应为： %s' % (wb.name, header[0]))
if header[1] not in lis:
raise ValueError('检查[%s]表头应为： %s' % (wb.name, header[1]))
if header[2] not in lis:
raise ValueError('检查[%s]表头应为： %s' % (wb.name, header[2]))
if header[3] not in lis:
raise ValueError('检查[%s]表头应为： %s' % (wb.name, header[3]))
## 赋值
sht[2, lis.index('Revenue ' + getM(strDat) + ' HK')].options(
transpose=True).value = df4['+HK Sales(RMB)'].values
sht[2, lis.index('Revenue ' + getM(strDat) + ' SZ')].options(
transpose=True).value = df4['p4p spending /1.06'].values
sht[2, lis.index('GP ' + getM(strDat) + ' HK')].options(
transpose=True).value = df4['HK GP(RMB)'].values
sht[2,
lis.index('GP ' + getM(strDat) +
' SZ')].options(transpose=True).value = df4['GP_'].values
# region
num_region = len(
sht['A2:' +
sht['A2'].end('down').get_address(False, False)].value) + 1
num_user = len(
sht['C2:' + sht['C2'].end('down').get_address(False, False)]) + 1
for r in range(1, num_user - num_region + 1):
if 'cny' in sht['A' + str(num_region + r)].offset(0, 2).value:
sht['A' + str(num_region + r)].value = 'SZ'
else:
sht['A' + str(num_region + r)].value = 'HK'
# save
wb.save()
except ValueError:
wb.close()
raise
def QQtoExcel(name):
print("QQtoxL start") #debug line
mqVersion = 'MultiQuoter2018v0' #This is the filename for current MQ
#Format and split input from csv into item list and pan list
output = QQtoPD(name)
itemsRaw = output[0]
pansRaw = output[1]
#Prep conv and itemsConv
numItems = len(itemsRaw['Item'])
panType = itemsRaw['Item'].values[0]
conv = getConv(mqVersion)
itemsConv = prepInput(itemsRaw, numItems, panType)
linFt = itemsConv.loc[0,'Qty']
#Add screws (round up to 250)
numScrews = linFt * conv.loc[panType.upper(), 'NUMSCREW']
bags = round(numScrews/250) + 1
numScrews = bags * 250
itemsConv.loc[numItems + 1] = [numScrews, 'SCREW', panType,'']
# Reset index and re-calc numItems counter
itemsConv.reset_index(drop=True, inplace=True)
numItems = len(itemsConv['Item'])
#Loop to convert SKUs
for i in range(numItems):
#Error handling because we've dorked with the index, and the numItems counter is off
try:
type = itemsConv.loc[i,'Type'].upper()
item = itemsConv.loc[i,'Item']
itemsConv.loc[i, 'SKU'] = conv.loc[type,item]
except KeyError:
pass
#--Add other items, Z flashing, screws, butyl, etc--
# Check if standing seam (sSeam = 1)
if itemsConv.loc[0,'SKU'] == 'GL' or itemsConv.loc[0,'SKU'] =='GS' or itemsConv.loc[0,'SKU'] =='DL':
sSeam = 1
else:
sSeam = 0
# For sSeam, add Z flashing to match # of HC, RC, EF, SW, and EW
# For sSeam, add PS to match # of PV, TF
# For sSeam, add pancakes for
# Reset index and re-calc numItems counter
itemsConv.reset_index(drop=True, inplace=True)
numItems = len(itemsConv['Item'])
#--Loop to match SKUs...this may not need an actual loop
# Reset index and re-calc numItems counter
itemsConv.reset_index(drop=True, inplace=True)
numItems = len(itemsConv['Item'])
#Loop to convert SKUs
for i in range(numItems):
#Error handling because we've dorked with the index, and the numItems counter is off
try:
type = itemsConv.loc[i,'Type'].upper()
item = itemsConv.loc[i,'Item']
itemsConv.loc[i, 'SKU'] = conv.loc[type,item]
except KeyError:
pass
#Loop to count ZF needs and PS needs and Butyl
numZF = 0
numPS = 0
numButyl = 0
if sSeam == 1:
for i in range(numItems):
temp = itemsConv.loc[i,'Item']
if temp == 'HC' or temp =='RC' or temp== 'EF' or temp == 'SW' or temp == 'EW':
numZF = numZF + itemsConv.loc[i, 'Qty']
if temp == 'PV' or temp == 'TF':
numPS = numPS + itemsConv.loc[i, 'Qty']
itemsConv.loc[numItems + 1] = [numZF,'ZF', panType, '']
itemsConv.loc[numItems + 2] = [numPS,'PS', panType, '']
itemsConv.loc[numItems + 3] = [round((numPS + numZF)/5)+1,'BUTYL', panType, 'BUTYL']
if sSeam == 0:
for i in range(numItems):
temp = itemsConv.loc[i,'Item']
if temp== 'EF' or temp == 'SW' or temp == 'PV' or temp == 'TF':
numButyl = numButyl+ itemsConv.loc[i, 'Qty']
itemsConv.loc[numItems + 1] = [round((numButyl)/5)+1,'BUTYL', panType, 'BUTYL']
#Loop to convert SKUs
#Reset index and get numItems counter
itemsConv.reset_index(drop=True, inplace=True)
numItems = len(itemsConv['Item'])
for i in range(numItems):
#Error handling because we've dorked with the index, and the numItems counter is off
try:
type = itemsConv.loc[i,'Type'].upper()
item = itemsConv.loc[i,'Item']
itemsConv.loc[i, 'SKU'] = conv.loc[type,item]
except KeyError:
pass
print(itemsConv)
# --Write output from ItemsConv to Excel--
# This is the xlwings version
mqVersion = 'MultiQuoter2018v0' #This is the filename for current MQ
# --Write output from ItemsConv to Excel--
#designate sheet to write to
sh = xw.books(mqVersion).sheets['MULTIQUOTER']
#Reset index and get numItems counter
itemsConv.reset_index(drop=True, inplace=True)
numItems = len(itemsConv['Item'])
#Write data from itemsConv to the designated sheet
for i in range(numItems):
tempQty = itemsConv['Qty'].values[i]
tempCode = itemsConv['SKU'].values[i]
if i == 0:
sh.range('B14').value = tempQty
sh.range('C14').value = tempCode
if i > 0:
tempCol = 16 + i
tempQtyLoc = 'B' + str(tempCol)
tempCodeLoc = 'C' + str(tempCol)
sh.range(tempQtyLoc).value = tempQty
sh.range(tempCodeLoc).value=tempCode
#extract QQ number
QQID = droppedFile[:-4] #drop ".csv"
m = re.search(r"[\d]*[-][A-z]{2}[-][\w]*", QQID)
if m:
QQID = m.group(0)
sh.range('D10').value = QQID
#Write pan list to excel
sh = xw.books(mqVersion).sheets['CUTLIST']
for i in range(len(pansRaw)):
tempRow = 9 + i
tempNumLoc = 'C' + str(tempRow)
tempFtLoc = 'D' + str(tempRow)
tempInLoc = 'E' + str(tempRow)
sh.range(tempNumLoc).value = pansRaw['Qty'].values[i]
sh.range(tempFtLoc).value = pansRaw['Feet'].values[i]
sh.range(tempInLoc).value = pansRaw['In'].values[i]
print("QQtoXL complete") #debug line
def writeTo(strDat):
'''
更新汇总表单中的P4P基本信息
Parameters
----------
strDat : TYPE
DESCRIPTION.
Returns
-------
TYPE
DESCRIPTION.
'''
def getBasicInfo():
# 获取账户基本信
daily_ = [f for f in getFil(strDat) if '日环比' in f]
wb = xw.books(daily_[0].split('.')[0])
sht = wb.sheets['P4P消费']
cntC = sht['A1'].current_region.columns.count
cntR = sht['A1'].current_region.rows.count
lis = sht[0, :cntC].value
# region, user
region = sht[9:cntR, lis.index('区域')].value
user = sht[9:cntR, lis.index('用户名')].value
port = sht[9:cntR, lis.index('端口')].value
return region, user, port
def getFilName(region):
lis_fil = [f for f in getFil(strDat) if region in f]
wb = xw.books(lis_fil[0].split('.')[0])
return wb
def dropDuplicates_1():
# 准备数据
#
## sz
wb_sz = getFilName('GP Analysis')
df_sz = getSZ(wb_sz, strDat)
### Series
s_sz = df_sz.loc[(df_sz['p4p spending /1.06'] + df_sz['GP_']) != 0,
'账户名称']
#
## hk
wb_hk = getFilName('P4P & KA')
df_hk = getHK(wb_hk, strDat)
### Series
s_hk = df_hk.loc[(df_hk['+HK Sales(RMB)'] + df_hk['HK GP(RMB)']) != 0,
'用户名']
#
b_user = pd.Series(getBasicInfo()[1])
b_user = b_user.append(s_sz).append(s_hk)
b_user.drop_duplicates(inplace=True)
return b_user.values
def addNull(dic):
# 补空值
max_ = max([len(v) for k, v in dic.items()])
for k, v in dic.items():
if len(v) < max_:
dic[k] = v + [None] * (max_ - len(v))
wb = xw.books('P4P+KA GP_' + strDat)
sht = wb.sheets['P4P']
cntC = sht['A2'].current_region.columns.count
cntR = sht['A2'].current_region.rows.count
header = sht[1, :cntC].value
# region
# 已有数据
old_data = pd.DataFrame(sht[2:cntR,
header.index('区域'):header.index('用户名') +
1].value,
columns=['区域', '端口', '用户名'])
# 新增
dic = {
'区域': getBasicInfo()[0],
'端口': getBasicInfo()[2],
'用户名': dropDuplicates_1()
}
addNull(dic)
# 合并，去重
old_data = old_data.append(pd.DataFrame(dic)).drop_duplicates('用户名')
sht['A3'].value = old_data.values
from xlwings import Book, Sheet, Range, Chart
import xlwings as xw
import time
book1 = xw.Book()
sht = xw.books('book1').sheets[1]
wrt = xw.books('book1').sheets[2]
xx = []
'''
def getQuote(ticker, type):
Range('B1').value = '=RTD("pi.rtdserver", ,"%s", "%s")' % (type, ticker)
time.sleep(2.5)
x = Range('B1').value
return Range('B1').value
getQuote("Volume","MCX_ALUMINI18SEPFUT")
'''
sht.range(
'B1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","TradingSymbol")'
sht.range('C1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","Last")'
sht.range('D1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","BidSize")'
sht.range('E1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","AskSize")'
sht.range('F1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","Bid")'
sht.range('G1').value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","Ask")'
sht.range(
'H1'
).value = '=RTD("pi.rtdserver",,"MCX_ALUMINI18SEPFUT","lastUpdateTime")'
def getFilName(region):
lis_fil = [f for f in getFil(strDat) if region in f]
wb = xw.books(lis_fil[0].split('.')[0])
return wb
import time
wb = xw.Book(r'C:\Users\Yash\Desktop\Marketwatch.xlsx')
x=['']*20
for i in range(2,20):
x[i] = str(wb.sheets['sheet'].range('B'+str(i)).value)
print(x[i])
Sheetname[i] = x[i]
wb.sheets(Sheetname[i])
write1 = xw.books('book1').sheets[2]
write2 = xw.books('book1').sheets[3]
write3 = xw.books('book1').sheets[4]
write4 = xw.books('book1').sheets[5]
write5 = xw.books('book1').sheets[6]
def prepare_chq(trn_pwd):
wb = xw.books("Online Cheque.xlsm")
ws = wb.sheets['current']
ws1 = wb.sheets['xpath']
cls = ws.range("b4:h44").columns.count
trnPass = ws1.range("aa3").value
nRws = int(ws.range("b1").value)
print("Payment Module", nRws)
for rw in range(4, nRws + 4):
print('first for')
bankType = ws.range(rw, 1).value
chqNum = ws.range(rw, 2).value
opartyNam = str(ws.range(rw, 3).value)
chqAmt = int(ws.range(rw, 4).value)
oRemark = str(ws.range(rw, 5).value)
remarkCd = int(ws.range(rw, 8).value)
print(chqNum, chqAmt, oRemark, remarkCd)
if oRemark is None:
print('first if')
nRemark = 'On Account'
else:
nRemark = oRemark
nRemark = nRemark[0:50]
partyNam = opartyNam[0:50]
if chqNum is None and partyNam is not None and bankType == 'OtherBank' and chqAmt > 100:
print('other payment')
browser.find_element_by_xpath(
'//*[@id="navbar"]/div[1]/a[1]').click()
payment = browser.find_element_by_xpath(
'//*[@id="navbar"]/div[2]/a[1]')
payment.click()
othBank = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[2]/div[3]/div[2]/h3/a')
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[2]/div[2]/div[2]/h3/a')
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[4]/div[3]/div[2]/h3/a')
othBank.click()
rTGS = browser.find_element_by_xpath('//*[@id="iconRTGS"]')
nEFT = browser.find_element_by_xpath('//*[@id="iconNEFT"]')
if chqAmt > 199999:
print('rtgs module')
rTGS.click()
browser.find_element_by_xpath(
'//*[@id="debitAmount"]').send_keys(chqAmt)
purposeCode = browser.find_element_by_xpath(
'//*[@id="purposeCode"]')
srchBene = browser.find_element_by_xpath(
'//*[@id="otherTd"]/a')
srchBRB = browser.find_element_by_xpath(
'//*[@id="container"]/div[1]/label[3]/input')
browser.find_element_by_xpath('//*[@id="remarks"]').send_keys(
nRemark)
drp1 = selenium.webdriver.support.ui.Select(purposeCode)
drp1.select_by_value('CORT:Trade settlement Payment')
srchBene.click()
srchBRB.click()
browser.find_element_by_id('srchInput').send_keys(partyNam)
browser.find_element_by_id('srchBtn').click()
browser.find_element_by_id('srdr0').click()
browser.find_element_by_name('acceptTerms').click()
browser.find_element_by_id('confirmSubmit').click()
browser.find_element_by_id('transactionPwd').send_keys(trnPass)
browser.find_element_by_id('confirmSubmit').click()
chNmbr = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/form/div[1]/div[2]/div[3]/div[9]'
).text
ws.range(rw, 2).value = chNmbr[1:11]
print('RTGS Cheque done! for ', partyNam, chqAmt)
else:
def purpsel(i):
switcher = {
0: 'Other Payments',
1: 'Salary Payment',
2: 'Payment towards Invoice Or Bill',
}
return switcher.get(
i, "Invalid Selection Value Please enter 0, 1 or 2")
trasPrpsSel = purpsel(remarkCd)
nEFT.click()
browser.find_element_by_xpath(
'//*[@id="debitAmount"]').send_keys(chqAmt)
browser.find_element_by_xpath(
'//*[@id="creditAmount"]').send_keys(chqAmt)
transPurpose = browser.find_element_by_xpath(
'//*[@id="purposeoftransact"]')
srchBene = browser.find_element_by_xpath(
'//*[@id="otherTd"]/a')
srchBRB = browser.find_element_by_xpath(
'//*[@id="container"]/div[1]/label[3]/input')
drp2 = selenium.webdriver.support.ui.Select(transPurpose)
drp2.select_by_value(trasPrpsSel)
if trasPrpsSel == "Other Payments":
browser.find_element_by_xpath(
'//*[@id="remarks"]').send_keys(nRemark)
srchBene.click()
srchBRB.click()
browser.find_element_by_id('srchInput').send_keys(partyNam)
browser.find_element_by_id('srchBtn').click()
browser.find_element_by_id('srdr0').click()
browser.find_element_by_name('acceptTerms').click()
browser.find_element_by_id('confirmSubmit').click()
browser.find_element_by_id('transactionPwd').send_keys(trnPass)
# browser.find_element_by_name('acceptTerms').click()
browser.find_element_by_id('confirmSubmit').click()
chNmbr = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/form/div[1]/div[2]/div[3]/div[9]'
).text
ws.range(rw, 2).value = chNmbr[1:11]
print('NEFT Cheque done! for ', opartyNam, chqAmt)
elif chqNum is None and partyNam is not None and bankType == 'SameBank':
print('same bank module')
browser.find_element_by_xpath(
'//*[@id="navbar"]/div[1]/a[1]').click()
payment = browser.find_element_by_xpath(
'//*[@id="navbar"]/div[2]/a[1]')
payment.click()
browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[2]/div[3]/div[2]/h3/a')
sameBank = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[2]/div[2]/div[2]/h3/a')
sameBank.click()
browser.implicitly_wait(20)
browser.find_element_by_xpath('//*[@id="clx"]/img').click()
browser.find_element_by_xpath('//*[@id="debitAmount"]').send_keys(
chqAmt)
browser.find_element_by_xpath('//*[@id="creditAmount"]').send_keys(
chqAmt)
browser.find_element_by_xpath(
'//*[@id="fundstransfer"]/div[1]/div[2]/div[8]/div/input'
).send_keys(nRemark)
srchBene = browser.find_element_by_xpath('//*[@id="otherTd"]/a')
srchBRB = browser.find_element_by_xpath(
'//*[@id="container"]/div[1]/label[3]/input')
srchBene.click()
srchBRB.click()
browser.find_element_by_id('srchInput').send_keys(partyNam)
browser.find_element_by_id('srchBtn').click()
browser.find_element_by_id('srdr0').click()
browser.find_element_by_xpath(
'//*[@id="divDefault"]/div[4]/input[2]').click()
browser.find_element_by_id('transactionPwd').send_keys(trnPass)
browser.find_element_by_xpath(
'//*[@id="divDefault"]/div[4]/input[2]').click()
chNmbr = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/form/div[1]/div[2]/div[3]/div[9]').text
ws.range(rw, 2).value = chNmbr[1:11]
print('Transfer Cheque done! for ', partyNam, chqAmt)
elif chqAmt == 100:
print('imps module')
browser.find_element_by_xpath(
'//*[@id="navbar"]/div[1]/a[1]').click()
payment = browser.find_element_by_xpath(
'//*[@id="navbar"]/div[2]/a[1]')
payment.click()
iMPS = browser.find_element_by_xpath(
'//*[@id="contentDiv"]/div/div[4]/div[3]/div[2]/h3/a')
iMPS.click()
browser.find_element_by_xpath('//*[@id="transacType"]').click()
browser.find_element_by_xpath('//*[@id="confirmSubmit"]').click()
browser.switch_to.alert.accept()
browser.find_element_by_xpath('//*[@id="debitAmount"]').send_keys(
chqAmt)
browser.find_element_by_xpath('//*[@id="creditAmount"]').send_keys(
chqAmt)
browser.find_element_by_xpath('//*[@id="debitRemarks"]').send_keys(
nRemark)
browser.find_element_by_xpath('//*[@id="otherTd"]/a').click()
browser.find_element_by_xpath(
'//*[@id="container"]/div[1]/label[3]/input').click()
browser.find_element_by_id('srchInput').send_keys(partyNam)
browser.find_element_by_id('srchBtn').click()
browser.find_element_by_id('srdr0').click()
browser.find_element_by_name('acceptTerms').click()
browser.find_element_by_id('confirmSubmit').click()
browser.find_element_by_id('transactionPwd').send_keys(trnPass)
# browser.find_element_by_name('acceptTerms').click()
browser.find_element_by_id('confirmSubmit').click()
chNmbr = browser.find_element_by_xpath(
'//*[@id="myTabContent"]/form/div/div/div[3]/div[9]').text
ws.range(rw, 2).value = chNmbr[1:11]
print('iIMPS Cheque done! for ', opartyNam, chqAmt)
