Rails 2.1 and Timestamps

Haven't posted many articles lately. Been busy with several work projects and other issues.

I've spent enough time with ruby that I can now look at tackling rails again. Since my first attempt at rails back in December were slow and did not gain traction. I thought a shift to getting to know the Ruby language would be better.

Version 2.1 of Rails has been released since I first looked at Rails. Apparently one of the new features that has been incorporated is time zones. One of my new rails projects uses date and timestamps. It was cool knowing everything is getting recorded in UTC. It cuts down on confusion of when a transaction was recorded. The only issue is displaying the users local time for it. I found this article that was very helpful in getting some quick results.

Ruby: Stock Project - Database Design

Now that we have data files, we need to examine them and see what we will have for a database design.

Fields	NYSE	AMEX	NASDQ
Name	Yes	Yes	Yes
Symbol	Yes	Yes	Yes
Market Value	Yes	Yes	Yes
Description	Yes	Yes	Yes
Security Type	No	No	Yes
Shares Outstanding	No	No	Yes

Looking at the fields there are two tables we will need to have.
1. A table to hold the values for the different stock exchanges
2. A table to hold the values for the individual stocks

Name, Symbol, and description all exist in all 3 files. We'll use these plus add a stock_id field, and exchange_id to our stocks table.

Security type might be useful, but since it is only in the NASDQ we'll toss it for now. Market value is a basic calculation based on share and share price. It doesn't need to be stored, it is always changing and should not be on the mainly static information a the stocks table.

Shares outstanding is another dynamic amount that could change over time so it really doesn't belong in the stocks table. Since it is only in the NASDQ data file, we'll not include it here and see if we can gather this information someplace else. Shares outstanding will be needed when comparing trading volumes to total shares outstanding to get a volatility value on a stock.

With Decisions made it's now time to set up the Database.

Ruby: Stock Project – Source Data

Now that we have installed ruby, postgres and have tested the connection between ruby and postgres, we need to find some data.

Google search for - stock list file

The search request brings back several hits. This link looked promising, plus free code. CodeProject: AJAX Stock Symbol Drop-down List. Free source code ...

The code is Java, but maybe if I need some ideas, I'll go back and skim over it.

Near the bottom of the page is a list of sites that the author used to get the free stock symbol files. The site I’m going to use is the NASDQ site. NASDQ is being very helpful they have provided their securities, plus AMEX and the NYSE. We won't have to go anyplace else to get our data.

The nasdq link for the data is http://www.nasdaq.com//asp/symbols.asp?exchange=Q&start=0. When looking at the other links the only thing that changes is exchange=? where the ? mark is exchange identifier. Downloading the file shows that it is a CSV file, and that it also contains quite a bit of information.

The data for these files was last updated on the 31st of December 2007. It's recent enough, especially in this slow market, Hopefully this file is updated about once a quarter, otherwise we may have to look for other sources, but for now this will work.

Now that we know what our initial data source data looks like we can now start to design the ruby scripts and the database table layouts.

For starters we are going to break this project into three areas.
1. Database
2. Ruby scripts (back end)
3. Rails / Other tools user front end

Retrieving your source data, and any other kind of updates, like stock prices and other financial data will be back end processes.

So lets get started on getting our data.

As noted earlier the url for retrieving the files changes very little, only the parameter of the exchange changes. Instead of writing 3 separate steps we can write 1 step that accepts parameters. I'm going to start out by creating a back end process file that will contain processes that are repeated continuously.

save this as "back_end_functions.rb"

----------- Start File -------------------

require 'net/http'

def get_source_stock(url,path_param,file_name)


Net::HTTP.start(url) { |http|
resp = http.get(path_param)
open(file_name, "wb") { |file|
file.write(resp.body)
}
}
end


-------------- End File ------------------------

This is the function that will retrieve the actual CSV files from the NASDQ web site and save them where you would like them saved based on the file name passed in the parameters.

