I found that finance.yahoo.com historical downloads (e.g. http://finance.yahoo.com/q/hp?s=GLD&a=00&b=1&c=2005&d=00&e=15&f=2012&g=d) came with the date formatted as YYYY-MM-DD, (e.g. 2012-01-14) and that my preferred spreadsheet program, LibreOffice Calc, couldn't parse that date format.
So, I wrote a little Ruby programming language script to do it (click Download yfcsvfix.rb to download the script). Of course, you'll need the Ruby interpreter on your PC to run the script (click here to go to the Ruby Download page).
Running the ycsvfix.rb with no command line parameters provides the documentation for the program. Here's that documentation:
yfcsvfix <inpath> <outpath>
yfcsvfix takes a finance.yahoo.com historical price csv download
and changes the format of the first field from YYYY-MM-DD to MM/DD/YYYY.
When <outpath> is not provided the file at <inpath> is overwritten.
yfcsvfix also changes the order that CSV records appear outputting them
with the oldest records first.
I'm publishing this simple program into the public domain. No guarantees are made of its correctness or absence of bugs.
Here's the source code:
puts "yfcsvfix <inpath> <outpath>"
puts " yfcsvfix takes a finance.yahoo.com historical price csv download"
puts "and changes the format of the first field from YYYY-MM-DD to MM/DD/YYYY."
puts "When <outpath> is not provided the file at <inpath> is overwritten."
puts "yfcsvfix also changes the order that CSV records appear outputting them "
puts "with the oldest records first."
if (ARGV[0])
# Read the file in ar
hdr = nil
ar = Array.new
File.open(ARGV[0], "r") do |fin|
while line = fin.gets
if (hdr)
ar << line
else
hdr = line
end
end
end
ar = ar.sort
outpath = ARGV[0]
if ARGV.length > 1
outpath = ARGV[1]
end
puts "outpath=" + outpath
File.open(outpath, "w") do |fout|
fout.puts hdr
ar.each do |line|
sp = line.split(',')
dsp = sp[0].split('-')
odsp = dsp[1] + '/' + dsp[2] + '/' + dsp[0]
oln = odsp + ',' + sp[1] + ',' + sp[2] + ',' + sp[3] + ',' + sp[4] + ',' + sp[5] + ',' + sp[6]
fout.puts oln
end
end
end
Hope someone finds this useful. Leave me a comment if you do as it will encourage me to keep sharing what I make.
MontyHigh, www.worldofwallstreet.us
Comments