{ "cells": [ { "cell_type": "code", "execution_count": 338, "id": "0f5465ea-745a-4bd2-ac9c-97e3b41a9eb8", "metadata": {}, "outputs": [], "source": [ "from xbbg import blp\n", "import numpy as np\n", "import math\n", "import scipy as sp" ] }, { "cell_type": "code", "execution_count": 339, "id": "a0b37115-c1ee-4638-8899-827c13b3bfdb", "metadata": {}, "outputs": [], "source": [ "bbg_tickers = 'JABAX US Equity','MSAAMMOR Index','SOFRINDX Index'\n", "bbg_fields = 'TOT_RETURN_INDEX_GROSS_DVDS'\n", "bbg_start_date = '2020-06-30'\n", "bbg_end_date = '2021-06-30'" ] }, { "cell_type": "code", "execution_count": 340, "id": "48d6044a-c409-403b-a06d-ad84ec1b62ba", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US EquityMSAAMMOR IndexSOFRINDX Index
02020-06-3036.483666.0841.041492
12020-07-0136.603670.0671.041495
22020-07-0236.723692.6091.041498
32020-07-03NaN3694.969NaN
42020-07-0637.103730.3721.041511
\n", "
" ], "text/plain": [ " price_date JABAX US Equity MSAAMMOR Index SOFRINDX Index\n", "0 2020-06-30 36.48 3666.084 1.041492\n", "1 2020-07-01 36.60 3670.067 1.041495\n", "2 2020-07-02 36.72 3692.609 1.041498\n", "3 2020-07-03 NaN 3694.969 NaN\n", "4 2020-07-06 37.10 3730.372 1.041511" ] }, "execution_count": 340, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = blp.bdh(tickers=bbg_tickers, flds=bbg_fields, start_date=bbg_start_date, end_date=bbg_end_date,)\n", "df.reset_index(level=0, inplace=True)\n", "new_column_names = ['price_date']\n", "new_column_names.extend(bbg_tickers)\n", "df.columns = new_column_names\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 341, "id": "1a985a87-8345-4e45-b23c-5b791aa7be28", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US EquityMSAAMMOR IndexSOFRINDX Index
02020-06-3036.483666.08401.041492
12020-07-0136.603670.06701.041495
22020-07-0236.723692.60901.041498
42020-07-0637.103730.37201.041511
52020-07-0736.903710.38741.041514
\n", "
" ], "text/plain": [ " price_date JABAX US Equity MSAAMMOR Index SOFRINDX Index\n", "0 2020-06-30 36.48 3666.0840 1.041492\n", "1 2020-07-01 36.60 3670.0670 1.041495\n", "2 2020-07-02 36.72 3692.6090 1.041498\n", "4 2020-07-06 37.10 3730.3720 1.041511\n", "5 2020-07-07 36.90 3710.3874 1.041514" ] }, "execution_count": 341, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = df.dropna()\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 342, "id": "a0a9913b-54f2-4c2e-9321-f14f13f1977d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US Equityprevious_priceprice_relativeprice_returnMSAAMMOR IndexSOFRINDX Index
02020-06-3036.48NaNNaNNaN3666.08401.041492
12020-07-0136.6036.481.0032890.0032843670.06701.041495
22020-07-0236.7236.601.0032790.0032733692.60901.041498
42020-07-0637.1036.721.0103490.0102953730.37201.041511
52020-07-0736.9037.100.994609-0.0054053710.38741.041514
\n", "
" ], "text/plain": [ " price_date JABAX US Equity previous_price price_relative price_return \\\n", "0 2020-06-30 36.48 NaN NaN NaN \n", "1 2020-07-01 36.60 36.48 1.003289 0.003284 \n", "2 2020-07-02 36.72 36.60 1.003279 0.003273 \n", "4 2020-07-06 37.10 36.72 1.010349 0.010295 \n", "5 2020-07-07 36.90 37.10 0.994609 -0.005405 \n", "\n", " MSAAMMOR Index SOFRINDX Index \n", "0 3666.0840 1.041492 \n", "1 3670.0670 1.041495 \n", "2 3692.6090 1.041498 \n", "4 3730.3720 1.041511 \n", "5 3710.3874 1.041514 " ] }, "execution_count": 342, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['previous_price'] = df[bbg_tickers[0]].shift(1)\n", "df['price_relative'] = df[bbg_tickers[0]] / df[bbg_tickers[0]].shift(1)\n", "df['price_return'] = np.log(df[bbg_tickers[0]] / df[bbg_tickers[0]].shift(1))\n", "\n", "#p['num_days'] = p['price_date'].diff().dt.days \n", "#p.drop(index=p.index[0], axis=0, inplace=True)\n", "\n", "cols = df.columns.to_list()\n", "cols = cols[:2] + cols[4:] + cols[2:4]\n", "df = df[cols]\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 343, "id": "7480f2ac-a7e3-4284-b582-3b0feccd120e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US Equityprevious_priceprice_relativeprice_returnMSAAMMOR Indexprevious_bmkbmk_relativebmk_returnactive_returnSOFRINDX Index
02020-06-3036.48NaNNaNNaN3666.0840NaNNaNNaNNaN1.041492
12020-07-0136.6036.481.0032890.0032843670.06703666.0841.0010860.0010860.0021981.041495
22020-07-0236.7236.601.0032790.0032733692.60903670.0671.0061420.006123-0.0028501.041498
42020-07-0637.1036.721.0103490.0102953730.37203692.6091.0102270.0101750.0001211.041511
52020-07-0736.9037.100.994609-0.0054053710.38743730.3720.994643-0.005372-0.0000341.041514
\n", "
" ], "text/plain": [ " price_date JABAX US Equity previous_price price_relative price_return \\\n", "0 2020-06-30 36.48 NaN NaN NaN \n", "1 2020-07-01 36.60 36.48 1.003289 0.003284 \n", "2 2020-07-02 36.72 36.60 1.003279 0.003273 \n", "4 2020-07-06 37.10 36.72 1.010349 0.010295 \n", "5 2020-07-07 36.90 37.10 0.994609 -0.005405 \n", "\n", " MSAAMMOR Index previous_bmk bmk_relative bmk_return active_return \\\n", "0 3666.0840 NaN NaN NaN NaN \n", "1 3670.0670 3666.084 1.001086 0.001086 0.002198 \n", "2 3692.6090 3670.067 1.006142 0.006123 -0.002850 \n", "4 3730.3720 3692.609 1.010227 0.010175 0.000121 \n", "5 3710.3874 3730.372 0.994643 -0.005372 -0.000034 \n", "\n", " SOFRINDX Index \n", "0 1.041492 \n", "1 1.041495 \n", "2 1.041498 \n", "4 1.041511 \n", "5 1.041514 " ] }, "execution_count": 343, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['previous_bmk'] = df[bbg_tickers[1]].shift(1)\n", "df['bmk_relative'] = df[bbg_tickers[1]] / df[bbg_tickers[1]].shift(1)\n", "df['bmk_return'] = np.log(df[bbg_tickers[1]] / df[bbg_tickers[1]].shift(1))\n", "df['active_return'] = df['price_return'] - df['bmk_return']\n", "\n", "cols = df.columns.to_list()\n", "cols = cols[:6] + cols[7:] + cols[6:7]\n", "df = df[cols]\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 344, "id": "1fb3f622-e8db-4825-8c42-9fb862eb1147", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US Equityprevious_priceprice_relativeprice_returnMSAAMMOR Indexprevious_bmkbmk_relativebmk_returnactive_returnSOFRINDX Indexprevious_rfrf_relativerf_returnexcess_return
02020-06-3036.48NaNNaNNaN3666.0840NaNNaNNaNNaN1.041492NaNNaNNaNNaN
12020-07-0136.6036.481.0032890.0032843670.06703666.0841.0010860.0010860.0021981.0414953666.0841.0010860.0010860.002198
22020-07-0236.7236.601.0032790.0032733692.60903670.0671.0061420.006123-0.0028501.0414983670.0671.0061420.006123-0.002850
42020-07-0637.1036.721.0103490.0102953730.37203692.6091.0102270.0101750.0001211.0415113692.6091.0102270.0101750.000121
52020-07-0736.9037.100.994609-0.0054053710.38743730.3720.994643-0.005372-0.0000341.0415143730.3720.994643-0.005372-0.000034
\n", "
" ], "text/plain": [ " price_date JABAX US Equity previous_price price_relative price_return \\\n", "0 2020-06-30 36.48 NaN NaN NaN \n", "1 2020-07-01 36.60 36.48 1.003289 0.003284 \n", "2 2020-07-02 36.72 36.60 1.003279 0.003273 \n", "4 2020-07-06 37.10 36.72 1.010349 0.010295 \n", "5 2020-07-07 36.90 37.10 0.994609 -0.005405 \n", "\n", " MSAAMMOR Index previous_bmk bmk_relative bmk_return active_return \\\n", "0 3666.0840 NaN NaN NaN NaN \n", "1 3670.0670 3666.084 1.001086 0.001086 0.002198 \n", "2 3692.6090 3670.067 1.006142 0.006123 -0.002850 \n", "4 3730.3720 3692.609 1.010227 0.010175 0.000121 \n", "5 3710.3874 3730.372 0.994643 -0.005372 -0.000034 \n", "\n", " SOFRINDX Index previous_rf rf_relative rf_return excess_return \n", "0 1.041492 NaN NaN NaN NaN \n", "1 1.041495 3666.084 1.001086 0.001086 0.002198 \n", "2 1.041498 3670.067 1.006142 0.006123 -0.002850 \n", "4 1.041511 3692.609 1.010227 0.010175 0.000121 \n", "5 1.041514 3730.372 0.994643 -0.005372 -0.000034 " ] }, "execution_count": 344, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['previous_rf'] = df[bbg_tickers[1]].shift(1)\n", "df['rf_relative'] = df[bbg_tickers[1]] / df[bbg_tickers[1]].shift(1)\n", "df['rf_return'] = np.log(df[bbg_tickers[1]] / df[bbg_tickers[1]].shift(1))\n", "df['excess_return'] = df['price_return'] - df['rf_return']\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 345, "id": "837f10bd-9bc6-4e61-8d23-80e2d37eb5ee", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
price_dateJABAX US Equityprevious_priceprice_relativeprice_returnMSAAMMOR Indexprevious_bmkbmk_relativebmk_returnactive_returnSOFRINDX Indexprevious_rfrf_relativerf_returnexcess_return
12020-07-0136.6036.481.0032890.0032843670.06703666.08401.0010860.0010860.0021981.0414953666.08401.0010860.0010860.002198
22020-07-0236.7236.601.0032790.0032733692.60903670.06701.0061420.006123-0.0028501.0414983670.06701.0061420.006123-0.002850
42020-07-0637.1036.721.0103490.0102953730.37203692.60901.0102270.0101750.0001211.0415113692.60901.0102270.0101750.000121
52020-07-0736.9037.100.994609-0.0054053710.38743730.37200.994643-0.005372-0.0000341.0415143730.37200.994643-0.005372-0.000034
62020-07-0837.0836.901.0048780.0048663718.01103710.38741.0020550.0020530.0028141.0415173710.38741.0020550.0020530.002814
\n", "
" ], "text/plain": [ " price_date JABAX US Equity previous_price price_relative price_return \\\n", "1 2020-07-01 36.60 36.48 1.003289 0.003284 \n", "2 2020-07-02 36.72 36.60 1.003279 0.003273 \n", "4 2020-07-06 37.10 36.72 1.010349 0.010295 \n", "5 2020-07-07 36.90 37.10 0.994609 -0.005405 \n", "6 2020-07-08 37.08 36.90 1.004878 0.004866 \n", "\n", " MSAAMMOR Index previous_bmk bmk_relative bmk_return active_return \\\n", "1 3670.0670 3666.0840 1.001086 0.001086 0.002198 \n", "2 3692.6090 3670.0670 1.006142 0.006123 -0.002850 \n", "4 3730.3720 3692.6090 1.010227 0.010175 0.000121 \n", "5 3710.3874 3730.3720 0.994643 -0.005372 -0.000034 \n", "6 3718.0110 3710.3874 1.002055 0.002053 0.002814 \n", "\n", " SOFRINDX Index previous_rf rf_relative rf_return excess_return \n", "1 1.041495 3666.0840 1.001086 0.001086 0.002198 \n", "2 1.041498 3670.0670 1.006142 0.006123 -0.002850 \n", "4 1.041511 3692.6090 1.010227 0.010175 0.000121 \n", "5 1.041514 3730.3720 0.994643 -0.005372 -0.000034 \n", "6 1.041517 3710.3874 1.002055 0.002053 0.002814 " ] }, "execution_count": 345, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = df.dropna()\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 346, "id": "1e754e72-60d3-4ae9-8074-17e643677205", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "250" ] }, "execution_count": 346, "metadata": {}, "output_type": "execute_result" } ], "source": [ "num_obs = len(df)\n", "num_obs" ] }, { "cell_type": "code", "execution_count": 347, "id": "97fe2a0e-e201-4313-afb4-fca8bbb18243", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.2161012686887972" ] }, "execution_count": 347, "metadata": {}, "output_type": "execute_result" } ], "source": [ "np.mean(df['price_return']) * num_obs" ] }, { "cell_type": "code", "execution_count": 361, "id": "20260d84-fb57-44de-b307-196856c74901", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "36.48 45.28 0.216101268688797\n" ] } ], "source": [ "price_first = df['previous_price'].iloc[0]\n", "price_last = df[bbg_tickers[0]].iloc[-1]\n", "print(price_first, price_last, np.log(price_last / price_first))" ] }, { "cell_type": "code", "execution_count": 349, "id": "fb6e094d-4b9b-47bc-a74f-2be8a0c5c433", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.21610126868879728" ] }, "execution_count": 349, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sum(df['price_return'])" ] }, { "cell_type": "code", "execution_count": 350, "id": "6ea1a701-c633-4443-b585-f3cb62b09fd1", "metadata": {}, "outputs": [], "source": [ "def ret(X, n=1):\n", " return np.mean(X) * n\n", " \n", " \n", "def vol(X, n=1):\n", " return np.std(X) * math.sqrt(n)" ] }, { "cell_type": "code", "execution_count": 360, "id": "ffdb3097-caa2-48a0-af18-580a6d8276db", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Rates of Return:\n", "price = 0.216101\n", "\n", "bench = 0.214480\n", "active = 0.001621\n", "\n", "riskless= 0.214480\n", "excess = 0.001621\n", "\n", "Standard Deviation:\n", "price = 0.099674\n", "bmk = 0.073933\n", "active = 0.055716\n", "\n", "Tracking Error:\n" ] } ], "source": [ "print(\"Rates of Return:\\nprice = %.6f\\n\\nbench = %.6f\\nactive = %.6f\\n\\nriskless= %.6f\\nexcess = %.6f\\n\" % (\n", " ret(df['price_return'], num_obs), \n", " ret(df['bmk_return'], num_obs), \n", " ret(df['price_return'] - df['bmk_return'], num_obs),\n", " ret(df['rf_return'], num_obs), \n", " ret(df['price_return'] - df['rf_return'], num_obs),\n", "))\n", "print(\"Standard Deviation:\\nprice = %.6f\\nbmk = %.6f\\nactive = %.6f\\n\" % (\n", " vol(df['price_return'], num_obs), \n", " vol(df['bmk_return'], num_obs), \n", " vol(df['active_return'], num_obs)\n", "))\n", "print(\"Tracking Error:\")" ] }, { "cell_type": "code", "execution_count": null, "id": "4ccc9c3a-65a4-47d2-af80-6044cd941826", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.0" } }, "nbformat": 4, "nbformat_minor": 5 }