GemelNet.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How to download data:\n",
    "- Go to: (https://gemelnet.cma.gov.il/views/dafmakdim.aspx)[https://gemelnet.cma.gov.il/views/dafmakdim.aspx]\n",
    "- choose the desired Kranot\n",
    "- Switch to a custom period (instead of 1-year)\n",
    "- Run the following JS in the console\n",
    "\n",
    "```javascript\n",
    "document.getElementById(\"inout\").value=\"..\"\n",
    "function work(year, month) {\n",
    "        actualYear = 2018-year\n",
    "        actualMonth = 1 + month\n",
    "        console.log(\"fetching \" + actualYear + \", \" + actualMonth)\n",
    "        $(\"#ddlbHodashimMe\")[0].selectedIndex = month\n",
    "        $(\"#ddlbHodashimAd\")[0].selectedIndex = month\n",
    "        $(\"#ddlbShanimMe\")[0].selectedIndex = year\n",
    "        $(\"#ddlbShanimAd\")[0].selectedIndex = year\n",
    "\n",
    "        if (actualYear == 2018 && actualMonth >= 6) {\n",
    "            console.log(\"skipping ...\")\n",
    "            month += 1\n",
    "            if (month == 12) {\n",
    "               month = 0\n",
    "               year += 1\n",
    "            }\n",
    "            work(year, month)\n",
    "        }\n",
    "        else {\n",
    "            document.getElementById(\"cbDisplay\").click()\n",
    "            console.log(\"submitted\")\n",
    "        }\n",
    "\n",
    "        setTimeout(function() {\n",
    "           $find('ReportViewer1').exportReport('CSV');\n",
    "           console.log(\"downloaded \" + actualYear + \", \" + actualMonth)\n",
    "\n",
    "            month += 1\n",
    "            if (month == 12) {\n",
    "               month = 0\n",
    "               year += 1\n",
    "            }\n",
    "            work(year, month)\n",
    "        }, 70000)\n",
    "}\n",
    "work(0, 0)\n",
    "// earliest supported year is 1999\n",
    "\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To process all the files and merge to a single table, run the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-21T20:01:21.463538Z",
     "start_time": "2018-07-21T20:01:21.185981Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline  \n",
    "\n",
    "pd.set_option('display.height', 1000)\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-21T20:01:21.525260Z",
     "start_time": "2018-07-21T20:01:21.465397Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "\n",
    "def process(filepath):\n",
    "    with open(filepath, \"r\") as f:\n",
    "        lines = f.readlines()\n",
    "\n",
    "    goodlines = []\n",
    "    good = False\n",
    "    for l in lines:\n",
    "        l = l.strip()\n",
    "        if not good and l == '':\n",
    "            good = True\n",
    "            continue\n",
    "        if good and l == '':\n",
    "            break\n",
    "        if len(l.split(\",\")) == 1:\n",
    "            continue\n",
    "        if good:\n",
    "            l = l.replace(\"\\n\", \" \")\n",
    "            goodlines.append(l)\n",
    "\n",
    "\n",
    "    TESTDATA = StringIO(\"\\n\".join(goodlines))\n",
    "    df = pd.read_csv(TESTDATA)\n",
    "    \n",
    "    df = df.rename({\"Textbox89\": \"month_return\", \"SHM2\": \"name\", \"Textbox90\": \"active\", \"ID2\": \"ID\",\n",
    "               \"Textbox77\": \"liquidity\", \"Textbox78\": \"net_flow\", \"Textbox83\": \"AUM\", \"Textbox84\": \"fee_assets\",\n",
    "               \"Textbox103\": \"fee_deposits\", \"Textbox85\": \"sharpe_5y\", \"Textbox86\": \"sector_median_sharpe_5y\",\n",
    "               \"Textbox87\": \"yearly_alpha_5y\", \"Textbox88\": \"YTD_return\"}, axis=1)\n",
    "    \n",
    "    df = df[list(c for c in df.columns if \"Textbox\" not in c)]\n",
    "    \n",
    "    dateStr = lines[1].split(\",\")[0].strip().split(' ')[-1]\n",
    "    date = pd.to_datetime(dateStr, format=\"%m/%Y\")\n",
    "    df.loc[:, \"month\"] = date\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-21T20:01:30.771884Z",
     "start_time": "2018-07-21T20:01:21.546572Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "\n",
    "path = \"data/part*/*.csv\"\n",
    "alldfs = []\n",
    "i = 0\n",
    "for filename in glob.iglob(path, recursive=True):\n",
    "    df = process(filename)\n",
    "    alldfs.append(df)\n",
    "    i += 1\n",
    "    #if i == 3:\n",
    "    #    break\n",
    "    \n",
    "    \n",
    "mdf = pd.concat(alldfs)\n",
    "mdf = mdf.sort_values([\"month\", \"name\"])\n",
    "mdf = mdf.reset_index().drop(\"index\",axis=1)\n",
    "\n",
    "def markType(filename, typeName):\n",
    "    df = process(filename)\n",
    "    kranot = set(df[\"name\"].values)\n",
    "    mdf.loc[mdf[\"name\"].isin(kranot), \"type\"] = typeName\n",
    "\n",
    "markType(\"data/types/KranotHishtalmut.csv\", \"קרן השתלמות\")\n",
    "markType(\"data/types/GemelHashkaa.csv\", \"קופת גמל להשקעה\")\n",
    "markType(\"data/types/HisahonYeled.csv\", \"קופג להשקעה - חסכון לכל ילד\")\n",
    "markType(\"data/types/MerkazitPitsuim.csv\", \"מרכזית לפיצויים\")\n",
    "markType(\"data/types/TagmulimIshitPitsuim.csv\", \"תגמולים ואישית לפיצויים\")\n",
    "mdf.to_csv(\"data/GemelNet.csv\")\n"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}