<jupyter_start><jupyter_text>Сбор данных по источникам коэффициента CAPE  Содержание1&nbsp;&nbsp;Сбор данных по CAPE1.1&nbsp;&nbsp;Данные с  Barclays1.2&nbsp;&nbsp;Данные с  Researchaffiliates1.3&nbsp;&nbsp;Объединение данных по CAPE2&nbsp;&nbsp;Сбор тикеров ETF3&nbsp;&nbsp;Сбор данных по коэфциенту P/B4&nbsp;&nbsp;Расчет долей портфеля5&nbsp;&nbsp;Экспорт данных в google drive<jupyter_code># подключение библиотек:
import pandas as pd # работа с таблицами
import numpy as np # работа с таблицами
import datetime # работа с датами
import requests # http запросы
from bs4 import BeautifulSoup
import gspread  # работа с google таблицами 
from gspread_dataframe import set_with_dataframe
import time
from selenium import webdriver
import math
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Сбор данных по CAPE  Данные с  Barclays<jupyter_code>barclays_url = 'https://indices.barclays/file.app?action=shared&path=shiller/cape.csv' # ссылка на файл с CAPE
barclays_data =  pd.read_csv(barclays_url, sep=",") # сохраним файл в датафрейм для последующей обработки
barclays_data # вывод данных
barclays_data_len = len(barclays_data) # посчитаем количество строк
barclays_last_string = barclays_data[barclays_data_len - 1:] # возьмем последнюю строку (актуальные данные)
barclays_last_string
barclays_date = barclays_last_string['Date'].values[0] # сохраним дату крайнего исследования
barclays_result = barclays_last_string.T # транспонируем строку
barclays_result.head() # выведем первые 5 строк из того что получилось
barclays_result.reset_index(inplace=True) # обновим таблицу, чтобы индексами таблицы были целые числа, а не названия стран
barclays_result
barclays_result = barclays_result.drop(barclays_result.index[[0]]) # удалим нулевую строку, которая содержит дату
barclays_result
barclays_column_name = 'Barclays CAPE ' + barclays_date # создаем название для столбца
barclays_result.set_axis (['country', barclays_column_name], axis=1, inplace=True) # заменяем названия столбцов
barclays_result<jupyter_output><empty_output><jupyter_text>Поменяем UK на United Kingdom<jupyter_code>barclays_result['country'] = barclays_result['country'].apply(lambda x : x if x != 'UK' else 'United Kingdom')
barclays_result['country'] = barclays_result['country'].apply(lambda x : x if x != 'USA' else 'US Large')
barclays_result['country'] = barclays_result['country'].apply(lambda x : x if x != 'Korea' else 'South Korea')<jupyter_output><empty_output><jupyter_text>Данные с  Researchaffiliates<jupyter_code># функция переводит дату в формат url
def date_to_url_format(date, eu_format=False):
  delimeter = '%2F' # разделитель дат в url запросах

  if eu_format: # от этого параметра меняется порядок дня и месяца
    return date.strftime("%d") +  delimeter  + date.strftime("%m") + delimeter + date.strftime("%Y")

  return date.strftime("%m") +  delimeter  + date.strftime("%d") + delimeter + date.strftime("%Y")<jupyter_output><empty_output><jupyter_text>Найдем дату исследования<jupyter_code>data_date_str = ''

try:
    driver = webdriver.Firefox(
        executable_path=r'C:\Users\User\AppData\Local\python\geckodriver.exe'
    )
    driver.get('https://interactive.researchaffiliates.com/asset-allocation#!/?currency=USD&email=undefined&expanded=tertiary&group=core&model=ER&models=ER&scale=LINEAR&terms=REAL&tertiary=shiller-pe-cape-ratio-box&type=Equities')
    
    time.sleep(5)

    soup                = BeautifulSoup(driver.page_source, "lxml")
    date_div = soup.find('div',attrs={'class': 'settings-toolbar__asof'})
    # price_book_selector = '#fundPortfolioData > section > div:nth-child(4) > span'
    # data                = soup.select(price_book_selector)        
    data_date_str = date_div.text.strip()[-10:]
    print(data_date_str)

except Exception as ex:
    print(ex)
