# -*- coding: utf-8 -*-
"""
Created on Sat Jun 25 21:14:00 2022

@author : fangfang666
"""
import pandas as pd
from WindPy import *
w.start()

endDate='2022-07-29'
contracts=['IF','IH','IC','IM']
codes=['000300.SH','000016.SH','000905.SH','000852.SH']

out = pd.ExcelWriter('hedging review.xlsx')

for contract, code in zip(contracts, codes):
    data1=w.wsd("%s,%s00.CFE,%s01.CFE,%s02.CFE,%s03.CFE"%(code,contract,contract,contract,contract), "close", "ED-40TD",endDate, "")
    df1 = pd.DataFrame({k:v for k, v in zip(data1.Codes, data1.Data)})
    df1.insert(0, "Date", data1.Times)

    data2=w.wsd("%s,%s00.CFE,%s01.CFE,%s02.CFE,%s03.CFE"%(code,contract,contract,contract,contract), "ltdate_new", "ED-40TD",endDate, "")
    df2 = pd.DataFrame({k:v for k, v in zip(data2.Codes, data2.Data)})
    df2.insert(0, "Date", data2.Times)

    df=pd.merge(df1, df2, on="Date", suffixes=('_close', '_lastday'))

    df["00-01"] = df["%s00.CFE_close"%contract] - df["%s01.CFE_close"%contract]
    df["00-02"] = df["%s00.CFE_close"%contract] - df["%s02.CFE_close"%contract]
    df["00-03"] = df["%s00.CFE_close"%contract] - df["%s03.CFE_close"%contract]

    df["M(00-01)"] = 12 * df["00-01"] / df["%s_close"%code]
    df["M(00-02)"] = 12 * df["00-02"] / df["%s_close"%code]
    df["M(00-03)"] = 12 * df["00-03"] / df["%s_close"%code]

    df["basis00"] = df["%s00.CFE_close"%contract] - df["%s_close"%code]
    df["basis01"] = df["%s01.CFE_close"%contract] - df["%s_close"%code]
    df["basis02"] = df["%s02.CFE_close"%contract] - df["%s_close"%code]
    df["basis03"] = df["%s03.CFE_close"%contract] - df["%s_close"%code]

    df['D-00'] = (df['%s00.CFE_lastday'%contract]-pd.to_datetime(df['Date'])).apply(lambda x: x.days)
    df['D-01'] = (df['%s01.CFE_lastday'%contract]-pd.to_datetime(df['Date'])).apply(lambda x: x.days)
    df['D-02'] = (df['%s02.CFE_lastday'%contract]-pd.to_datetime(df['Date'])).apply(lambda x: x.days)
    df['D-03'] = (df['%s03.CFE_lastday'%contract]-pd.to_datetime(df['Date'])).apply(lambda x: x.days)

    df["A(basis00)"] = df["basis00"]*365/df["%s_close"%code]/df['D-00']
    df["A(basis01)"] = df["basis01"]*365/df["%s_close"%code]/df['D-01']
    df["A(basis02)"] = df["basis02"]*365/df["%s_close"%code]/df['D-02']
    df["A(basis03)"] = df["basis03"]*365/df["%s_close"%code]/df['D-03']

    df.to_excel(out, contract)


out.save()