将msci A股指数成份列表的ISIN转为A股代码..

isin_to_a.py

import pandas as pd
import requests as req

path_to_china_a_constituents = './China_A_constituents_FINAL-Aug2018.xlsx'

df = pd.read_excel(path_to_china_a_constituents, sheet_name='China A Inclusion', skiprows=4)

res = []

for n in df['ISIN']:
    print(n)
    # session = requests_retry_session()
    j = req.get('https://www.morningstar.com/api/v2/search/securities/5/usquote-v2/?q={}'.format(str.lower(n))).json()
    res_ = []
    if j is not None and j['result']['msg'] == 'success':
        for m in j['m']:
            for r in m['r']:
                print("\t", r["OS001"])
                res_.append(r['OS001'])
    # assert len(res_) == 1
    res.append(res_[0])
    
df['A share code'] = res

df.to_csv(out_path, index=False)