Database and Models

{db.rb 2}

require 'sqlite3'
require 'sequel'
require 'alpaca/trade/api'

DB = Sequel.connect "sqlite://#{CONFIG[:DB][:path]}"

{Bar table, 3}

{Ticker table, 8}

{Split table, 15}

# Load the models for handling the data
require './models/ticker.rb'
require './models/split.rb'
require './models/bar.rb'

{Alpaca Ruby API integration, 17}

{Bar table 3}

DB.create_table? :bars do
  primary_key :id
  foreign_key :ticker_id, :tickers
  float :close
  float :high
  float :low
  float :open
  datetime :datetime
  integer :volume
  string :span # day, 15 min, 5 min, 1 min
  integer :rank
  float :value

  index :ticker_id
end

Used in section 2

{models/bar.rb 4}

class Bar < Sequel::Model
  many_to_one :ticker

  {Alpaca Bar Integration, 18}

  def inspect
    "#<Bar id => #{id}, sym => #{ticker.symbol}, date => #{date.strftime "%Y-%m-%d"}, o => #{open}, c => #{close}, h => #{high}, l => #{low}>"
  end

  # GREAT trick here: the goal is to figure out how many records
  # exist between `self` and `from`. Since a simple date calculation
  # won't work, we just ask the DB to see what's in store for us.
  #
  # This originally loaded up `ticker.bars` and then got the indices
  # for the two elements, but then that got turned into querying the DB
  # directly and operating on that list, but then I realized it was the
  # size of that array minus 1, and then I realized I could just get the
  # count directly from the DB.
  def trading_days_from(from)
    raise unless from.ticker == ticker

    dates = [date, from.date].sort

    Bar.where(:ticker => ticker, :date => dates[0]..dates[-1])
       .order(Sequel.asc(:date))
       .count - 1
  end

  {Measurements of Future Bar Change, 6}

  {Measurements of Past Bar Performance, 7}
end

{Measurements of Future Bar Change 6}

  # the "rise" part of the name is baked into the `>=`
  # How many trading days does it take to rise by `percent`?
  # Returns -1 if it never does.
  def time_to_rise(percent)
    #bars  = self.ticker.bars
    bars  = Bar.where(:ticker => ticker) { date >= self.date }
               .order(Sequel.asc(:date))
               .all
    index = bars.index self
    #i = bars[index..-1].index

    (index..bars.size - 1).each do |j|
      if bars[j].change_from(bars[index]) >= percent
        #if self.before_split?(:days => j - index)
        #  return -2
        #else
          return j - index
        #end
      end
    end

    -1
  end

  # What is the maximum percent rise when compared to `self` over the next
  # `days` trading days?
  def max_rise_over(days)
    bars  = Bar.where(:ticker => ticker,
                      :date => date..(date + (days * 1.4).ceil * 86_400))
               .order(Sequel.asc(:date))
               .all
    index = bars.index self
    range = bars[index..(index + days)]
    range.map {|b| [b, b.change_from(self)] }.max_by {|a| a[1] }
  end

  # When is the first two-day period that drops by `drop`, after this bar?
  def drop(drop)
    bars  = Bar.where(:ticker => ticker) { date >= self.date }
               .order(Sequel.asc(:date))
               .all
    i    = bars.index self
    fin  = bars.size - 1
    bars = bars[i..fin]

    # oldest bar is first
    bars.each_cons(2).find do |span|
      span[-1].change_from(span[0]) <= drop
    end
  end

  # When is the first two-day period that rises by `rise`, after this bar?
  def rise(rise)
    bars  = Bar.where(:ticker => ticker) { date >= self.date }
               .order(Sequel.asc(:date))
               .all
    i    = bars.index self
    fin  = bars.size - 1
    bars = bars[i..fin]

    # oldest bar is first
    bars.each_cons(2).find do |span|
      span[-1].change_from(span[0]) >= rise
    end
  end

Used in section 4

