Sub WebRequest_yahoo() Dim WebRequest As WinHttp.WinHttpRequest ' Need to define the webrequest Dim OutRow() As String ' Place to put row by row output Dim matrix(20000, 20000) As String ' Dimension from S&P 500 daily Dim range_name As String Application.Calculation = xlCalculationManual response_wait = Range("response_wait") Range("cookie_string").Calculate Cookie = Range("cookie_string") Range("yahoo_url").Calculate ' calculate the range name -- to get the name WebRequestURL = Range("yahoo_url") ' Need to define the webrequest Set WebRequest = New WinHttp.WinHttpRequest ' This is the core of the method -- you need this statement With WebRequest 'FETCH THE DATA: .Open "GET", WebRequestURL, False ' Need to define the URL .setRequestHeader "Cookie", Cookie ' This is a painful part .send .waitForResponse (response_wait) ' Test how long to wait -- 10 seconds is a long time End With OutRow = Split(WebRequest.ResponseText, vbLf): RowMax = UBound(OutRow) + 1 ' Split a single row when hit vbLF On Error GoTo end2: ' When cannot find the url Sheets.Add ' Add a new sheet ActiveSheet.Move after:=Sheets(Sheets.Count) ' This is for speeding up things For i = 1 To RowMax ' Put this in the new sheet matrix(i, 1) = OutRow(i - 1) ' Put into array to read Next i range_name = "A1:A" & RowMax Range(range_name) = matrix ' Put in whole range name clean_yahoo_stocks ' Runs text to col etc. DoEvents Exit Sub end2: error_row = error_row + 1 Sheets("Errors").Cells(error_row, 1) = Range("yahoo_url") End Sub
The next part of the summary sheet involves making the graph. The columns for presenting the graph are shown in the screenshot below. The graph is made using the OFFSET method and it is important that the number of rows is correct for use in the range name created with the OFFSET function. The area that is graphed is shown in the gray columns. The height shown at the top in column P (cell P1) is a count of the number of dates +1. This is used in the range name with the OFFSET function. The item in column P for graphing is taken from column N and is the choice of what kind of element to graph (most of the time I use type 1 for the index graph). The items in columns to the right of column P for the other stocks (or commodity prices etc.) are taken from columns to the right that read the raw data and make similar adjustments that are made for the base index.