finally:
    driver.close()
    driver.quit()<jupyter_output>07/31/2021<jupyter_text>Подготовим переменные, которые требуются в запросе к Researchaffiliates<jupyter_code>one_day         = datetime.timedelta(days=1) # сдвиг на один день
today           = datetime.date.today() # сегодня
first_day_month = today.replace(day=1) # первый день текущего месяца
#last_month      = first_day_month - one_day # последний день предыдущего месяца

researchaffiliates_URL_start = 'https://download.researchaffiliates.com/download/asset-allocation/excel?webAccountId=36b8786b-0fef-491f-a785-4fe24574fd5d&currency=USD&terms=REAL&model=ER'


date_str = 'Fri, 24 Apr 2021 16:22:54 +0000'
research_date_raw =  datetime.datetime.strptime(data_date_str, '%m/%d/%Y')

research_date   = '&dataDate=' + date_to_url_format(research_date_raw)

#research_date   = '&dataDate=05%2F31%2F2021'
try_run = 0


print('Параметр "дата исследования"', research_date)
researchaffiliates_URL = ''
# ищем правильный URL методом перебора

for try_run in range(0, 95): # диапазон дат - от начала до 95 дней вперед

   #run_url  = '&runDate=' + date_to_url_format(today - one_day * try_run) # ставим очередную дату в URL
   run_url  = '&runDate=' + date_to_url_format(research_date_raw + one_day * try_run) # ставим очередную дату в URL
   test_URL = researchaffiliates_URL_start + research_date + run_url # собираем URL 
   resp     = requests.get(test_URL) # запрос по URL

   if resp.status_code == 200: # Если файл с отчетом нашёлся
     researchaffiliates_URL = test_URL # сохраняем URL 
     break

if researchaffiliates_URL == '':
   print('Правильный URL для Researchaffiliates не найден') 

researchaffiliates_URL<jupyter_output><empty_output><jupyter_text>Делаем запрос к Researchaffiliates с подобранными параметрами<jupyter_code># скачиваем Excel файл, выбираем страницу Shiller.PE
researchaffiliates_data =  pd.read_excel(researchaffiliates_URL, sheet_name='Shiller.PE')
researchaffiliates_data<jupyter_output><empty_output><jupyter_text>Заготовим переменные, в которые потом положим адреса необходимых колонок и ячеек<jupyter_code>market_column = '' # сюда потом сохраним название колонки "рынок страны"
index_column  = '' # сюда потом сохраним название колонки индекса, который предлагает Research Affiliates
cape_column   = '' # сюда потом сохраним название колонки с CAPE
start_string  = 0  # сюда потом сохраним номер строки, с которой начинаются данные (заголовки строк не учитываем)
# пройдем в цикле по всем колонкам таблицы
for column in researchaffiliates_data.columns:
  
  market_in_column = len(researchaffiliates_data[researchaffiliates_data[column] =='Market'].index.values)
  if market_in_column: # если ячейка в колонке содержит слово Market    
    market_column = column # значит, это колонка с названием страны
    start_string = researchaffiliates_data[researchaffiliates_data[column] =='Market'].index[0] + 1 # со следующей ячейки начинаются данные

  index_in_column = len(researchaffiliates_data[researchaffiliates_data[column] =='Index'].index.values)
  if index_in_column: # если ячейка в колонке содержит слово Index    
    index_column = column # значит, это колонка с названием индекса
  
  cape_in_column = len(researchaffiliates_data[researchaffiliates_data[column] =='Current'].index.values)
  if cape_in_column:  # если ячейка в колонке содержит слово Current 
    cape_column = column # значит, это колонка с CAPE
  
