【爬虫】获取LedgerX的交易数据

下载数据

import requests
import json
import openpyxl
import datetime
import easygui as g

def get_datetime():

    start = str(input('输入开始日期，形式如‘2018-01-01’：'))
    end = str(input('输入结束日期，形式如‘2018-02-01’：'))
  
    datestart = datetime.datetime.strptime(start,'%Y-%m-%d')
    dateend = datetime.datetime.strptime(end,'%Y-%m-%d')

    data_time_list = []
     
    while datestart <= dateend:
        data_time_list.append(datestart.strftime('%Y-%m-%d'))
        datestart += datetime.timedelta(days=1)
    
    return data_time_list


def get_url(each_day):
    
    url = 'http://data.ledgerx.com/json/{}.json' .format(each_day)
    return url,each_day

def get_res(url):
    
    headers = {
        'Host': 'data.ledgerx.com',
        'Connection': 'keep-alive',
        'Pragma': 'no-cache',
        'Cache-Control': 'no-cache',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36',
        'Accept': r'*/*',
        'Referer': 'http://data.ledgerx.com/',
        'Accept-Encoding': 'gzip, deflate, sdch',
        'Accept-Language': 'zh-CN,zh;q=0.8'
        }
    
    res = requests.get(url,headers=headers)

    return res


def get_data(file_json):
    
    report_data = file_json['report_data']
##  data_num = file_json['date'].split(' ')[0]
    all_num = int(len(report_data))    
    
    contract_id = []
    contract_label_coin = []
    contract_label_exercise_data = []
    contract_label_option = []
    contract_label_strike_price = []
    open_interest = []
    contract_type = []
    last_ask = []
    last_bid = []
    vwap = []
    volume = []

    swap_id = []
    swap_contract_label_coin = []
    swap_contract_label_data = []
    swap_contract_label_exercise_data = []
    swap_open_interest = []
    swap_type = []
    swap_last_ask = []
    swap_last_bid = []
    swap_vwap = []
    swap_volume = []


    for each in range(all_num):
        if report_data[each]['contract_type'] != "day_ahead_swap":
            contract_id.append(report_data[each]['contract_id'])
            contract_label_coin.append(report_data[each]['contract_label'].split(' ')[0])
            contract_label_exercise_data.append(report_data[each]['contract_label'].split(' ')[1])
            contract_label_option.append(report_data[each]['contract_label'].split(' ')[2])
            contract_label_strike_price.append(float(report_data[each]['contract_label'].split(' ')[3][1:].replace(',','')))
            open_interest.append(report_data[each]['open_interest'])
            contract_type.append(report_data[each]['contract_type'])
            last_ask.append((float(report_data[each]['last_ask'])/100) if (report_data[each]['last_ask']) !=  None else 0)
            last_bid.append((float(report_data[each]['last_bid'])/100) if (report_data[each]['last_bid']) !=  None else 0)
            vwap.append(float(report_data[each]['vwap'])/100)
            volume.append(report_data[each]['volume'])
        else:
            swap_id.append(report_data[each]['contract_id'])
            swap_contract_label_coin.append(report_data[each]['contract_label'].split(' ')[2])
            swap_contract_label_data.append(report_data[each]['contract_label'].split(' ')[0])
            swap_contract_label_exercise_data.append(report_data[each]['contract_label'].split(' ')[1])
            swap_open_interest.append(report_data[each]['open_interest'])
            swap_type.append(report_data[each]['contract_type'])
            swap_last_ask.append((float(report_data[each]['last_ask'])/100) if (report_data[each]['last_ask']) !=  None else 0)
            swap_last_bid.append((float(report_data[each]['last_bid'])/100) if (report_data[each]['last_bid']) !=  None else 0)
            swap_vwap.append(float(report_data[each]['vwap'])/100)
            swap_volume.append(report_data[each]['volume'])
            
            
    contrat_num = len(contract_id)
    swap_num = len(swap_id)

    contract_target = []
    swap_target = []

    for i in range(contrat_num):
        contract_target.append([contract_id[i],contract_label_coin[i],contract_label_exercise_data[i],contract_label_option[i],contract_label_strike_price[i],open_interest[i],contract_type[i],last_ask[i],last_bid[i],vwap[i],volume[i]])

    for i in range(swap_num):
        swap_target.append([swap_id[i],swap_contract_label_coin[i],swap_contract_label_data[i],swap_contract_label_exercise_data[i],swap_open_interest[i],swap_type[i],swap_last_ask[i],swap_last_bid[i],swap_vwap[i],swap_volume[i]])

    return contract_target,swap_target


