Extract non-agency yields from Credit Pricing Sheet

extract-yields.rb

require 'win32ole'
require 'pathname'
require 'chronic'

excel = WIN32OLE.new('Excel.Application')
excel.visible = false

# Print header line
header = %w(Date Subprime_1yr Subprime_2yr Subprime_3yr Subprime_5yr Subprime_7+yr Prime_03-04_15yrPT Prime_03-04_30yrPT Prime_03-04_5/1PT Prime_03-04_10/1PT Prime_06-07_15yrPT Prime_06-07_30yrPT Prime_06-07_5/1PT Prime_06-07_10/1PT)
puts header.join(",").gsub("_", " ")

Pathname.glob('./raw/*.xls') do |xlsfile|
	date = Chronic.parse( xlsfile.to_s.match(/\((.*)\)/)[1].gsub("_", " ") )
	data = [ date.strftime('%m/%d/%Y') ]
	
	workbook = excel.Workbooks.Open( 'G:\Asif\Work\Data\Yields' + "\\" + xlsfile.to_s )	
	worksheet = workbook.Worksheets(1)
	
	# Read subprime yields	
	data.push( worksheet.Range("H33:L33").value )
	
	# Read prime yields	
	data.push( worksheet.Range("B38:B41").value )
	data.push( worksheet.Range("C38:C41").value )
	
	puts data.join(",")	
	workbook.Close(0);	
end
excel.Quit();