def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
dates = todo_dates_buy(read)
symbols = industry_constituents.symbols(read)
for i, date in enumerate(dates):
for j, symbol in enumerate(symbols):
print('\rBuilding power.buy: %.2f%%' % ((i * len(symbols) + j) * 100 / (len(dates) * len(symbols))), end='')
if symbol not in positions(read, date):
if p250(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p250', None))
elif p120(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p120', None))
elif p60(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p60', None))
elif p20(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p20', None))
elif p10(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p10', None))
elif p5(read, symbol, date):
cursor.execute('INSERT INTO power(bob, eob, symbol, bpol, spol) VALUES (%s,%s,%s,%s,%s)',
(date, None, symbol, 'p5', None))
dicts = sellables(read, date)
for j, dict in enumerate(dicts):
if s250(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's250', dict['bob'], dict['symbol']))
elif s120(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's120', dict['bob'], dict['symbol']))
elif s60(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's60', dict['bob'], dict['symbol']))
elif s20(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's20', dict['bob'], dict['symbol']))
elif s10(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's10', dict['bob'], dict['symbol']))
elif s5(read, dict['symbol'], date):
cursor.execute('UPDATE power SET eob = %s, spol = %s WHERE bob=%s AND symbol=%s',
(date, 's5', dict['bob'], dict['symbol']))
cursor.close()
write.close()
read.close()
print('\rBuilding power: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding trading_derivative_indicator: %.2f%%' %
(i * 100 / len(infos)),
end='')
last_eob = max_end(read, info['symbol'])
start = info[
'listed_date'] if last_eob is None else last_eob + timedelta(
days=1)
while True:
end = start + timedelta(days=1000)
fundamentals = get_fundamentals(
table='trading_derivative_indicator',
symbols=info['symbol'],
start_date=start,
end_date=end,
fields=
'DY,EV,EVEBITDA,EVPS,LYDY,NEGOTIABLEMV,PB,PCLFY,PCTTM,PELFY,PELFYNPAAEI,PEMRQ,PEMRQNPAAEI,PETTM,PETTMNPAAEI,PSLFY,PSMRQ,PSTTM,TCLOSE,TOTMKTCAP,TURNRATE,TOTAL_SHARE,FLOW_SHARE'
)
fundamentals.sort(key=lambda x: x['end_date'])
for fundamental in fundamentals:
if fundamental['symbol'] == info['symbol']:
cursor.execute(
'INSERT INTO trading_derivative_indicator (symbol, pub, "end", dy, ev, evebitda, evps, lydy, negotiablemv, pb, pclfy, pcttm, pelfy, pelfynpaaei, pemrq, pemrqnpaaei, pettm, pettmnpaaei, pslfy, psmrq, psttm, tclose, totmktcap, turnrate, total_share, flow_share) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(fundamental['symbol'], fundamental['pub_date'],
fundamental['end_date'], fundamental['DY'],
fundamental['EV'], fundamental['EVEBITDA'],
fundamental['EVPS'], fundamental['LYDY'],
fundamental['NEGOTIABLEMV'], fundamental['PB'],
fundamental['PCLFY'], fundamental['PCTTM'],
fundamental['PELFY'], fundamental['PELFYNPAAEI'],
fundamental['PEMRQ'], fundamental['PEMRQNPAAEI'],
fundamental['PETTM'], fundamental['PETTMNPAAEI'],
fundamental['PSLFY'], fundamental['PSMRQ'],
fundamental['PSTTM'], fundamental['TCLOSE'],
fundamental['TOTMKTCAP'], fundamental['TURNRATE'],
fundamental['TOTAL_SHARE'],
fundamental['FLOW_SHARE']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding trading_derivative_indicator: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding history_instruments: %.2f%%' %
(i * 100 / len(infos)),
end='')
last_eob = max_date(read, info['symbol'])
start = info[
'listed_date'] if last_eob is None else last_eob + timedelta(
days=1)
while True:
end = start + timedelta(days=1000)
instruments = get_history_instruments(
symbols=info['symbol'],
fields=
'symbol,trade_date,sec_level,is_suspended,multiplier,margin_ratio,settle_price,pre_settle,position,pre_close,upper_limit,lower_limit,adj_factor',
start_date=start,
end_date=end)
instruments.sort(key=lambda x: x['trade_date'])
for instrument in instruments:
cursor.execute(
'INSERT INTO history_instruments (symbol, trade_date, sec_level, is_suspended, multiplier, margin_ratio, settle_price, pre_settle, position, pre_close, upper_limit, lower_limit, adj_factor) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(instrument['symbol'], instrument['trade_date'],
instrument['sec_level'], instrument['is_suspended'],
instrument['multiplier'], instrument['margin_ratio'],
instrument['settle_price'], instrument['pre_settle'],
instrument['position'], instrument['pre_close'],
instrument['upper_limit'], instrument['lower_limit'],
instrument['adj_factor']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding history_instruments: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding history_1d: %.2f%%' % (i * 100 / len(infos)), end='')
last_eob = max_eob(read, info['symbol'])
start = info[
'listed_date'] if last_eob is None else last_eob + timedelta(
days=1)
while True:
end = start + timedelta(days=1000)
bars = history(symbol=info['symbol'],
frequency='1d',
start_time=start,
end_time=end,
fields='bob,eob,open,close,high,low,amount,volume',
skip_suspended=False,
fill_missing='Last',
adjust=ADJUST_NONE)
bars.sort(key=lambda x: x['eob'])
for bar in bars:
cursor.execute(
'INSERT INTO history_1d (symbol, bob, eob, open, close, high, low, amount, volume) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(info['symbol'], bar['bob'], bar['eob'], bar['open'],
bar['close'], bar['high'], bar['low'], bar['amount'],
bar['volume']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding history_1d: Finish')
return count
def full_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
cursor.execute('DELETE FROM instrumentinfos')
infos = get_instrumentinfos(
exchanges=['SHSE', 'SZSE'],
sec_types=[1],
fields='symbol,sec_name,sec_abbr,price_tick,listed_date,delisted_date')
for i, info in enumerate(infos):
print('\rBuilding instrumentinfos: %.2f%%' % (i * 100 / len(infos)),
end='')
cursor.execute(
'INSERT INTO instrumentinfos (symbol, sec_name, sec_abbr, price_tick, listed_date, delisted_date) VALUES (%s,%s,%s,%s,%s,%s)',
(info['symbol'], info['sec_name'], info['sec_abbr'],
info['price_tick'], info['listed_date'], info['delisted_date']))
cursor.close()
write.close()
read.close()
print('\rBuilding instrumentinfos: Finish')
return count
def full_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
cursor.execute('DELETE FROM industry_constituents')
codes = industries.codes(read)
for i, code in enumerate(codes):
print('\rBuilding industry_constituents: %.2f%%' %
(i * 100 / len(codes)),
end='')
symbols = get_industry(code)
for symbol in symbols:
if not exists(read, code, symbol):
cursor.execute(
'INSERT INTO industry_constituents (code, symbol) VALUES (%s,%s)',
(code, symbol))
count += 1
cursor.close()
write.close()
read.close()
print('\rBuilding industry_constituents: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
dates = todo_dates(read)
symbols = industry_constituents.symbols(read)
for i, date in enumerate(dates):
for j, symbol in enumerate(symbols):
print('\rBuilding finance: %.2f%%' %
((i * len(symbols) + j) * 100 / (len(dates) * len(symbols))),
end='')
if symbol not in list(
map(lambda x: x['symbol'], candicates(
read, date))) and fin(read, symbol, date):
cursor.execute(
'INSERT INTO finance(symbol, date) VALUES (%s,%s)',
(symbol, date))
count += 1
cursor.close()
write.close()
read.close()
print('\rBuilding finance: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding income_statement: %.2f%%' % (i * 100 / len(infos)),
end='')
last_eob = max_end(read, info['symbol'])
start = info[
'listed_date'] if last_eob is None else last_eob + timedelta(
days=1)
while True:
end = start + timedelta(days=100000)
fundamentals = get_fundamentals(
table='income_statement',
symbols=info['symbol'],
start_date=start,
end_date=end,
fields=
'NETPROFIT,TOTPROFIT,PERPROFIT,BIZTOTINCO,BIZINCO,INTEINCO,EARNPREM,POUNINCO,REALSALE,OTHERBIZINCO,BIZTOTCOST,BIZCOST,INTEEXPE,POUNEXPE,REALSALECOST,DEVEEXPE,SURRGOLD,COMPNETEXPE,CONTRESS,POLIDIVIEXPE,REINEXPE,OTHERBIZCOST,BIZTAX,SALESEXPE,MANAEXPE,FINEXPE,ASSEIMPALOSS,VALUECHGLOSS,INVEINCO,ASSOINVEPROF,EXCHGGAIN,FUTULOSS,CUSTINCO,SUBSIDYINCOME,OTHERBIZPROF,NONOREVE,NONOEXPE,NONCASSETSDISL,INCOTAXEXPE,UNREINVELOSS,PARENETP,MERGEFORMNETPROF,MINYSHARRIGH,BASICEPS,DILUTEDEPS,AVAIDISTPROF,AVAIDISTSHAREPROF,CINAFORSFV,CINALIBOFRBP,COMDIVPAYBABLE,COMPINCOAMT,CPLTOHINCO,EARLYUNDIPROF,EPOCFHGL,EQUMCPOTHINCO,EUQMICOLOTHINCO,EXTRARBIRESE,EXTSTAFFFUND,HTMCCINAFORSFV,LEGALSURP,MAINBIZCOST,MAINBIZINCO,MINYSHARINCO,MINYSHARINCOAMT,NCPOTHINCO,NONCASSETSDISI,OTHERCOMPINCO,OTHERCPLTOHINCO,OTHERREASADJU,PARECOMPINCO,PARECOMPINCOAMT,PEXTCCAPIFD,PEXTCDEVEFD,PPROFRETUINVE,PREFSTOCKDIVI,PSUPPFLOWCAPI,RUNDISPROBYRREGCAP,STATEXTRUNDI,TDIFFFORCUR,TRUSTLOSS,TURNCAPSDIVI,UNDIPROF'
)
fundamentals.sort(key=lambda x: x['end_date'])
for fundamental in fundamentals:
if fundamental['symbol'] == info['symbol']:
cursor.execute(
'INSERT INTO income_statement (symbol, pub, "end", netprofit, totprofit, perprofit, biztotinco, bizinco, inteinco, earnprem, pouninco, realsale, otherbizinco, biztotcost, bizcost, inteexpe, pounexpe, realsalecost, deveexpe, surrgold, compnetexpe, contress, polidiviexpe, reinexpe, otherbizcost, biztax, salesexpe, manaexpe, finexpe, asseimpaloss, valuechgloss, inveinco, assoinveprof, exchggain, futuloss, custinco, subsidyincome, otherbizprof, nonoreve, nonoexpe, noncassetsdisl, incotaxexpe, unreinveloss, parenetp, mergeformnetprof, minysharrigh, basiceps, dilutedeps, avaidistprof, avaidistshareprof, cinaforsfv, cinalibofrbp, comdivpaybable, compincoamt, cpltohinco, earlyundiprof, epocfhgl, equmcpothinco, euqmicolothinco, extrarbirese, extstafffund, htmccinaforsfv, legalsurp, mainbizcost, mainbizinco, minysharinco, minysharincoamt, ncpothinco, noncassetsdisi, othercompinco, othercpltohinco, otherreasadju, parecompinco, parecompincoamt, pextccapifd, pextcdevefd, pprofretuinve, prefstockdivi, psuppflowcapi, rundisprobyrregcap, statextrundi, tdiffforcur, trustloss, turncapsdivi, undiprof) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(fundamental['symbol'], fundamental['pub_date'],
fundamental['end_date'], fundamental['NETPROFIT'],
fundamental['TOTPROFIT'], fundamental['PERPROFIT'],
fundamental['BIZTOTINCO'], fundamental['BIZINCO'],
fundamental['INTEINCO'], fundamental['EARNPREM'],
fundamental['POUNINCO'], fundamental['REALSALE'],
fundamental['OTHERBIZINCO'],
fundamental['BIZTOTCOST'], fundamental['BIZCOST'],
fundamental['INTEEXPE'], fundamental['POUNEXPE'],
fundamental['REALSALECOST'], fundamental['DEVEEXPE'],
fundamental['SURRGOLD'], fundamental['COMPNETEXPE'],
fundamental['CONTRESS'], fundamental['POLIDIVIEXPE'],
fundamental['REINEXPE'], fundamental['OTHERBIZCOST'],
fundamental['BIZTAX'], fundamental['SALESEXPE'],
fundamental['MANAEXPE'], fundamental['FINEXPE'],
fundamental['ASSEIMPALOSS'],
fundamental['VALUECHGLOSS'], fundamental['INVEINCO'],
fundamental['ASSOINVEPROF'], fundamental['EXCHGGAIN'],
fundamental['FUTULOSS'], fundamental['CUSTINCO'],
fundamental['SUBSIDYINCOME'],
fundamental['OTHERBIZPROF'], fundamental['NONOREVE'],
fundamental['NONOEXPE'],
fundamental['NONCASSETSDISL'],
fundamental['INCOTAXEXPE'],
fundamental['UNREINVELOSS'], fundamental['PARENETP'],
fundamental['MERGEFORMNETPROF'],
fundamental['MINYSHARRIGH'], fundamental['BASICEPS'],
fundamental['DILUTEDEPS'],
fundamental['AVAIDISTPROF'],
fundamental['AVAIDISTSHAREPROF'],
fundamental['CINAFORSFV'],
fundamental['CINALIBOFRBP'],
fundamental['COMDIVPAYBABLE'],
fundamental['COMPINCOAMT'], fundamental['CPLTOHINCO'],
fundamental['EARLYUNDIPROF'], fundamental['EPOCFHGL'],
fundamental['EQUMCPOTHINCO'],
fundamental['EUQMICOLOTHINCO'],
fundamental['EXTRARBIRESE'],
fundamental['EXTSTAFFFUND'],
fundamental['HTMCCINAFORSFV'],
fundamental['LEGALSURP'], fundamental['MAINBIZCOST'],
fundamental['MAINBIZINCO'],
fundamental['MINYSHARINCO'],
fundamental['MINYSHARINCOAMT'],
fundamental['NCPOTHINCO'],
fundamental['NONCASSETSDISI'],
fundamental['OTHERCOMPINCO'],
fundamental['OTHERCPLTOHINCO'],
fundamental['OTHERREASADJU'],
fundamental['PARECOMPINCO'],
fundamental['PARECOMPINCOAMT'],
fundamental['PEXTCCAPIFD'],
fundamental['PEXTCDEVEFD'],
fundamental['PPROFRETUINVE'],
fundamental['PREFSTOCKDIVI'],
fundamental['PSUPPFLOWCAPI'],
fundamental['RUNDISPROBYRREGCAP'],
fundamental['STATEXTRUNDI'],
fundamental['TDIFFFORCUR'], fundamental['TRUSTLOSS'],
fundamental['TURNCAPSDIVI'], fundamental['UNDIPROF']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding income_statement: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding balance_sheet: %.2f%%' % (i * 100 / len(infos)), end='')
last_eob = max_end(read, info['symbol'])
start = info['listed_date'] if last_eob is None else last_eob + timedelta(days=1)
while True:
end = start + timedelta(days=100000)
fundamentals = get_fundamentals(
table='balance_sheet',
symbols=info['symbol'],
start_date=start,
end_date=end,
fields='TOTASSET,TOTCURRASSET,CURFDS,SETTRESEDEPO,PLAC,TRADFINASSET,DERIFINAASSET,NOTESRECE,ACCORECE,PREP,PREMRECE,REINRECE,REINCONTRESE,INTERECE,DIVIDRECE,OTHERRECE,EXPOTAXREBARECE,SUBSRECE,MARGRECE,INTELRECE,PURCRESAASSET,INVE,PREPEXPE,UNSEG,ACCHELDFORS,EXPINONCURRASSET,OTHERCURRASSE,TOTALNONCASSETS,LENDANDLOAN,AVAISELLASSE,HOLDINVEDUE,LONGRECE,EQUIINVE,OTHERLONGINVE,INVEPROP,FIXEDASSEIMMO,ACCUDEPR,FIXEDASSENETW,FIXEDASSEIMPA,FIXEDASSENET,CONSPROG,ENGIMATE,FIXEDASSECLEA,PRODASSE,COMASSE,HYDRASSET,INTAASSET,DEVEEXPE,GOODWILL,LOGPREPEXPE,TRADSHARTRAD,DEFETAXASSET,OTHERNONCASSE,TOTLIABSHAREQUI,TOTLIAB,TOTALCURRLIAB,SHORTTERMBORR,CENBANKBORR,DEPOSIT,FDSBORR,TRADFINLIAB,DERILIAB,NOTESPAYA,ACCOPAYA,ADVAPAYM,SELLREPASSE,COPEPOUN,COPEWORKERSAL,TAXESPAYA,INTEPAYA,DIVIPAYA,OTHERFEEPAYA,MARGREQU,INTELPAY,OTHERPAY,ACCREXPE,EXPECURRLIAB,COPEWITHREINRECE,INSUCONTRESE,WARLIABRESE,ACTITRADSECU,ACTIUNDESECU,INTETICKSETT,DOMETICKSETT,DEFEREVE,SHORTTERMBDSPAYA,LIABHELDFORS,DUENONCLIAB,OTHERCURRELIABI,TOTALNONCLIAB,LONGBORR,BDSPAYA,BDSPAYAPERBOND,BDSPAYAPREST,LONGPAYA,LCOPEWORKERSAL,SPECPAYA,EXPENONCLIAB,LONGDEFEINCO,DEFEINCOTAXLIAB,OTHERNONCLIABI,RIGHAGGR,PAIDINCAPI,OTHEQUIN,PREST,PERBOND,CAPISURP,TREASTK,OCL,SPECRESE,RESE,GENERISKRESE,UNREINVELOSS,UNDIPROF,TOPAYCASHDIVI,CURTRANDIFF,PARESHARRIGH,MINYSHARRIGH')
fundamentals.sort(key=lambda x: x['end_date'])
for fundamental in fundamentals:
if fundamental['symbol'] == info['symbol']:
cursor.execute(
'INSERT INTO balance_sheet (symbol, pub, "end", totasset, totcurrasset, curfds, settresedepo, plac, tradfinasset, derifinaasset, notesrece, accorece, prep, premrece, reinrece, reincontrese, interece, dividrece, otherrece, expotaxrebarece, subsrece, margrece, intelrece, purcresaasset, inve, prepexpe, unseg, accheldfors, expinoncurrasset, othercurrasse, totalnoncassets, lendandloan, avaisellasse, holdinvedue, longrece, equiinve, otherlonginve, inveprop, fixedasseimmo, accudepr, fixedassenetw, fixedasseimpa, fixedassenet, consprog, engimate, fixedasseclea, prodasse, comasse, hydrasset, intaasset, deveexpe, goodwill, logprepexpe, tradshartrad, defetaxasset, othernoncasse, totliabsharequi, totliab, totalcurrliab, shorttermborr, cenbankborr, deposit, fdsborr, tradfinliab, deriliab, notespaya, accopaya, advapaym, sellrepasse, copepoun, copeworkersal, taxespaya, intepaya, divipaya, otherfeepaya, margrequ, intelpay, otherpay, accrexpe, expecurrliab, copewithreinrece, insucontrese, warliabrese, actitradsecu, actiundesecu, inteticksett, dometicksett, defereve, shorttermbdspaya, liabheldfors, duenoncliab, othercurreliabi, totalnoncliab, longborr, bdspaya, bdspayaperbond, bdspayaprest, longpaya, lcopeworkersal, specpaya, expenoncliab, longdefeinco, defeincotaxliab, othernoncliabi, righaggr, paidincapi, othequin, prest, perbond, capisurp, treastk, ocl, specrese, rese, generiskrese, unreinveloss, undiprof, topaycashdivi, curtrandiff, paresharrigh, minysharrigh) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(fundamental['symbol'],
fundamental['pub_date'],
fundamental['end_date'],
fundamental['TOTASSET'],
fundamental['TOTCURRASSET'],
fundamental['CURFDS'],
fundamental['SETTRESEDEPO'],
fundamental['PLAC'],
fundamental['TRADFINASSET'],
fundamental['DERIFINAASSET'],
fundamental['NOTESRECE'],
fundamental['ACCORECE'],
fundamental['PREP'],
fundamental['PREMRECE'],
fundamental['REINRECE'],
fundamental['REINCONTRESE'],
fundamental['INTERECE'],
fundamental['DIVIDRECE'],
fundamental['OTHERRECE'],
fundamental['EXPOTAXREBARECE'],
fundamental['SUBSRECE'],
fundamental['MARGRECE'],
fundamental['INTELRECE'],
fundamental['PURCRESAASSET'],
fundamental['INVE'],
fundamental['PREPEXPE'],
fundamental['UNSEG'],
fundamental['ACCHELDFORS'],
fundamental['EXPINONCURRASSET'],
fundamental['OTHERCURRASSE'],
fundamental['TOTALNONCASSETS'],
fundamental['LENDANDLOAN'],
fundamental['AVAISELLASSE'],
fundamental['HOLDINVEDUE'],
fundamental['LONGRECE'],
fundamental['EQUIINVE'],
fundamental['OTHERLONGINVE'],
fundamental['INVEPROP'],
fundamental['FIXEDASSEIMMO'],
fundamental['ACCUDEPR'],
fundamental['FIXEDASSENETW'],
fundamental['FIXEDASSEIMPA'],
fundamental['FIXEDASSENET'],
fundamental['CONSPROG'],
fundamental['ENGIMATE'],
fundamental['FIXEDASSECLEA'],
fundamental['PRODASSE'],
fundamental['COMASSE'],
fundamental['HYDRASSET'],
fundamental['INTAASSET'],
fundamental['DEVEEXPE'],
fundamental['GOODWILL'],
fundamental['LOGPREPEXPE'],
fundamental['TRADSHARTRAD'],
fundamental['DEFETAXASSET'],
fundamental['OTHERNONCASSE'],
fundamental['TOTLIABSHAREQUI'],
fundamental['TOTLIAB'],
fundamental['TOTALCURRLIAB'],
fundamental['SHORTTERMBORR'],
fundamental['CENBANKBORR'],
fundamental['DEPOSIT'],
fundamental['FDSBORR'],
fundamental['TRADFINLIAB'],
fundamental['DERILIAB'],
fundamental['NOTESPAYA'],
fundamental['ACCOPAYA'],
fundamental['ADVAPAYM'],
fundamental['SELLREPASSE'],
fundamental['COPEPOUN'],
fundamental['COPEWORKERSAL'],
fundamental['TAXESPAYA'],
fundamental['INTEPAYA'],
fundamental['DIVIPAYA'],
fundamental['OTHERFEEPAYA'],
fundamental['MARGREQU'],
fundamental['INTELPAY'],
fundamental['OTHERPAY'],
fundamental['ACCREXPE'],
fundamental['EXPECURRLIAB'],
fundamental['COPEWITHREINRECE'],
fundamental['INSUCONTRESE'],
fundamental['WARLIABRESE'],
fundamental['ACTITRADSECU'],
fundamental['ACTIUNDESECU'],
fundamental['INTETICKSETT'],
fundamental['DOMETICKSETT'],
fundamental['DEFEREVE'],
fundamental['SHORTTERMBDSPAYA'],
fundamental['LIABHELDFORS'],
fundamental['DUENONCLIAB'],
fundamental['OTHERCURRELIABI'],
fundamental['TOTALNONCLIAB'],
fundamental['LONGBORR'],
fundamental['BDSPAYA'],
fundamental['BDSPAYAPERBOND'],
fundamental['BDSPAYAPREST'],
fundamental['LONGPAYA'],
fundamental['LCOPEWORKERSAL'],
fundamental['SPECPAYA'],
fundamental['EXPENONCLIAB'],
fundamental['LONGDEFEINCO'],
fundamental['DEFEINCOTAXLIAB'],
fundamental['OTHERNONCLIABI'],
fundamental['RIGHAGGR'],
fundamental['PAIDINCAPI'],
fundamental['OTHEQUIN'],
fundamental['PREST'],
fundamental['PERBOND'],
fundamental['CAPISURP'],
fundamental['TREASTK'],
fundamental['OCL'],
fundamental['SPECRESE'],
fundamental['RESE'],
fundamental['GENERISKRESE'],
fundamental['UNREINVELOSS'],
fundamental['UNDIPROF'],
fundamental['TOPAYCASHDIVI'],
fundamental['CURTRANDIFF'],
fundamental['PARESHARRIGH'],
fundamental['MINYSHARRIGH']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding balance_sheet: Finish')
return count
def increment_build():
count = 0
read = read_conn()
write = write_conn()
cursor = write.cursor()
infos = instrumentinfos.infos(read)
for i, info in enumerate(infos):
print('\rBuilding cashflow_statement: %.2f%%' % (i * 100 / len(infos)),
end='')
last_eob = max_end(read, info['symbol'])
start = info[
'listed_date'] if last_eob is None else last_eob + timedelta(
days=1)
while True:
end = start + timedelta(days=100000)
fundamentals = get_fundamentals(
table='cashflow_statement',
symbols=info['symbol'],
start_date=start,
end_date=end,
fields=
'CASHNETR,MANANETR,BIZCASHINFL,LABORGETCASH,DEPONETR,BANKLOANNETINCR,FININSTNETR,INSPREMCASH,INSNETC,SAVINETR,DISPTRADNETINCR,CHARINTECASH,FDSBORRNETR,REPNETINCR,TAXREFD,RECEOTHERBIZCASH,BIZCASHOUTF,LABOPAYC,LOANSNETR,TRADEPAYMNETR,PAYCOMPGOLD,PAYINTECASH,PAYDIVICASH,PAYWORKCASH,PAYTAX,PAYACTICASH,INVNETCASHFLOW,INVCASHINFL,WITHINVGETCASH,INVERETUGETCASH,FIXEDASSETNETC,SUBSNETC,RECEINVCASH,REDUCASHPLED,INVCASHOUTF,ACQUASSETCASH,INVPAYC,LOANNETR,SUBSPAYNETCASH,PAYINVECASH,INCRCASHPLED,FINNETCFLOW,FINCASHINFL,INVRECECASH,SUBSRECECASH,RECEFROMLOAN,ISSBDRECECASH,RECEFINCASH,FINCASHOUTF,DEBTPAYCASH,DIVIPROFPAYCASH,SUBSPAYDIVID,FINRELACASH,CHGEXCHGCHGS,INICASHBALA,FINALCASHBALA,NETPROFIT,MINYSHARRIGH,UNREINVELOSS,ASSEIMPA,ASSEDEPR,INTAASSEAMOR,LONGDEFEEXPENAMOR,PREPEXPEDECR,ACCREXPEINCR,DISPFIXEDASSETLOSS,FIXEDASSESCRALOSS,VALUECHGLOSS,DEFEINCOINCR,ESTIDEBTS,FINEXPE,INVELOSS,DEFETAXASSETDECR,DEFETAXLIABINCR,INVEREDU,RECEREDU,PAYAINCR,UNSEPARACHG,UNFIPARACHG,OTHER,BIZNETCFLOW,DEBTINTOCAPI,EXPICONVBD,FINFIXEDASSET,CASHFINALBALA,CASHOPENBALA,EQUFINALBALA,EQUOPENBALA,CASHNETI,REALESTADEP'
)
fundamentals.sort(key=lambda x: x['end_date'])
for fundamental in fundamentals:
if fundamental['symbol'] == info['symbol']:
cursor.execute(
'INSERT INTO cashflow_statement (symbol, pub, "end", cashnetr, mananetr, bizcashinfl, laborgetcash, deponetr, bankloannetincr, fininstnetr, inspremcash, insnetc, savinetr, disptradnetincr, charintecash, fdsborrnetr, repnetincr, taxrefd, receotherbizcash, bizcashoutf, labopayc, loansnetr, tradepaymnetr, paycompgold, payintecash, paydivicash, payworkcash, paytax, payacticash, invnetcashflow, invcashinfl, withinvgetcash, inveretugetcash, fixedassetnetc, subsnetc, receinvcash, reducashpled, invcashoutf, acquassetcash, invpayc, loannetr, subspaynetcash, payinvecash, incrcashpled, finnetcflow, fincashinfl, invrececash, subsrececash, recefromloan, issbdrececash, recefincash, fincashoutf, debtpaycash, diviprofpaycash, subspaydivid, finrelacash, chgexchgchgs, inicashbala, finalcashbala, netprofit, minysharrigh, unreinveloss, asseimpa, assedepr, intaasseamor, longdefeexpenamor, prepexpedecr, accrexpeincr, dispfixedassetloss, fixedassescraloss, valuechgloss, defeincoincr, estidebts, finexpe, inveloss, defetaxassetdecr, defetaxliabincr, inveredu, receredu, payaincr, unseparachg, unfiparachg, other, biznetcflow, debtintocapi, expiconvbd, finfixedasset, cashfinalbala, cashopenbala, equfinalbala, equopenbala, cashneti, realestadep) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(fundamental['symbol'], fundamental['pub_date'],
fundamental['end_date'], fundamental['CASHNETR'],
fundamental['MANANETR'], fundamental['BIZCASHINFL'],
fundamental['LABORGETCASH'], fundamental['DEPONETR'],
fundamental['BANKLOANNETINCR'],
fundamental['FININSTNETR'],
fundamental['INSPREMCASH'], fundamental['INSNETC'],
fundamental['SAVINETR'],
fundamental['DISPTRADNETINCR'],
fundamental['CHARINTECASH'],
fundamental['FDSBORRNETR'], fundamental['REPNETINCR'],
fundamental['TAXREFD'],
fundamental['RECEOTHERBIZCASH'],
fundamental['BIZCASHOUTF'], fundamental['LABOPAYC'],
fundamental['LOANSNETR'],
fundamental['TRADEPAYMNETR'],
fundamental['PAYCOMPGOLD'],
fundamental['PAYINTECASH'],
fundamental['PAYDIVICASH'],
fundamental['PAYWORKCASH'], fundamental['PAYTAX'],
fundamental['PAYACTICASH'],
fundamental['INVNETCASHFLOW'],
fundamental['INVCASHINFL'],
fundamental['WITHINVGETCASH'],
fundamental['INVERETUGETCASH'],
fundamental['FIXEDASSETNETC'],
fundamental['SUBSNETC'], fundamental['RECEINVCASH'],
fundamental['REDUCASHPLED'],
fundamental['INVCASHOUTF'],
fundamental['ACQUASSETCASH'], fundamental['INVPAYC'],
fundamental['LOANNETR'],
fundamental['SUBSPAYNETCASH'],
fundamental['PAYINVECASH'],
fundamental['INCRCASHPLED'],
fundamental['FINNETCFLOW'],
fundamental['FINCASHINFL'],
fundamental['INVRECECASH'],
fundamental['SUBSRECECASH'],
fundamental['RECEFROMLOAN'],
fundamental['ISSBDRECECASH'],
fundamental['RECEFINCASH'],
fundamental['FINCASHOUTF'],
fundamental['DEBTPAYCASH'],
fundamental['DIVIPROFPAYCASH'],
fundamental['SUBSPAYDIVID'],
fundamental['FINRELACASH'],
fundamental['CHGEXCHGCHGS'],
fundamental['INICASHBALA'],
fundamental['FINALCASHBALA'],
fundamental['NETPROFIT'], fundamental['MINYSHARRIGH'],
fundamental['UNREINVELOSS'], fundamental['ASSEIMPA'],
fundamental['ASSEDEPR'], fundamental['INTAASSEAMOR'],
fundamental['LONGDEFEEXPENAMOR'],
fundamental['PREPEXPEDECR'],
fundamental['ACCREXPEINCR'],
fundamental['DISPFIXEDASSETLOSS'],
fundamental['FIXEDASSESCRALOSS'],
fundamental['VALUECHGLOSS'],
fundamental['DEFEINCOINCR'], fundamental['ESTIDEBTS'],
fundamental['FINEXPE'], fundamental['INVELOSS'],
fundamental['DEFETAXASSETDECR'],
fundamental['DEFETAXLIABINCR'],
fundamental['INVEREDU'], fundamental['RECEREDU'],
fundamental['PAYAINCR'], fundamental['UNSEPARACHG'],
fundamental['UNFIPARACHG'], fundamental['OTHER'],
fundamental['BIZNETCFLOW'],
fundamental['DEBTINTOCAPI'],
fundamental['EXPICONVBD'],
fundamental['FINFIXEDASSET'],
fundamental['CASHFINALBALA'],
fundamental['CASHOPENBALA'],
fundamental['EQUFINALBALA'],
fundamental['EQUOPENBALA'], fundamental['CASHNETI'],
fundamental['REALESTADEP']))
count += 1
if end < min(info['delisted_date'], today().date()):
start = end + timedelta(days=1)
else:
break
cursor.close()
write.close()
read.close()
print('\rBuilding cashflow_statement: Finish')
return count