researchaffiliates_result = researchaffiliates_data.loc[start_string:, [market_column, index_column, cape_column]] # вырежем из таблицы необходимые данные
researchaffiliates_result
researchaffiliates_result.set_axis(['country', 'RA_Index', 'RA_CAPE'], axis=1, inplace=True) # переименуем названия колонок
researchaffiliates_result.dropna(subset = ['RA_CAPE'], inplace = True) # Удаляем строки, если в них не заполнен CAPE
researchaffiliates_result
researchaffiliates_result['RA_CAPE'] = researchaffiliates_result['RA_CAPE'].astype('float') # преобразуем данные по CAPE из строки в число
researchaffiliates_result = researchaffiliates_result.round(2) # округлим до двух знаков после запятой<jupyter_output><empty_output><jupyter_text>Объединение данных по CAPEОбъеденим данные с источников по колонке 'country'. Тип объединения outer - сохранение данных с всех таблиц (если в одной из таблиц нет данных по стране, то получим пустую ячейку).<jupyter_code>final_data = barclays_result.merge(researchaffiliates_result, on='country', how='outer')
final_data
# final_data.to_excel('result.xlsx') # сохраним полученные данные в файл
# files.download('result.xlsx') # скачаем файл<jupyter_output><empty_output><jupyter_text>Сбор тикеров ETF<jupyter_code>USA_index = final_data.query('country =="US Large"').index[0] # запомним номер USA
final_data.loc[USA_index, 'country'] = 'U.S.' # поменяем название как на сайте ETFdb
final_data
countries = final_data['country'].tolist()
countries
countries_string = '"' + '", "'.join(countries) + '"'
countries_string<jupyter_output><empty_output><jupyter_text>Соберем все ETF стран, по которым есть информация по CAPE<jupyter_code>etf_URL = 'https://etfdb.com/api/screener/'

#data = '{"sort_by":"average_volume","sort_direction":"desc","page":1,"asset_class":"equity","regions":["Egypt" , "Nigeria", "jopasrukoi", "Israel"],"active_or_passive":"Passive","structure":["ETF"],"only":["meta","data","count"]}'
data_start = '{"sort_by":"average_volume","per_page":999,"sort_direction":"desc","page":1,"asset_class":"equity","regions":['
data_end = '],"active_or_passive":"Passive","structure":["ETF"],"only":["meta","data","count"]}'
data = data_start + countries_string + data_end

response  = requests.post(etf_URL, data= data).json()<jupyter_output><empty_output><jupyter_text>Разложим данные по спискам<jupyter_code>etf_tickers = []
etf_names  =  []
etf_assets =  []

for etf in response['data']:
  etf_tickers.append(etf['symbol']['text'] )
  etf_names.append(etf['name']['text'] )
  
  assets = etf['assets']
  assets = assets.replace("$", "")
  assets = assets.replace(",", "")
  etf_assets.append( assets)<jupyter_output><empty_output><jupyter_text>Создадим датафрейм с информацией о ETF<jupyter_code>pd.options.display.max_rows = 250
 tickers_data = pd.DataFrame(list(zip(etf_tickers, etf_names, etf_assets)), columns =['ticker', 'name', 'assets($M)'])
 tickers_data
