<file_sep>class Statement < ApplicationRecord
  has_many :data

  validates :statement, presence: true, uniqueness: true
end
<file_sep>class AddIndexToYearQuarters < ActiveRecord::Migration[5.2]
  def change
    add_index :year_quarters, [:year_id, :quarter_id], unique: true
  end
end
<file_sep>class Quarter < ApplicationRecord
  has_many :data
  has_many :year_quarters
  has_many :years, through: :year_quarters

  validates :quarter, presence: true, uniqueness: true
end
<file_sep>class Datum < ApplicationRecord
  belongs_to :statement
  belongs_to :year
  belongs_to :quarter
  belongs_to :company
  belongs_to :page

  validates_associated :statement
  validates_associated :year
  validates_associated :quarter
  validates_associated :company
  validates_associated :page
end
<file_sep>class CreateYears < ActiveRecord::Migration[5.2]
  def change
    create_table :years do |t|
      t.column :year, 'char(2)', null: false

      t.index :year, unique: true
    end
  end
end
<file_sep>class CreateData < ActiveRecord::Migration[5.2]
  def change
    create_table :data do |t|
      t.integer :row_index, null: false
      t.integer :column_index, null: false
      t.decimal :amount, null: false
      t.references :statement, foreign_key: true
      t.references :year, foreign_key: true
      t.references :quarter, foreign_key: true
      t.references :company, foreign_key: true
      t.references :page, foreign_key: true
    end
  end
end
<file_sep># This file should contain all the record creation needed to seed the database with its default values.
# The data can then be loaded with the rails db:seed command (or created alongside the database with db:setup).
#
# Examples:
#
#   movies = Movie.create([{ name: 'Star Wars' }, { name: 'Lord of the Rings' }])
#   Character.create(name: 'Luke', movie: movies.first)

require 'csv'

path = "app/data/"

start = Time.now

# helpers
def fast_insert(insert_model, column, values)
  values -= insert_model.pluck(column)
  insert_values = values.map{|value| "(#{value})"}.join(",")
  return if insert_values.empty?
  sql = "INSERT INTO #{insert_model.table_name} (#{column}) VALUES #{insert_values}"
  ActiveRecord::Base.connection.execute(sql)
end

def fast_insert_multiple(insert_model, unique_column, values)
  conn = ActiveRecord::Base.connection
  existing_values = insert_model.pluck(unique_column)
  columns = values.first.map{ |k,v| k.to_s }.join(",")
  values.delete_if{ |item| existing_values.include?(item[unique_column.to_sym]) }
  values.map{ |item| "(#{item.map{ |k,v| conn.quote(v) }.join(',')})" }.join(",")
  insert_values = values.map{ |item| "(#{item.map{ |k,v| conn.quote(v) }.join(',')})" }.join(",")
  return if insert_values.empty?
  sql = "INSERT INTO #{insert_model.table_name} (#{columns}) VALUES #{insert_values}"
  conn.execute(sql)
end

def fast_insert_data(values)
  conn = ActiveRecord::Base.connection
  insert_model = Datum
  insert_model.delete_all
  columns = values.first.map{ |k,v| k.to_s }.join(",")
  values.map{ |item| "(#{item.map{ |k,v| conn.quote(v) }.join(',')})" }.join(",")
  insert_values = values.map{ |item| "(#{item.map{ |k,v| conn.quote(v) }.join(',')})" }.join(",")
  return if insert_values.empty?
  sql = "INSERT INTO #{insert_model.table_name} (#{columns}) VALUES #{insert_values}"
  ActiveRecord::Base.connection.execute(sql)
  # p sql
end

def create_lookup_hash(model, lookup_column )
  result = Hash.new
  model.pluck(lookup_column, :id).each{ |k,v| result[k]=v }
  return result
end

# static data definitions
years = ("10".."18").to_a
quarters = ("1".."4").to_a
statements = [
  { name: "Canadian P&C Companies", code: "10" },
  { name: "Foreign P&C Companies", code: "20" },
]
pages = [
  { code: "6020", name: "Consolidated Premiums and Claims" },
  { code: "6021", name: "Claims Incurred - Discounted" },
  { code: "6030", name: "Claims and Adjustment Expenses - Paid, Current Year and Unpaid, Current and Prior Year" },
]

# parse data from files
# companies
companies = []
years.each { |year|
  filename = "#{path}20#{year} P&C Companies.csv"
  if File.exist?(filename) then
    count = %x{wc -l '#{filename}'}.split.first.to_i
    p "#{filename} company records found: " + count.to_s
    CSV.foreach(filename, :headers => true, encoding:'iso-8859-1:utf-8') { |row|
      companies.push({ code: row.field('Code'), name: row.field('Company') })
    }
  end
}

# database insertions
fast_insert(Year, "year", years)
fast_insert(Quarter, "quarter", quarters)
fast_insert_multiple(Statement, "code", statements)
fast_insert_multiple(Company, "code", companies)
fast_insert_multiple(Page, "code", pages)