With the function created we can now create our retrieval script. Your path will most likely need to changed or created. I like to keep data and scripts in different locations. This reduces the chance that you accidentally delete your script while trying to delete data files.

-------------- Stat File ------------------------

require "back_end_functions" # contains of fuctions

nasdq_url = "www.nasdaq.com" # defines the url to retrieve files from
url_path = "//asp/symbols.asp?exchange=Q&start=0" #path and parameters to requesst file
file_name = "nasdq.csv" #Name we want to assign the file
file_path = "/data/ruby/stock/data/" #path that the file will be written to
file = file_path+file_name #combined value of file and path to pass to function

p file
get_source_stock(nasdq_url,url_path,file)

# data to get AMEX file exchange = 1
url_path = "//asp/symbols.asp?exchange=1&start=0"
file_name = "amex.csv"
file = file_path+file_name

p file
get_source_stock(nasdq_url,url_path,file)

#Change data to get NYSE file exchange = N
url_path = "//asp/symbols.asp?exchange=N&start=0"
file_name = "nyse.csv"
file = file_path+file_name

p file
get_source_stock(nasdq_url,url_path,file)

-------------- End File ---------------------------

Execute the script above and you should end up with 3 csv files in your data directory. From what I can tell everything has to be done from the same drive. I know you can change drives, just haven't tried that. No reason to change so far in this project.

Next step will be to look at all of the data files and start looking at how to design the database

Ruby: Stock Project – install ruby and postgres

First part of the project, you will need to install ruby, postgreSQL, and the postgres ruby gem. Everything for this project is being developed under Windows. If you are using an operating system other than windows, you may have to make changes that are different from my documentation.

Download and install the latest version of Ruby from the Ruby Site here

Download and install the latest version of PostgresSQL here

When installing change the default port to 5230

Bring up a command prompt and install the ruby gem postgres

gem install postgres

This is optional, but you can download and install pgAdmin, pgAdmin is a database administration tool for Postgres.

SciTE is installed with Ruby, you will want to launch this and place the following code into it. Save the code to a file like test_db.rb. Then press f5 to execute the script. You’ll need to key in your password that you created while installing postgres.

require 'postgres'

conn = PGconn.connect("127.0.0.1", 5430, "", "", "postgres","postgres","[password]")

sql = "select * from pg_tablespace"

res_pid = conn.query(sql )

res_pid.each do |x|
p x
end

If everything has been done correctly you should get a result like this:

>ruby db_check.rb
["pg_default", "10", "", nil]
["pg_global", "10", "", nil]
>Exit code: 0

If not you’ll have to look at the error messages that generated instead to figure out what is wrong with the setup. Once complete you’ll be able to go to the next Step.

Ruby: Stock project – Build your own Stock Database

With the state of economy taking up so much of the news, the stock market is taking center stage like I have never seen before. 1999 and 2000 were about how everything was going up and there was no end in site. Now 8 years later everything is going down and we don’t know where the bottom might be. It’s kind of funny to see how much has changed in only the past six months. We have gone from a pretty much status quo market to one of the most turbulent markets in over 30 years.

With all of the focus on the credit crisis, I’ve been watching a lot of CNBC in the evening. This is not for investing advice, just to see what the financial pundits are saying about the current financial events. So I’ve been watching a lot of Mad Money, Kudlow & Company, and Fast Money over the past few months.

The three shows are good shows to watch, just don’t act on the recommendations immediately. Listen, learn how to filter and learn how people make decisions. I could go into more detail about these shows, but that would be for a different post.

This brings me to the real reason for this post. In all of the shows, they, the hosts, are always referencing research. I researched this and found that this is this. They never go into the details of how they researched, the criteria used, etc. These TV hosts only show you the selections and not how they came to their selections.

A lot of free information services exist out on the Internet for stocks. There are lots of free development tools like Ruby and databases like PostgreSQL. This is going to be an exercise on how to build your own stock picking database.

What you will need for this project are the following:

Ruby
Ruby postgres gem
PostgreSQL data base
Internet Connection

