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.