tickers_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 999 entries, 0 to 998
Data columns (total 3 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   ticker      999 non-null    object
 1   name        999 non-null    object
 2   assets($M)  999 non-null    object
dtypes: object(3)
memory usage: 23.5+ KB<jupyter_text>Уберем из списка ETF отраслевые, с плечом, активные, шорты и т.д.<jupyter_code>banned_words = ['tech', '2x', '3x', '4x', '5x',  'divid',  '5g', 'health', 'energ', 'material', 'pharm', 'utilit', 'short', 'innov']


def ban_etf(name):
  for bann_word in banned_words:
    if bann_word in name.lower():
      return True
  
  return False


tickers_data['banned'] = tickers_data['name'].apply(ban_etf)<jupyter_output><empty_output><jupyter_text>Посмотрим, например, Израиль - все ли отраслевые ETF забанили<jupyter_code>tickers_data[tickers_data.name.str.contains("Israel") == True]<jupyter_output><empty_output><jupyter_text>Оставим только индексные страновые ETF<jupyter_code>tickers_data = tickers_data.query('banned == False')
tickers_data.info()
tickers_data.dropna(subset =['assets($M)'], inplace = True) # убираем тикеры без данных об активах
len(tickers_data)
#pd.options.display.max_rows = 800
tickers_data.sort_values(by='assets($M)')<jupyter_output><empty_output><jupyter_text>Убираем текстовые значения 'N/A'<jupyter_code>tickers_data['assets($M)'] = tickers_data['assets($M)'].apply(lambda x: 0 if x=='N/A' else x)<jupyter_output><ipython-input-43-17d9f13dd82d>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  tickers_data['assets($M)'] = tickers_data['assets($M)'].apply(lambda x: 0 if x=='N/A' else x)<jupyter_text>Меняем тип текст на float<jupyter_code>tickers_data['assets($M)'] = tickers_data['assets($M)'].astype('float')
tickers_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 730 entries, 1 to 998
Data columns (total 4 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   ticker      730 non-null    object 
 1   name        730 non-null    object 
 2   assets($M)  730 non-null    float64
 3   banned      730 non-null    bool   
dtypes: bool(1), float64(1), object(2)
memory usage: 23.5+ KB<jupyter_text>Функция ищет страну в названии ETF<jupyter_code>def get_country(name):
  for country in countries:
    if country.lower() in name.lower():
      return country
  return ''

tickers_data['country'] = tickers_data['name'].apply(get_country)
tickers_data.sort_values(by=[ 'country', 'assets($M)'])<jupyter_output><empty_output><jupyter_text>Приводим названия тикеров по США к формату 'U.S.'<jupyter_code>def sp500_to_us(row):
  if 'S\u0026P 500' in row['name']:
    return 'U.S.'

  if 'S&P 500' in row['name']:
    return 'U.S.'

  return row['country']

tickers_data['country'] = tickers_data.apply(sp500_to_us, axis=1)
tickers_data.sort_values(by='assets($M)', ascending=False)<jupyter_output><ipython-input-47-7d9c502f641a>:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  tickers_data['country'] = tickers_data.apply(sp500_to_us, axis=1)<jupyter_text>Сделаем выборку ETF с самым большим числом активов по каждой стране<jupyter_code>high_assets = tickers_data.sort_values(by='assets($M)', ascending=False).groupby('country').first().reset_index()
high_assets = high_assets.drop('banned', 1)
high_assets<jupyter_output><empty_output><jupyter_text>Если не заполнена страна у ETF VTI<jupyter_code>if high_assets[high_assets['ticker']=='VTI']['country'].to_string().strip()  == '0':
  str_num = high_assets[high_assets['ticker']=='VTI'].index 
  high_assets.loc[str_num,'country'] = 'All country' # заполняем<jupyter_output><empty_output><jupyter_text>Объединим данные по CAPE и страновым ETF в одну таблицу<jupyter_code>aggregated_data = final_data.merge(high_assets, on='country', how='outer')
aggregated_data
aggregated_data[barclays_column_name] = aggregated_data[barclays_column_name].astype('float64')
aggregated_data<jupyter_output><empty_output><jupyter_text>Сбор данных по коэфциенту P/B Функция для добычи коэффициента P/B с сайта etf.com<jupyter_code>def get_price_book_ratio(ticker):
    
    if ticker is None: 
        return None
    
    
    url = "https://www.etf.com/"

    try:
        driver = webdriver.Firefox(
            executable_path=r'C:\Users\User\AppData\Local\python\geckodriver.exe'
        )
        driver.get(url = url + ticker)
        time.sleep(5)

        soup                = BeautifulSoup(driver.page_source, "lxml")
        price_book_selector = '#fundPortfolioData > section > div:nth-child(4) > span'
        data                = soup.select(price_book_selector)        
        return data[0].text

    except Exception as ex:
        print('Exception:', ticker, ex)
        
    finally:
        driver.close()
        driver.quit()<jupyter_output><empty_output><jupyter_text>Заполним значения P/B для каждой страны<jupyter_code>aggregated_data['PB'] = aggregated_data['ticker'].apply(get_price_book_ratio) 
aggregated_data<jupyter_output>Exception: nan can only concatenate str (not "float") to str
Exception: nan can only concatenate str (not "float") to str<jupyter_text>Переводим строку в число<jupyter_code>aggregated_data['PB'] = aggregated_data['PB'].astype('float')<jupyter_output><empty_output><jupyter_text>Расчет долей портфеля Функция считает средний коэффициент по двум значениеям CAPE и одному PB<jupyter_code>def get_aggregated_factor(row):
    
    cape1 = row['RA_CAPE']
    cape2 = row[barclays_column_name]
    pb    = row['PB']
    
    if (cape1 is None) or (cape2 is None) or (pb is None) : 
        return None
    
    if  math.isnan(cape1) or math.isnan(cape2) or math.isnan(pb) : 
        return None
    
    medium = (cape1 + cape1 + pb*10 ) / 3
    return round(medium, 2)<jupyter_output><empty_output><jupyter_text>Считаем аггрегированый коэффициент<jupyter_code>aggregated_data['aggregated_factor'] = aggregated_data.apply(get_aggregated_factor, axis=1)
aggregated_data = aggregated_data.sort_values('aggregated_factor').reset_index(drop=True)
aggregated_data<jupyter_output><empty_output><jupyter_text>Создадим коэффициент, который обратно пропорцианален посчитанному<jupyter_code>aggregated_data['reverse_factor'] = 1 /  aggregated_data['aggregated_factor']<jupyter_output><empty_output><jupyter_text>Посчитаем коэффициент, который надо делить на коэффициент страны, чтобы по топ 10 недооцененных стран сумма долей была 100%<jupyter_code>koef = 100 /  aggregated_data[:10]['reverse_factor'].sum()   
koef<jupyter_output><empty_output><jupyter_text>Удалим ненужный  коэффициент<jupyter_code>aggregated_data = aggregated_data.drop('reverse_factor', 1)<jupyter_output><empty_output><jupyter_text>Посчитаем доли в портфеле<jupyter_code>aggregated_data['portfolio %'] = np.round( koef / aggregated_data['aggregated_factor'], 1)
aggregated_data<jupyter_output><empty_output><jupyter_text>Всем ETF, которые не вошли в первую десятку по недооцененности, ставим 0% присутствия в портфеле<jupyter_code>aggregated_data.loc[aggregated_data.index > 9, 'portfolio %'] = 0
aggregated_data<jupyter_output><empty_output><jupyter_text>Построим диаграмму долей стран в портфеле<jupyter_code>top_10 = aggregated_data.head(10)

fig1, ax = plt.subplots(figsize=(10,10))
plt.title('Доли индексов в портфеле')
ax.pie(top_10['portfolio %'], labels = top_10['RA_Index'], autopct='%1.f%%' )
plt.show()<jupyter_output><empty_output><jupyter_text>Экспорт данных в google drive Создадим функцию для подготовки данных к отправке в google sheet. Она будет менять float на string с запятой вместо точки<jupyter_code>def prepare_to_google_sheets(df):
  new_df = df.copy()  

  for col in new_df.columns:
    if new_df[col].dtype == 'float64':
      new_df[col] = new_df[col].astype('string')      
      new_df[col] = new_df[col].str.replace('.',',')
  
  return new_df<jupyter_output><empty_output><jupyter_text>Обновим данные в  google drive<jupyter_code>google_service = gspread.service_account(filename='underestimated-countries-cb59d0c70a49.json')
google_sheet   = google_service.open_by_key('<KEY>')
worksheet      = google_sheet.get_worksheet(0) #-> 0 - first sheet, 1 - second sheet etc. 

worksheet.clear()

# APPEND DATA TO SHEET
set_with_dataframe(worksheet, prepare_to_google_sheets(aggregated_data)) #-> THIS EXPORTS YOUR DATAFRAME TO THE GOOGLE SHEET<jupyter_output><ipython-input-79-1cf8a15c603f>:7: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will*not* be treated as literal strings when regex=True.
  new_df[col] = new_df[col].str.replace('.',',')
{'fraction_non_alphanumeric': 0.11831626848691695, 'fraction_numerical': 0.01478953356086462, 'mean_word_length': 5.867542613636363, 'pattern_counts': {'":': 17, '<': 144, '<?xml version=': 0, '>': 153, 'https://': 7, 'lorem ipsum': 0, 'www.': 1, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '148890', 'license': 'MIT', 'n_tokens_mistral': 7507, 'n_tokens_neox': 7598, 'n_words': 1379, 'path': 'undervaluation_coefficients.ipynb', 'repo_name': 'grigoriy-grachev/undervalued-countries-portfolio', 'size': '19377'}