#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Aug 24 09:38:58 2021

@author: benjaminull
"""
import investpy
import datetime as dt
from datetime import date
import streamlit as st
from plotly import graph_objs as go
import pandas as pd
import pybase64 as base64
import io
from plotly.subplots import make_subplots
from logs_portal import log


def formatnum(numero):
    return '{:,.2f}'.format(numero).replace(",", "@").replace(".", ",").replace("@", ".")


def button_style():
    style_button = """
        <style>
          button {
            display: inline-block;
            background-color: #e8e8e8;
            border-radius: 15px;
            border: 4px  #cccccc;
            color: #4a4a4a;
            text-align: center;
            font-size: 20px;
            padding: 2px;
            width: 12em;
            transition: all 0.5s;
            cursor: pointer;
            margin: 0px;
            margin-top: 30px;
          }
          button span {
            cursor: pointer;
            display: inline-block;
            position: relative;
            transition: 0.5s;
          }
          button span:after {
            content: '\00bb';
            position: absolute;
            opacity: 0;
            top: 0;
            right: -20px;
            transition: 0.5s;
          }
          button:hover {
            background-color: #bb1114;
            color:#e8e8e8;
          }
          button:hover span {
            padding-right: 25px;
          }
          button:hover span:after {
            opacity: 1;
            right: 0;
            }
         .stMarkdown{
            margin-bottom:0px;}
    </style>
    """
    st.markdown(style_button, unsafe_allow_html=True)


def get_table_excel_link(df, selected_stocks):
    towrite = io.BytesIO()
    downloaded_file = df.to_excel(towrite, encoding='utf-8', index=False,
                                  header=True)
    towrite.seek(0)  # reset pointer
    file_name = 'Data ' + selected_stocks+'.xlsx'
    style = 'style="color:black;text-decoration: none; font-size:18px;"'
    name_mark = "Descargar " + selected_stocks + ".xlsx"
    b64 = base64.b64encode(towrite.read()).decode()  # some strings
    linko= f'<center><a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" '+style+'download="'+file_name+'"><button>'+name_mark+'</button></a></center>'
    return linko


def style_table():
    # tr:hover {background-color: #E8E8E8;
    #          color:#BB1114;}
    style_table = """
                <style>
                tbody tr:hover { 
                           color:#BB1114;}
                tr { line-height: 5px; }
                thead {
                      background-color:#BB1114 ;
                      color: #E8E8E8;
                    }
                tbody tr:nth-child(odd) {
                      background-color: #fff;
                    }
                    tbody tr:nth-child(even) {
                      background-color: #eee;
                    }
                .css-1rcck9u{
                    padding:0.25rem;}
                tbody tr:nth-child(odd)
                stTable {
                    border-collapse: collapse;
                    background-color:red;
                    margin: 25px 0;
                    font-size: 0.9em;
                    min-width: 400px;
                    box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
                    }
                table{
                    margin-top:0px;
                    font-size:12px;
                    padding:0px;
                    height:200px;}

                @keyframes slidein {
                  from {
                    margin-left: 100%;
                    width: 300%
                  }
                  to {
                    margin-left: 0%;
                    width: 100%;
                  }
                }
                </style>
                """
    st.markdown(style_table, unsafe_allow_html=True)


def grafico_avanzado_com(col_filter, col_button, col_chart, lista, TODAY):
    titulo = "Commodities"
    options = lista
    
    fecha_0 = col_filter.selectbox("Periodo  ", ["1 year", "1 week",
                                                 "1 month", "3 month",
                                                 "6 month", "5 year",
                                                 "10 year", "15 year"])
    info = pd.DataFrame()
    orden = ["Date"]

    fecha1 = seleccionar_fecha(fecha_0)
    fig2 = go.Figure()
    for price in options:
        data = investpy.commodities.get_commodity_historical_data(
                                                 price,
                                                 from_date=fecha1,
                                                 to_date=TODAY)
        info[price] = data['Close']
        close_ = go.Scatter(x=data.index,
                            y=data['Close']/data.iloc[0]["Close"],
                            name=price)
        fig2.add_trace(close_)
        orden.append(price)
    info["Date"] = info.index
    info["Date"] = info["Date"].dt.date
    info = info[orden]
    col_button.markdown(get_table_excel_link(info, "Commodities"),
                          unsafe_allow_html=True)
    fig2.layout.update(title_text=titulo,
                       xaxis_rangeslider_visible=True,
                       height=500,
                       margin_b=20,
                       margin_r=20,
                       margin_l=20,
                       legend=dict(orientation="h",
                                   yanchor="bottom",
                                   y=1.02,
                                   xanchor="right",
                                   x=1))
    col_chart.plotly_chart(fig2, use_container_width=True)


def grafico_avanzado_div(col_chart, col_filter, col_button, monedabase, lista, TODAY):
    button_style()
    titulo = "Divisas"
    base = "USD"
    options = lista
    fecha_0 = col_filter.selectbox("Periodo", ["1 year",
                                               "1 week",
                                               "1 month",
                                               "3 month",
                                               "6 month",
                                               "5 year",
                                               "10 year",
                                               "15 year"])
    info = pd.DataFrame()
    orden = ["Date"]
    fig = make_subplots(specs=[[{"secondary_y": True}]])
    fecha1 = seleccionar_fecha(fecha_0)
    i = 0
    for price in options:
        titulo = titulo 
        selected_cc = base + "/" + price
        data = investpy.currency_crosses.get_currency_cross_historical_data(selected_cc, from_date=fecha1, to_date=TODAY)
        info[price] = data['Close']
        close_ = go.Scatter(x=data.index,
                            y=data['Close']/data.iloc[0]['Close'],
                            name=price)
        fig.add_trace(close_)
        orden.append(price)
        i = i+1
    info["Date"] = info.index
    info["Date"] = info["Date"].dt.date
    info = info[orden]
    col_button.markdown(get_table_excel_link(info, "Divisas"),
                        unsafe_allow_html=True)
    fig.layout.update(title_text=titulo,
                      xaxis_rangeslider_visible=True,
                      height=500,
                      margin_b=20,
                      margin_r=20,
                      margin_l=20,
                      legend=dict(orientation="h",
                                    yanchor="bottom",
                                    y=1.02,
                                    xanchor="right",
                                    x=1))
    col_chart.plotly_chart(fig, use_container_width=True)
import plotly.express as px

def grafico_avanzado_ind(col_chart,
                         col_filter,
                         col_button,
                         lista,
                         countries,
                         today, fechas):
    button_style()
    dict_indices = dict(zip(lista, countries))
    fecha_0 = col_filter.selectbox("Periodo ", fechas)
    fecha1 = seleccionar_fecha(fecha_0)
    titulo = "Indices"
    options = lista
    info = pd.DataFrame()
    orden = ["Date"]
    fig2 = go.Figure()
    i = 0
    for price in options:
        
        data = investpy.get_index_historical_data(
                                        index=price,
                                        country=dict_indices[price],
                                        from_date=fecha1,
                                        to_date=today)
        info[price] = data['Close']
        close_ = go.Scatter(x=data.index,
                            y=data['Close']/data.iloc[0]["Close"],
                            name=price, 
                            line=dict(
                              color=px.colors.qualitative.Pastel[i]))
                            
        fig2.add_trace(close_)
        orden.append(price)
        i=i+1
    info["Date"] = info.index
    info["Date"] = info["Date"].dt.date
    info = info[orden]
    col_button.markdown(get_table_excel_link(info, "Indices"),
                          unsafe_allow_html=True)
    fig2.layout.update(title_text=titulo,
                       xaxis_rangeslider_visible=True,
                       height=500,
                       margin_b=20,
                       margin_r=20,
                       margin_l=20,
                       
                       legend=dict(orientation="h",
                                   yanchor="bottom",
                                   y=1.02,
                                   xanchor="right",
                                   x=1))
    col_chart.plotly_chart(fig2, use_container_width=True)


def grafico_avanzado_tasas(col_filter, col_button, col_chart, bonds, TODAY):
    titulo = "Tasas"
    fecha_0 = col_filter.selectbox("Periodo   ", ["1 year", "1 week",
                                                   "1 month", "3 month",
                                                   "6 month", "5 year",
                                                   "10 year", "15 year"])
    fecha1 = seleccionar_fecha(fecha_0)
    options = bonds
    info = pd.DataFrame()
    orden = ["Date"]
    fig2 = go.Figure()
    i = 0
    for price in options:
        i = i + 1
        data = investpy.get_bond_historical_data(bond=price,
                                                 from_date=fecha1,
                                                 to_date=TODAY)
        info[price] = data['Close']
        close_ = go.Scatter(x=data.index,
                            y=data['Close']/data.iloc[0]["Close"],
                            name=price,
                            line=dict(
                              color=px.colors.qualitative.Pastel[i]))
        fig2.add_trace(close_)
        orden.append(price)
    info["Date"] = info.index
    info["Date"] = info["Date"].dt.date
    info = info[orden]
    col_button.markdown(get_table_excel_link(info, "Tasas"),
                          unsafe_allow_html=True)
    # place0.header(titulo)
    fig2.layout.update(title_text=titulo,
                       xaxis_rangeslider_visible=True,
                       height=500,
                       margin_b=20,
                       margin_r=20,
                       margin_l=20,
                       
                       legend=dict(orientation="h",
                                   yanchor="bottom",
                                   y=1.02,
                                   xanchor="right",
                                   x=1))
    col_chart.plotly_chart(fig2, use_container_width=True)

def view_macro():
    col_filter1, col_button1, col_filter2, col_button2 = st.columns(4)
    col_chart1, col_chart2 = st.columns(2)
    
    
    index = ["S&P CLX IPSA",
             "S&P Merval",
             "Bovespa",
             "S&P Lima General",
             "COLCAP",
             "S&P/BMV IPC",
             "S&P 500",]
             # "FTSE 100",
             # "China A50",
             # "Nikkei 225"]
    countries = ["chile",
                 "argentina",
                 "brazil",
                 "peru",
                 "colombia",
                 "mexico",
                 "united states",]
                  # "united kingdom",
                  # "china",
                  # "japan"]
    place2_index_st = st.empty()
    today = date.today().strftime("%d/%m/%Y")
    fechas = ["1 year",
              "1 week",
              "1 month",
              "3 month",
              "6 month",
              "5 year",
              "10 year",
              "15 year"]
    cc2_i = ["USD", "EUR", 'MXN', "GBP"]
    cc2_f = ["CLP", "EUR", "GBP", "MXN", "JPY", "BRL", "PEN"]
    try:
        grafico_avanzado_ind(col_chart1,
                             col_filter1,
                             col_button1,
                             index,
                             countries,
                             today,
                             fechas)
    except Exception as exc:
        st.write(exc)
    grafico_avanzado_div(col_chart2,
                         col_filter2,
                         col_button2,
                         cc2_i,
                         cc2_f,
                         today)
    commodity = sorted(["Copper",
                        "Silver",
                        "Gold",
                        "Platinum",
                        'Brent Oil',
                        'Crude Oil WTI',
                        "Natural Gas"])
    col_filter1, col_button1, col_filter2, col_button2 = st.columns(4)
    col_chart1, col_chart2 = st.columns(2)
    
    
    grafico_avanzado_com(col_filter1, col_button1, col_chart1, commodity, today)
    bonds = ["Chile 10Y", "Peru 10Y", "China 10Y", "U.S. 10Y", "U.K. 10Y",
                "Germany 10y", "Japan 10Y", "Brazil 10Y"]
    try:
        grafico_avanzado_tasas(col_filter2, col_button2, col_chart2, bonds, today)
    except Exception as exc:
        st.write(exc)
        


@st.cache
def tabla_bonos(stocks, TODAY):
    tabla = pd.DataFrame()
    year_ago = date.today() - dt.timedelta(days=365)
    year_ago = year_ago.strftime("%d/%m/%Y")
    for stock in stocks:
        precios = investpy.get_bond_historical_data(bond=stock,
                                                    from_date=year_ago,
                                                    to_date=TODAY)
        precios = precios["Close"]
        last_price = precios.iloc[-1]
        oned = precios.iloc[-2]
        onew = precios.iloc[-5]
        onem = precios.iloc[-20]
        oney = precios.iloc[0]
        return1m = str(round((last_price - onem), 2))+"%"
        return1d = str(round((last_price - oned), 2))+"%"
        return1w = str(round((last_price - onew), 2))+"%"
        return1y = str(round((last_price - oney), 2))+"%"
        last_price = str(round(last_price, 2))+"%"
        tabla = tabla.append([[last_price, return1d, return1w, return1m,
                               return1y]])
    tabla.columns = ["Tasa", "1d", "1w", "1m", "1y"]
    tabla.index = stocks
    return tabla


@st.cache
def tabla_pendiente(stocks, TODAY):
    tabla = pd.DataFrame()
    year_ago = date.today() - dt.timedelta(days=365)
    year_ago = year_ago.strftime("%d/%m/%Y")
    for stock in stocks:
        precios1 = investpy.get_bond_historical_data(bond=stock + " 2Y",
                                                     from_date=year_ago,
                                                     to_date=TODAY)
        precios2 = investpy.get_bond_historical_data(bond=stock + " 10Y",
                                                     from_date=year_ago,
                                                     to_date=TODAY)
        precios = precios2 - precios1
        precios = precios["Close"]
        last_price = precios.iloc[-1]
        oned = precios.iloc[-2]
        onew = precios.iloc[-5]
        onem = precios.iloc[-20]
        oney = precios.iloc[0]
        return1m = str(round((last_price - onem), 2))+"%"
        return1d = str(round((last_price - oned), 2))+"%"
        return1w = str(round((last_price - onew), 2))+"%"
        return1y = str(round((last_price - oney), 2))+"%"
        last_price = str(round((last_price), 2))+"%"
        tabla = tabla.append([[last_price, return1d, return1w, return1m,
                               return1y]])
    tabla.columns = ["Pendiente", "1d", "1w", "1m", "1y"]
    tabla.index = stocks
    return tabla


@st.cache
def tabla_divisas(stocks, TODAY):
    tabla = pd.DataFrame()
    year_ago = date.today() - dt.timedelta(days=365)
    year_ago = year_ago.strftime("%d/%m/%Y")
    for stock in stocks:
        precios = investpy.currency_crosses.get_currency_cross_historical_data(
                                                    stock,
                                                    from_date=year_ago,
                                                    to_date=TODAY)
        precios = precios["Close"]
        last_price = precios.iloc[-1]
        oned = precios.iloc[-2]
        onew = precios.iloc[-5]
        onem = precios.iloc[-20]
        oney = precios.iloc[0]
        return1m = str(round((last_price/onem-1)*100, 2))+"%"
        return1d = str(round((last_price/oned-1)*100, 2))+"%"
        return1w = str(round((last_price/onew-1)*100, 2))+"%"
        return1y = str(round((last_price/oney-1)*100, 2))+"%"
        last_price = "$" + str(round(last_price, 2))
        tabla = tabla.append([[last_price, return1d, return1w, return1m,
                               return1y]])
    tabla.columns = ["Precio","1d", "1w", "1m", "1y"]
    tabla.index = stocks
    return tabla


@st.cache
def tabla_commodity(stocks, TODAY):
    tabla = pd.DataFrame()
    year_ago = date.today() - dt.timedelta(days=365)
    year_ago = year_ago.strftime("%d/%m/%Y")
    for stock in stocks:
        precios = investpy.commodities.get_commodity_historical_data(
                                                    commodity=stock,
                                                    from_date=year_ago,
                                                    to_date=TODAY)
        precios = precios["Close"]
        last_price = precios.iloc[-1]
        oned = precios.iloc[-2]
        onew = precios.iloc[-5]
        onem = precios.iloc[-20]
        oney = precios.iloc[0]
        return1m = str(round((last_price/onem-1)*100, 2))+"%"
        return1d = str(round((last_price/oned-1)*100, 2))+"%"
        return1w = str(round((last_price/onew-1)*100, 2))+"%"
        return1y = str(round((last_price/oney-1)*100, 2))+"%"
        last_price = "$" + str(round(last_price, 2))
        tabla = tabla.append([[last_price, return1d, return1w, return1m, return1y]])
    tabla.columns = ["Precio","1d", "1w", "1m", "1y"]
    tabla.index = stocks
    return tabla


@st.cache
def tabla_indices(index, countries, TODAY):
    tabla = pd.DataFrame()
    year_ago = date.today() - dt.timedelta(days=365)
    year_ago = year_ago.strftime("%d/%m/%Y")
    for i in range(len(index)):
        precios = investpy.get_index_historical_data(index=index[i],
                                                     country=countries[i],
                                                     from_date=year_ago,
                                                     to_date=TODAY)
        precios = precios["Close"]
        last_price = precios.iloc[-1]
        oned = precios.iloc[-2]
        onew = precios.iloc[-5]
        onem = precios.iloc[-20]
        oney = precios.iloc[0]
        return1m = str(round((last_price/onem-1)*100, 2))+"%"
        return1d = str(round((last_price/oned-1)*100, 2))+"%"
        return1w = str(round((last_price/onew-1)*100, 2))+"%"
        return1y = str(round((last_price/oney-1)*100, 2))+"%"
        last_price = "$" + str(round(last_price, 2))
        tabla = tabla.append([[last_price, return1d, return1w, return1m, return1y]])
    tabla.columns = ["Precio","1d", "1w", "1m", "1y"]
    tabla.index = index
    return tabla


def to_number(valor):
    if valor == "1w":
        value = 0.25
    if valor == "1m":
        value = 1
    elif valor == "3m":
        value = 3
    elif valor == "6m":
        value = 6
    return value


def seleccionar_fecha(fecha_select):
    if fecha_select == "1 week" or fecha_select == "1w":
        fec_in = date.today() - dt.timedelta(days=7)
    elif fecha_select == "1 month":
        fec_in = date.today() - dt.timedelta(days=30)
    elif fecha_select == "3 month":
        fec_in = date.today() - dt.timedelta(days=90)
    elif fecha_select == "6 month":
        fec_in = date.today() - dt.timedelta(days=180)
    elif fecha_select == "1 year":
        fec_in = date.today() - dt.timedelta(days=365)
    elif fecha_select == "5 year":
        fec_in = date.today() - dt.timedelta(days=365*5)
    elif fecha_select == "10 year":
        fec_in = date.today() - dt.timedelta(days=365*10)
    elif fecha_select == "15 year":
        fec_in = date.today() - dt.timedelta(days=365*15)
    fec_in = fec_in.strftime("%d/%m/%Y")
    return fec_in






@log
def curva_yield():
    today = date.today()
    col1, col2 = st.columns(2)
    pais = col1.selectbox("Pais", ["Chile", "Brazil", "Mexico", "Colombia",
                                   "Peru", "Japan", "U.S."])
    meses = col2.selectbox("periodo", ["1w", "1m", "3m", "6m", "1y"])
    if meses == "1w":
        one_months_ago = seleccionar_fecha(meses)
    elif meses == "1y":
        one_months_ago = today.replace(year=today.year - 1).strftime("%d/%m/%Y")
    else:
        mes = to_number(meses)
        one_months_ago = today.replace(month=today.month - mes).strftime("%d/%m/%Y")
    today = today.strftime("%d/%m/%Y")
    if pais == "Chile":
        bonos = ['Chile 1Y', 'Chile 2Y', 'Chile 3Y', 'Chile 4Y', 'Chile 5Y',
                 'Chile 8Y', 'Chile 10Y']
        proporcion = [1, 2, 3, 4, 5, 8, 10]
    elif pais == "Brazil":
        bonos = ['Brazil 3m', 'Brazil 6m', 'Brazil 1Y', 'Brazil 2Y',
                 'Brazil 3Y', 'Brazil 5Y', 'Brazil 8Y', 'Brazil 10Y']
        proporcion = [0.25, 0.5, 1, 2, 3, 5, 8, 10]
    elif pais == "Mexico":
        bonos = ['Mexico 3m', 'Mexico 6m', 'Mexico 1Y', "Mexico 3Y",
                 'Mexico 5Y', 'Mexico 7Y', 'Mexico 10Y']
        proporcion = [0.25, 0.5, 1, 3, 5, 7, 10]
    elif pais == "Colombia":
        bonos = ['Colombia 1Y', 'Colombia 4Y', 'Colombia 5Y', 'Colombia 10Y']
        proporcion = [1, 4, 5, 10]
    elif pais == "Peru":
        bonos = ['Peru 2Y', 'Peru 5Y', 'Peru 10Y']
        proporcion = [2, 5, 10]
    elif pais == "Japan":
        bonos = ['Japan 3m', 'Japan 6m', 'Japan 1Y', "Japan 2Y",
                 'Japan 3Y', 'Japan 5Y', 'Japan 8Y', 'Japan 10Y']
        proporcion = [0.25, 0.5, 1, 3, 5, 7, 10]
    elif pais == "U.S.":
        bonos = ['U.S. 3m', 'U.S. 6m', 'U.S. 1Y', "U.S. 2Y",
                 'U.S. 3Y', 'U.S. 5Y', 'U.S. 8Y', 'U.S. 10Y']
        proporcion = [0.25, 0.5, 1, 3, 5, 7, 10]
    data_today = []
    data_one_month = []
    delta = []
    for bono in bonos:
        data_bono = investpy.bonds.get_bond_historical_data(bono,
                                                            one_months_ago,
                                                            today)
        data_today.append(data_bono.iloc[-1]["Close"])
        data_one_month.append(data_bono.iloc[0]["Close"])
        delta.append(data_bono.iloc[-1]["Close"] - data_bono.iloc[0]["Close"])

    def plot_tasas():
        fig = go.Figure()
        today = go.Scatter(x=proporcion, y=data_today, name="Yield today",
                           line=dict(color="darkred"))
        onemonth = go.Scatter(x=proporcion, y=data_one_month, name="Yield" +
                              meses + " ago", line=dict(color="dimgrey"))
        fig.add_trace(today)
        fig.add_trace(onemonth)
        fig.layout.update(title_text="",
                          width=900, height=300, margin_b=0, margin_t=0,
                          margin_r=0, margin_l=0, legend=dict(orientation="h",
                                                              yanchor="bottom",
                                                              y=1.0,
                                                              xanchor="right",
                                                              x=1),
                          xaxis={'visible': False,
                                 'showticklabels': False})
        st.plotly_chart(fig)
        fig2 = go.Figure()
        fig2.add_trace(go.Bar(
            x=proporcion,
            y=delta,
            name='Delta',
            marker_color='dimgrey'
        ))
        if pais == "Brazil" or pais == "Mexico":
            fig2.layout.update(title_text="",
                               width=900, height=200, margin_b=0, margin_t=0,
                               margin_r=0, margin_l=15,
                               xaxis=go.layout.XAxis(tickangle=70))
            fig2.update_xaxes(range=[-0.3, proporcion[-1]+0.5], ticktext=bonos,
                              tickvals=proporcion)
        else:
            fig2.layout.update(title_text="",
                               width=900, height=200, margin_b=0, margin_t=0,
                               margin_r=0, margin_l=0,
                               xaxis=go.layout.XAxis(tickangle=70))
            fig2.update_xaxes(ticktext=bonos, tickvals=proporcion,
                              range=[0.5, proporcion[-1]+0.5])
        fig2.update_layout(barmode='group')
        st.plotly_chart(fig2)
    plot_tasas()


def plot_raw_data(col, data, color, prefijo, ancho, largo):
    fig = go.Figure()
    close_ = go.Scatter(x=data.index, y=data['Close'], name="stock_close",
                        line=dict(color=color), fill='tonexty')
    fig.add_trace(close_)
    fig.layout.update(title_text="", xaxis_rangeslider_visible=True,
                      width=ancho, height=largo, margin_b=0, margin_t=0,
                      margin_r=0, margin_l=0)
    fig.update_yaxes(range=[min(data['Close'])/1.05,
                            max(data['Close'])*1.05], tickprefix=prefijo)
    col.plotly_chart(fig, use_container_width=True)


# Brasil, Mexico, Chile, Colombia, Peru, USA, Alemania, UK, China, Japon
@log
def tasa10y_2y():
    button_style()
    TODAY = date.today().strftime("%d/%m/%Y")
    bond_10y = ["Chile 10Y", "Peru 10Y", "China 10Y", "U.S. 10Y", "U.K. 10Y",
                "Germany 10y", "Japan 10Y", "Brazil 10Y"]
    bond_2y = ["Chile 2Y",
               "Peru 2Y", "China 2Y", "U.S. 2Y", "U.K. 2Y", "Germany 2y",
               "Japan 2Y", "Brazil 2Y"]
    paises = ["Chile", "Peru", "China", "U.S.", "U.K.", "Alemania",
              "Japon", "Brasil"]
    col1, col2 = st.columns((1.681, 1))
    selected = col1.selectbox("Seleccionar pais", paises)
    fecha_select = col2.selectbox("  ", ["1 year", "1 week", "1 month",
                                         "3 month",   "6 month", "5 year",
                                         "10 year", "15 year"])
    fec_in = seleccionar_fecha(fecha_select)
    data_bonds10y = investpy.get_bond_historical_data(
                                bond=bond_10y[paises.index(selected)],
                                from_date=fec_in,
                                to_date=TODAY)
    data_bonds2y = investpy.get_bond_historical_data(
                                bond=bond_2y[paises.index(selected)],
                                from_date=fec_in,
                                to_date=TODAY)
    data_final = data_bonds10y["Close"]-data_bonds2y["Close"]
    fig = go.Figure()
    close_ = go.Scatter(x=data_bonds10y.index, y=data_final, name="Delta",
                        line=dict(color="midnightblue"), fill='tonexty')
    fig.add_trace(close_)
    fig.layout.update(title_text="", xaxis_rangeslider_visible=True,
                      width=900, height=400, margin_b=0, margin_t=0,
                      margin_r=0, margin_l=0)
    fig.update_yaxes(range=[min(data_final)/1.05,
                            max(data_final)*1.05])
    cols = st.columns((1.681*2.681, 1.681, 1))
    col1, col2 = st.columns((1.681, 1))
    col1.plotly_chart(fig, use_container_width=True)
    data_final2 = pd.DataFrame()
    data_final2["Date"] = list(data_final.index)
    cierre = list(data_bonds10y["Close"]-data_bonds2y["Close"])
    data_final2["Delta"] = list(data_bonds10y["Close"]-data_bonds2y["Close"])
    last_price = cierre[-1]
    first_price = cierre[0]
    returns = round(((last_price - first_price)), 2)
    cols[1].markdown('<p style="font-size:15px; padding-left:20px; margin-bottom:0px;">'+"Tasa 10Y - 2Y"+"</p>", unsafe_allow_html=True)
    cols[1].markdown('<p style="font-size:35px; padding-left:30px;">'+formatnum(last_price)+"%</p>", unsafe_allow_html=True)
    if returns > 0:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:green;">▲ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    else:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:red;">▼ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    st.markdown(get_table_excel_link(data_final2, selected),
                unsafe_allow_html=True)
    paises = ["Brazil", "Chile",
              "Peru", "China", "U.S.", "U.K.", "Germany",
              "Japan"]
    style_table()
    col2.dataframe(tabla_pendiente(paises, TODAY))


@log
def bonos():
    st.sidebar.subheader("Opciones")
    largo = 350
    ancho = 450
    button_style()
    placeholder = st.empty()
    placeholder1 = st.empty()
    TODAY = date.today().strftime("%d/%m/%Y")
    cols = st.columns((1.681*2.5, 1.681, 1))
    col1, col2 = st.columns((1.6, 1))
    paises = ["Brazil", "Mexico", "Chile", "Colombia",
              "Peru", "China", "U.S.", "U.K.", "Germany",
              "Japan"]
    time = ["2Y", "10Y"]
    # #################
    selected_pais = cols[0].selectbox("  ", paises)
    selected_time = cols[1].selectbox("  ", time)
    fecha_select = cols[2].selectbox("  ", ["1 year", "1 week", "1 month",
                                            "3 month",   "6 month", "5 year",
                                            "10 year", "15 year"])
    fec_in = seleccionar_fecha(fecha_select)
    selected = selected_pais + " " + selected_time
    data_bonds = investpy.get_bond_historical_data(bond=selected,
                                                   from_date=fec_in,
                                                   to_date=TODAY)

    plot_raw_data(col1, data_bonds, 'dimgrey', "", ancho, largo)

    last_price = data_bonds.iloc[-1]["Close"]
    first_price = data_bonds.iloc[0]["Close"]
    returns = round((last_price - first_price), 2)
    cols[0].title("Tasa " + selected)
    cols[1].markdown('<p style="font-size:15px; padding-left:20px; margin-bottom:0px;">'+"Tasa"+"</p>", unsafe_allow_html=True)
    cols[1].markdown('<p style="font-size:35px; padding-left:30px;">'+formatnum(last_price)+"%</p>", unsafe_allow_html=True)
    if returns > 0:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:green;">▲ +'+ formatnum(returns)+" %</p>", unsafe_allow_html=True)
    else:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:red;">▼ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    # #################
    style_table()
    bonds10y = ["Brazil 10Y",
                "Mexico 10Y",
                "Chile 10Y",
                "Colombia 10Y",
                "Peru 10Y","China 10Y", "U.S. 10Y", "U.K. 10Y", "Germany 10y",
                "Japan 10Y", ]
    col2.dataframe(tabla_bonos(bonds10y, TODAY))
    data_bonds["Date"] = data_bonds.index
    data_bonds["Date"] = data_bonds["Date"].dt.date
    data_toexcel = data_bonds[["Date", "Close"]]
    st.markdown(get_table_excel_link(data_toexcel, selected),
                unsafe_allow_html=True)
    # graph_advance = st.sidebar.checkbox("Graficos avanzados")
    # if graph_advance:



@log
def Commodities():
    st.sidebar.subheader("Opciones")
    largo = 350
    ancho = 450
    placeholder = st.empty()
    placeholder1 = st.empty()
    button_style()
    TODAY = date.today().strftime("%d/%m/%Y")
    col1, col2 = st.columns((1.681, 1))
    cols = st.columns((1.681*2.681, 1.681, 1))
    commodity = sorted(["Copper", "Silver", "Gold", "Platinum", 'Brent Oil',
                        'Crude Oil WTI', "Natural Gas"])
    # #################
    selected_com = col1.selectbox("  ", commodity)
    fecha_select = col2.selectbox("  ", ["1 year", "1 week", "1 month",
                                         "3 month", "6 month", "5 year",
                                         "10 year", "15 year"])
    fec_in = seleccionar_fecha(fecha_select)
    data_com = investpy.commodities.get_commodity_historical_data(
                                                        commodity=selected_com,
                                                        from_date=fec_in,
                                                        to_date=TODAY)
    col1, col2 = st.columns((1.681, 1))
    plot_raw_data(col1, data_com, 'dimgrey', "", ancho, largo)
    last_price = data_com.iloc[-1]["Close"]
    first_price = data_com.iloc[0]["Close"]
    returns = round(((last_price/first_price-1)*100), 2)
    cols[0].title("Precio " + selected_com)
    cols[1].markdown('<h4 style="font-size:15px; padding-left:20px; margin-bottom:0px;">'+"Precio"+"</h4>", unsafe_allow_html=True)
    cols[1].markdown('<p style="font-size:30px; padding-left:30px;">$'+formatnum(last_price)+"</p>", unsafe_allow_html=True)
    if returns > 0:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:green;">▲ +'+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    else:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:red;">▼ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    ##################

    style_table()
    col2.dataframe(tabla_commodity(commodity, TODAY))
    data_com["Date"] = data_com.index
    data_com["Date"] = data_com["Date"].dt.date
    data_com_toexcel = data_com[["Date", "Close"]]
    st.markdown(get_table_excel_link(data_com_toexcel, selected_com),
                unsafe_allow_html=True)



@log
def Indices():
    st.sidebar.subheader("Opciones")
    largo = 350
    ancho = 450
    placeholder = st.empty()
    placeholder1 = st.empty()
    button_style()
    TODAY = date.today().strftime("%d/%m/%Y")
    col1, col2 = st.columns((1.681, 1))
    cols = st.columns((1.681*2.681, 1.681, 1.2))
    index = ["S&P CLX IPSA", "S&P Merval", "Bovespa", "S&P Lima General",
             "COLCAP", "S&P/BMV IPC", "S&P 500", "FTSE 100", "China A50",
             "Nikkei 225"]
    countries = ["chile", "argentina", "brazil", "peru", "colombia", "mexico",
                 "united states", "united kingdom", "china", "japan"]
    ##################
    selected_index = col1.selectbox("  ", index)
    fecha_select = col2.selectbox("  ", ["1 year", "1 week", "1 month",
                                         "3 month",   "6 month", "5 year",
                                         "10 year", "15 year"])
    fec_in = seleccionar_fecha(fecha_select)
    data_index = investpy.get_index_historical_data(
                                index=selected_index,
                                country=countries[index.index(selected_index)],
                                from_date=fec_in,
                                to_date=TODAY)
    col1, col2 = st.columns((1.681, 1))
    plot_raw_data(col1, data_index, 'dimgrey', "", ancho, largo)
    last_price = data_index.iloc[-1]["Close"]
    first_price = data_index.iloc[0]["Close"]
    returns = round(((last_price/first_price-1)*100), 2)
    cols[0].title("Precio " + selected_index)
    cols[1].markdown('<h4 style="font-size:15px; padding-left:20px; margin-bottom:0px;">'+"Precio"+"</h4>", unsafe_allow_html=True)
    cols[1].markdown('<p style="font-size:30px; padding-left:30px;">$'+formatnum(last_price)+"</p>", unsafe_allow_html=True)
    if returns > 0:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:green;">▲ +'+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    else:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:red;">▼ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    ##################
    style_table()
    col2.dataframe(tabla_indices(index, countries, TODAY))
    data_index["Date"] = data_index.index
    data_index["Date"] = data_index["Date"].dt.date
    data_index_toexcel = data_index[["Date", "Close"]]
    st.markdown(get_table_excel_link(data_index_toexcel, selected_index),
                unsafe_allow_html=True)



@log
def Divisas():
    st.sidebar.subheader("Opciones")
    largo = 350
    ancho = 450
    placeholder = st.empty()
    placeholder1 = st.empty()
    button_style()
    TODAY = date.today().strftime("%d/%m/%Y")
    cols = st.columns(3)
    cc1 = ["USD/CLP", "EUR/CLP", "GBP/CLP", "BRL/CLP", "JPY/CLP", "MXN/CLP",
           "PEN/CLP"]
    ##################
    cc2_i = ["USD", "EUR", 'MXN', "GBP"]
    cc2_f = ["CLP", "USD", "EUR", "GBP", "MXN", "JPY", "BRL", "PEN"]
    ##################
    selected_cc2_i = cols[0].selectbox("   ", cc2_i)
    selected_cc2_f = cols[1].selectbox("     ", cc2_f)
    selected_cc2 = selected_cc2_i + "/" + selected_cc2_f
    fecha_select2 = cols[2].selectbox("    ", ["1 year", "1 week", "1 month",
                                               "3 month",   "6 month",
                                               "5 year", "10 year",
                                               "15 year"])
    fec_in2 = seleccionar_fecha(fecha_select2)
    data_cc2 = investpy.currency_crosses.get_currency_cross_historical_data(
                                selected_cc2, from_date=fec_in2, to_date=TODAY)
    cols = st.columns((1.681*2.681, 1.681, 1))
    col1, col2 = st.columns((1.681, 1))
    plot_raw_data(col1, data_cc2, 'midnightblue', "", ancho, largo)
    last_price = data_cc2.iloc[-1]["Close"]
    first_price = data_cc2.iloc[0]["Close"]
    returns = round(((last_price/first_price-1)*100), 2)
    cols[0].title(selected_cc2)
    cols[1].markdown('<p style="font-size:15px; padding-left:15px; margin-bottom:0px;">'+"Precio"+"</p>", unsafe_allow_html=True)
    cols[1].markdown('<p style="font-size:30px; padding-left:30px;">' + formatnum(last_price)+ " "+selected_cc2_f +"</p>", unsafe_allow_html=True)
    if returns > 0:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:green;">▲ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    else:
        cols[2].markdown('<p style="font-size:22px; padding-top:27px; color:red;">▼ '+formatnum(returns)+" %</p>", unsafe_allow_html=True)
    style_table()
    # col1.dataframe(tabla_indices(index, countries, TODAY))
    # col2.dataframe(tabla_indices(index2, countries, TODAY))
    data_cc2["Date"] = data_cc2.index
    data_cc2["Date"] = data_cc2["Date"].dt.date
    data_cc2_toexcel = data_cc2[["Date", "Close"]]
    st.markdown(get_table_excel_link(data_cc2_toexcel, selected_cc2),
                unsafe_allow_html=True)
    col2.dataframe(tabla_divisas(cc1, TODAY))