{Measurements of Past Bar Performance 7}

  def change_from(bar)
    (close - bar.open).to_f / bar.open
  end

  def rsquared(prior: 10)
    ticker.rsquared(:at => self, :prior => prior)
  end

  def mse(prior: 10)
    ticker.mse(:at => self, :prior => prior)
  end

  def history(prior: 10, post: 0)
    ticker.history(:around => self, :prior => prior, :post => post)
  end

  def volumes(prior: 10)
    history(:prior => prior).map {|b| b.volume }
  end

  def regression(prior: 10)
    ticker.regression(:at => self, :prior => prior)
  end

Used in section 4

{Ticker table 8}

DB.create_table? :tickers do
  primary_key :id
  string :symbol
  string :exchange
end

Used in section 2

{models/ticker.rb 9}

class Ticker < Sequel::Model
  one_to_many :bars, :order => :date
  one_to_many :splits, :order => :date

  {Measurement of Ticker Ranking, 10}

  {Measurements of Past Ticker Performance, 11}

  {Measurements of Future Ticker Change, 12}

  {Split Handling, 13}

  {AlphaVantage Integration, 14}

  # hook to ensure no orphans
  def before_destroy
    splits.map {|s| s.destroy }
    bars.map {|b| b.destroy }
    super
  end
end

{Measurement of Ticker Ranking 10}

  # Return N_trade / P_day rankings
  #
  # This would be SO MUCH FASTER if I just wrote the SQL by hand (since Sequel
  # doesn't allow me to do GROUP BY and AVG)
  def self.rankings(stocks: nil, date: Time.parse(Date.today.to_s), prior: 10)
    @@rankings ||= {}
    return @@rankings[[stocks, date, prior]] if @@rankings[[stocks, date, prior]]

    tids = stocks.map {|t| t.id}

    query = DB[:bars].where(:ticker_id => tids, :date => (date - prior.days)..date)
                     .group(:ticker_id)
                     .select_append(:ticker_id)
                     .sql
    query.gsub! "*", "AVG(`volume`)"
    volumes = DB.fetch(query).all.inject({}) do |vols, hash|
      vols[hash[:ticker_id]] = hash[:"AVG(`volume`)"]
      vols
    end

    closes = DB[:bars].select(:close, :ticker_id)
                      .where(:ticker_id => tids, :date => date)
                      .all
    closes = closes.inject({}) do |cls, hash|
      cls[hash[:ticker_id]] = hash[:close]
      cls
    end

    # {"SYM" => [Rank, Value]}
    ranks = {}

    values = {}
    stocks.each do |stock|
      if volumes[stock.id] == nil || closes[stock.id] == nil
        values[stock] = 0
      else
        values[stock] = volumes[stock.id] / closes[stock.id]
      end
    end
    sorted_values = values.values.sort.reverse

    values.each {|tick, value| ranks[tick.id] = [sorted_values.index(value), value] }

    @@rankings[[stocks, date, prior]] = ranks
  end

Used in section 9

{Measurements of Past Ticker Performance 11}

  # bar history based on a bar
  def history(around: nil, prior: 10, post: 5)
    idx  = around ? bars.index(around) : -1
    bars[[idx - prior + 1, 0].max..(idx + post)]
  end

  def volumes(at: nil, prior: 10, post: 0)
    history(:around => at, :prior => prior, :post => post).map {|b| b.volume }
  end

  # This is an absolute value, so a line with 3% shift of a trend at $600 will
  # have a greater MSE than a line with 3% shift of a trend at $6.
  # The cheaper a stock is, the more volatility it is allowed to have.
  def mse(at: nil, prior: 10)
    regression(:at => at, :prior => prior).meanSqError
  end

  # r^2 = 1 - NMSE
  #
  # Since I was originally using MSE and then I wanted to normalize it,
  # r^2 became an easier figure to use. Still gotta write my own function
  # for it (and particularly NMSE) so I can claim I understand it.
  def rsquared(at: nil, prior: 10)
    regression(:at => at, :prior => prior).rSquared
  end

  def regression(at: nil, prior: 10)
    domain   = history(:around => at, :prior => prior, :post => 0)[0..-2]
    prices   = domain.map {|b| [b.open, b.close] }.flatten
    xs       = (1..prices.size).to_a

    line_fit = LineFit.new
    line_fit.setData xs, prices 
    line_fit
  end

Used in section 9

