R语言-股票数据库(2)-股票日K线信息-未复权

注：1、此方法获得的股票价格是实际价格，未进行前/后复权
       2、只举例深市的获取方法，沪市的同理
       3、步骤：先从网上爬取每个股票的交易历史，存为csv格式文件；读取csv文件数据加载至mysql数据库

#######################################################################################################################################批量下载深市股票信息到csv文件###############################################################################################################################################把下载好的数据按照市场分开，分别保存到txt文件即可，这里不再赘述。#保存好的数据要稍微处理一下，日期的格式调整为yyyymmdd，write.table会把数据框的“列名”打印出来，列名也是要去掉的。#有了股票代码和上市日期数据就可以自动化下载数据了，最好深市沪市分开进行，存在不同的文件夹下，R代码如下#下载股票数据library(RCurl)#http://quotes.money.163.com/service/chddata.html?code=0600030&start=20030106&end=20140920&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAPurl1    <- "http://quotes.money.163.com/service/chddata.html?code="market  <- "1" # 1:深市，0:沪市code    <- "000003"url2    <- "&start="start   <- "19900101"url3    <- "&end="end     <- "20170711"         #用当前最新日期url4    <- "&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP"#文件的存放路径file.path   <- "E:/.../数据源/SZ/"#股票代码+发行日期，格式：CODE制表符yyyymmddsecurity <- readLines("E:/.../数据源/深市股票上市日.txt")code <- vector(length = length(security))listingdate <- vector(length = length(security))security.tab <- data.frame(code, listingdate, stringsAsFactors=FALSE)for (i in 1 : length(security)){  security.tab[i,] = do.call(cbind,(strsplit(security[i],"\t")))          #do.call(cbind,[list]) 列表转矩阵}for (i in 1 : dim(security.tab)[1]){  code <- security.tab$code[i]  start <- security.tab$listingdate[i]  cat(i,"\t---",code,"\n")  url <- paste(url1,market,code,url2,start,url3,end,url4,sep="")  file <- paste(file.path,code,".csv",sep="")  download.file(url,destfile=file, method="auto",quiet=T)}#######################################################################################################################################批量导入深市股票信息到数据库################################################################################################################################################创建表create table stock_info_sz (data_dt varchar(10),stock_id varchar(10),stock_name  varchar(100),shoupanjia  decimal(18,2),zuigaojia   decimal(18,2),zuidijia    decimal(18,2),kaipanjia   decimal(18,2),qianshoupan   decimal(18,2),zhangdiee    decimal(18,2),zhangdiefu   decimal(18,6),huanshoulv   decimal(18,6),chengjiaoliang decimal(18,0),chengjiaojine  decimal(18,2),zongshizhi     decimal(18,0),liutongzhi     decimal(18,0))#在成功获得数据，并保存到若干csv、txt文件之后，最后一步就是导入数据库MySQL。借助R包RMySQL，可以实现R和MySQL的连接，在R环境中直接操作MySQL数据库。# 载入RMySQL包library(RMySQL)# 建立数据库连接conn <- dbConnect(MySQL(),dbname = "gushi",username = "root")# 启动非严格模式dbSendQuery(conn, "SET @@sql_mode=ANSI;")# win7环境下如果汉字乱码，就运行这条命令dbSendQuery(conn, "SET NAMES GBK")security <- readLines("E:/.../数据源/深市股票上市日.txt")code <- vector(length = length(security))listingdate <- vector(length = length(security))security.tab <- data.frame(code, listingdate, stringsAsFactors=FALSE)for (i in 1 : length(security)){  security.tab[i,] = do.call(cbind,(strsplit(security[i],"\t")))          #do.call(cbind,[list]) 列表转矩阵}#文件的存放路径file.path   <- "E:/.../数据源/SZ/"for (i in 1 : dim(security.tab)[1]){  code <- security.tab$code[i]  cat(i,"\t---",code,"\n")  file <- paste(file.path,code,".csv",sep="")  #拼接SQL  start <- "load data infile '"  end <- "' into table stock_info_sz character set GBK fields terminated by ',' lines terminated by '\n'"  sql <- paste(start,file,end,sep="")  res1 <-dbSendQuery(conn, sql)}#清除文件头数据sql <- "delete from stock_info_sz where data_dt='日期'"res1 <-dbSendQuery(conn, sql)#去掉股票代码前的'sql <- "update stock_info_sz set stock_id=replace(stock_id,'\\'','')"res1 <-dbSendQuery(conn, sql)