On-Line Course for Using Webrequest Method – Edward Bodmer

This website describes an alternative way to read data from yahoo.finance into excel. The method is more complex, but it provides an alternative when you are having trouble reading using the Workbooks.open. Details of the technique have changed, but it can be used to read the adjusted stock prices from yahoo.finance.

Credit

This method was provided by people at a website named signal.com. There are a some programmers who are very smart and I have just copied and adjusted their method. The technique uses something named WebRequest and requires that URL’s are adjusted. The adjustments involve cookies and crumbs. I do not claim to understand it all, but the method is very effective and it can be used for other applications.

Requirements

When you use the method, you must have an add-in to VBA installed.

VBA Code Introduction

The key code is shown below.

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
End With

OutRow = Split(WebRequest.ResponseText, vbLf): RowMax = UBound(OutRow) - 1 ' Split a single row when hit vbLF

Other Code

Cleaning Up CSV and Putting in Separate Cells

The code below demonstrates how to re-format the data so that it can be used

Sheets.Add ' Add a new sheet to put output

For i = 1 To RowMax ' This is for speeding up things
matrix(i, 1) = OutRow(i - 1) ' Puts into separate rows with a single column
Next i

range_name = "A1:A" & RowMax ' Where to put output

Range(range_name) = matrix ' Put the output into a range name

clean_yahoo ' This changes the CSV to separate columns

Application.Calculation = current_status

End Sub

Sub clean_yahoo()

Application.DisplayAlerts = False

Columns("A:A").Select ' Select column for text to columns

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

Columns("A:A").EntireColumn.AutoFit ' Format first column

sheet_name = Range("sheet_name_yahoo") ' Change the sheet name

On Error GoTo new_name: ' Delete if existing name
Sheets(sheet_name).Delete

new_name:
ActiveSheet.Name = sheet_name
ActiveSheet.Move after:=Sheets(Sheets.Count)
End Sub