Supplementary information:
How to get all the historical data of the stock;
In order to analyze the stock market, we must first obtain the historical data of all stocks. Only through the historical data of the stock market can we analyze the laws of the stock market.
(Take Think pad X 13 Ruilong laptop and Windows 10 operation as examples)
I. Tools/raw materials
1, EXCEL2007 or above, WPS cannot be used.
2. Computer 1g memory 1 core processor and above.
3. A brain with strong logical analysis ability and a little more wisdom.
Methods/steps:
Step 1, get the stock code, copy a part of it to the first worksheet A4 to A 127, and then write each code to a different worksheet A2 position through the program, and name the worksheet with the stock code. The process is as follows:
Subsheet Naming ()
For i = 4 to 127
Sheets (1). range(" a2 ")= " ' " & amp； Zhang (1). Scope ("a"&)
Next, I
For i = 4 to Zhang. count
Sheets (1). Name = Sheets(i)。 Range ("a2"). value
then
End joint
The second step is to obtain the historical data of the stock. The code is as follows:
Private function getsource (surlastring) asstring
Dim oXHTTP as an object
Set oXHTTP = CreateObject("MSXML2。 XMLHTTP”)
oXHTTP。 open“GET”，sURL，False
oXHTTP。 send
GetSource = oXHTTP.responsetext
Set oXHTTP = Nothing.
End function
Child historical data ()
Dim objXML as an object
Mark txtContent as a string
Mark I as an integer
Dim strCode as a string
Size assembly as a string
Dim kaishihang
Dim arr，arr 1，arr2，arr3，arr4，arr5，arr6，arr7，arr8，arr9，arr 10，arr 1 1
Continue to the next step when an error occurs.
EndRow = Range("a65536 ")。 End (xlUp). row
startRow = 4
If startRow Range (cell (startRow, 1), cell (EndRow, 1 1)). Value = ""
other
outlet connection
If ... it will be over.
set obj XML = CreateObject(" Microsoft。 XMLHTTP”)
gp = [A2]
For h = 1 to 4
For m = 1 to 4
kaishihang = [A65535]。 End (xlUp). row
nian = Replace(Str(Year(Now)+ 1-h)，" "，"")
Du Ji = Replace(Str(4+1-m), "","").
Using objXML
. Open "GET", "/trade/lsjj _"+gp+". html？ year = "+nian+" & amp； Season = "+Du Ji+"",false.
. send
If objXML. Then state = 200
txtContent =。 Response text
arr = Split(txtContent，" ' & gt")
For i = 1 to UBound(arr)
arr 1 = Split(arr(i)，" Cells(i + kaishihang， 1)= Right(Left(arr 1(0)， 10)， 10)
arr2 = Split(arr 1( 1)，Chr(60))
Cells(i + kaishihang，2) = Mid(arr2(0)，InStr(arr2(0)，" & gt) + 1)
arr3 = Split(arr 1(2)，Chr(60))
Cells(i + kaishihang，3) = Mid(arr3(0)，InStr(arr3(0)，" & gt) + 1)
arr4 = Split(arr 1(3)，Chr(60))
Cells(i + kaishihang，4) = Mid(arr4(0)，InStr(arr4(0)，" & gt) + 1)
arr5 = Split(arr 1(4)，Chr(60))
Cells(i + kaishihang，5) = Mid(arr5(0)，InStr(arr5(0)，" & gt) + 1)
arr6 = Split(arr 1(5)，Chr(60))
Cells(i + kaishihang，6) = Mid(arr6(0)，InStr(arr6(0)，" & gt) + 1)
arr7 = Split(arr 1(6)，Chr(60))
Cells(i + kaishihang，7) = Mid(arr7(0)，InStr(arr7(0)，" & gt) + 1)
arr8 = Split(arr 1(7)，Chr(60))
Cells(i + kaishihang，8) = Mid(arr8(0)，InStr(arr8(0)，" & gt) + 1)
arr9 = Split(arr 1(8)，Chr(60))
Cells(i + kaishihang，9) = Mid(arr9(0)，InStr(arr9(0)，" & gt) + 1)
arr 10 = Split(arr 1(9)，Chr(60))
Cells(i + kaishihang， 10) = Mid(arr 10(0)，InStr(arr 10(0)，" >) + 1)
arr 1 1 = Split(arr 1( 10)，Chr(60))
Cells(i + kaishihang， 1 1)= Mid(arr 1 1(0)，InStr(arr 1 1(0)，" >) + 1)
Next, I
If ... it will be over.
End with ...
The next m
The next h
Set objXML = Nothing
End joint
The third step is to obtain the historical data of Shanghai Stock Exchange and all stocks. The process is as follows:
Sub All Stock Historical Data Collection ()
Apply. ScreenUpdating = False
Dim s is a string, gp is a string, year is a string, Du Ji is a string, and s 1 is a string.
Dim arr，arr 1，arr2，arr3，arr4，arr5，arr6，arr7，arr8，arr9
Dim i is as long as H.
Dim kaishihang
Dim LastRow is as long as R.
Continue to the next step when an error occurs.
EndRow = Sheet2。 Scope ("a65536"). End (xlUp). row
startRow = 4
If you start with worksheet 2. Scope (worksheet 2. Cell (startRow, 1), Sheet2. Cell (EndRow, 9)). Value = ""
other
outlet connection
If ... it will be over.
For h = 1 to 5
For m = 1 to 4
kaishihang = Sheet2。 [A65535]。 End (xlUp). row
nian = Replace(Str(Year(Now)+ 1-h)，" "，"")
Du Ji = Replace(Str(4+1-m), "","").
s 1 = "/trade/lsjysj _ zhi Shu _ 00000 1 . html？ year = "+nian+" & amp； Season = "+Du Ji+"
s = GetSource(s 1)
arr = Split(s，" ' & gt")
For i = 1 to UBound(arr)
arr 1 = Split(arr(i)，" Sheet2。 Cells(i + kaishihang， 1)= Right(Left(arr 1(0)，4)，4)； -“& amp； Right(Left(arr 1(0)，6)，2)-“& amp； Right (left (arr 1(0), 10), 2)
arr2 = Split(arr 1( 1)，Chr(60))
Worksheet 2. Cells(i + kaishihang，2) = Mid(arr2(0)，InStr(arr2(0)，" & gt) + 1)
arr3 = Split(arr 1(2)，Chr(60))
Worksheet 2. Cells(i + kaishihang，3) = Mid(arr3(0)，InStr(arr3(0)，" & gt) + 1)
arr4 = Split(arr 1(3)，Chr(60))
Worksheet 2. Cells(i + kaishihang，4) = Mid(arr4(0)，InStr(arr4(0)，" & gt) + 1)
arr5 = Split(arr 1(4)，Chr(60))
Worksheet 2. Cells(i + kaishihang，5) = Mid(arr5(0)，InStr(arr5(0)，" & gt) + 1)
arr6 = Split(arr 1(5)，Chr(60))
Worksheet 2. Cells(i + kaishihang，6) = Mid(arr6(0)，InStr(arr6(0)，" & gt) + 1)
arr7 = Split(arr 1(6)，Chr(60))
Worksheet 2. Cells(i + kaishihang，7) = Mid(arr7(0)，InStr(arr7(0)，" & gt) + 1)
arr8 = Split(arr 1(7)，Chr(60))
Worksheet 2. Cells(i + kaishihang，8) = Mid(arr8(0)，InStr(arr8(0)，" & gt) + 1)
arr9 = Split(arr 1(8)，Chr(60))
Worksheet 2. Cells(i + kaishihang，9) = Mid(arr9(0)，InStr(arr9(0)，" & gt) + 1)
Next, I
The next m
The next h
Apply. ScreenUpdating = True
N = worksheet. count
For i = 4 to n
Worksheet (1). Make active
history data
then
End joint
Step 4, save the workbook and name it 1, then copy 27 workbooks, and write all the stock codes in the first worksheet A3 to A 127 of each workbook. Then name the worksheets in the workbook according to the first step, and write the stock code in the A2 cell of each worksheet.
Step 5, store these workbooks in the database file under the stock folder. Then create a new workbook named "one-click update table" under the stock folder, and write the following code:
Sub All Stock Historical Data Update ()
Mark wb as a workbook
For i = 1 to 27
Set wb = Workbooks. Open (ThisWorkbook. Path and path. "\ Database \"&; I&"".xlsb "")
Apply. Run ""'""& wb. Path and path. ""&I&"".xlsb! All inventory historical data collection ""
wb。 rescue
wb。 close
Next, I
End joint
When this code is started, the program will automatically open the corresponding workbooks one by one, and then obtain the historical data of all the stocks in them and store them in the corresponding worksheets.
Step 6, add a button to the corresponding program, so that when we start the corresponding program, we only need to click the corresponding button. In this way, the method of obtaining all historical data of stocks through multiple workbooks is realized. In the future, we only need to obtain the historical data of the corresponding stocks every day, and we can quickly have the historical data of all stocks on any day.
Matters needing attention
1. We need to analyze these data in the future, which will add a lot of calculations, so when we start all historical data updates, we should turn off automatic updates.
Because of the large historical data, only four years' data are available here. If you want to get more data, you can change the number 5 of "For h = 1 5" in the program.
It takes a long time to obtain historical materials, so it is necessary to make preparations in advance.
4. To know the whole method of establishing stock trading system with EXCEL, please see the following. "I have already boasted and stepped on it. What is your evaluation of this answer? Put the comments away. _ 1 uevpeq { zoom: 1； Background color: # fff border: 0; margin-bottom: 10px； Fill: 30px 0 20px 42px Location: Relative. _ 1 uevpeq . EC- 184 1 { padding:20px 0 }。 _ 1 uevpeq . EC-2246 { padding:20px 0 10px }。 EC- 184 1 . y 7 we 4 Hu { font-size: 16px； margin-bottom:-5px } . y 7 we 4 Hu { color:# 7 A8 F9 a； Height: 25px line height: 25px overflow: hidden; position:relative } . y 7 we 4 Hu H2 { margin:0； padding:0 } . y 7 we 4 Hu:after { clear:both； Content: ""; Display: block; Height: 0; Visibility: hidden a.tycfu7u {color: # 666; Floating: right; font-size: 12px； Left margin: 8pxtext-decoration: none}. hhhv6ex {color: # 666; font-size: 13px； Line height: normal; Line height: 20pxmargin-top: 10px}. vnsdjzp { margin-top: 15px； Location: relative. vnsdjzp H3 { font-weight:400； Fill: 0}. vnsdjzp a{text-decoration:none}。 vnsdjzp em { color:# d 8 14 19； Font style: normal. ec-2246。 vnsdjzp{margin-top:20px}。 _ 853 bpkf { margin-top: 10px } . a8gn 2 rz { margin-top:24px }。 _ 9rp 2 sus { display:block； Overflow: hide; Width: automatic. _4fuv54y，。 _85hk68c，. c84cjyj，. cd2v8yz，.gbuysgj、. gbxbqd2、. kpq54qk、. kw488k9、. puc33cm、. rdg9v5w、. yg 47 vz 5 { box-sizing:border-box； Floating: left; min-height: 1px； Location: opposite} .cd2v8yz {width: 8.33333%}. gbuysgj { width: 16.66666666% } . c 84 cjyj { width:25% } . puc 33cm { width:33.33333333% }。 _ 85hk 68 c { width:50% } . rdg 9 V5 w { width:58.33333333% } . kw 488k 9 { width:66.666666667% } . kpq 54 qk { width:75% } . gbxbqd 2 { width:83.33333333% }。 _ 4 fuv 54y { width:9 1.66666667% } . yg 47 vz 5 { width: 100% } . HC 9 wng 2 { float:right } body a . xny 78 f 9，body div . xny 78 f 9 { color:# 333； Display: block; Font series: Arial, Helvetica, sans serif; font-size: 14px！ Important; Line height:19px; margin-bottom: 10px； text-decoration:none } a . xny 78 f 9:hover { color:# 34b 458； text-decoration:underline } a . xny 78 f 9 font { color:# 34b 458 } a . xny 78 f 9 div { word-break:break-all } . v3 zrcrj { overflow:hidden； Text overflow: ellipsis; Blank: nowrap}. q1dpns8 {-Webkit-line-clamp: 2}. ndfxdqz，. q 1 dpns 8 { word-wrap:break-word； -webkit-box-orient: vertical; Display:-WebKit-box; Overflow: hide; Word segmentation: break-all}. ndfxdqz{-webkit-line-clamp:3}。 _7qtpw9j{-webkit-line-clamp:4}。 _2dt35k2，。 _ 7qtpw9j { word-wrap:break-word； -webkit-box-orient: vertical; Display:-WebKit-box; Overflow: hide; Word segmentation: break-all}. _2dt35k2{-webkit-line-clamp:5}。 _ 1 R3 uysh {-WebKit-line-clamp: 10 }。 _ 1r3uysh，. yux2cc 6 { word-wrap:break-word； -webkit-box-orient: vertical; Display:-WebKit-box; Overflow: hide; word-break:break-all } . yux2cc 6 {-WebKit-line-clamp: 12 }。 _ 4b xpxs 9 {-WebKit-line-clamp: 18； Word wrap: hyphenation; -webkit-box-orient: vertical; Display:-WebKit-box; Overflow: hide; word-break:break-all } body . k 795 fxy { color:# 7 A8 F9 a； Font series: Arial, Helvetica, sans serif; font-size: 12px； Line height: 22px padding top: 0; Location: relative; transform:translate y(. 466666667 em)} body . k 795 fxy:before { content:" "； Display: block; Height: 0; margin-top:- 10px } body . k 795 fxy a { color:# 7 A8 F9 a； Display: block} body.k795fxy a. _1nczan {color: # 666} body.k795fxy button {background:# fff；; Border: 1px solid; Border radius: 3px bottom: 0; Color: # 38f Floating: Yes; font-size: 12px； Line height:12px; Filling: 7px13px; Location: absolute; Right: 0} body.k795fxy.db1j6uz {margin-left: 5px} body.k795fxy. mxyzxgv { border: 1px solid # eee； Boundary radius: 50%; Display: embedded-block; Height: 22px Line height: 0; Right margin: 7pxmark-top:-2px; Vertical alignment: centered; Width: 22px} Body.k795fxy. accwaqu{margin-right:8px}。 Zdjeshn {Location: absolute; right:0 } . tx 6 hcqf {-WebKit-box-orient:horizontal； -WebKit-box-align:stretch； -WebKit-align-content:flex-start； -WebKit-align-items:stretch； Display:-WebKit-flex; -WebKit-flex-wrap:nowrap； -WebKit-justify-content:space-between； width:auto } . BD E3 HFS { flex: 1 } body . k 795 fxy a . n 1 yex 5 p，body . k 795 fxy div . n 1 yex 5 p { display:flex； justify-content:space-between } . k 795 fxy。 EC-showurl-line:hover { text-decoration:underline } . k 795 fxy。 EC-showurl-line { color:# 9 eacb 6 } . k 795 fxy { padding-bottom: 1px } @ charset " UTF-8 "； Body. _ 886 bxmw { font-size: 12px； Line height: 22px padding top: 0; transform:translate y(. 4 16666667 em)} body。 _ 886 bxmw:before { content:" "； Display: block; Height: 0; Margin-top:- 10px} text. _886bxmw a，body。 _886bxmw div{color:#333}body。 _ 886 bxmw . d 8 1 nas 8 { color:# 999； font-size: 15px； Line height: 25px} Text. _886bxmw。 _9mnfgfc{margin-right:5px}。 _886bxmw font{color:#34b458}。 ec-2246。 _886bxmw font{color:#c60a00}。 ec-2246。 _886bxmw{font-size: 16px}。 ec-2246。 _ 8gpc 1uj { position:relative }。 ec-2246。 _ 8 GPC 1uj:after { background-color:# fff； Bottom: 0; Color: # 34b458 Content: "Click to view details"; Display: embedded-block; Left padding:10px; Right padding: 0; Location: absolute; Right: 0}. ec-2246。 _ 8gpc 1uj:before { background-image:linear-gradient(270 deg，#fff，hsla(0，0%， 100%，0))； Bottom: 0; Content: ""; Height: 29px Location: Absolute; Right: 90px width: 47px} body.c69sfzc {display: flex; Font size: 0; Overflow: hide} body. _ 5d n6 uyv { margin-right:8px； Maximum width: 35px Minimum width: 35px Vertical alignment: top} Text. Qrpnxfs {background: URL (//nv00.cdn.bcebos.com/nv01/static/ECOM/img/PC/head-img-535c33798.png) is not duplicated by 50%; Background-size:100%100%; Height:100%; Width: 100%} text. nmkjhaf { height:35px； min-width:0 } body . v 1 ftvns { color:# 000； Font size:16px; Font-weight: inheritance; Line height:1; Margin-bottom: 8px overflow: hidden; Text overflow: ellipsis; Blank: nowrap}body _ 7pt 3pe 5 { color:# 999； font-size: 12px； line-height: 1 } body . c 69 sfzc . m 1 1 ehk 9 img { width: 100% } body . c 69 sfzc ._ 7 enmqsz { margin-right: 15px } body。 _ 5dn6uyv {Border radius: 50%; Maximum width: 40px Minimum width: 40px overflow: hidden} body.v1ftvns {color: # 333; font-size: 14px； Font thickness: 700; Line height: 20pxmargin-bottom:0 text. _ 7pt 3pe 5 { color:# 9 eacb 6； Line height:17px; Margin-top:3px} Taobao's best-selling advertisement 2024-07-04 ranking of stock trading app, shopping on Taobao, rest assured to choose the right goods, shopping is more worry-free. Super brand, super discount, fast life, one stop! Tao! I like it! simba.taobao.com！ Function (t){ "Use strictly"; Function e(t, e){ if(t){ var n = new date (). GetTime (), I = new image; t+="&rand="+(n+Math.random())，window . ecomlogimage | |(window . ecomlogimage = { })，window . ecomlogimage["-IMAGE "+n]= I，e = e | | { }； var r=！ 1; I . onload = I . on error = I . on abort = function(o){ if(！ e . charge){ if(o & &“load”！ = = o.type) {if (r) "error" in e&& e.error (); else{i.src=t，r=！ 0; return } } else " load " in e & & e . load()} I . on load = I . on error = I . ona bort = null，window . ecomlogimage["-IMAGE "+n]= null，i=null}， i.src=t}}var n，i=function(){var t，e，n，i=0，r=0，o=0，s=0，a=0，c=0，u=0，l=0，h=/link\？ url\=([^\&]+)/,f=/\？ url\=([^\.]+)\./，d="data-cklink "，p=/[？ &]tn=([^&]*)/； Function v(t){if (! T) return! 1; var e = h . exec(t)| | f . exec(t)； Return! ! e&&(h.exec(t)？ e[ 1]。 length