Created
April 1, 2014 00:58
-
-
Save urbantumbleweed/9905722 to your computer and use it in GitHub Desktop.
Reading CSV data into database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
I am working on the user story to show a quote, display opportunities, and trades. | |
I am trying to update the price data for all the ticker symbols in the database. I have created a method that maps all the ticker symbols into an array. I have tested this method and comfirms that it works. | |
I then send the place the array as a parameter in a method supplied by the YahooFinance ruby gem. It is at this point that I get an error. | |
The error I get is CSV::MalformedCSVError: Illegal quoting in line 1. | |
I have googled this issue, looked through stackoverflwo, talked to fellow classmates and nagged you guys. please help. | |
I have cleaned out the empty spaces from the symbol data but an still getting the error. | |
I tried iterating over each ticker symbol with a method that worked until the 147 item. I examined the ticker that threw an error and saw that it had a '/' character. I then dropped the database and reseeded a new database after cleaning the data with strip, compact, and regex so that only symbols that have only letters remained. I also removed the spaces. | |
I tried running the method to get details and again I get the Malformed csv error. I don't know what to do at this point and need some help. | |
Is there any suggestion you have for this? Is there a way I get can my script to run and just ignore the problem cases? | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'json' | |
raw_data = File.read('./db/json/nyse2.json') | |
stocks = JSON.parse(raw_data) | |
stocks.each { |stock| | |
if stock["Symbol"] =~ /^[a-zA-Z]+$/ | |
new_tick = Ticker.new(:ticker => stock["Symbol"].strip, | |
:name => stock["Name"], | |
:sector => stock["Sector"], | |
:industry => stock["Industry"], | |
:exchange => stock["Exchange"]) | |
new_tick.save | |
end | |
} | |
raw_data = File.read('./db/json/nasdaq2.json') | |
stocks = JSON.parse(raw_data) | |
stocks.each { |stock| | |
if stock["Symbol"] =~ /^[a-zA-Z]+$/ | |
new_tick = Ticker.new(:ticker => stock["Symbol"].strip, | |
:name => stock["Name"], | |
:sector => stock["Sector"], | |
:industry => stock["Industry"], | |
:exchange => stock["Exchange"]) | |
new_tick.save | |
end | |
} | |
raw_data = File.read('./db/json/amex2.json') | |
stocks = JSON.parse(raw_data) | |
stocks.each { |stock| | |
if stock["Symbol"] =~ /^[a-zA-Z]+$/ | |
new_tick = Ticker.new(:ticker => stock["Symbol"].strip, | |
:name => stock["Name"], | |
:sector => stock["Sector"], | |
:industry => stock["Industry"], | |
:exchange => stock["Exchange"]) | |
new_tick.save | |
end | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
class Ticker < ActiveRecord::Base | |
has_many :price_histories, dependent: :destroy | |
has_many :scans | |
has_and_belongs_to_many :patterns | |
has_many :opportunities | |
has_many :trades, through: :opportunities | |
def retrieve_standard_quote | |
#requests the standard quote from Yahoo | |
return YahooFinance.get_standard_quotes(self.ticker) | |
end | |
def retrieve_extended_quote | |
#requests the extended quote from Yahoo | |
return YahooFinance.get_extended_quotes(self.ticker) | |
end | |
def update_standard_fields(std_quote) | |
#sets the values that use the standard quote | |
self.update(#name: std_quote[self.ticker].name, | |
date: std_quote[self.ticker].date, | |
ask: std_quote[self.ticker].ask, | |
bid: std_quote[self.ticker].bid, | |
volume: std_quote[self.ticker].volume, | |
previousClose: std_quote[self.ticker].previousClose, | |
open: std_quote[self.ticker].open, | |
dayHigh: std_quote[self.ticker].dayHigh, | |
dayLow: std_quote[self.ticker].dayLow, | |
lastTrade: std_quote[self.ticker].lastTrade, | |
changePoints: std_quote[self.ticker].changePoints, | |
changePercent: std_quote[self.ticker].changePercent) | |
end | |
def update_extended_fields(ext_quote) | |
#sets the values that use the extended quote | |
self.update(#exchange: ext_quote[self.ticker].stockExchange, | |
peRatio: ext_quote[self.ticker].peRatio, | |
pegRatio: ext_quote[self.ticker].pegRatio, | |
weeks52ChangeFromHigh: ext_quote[self.ticker].weeks52ChangeFromHigh, | |
weeks52ChangePercentFromHigh: ext_quote[self.ticker].weeks52ChangePercentFromHigh, | |
weeks52ChangeFromLow: ext_quote[self.ticker].weeks52ChangeFromLow, | |
weeks52ChangePercentFromLow: ext_quote[self.ticker].weeks52ChangePercentFromLow) | |
self.update(weeks52High: self.bid - self.weeks52ChangeFromHigh, | |
weeks52Low: self.bid - self.weeks52ChangeFromLow) | |
end | |
def refresh_standard_quote | |
quote = self.retrieve_standard_quote | |
if self.update_standard_fields(quote) | |
return true | |
end | |
end | |
def refresh_extended_quote | |
quote = self.retrieve_extended_quote | |
if self.update_extended_fields(quote) | |
return true | |
end | |
end | |
def refresh | |
std_quote_complete = self.refresh_standard_quote | |
ext_quote_complete = self.refresh_extended_quote | |
return std_quote_complete && ext_quote_complete | |
end | |
def self.retrieve_all_standard_quotes(tickers_array) | |
return YahooFinance.get_standard_quotes(tickers_array) | |
end | |
def self.retrieve_all_extended_quotes(tickers_array) | |
return YahooFinance.get_extended_quotes(tickers_array) | |
end | |
def self.get_all_tickers | |
self.all.map {|tick| | |
tick.ticker | |
} | |
end | |
#refrehses all the standard information for each ticker in the array | |
def self.refresh_all_standard_quotes(all_tickers) | |
standard_quotes = self.retrieve_all_standard_quotes(all_tickers) | |
self.all.each {|tick| | |
tick.update_standard_fields(standard_quotes) | |
} | |
end | |
#refreshes all the extended information for each ticker | |
def self.refresh_all_extended_quotes(all_tickers) | |
extended_quotes = self.retrieve_all_extended_quotes(all_tickers) | |
self.all.each {|tick| | |
tick.update_extended_fields(extended_quotes) | |
} | |
end | |
#refreshes each ticker by sending one array | |
def self.refresh_all | |
tickers_array = self.get_all_tickers.compact | |
self.refresh_all_standard_quotes(tickers_array) | |
self.refresh_all_extended_quotes(tickers_array) | |
end | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have found a way to load data into the application. I would still like to discuss this with one of my instructors.