Last active
March 28, 2016 15:41
-
-
Save zackster/05f7906b6476965792f7 to your computer and use it in GitHub Desktop.
Moving data from Quandl into MySQL – http://www.zackburt.com/financialmarkets/index.php/2016/03/27/moving-quandl-data-into-mysql/
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 'mysql2' | |
client = Mysql2::Client.new(:host => "localhost", :username => "*USERNAME*", :password => "*PASSWORD*", :database => "*DATABASENAME*", :local_infile => true) | |
puts 'Downloading EOD data' | |
system('curl -J -L -oeod https://www.quandl.com/api/v3/databases/EOD/data?auth_token=**AUTHTOKEN**') | |
puts 'UnZIPPING EOD Data' | |
system('unzip -p eod > eod2') | |
puts 'Filtering out EOD Data before 2014' | |
system('awk -F, \'$2 > "2014" {print}\' eod2 > eod_since_2014') | |
puts 'Dropping the EOD table' | |
client.query("DROP TABLE adj_eod") | |
puts 'Creating the EOD table' | |
client.query('CREATE TABLE `adj_eod` ( `id` int(11) NOT NULL AUTO_INCREMENT, `symbol` varchar(10) DEFAULT NULL, `date` date DEFAULT NULL, `open` decimal(35,20) DEFAULT NULL, `high` decimal(35,20) DEFAULT NULL, `low` decimal(35,20) DEFAULT NULL, `close` decimal(35,20) DEFAULT NULL, `volume` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_index` (`symbol`,`date`), KEY `symbol` (`symbol`), KEY `date` (`date`) )') | |
puts "Cleaning up old tempfiles" | |
system("rm eod_tempfile.part.*") | |
puts "Splitting up eod data into chunks" | |
system("split -d -l 100000 eod_since_2014 eod_tempfile.part.") | |
filenames = Dir.glob("/path/to/the/folder/that/contains/this/script/**/eod_tempfile.part.*") | |
filenames.each_with_index do |filename, idx| | |
print "Loading #{filename}" | |
client.query("LOAD DATA LOCAL INFILE '#{filename}' INTO TABLE adj_eod FIELDS TERMINATED BY ',' (symbol,date,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,open,high,low,close,volume)") | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment