<jupyter_start><jupyter_text>OECD ShortThese are 3-month rates.<jupyter_code>oecd_short = pandas.read_excel('OECD IMF RoE Interest Rates.xlsx',
                          usecols='A,F,G',
                          parse_dates=['TIME'],
                          sheet_name='DP_LIVE_ShortM',
                          converters = {'Value': lambda x: x/100}
                         )
def get_country(country, df):
    cc = df[df['LOCATION'] == country].sort_values('TIME')
    return pandas.Series(data=cc['Value'].values, index=cc['TIME'])
get_country('AUT', oecd_short).head()<jupyter_output><empty_output><jupyter_text>OECD LongThese are 10-year rates.<jupyter_code>oecd_long = pandas.read_excel('OECD IMF RoE Interest Rates.xlsx',
                          usecols='A,F,G',
                          parse_dates=['TIME'],
                          sheet_name='DP_LIVE_LongM',
                          converters = {'Value': lambda x: x/100}
                         )
get_country('AUT', oecd_long).head()<jupyter_output><empty_output><jupyter_text>IMF RatesThis sheet contains "bond", "bill", and "money market" rates. We need to do some extra processing here to get everything in the right shape for us to use.* "bond" maturity is ???* "bill" maturity is anywhere from 3- to 12-months depending on the country* "money market" maturity is (presumably?) overnight or 1-month?<jupyter_code>imf = pandas.read_excel('OECD IMF RoE Interest Rates.xlsx',
                        sheet_name='IMF Monthly',
                        usecols='A,F,H:AKF', # Warning! AKF goes to January 2020 only.
                        skipfooter=18
                         )
imf.head()
imf = imf.rename(columns={'Unnamed: 0': 'Country'})

def slice_imf(df, indicator_code):
    df_slice = df[df['Indicator Code'] == indicator_code]
    dates = [datetime.strptime(x, '%YM%m') for x in df_slice.T.index[2:]]
    n = pandas.DataFrame(columns=df_slice['Country'], data=df_slice.T[2:].values, index=dates)
    return n.applymap(lambda x: x / 100)

imf_bonds = slice_imf(imf, 'FIGB_PA')
imf_moneymarket = slice_imf(imf, 'FIMM_PA')
imf_bills = slice_imf(imf, 'FITB_PA')
imf_moneymarket.dropna().head()
def make_series(country):
    short = imf_bills[country].combine_first(get_country(country, oecd_short)).dropna()
    long = imf_bonds[country].combine_first(get_country(country, oecd_long)).dropna()

    df = pandas.DataFrame(columns=range(1,11), data={1: short, 10: long}, dtype=numpy.float64)
    df.interpolate(axis=1, inplace=True)
    df.fillna(method='backfill', axis=1, inplace=True)
    return df<jupyter_output><empty_output><jupyter_text>Prefer IMF rates. Are there any cases where we have OECD rates but not IMF rates?<jupyter_code>make_series('AUS')
# given a series of yearly columns, convert it to monthly columns
def explode_series(annual_series):
    years = len(annual_series.columns)
    df = pandas.DataFrame(index=annual_series.index, columns=range(1, 12 * years + 1), dtype=numpy.float64)
    for index, cols in annual_series.iterrows():
        for year, rate in cols.iteritems():
            df.loc[index][year * 12] = rate
    df.interpolate(axis=1, inplace=True)
    df.fillna(method='backfill', axis=1, inplace=True)
    return df / 12 # we need to change the yields from annual yields to monthly yields
    return df
writer = pandas.ExcelWriter('oecd_monthly.xls', engine='xlsxwriter')
for country in imf_bills.columns:
    print(f'Simulating {country}')
    one_df = simulate_annual_turnover(120, 60, explode_series(make_series(country)))
    one_df.to_excel(writer, sheet_name=country)
writer.save()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup