ipython/12_Excel_Integration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"http://hilpisch.com/tpq_logo.png\" alt=\"The Python Quants\" width=\"35%\" align=\"right\" border=\"0\"><br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python for Finance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Analyze Big Financial Data**\n",
    "\n",
    "O'Reilly (2014)\n",
    "\n",
    "Yves Hilpisch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img style=\"border:0px solid grey;\" src=\"http://hilpisch.com/python_for_finance.png\" alt=\"Python for Finance\" width=\"30%\" align=\"left\" border=\"0\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Buy the book ** |\n",
    "<a href='http://shop.oreilly.com/product/0636920032441.do' target='_blank'>O'Reilly</a> |\n",
    "<a href='http://www.amazon.com/Yves-Hilpisch/e/B00JCYHHJM' target='_blank'>Amazon</a>\n",
    "\n",
    "**All book codes & IPYNBs** |\n",
    "<a href=\"http://oreilly.quant-platform.com\">http://oreilly.quant-platform.com</a>\n",
    "\n",
    "**The Python Quants GmbH** | <a href='http://pythonquants.com' target='_blank'>www.pythonquants.com</a>\n",
    "\n",
    "**Contact us** | <a href='mailto:analytics@pythonquants.com'>analytics@pythonquants.com</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excel Integration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Spreadsheet Interaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "uuid": "afcf7ac4-6e55-4f16-af31-89bcb2975356"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import xlrd, xlwt\n",
    "import xlsxwriter\n",
    "path = 'data/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generating Workbooks (xls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "uuid": "78a2d1aa-68bb-4efc-bb07-33940d780bb4"
   },
   "outputs": [],
   "source": [
    "wb = xlwt.Workbook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "uuid": "4733e4f7-22bc-4830-b70d-75210b9f5099"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlwt.Workbook.Workbook at 0x7fb6dc525190>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "uuid": "3642dc8c-25eb-4182-9cdf-00929f361a18"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlwt.Worksheet.Worksheet at 0x7fb6bcf66050>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb.add_sheet('first_sheet', cell_overwrite_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "uuid": "7c3e80b7-763b-40bf-95ef-baeb82bed88d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb.get_active_sheet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "uuid": "eaeb018a-c028-4ae6-812b-0a792491926a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlwt.Worksheet.Worksheet at 0x7fb6bcf66050>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws_1 = wb.get_sheet(0)\n",
    "ws_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "uuid": "fec1d55e-4ca6-4391-9547-74469632e499"
   },
   "outputs": [],
   "source": [
    "ws_2 = wb.add_sheet('second_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "uuid": "3f082bb9-5871-4e45-92eb-917421a5607d"
   },
   "outputs": [],
   "source": [
    "data = np.arange(1, 65).reshape((8, 8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "uuid": "586d40cb-a458-4df0-b6e3-35035df08f05"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 1,  2,  3,  4,  5,  6,  7,  8],\n",
       "       [ 9, 10, 11, 12, 13, 14, 15, 16],\n",
       "       [17, 18, 19, 20, 21, 22, 23, 24],\n",
       "       [25, 26, 27, 28, 29, 30, 31, 32],\n",
       "       [33, 34, 35, 36, 37, 38, 39, 40],\n",
       "       [41, 42, 43, 44, 45, 46, 47, 48],\n",
       "       [49, 50, 51, 52, 53, 54, 55, 56],\n",
       "       [57, 58, 59, 60, 61, 62, 63, 64]])"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "uuid": "bf1c726a-1d15-454e-b9bf-a8b4c0314cec"
   },
   "outputs": [],
   "source": [
    "ws_1.write(0, 0, 100)\n",
    "  # write 100 in cell \"A1\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "uuid": "f4dbf31c-45b6-4fa9-b350-d3e3570b1ae9"
   },
   "outputs": [],
   "source": [
    "for c in range(data.shape[0]):\n",
    "    for r in range(data.shape[1]):\n",
    "        ws_1.write(r, c, data[c, r])\n",
    "        ws_2.write(r, c, data[r, c])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "uuid": "ca250260-27d4-4d60-b82c-8b14ec1def9d"
   },
   "outputs": [],
   "source": [
    "wb.save(path + 'workbook.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generating Workbooks (xslx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "uuid": "2e82cdbd-68be-4769-936a-7c4598da7b75"
   },
   "outputs": [],
   "source": [
    "wb = xlsxwriter.Workbook(path + 'workbook.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "uuid": "95bbd41a-158f-4a67-8ff2-d5f7b9ed730d"
   },
   "outputs": [],
   "source": [
    "ws_1 = wb.add_worksheet('first_sheet')\n",
    "ws_2 = wb.add_worksheet('second_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "uuid": "478f1615-5147-4c93-a098-d50a53ac1692"
   },
   "outputs": [],
   "source": [
    "for c in range(data.shape[0]):\n",
    "    for r in range(data.shape[1]):\n",
    "        ws_1.write(r, c, data[c, r])\n",
    "        ws_2.write(r, c, data[r, c])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "uuid": "b45daff9-9ea0-46a4-b8ac-c32dcf1accf2"
   },
   "outputs": [],
   "source": [
    "wb.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "uuid": "9dddedc0-c294-4910-aee5-dfd0316ce270"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 trial_yves 5632 Aug  8 11:44 data/workbook.xls\r\n",
      "-rw-r--r-- 1 trial_yves 6049 Aug  8 11:44 data/workbook.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "ll $path*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "uuid": "1de6f4b9-b9ca-46d1-babd-60c02c426871"
   },
   "outputs": [],
   "source": [
    "wb = xlsxwriter.Workbook(path + 'chart.xlsx')\n",
    "ws = wb.add_worksheet()\n",
    "\n",
    "# write cumsum of random values in first column\n",
    "values = np.random.standard_normal(15).cumsum()\n",
    "ws.write_column('A1', values)\n",
    "\n",
    "# create a new chart object\n",
    "chart = wb.add_chart({'type': 'line'})\n",
    "\n",
    "# add a series to the chart\n",
    "chart.add_series({'values': '=Sheet1!$A$1:$A$15',\n",
    "                  'marker': {'type': 'diamond'},})\n",
    "  # series with markers (here: diamond)\n",
    "\n",
    "# insert the chart\n",
    "ws.insert_chart('C1', chart)\n",
    "\n",
    "wb.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading from Workbooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "uuid": "0b447795-de63-4893-a587-17757de0ed43"
   },
   "outputs": [],
   "source": [
    "book = xlrd.open_workbook(path + 'workbook.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "uuid": "2f8e1a77-f30e-48b6-877f-5c022726ef6a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlrd.book.Book at 0x7fb6dc525790>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "uuid": "14534892-0353-4e8c-b55e-1d052623381f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'first_sheet', u'second_sheet']"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book.sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "uuid": "92920fc9-6b93-4410-beb1-e556cd9b2b46"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlrd.sheet.Sheet at 0x7fb6bcee0d10>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1 = book.sheet_by_name('first_sheet')\n",
    "sheet_2 = book.sheet_by_index(1)\n",
    "sheet_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "uuid": "9d1e412e-b41a-4583-bab1-bf028eb95bcf"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'second_sheet'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "uuid": "38004e12-ab84-4e9f-8665-9bf41ad38628"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8, 8)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.ncols, sheet_1.nrows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false,
    "uuid": "8cc96ed8-787d-438a-b2c8-9de1ce5d3084"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cl = sheet_1.cell(0, 0)\n",
    "cl.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false,
    "uuid": "388a04e6-1cdc-4fcb-b65d-9639fe2b3fca"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cl.ctype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false,
    "uuid": "e13520db-f2dd-4ee7-bf61-22e680e8ab87"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[number:25.0,\n",
       " number:26.0,\n",
       " number:27.0,\n",
       " number:28.0,\n",
       " number:29.0,\n",
       " number:30.0,\n",
       " number:31.0,\n",
       " number:32.0]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.row(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false,
    "uuid": "d09bb78b-cf07-486d-a8bd-5c163deff76b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[number:4.0,\n",
       " number:12.0,\n",
       " number:20.0,\n",
       " number:28.0,\n",
       " number:36.0,\n",
       " number:44.0,\n",
       " number:52.0,\n",
       " number:60.0]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.col(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false,
    "uuid": "689202bf-5d67-407a-942e-c98cd096fdf4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[28.0, 29.0, 30.0, 31.0]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.col_values(3, start_rowx=3, end_rowx=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false,
    "uuid": "4efd595d-15ae-406b-8d44-bd5d695cd3db"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[28.0, 36.0, 44.0, 52.0]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.row_values(3, start_colx=3, end_colx=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false,
    "uuid": "bc0c08a2-fee0-4ef7-a25b-b46c1d943afc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 2 3 4 5 6 7 8\n",
      "9 10 11 12 13 14 15 16\n",
      "17 18 19 20 21 22 23 24\n",
      "25 26 27 28 29 30 31 32\n",
      "33 34 35 36 37 38 39 40\n",
      "41 42 43 44 45 46 47 48\n",
      "49 50 51 52 53 54 55 56\n",
      "57 58 59 60 61 62 63 64\n"
     ]
    }
   ],
   "source": [
    "for c in range(sheet_1.ncols):\n",
    "    for r in range(sheet_1.nrows):\n",
    "        print '%i' % sheet_1.cell(r, c).value,\n",
    "    print"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using OpenPyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false,
    "uuid": "c8a07efa-bac1-4291-84d4-09646c645d3c"
   },
   "outputs": [],
   "source": [
    "import openpyxl as oxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false,
    "uuid": "1dbe5488-9d7a-4b81-afa5-b089896590d1"
   },
   "outputs": [],
   "source": [
    "wb = oxl.Workbook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false,
    "uuid": "e6f8e75c-c9d4-4b1f-9dcd-283f2520b398"
   },
   "outputs": [],
   "source": [
    "ws = wb.create_sheet(index=0, title='oxl_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false,
    "uuid": "90ebe04d-4e8c-4128-a1a2-50b8466f8d44"
   },
   "outputs": [],
   "source": [
    "for c in range(data.shape[0]):\n",
    "    for r in range(data.shape[1]):\n",
    "        ws.cell(row=r, column=c).value = data[c, r]\n",
    "        # creates a Cell object and assigns a value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false,
    "uuid": "2ffee32b-6b71-429f-a2e6-41039be7f08f"
   },
   "outputs": [],
   "source": [
    "wb.save(path + 'oxl_book.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false,
    "uuid": "b5c30786-7fdd-4ea2-b3dc-0156d3222988"
   },
   "outputs": [],
   "source": [
    "wb = oxl.load_workbook(path + 'oxl_book.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false,
    "uuid": "42afcab1-8ddc-4352-8525-e5c18e981998"
   },
   "outputs": [],
   "source": [
    "ws = wb.get_active_sheet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false,
    "uuid": "529d482b-7bf7-4236-ae6e-288ba9e2c69c"
   },
   "outputs": [],
   "source": [
    "cell = ws['B4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "uuid": "1696a7ca-dc6d-4d59-bde7-aeeca10a6671"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'B'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cell.column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false,
    "uuid": "5ec00802-cb6c-4d7a-be3c-5553e879a409"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cell.row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "uuid": "34d6d76a-a761-40e6-b10b-e553e50e76c5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cell.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false,
    "uuid": "dd2ad818-070f-4b18-b5f3-c3a67d9ab06a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((<Cell oxl_sheet.B1>,),\n",
       " (<Cell oxl_sheet.B2>,),\n",
       " (<Cell oxl_sheet.B3>,),\n",
       " (<Cell oxl_sheet.B4>,))"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws['B1':'B4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false,
    "uuid": "5b08db4f-cbc5-4754-8f1a-704be0ab11cd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n",
      "10\n",
      "11\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "for cell in ws['B1':'B4']:\n",
    "    print cell[0].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false,
    "uuid": "6e7f8c39-310b-4ad8-adcb-a2be8918d0df"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((<Cell oxl_sheet.B1>, <Cell oxl_sheet.C1>),\n",
       " (<Cell oxl_sheet.B2>, <Cell oxl_sheet.C2>),\n",
       " (<Cell oxl_sheet.B3>, <Cell oxl_sheet.C3>),\n",
       " (<Cell oxl_sheet.B4>, <Cell oxl_sheet.C4>))"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws.range('B1:C4')\n",
    "  # same as ws['B1':'C4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false,
    "uuid": "83fd28b6-5842-4b06-be98-15c1c6bcdce9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 17\n",
      "10 18\n",
      "11 19\n",
      "12 20\n"
     ]
    }
   ],
   "source": [
    "for row in ws.range('B1:C4'):\n",
    "    for cell in row:\n",
    "        print cell.value,\n",
    "    print"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using pandas for Reading and Writing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false,
    "uuid": "d2a24b4b-9218-4887-9923-7fbe55342a1e"
   },
   "outputs": [],
   "source": [
    "df_1 = pd.read_excel(path + 'workbook.xlsx',\n",
    "                     'first_sheet', header=None)\n",
    "df_2 = pd.read_excel(path + 'workbook.xlsx',\n",
    "                     'second_sheet', header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false,
    "uuid": "9119b6ba-6043-4db5-bac9-58498af778b3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H']"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import string\n",
    "columns = []\n",
    "for c in range(data.shape[0]):\n",
    "    columns.append(string.uppercase[c])\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false,
    "uuid": "fa4699d4-3e2b-448f-b5b4-10770cc0dfff"
   },
   "outputs": [],
   "source": [
    "df_1.columns = columns\n",
    "df_2.columns = columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false,
    "uuid": "b0dbcfe3-c903-4f7a-827b-60080f490047"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>41</td>\n",
       "      <td>49</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>26</td>\n",
       "      <td>34</td>\n",
       "      <td>42</td>\n",
       "      <td>50</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>27</td>\n",
       "      <td>35</td>\n",
       "      <td>43</td>\n",
       "      <td>51</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>36</td>\n",
       "      <td>44</td>\n",
       "      <td>52</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>37</td>\n",
       "      <td>45</td>\n",
       "      <td>53</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "      <td>30</td>\n",
       "      <td>38</td>\n",
       "      <td>46</td>\n",
       "      <td>54</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>39</td>\n",
       "      <td>47</td>\n",
       "      <td>55</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "      <td>56</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A   B   C   D   E   F   G   H\n",
       "0  1   9  17  25  33  41  49  57\n",
       "1  2  10  18  26  34  42  50  58\n",
       "2  3  11  19  27  35  43  51  59\n",
       "3  4  12  20  28  36  44  52  60\n",
       "4  5  13  21  29  37  45  53  61\n",
       "5  6  14  22  30  38  46  54  62\n",
       "6  7  15  23  31  39  47  55  63\n",
       "7  8  16  24  32  40  48  56  64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "uuid": "d9444f00-f215-4775-bc3e-542afdde318f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>27</td>\n",
       "      <td>28</td>\n",
       "      <td>29</td>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>36</td>\n",
       "      <td>37</td>\n",
       "      <td>38</td>\n",
       "      <td>39</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "      <td>44</td>\n",
       "      <td>45</td>\n",
       "      <td>46</td>\n",
       "      <td>47</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>49</td>\n",
       "      <td>50</td>\n",
       "      <td>51</td>\n",
       "      <td>52</td>\n",
       "      <td>53</td>\n",
       "      <td>54</td>\n",
       "      <td>55</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>57</td>\n",
       "      <td>58</td>\n",
       "      <td>59</td>\n",
       "      <td>60</td>\n",
       "      <td>61</td>\n",
       "      <td>62</td>\n",
       "      <td>63</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D   E   F   G   H\n",
       "0   1   2   3   4   5   6   7   8\n",
       "1   9  10  11  12  13  14  15  16\n",
       "2  17  18  19  20  21  22  23  24\n",
       "3  25  26  27  28  29  30  31  32\n",
       "4  33  34  35  36  37  38  39  40\n",
       "5  41  42  43  44  45  46  47  48\n",
       "6  49  50  51  52  53  54  55  56\n",
       "7  57  58  59  60  61  62  63  64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false,
    "uuid": "6f15f0c8-9fbd-42d4-825c-464d52ef5691"
   },
   "outputs": [],
   "source": [
    "df_1.to_excel(path + 'new_book_1.xlsx', 'my_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false,
    "uuid": "90b1de7b-b4d8-4e14-a8e6-43801c0bcde8"
   },
   "outputs": [],
   "source": [
    "wbn = xlrd.open_workbook(path + 'new_book_1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false,
    "uuid": "207f983b-7e03-45bd-8ab7-93a27676080c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'my_sheet']"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wbn.sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false,
    "uuid": "62892699-6d38-40f1-9713-21a0ae5b5f99"
   },
   "outputs": [],
   "source": [
    "wbw = pd.ExcelWriter(path + 'new_book_2.xlsx')\n",
    "df_1.to_excel(wbw, 'first_sheet')\n",
    "df_2.to_excel(wbw, 'second_sheet')\n",
    "wbw.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false,
    "uuid": "f4823555-b6e4-4e10-b901-a4039a136e10"
   },
   "outputs": [],
   "source": [
    "wbn = xlrd.open_workbook(path + 'new_book_2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false,
    "uuid": "0bc67f46-4ca2-4f06-a390-556e4f569597"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'first_sheet', u'second_sheet']"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wbn.sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false,
    "uuid": "507f4025-88a2-4bb4-98a8-00e7c5724c8e"
   },
   "outputs": [],
   "source": [
    "data = np.random.rand(20, 100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false,
    "uuid": "5d116207-5ed9-4a91-b0c9-ebcf362f01e4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16000000"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.nbytes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false,
    "uuid": "7ec20d92-67a7-4cf8-bd9a-8b081990609a"
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false,
    "uuid": "b2c3f25b-0a7d-4495-9cfa-4c6df46c8e69"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 38.2 s, sys: 95 ms, total: 38.2 s\n",
      "Wall time: 38.1 s\n"
     ]
    }
   ],
   "source": [
    "%time df.to_excel(path + 'data.xlsx', 'data_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false,
    "uuid": "a03f11b2-350b-4959-94e6-5196ba2a94ab"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1e+03 µs, sys: 11 ms, total: 12 ms\n",
      "Wall time: 11.8 ms\n"
     ]
    }
   ],
   "source": [
    "%time np.save(path + 'data', data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false,
    "uuid": "6b2c8cf0-987e-43bc-98cc-5ad1a62e430f"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 trial_yves     7382 Aug  8 11:44 data/chart.xlsx\r\n",
      "-rw-r--r-- 1 trial_yves 16000080 Aug  8 11:45 data/data.npy\r\n",
      "-rw-r--r-- 1 trial_yves  3987366 Aug  8 11:45 data/data.xlsx\r\n",
      "-rw-r--r-- 1 trial_yves     5810 Aug  8 11:44 data/new_book_1.xlsx\r\n",
      "-rw-r--r-- 1 trial_yves     6664 Aug  8 11:44 data/new_book_2.xlsx\r\n",
      "-rw-r--r-- 1 trial_yves     6079 Aug  8 11:44 data/oxl_book.xlsx\r\n",
      "-rw-r--r-- 1 trial_yves     5632 Aug  8 11:44 data/workbook.xls\r\n",
      "-rw-r--r-- 1 trial_yves     6049 Aug  8 11:44 data/workbook.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "ll $path*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false,
    "uuid": "1309c082-6a6b-495b-b0e7-031507b15ad5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.2 s, sys: 30 ms, total: 3.23 s\n",
      "Wall time: 3.21 s\n"
     ]
    }
   ],
   "source": [
    "%time df = pd.read_excel(path + 'data.xlsx', 'data_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false,
    "uuid": "2555b0d4-fca4-45c1-9a29-0386dca800c5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 ms, sys: 4 ms, total: 7 ms\n",
      "Wall time: 6.66 ms\n"
     ]
    }
   ],
   "source": [
    "%time data = np.load(path + 'data.npy')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false,
    "uuid": "5b4fc67c-8920-4a79-9935-d802d1c51d2b"
   },
   "outputs": [],
   "source": [
    "data, df = 0.0, 0.0\n",
    "!rm $path*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scripting Excel with Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Installing DataNitro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with DataNitro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Scripting with DataNitro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plotting with DataNitro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### User Defined Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## xlwings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Further Reading"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"http://hilpisch.com/tpq_logo.png\" alt=\"The Python Quants\" width=\"35%\" align=\"right\" border=\"0\"><br>\n",
    "\n",
    "<a href=\"http://www.pythonquants.com\" target=\"_blank\">www.pythonquants.com</a> | <a href=\"http://twitter.com/dyjh\" target=\"_blank\">@dyjh</a>\n",
    "\n",
    "<a href=\"mailto:analytics@pythonquants.com\">analytics@pythonquants.com</a>\n",
    "\n",
    "**Python Quant Platform** |\n",
    "<a href=\"http://oreilly.quant-platform.com\">http://oreilly.quant-platform.com</a>\n",
    "\n",
    "**Derivatives Analytics with Python** |\n",
    "<a href=\"http://www.derivatives-analytics-with-python.com\" target=\"_blank\">Derivatives Analytics @ Wiley Finance</a>\n",
    "\n",
    "**Python for Finance** |\n",
    "<a href=\"http://shop.oreilly.com/product/0636920032441.do\" target=\"_blank\">Python for Finance @ O'Reilly</a>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python2",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}