Table of Contents Overview Usage Web API Examples Query Filter Strings Data Collection Service Installation Notes

Overview

Given a list of financial tickers, this Rails application will fetch stock, option, dividend, and split data from Yahoo Finance and store everything in a MySql Database. A backend web service provides filtered access to the data over a simple web-based API served as CSV, JSON, or XML formats.

Usage

Web API

Examples

• List all Stocks for which data is available

  # CSV Format Example
  wget -O - 'https://dataservice.yourdomain.com/stocks/index.csv'

  symbol
  AAPL
  ABC
  ABT
  ACE
  ACN
  ...

  # JSON Format Example
  wget -O - 'https://dataservice.yourdomain.com/stocks/index.json'

  [{"symbol":"A"},{"symbol":"AA"},{"symbol":"AAPL"},
  ...

  # XML Format Example
  wget -O - 'https://dataservice.yourdomain.com/stocks/index.xml'

 
  <?xml version="1.0" encoding="UTF-8"?>
  <stocks type="array">
    <stock>
      <symbol>AAPL</symbol>
    </stock>
    <stock>
      <symbol>ABC</symbol>
    </stock>
  ...

• Download stock data as CSV for a particular underlying between certain dates, limiting to 10 (change the export.SUFFIX for XML and JSON formats)

  wget -O - 'https://dataservice.yourdomain.com/stocks/export.csv?symbol=csco&start_date=2012-01-01&end_date=2012-02-01&limit=10'

  id,symbol,open,high,low,close,volume,date,created_at,updated_at
  319169,CSCO,1968,1995,1960,1979,52482100,2012-01-19,2012-03-26 06:54:34 UTC,2012-03-26 06:54:34 UTC
  319170,CSCO,1975,1993,1967,1992,42046700,2012-01-20,2012-03-26 06:54:34 UTC,2012-03-26 06:54:34 UTC
  319171,CSCO,1985,2007,1955,1982,50013100,2012-01-23,2012-03-26 06:54:34 UTC,2012-03-26 06:54:34 UTC
  ...

• Download a list of all option underlyings

  wget -O - 'https://dataservice.yourdomain.com/options/index.csv'

  underlying
  A
  AA
  AAPL
  ...

• Download a list of option symbols for a particular underlying

  wget -O - 'https://dataservice.yourdomain.com/options/index.csv?underlying=csco'

  symbol
  CSCO120330C00019000
  CSCO120330C00020000
  CSCO120330C00021000
  ...

• Download a list of options prices for a particular underlying and option put symbol between certain dates

  wget -O - 'https://dataservice.yourdomain.com/options/export.xml?underlying=csco&symbol=CSCO130119P00015000&underlying=csco&date=2012-03-26&start_date=2012-01-01&end_date=2013-01-01&limit=365&option_type=put'

 
  <?xml version="1.0" encoding="UTF-8"?>
  <options type="array">
    <option>
      <ask type="integer">44</ask>
      <bid type="integer">43</bid>
      <change type="integer">-3</change>
      <created-at type="datetime">2012-03-27T02:22:41Z</created-at>
      <date type="date">2012-03-26</date>
      <expiration type="date">2013-01-19</expiration>
      <id type="integer">73927</id>
      <interest type="integer">87972</interest>
      <option-type>put</option-type>
      <price type="integer">44</price>
      <strike type="integer">1500</strike>
      <symbol>CSCO130119P00015000</symbol>
      <underlying>CSCO</underlying>
      <updated-at type="datetime">2012-03-27T02:22:41Z</updated-at>
      <volume type="integer">835</volume>
    </option>
  </options>

Query Filter Strings

HTTP query strings allow filtering parameters

• Parameters for Stocks Export: https://dataservice.yourdomain.com/stocks/export.csv (see controller code in ~/finance-dataservice/app/controllers/stocks_controller.rb)
  • symbol - stock security symbol (e.g. csco, akam)
  • limit - maximum number of records to return (e.g. 10)
  • date - limit return of records to this specific date (e.g. 2011-12-31)
  • start_date - limit return of records after this inclusive date
  • end_date - limit return of records before this inclusive date
• Parameters for Options Export: https://dataservice.yourdomain.com/options/export.xml (see controller code in ~/finance-dataservice/app/controllers/options_controller.rb)
  • underlying - underlying stock symbol (e.g. csco, akam)
  • symbol - option security symbol (e.g. CSCO120330C00019000)
  • limit - maximum number of records to return (e.g. 10)
  • option_type - limit return of options records to "put" or "call" types
  • date - limit return of records to this specific date (e.g. 2011-12-31)
  • start_date - limit return of records after this inclusive date
  • end_date - limit return of records before this inclusive date
  • expiration - limit return of records to a specific expiration date
  • start_expiration - limit return of records after this inclusive expiration date
  • end_expiration - limit return of records after this inclusive expiration date
  • strike - limit return of records to a specific strike price
  • start_strike - limit return of records greater than this inclusive strike price
  • end_strike- limit return of records less than this inclusive strike price

Data Collection Service

• Edit the data collector script and set the list of hard-coded stock tickers that you want to collect data for

  emacs ~/finance-dataservice/bin/update.rb

• Run the data collector script (best to put in crond or a bash while loop)

  RAILS_ENV=production rake db:drop db:create db:migrate
  script/rails runner -e production bin/update.rb

Installation

• Install or Clone a new linux machine (Ubuntu 10.04 LTS)
• Install some utils

  sudo apt-get update
  sudo apt-get install curl git bash

• Install RVM

  bash -s stable < <(curl -s https://raw.github.com/wayneeseguin/rvm/master/binscripts/rvm-installer)
  source ~/.bashrc
  rvm requirements
    # Then follow the instructions
    sudo apt-get update
    sudo apt-get install build-essential openssl libreadline6 \
      libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev \
      libyaml-dev libsqlite3-0 libsqlite3-dev sqlite3 libxml2-dev \
      libxslt-dev autoconf libc6-dev ncurses-dev automake libtool \
      bison subversion

• Install MySql

  sudo apt-get install mysql-server mysql-client libmysql-ruby libmysqlclient-dev
  sudo apt-get install nodejs

• Install Ruby

  rvm install 1.9.3

• Set the default ruby to be your favorite version

  rvm --default use 1.9.3

• Install missing gems

  # Install one in particular, which requires an argument
  gem install ruby-debug19 -- --with-ruby-include=\$rvm_path/src/ruby-1.9.3-p125 
  gem install linecache19 -- --with-ruby-include=\$rvm_path/src/ruby-1.9.3-p125
  # then install the rest
  bundle install

• Clone the repository

  git clone git://github.com/dcwangmit01/finance-dataservice.git
  git config --global user.name "First Last"
  git config --global user.email "[email protected]"

• Test

Notes

License

• http://www.opensource.org/licenses/mit-license.php

This project is not production quality code.

• Regarding quality: I wouldn't count on this home project, whose purpose is to understand the Ruby and Rails hype.
• The code is ugly and I don't plan on investing in this project beyond what is minimally useful, so feel free to clone and own it.

Issues

• The default Rails WEBrick server leaks memory and eventually crashes. You should use a different server.
• The RESTful webapi is susceptible to SQL injection attacks, since HTTP GET args are fed directly to the DB query.
• Please make sure you protect the webapi with SSL and at least HTTP Basic Auth.
• I run the data gathering script in a bash while loop. It probably needs to be a crond task or a Ruby "service".
• Log files are unmanaged
• There is some leftover code for using Google Finance as the data provider. It's partially broken. However, the Google Finance provides seriously inaccurate data... outright missing historical data etc. Don't use it.
• Library code for gathering dividends and splits from Yahoo is written, but not hooked up with the data collector script.