import datetime
from WindPy import w
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
"""
金融期货
股指
"""
w.start()
conn = admin.conn_mktdata()
conn_gc = admin.conn_gc()
futures_mktdata = admin.table_futures_mktdata() # 中金所金融期货仍放在mktdata
future_contracts = admin.table_future_contracts()
dc = DataCollection()
today = datetime.date.today()
beg_date = datetime.date(2010, 1, 1)
end_date = datetime.date(2013, 12, 31)
# windcode = "000300.SH"
# id_instrument = 'index_300sh'
# windcode = "000016.SH"
# id_instrument = 'index_50sh'
# windcode = "510050.SH"
# id_instrument = 'index_50etf'
# windcode = "000905.SH"
# id_instrument = 'index_500sh'
# data = w.wsd(windcode, "open,high,low,close,volume,amt",beg_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d"), "")
#
# df = pd.DataFrame(data=np.transpose(data.Data), columns=data.Fields)
engine = create_engine('mysql+pymysql://root:[email protected]/mktdata', echo=False)
conn = engine.connect()
metadata = MetaData(engine)
options_mktdata_daily = Table('options_mktdata', metadata, autoload=True)
futures_mktdata_daily = Table('futures_mktdata', metadata, autoload=True)
futures_institution_positions = Table('futures_institution_positions', metadata, autoload=True)
engine_intraday = create_engine('mysql+pymysql://root:[email protected]/mktdata_intraday', echo=False)
conn_intraday = engine_intraday.connect()
metadata_intraday = MetaData(engine_intraday)
equity_index_intraday = Table('equity_index_mktdata_intraday', metadata_intraday, autoload=True)
option_mktdata_intraday = Table('option_mktdata_intraday', metadata_intraday, autoload=True)
option_tick_data = Table('option_tick_data', metadata_intraday, autoload=True)
future_tick_data = Table('future_tick_data', metadata_intraday, autoload=True)
index_daily = Table('indexes_mktdata', metadata, autoload=True)
dc = DataCollection()
#####################################################################################
beg_date = datetime.date(2018, 1, 19)
end_date = datetime.date(2018, 1, 19)
date_range = w.tdays(beg_date, end_date, "").Data[0]
for dt in date_range:
date = dt.date()
dt_date = date.strftime("%Y-%m-%d")
print(dt_date)
#####################table_options_mktdata_daily######################################
# wind 50ETF option
res = options_mktdata_daily.select((options_mktdata_daily.c.dt_date == dt_date)
& (options_mktdata_daily.c.name_code == '50etf')).execute()
if res.rowcount == 0:
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
"""
Option data from Wind
"""
w.start()
conn = admin.conn_gc()
conn_intraday = admin.conn_intraday()
options_mktdata_daily = admin.table_options_mktdata()
option_contracts = admin.table_option_contracts()
dc = DataCollection()
today = datetime.date.today()
# beg_date = datetime.date(2015, 1, 1)
beg_date = datetime.date(2018, 12, 1).strftime("%Y-%m-%d")
end_date = datetime.date(2019, 1, 8).strftime("%Y-%m-%d")
date_range = w.tdays(beg_date, end_date, "").Data[0]
date_range = sorted(date_range, reverse=True)
# dt_date = dt.strftime("%Y-%m-%d")
# code_list = [
# # 'M1707',
# # 'M1708',
# # 'M1709',
# # 'M1711',
# # 'M1712',
from sqlalchemy import create_engine, MetaData, Table, Column, TIMESTAMP
import datetime
import pandas as pd
from WindPy import w
import os
from data_access.db_data_collection import DataCollection
w.start()
engine = create_engine('mysql+pymysql://root:[email protected]/mktdata',
echo=False)
conn = engine.connect()
metadata = MetaData(engine)
option_contracts = Table('option_contracts', metadata, autoload=True)
future_contracts = Table('future_contracts', metadata, autoload=True)
dc = DataCollection()
##############################option_contracts####################################################
db_datas = dc.table_option_contracts().wind_options_50etf()
for db_data in db_datas:
id_instrument = db_data['id_instrument']
res = option_contracts.select(
option_contracts.c.id_instrument == id_instrument).execute()
if res.rowcount > 0: continue
try:
conn.execute(option_contracts.insert(), db_data)
print('option_contracts -- inserted into data base succefully')
except Exception as e:
print(e)
print(db_data)
continue
from data_access.db_data_collection import DataCollection
w.start()
# date = datetime.date(2018, 4, 4)
# dt_date = date.strftime("%Y-%m-%d")
# print(dt_date)
engine = create_engine('mysql+pymysql://root:[email protected]/mktdata',
echo=False)
conn = engine.connect()
metadata = MetaData(engine)
stocks = Table('stocks', metadata, autoload=True)
stocks_mktdata = Table('stocks_mktdata', metadata, autoload=True)
dc = DataCollection()
##################### CONTRACT INFO #########################################
# option_contracts
# db_datas = dc.table_stocks().wind_A_shares_total(dt_date)
# for db_data in db_datas:
# try:
# conn.execute(stocks.insert(), db_data)
# except Exception as e:
# print(e)
# continue
# for dt in date_range:
# print(dt)
metadata = MetaData(engine)
options_mktdata_daily = Table('options_mktdata', metadata, autoload=True)
futures_mktdata_daily = Table('futures_mktdata', metadata, autoload=True)
futures_institution_positions = Table('futures_institution_positions', metadata, autoload=True)
engine_intraday = create_engine('mysql+pymysql://root:[email protected]/mktdata_intraday', echo=False)
conn_intraday = engine_intraday.connect()
metadata_intraday = MetaData(engine_intraday)
equity_index_intraday = Table('equity_index_mktdata_intraday', metadata_intraday, autoload=True)
option_mktdata_intraday = Table('option_mktdata_intraday', metadata_intraday, autoload=True)
option_tick_data = Table('option_tick_data', metadata_intraday, autoload=True)
future_tick_data = Table('future_tick_data', metadata_intraday, autoload=True)
index_daily = Table('indexes_mktdata', metadata, autoload=True)
option_contracts = Table('option_contracts', metadata, autoload=True)
future_contracts = Table('future_contracts', metadata, autoload=True)
dc = DataCollection()
#####################CONTRACT INFO#########################################
## option_contracts
db_datas = dc.table_option_contracts().wind_options_50etf()
for db_data in db_datas:
id_instrument = db_data['id_instrument']
res = option_contracts.select(option_contracts.c.id_instrument == id_instrument).execute()
if res.rowcount > 0: continue
try:
conn.execute(option_contracts.insert(), db_data)
print('option_contracts -- inserted into data base succefully')
except Exception as e:
print(e)
print(db_data)
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
"""
SH300 TR
wind data
"""
w.start()
conn = admin.conn_mktdata()
conn_intraday = admin.conn_intraday()
index_daily = admin.table_indexes_mktdata()
dc = DataCollection()
today = datetime.date.today()
beg_date = datetime.date(2015, 1, 1)
# beg_date = datetime.date(2018, 9, 1)
end_date = datetime.date.today()
date_range = w.tdays(beg_date, end_date, "").Data[0]
date_range = sorted(date_range, reverse=True)
for dt in date_range:
windcode = "H00300.CSI"
id_instrument = 'index_300sh_total_return'
datestr = dt.strftime("%Y-%m-%d")
db_data = dc.table_index().wind_data_index(windcode, datestr,
id_instrument)
# print(db_data)
from WindPy import w
from Utilities import admin_write_util as admin
from data_access import spider_api_czce as czce
from data_access import spider_api_sfe as sfe
from data_access.db_data_collection import DataCollection
from data_access.deprecated import spider_api_dce as dce
w.start()
# date = datetime.date.today()
date = datetime.date(2019, 1, 25)
dt_date = date.strftime("%Y-%m-%d")
conn = admin.conn_mktdata()
futures_mktdata_daily = admin.table_futures_mktdata()
dc = DataCollection()
################################## Future Mktdata from Exchange Website ###################################
# dce futures data
ds = dce.spider_mktdata_day(date, date, 0)
for dt in ds.keys():
data = ds[dt]
db_data = dc.table_futures().dce_day(dt, data)
if len(db_data) == 0: continue
try:
conn.execute(futures_mktdata_daily.insert(), db_data)
print('dce futures data 0 -- inserted into data base succefully')
except Exception as e:
print(dt)
print(e)
continue
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
"""
Option data from Wind
"""
w.start()
conn = admin.conn_gc()
conn_intraday = admin.conn_intraday()
options_mktdata_daily = admin.table_options_mktdata_gc()
option_contracts = admin.table_option_contracts()
dc = DataCollection()
today = datetime.date.today()
# beg_date = datetime.date(2015, 1, 1)
beg_date = datetime.date(2017, 1, 1).strftime("%Y-%m-%d")
end_date = datetime.date.today().strftime("%Y-%m-%d")
date_range = w.tdays(beg_date, end_date, "").Data[0]
date_range = sorted(date_range, reverse=True)
# dt_date = dt.strftime("%Y-%m-%d")
# code_list = [
# # 'M1707',
# # 'M1708',
# # 'M1709',
# # 'M1711',
# # 'M1712',
print(dt_date)
conn = admin.conn_mktdata()
conn_intraday = admin.conn_intraday()
conn_gc = admin.conn_gc()
options_mktdata_daily = admin.table_options_mktdata()
futures_mktdata = admin.table_futures_mktdata() # 中金所金融期货仍放在mktdata
futures_mktdata_gc = admin.table_futures_mktdata_gc() # 商品期货放在golden_copy
option_contracts = admin.table_option_contracts()
future_contracts = admin.table_future_contracts() # 只包含中金所金融期货
index_daily = admin.table_indexes_mktdata()
equity_index_intraday = admin.table_index_mktdata_intraday()
option_mktdata_intraday = admin.table_option_mktdata_intraday()
dc = DataCollection()
####################################### INDEXES ##################################################
#### 1. Get Indexes Market Data.
dict_windcode_id = {"000905.SH":"index_500sh",
"000300.SH":'index_300sh',
"000016.SH":'index_50sh',
"510050.SH":'index_50etf'}
for windcode in dict_windcode_id.keys():
id_instrument = dict_windcode_id[windcode]
data = w.wsd(windcode, "open,high,low,close,volume,amt",dt_date, dt_date, "")
df = pd.DataFrame(data=np.transpose(data.Data), columns=data.Fields)
df.loc[:,Util.DT_DATE] = data.Times
df.loc[:,Util.ID_INSTRUMENT] = id_instrument
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
w.start()
conn = admin.conn_mktdata()
options_mktdata_daily = admin.table_options_mktdata()
futures_mktdata_daily = admin.table_futures_mktdata()
futures_institution_positions = admin.table_futures_institution_positions()
conn_intraday = admin.conn_intraday()
equity_index_intraday = admin.table_index_mktdata_intraday()
option_mktdata_intraday = admin.table_option_mktdata_intraday()
index_daily = admin.table_indexes_mktdata()
dc = DataCollection()
#####################################################################################
# beg_date = datetime.date(2004, 1, 1)
# end_date = datetime.date(2004, 12, 31)
################################### 低频宏观经济指标 ##################################################
macro_dict = {
"M5792266" : ["industrial_capacity_utilization_quarter", "工业产能利用率：当季值"],
"M0061571" : ["industrial_added_value_month_chain_ratio", "工业增加值：环比：季调"],
"M0000545" : ["industrial_added_value_month_yoy_ratio", "工业增加值：当月：同比"],
"M0000011" : ["industrial_added_value_month_cumulative_yoy_ratio", "工业增加值：累计同比"],
"M0041963" : ["industrial_added_value_year", "全部工业增加值：亿元"],
"M0041964" : ["industrial_added_value_year_yoy_ratio", "全部工业增加值：同比（年）"],
"M9003295" : ["industrial_added_value_above_scale_year_yoy_ratio", "规模以上工业增加值：同比（年）"],
"M0000272" : ["fixed_asset_investment_month", "固定资产投资完成额：累计值:亿元（月）"],
"M0000273" : ["fixed_asset_investment_month_cumulative_yoy_ratio", "固定资产投资完成额：累计同比（月）"],
from WindPy import w
from Utilities import admin_write_util as admin
from data_access.db_data_collection import DataCollection
"""
batch insert option daily data
"""
w.start()
conn = admin.conn_gc()
options_mktdata_daily = admin.table_options_mktdata()
dc = DataCollection()
today = datetime.date.today()
beg_date = datetime.date(2019, 1, 1)
end_date = datetime.date.today()
date_range = w.tdays(beg_date, end_date, "").Data[0]
date_range = sorted(date_range, reverse=True)
for dt in date_range:
db_data = dc.table_options().wind_cu_option(dt.strftime("%Y-%m-%d"))
# db_data = dc.table_options().wind_data_50etf_option(dt.strftime("%Y-%m-%d"))
if len(db_data) == 0: print('no data')
for res in db_data:
try:
conn.execute(options_mktdata_daily.insert(), res)
print(res)