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] ))
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()
