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)