{Measurements of Future Ticker Change 12}

  # Find all 2-day drops of at least `drop`
  def drops(drop)
    # oldest bar is first
    bars.each_cons(2).filter do |span|
      span[-1].change_from(span[0]) <= drop
    end
  end

  # Find all 2-day rises of at least `rise`
  def rises(rise)
    # oldest bar is first
    bars.each_cons(2).filter do |span|
      span[-1].change_from(span[0]) >= rise
    end
  end

Used in section 9

{Split Handling 13}

  # normalize the prices to get rid of splits
  # percentage drops will still be evident
  #
  # THIS SHOULD BE RARELY CALLED
  # THE DATA SHOULD BE STORED IN ITS NORMALIZED FORM
  def normalize!(debug: false)
    # operating on hashes and optimized to minimize calls to the DB
    # and also minimizing the number of objects created
    ticker.splits.each do |split|
      next if split.applied

      unnorm_size = DB[:bars].where(:ticker_id => id,
                                    :date => Time.parse('1 jan 1900')..split[:date])
                             .count

      next unless unnorm_size >= 2

      unnormal = DB[:bars].where(:ticker_id => id,
                                 :date => (split[:date] - 30 * 86400)..split[:date])
                          .order(Sequel.asc(:date))
                          .all
      ratio = unnormal[-1][:open] / unnormal[-2][:close]

      puts "\tupdating #{unnorm_size} bars" if debug

      DB[:bars].where(:ticker_id => id,
                      :date => Time.parse('1 jan 1900')..(split[:date] - 1.day))
               .update(:close => Sequel[:close] * ratio,
                       :open  => Sequel[:open]  * ratio,
                       :high  => Sequel[:high]  * ratio,
                       :low   => Sequel[:low]   * ratio)

      split.applied = true
      split.save
    end
  end

  def normalized?; splits.all {|s| s.applied } ; end

Used in section 9

{AlphaVantage Integration 14}

  def download!(since: '2008-01-01')
    stock  = AV_CLIENT.stock :symbol => symbol
    series = stock.timeseries :outputsize => 'full'

    bars = series.output['Time Series (Daily)']
    bars = bars.filter {|k, bar| k > since }

    insertion = bars.map do |k, bar|
      {:date   => Time.parse(k),
       :open   => bar['1. open'].to_f,
       :high   => bar['2. high'].to_f,
       :low    => bar['3. low'].to_f,
       :close  => bar['4. close'].to_f,
       :volume => bar['5. volume'].to_i,
       :span   => 'day',
       :ticker_id => id
      }
    end
    DB[:bars].multi_insert insertion
  end

Used in section 9

{Split table 15}

DB.create_table? :splits do
  primary_key :id
  foreign_key :ticker_id, :tickers
  string :ratio
  datetime :date

  index :ticker_id
end

Used in section 2

{models/split.rb 16}

class Split < Sequel::Model
  many_to_one :ticker
end

{Alpaca Ruby API integration 17}

class Alpaca::Trade::Api::Bar
  def date; @time; end

  def save(symbol, period)
    ::Bar.create :span   => period,
                 :close  => @close,
                 :high   => @high,
                 :low    => @low,
                 :open   => @open,
                 :date   => @time,
                 :volume => @volume,
                 :ticker_id => ::Ticker.where(:symbol => symbol).first.id
  end
end

class Numeric
  # useful for dealing with Time
  def days
    self * 86400.0
  end
  alias_method :day, :days
end

Used in section 2

{Alpaca Bar Integration 18}

  def self.download(tickers, opts={})
    span = opts.delete(:span) || 'day'

    opts.each do |k, v| 
      if [String, Date, DateTime, Time].include? v.class
        opts[k] = DateTime.parse(v.to_s).to_s
      end
    end

    # `CLIENT.bars` returns a hash, so this will also merge them all
    # into one. key collision will only happen if the key is duplicated
    # in the `ticker` argument.
    symbols = tickers.map {|t| t.symbol }
    symbols.each_slice(50).map do |ticks|
      CLIENT.bars span, ticks, opts
    end.inject({}) {|h, v| h.merge v }
  end

Used in section 4