If something is missing I’ll update this post with new information. I’ve installed several gems over the past couple of months. At this point I’m not going to install rails or use rails. I will use Rails, when I get to reporting and making things available to other people.

This will be the list of topics in the order they are created.

1. Install Ruby and Postgres

2. Source Data

3.

Ruby: How to Parse an HTML page and retrieve embedded table information

I couldn’t find readily available an HTML::TABLE parser for ruby like I had for Perl. So I thought I’d just try and make basic one for a page that I like to grab information from. I really couldn’t find any good examples posted. So here is a sample method for extracting information from a table on an HTML page.

First I usually start with a very simple get the data and print the data out. The main purpose is to verify that you actually retrieving the data.

require 'net/http'

url = 'http://your.url.here/

url_data = Net::HTTP.get_response(URI.parse(url)).body

p url_data

Second you can look at the output data to see how the table labels are made.

Next I add the next piece to split the sheet data along the different embedded tables. This will become a loop process to access the different tables. Right now I just want to print out all of the tables.

data = url_data.split(/

data.each do |x|

p x

end

The first table split lines is 24 tables down. In this case I’m finding an embedded table within the original table or Table 24 for this page and it has a different tag format a "forward slash"table. The page I’m looking at is very table extensive in its layout. I reset a counter before going into the do each on the split of the table lines. Once I hit the table that I want to drill down further into I start another loop. At this stage I’m printing out the entire row of the table that is being displayed.

y=0

data.each do |x|

y = y+1

if y == 24

data_table = x.split(/<\/table>/)

z = 0

data_table.each do |t2|

if z == 0

data_row = t2.split(/tr/)

data_row.each do |row|

p row

end

end

end

end

end

The next step would be to isolate the individual elements in the row that you want to extract. In my case my elements are laid out in a single column. Column 1 is padded space, column 2 are labels and column 3 are the data values. So to display only the column 3 values I would loop through like this.

data.each do |x|

y = y+1

if y == 24

data_table = x.split(/<\/table>/)

z = 0

data_table.each do |t2|

if z == 0

data_row = t2.split(/tr/)

data_row.each do |row|

data_column = row.split(/

c = 0 data_column.each do |column| c= c +1 if c == 3 p column end end end end end end end After this point it’s clean up of the extra HTML formatting tags. Save the values to a local variable and work with the data.

Ruby Project

The past few weeks I've been working with ruby creating a few small short projects. I've been impressed with what I've been able to do with little effort. Almost everything I've tried has taken little time, and has been fairly easy to debug and get working well enough for me to use.

This is a short list of what I've been doing over the past few weeks.
Image file replacement program with GUI interface
XML feeds from a web site to populate a database with values
Retrieve a list of paid checks from Oracle ERP and place them into an excel spreadsheet
Send an e-mail with attachments (file mentioned above)

most of these were work related, some were just to test, while others turned into great time savings.

I'll post some details on some of the programs that I've written. The only dissappointment I have with ruby at the moment is the lack creating a compiled program. I want to distribute one program so that I don't have to actually run it anymore. Oh well.

First Ruby Script

I wrote my first ruby script today. I’ve been playing with rails and trying things out by example for the past few weeks., but this is the first time a wrote something that I actually needed to get something done at work. One of the projects I worked on last year was upgrading our imaging solution for AP. The imaging solution was over 3 major releases old. We were behind a total of 17 dot releases, which would get us to a point where we could jump to the current version. This old version that we were on had started to develop issues that just couldn’t be solved. One of the major issues was that some users could not view the images. As best as we could determine at the time, incompatibilities had arisen due to changes in Java. The core product was written for Java 1.2 or 1.3. Very old and very unsupported. The imaging solution and almost everything that it used from 3^rd parties was no longer supported, but that is a different story.

Turns out it wasn’t the java it was the actual image file. Somewhere in the chain of scanning, and transmitting the image, some of the tif files have become slightly corrupted. I guess new versions of Java now check the file instead of just blindly displaying the image or displaying what it can display without so much of a warning that something is wrong..

So now I know that some of the image files are corrupt and need to be fixed. How do I get these corrupt files? The images are on an ftp server, and I have a query that gives me the file information based upon information stored on an invoice. Running a query and manually ftp’ing and retrieving the images is very time consuming. A single invoice could have several pages associated with it: Meaning several image files for a document since each page is stored as a separate image.

This would be a great place to try out ruby, perl is a pain to get working on a windows PC. Ruby turned out to be very easy to install. I did start off with two separate ruby scripts and then combined the two into a single script.

The first step was to try to ftp to the server and retrieve one of the files. Googling gave me this link from Ruby Docs . This helped me get to a point to ftp the file back. This saved some time, but it would be really helpful if I could query the Oracle Database and retrieve the files based on the query.

A query of “ruby oracle” brought back some very good articles. These articles are on Oracle’s site: Accessorize Oracle Database with Ruby , Ruby on Rails on Oracle: A Simple Tutorial , and Tips for Optimizing Rails on Oracle . I haven’t read all of the articles in their entirety, but it did narrow down what I had to do very quickly.

I downloaded Ruby/OCI8 at http://rubyforge.org/projects/ruby-oci8/ followed the directions for installing on a windows machine and was ready to try running a query. I took one of the example queries in one of the articles and modified it to meet my needs.

Connecting to the database wasn’t clear, and having text values contained within single quotes also gave me a few issues.

I’ve worked with perl’s DBI with other project’s so ruby’s was not that hard to figure out. The only issue I had was that some of the calls were not clear that they were calls. I’ve modified the connect line to reflect something more readable.

dbh = DBI.connect('DBI:OCI8:[DBINSTANCE]', 'user', 'password')

Ruby’s wiki had a How to Connect to Oracle page that was very helpful. My experience in the past with DB connection examples are that you always use the default DB name and have only one DB.

Once I got past the connect issue I ran into a new one where ruby wasn’t liking the single quote used in the query. This was easily resolved by double quoting the entire query.

Below is the end result. Start to finish including downloading and installing everything needed. 2 hours. This will save me hours of time now. With audit season coming on strong, the request of images that can’t be retrieved through the image solution are only going to increase.

--Start Script

require 'dbi'
require 'net/ftp'

dbh = DBI.connect('DBI:OCI8:[DBINSTANCE]', 'user', 'password')

rs = dbh.prepare("select FILENAME, substr(COMPLETE_FILENAME, 32,21) from [table]
where document_id = 952154")

rs.execute

#Connect to FTP Server to retrieve image files

ftp = Net::FTP.new('[ftp_server]')
ftp.debug_mode=true
ftp.login(user = "user", passwd = "password")

while rsRow = rs.fetch do #loop through query results

#retrieve image files returned by query
ftp.chdir(rsRow[1])
ftp.getbinaryfile(rsRow[0], File.basename(rsRow[0]))

end

rs.finish

dbh.disconnect

ftp.close

--End Script

Ruby, Postgres and Primary keys

Started working on one of my first ruby projects. The first issue I have run into deals with keys for a table. In my designs I had a primary key that I used. It was causing an error message when trying to create a record in the table.

User Create (0.000000) RuntimeError: ERRORC23502Mnull value in column "user_id" violates not-null constraintFexecMain.cL1818RExecConstraints: INSERT INTO users ("end_date", "last_login", "name", "salt", "start_date", "hashed_password", "last_session", "user_id") VALUES(NULL, NULL, 'user', NULL, NULL, NULL, NULL, NULL)

I couldn't find anything that would show me how to not include a field in the automatically generated SQL by ruby. I did notice after looking at my table that ruby had created it's own primary key for the table and that it isn't included in the auto generated SQL.

Solution roll back to a previous version of the database that didn't have my new user table remove the column definition in the db\migrate\002_create_user_table. Then migrate back up to the current version.

rake db:migrate VERSION=

I do intend to find out how to override values, if they can be overridden.