import pandas as pd
from tqdm import tqdm_notebook
from bs4 import BeautifulSoup
import requests
import json
from jqdata import finance, macro

# get hk stock minute data from sina finance
def get_ret(code):
    url = 'https://quotes.sina.cn/hk/api/openapi.php/HK_MinlineService.getMinline?symbol={}&day=1'.format(code)
    html = requests.get(url).text
    data = json.loads(html)['result']['data'][0]
    if len(data) == 0:
        return None
    df = pd.DataFrame(data)
    df.index = df['m']
    start_time = '15:00:00'
    end_time = df.index[-1]
    if start_time not in df.index:
        return None
    df['price'] = df['price'].apply(lambda x: float(x))
    ret = (df.loc[end_time, 'price'] / df.loc[start_time, 'price'] - 1).round(4)
    return {
        'ret': ret,
        'start_time': start_time,
        'end_time': end_time
    }

def get_ah_corr(code, date, N):
    # AH price
    table_name = finance.STK_AH_PRICE_COMP
    q = query(
            table_name.day, table_name.a_price, table_name.h_price
        ).filter(
            table_name.h_code==code, table_name.day<=date
        ).order_by(table_name.day.desc()).limit(N)
    ah_df = finance.run_query(q)
    start = ah_df['day'].tolist()[-1]
    end = ah_df['day'].tolist()[0]
    # forex
    table_name = macro.MAC_RMB_EXCHANGE_RATE
    q = query(
            table_name.day, table_name.bank_reduced_prc
        ).filter(
            table_name.currency_name=='港币', table_name.day>=start, table_name.day<=end
        ).order_by(table_name.day.desc())
    forex_df = macro.run_query(q)
    # calc corr
    ah_df['day'] = ah_df['day'].apply(lambda x: str(x))
    ah_df = pd.merge(ah_df, forex_df, on='day')
    ah_df['h_price_rmb'] = ah_df['h_price'] * ah_df['bank_reduced_prc'] / 100
    corr = round(ah_df[['a_price', 'h_price_rmb']].corr().values[0, 1], 4)
    return corr


date = '2019-9-30'
table_name = finance.STK_AH_PRICE_COMP
q = query(table_name.h_code, table_name.a_code, table_name.name).filter(table_name.day==date)
code_info = finance.run_query(q).to_dict('records')

ret_list = []
for code_dict in tqdm_notebook(code_info):
    code = code_dict['h_code']
    ret_dict = get_ret(code)
    if ret_dict is None:
        continue
    corr_6mon = get_ah_corr(code, date, 120)
    corr_1y = get_ah_corr(code, date, 250)
    
    ret_list.append({
        'code': code, 
        'a_code': code_dict['a_code'],
        'name': code_dict['name'],
        'ret': ret_dict['ret'],
        'start_time': ret_dict['start_time'],
        'end_time': ret_dict['end_time'],
        'corr_6mon': corr_6mon,
        'corr_1y': corr_1y
    })

result_df = pd.DataFrame(ret_list).sort_values(by='ret', ascending=False)
result_df