最近在跟随老师学习期货分析,自告奋勇去整理相关的数据,查找到一个不错的数据库——tushare,然后注册,“买积分”,开始正式的分析之旅。

Tushare:如果需要了解官方说明,可以点击下面的连接。

有了Tableau的分析功底,我的第一个python小程序相对而言比较顺利。从tushare中获得期货的每天交易记录。其中的几个关键地方是:

  • 获得期货的合约列表(pro.fut_basic),然后把它转为一个字典保存;
  • 创建一个列表,依次枚举,只检索满足条件的部分;
  • 使用df = pro.fut_daily 返回日线交易;
  • 根据检索的数量,设置一个睡眠时间,超过100个自动休息1分钟,因为tushare的这个端口每分钟不能超过120次请求;
  • 从网上找了一个方法,alchemyEngine 写入本地数据库。

之后数据导入Tableau进行分析,保密起见,图像不能分享啦。

import tushare as ts
from random import randint
import time
from sqlalchemy import create_engine
import datetime
pro = ts.pro_api('*************************')
bs = pro.fut_basic(exchange='SHFE', fields='ts_code,symbol,exchange,name,fut_code,'
                                                        # 'trade_unit,per_unit,per_unit,quote_unit, quote_unit_desc,'
                                                        # 'd_mode_desc,'  'd_month ,last_ddate'
                                                        'list_date,delist_date' )
# 交易所 SHFE 上期所 DCE 大商所 CFFEX中金所 CZCE郑商所 INE上海国际能源交易所
#RB  螺纹钢
#print(bs)

print(bs.fut_code)  # 打印合约编码,如 I,A,FB等。
print('__totally have ',len(bs),'rows')
#print(bs.index)
#print(bs.columns)  #打印标题
#print(bs['ts_code'])
ts_dict = bs.set_index('ts_code').T.to_dict('list') #转化为字典
#print(ts_dict)  # pring list with 5 items
print('ts_dict type is:',type(ts_dict),ts_dict.items())

RR_code=list()

i=0
for key,value in ts_dict.items():
    ts_code=key     #遍历字典的名称,即ts_code
    i=i+1
    print(i)
    #print(ts_code)
    #print(key, ' value : ', value)
    list=tuple(value)
    ts_code_long=list[0]
    fut_code=list[3] # I ——————short code
    start_date=list[4]
    end_date=list[5]
    print('  list is:',list)

    if fut_code=='RB':
        # I 特矿石,FG玻璃,RB 螺纹钢
        RR_code.append(ts_code_long)
        print(len(RR_code),'running sum of RR_code')  ## attention, how much RR_code have been search
        if len(RR_code)//100 >= 1:
            print('Have a sleep for 60s…… and list will be cleared.')
            time.sleep(60)
            RR_code.clear()  #
            print('sleep over  and search will go on.')
        else:
            df = pro.fut_daily(ts_code=ts_code, start_date=start_date, end_date=end_date)
            #print(df)  # 不打印明细。

            alchemyEngine = create_engine('postgresql+psycopg2://postgres:admin@127.0.0.1/exercise', pool_recycle=3600);
            postgreSQLConnection = alchemyEngine.connect();
            print("Opened database successfully")
            SQLTable = "F_daily_RB";
            try:
                frame = df.to_sql(SQLTable, postgreSQLConnection, if_exists='append');
            except ValueError as vx:
                print(vx)
            except Exception as ex:
                print(ex)
            else:
                print("PostgreSQL Table %s has been created successfully." % SQLTable);
            finally:
                postgreSQLConnection.close();
            # print(ts_code_long)
            # print(fut_code)
            # print(start_date)
            # print(end_date)
            print(ts_code, 'totally',len(df),' detail get success. From ',start_date,'to',end_date)
    else:
        print('       this is' ,ts_code,'; not data you want')
    # print(ts_dict[name])

print(RR_code)
print(len(RR_code),'have done!')





还不错。

第二个程序是获得各个交易所的每日行情,这个相对要难一些,增加了几个循环,就搞定了。

python,真香。

tushare,很好用。

import tushare as ts
from random import randint
import time
from sqlalchemy import create_engine
import datetime
pro = ts.pro_api('fb7******************') //我的token密钥
to_SQLTable = "F_daily"

exchange_list=['SHFE','DCE','CZCE','CFEX']
print(exchange_list)
for i in range(len(exchange_list)):
print(i)
print(exchange_list[i])
exchange = exchange_list[i]

# 交易所 SHFE 上期所 DCE 大商所 CFFEX中金所 CZCE郑商所 INE上海国际能源交易所

bs = pro.fut_basic(exchange=exchange, fields='ts_code,symbol,exchange,name,fut_code,'
# 'trade_unit,per_unit,per_unit,quote_unit, quote_unit_desc,'
# 'd_mode_desc,' 'd_month ,last_ddate'
'list_date,delist_date')

#print(bs.fut_code) # 打印合约编码,如 I,A,FB等。
print('__totally have ',len(bs),'rows')
#print(bs.index)
#print(bs.columns) #打印标题
#print(bs['ts_code'])
#print(bs['fut_code'])
ts_dict = bs.set_index('ts_code').T.to_dict('list') #转化为字典
#print(ts_dict) # pring list with 5 items
print('ts_dict type is:',type(ts_dict),ts_dict.items())

RR_code=[]
R_code=[]

i=0
for key,value in ts_dict.items():
ts_code=key #遍历字典的名称,即ts_code
i=i+1
print(i)
#print(ts_code)
#print(key, ' value : ', value)
list=tuple(value)
ts_code_long=list[0]
fut_code=list[3] # I ——————short code
start_date=list[4]
end_date=list[5]
print(' list is:',list)

RR_code.append(fut_code)
R_code.append(fut_code)
print(len(RR_code),'running sum of RR_code') ## attention, how much RR_code have been search
if len(RR_code)//100 >= 1:
print('Have a sleep for 60s…… and list will be cleared.')
time.sleep(60)
RR_code.clear() #
print('sleep over and search will go on.')
else:
df = pro.fut_daily(ts_code=ts_code, start_date=start_date, end_date=end_date)
#print(df) # 不打印明细。

alchemyEngine = create_engine('postgresql+psycopg2://postgres:admin@127.0.0.1/exercise', pool_recycle=3600);
postgreSQLConnection = alchemyEngine.connect();
print("Opened database successfully")
SQLTable = to_SQLTable ;
try:
frame = df.to_sql(SQLTable, postgreSQLConnection, if_exists='append');
except ValueError as vx:
print(vx)
except Exception as ex:
print(ex)
else:
print("PostgreSQL Table %s has been created successfully." % SQLTable);
finally:
postgreSQLConnection.close();
# print(ts_code_long)
# print(fut_code)
# print(start_date)
# print(end_date)
print(ts_code, 'totally',len(df),' detail get success. From ',start_date,'to',end_date)

print(R_code)
print(len(R_code),'have done!')



特别注意,这里使用了一个列表,除以100计算余数,来确定循环的次数,避免过量请求。

 

Nov 29, 2019