sharedatacollect.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from alpha_vantage.timeseries import TimeSeries\n",
    "from pprint import pprint\n",
    "import pandas as pd\n",
    "import time\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "alpha_vantage\n",
    "https://alpha-vantage.readthedocs.io/en/latest/\n",
    "Python module to get stock data from the Alpha Vantage API Alpha Vantage http://www.alphavantage.co/ is a free JSON APIs for stock market data, plus a comprehensive set of technical indicators. This project is a python wrapper around this API to offer python plus json/pandas support. I hope you enjoy it. It requires a free API, that can be requested on http://www.alphavantage.co/support/#api-key.\n",
    "\n",
    "The code is hosted in github: https://github.com/RomelTorres/alpha_vantage \n",
    "\n",
    "the specific module: https://github.com/RomelTorres/alpha_vantage/blob/develop/alpha_vantage/timeseries.py  is well documented.   \n",
    "\n",
    "Note: Rather than the entire series of prices, the more efficent approach would be to fetch single values, using  def get_batch_stock_quotes(self, symbols): or def get_quote_endpoint(self, symbol): functions. hwr, the fucntionaly does not currently work for non-US shares.  So fetching a larger set anf triming to the last value is what is done below.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set some initial and trial values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Alphavantage_API_key = 'YOUR_API_KEY'\n",
    "Alphavantage_API_key = '21GBFSMHLICNID9Y'\n",
    "\n",
    "# symbols = ['HWDN.l','C'] # test data, keep for troubleshooting for problmes with share_data file\n",
    "\n",
    "datafile='share_data.csv'  # where the exising data is kept\n",
    "earningsfile = 'epsdiv.csv' #where Share Symbol, eps and div data is kept. Updated for each share after the company reports. (ca once per quarter)\n",
    "\n",
    "pause_call = 21 #time to wait between API calls a multiple of this is use on error to allow time for server to allo more calls \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getnewdata(symbol, days=1, apikey=Alphavantage_API_key):\n",
    "    ts = TimeSeries(key=apikey, output_format='pandas')\n",
    "    df, meta_df = ts.get_daily(symbol=symbol, outputsize='compact')\n",
    "    #df, meta_df = ts.get_batch_stock_quotes(symbols=shrsymbol)  # only works with US stocks\n",
    "    data = df.tail(days)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Keyword Arguments:\n",
    "    days: use in dataframe tail function, the number of data points \n",
    "    symbol:  the symbol for the equity we want to get its data\n",
    "    outputsize:  The size of the call, supported values are 'compact' and 'full; the first returns the last 100 points in the data series, and 'full' returns the full-length intraday times series, commonly above 1MB (default 'compact')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getexistingdata(datafile):\n",
    "    with open(datafile, 'r') as f:\n",
    "        data_existing = pd.read_csv(f, index_col='date') # set index to date as Alpha Vantage uses date as index\n",
    "        return data_existing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Main"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get new data using list, symbols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'date'",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-5-aebcab1ad722>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#Retreive the existing data sorred on disk in preperation for merging with the new data.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdata_existing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgetexistingdata\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdatafile\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;31m#pprint(data_existing)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-4-dcf9aa7d8d5a>\u001b[0m in \u001b[0;36mgetexistingdata\u001b[0;34m(datafile)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mgetexistingdata\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdatafile\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m     \u001b[0;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdatafile\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'r'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m         \u001b[0mdata_existing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex_col\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'date'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# set index to date as Alpha Vantage uses date as index\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mdata_existing\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, skip_footer, doublequote, delim_whitespace, as_recarray, compact_ints, use_unsigned, low_memory, buffer_lines, memory_map, float_precision)\u001b[0m\n\u001b[1;32m    707\u001b[0m                     skip_blank_lines=skip_blank_lines)\n\u001b[1;32m    708\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 709\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    710\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    711\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    453\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    454\u001b[0m     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 455\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mparser\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    456\u001b[0m     \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    457\u001b[0m         \u001b[0mparser\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m   1067\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'skipfooter not supported for iteration'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1068\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1069\u001b[0;31m         \u001b[0mret\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1070\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1071\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'as_recarray'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m   1879\u001b[0m                     \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1880\u001b[0m                 \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1881\u001b[0;31m                     \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex_col\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1882\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1883\u001b[0m                 values = self._maybe_parse_dates(values, i,\n",
      "\u001b[0;31mKeyError\u001b[0m: 'date'"
     ],
     "output_type": "error"
    }
   ],
   "source": [
    "#Retreive the existing data sorred on disk in preperation for merging with the new data.\n",
    "data_existing = getexistingdata(datafile)\n",
    "#pprint(data_existing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data_point = pd.DataFrame()  # creating an empty dataframe into which the new data will be placed "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data file:  'epsdiv.cdv'  Share symbols, eps and div data is held in a csv file.  This file can be edditied when the eps and div values change or when a new symbol is added or deleted. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read the symbols for each share to be used in the call to AV\n",
    "print('reading share Symbols to retreive from file', earningsfile)\n",
    "data = pd.read_csv(earningsfile,index_col=False) # creat a dataframe from the  csv file \n",
    "symbols = data['Symbol'].drop_duplicates().values.tolist()  # create a list of only the unique values in 'Symbol' column\n",
    "print('read ', len(symbols),' unique share symbols')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read the eps and div values for each share in to a dict \n",
    "print('reading share eps and div values for the Symbols from file', earningsfile )\n",
    "epsdiv=pd.read_csv(earningsfile, header=0, index_col=0).to_dict() #create a dict of the Symbols and eps, div values to be put in the new data frame with values retreived from AV\n",
    "#todo could the csv file be read in once to a dataframe an exported to the Symbol list and eps/div dict from the singel DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt = 1 # for a counter to track progress of the retreival process. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print('retreiving  data')\n",
    "for s in symbols:                    #iterate over the Symbols retreived from earningsfile\n",
    "    print(cnt,' of', len(symbols))\n",
    "    cnt =cnt+1\n",
    "    \n",
    "    try:\n",
    "        d = getnewdata(s)       # retreives dataframe of date and prices \n",
    "        d['eps'] = epsdiv['eps'][s]   #create a column 'eps' with the value from the dict epsdiv\n",
    "        d['PE']  = d['4. close']/epsdiv['eps'][s] #calc the PE and add to dataframe\n",
    "        d['div'] = epsdiv['div'][s]\n",
    "        d['yld'] = epsdiv['div'][s]/d['4. close']*100 #calc the yld and add to dataframe\n",
    "        d['Symbol']= str(s)\n",
    "    except Exception as E:\n",
    "        cnt_dwn = pause_call*3  #Pause the API calls if the rate was too high and tossed an error\n",
    "        print('error encounted while retreiving data ', s)\n",
    "        print(E)\n",
    "        print('retreval will pause for ', cnt_dwn,  'seconds' )\n",
    "        while cnt_dwn >0:\n",
    "            print(cnt_dwn)\n",
    "            time.sleep(1)\n",
    "            cnt_dwn = cnt_dwn -1 \n",
    "            \n",
    "    d.rename(columns={'1. open':'open','2. high':'high','3. low':'low','4. close':'close','5. volume':'volume'}, inplace=True)\n",
    "    #print('New data', s)\n",
    "    pprint(d)\n",
    "    print('')\n",
    " \n",
    "    data_existing = pd.concat([data_existing,d])\n",
    "    # print('data_existing.tail()',data_existing.tail())\n",
    "    #print('')\n",
    "    \n",
    "    time.sleep(pause_call)  #Time between API calls, the AV server limits the number of calls in a given time \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the 'Time Series (Daily) call fails at 25 calls with 15 sec intervals.  try greater time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_existing.drop_duplicates(keep='last', inplace=True)   # remove duplicate values, keeping the later. This allows earnings data to be updated in later runs\n",
    "print('data_existing after drop dups')\n",
    "#print(data_existing.tail(len(symbols)))\n",
    "#pprint(data_existing)\n",
    "print('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the data looks correct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('share_data.csv', 'a') as f:\n",
    "    data_existing.to_csv(f, header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}