Sample fixed income.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Start the Bloomberg Console\n",
    "%run pdblp.ipynb  #Load the functions from PDBLP. This contains access to the BDP and BDH functionality with a Pandas layer on top\n",
    "\n",
    "con = BCon(debug=True, port=8194)\n",
    "con.start()\n",
    "con.debug = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['912828Y9 Govt'], ['9128286B Govt'], ['9128285M Govt'], ['9128284V Govt'], ['9128282A Govt'], ['9128287B Govt'], ['9128286S Govt'], ['912828YD Govt'], ['9128282R Govt'], ['9128283F Govt'], ['9128283W Govt'], ['9128284N Govt'], ['912828P4 Govt'], ['912828U2 Govt'], ['912828V9 Govt'], ['912828R3 Govt'], ['912828X8 Govt'], ['912828M5 Govt'], ['9128286X Govt'], ['9128285T Govt'], ['9128285J Govt'], ['9128286A Govt'], ['9128285C Govt'], ['9128286F Govt'], ['9128286L Govt'], ['9128285N Govt']]\n"
     ]
    }
   ],
   "source": [
    "'''deliverable basket for TYZ9'''\n",
    "''' pull results from bloomberg SRCH'''\n",
    "\n",
    "my_srch = con.bsrch(\"FI:TYU9 DELIV\")\n",
    "my_srch = my_srch.values.tolist()\n",
    "print(my_srch)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20190905\n"
     ]
    }
   ],
   "source": [
    "'''example of getting standard settle date'''\n",
    "\n",
    "\n",
    "security = 'T3.125 11/15/2028 Govt'\n",
    "\n",
    "trd_dt = '20190904'\n",
    "set_date = con.ref(security, 'SETTLE_DT',[('USER_LOCAL_TRADE_DATE',trd_dt)]).value[0]\n",
    "date = set_date.strftime('%Y%m%d')\n",
    "print(date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "T 3 1/8 11/15/28\n"
     ]
    }
   ],
   "source": [
    "'''print description'''\n",
    "\n",
    "trd_dt = '20190903'\n",
    "descrip = con.ref('9128285M Govt', 'SECURITY_DES').value[0]\n",
    "\n",
    "print(descrip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n",
      "[('20190815', '1.9166'), ('20190816', '1.9350'), ('20190819', '1.9568'), ('20190820', '2.2221'), ('20190821', '1.9564'), ('20190822', '1.9590'), ('20190823', '1.9200'), ('20190826', '1.9380'), ('20190827', '1.9370'), ('20190828', '1.9400'), ('20190829', '1.9210'), ('20190830', '1.9190'), ('20190903', '1.8770'), ('20190904', '1.9303')]\n"
     ]
    }
   ],
   "source": [
    "'''SOFR Interpolation to futures delivery date'''\n",
    "\n",
    "s_date = '20190815'   # start date to run interpolation\n",
    "e_date = '20190904'   # end date to run interpolation\n",
    "delivery_date = '20191202' # futures devliery date for repo\n",
    "\n",
    "one_wk= 7\n",
    "two_wk = 14\n",
    "one_month = 30\n",
    "two_month = 60\n",
    "three_month = 90\n",
    "four_month = 120\n",
    "five_month = 150\n",
    "six_month = 180\n",
    "seven_month = 210\n",
    "eight_month = 240\n",
    "nine_month = 270\n",
    "\n",
    "sofr_tickers = {7 : 'USOSFR1Z Curncy', 14 : 'USOSFR2Z Curncy', 30 : 'USOSFRA Curncy', 60 : 'USOSFRB Curncy', \n",
    "               90 : 'USOSFRC Curncy' , 120 : 'USOSFRD Curncy' , 150 : 'USOSFRE Curncy' , 180 : 'USOSFRF Curncy',\n",
    "               210 : 'USOSFRG Curncy' , 240 : 'USOSFRH Curncy', 270 : 'USOSFRI Curncy'}\n",
    "\n",
    "\n",
    "\n",
    "time_periods = [one_wk, two_wk, one_month, two_month, three_month, four_month, five_month, \n",
    "                six_month, seven_month, eight_month, nine_month]\n",
    "\n",
    "def date_range():\n",
    "    df = con.bdh('USOSFRA Curncy', ['PX_LAST'], s_date, e_date, elms=[(\"calendarCodeOverride\", \"US\")])\n",
    "    t_date =df.index\n",
    "    return t_date.strftime('%Y%m%d')  #date list in bloomberg format\n",
    "\n",
    "\n",
    "\n",
    "interp_sofr = []   # create empty list\n",
    "\n",
    "def low(x):\n",
    "    \n",
    "    time = datetime.strptime(delivery_date,'%Y%m%d')- datetime.strptime(x,'%Y%m%d')\n",
    "    time = time.days # calculate days to delivery date\n",
    "    \n",
    "    z = time_periods\n",
    "    \n",
    "    for e, i in enumerate(z):\n",
    "        if i < time:\n",
    "            continue\n",
    "        else:\n",
    "            n = z[e-1]\n",
    "            ld = con.bdh(sofr_tickers[n], ['PX_LAST'], x, x)\n",
    "            ld = ld.unstack()\n",
    "            ld = ld[0]\n",
    "            ud = con.bdh(sofr_tickers[i], ['PX_LAST'], x, x)\n",
    "            ud = ud.unstack()\n",
    "            ud = ud[0]\n",
    "            interpolated_rate = ((time - n)/(i-n) * (ud - ld)) + ld\n",
    "            return x ,  '%.4f' % interpolated_rate\n",
    "            break\n",
    "        \n",
    "        \n",
    "def all_dates(d):\n",
    "    for i in d:\n",
    "        interp = low(i)\n",
    "        interp_sofr.append(interp)\n",
    "        \n",
    "print(all_dates(date_range()))\n",
    "\n",
    "'''Interpolated SOFR to CSV'''\n",
    "print(interp_sofr)\n",
    "\n",
    "I_sofr= pd.DataFrame(interp_sofr)\n",
    "I_sofr.columns = ['Date','Rate']\n",
    "I_sofr['Date'] = pd.DatetimeIndex(I_sofr['Date'])\n",
    "I_sofr['Rate'] = pd.to_numeric(I_sofr['Rate'])\n",
    "#I_sofr.to_csv('I_sofr.csv',mode='a',header=None)\n",
    "I_sofr.to_csv('I_sofr.csv')  # will overwrite file\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('20190815', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.704003125, 'NA', '1.443', '11.196')\n",
      "('20190816', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.64829374999999, 'NA', '1.456', '10.619')\n",
      "('20190819', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.21888125, 'NA', '1.523', '11.215')\n",
      "('20190820', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.556675, 'NA', '1.466', '10.151')\n",
      "('20190821', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.335565625, 'NA', '1.505', '10.364')\n",
      "('20190822', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.16819375, 'NA', '1.531', '9.146')\n",
      "('20190823', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.677096875, 'NA', '1.452', '9.940')\n",
      "('20190826', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.66791875, 'NA', '1.453', '9.793')\n",
      "('20190827', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.987590625, 'NA', '1.398', '10.314')\n",
      "('20190828', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.970803125, 'NA', '1.401', '11.558')\n",
      "('20190829', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.85333749999999, 'NA', '1.419', '11.370')\n",
      "('20190830', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 102.856278125, 'NA', '1.419', '12.715')\n",
      "('20190903', '20191202', '.TYA Corp', 'TYZ9 Comdty', 'NA', 103.19686249999999, 'NA', '1.365', '12.783')\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "''' FUTURES PRICER'''\n",
    "\n",
    "contract = 'TYZ9 Comdty'\n",
    "start_dt = '20190815'\n",
    "end_dt = '20190904'\n",
    "delivery_date = \"20191202\"\n",
    "\n",
    "fut_output=[]\n",
    "\n",
    "def date_range():\n",
    "    df = con.bdh(\"T 3 09/30/2025 Govt\", ['BB_LIBOR_MMS_OIS_DISC_MID'], start_dt, end_dt)\n",
    "    x =df.index\n",
    "    return x.strftime('%Y%m%d')  #date list in bloomberg format\n",
    "\n",
    "\n",
    "\n",
    "def pricer(x): \n",
    "    trd_dt = x\n",
    "    ctd = con.bdh(contract, ['FUT_CTD_CUSIP'], trd_dt, trd_dt)\n",
    "    ctd = ctd.unstack()\n",
    "    ctd = ctd[0]\n",
    "    ctd = ctd + \" Govt\"\n",
    "    ctd_price = con.bdh(ctd, ['PX_LAST'], trd_dt, trd_dt)\n",
    "    ctd_price = ctd_price.unstack()\n",
    "    ctd_price = ctd_price[0]\n",
    "    gross_basis = con.bdh(contract, ['FUT_CTD_GROSS_BASIS'], trd_dt, trd_dt)\n",
    "    gross_basis = gross_basis.unstack()\n",
    "    gross_basis = gross_basis[0]/32\n",
    "    cash_fwd_prc = ctd_price - gross_basis\n",
    "    fwd_yld = con.ref(ctd, 'YAS_BOND_YLD',[('YAS_BOND_PX',cash_fwd_prc),('SETTLE_DT',delivery_date)]).value[0]\n",
    "    fwd_z = con.ref(ctd, 'YAS_ZSPREAD',[('YAS_BOND_YLD',fwd_yld),('OAS_CURVE_DT', trd_dt),('SETTLE_DT',delivery_date)]).value[0]\n",
    "    return (trd_dt, delivery_date, \".TYA Corp\", contract,\"NA\", cash_fwd_prc,\"NA\" ,'%.3f' % fwd_yld, '%.3f' % fwd_z)\n",
    "\n",
    "def one_security_loop():\n",
    "    x = date_range()\n",
    "    for i in x:\n",
    "        fut_out = pricer(i)\n",
    "        print(fut_out)\n",
    "        fut_output.append(fut_out)\n",
    "\n",
    "print(one_security_loop())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}