main.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:46.961485Z",
     "start_time": "2018-01-09T09:59:46.580517Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import glob\n",
    "import datetime\n",
    "import pandas as pd\n",
    "from scipy import optimize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:46.970105Z",
     "start_time": "2018-01-09T09:59:46.964010Z"
    }
   },
   "outputs": [],
   "source": [
    "data = {file[-6:]:json.load(open(file)) for file in glob.glob('history*')}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:46.983719Z",
     "start_time": "2018-01-09T09:59:46.972121Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'adjustTxnDate': 1510675200000,\n",
       " 'adjustmentId': 85,\n",
       " 'fund': {'canBuy': True,\n",
       "  'canRedeem': True,\n",
       "  'fundCode': '000071',\n",
       "  'fundInvestType': '1',\n",
       "  'fundName': '华夏恒生ETF联接',\n",
       "  'isEtf': False,\n",
       "  'isIndex': True,\n",
       "  'isLof': False,\n",
       "  'isPeriod': False,\n",
       "  'isQdii': True,\n",
       "  'onSale': True,\n",
       "  'riskLevel': 3,\n",
       "  'valDisBy': 'nav'},\n",
       " 'nav': 1.5077,\n",
       " 'navDate': 1510675200000,\n",
       " 'orderCode': '024',\n",
       " 'tradeUnit': 1,\n",
       " 'variety': '恒生'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['000071'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:46.991616Z",
     "start_time": "2018-01-09T09:59:46.985959Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data['000071'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 计算交易记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.012451Z",
     "start_time": "2018-01-09T09:59:46.994211Z"
    }
   },
   "outputs": [],
   "source": [
    "def fmt_date(timestamp):\n",
    "    return datetime.datetime.fromtimestamp(timestamp/1000).date()\n",
    "\n",
    "def buy(code, order):\n",
    "    status[code]['unit'] += order['tradeUnit']\n",
    "    fund_num = int(unit_money / order['nav'])\n",
    "    status[code]['num'] += fund_num\n",
    "    transcations.append({'date': fmt_date(order['navDate']),\n",
    "                         'code': code,\n",
    "                         'unit': -order['tradeUnit'],\n",
    "                         'money': -fund_num * order['nav']})\n",
    "\n",
    "\n",
    "def sell(code, order):\n",
    "    status[code]['unit'] -= order['tradeUnit']\n",
    "    fund_num = int(status[code]['num'] / status[code]['unit']) * order['tradeUnit']\n",
    "    status[code]['num'] -= fund_num\n",
    "    transcations.append({'date': fmt_date(order['navDate']),\n",
    "                         'code': code,\n",
    "                         'unit': order['tradeUnit'],\n",
    "                         'money': fund_num * order['nav']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.021994Z",
     "start_time": "2018-01-09T09:59:47.014228Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def update(code, order):\n",
    "    status.setdefault(code, {'code':code, 'unit':0,'num':0})\n",
    "    \n",
    "    if order['orderCode'] == '022':\n",
    "        buy(code, order)\n",
    "    elif order['orderCode'] == '024':\n",
    "        sell(code, order)\n",
    "    else:\n",
    "        print('unknown order code', code, order)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.030076Z",
     "start_time": "2018-01-09T09:59:47.024228Z"
    }
   },
   "outputs": [],
   "source": [
    "unit_money = 1000  # 每份 1000 元\n",
    "status = {}  # 每个基金的份数和基金份额\n",
    "transcations = []  # 每次交易的份数和金额\n",
    "\n",
    "for k, v in data.items():\n",
    "    for vv in v[::-1]:\n",
    "        update(k, vv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.038954Z",
     "start_time": "2018-01-09T09:59:47.032343Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "current unit: 71\n",
      "73 transcations\n"
     ]
    }
   ],
   "source": [
    "print('current unit:',sum(v['unit'] for v in status.values()))\n",
    "print(len(transcations),'transcations')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 添加最新净值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.046947Z",
     "start_time": "2018-01-09T09:59:47.040999Z"
    }
   },
   "outputs": [],
   "source": [
    "for fund in json.load(open('plan.json'))['composition']:\n",
    "    if fund['planUnit'] > 0 and 'fund' in fund:\n",
    "        fund_status = status[fund['fund']['fundCode']]\n",
    "        fund_status['date'] = fmt_date(fund['navDate'])\n",
    "        fund_status['net'] = fund['nav']\n",
    "        fund_status['value'] = fund['nav'] * fund_status['num']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 转换为 DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.059482Z",
     "start_time": "2018-01-09T09:59:47.048804Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "status_df = pd.DataFrame(list(status.values())).sort_values('unit', ascending=False)\n",
    "status_df = status_df[['code', 'unit', 'num', 'date', 'net', 'value']]\n",
    "transcations_df = pd.DataFrame(transcations).sort_values('date')\n",
    "transcations_df = transcations_df[['date', 'code', 'unit', 'money']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.075614Z",
     "start_time": "2018-01-09T09:59:47.061401Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>unit</th>\n",
       "      <th>num</th>\n",
       "      <th>date</th>\n",
       "      <th>net</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000071</td>\n",
       "      <td>8</td>\n",
       "      <td>6118</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.5711</td>\n",
       "      <td>9611.9898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>001180</td>\n",
       "      <td>7</td>\n",
       "      <td>8829</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>0.8642</td>\n",
       "      <td>7630.0218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000478</td>\n",
       "      <td>7</td>\n",
       "      <td>2488</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>2.4412</td>\n",
       "      <td>6073.7056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000968</td>\n",
       "      <td>7</td>\n",
       "      <td>6286</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.0934</td>\n",
       "      <td>6873.1124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>001051</td>\n",
       "      <td>7</td>\n",
       "      <td>9815</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>0.9880</td>\n",
       "      <td>9697.2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>003376</td>\n",
       "      <td>6</td>\n",
       "      <td>6371</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>0.9265</td>\n",
       "      <td>5902.7315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>100032</td>\n",
       "      <td>6</td>\n",
       "      <td>4743</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.3810</td>\n",
       "      <td>6550.0830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>001064</td>\n",
       "      <td>5</td>\n",
       "      <td>6582</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>0.7790</td>\n",
       "      <td>5127.3780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>001052</td>\n",
       "      <td>3</td>\n",
       "      <td>4794</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>0.6870</td>\n",
       "      <td>3293.4780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>270048</td>\n",
       "      <td>2</td>\n",
       "      <td>1688</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.1790</td>\n",
       "      <td>1990.1520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>162411</td>\n",
       "      <td>2</td>\n",
       "      <td>3658</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>0.6200</td>\n",
       "      <td>2267.9600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>001061</td>\n",
       "      <td>2</td>\n",
       "      <td>1636</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>1.2340</td>\n",
       "      <td>2018.8240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>050027</td>\n",
       "      <td>2</td>\n",
       "      <td>1851</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.0490</td>\n",
       "      <td>1941.6990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>110026</td>\n",
       "      <td>1</td>\n",
       "      <td>530</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.8385</td>\n",
       "      <td>974.4050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>160416</td>\n",
       "      <td>1</td>\n",
       "      <td>1303</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>1.0150</td>\n",
       "      <td>1322.5450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000051</td>\n",
       "      <td>1</td>\n",
       "      <td>1026</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.3400</td>\n",
       "      <td>1374.8400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>003765</td>\n",
       "      <td>1</td>\n",
       "      <td>1041</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.0003</td>\n",
       "      <td>1041.3123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000614</td>\n",
       "      <td>1</td>\n",
       "      <td>994</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>1.2850</td>\n",
       "      <td>1277.2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000216</td>\n",
       "      <td>1</td>\n",
       "      <td>971</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.0238</td>\n",
       "      <td>994.1098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>502010</td>\n",
       "      <td>1</td>\n",
       "      <td>913</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.1095</td>\n",
       "      <td>1012.9735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code  unit   num        date     net      value\n",
       "1   000071     8  6118  2018-01-08  1.5711  9611.9898\n",
       "10  001180     7  8829  2018-01-08  0.8642  7630.0218\n",
       "3   000478     7  2488  2018-01-08  2.4412  6073.7056\n",
       "5   000968     7  6286  2018-01-08  1.0934  6873.1124\n",
       "6   001051     7  9815  2018-01-08  0.9880  9697.2200\n",
       "11  003376     6  6371  2018-01-08  0.9265  5902.7315\n",
       "14  100032     6  4743  2018-01-08  1.3810  6550.0830\n",
       "9   001064     5  6582  2018-01-08  0.7790  5127.3780\n",
       "7   001052     3  4794  2018-01-08  0.6870  3293.4780\n",
       "18  270048     2  1688  2018-01-08  1.1790  1990.1520\n",
       "17  162411     2  3658  2018-01-05  0.6200  2267.9600\n",
       "8   001061     2  1636  2018-01-05  1.2340  2018.8240\n",
       "13  050027     2  1851  2018-01-08  1.0490  1941.6990\n",
       "15  110026     1   530  2018-01-08  1.8385   974.4050\n",
       "16  160416     1  1303  2018-01-05  1.0150  1322.5450\n",
       "0   000051     1  1026  2018-01-08  1.3400  1374.8400\n",
       "12  003765     1  1041  2018-01-08  1.0003  1041.3123\n",
       "4   000614     1   994  2018-01-05  1.2850  1277.2900\n",
       "2   000216     1   971  2018-01-08  1.0238   994.1098\n",
       "19  502010     1   913  2018-01-08  1.1095  1012.9735"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "status_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.109680Z",
     "start_time": "2018-01-09T09:59:47.078588Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>code</th>\n",
       "      <th>unit</th>\n",
       "      <th>money</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2015-08-03</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2015-09-08</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.3060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-11-02</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-12-08</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2015-12-14</td>\n",
       "      <td>160416</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-12-30</td>\n",
       "      <td>000071</td>\n",
       "      <td>-2</td>\n",
       "      <td>-999.3060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.3600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-02-15</td>\n",
       "      <td>000071</td>\n",
       "      <td>-2</td>\n",
       "      <td>-999.0960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>000968</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>001052</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>000071</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2016-05-03</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2016-05-09</td>\n",
       "      <td>001064</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.7470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2016-06-15</td>\n",
       "      <td>001051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.7390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-06-24</td>\n",
       "      <td>000051</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.3240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016-09-14</td>\n",
       "      <td>000614</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>001064</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>050027</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2016-12-15</td>\n",
       "      <td>001180</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>2016-12-15</td>\n",
       "      <td>100032</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2016-12-15</td>\n",
       "      <td>001051</td>\n",
       "      <td>1</td>\n",
       "      <td>1255.6800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>2016-12-30</td>\n",
       "      <td>100032</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2017-01-16</td>\n",
       "      <td>001180</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>2017-01-25</td>\n",
       "      <td>100032</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2017-05-23</td>\n",
       "      <td>001052</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2017-05-23</td>\n",
       "      <td>001180</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>110026</td>\n",
       "      <td>-1</td>\n",
       "      <td>-998.2550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2017-07-17</td>\n",
       "      <td>000968</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>003765</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.3600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>003376</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.5425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>000071</td>\n",
       "      <td>1</td>\n",
       "      <td>1258.0803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>001180</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>001061</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>162411</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>001064</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.3630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>000478</td>\n",
       "      <td>-1</td>\n",
       "      <td>-998.0698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>001061</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>000478</td>\n",
       "      <td>-1</td>\n",
       "      <td>-997.9687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>000216</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>270048</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>050027</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>270048</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>000478</td>\n",
       "      <td>-1</td>\n",
       "      <td>-997.6596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2017-11-02</td>\n",
       "      <td>001052</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.7390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2017-11-02</td>\n",
       "      <td>001064</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2017-11-15</td>\n",
       "      <td>003376</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2017-11-15</td>\n",
       "      <td>000071</td>\n",
       "      <td>1</td>\n",
       "      <td>1316.2221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>001064</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.5583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>000478</td>\n",
       "      <td>-2</td>\n",
       "      <td>-997.8840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>003376</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.4560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>100032</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.5300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>502010</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>2017-12-29</td>\n",
       "      <td>100032</td>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2017-12-29</td>\n",
       "      <td>000478</td>\n",
       "      <td>-1</td>\n",
       "      <td>-997.9264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date    code  unit      money\n",
       "25  2015-08-03  001051    -1  -999.1910\n",
       "1   2015-09-01  000071    -1  -999.4710\n",
       "26  2015-09-08  001051    -1  -999.3060\n",
       "2   2015-11-02  000071    -1  -999.1040\n",
       "3   2015-12-08  000071    -1  -999.9080\n",
       "67  2015-12-14  160416    -1  -999.4010\n",
       "4   2015-12-30  000071    -2  -999.3060\n",
       "5   2016-01-11  000071    -1  -999.9840\n",
       "27  2016-01-11  001051    -1  -999.3600\n",
       "28  2016-02-01  001051    -1  -999.4530\n",
       "6   2016-02-15  000071    -2  -999.0960\n",
       "19  2016-02-29  000968    -1  -999.6951\n",
       "29  2016-03-01  001051    -1  -999.8640\n",
       "7   2016-03-01  000071    -1  -999.8780\n",
       "34  2016-03-01  001052    -1  -999.8080\n",
       "8   2016-04-01  000071    -1  -999.8560\n",
       "30  2016-04-01  001051    -1  -999.6960\n",
       "31  2016-05-03  001051    -1  -999.6660\n",
       "39  2016-05-09  001064    -1  -999.7470\n",
       "32  2016-06-15  001051    -1  -999.7390\n",
       "0   2016-06-24  000051    -1  -999.3240\n",
       "18  2016-09-14  000614    -1  -999.9640\n",
       "40  2016-11-01  001064    -1  -999.6664\n",
       "58  2016-12-01  050027    -1  -999.8720\n",
       "44  2016-12-15  001180    -1  -999.6030\n",
       "60  2016-12-15  100032    -1  -999.6250\n",
       "33  2016-12-15  001051     1  1255.6800\n",
       "61  2016-12-30  100032    -1  -999.6250\n",
       "45  2017-01-16  001180    -1  -999.6300\n",
       "62  2017-01-25  100032    -1  -999.6210\n",
       "..         ...     ...   ...        ...\n",
       "35  2017-05-23  001052    -1  -999.8030\n",
       "49  2017-05-23  001180    -1  -999.9415\n",
       "66  2017-06-30  110026    -1  -998.2550\n",
       "24  2017-07-17  000968    -1  -999.6987\n",
       "57  2017-07-31  003765    -1  -999.3600\n",
       "54  2017-07-31  003376    -1  -999.5425\n",
       "9   2017-07-31  000071     1  1258.0803\n",
       "50  2017-07-31  001180    -1  -999.4376\n",
       "37  2017-08-15  001061    -1  -999.1900\n",
       "69  2017-08-15  162411    -1  -999.9840\n",
       "41  2017-09-01  001064    -1  -999.3630\n",
       "13  2017-09-01  000478    -1  -998.0698\n",
       "38  2017-09-15  001061    -1  -999.1570\n",
       "14  2017-09-29  000478    -1  -997.9687\n",
       "11  2017-09-29  000216    -1  -999.1590\n",
       "70  2017-10-16  270048    -1  -999.9490\n",
       "59  2017-10-16  050027    -1  -999.1040\n",
       "71  2017-10-27  270048    -1  -999.4600\n",
       "15  2017-10-27  000478    -1  -997.6596\n",
       "36  2017-11-02  001052    -1  -999.7390\n",
       "42  2017-11-02  001064    -1  -999.8100\n",
       "55  2017-11-15  003376    -1  -999.6714\n",
       "10  2017-11-15  000071     1  1316.2221\n",
       "43  2017-11-30  001064    -1  -999.5583\n",
       "16  2017-11-30  000478    -2  -997.8840\n",
       "56  2017-11-30  003376    -1  -999.4560\n",
       "64  2017-12-14  100032    -1  -999.5300\n",
       "72  2017-12-14  502010    -1  -999.1872\n",
       "65  2017-12-29  100032    -1  -999.9840\n",
       "17  2017-12-29  000478    -1  -997.9264\n",
       "\n",
       "[73 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transcations_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# XIRR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.126315Z",
     "start_time": "2018-01-09T09:59:47.112279Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# https://stackoverflow.com/questions/8919718/financial-python-library-that-has-xirr-and-xnpv-function\n",
    "def xnpv(rate, values, dates):\n",
    "    '''Equivalent of Excel's XNPV function.\n",
    "\n",
    "    >>> from datetime import date\n",
    "    >>> dates = [date(2010, 12, 29), date(2012, 1, 25), date(2012, 3, 8)]\n",
    "    >>> values = [-10000, 20, 10100]\n",
    "    >>> xnpv(0.1, values, dates)\n",
    "    -966.4345...\n",
    "    '''\n",
    "    if rate <= -1.0:\n",
    "        return float('inf')\n",
    "    d0 = dates[0]    # or min(dates)\n",
    "    return sum([ vi / (1.0 + rate)**((di - d0).days / 365.0) for vi, di in zip(values, dates)])\n",
    "\n",
    "def xirr(values, dates):\n",
    "    '''Equivalent of Excel's XIRR function.\n",
    "\n",
    "    >>> from datetime import date\n",
    "    >>> dates = [date(2010, 12, 29), date(2012, 1, 25), date(2012, 3, 8)]\n",
    "    >>> values = [-10000, 20, 10100]\n",
    "    >>> xirr(values, dates)\n",
    "    0.0100612...\n",
    "    '''\n",
    "    try:\n",
    "        return optimize.newton(lambda r: xnpv(r, values, dates), 0.0)\n",
    "    except RuntimeError:    # Failed to converge?\n",
    "        return optimize.brentq(lambda r: xnpv(r, values, dates), -1.0, 1e10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.134315Z",
     "start_time": "2018-01-09T09:59:47.129028Z"
    }
   },
   "outputs": [],
   "source": [
    "dates = list(transcations_df['date'])\n",
    "values = list(transcations_df['money'])\n",
    "dates.append(status_df['date'].max())\n",
    "values.append(status_df['value'].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.145859Z",
     "start_time": "2018-01-09T09:59:47.136940Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年化收益率: 0.160825712598\n",
      "项目开始时间: 2015-08-03\n",
      "截至 2018-01-08 已运行 889 天\n"
     ]
    }
   ],
   "source": [
    "print('年化收益率:', xirr(values, dates))\n",
    "print('项目开始时间:', dates[0])\n",
    "print('截至 %s 已运行 %d 天' % (dates[-1], (dates[-1]-dates[0]).days))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:44:40.831157Z",
     "start_time": "2018-01-09T09:44:40.828482Z"
    }
   },
   "source": [
    "# 每月投入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.186704Z",
     "start_time": "2018-01-09T09:59:47.148725Z"
    }
   },
   "outputs": [],
   "source": [
    "transcations_df['month'] = transcations_df['date'].apply(lambda x: x.strftime('%Y%m'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.208225Z",
     "start_time": "2018-01-09T09:59:47.189959Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unit</th>\n",
       "      <th>money</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201508</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201509</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1998.7770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201511</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201512</th>\n",
       "      <td>-4</td>\n",
       "      <td>-2998.6150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201601</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1999.3440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201602</th>\n",
       "      <td>-4</td>\n",
       "      <td>-2998.2441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201603</th>\n",
       "      <td>-3</td>\n",
       "      <td>-2999.5500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201604</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1999.5520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201605</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1999.4130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201606</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1999.0630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201609</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.9640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201611</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.6664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201612</th>\n",
       "      <td>-3</td>\n",
       "      <td>-2743.0450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201701</th>\n",
       "      <td>-3</td>\n",
       "      <td>-2999.0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201702</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.8208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201703</th>\n",
       "      <td>-1</td>\n",
       "      <td>-999.5578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201704</th>\n",
       "      <td>-7</td>\n",
       "      <td>-6996.2426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201705</th>\n",
       "      <td>-6</td>\n",
       "      <td>-4999.2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201706</th>\n",
       "      <td>-1</td>\n",
       "      <td>-998.2550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201707</th>\n",
       "      <td>-3</td>\n",
       "      <td>-2739.9585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201708</th>\n",
       "      <td>-2</td>\n",
       "      <td>-1999.1740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201709</th>\n",
       "      <td>-5</td>\n",
       "      <td>-4993.7175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201710</th>\n",
       "      <td>-4</td>\n",
       "      <td>-3996.1726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201711</th>\n",
       "      <td>-6</td>\n",
       "      <td>-4679.8966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201712</th>\n",
       "      <td>-4</td>\n",
       "      <td>-3996.6276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        unit      money\n",
       "month                  \n",
       "201508    -1  -999.1910\n",
       "201509    -2 -1998.7770\n",
       "201511    -1  -999.1040\n",
       "201512    -4 -2998.6150\n",
       "201601    -2 -1999.3440\n",
       "201602    -4 -2998.2441\n",
       "201603    -3 -2999.5500\n",
       "201604    -2 -1999.5520\n",
       "201605    -2 -1999.4130\n",
       "201606    -2 -1999.0630\n",
       "201609    -1  -999.9640\n",
       "201611    -1  -999.6664\n",
       "201612    -3 -2743.0450\n",
       "201701    -3 -2999.0500\n",
       "201702    -1  -999.8208\n",
       "201703    -1  -999.5578\n",
       "201704    -7 -6996.2426\n",
       "201705    -6 -4999.2007\n",
       "201706    -1  -998.2550\n",
       "201707    -3 -2739.9585\n",
       "201708    -2 -1999.1740\n",
       "201709    -5 -4993.7175\n",
       "201710    -4 -3996.1726\n",
       "201711    -6 -4679.8966\n",
       "201712    -4 -3996.6276"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transcations_df.groupby('month')[['unit', 'money']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 输出CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-01-09T09:59:47.218518Z",
     "start_time": "2018-01-09T09:59:47.211950Z"
    }
   },
   "outputs": [],
   "source": [
    "status_df.to_csv('status.csv', index=False)\n",
    "transcations_df.to_csv('transcations.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}