data_wrangling/api/data_wrangling_using_api.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This exercise will require you to pull some data from the Qunadl API. Qaundl is currently the most widely used aggregator of financial market data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a first step, you will need to register a free account on the http://www.quandl.com website."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After you register, you will be provided with a unique API key, that you should store:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the API key as a string - according to PEP8, constants are always named in all upper case\n",
    "import os\n",
    "API_KEY = os.environ[\"QUANDL_API_KEY\"] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Qaundl has a large number of data sources, but, unfortunately, most of them require a Premium subscription. Still, there are also a good number of free datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this mini project, we will focus on equities data from the Frankfurt Stock Exhange (FSE), which is available for free. We'll try and analyze the stock prices of a company called Carl Zeiss Meditec, which manufactures tools for eye examinations, as well as medical lasers for laser eye surgery: https://www.zeiss.com/meditec/int/home.html. The company is listed under the stock ticker AFX_X."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find the detailed Quandl API instructions here: https://docs.quandl.com/docs/time-series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While there is a dedicated Python package for connecting to the Quandl API, we would prefer that you use the *requests* package, which can be easily downloaded using *pip* or *conda*. You can find the documentation for the package here: http://docs.python-requests.org/en/master/ "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, apart from the *requests* package, you are encouraged to not use any third party Python packages, such as *pandas*, and instead focus on what's available in the Python Standard Library (the *collections* module might come in handy: https://pymotw.com/3/collections/).\n",
    "Also, since you won't have access to DataFrames, you are encouraged to us Python's native data structures - preferably dictionaries, though some questions can also be answered using lists.\n",
    "You can read more on these data structures here: https://docs.python.org/3/tutorial/datastructures.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Keep in mind that the JSON responses you will be getting from the API map almost one-to-one to Python's dictionaries. Unfortunately, they can be very nested, so make sure you read up on indexing dictionaries in the documentation provided above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, import the relevant modules\n",
    "import requests\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, call the Quandl API and pull out a small sample of the data (only one day) to get a glimpse\n",
    "# into the JSON structure that will be returned\n",
    "quandl_afx_base_url = 'https://www.quandl.com/api/v3/datasets/FSE/AFX_X'\n",
    "url_params = {\n",
    "    'start_date' : datetime.strftime(datetime.now() - timedelta(1), '%Y-%m-%d'),\n",
    "    'end_date' : datetime.strftime(datetime.now() - timedelta(1), '%Y-%m-%d'),\n",
    "    'api_key' : API_KEY\n",
    "}\n",
    "response = requests.get(quandl_afx_base_url, params=url_params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'dataset': {'id': 10095370,\n",
       "  'dataset_code': 'AFX_X',\n",
       "  'database_code': 'FSE',\n",
       "  'name': 'Carl Zeiss Meditec (AFX_X)',\n",
       "  'description': 'Stock Prices for Carl Zeiss Meditec (AFX) from the Frankfurt Stock Exchange.<br><br>Trading System: Xetra<br><br>ISIN: DE0005313704',\n",
       "  'refreshed_at': '2019-05-17T22:34:17.691Z',\n",
       "  'newest_available_date': '2019-05-17',\n",
       "  'oldest_available_date': '2000-06-07',\n",
       "  'column_names': ['Date',\n",
       "   'Open',\n",
       "   'High',\n",
       "   'Low',\n",
       "   'Close',\n",
       "   'Change',\n",
       "   'Traded Volume',\n",
       "   'Turnover',\n",
       "   'Last Price of the Day',\n",
       "   'Daily Traded Units',\n",
       "   'Daily Turnover'],\n",
       "  'frequency': 'daily',\n",
       "  'type': 'Time Series',\n",
       "  'premium': False,\n",
       "  'limit': None,\n",
       "  'transform': None,\n",
       "  'column_index': None,\n",
       "  'start_date': '2019-05-18',\n",
       "  'end_date': '2019-05-17',\n",
       "  'data': [],\n",
       "  'collapse': None,\n",
       "  'order': None,\n",
       "  'database_id': 6129}}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inspect the JSON structure of the object you created, and take note of how nested it is,\n",
    "# as well as the overall structure\n",
    "response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(response.json())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are your tasks for this mini project:\n",
    "\n",
    "1. Collect data from the Franfurt Stock Exchange, for the ticker AFX_X, for the whole year 2017 (keep in mind that the date format is YYYY-MM-DD).\n",
    "2. Convert the returned JSON object into a Python dictionary.\n",
    "3. Calculate what the highest and lowest opening prices were for the stock in this period.\n",
    "4. What was the largest change in any one day (based on High and Low price)?\n",
    "5. What was the largest change between any two days (based on Closing Price)?\n",
    "6. What was the average daily trading volume during this year?\n",
    "7. (Optional) What was the median trading volume during this year. (Note: you may need to implement your own function for calculating the median.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Task 1: Collect data from the Franfurt Stock Exchange, for the ticker AFX_X, for the whole year 2017 (keep in mind that the date format is YYYY-MM-DD)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "quandl_afx_base_url = 'https://www.quandl.com/api/v3/datasets/FSE/AFX_X'\n",
    "\n",
    "url_params = {\n",
    "    'start_date': '2017-01-01',\n",
    "    'end_date' : '2017-12-31',\n",
    "    'api_key' : API_KEY\n",
    "}\n",
    "response = requests.get(quandl_afx_base_url, params=url_params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All good!\n"
     ]
    }
   ],
   "source": [
    "if response.status_code != 200:\n",
    "    print(\"Something went wrong!\")\n",
    "else:\n",
    "    print(\"All good!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2: Convert the returned JSON object into a Python dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = response.json()\n",
    "type(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['dataset'])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['id', 'dataset_code', 'database_code', 'name', 'description', 'refreshed_at', 'newest_available_date', 'oldest_available_date', 'column_names', 'frequency', 'type', 'premium', 'limit', 'transform', 'column_index', 'start_date', 'end_date', 'data', 'collapse', 'order', 'database_id'])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['dataset'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2017-12-29',\n",
       " 51.76,\n",
       " 51.94,\n",
       " 51.45,\n",
       " 51.76,\n",
       " None,\n",
       " 34640.0,\n",
       " 1792304.0,\n",
       " None,\n",
       " None,\n",
       " None]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['dataset']['data'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to understand columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Date',\n",
       " 'Open',\n",
       " 'High',\n",
       " 'Low',\n",
       " 'Close',\n",
       " 'Change',\n",
       " 'Traded Volume',\n",
       " 'Turnover',\n",
       " 'Last Price of the Day',\n",
       " 'Daily Traded Units',\n",
       " 'Daily Turnover']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['dataset']['column_names']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = data['dataset']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3: Calculate what the highest and lowest opening prices were for the stock in this period."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "open is the second column in the data row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2017-12-29',\n",
       " 51.76,\n",
       " 51.94,\n",
       " 51.45,\n",
       " 51.76,\n",
       " None,\n",
       " 34640.0,\n",
       " 1792304.0,\n",
       " None,\n",
       " None,\n",
       " None]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "afx_data = dataset['data']\n",
    "afx_data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "255"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(afx_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Opening price for 29 Dec 2017:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51.76"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "afx_data[0][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Max open price:  53.11\n",
      "Min open price:  34.0\n"
     ]
    }
   ],
   "source": [
    "max_open_price = max(data_row[1] for data_row in afx_data if data_row[1] is not None)\n",
    "min_open_price = min(data_row[1] for data_row in afx_data if data_row[1] is not None)\n",
    "print(\"Max open price: \", max_open_price)\n",
    "print(\"Min open price: \", min_open_price)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems we might have none values for the opening price, lets print those dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "none_dates = [data_row for data_row in afx_data if data_row[1] is None]\n",
    "len(none_dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['2017-05-01', None, 42.245, 41.655, 41.72, -0.44, 86348.0, 3606589.0, None, None, None], ['2017-04-17', None, 42.48, 41.985, 42.2, None, 88416.0, 3734717.0, None, None, None], ['2017-04-14', None, 42.48, 41.985, 42.2, None, 88416.0, 3734717.0, None, None, None]]\n"
     ]
    }
   ],
   "source": [
    "print(none_dates)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4: What was the largest change in any one day (based on High and Low price)?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "High and low price for AFX on 29th Dec, 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "high: 51.94 low: 51.45\n"
     ]
    }
   ],
   "source": [
    "print(\"high:\", afx_data[0][2], \"low:\", afx_data[0][3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_diff = max(data_row[2]-data_row[3] for data_row in afx_data if data_row[2] is not None and data_row[3] is not None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Largest value change in one day: 2.8100000000000023\n"
     ]
    }
   ],
   "source": [
    "print(\"Largest value change in one day:\", max_diff)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 5: What was the largest change between any two days (based on Closing Price)?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "closing price for AFX on 29th Dec, 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51.76"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "afx_data[0][4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Largest change between any two days: 2.559999999999995\n"
     ]
    }
   ],
   "source": [
    "max_change = 0\n",
    "for i in range(1, len(afx_data)):\n",
    "    diff_close_price = afx_data[i][4] - afx_data[i-1][4]\n",
    "    if abs(diff_close_price) > max_change:\n",
    "        max_change = abs(diff_close_price)\n",
    "print(\"Largest change between any two days:\", max_change)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 6: What was the average daily trading volume during this year?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trading volume of AFX on 29th Dec 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34640.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "afx_data[0][6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average daily trading volume during 2017 year for AFX:  89124.33725490196\n"
     ]
    }
   ],
   "source": [
    "average_daily_trading_volume = sum([data_row[6] for data_row in afx_data])/len(afx_data)\n",
    "print(\"Average daily trading volume during 2017 year for AFX: \", average_daily_trading_volume)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7: What was the median trading volume during this year. (Note: you may need to implement your own function for calculating the median.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n"
     ]
    }
   ],
   "source": [
    "#test median\n",
    "data = [4, 5, 19, 3, 42, 7, 11]\n",
    "from statistics import median\n",
    "print(median(data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Median trading volume for AFX during 2017:  76286.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Median trading volume for AFX during 2017: \", median([data_row[6] for data_row in afx_data]))"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}