bonds

#!/usr/bin/python
# -*- coding:utf-8 -*-

import datetime
import json
import os
import sys
import time

import requests

import xlrd
import xlwt
from xlutils import copy as xlcopy

xlrd.Book.encoding = "utf-8"

file_name = 'bonds.xls'

url = 'https://www.jisilu.cn/data/cbnew/cb_list/'


holds = {
        113027 : ('华钰转债', 101, 10),
        113514 : ('威帝转债', 99.1, 10),
        113536 : ('三星转债', 101.77, 10),
        123026 : ('中环转债', 110.9, 10),
        128053 : ('尚荣转债', 108.096, 10),
        128070 : ('智能转债', 100.107, 10),
        }


def open_or_create_book(file_name):
    if not os.path.exists(file_name):
        wb = xlwt.Workbook(encoding = 'utf-8')
        return wb

    rb = xlrd.open_workbook(file_name, formatting_info=True)
    wb = xlcopy.copy(rb)
    return wb

def get_or_create_sheet(work_book):
    local_time = time.localtime(time.time())
    time_str = time.strftime("%Y_%m_%d", local_time)
    try:
        index = work_book.sheet_index(time_str)
    except:
        sheet = work_book.add_sheet(time_str)
    else:
        sheet = work_book.get_sheet(index)

    return sheet

def get_bonds(url):
    res = requests.get(url)
    rows = res.json()['rows']
    double_lows = []

    now = datetime.datetime.now()

    for row in rows:
        cur_bond = row['cell']
        cur_id = cur_bond['bond_id']
        name = cur_bond['bond_nm'].encode('utf-8').ljust(12)
        price = float(cur_bond['price'])
        ratio = cur_bond['premium_rt']
        fratio = float(ratio.strip('%'))
        sratio = 'Y' if fratio < 0 else ''
        rating = cur_bond['rating_cd']
        double_low = price + fratio
        iratio = float(cur_bond['increase_rt'].strip('%'))
        convert_dt = datetime.datetime.strptime(cur_bond['convert_dt'], '%Y-%m-%d')
        interval = (convert_dt - now).days
        convert_flag = interval if interval >= 0 else ''
        pre_trading = '---' if cur_bond['price_tips'].encode('utf-8') == '待上市' else ''
        if pre_trading:
            continue
        double_lows.append([int(cur_id), name, price, fratio, double_low, rating, convert_flag, iratio, pre_trading])

    double_lows.sort(key = lambda x:x[4])
    return double_lows

def add_to_sheet(sheet, double_lows):
    # header
    head = ['序号', '代码', '转债名称', '现价', '溢价率', '评分', '评级', '距转股日', '涨幅', '成本', '持仓', '盈亏']
    for j, c in enumerate(head):
        sheet.write(0, j, c.decode('utf-8'))

    alignment = xlwt.Alignment()
    alignment.horz = 0x01
    style = xlwt.XFStyle()
    style.alignment = alignment
    for i, row in enumerate(double_lows):
        if row[0] in holds:
            hold = holds[row[0]]
            row.pop()
            row.append(hold[1])
            row.append(hold[2])
            row.append((row[2] - hold[1]) * hold[2])
        else:
            row.append('')
            row.append('')

        sheet.write(i+1, 0, i+1, style)
        for j, c in enumerate(row):
            if j == 1:
                sheet.write(i+1, j+1, c.decode('utf-8'), style)
            else:
                sheet.write(i+1, j+1, c, style)

def save_workbook(work_book):
    local_time = time.localtime(time.time())
    time_str = time.strftime("%Y_%m_%d", local_time)
    try:
        index = work_book.sheet_index(time_str)
    except:
        index = 0

    work_book.active_sheet = index
    work_book.save(file_name)

if __name__ == '__main__':
    work_book = open_or_create_book(file_name)
    sheet = get_or_create_sheet(work_book)
    double_lows = get_bonds(url)
    add_to_sheet(sheet, double_lows)
    save_workbook(work_book)