def load_db():
# Dados Excel
df_excel = pd.read_excel(
r'F:\DADOS\ASSET\MACROECONOMIA\DADOS\Setor_Externo\Estudos\MODELO_BALANCA.xlsm',
sheet_name='dados',
index_col=0)
df_excel = df_excel.pct_change(1, fill_method=None)
idx = list(df_excel.index)
new_idx = idx.copy()
for i in range(len(idx)):
new_idx[i] = idx[i].replace(day=1)
new_idx = pd.Series(new_idx, index=df_excel.index)
df_excel = pd.concat([df_excel, new_idx], axis=1)
df_excel = df_excel.set_index(df_excel.columns[-1])
x_exoil = df_excel.loc[:, 'x_exoil'].copy().dropna()
x_oil = df_excel.loc[:, 'x_oil'].copy().dropna()
m_exoil = df_excel.loc[:, 'm_exoil'].copy().dropna()
m_oil = df_excel.loc[:, 'm_oil'].copy().dropna()
df_l = df_excel.iloc[:, 4:].shift(3)
names = list(df_l.columns)
names_dic = dict(zip(names, np.zeros(len(names))))
for key in names_dic:
names_dic[key] = str(key) + '_3'
df_l = df_l.rename(columns=names_dic)
idx = list(df_l.index)
new_idx = idx.copy()
for i in range(len(idx)):
new_idx[i] = idx[i].replace(day=1)
new_idx = pd.Series(new_idx, index=df_l.index)
df_l = pd.concat([df_l, new_idx], axis=1)
df_l = df_l.set_index(df_l.columns[-1])
# Dados Bloomberg (commodities)
blp1 = rBLP()
bbg = blp1.getHistoricData([
'CO1 Comdty', 'S 1 COMB Comdty', 'LC1 COMB Comdty', 'XAU BGN Curncy',
'KC1 Comdty', 'SB1 Comdty', 'CT1 Comdty', 'CNY REGN Curncy'
], ['PX_LAST'],
startDate=dt(2000, 1, 1),
endDate=dt(2100, 1, 1))
bbg_names = [
'petroleo', 'soja', 'boi', 'ouro', 'cafe', 'acucar', 'algodao', 'cny'
]
bbg_dic = {}
for i in range(len(bbg)):
bbg_dic[bbg_names[i]] = bbg[i].rename(
columns={'PX_LAST': bbg_names[i]})
bbg_month = {}
for i in bbg_dic.keys():
bbg_month[i] = pd.Series(eu.month_mma(bbg_dic[i]).iloc[:, 0])
bbg_month = pd.DataFrame(bbg_month)
bbg_month = bbg_month.pct_change(1).shift(3)
names = list(bbg_month.columns)
names_dic = dict(zip(names, np.zeros(len(names))))
for key in names_dic:
names_dic[key] = str(key) + '_3'
bbg_month = bbg_month.rename(columns=names_dic)
# Dados de Atividade (PMC e PIM)
pmc_list = mds.find(library=mds.econVS, **{'table': 'pmc'})
pim_list = mds.find(library=mds.econVS, **{'table': 'pim'})
all_list = pmc_list + pim_list
m_list = [
i for i in all_list if i['real'] == 'yes' and i['seasonality'] == 'nsa'
]
activ_df, _ = mongo_load(m_list=m_list)
activ_df = activ_df.pct_change(1).shift(3)
names = list(activ_df.columns)
names_dic = dict(zip(names, np.zeros(len(names))))
for key in names_dic:
names_dic[key] = str(key) + '_3'
activ_df = activ_df.rename(columns=names_dic)
activ_df = activ_df.loc['2002-05-01':, :]
activ_df = activ_df.dropna(how='any', axis=1)
# Juntando tudo e fazendo lags
X = pd.concat([activ_df, df_l, bbg_month], axis=1)
for series in X:
series_6 = X[series].shift(3)
series_6.name = series[:-2] + '_6'
series_9 = X[series].shift(6)
series_9.name = series[:-2] + '_9'
series_12 = X[series].shift(9)
series_12.name = series[:-2] + '_12'
series_15 = X[series].shift(12)
series_15.name = series[:-2] + '_15'
series_18 = X[series].shift(15)
series_18.name = series[:-2] + '_18'
X = pd.concat([X, series_6, series_9, series_12, series_15, series_18],
axis=1)
return X, x_exoil, x_oil, m_exoil, m_oil
import xlwings as xw
import pandas as pd
from mDataStore.globalMongo import mds
from mDataStore.bloomberg.remote import rBLP
blp1 = rBLP()
def updateDB():
input = xw.Book.caller().sheets['db_input']
out = xw.Book.caller().sheets['db']
tb_blp = input.range('blp_hist_inp[#ALL]').options(pd.DataFrame,
expand='table').value
tb_db = input.range('db_inp[#ALL]').options(pd.DataFrame,
expand='table').value
out.clear_contents()
k = 1
for i in range(tb_db.shape[0]):
at = tb_db.index[i]
freq = tb_db.freq[i]
library = tb_db.library[i]
dti = tb_db.dt_start[i]
dtf = tb_db.dt_end[i]
dfO = mds.read(at, freq, library=library, date_range=[dti, dtf])
out.cells(1, k).value = pd.DataFrame(dfO)
out.cells(1, k).value = at + '_' + freq
k += dfO.shape[1] + 2
# import globalM
from mDataStore.bloomberg.remote import rBLP
# import time
# if globalM.useLocalDB:
# mds = mongoDS()
blp = rBLP()
# pass
# else:
# mds = mongoDS(replicaSet='r1',doAssetDF=False)
#
# if globalM.doSetPrimary:
# if mds.mongo.primary[0] != globalM.myMongoHost.split(':')[0]:
# mds.setPrimary(globalM.myMongoHost)
# time.sleep(10)
# mds = mongoDS(replicaSet='r1',doAssetDF=True)