如何获取所有股票历史数据（excel炒股经验三）

1第一步，获取股票代码，复制其中一部分到第一个工作表a4到a127，然后通过程序把每一个代码写入到不同的工作表a2位置，并对该工作表以该股票代码命名。程序如下：sub 工作表命名()for i = 4 to 127sheets(i).range("a2") = "'" & sheets(1).range("a" & i)next ifor i = 4 to sheets.countsheets(i).name = sheets(i).range("a2").valuenextend sub2第二步，获取股票历史数据。代码如下：private function getsource(surl as string) as string dim oxhttp as object set oxhttp = createobject("msxml2.xmlhttp") oxhttp.open "get", surl, false oxhttp.send getsource = oxhttp.responsetext set oxhttp = nothingend functionsub 历史数据() dim objxml as object dim txtcontent as string dim i as integer dim strcode as string dim gp as string dim ka**hihang dim arr, arr1, arr2, arr3, arr4, arr5, arr6, arr7, arr8, arr9, arr10, arr11 on error resume nextendrow = range("a65536").end(xlup).rowstartrow = 4if startrow <= endrow then range(cells(startrow, 1), cells(endrow, 11)).value = ""else exit subend if set objxml = createobject("microsoft.xmlhttp") gp = [a2] for h = 1 to 4 for m = 1 to 4 ka**hihang = [a65535].end(xlup).row nian = replace(str(year(now) + 1 - h), " ", "") jidu = replace(str(4 + 1 - m), " ", "") with objxml .open "get", "http://quotes.money.163.com/trade/lsjyj_" + gp + ".html?year=" + nian + "&season=" + jidu + "", false .send if objxml.status = 200 then txtcontent = .responsetext arr = split(txtcontent, "'><td>") for i = 1 to ubound(arr) arr1 = split(arr(i), "</td><td") cells(i + ka**hihang, 1) = right(left(arr1(0), 10), 10) arr2 = split(arr1(1), chr(60)) cells(i + ka**hihang, 2) = mid(arr2(0), instr(arr2(0), ">") + 1) arr3 = split(arr1(2), chr(60)) cells(i + ka**hihang, 3) = mid(arr3(0), instr(arr3(0), ">") + 1) arr4 = split(arr1(3), chr(60)) cells(i + ka**hihang, 4) = mid(arr4(0), instr(arr4(0), ">") + 1) arr5 = split(arr1(4), chr(60)) cells(i + ka**hihang, 5) = mid(arr5(0), instr(arr5(0), ">") + 1) arr6 = split(arr1(5), chr(60)) cells(i + ka**hihang, 6) = mid(arr6(0), instr(arr6(0), ">") + 1) arr7 = split(arr1(6), chr(60)) cells(i + ka**hihang, 7) = mid(arr7(0), instr(arr7(0), ">") + 1) arr8 = split(arr1(7), chr(60)) cells(i + ka**hihang, 8) = mid(arr8(0), instr(arr8(0), ">") + 1) arr9 = split(arr1(8), chr(60)) cells(i + ka**hihang, 9) = mid(arr9(0), instr(arr9(0), ">") + 1) arr10 = split(arr1(9), chr(60)) cells(i + ka**hihang, 10) = mid(arr10(0), instr(arr10(0), ">") + 1) arr11 = split(arr1(10), chr(60)) cells(i + ka**hihang, 11) = mid(arr11(0), instr(arr11(0), ">") + 1) next i end if end with next m next hset objxml = nothingend sub3第三步，获取上证历史数据，并获取所有股票的历史数据。程序如下：sub 所有股票历史数据获取()application.screenupdating = false dim s as string, gp as string, nian as string, jidu as string, s1 as string dim arr, arr1, arr2, arr3, arr4, arr5, arr6, arr7, arr8, arr9 dim i, h as long dim ka**hihang dim lastrow as long, r as long on error resume nextendrow = sheet2.range("a65536").end(xlup).rowstartrow = 4if startrow <= endrow then sheet2.range(sheet2.cells(startrow, 1), sheet2.cells(endrow, 9)).value = ""else exit subend if for h = 1 to 5 for m = 1 to 4 ka**hihang = sheet2.[a65535].end(xlup).row nian = replace(str(year(now) + 1 - h), " ", "") jidu = replace(str(4 + 1 - m), " ", "") s1 = "http://quotes.money.163.com/trade/lsjysj_zh**hu_000001.html?year=" + nian + "&season=" + jidu + "" s = getsource(s1) arr = split(s, "'><td>") for i = 1 to ubound(arr) arr1 = split(arr(i), "</td><td") sheet2.cells(i + ka**hihang, 1) = right(left(arr1(0), 4), 4) & "-" & right(left(arr1(0), 6), 2) & "-" & right(left(arr1(0), 10), 2) arr2 = split(arr1(1), chr(60)) sheet2.cells(i + ka**hihang, 2) = mid(arr2(0), instr(arr2(0), ">") + 1) arr3 = split(arr1(2), chr(60)) sheet2.cells(i + ka**hihang, 3) = mid(arr3(0), instr(arr3(0), ">") + 1) arr4 = split(arr1(3), chr(60)) sheet2.cells(i + ka**hihang, 4) = mid(arr4(0), instr(arr4(0), ">") + 1) arr5 = split(arr1(4), chr(60)) sheet2.cells(i + ka**hihang, 5) = mid(arr5(0), instr(arr5(0), ">") + 1) arr6 = split(arr1(5), chr(60)) sheet2.cells(i + ka**hihang, 6) = mid(arr6(0), instr(arr6(0), ">") + 1) arr7 = split(arr1(6), chr(60)) sheet2.cells(i + ka**hihang, 7) = mid(arr7(0), instr(arr7(0), ">") + 1) arr8 = split(arr1(7), chr(60)) sheet2.cells(i + ka**hihang, 8) = mid(arr8(0), instr(arr8(0), ">") + 1) arr9 = split(arr1(8), chr(60)) sheet2.cells(i + ka**hihang, 9) = mid(arr9(0), instr(arr9(0), ">") + 1) next i next mnext happlication.screenupdating = truen = worksheets.countfor i = 4 to n worksheets(i).activate 历史数据nextend sub4第四步，保存工作簿，并对该工作簿命名为1，然后复制该工作簿27份，把所有股票代码写入每一个工作簿第一个工作表的a3到a127列。然后根据第一步对工作簿内的工作表命名，并在每一个工作表a2单元格内写入股票代码。5第五步，把这些工作簿存放到炒股文件夹下的数据库文件下。然后在炒股文件夹下新建一个工作簿，命名为“一键更新表格”，写入如下代码：sub 所有股票历史数据更新()dim wb as workbook for i = 1 to 27 set wb = workbooks.open(th**workbook.path & "\数据库\" & i & ".xl**") application.run "'" & wb.path & "\" & i & ".xl**'!所有股票历史数据获取"wb.s**ewb.close next iend sub当启动这一段代码以后，程序会自动一个一个的打开对应的工作簿然后获取里面所有股票的历史数据，存放在对应的工作表当中。6第六步，对相应的程序添加按钮，这样，我们在启动对应程序时，只需要点一下对应的按钮就可以实现了。这样，我们通过多个工作簿就实现了获取所有股票历史数据的方法。而后面，我们只需要通过获取对应股票每一天的历史数据，我们就能够在任意一天很快速的能够拥有所有股票的历史数据了。end 20210311