open_source_in_quant_finance_frankfurt_20150605.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Open Source in Quantitative Finance 2015 (Frankfurt/Eschborn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Quandl WIKI dataset - A first look at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# TODO: adjust directory\n",
    "# Download file from: https://www.quandl.com/api/v1/datasets/WIKI/MSFT.csv and save as MSFT.csv\n",
    "# Alternatively, load data directly via quandl package\n",
    "data_dir = '/Users/Felix/quandl_wiki/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "head = pd.read_csv(data_dir + 'MSFT.csv', nrows=20, index_col=0, parse_dates=True)\n",
    "head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## qgrid (by Quantopian)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import qgrid\n",
    "qgrid.nbinstall()\n",
    "qgrid.show_grid(head)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's have a second look...this time in Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## By default, `pandas` uses\n",
    "* ### `XlsxWriter` to write `.xlsx`\n",
    "* ### `openpyxl` to write `.xlsm`\n",
    "* ### `xlwt` to write `.xls`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "head.to_excel('pandas_out.xlsx', 'Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open pandas_out.xlsx  # on Windows: !start pandas_out.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulating existing Excel sheets is ... limited\n",
    "\n",
    "* ### Openpyxl can do it, but not everything is supported (e.g. charts are not)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "writer = pd.ExcelWriter('pandas_out.xlsx', engine='openpyxl')\n",
    "writer.book = load_workbook('pandas_out.xlsx')\n",
    "head.to_excel(writer, 'Sheet2')\n",
    "\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open pandas_out.xlsx  # on Windows: !start pandas_out.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `xlwings`: interacts with an open/unsaved Workbook\n",
    "* ### Windows: by wrapping `pywin32` (COM interface)\n",
    "* ### Mac: by wrapping `appscript` (AppleScript)\n",
    "* ### In turn, xlwings needs an installation of Microsoft Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from xlwings import Application, Workbook, Range, Sheet, Chart, ChartType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "Range(\"A1\").value = head\n",
    "Sheet(1).autofit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Sheet.add('Sheet2')\n",
    "Range('Sheet2', 'B2').value = head\n",
    "Sheet(2).autofit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2d arrays: nested lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# A1 Notation\n",
    "Sheet(1).activate()\n",
    "Range('Sheet1', 'A1:B2').value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# The same with Index notation (Excel-1-based)\n",
    "Range(1, (1,1),(2,2)).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get a contiguous Range of cells (as in: \"Ctrl-Shift-Down-and-Right\")\n",
    "out = Range(1, 'K2').table.value\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Assign to top-left corner\n",
    "Range(1, 'B25').value = out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `table` returns a `Range` object, so we can use any `Range` attribute/method on it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Range('B25').table.clear_contents()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `NumPy` Arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Range(1, 'K2', asarray=True).table.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `pandas` DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = Range('A1').table.value\n",
    "df = pd.DataFrame(data[1:], columns=data[0])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's do some `pandas` magic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(data_dir + 'MSFT.csv', index_col=0, parse_dates=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's create a \"behavior\" table: Adj. Volume by month x weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby([df.index.month, df.index.weekday])\n",
    "behavior = grouped['Adj. Volume'].aggregate(np.sum).unstack()\n",
    "behavior.columns = ['MO', 'TU', 'WE', 'TH', 'FR']\n",
    "behavior.index = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']\n",
    "behavior"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now let's use Excel to create a heatmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb_heatmap = Workbook()\n",
    "Range('A1').value = behavior\n",
    "Range('A:A').number_format = 'HH:MM'\n",
    "Sheet(1).autofit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's add a Chart, too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "chart = Chart.add(source_data=Range('A1').table,\n",
    "                  chart_type=ChartType.xlLine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulate attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chart.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chart.name = 'AdjVolume'\n",
    "chart.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# One more thing: PDF Reporting with ReportLab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from reportlab.platypus import SimpleDocTemplate, Table\n",
    "\n",
    "# reportlab initialization with container for Flowables\n",
    "doc = SimpleDocTemplate(\"report_basics.pdf\")\n",
    "elements = []\n",
    "\n",
    "# Create reportlab table from Excel data\n",
    "data = Range('B1').table.value\n",
    "table = Table(data)\n",
    "\n",
    "# Compose content and write PDF document\n",
    "elements.append(table)\n",
    "doc.build(elements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open report_basics.pdf"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}