python读取excel股票历史数据_Ruby实现股票实时数据和历史数据查询并写入excel

用Ruby写股票程序的好像不大多见......我也打算改用Python了。

实现效果：

Ruby代码：

# -*- coding: UTF-8 -*-

require 'rubygems'

require 'hpricot'

require 'open-uri'

require 'win32ole'

x = 1

s_date = "20170609" #指定历史时间

time = Time.new #取当前时间

s_today = "#{time.year}" + "#{time.month}" + "#{time.day}" #把当前时间拼接成2017616格式，用于命名excel文件名

#puts s_today

#创建一个excel表并按照当前日期命名

file_path = "d:\\rb\\stock\\"

file_name = s_today + "股票池.xls"

file_name_path = file_path + file_name

excel = WIN32OLE.new("excel.application")

excel.visible = true # in case you want to see what happens

workbook = excel.workbooks.add

workbook.saveas(file_name_path)

workbook.close

excel.Quit

#写入表头

excel = WIN32OLE::new('Excel.Application')

workbook = excel.Workbooks.Open(file_name_path)

worksheet = workbook.Worksheets(1) #定位到第一个sheet

worksheet.Select

#写入

worksheet.Range("a3").Value = "股票代码"

worksheet.Range("b3").Value = "股票名称"

worksheet.Range("d3").Value = "当前价格"

worksheet.Range("f3").Value = "昨收"

worksheet.Range("g3").Value = "今开"

worksheet.Range("h3").Value = "#{s_date}" + "收盘价"

worksheet.Range("i3").Value = "本日涨幅"

worksheet.Range("j3").Value = "累计涨幅"

workbook.Close(1)

excel.Quit

#获取股票文件stock.txt的总行数

def wc(filename)

$nline = $nword = $nchar = 0 #$符号表示全局变量，普通变量不在def外起作用

File.open(filename) do |io|

io.each_line do |line|

words = line.split(/\s+/).reject{|w| w.empty? }

#本例中使用了split方法分割单词，当行首有空白字符时，split方法的执行结果中会产生空白字符串，因此我们

#会删除该空白字符串。

$nline += 1

$nword += words.length

$nchar += line.length

end

end

#puts "文件的行数为:#{$nline}\n文件的单词数为:#{$nword}\n文件的字符数为:#{$nchar}"

puts "股票池股票数:#{$nword}\n"

end

wc("d:/rb/stock/stock.txt")

#puts $nword

#循环开始

while x <= $nword

#puts "轮询中:"

stock_lines = IO.readlines("d:/rb/stock/stock.txt");

s_code = stock_lines[x]

s_code_s = s_code.chomp # chomp用来删除文本里带过来的换行符

if s_code_s > "600000"

scode = "sh" + s_code_s

else

scode = "sz" + s_code_s

end

puts "-----------------------"

doc = Hpricot(open('http://biz.finance.sina.com.cn/stock/flash_hq/kline_data.php?symbol=' + "#{scode}" + '&end_date=' + "#{s_date}" + '&begin_date=' + "#{s_date}" )) #调用新浪股票历史数据接口

#puts doc

out_file = open('d:/rb/stock/temp.txt', 'w')

out_file.write(doc)

out_file.close

str = IO.read("d:/rb/stock/temp.txt");

#puts str.length;

#puts str[0,30]

str.split(/"/) #新浪历史数据接口分隔符为双引号

arr = str.split(/"/)

doc1 = Hpricot(open('http://qt.gtimg.cn/q=' + "#{scode}")) #调用腾讯股票实时接口

#puts doc

out_file1 = open('d:/rb/stock/temp1.txt', 'w')

out_file1.write(doc1)

out_file1.close

str1 = IO.read("d:/rb/stock/temp1.txt");

#puts str.length;

#puts str[0,30]

str1.split(/~/)

arr1 = str1.split(/~/)

#定义股票接口对应的字段名

s_name = arr1[1] #股票名

s_number = arr1[2]

s_current_price = arr1[3] #当前价格

s_closing_price = arr1[4] #昨收

s_opening_price = arr1[5] #今开

#定义股票接口对应的字段名

s_last_week_closeprice = arr[7] #经测试，第8个数组是历史数据的收盘价。

#显示查询结果

#puts "股票代码：" + s_code

#puts "股票： " + s_number

puts s_number + s_name

#puts s_date + "收盘价：" + s_last_week_closeprice

puts "当前价格：" + s_current_price

puts "昨收： " + s_closing_price

puts "今开： " + s_opening_price

puts s_date + "收盘价：" + s_last_week_closeprice

#把以上数据写入程序自动创建的excel(目前测试不支持xlsx格式，暂用xls格式)

#打开excel文件,对其中的sheet进行访问:

puts "#{s_number}正在写入excel表... ..."

excel = WIN32OLE::new('Excel.Application')

workbook = excel.Workbooks.Open(file_name_path)

worksheet = workbook.Worksheets(1) #定位到第一个sheet

worksheet.Select

#写入

worksheet.Range("a#{x + 3}").Value = "'" + s_number

worksheet.Range("b#{x + 3}").Value = s_name

worksheet.Range("d#{x + 3}").Value = s_current_price

worksheet.Range("f#{x + 3}").Value = s_closing_price

worksheet.Range("g#{x + 3}").Value = s_opening_price

worksheet.Range("h#{x + 3}").Value = s_last_week_closeprice

worksheet.Range("i#{x + 3}").Value = "=(D#{x + 3}-F#{x + 3})/F#{x + 3}"

worksheet.Range("j#{x + 3}").Value = "=(D#{x + 3}-H#{x + 3})/H#{x + 3}"

workbook.Close(1)

excel.Quit

puts "写入完毕！"

x = x + 1

end

下个代码不用Ruby写了，查找资料和调试太累了.........

改用资源比较丰富的Python