Sunday, February 8, 2009

A "hackish" data loader

If I'm going to be dealing with whacks of data, I want a fairly clean way of loading the data into the database in the format I specified.

So, while the first order of business would be the creation of the PaperTrade class (which will be the first gem I release out of this work), I wanted a quick and dirty way to load the files so I had to do a few things first.

0. Create the table on the database.
  • So, I created a sqlite3 file called "db.db" (because I like palindromes)
  • I used the table definition below (at some point I'll come back and create a migration for this
  • CREATE TABLE stocks(
    id integer primary key,
    name varchar(20),
    date integer,
    open float,
    high float,
    low float,
    close float,
    volume integer,
    adj_close float);
1. Creation of the Data Loader class.
There are a few notes to this:
  • I use a couple of modules to abstract out the ActiveRecord calls so that the loader script which creates the DataLoad class will not have to deal with anything - the only part that is a bit unwieldy is that there is an "include Tables" to pull in the creation of the abstracted Stocks class. On the whole, I believe there should be a more elegant way of dealing with this but for now, it works. The only time I think it will bite me will be if I build more on to this so that multiple tables are created. That could get overly messy
  • The date format that the csv data I receive back from Yahoo! is DD/MM/YY which apparently is what it thinks all Canadian localised date formats should be. So, I had to create a twist date method to deal with that and it's highly idiosyncratic to my uses - you may not get the same mileage if your date data does not comply with that format. Also, there is a bit of a "Y19K" thing going on so don't import any data prior to 1919 or use this class after 2019 ;-)
  • Since I felt so "clever" with my Tables include, I decided to do one for the require statements.
  • module Requires
    require 'activerecord'

    module Tables
    class Stock < ActiveRecord::Base

    class DataLoad
    include Requires
    def initialize(data_file_load, stock_name, db_file_name)
    @file_load = data_file_load
    @name = stock_name
    @db_file_name = db_file_name
    @adapter = 'sqlite3'

    def connect_to_db
    # Connect to a database
    {:adapter => @adapter,
    :database => @db_file_name})

    def twist_date(stringer)
    months = ["", "jan", "feb", "mar", "apr", "may", "jun", "jul",
    "aug", "sep", "oct", "nov", "dec"]
    split_string = stringer.split('/')
    short_date = split_string.last
    if short_date.to_i > 19 then
    return_date = '19' + short_date
    return_date = '20' + short_date
    return [return_date.to_i, months[split_string[1].to_i], split_string[0].to_i]

    def data_file_load
    @data_load = [] do |file|
    while line = file.gets
    @data_load << line.chomp!
    @headers = @data_load.shift

    def csv_to_db
    @data_load.each do |line|
    nter = = @name
    date_twisted = twist_date(line.split(',').first) = Time.local(date_twisted.first, date_twisted[1], date_twisted.last).to_i = line.split(',')[1].to_f
    nter.high = line.split(',')[2].to_f
    nter.low = line.split(',')[3].to_f
    nter.close = line.split(',')[4].to_f
    nter.volume = line.split(',')[5].to_i
    nter.adj_close = line.split(',')[6].to_f
    end #class End
At this point in time, I haven't written any unit tests but that should be in an upcoming post.

2. The data loader script:
  • # This is a script to load tables to the database

    require 'data_load_class'

    puts "enter the name of the csv file to load"
    data_file_load = gets.chomp!

    puts "enter the name of the identifer for the stock ex: USO"
    stock_name = gets.chomp!

    puts "enter the name of the database file"
    db_file_name = gets.chomp!

    a_data_loader =, stock_name, db_file_name)


    # We can only create the tables after we've connected
    # to the database
    include Tables

    # Get the data

    # Load 'em up
Since I'm blogging the code as it exists today, there are a bunch of unpolished bits.

The parts I'm most unhappy about are:
  • The whole creating the ActiveRecord Stock class. I did quite a bit of reading on how to do this and wasn't pleased with any of the current solutions. I think Rails has it's own way of doing this so that's a study task for me.
  • Creating my own Y19K bug wasn't too pleasing but, I wanted to deal with the data as I was receiving it and not have ot 'normalise' the data through OOCalc or Excel if at all possible.
  • Lack of unit tests - I recognize this is a bit askew from my last post but, the fact of the matter is that I have not yet internalised the whole TDD because I have not made up my mind as to whether or not BDD (a la RSpec) makes more sense to me.
See you soon.

No comments:

Post a Comment