# build data hashes
hash_years = create_lookup_hash(Year, :year)
hash_quarters = create_lookup_hash(Quarter, :quarter)
hash_statements = create_lookup_hash(Statement, :code)
hash_companies = create_lookup_hash(Company, :code)
hash_pages = create_lookup_hash(Page, :code)

# parse financial data
# TODO: set up and parse data for all pages, currently only using data for:
# 60.20, 60.21, 60.30

slice_map = [2,2,1,4,4,2,2,16]
schema = [
  {statement_id: hash_statements},
  {year_id: hash_years},
  {quarter_id: hash_quarters},
  {company_id: hash_companies},
  {page_id: hash_pages},
  {row_index: ''},
  {column_index: ''},
  {amount: ''}
]

def parse_data_line(line, slice_map)
  count = 0
  result = []
  slice_map.each { |value|
    result << line.slice(count, value)
    count += value
  }
  return result
end

def format_data_line(line, schema)
  result = Hash.new
  schema.each_with_index { |schem,index|
    line_value = line[index]
    schema_hash = schem.first[1]
    schema_key = schem.first[0]

    lookup = schema_hash[line_value] || line_value
    if schema_key == :value then
      lookup = lookup.to_d
    elsif schema_key == :row || schema_key == :column then
      lookup = lookup.to_i
    end

    result[schema_key] = lookup
  }
  return result
end

restricted_pages = Page.pluck(:code)
skip_companies = ["D850"]

data_lines = []
total_records_searched = 0

Year.pluck(:year).each { |year|
  Quarter.pluck(:quarter).each { |quarter| 
    Statement.pluck(:code).each { |statement|
      statement = statement.slice(0)
      filename = "#{path}20#{year}#{quarter}-#{statement}-pc.txt"
      if File.exist?(filename) then
        count = %x{wc -l '#{filename}'}.split.first.to_i
        p "#{filename} data records found: " + count.to_s
        total_records_searched += count
        File.open(filename).each do |line|
          # only parse lines for set up pages right now
          page = line.slice(9,4)
          company = line.slice(5,4)
          if restricted_pages.include?(page) && !skip_companies.include?(company) then
            parsed = parse_data_line(line, slice_map)
            formatted = format_data_line(parsed, schema)
            data_lines << formatted
          end
        end
      end
    }
  }
}

fast_insert_data(data_lines)

p "TOTAL PARSED DATA LINES: #{data_lines.count} / #{total_records_searched}"
finish = Time.now
diff = finish - start
p "TOTAL TIME TAKEN: #{diff} seconds"<file_sep>class CreatePages < ActiveRecord::Migration[5.2]
  def change
    create_table :pages do |t|
      t.string :name, null: false
      t.column :code, 'char(4)', null: false, unique: true
    end
  end
end
<file_sep># Balsam API

Balsam provides a way to query, visualize and analyze financial data for Canadian property and casualty insurers.

## Concept

There is a wealth of publicly available financial data that has been filed with OSFI. By exposing this data to fast, modern means of analysis it is hoped that new insights into risk management can be found.

## Concerns

- Rails ActiveRecord does not support certain database features like composite keys.
- As the analysis aspect grows more complex this may impact performance.
- Must maintain detailed ERDs and documentation on data consumption in case migration to a different backend is necessary.

## Roadmap
1. Single Financial Statement Mapping (60.20 Consolidated Premiums and Claims)
a. Create a template to display the data as a financial statement
b. Generate year-specific schema
c. Parse, record, index and map the raw data to the template
2. Multiple Mapping

## Schema
1. Tables
a. Statement
i. Name (Domestic, Foreign)
b. Year
c. Quarter
d. Company
i. Name
ii. Code
e. Page
i. Code
ii. Name
f. Data
i. Row
ii. Column
iii. Value
iv. FK for all of the above<file_sep>class Year < ApplicationRecord
  has_many :data
  has_many :year_quarters
  has_many :quarters, through: :year_quarters

  validates :year, presence: true, uniqueness: true
end
<file_sep>class CreateQuarters < ActiveRecord::Migration[5.2]
  def change
    create_table :quarters do |t|
      t.column :quarter, 'char(1)', null: false
      
      t.index :quarter, unique: true
    end
  end
end
<file_sep>class YearQuarter < ApplicationRecord
  belongs_to :year
  belongs_to :quarter

  validates :quarter, presence: true, uniqueness: { scope: :year, message: "no duplicate quarters per year" }
  validates :year, presence: true
end
<file_sep>class CreateCompanies < ActiveRecord::Migration[5.2]
  def change
    create_table :companies do |t|
      t.string :name, null: false
      t.column :code, 'char(4)', null: false

      t.index :code, unique: true
    end
  end
end
<file_sep>class CreateStatements < ActiveRecord::Migration[5.2]
  def change
    create_table :statements do |t|
      t.string :name, null: false
      t.column :code, 'char(2)'

      t.index :code, unique: true
    end
  end
end
<file_sep>class CreateYearQuarters < ActiveRecord::Migration[5.2]
  def change
    create_table :year_quarters do |t|
      t.references :year, foreign_key: true, null: false
      t.references :quarter, foreign_key: true, null: false
    end
  end
end