def save_excel_file(contract_target,swap_target,each_day):

    ledger_option_sheet = openpyxl.load_workbook(r'options_days.xlsx')
    
    data_sheet = ledger_option_sheet.create_sheet(index = -1,title = each_day )

    data_sheet['A1'] = '合约ID'
    data_sheet['B1'] = '合约币种'
    data_sheet['C1'] = '行权日期'
    data_sheet['D1'] = '行权方向'
    data_sheet['E1'] = '行权价格'
    data_sheet['F1'] = '未平仓数量'
    data_sheet['G1'] = '合约类型'
    data_sheet['H1'] = '最新卖价'
    data_sheet['I1'] = '最新买价'
    data_sheet['J1'] = '加权平均价'
    data_sheet['K1'] = '成交量'
    
    for each in contract_target:
            data_sheet.append(each )
      
    ledger_option_sheet.save('options_days.xlsx')
          
    ledger_swap_sheet = openpyxl.load_workbook(r'swap_days.xlsx')

    data_sheet = ledger_swap_sheet.create_sheet(index = -1,title = each_day )

    data_sheet['A1'] = '互换ID'
    data_sheet['B1'] = '互换币种'
    data_sheet['C1'] = '成交日期'
    data_sheet['D1'] = '行权日期'
    data_sheet['E1'] = '未平仓数量'
    data_sheet['F1'] = '合约类型'
    data_sheet['G1'] = '最新卖价'
    data_sheet['H1'] = '最新买价'
    data_sheet['I1'] = '加权平均价'
    data_sheet['J1'] = '成交量'

    for each in swap_target:
            data_sheet.append(each)

    ledger_swap_sheet.save('swap_days.xlsx')

def main():
    
    ledger_option_sheet = openpyxl.Workbook()
    ledger_option_sheet.save('options_days.xlsx')
    
    ledger_swap_sheet = openpyxl.Workbook()
    ledger_swap_sheet.save('swap_days.xlsx')

    data_time_list = get_datetime()
    
    print('共将统计%s天的数据。' %str(len(data_time_list)))
    
    for each_day in data_time_list:
        url,each_day = get_url(each_day)
        print(each_day +'数据下载完成')
        res = get_res(url)
        file_json = json.loads(res.text)
        
        contract_target,swap_target = get_data(file_json)

        save_excel_file(contract_target,swap_target,each_day)

    print('完成全部下载')
 

if __name__ == '__main__':
    main()

加总每日成交量

import openpyxl

ledger_options_sheet = openpyxl.load_workbook('options_days.xlsx')

#根据字符串中的内容猜测”合适的数据类型
ledger_options_sheet.guess_types = True

#删除sheet
ledger_options_sheet.remove(ledger_options_sheet['Sheet'])

#创建汇总表
sum_sheet = ledger_options_sheet.create_sheet(index = 0,title ='sum_sheet' )
sum_sheet.append(['日期','call日成交量','put日成交量','日总成交量'])

for i in ledger_options_sheet.sheetnames[1:]:
    wb1 = ledger_options_sheet[i]
    call_turnover = 0
    put_turnover = 0
    for each_rows in wb1.rows:
        if each_rows[3].value == 'Call':
            call_turnover += float(each_rows[10].value)
        elif each_rows[3].value == 'Put':
            put_turnover += float(each_rows[10].value)

    wb1['M1'] = 'call当日成交量'
    wb1['M2'] = call_turnover
    wb1['N1'] =  'put当日成交量'
    wb1['N2'] = put_turnover
    
    sum_sheet.append([wb1.title,call_turnover,put_turnover,call_turnover+put_turnover])
    
ledger_options_sheet.save('options_sum.xlsx')