PDA

View Full Version : HTTPS GET REQUEST ***HELP***



alex878
08-07-2012, 07:43 PM
Hi guys,

I don’t know if anyone can help, but I am trying to retrieve the html code for a https site (online broker site). I can do this no problem for a http site (second URL) using a get request comand.

The code is below (sorry but i could not post URLs so i had to chop them up).

I am not too sure if I am missing something or if this can actually be achieved using VBA. Does anyone know if this can be done?



Sub test()

Dim HttpReq As Object
Dim response As String
Dim URL As String

'URL = "https" & "://www2" & ".comsec.com.au/Private/MarketPrices/QuoteSearch/QuoteSearch.aspx?stockCode=ari"
URL = "http" & "://www" & ".bom.gov.au/vic/observations/melbourne.shtml"

Set HttpReq = CreateObject("MSXML2.XMLHTTP.5.0")

On Error Resume Next

With HttpReq
.Open "GET", URL, False
.Send
End With

response = HttpReq.responseText

MsgBox response

End Sub


Alex

Kenneth Hobs
08-07-2012, 08:13 PM
Try:
Sub test()
'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
Dim HttpReq As New WinHttpRequest
'Dim HttpReq As Object

Dim response As String
Dim URL As String

URL = "https" & "://www2" & ".comsec.com.au/Private/MarketPrices/QuoteSearch/QuoteSearch.aspx?stockCode=ari"
'URL = "http" & "://www" & ".bom.gov.au/vic/observations/melbourne.shtml"

'Set HttpReq = CreateObject("MSXML2.XMLHTTP.5.0")

On Error Resume Next

With HttpReq
.Open "GET", URL, False
.Send
End With

response = HttpReq.responseText
HttpReq.WaitForResponse
Debug.Print response

End Sub

alex878
08-07-2012, 10:40 PM
Thanks Kenneth.

I have just tried the code you posted and added the reference to Micorsoft WinHTTP Serivices, version 5.1, however, the html text is still not being pulled from now from both the URLs.

Are there other settings I have to configure by any chance?

Alex.

Kenneth Hobs
08-08-2012, 06:08 AM
It gets the source code for me. I guess you can try my workbook to see if that helps.

alex878
08-09-2012, 02:10 AM
Thank Kenneth.

You are right it does work. However it is pulling back the source code from the homepage of Comsec and not the source code relating to the company ARI (ticker). This site you do have to log into and I have tried this but still it’s just pulling back the home page source code.

Is it possible to get around this in order to pull back the source code for a stock quote? Or is this a limitation?

Alex.

Kenneth Hobs
08-09-2012, 06:25 AM
Since I can't login, I can't really test it. I guess that is where the "s" for security comes from in https. Try viewing the source code directly in your browser.

There are other sites like yahoo that make getting stock quotes easy.

alex878
08-09-2012, 03:51 PM
Unfortunately yahoo does no provide live quotes for the Australian market.

alex878
08-09-2012, 03:53 PM
I found this other https site below which does have live quotes and no log in is needed to view quotes.

https://invest.etrade.com.au/QuotesAndResearch/Shares/Quote.aspx?symbol=ARI&tab=Quotes

However it has the same problem as the source code for the quote cannot be viewed but rather some rubbish code.

So I am guessing there is no way of pulling back source code from https sites with VBA or any other programming language?

Kenneth Hobs
08-09-2012, 04:43 PM
I don't know what you mean by rubbish. Keep in mind that debug.print and MsgBox methods to view long strings are limited.

Option Explicit

Sub test()
'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
Dim HttpReq As New WinHttpRequest
'Dim HttpReq As Object

Dim response As String
Dim URL As String
Dim fn As String

'URL = "https" & "://www2" & ".comsec.com.au/Private/MarketPrices/QuoteSearch/QuoteSearch.aspx?stockCode=ari"
'URL = "http" & "://www" & ".bom.gov.au/vic/observations/melbourne.shtml"
URL = "https://invest.etrade.com.au/QuotesAndResearch/Shares/Quote.aspx?symbol=ARI&tab=Quotes"
fn = "c:\temp\ken.txt"

'Set HttpReq = CreateObject("MSXML2.XMLHTTP.5.0")

On Error Resume Next

With HttpReq
.Open "GET", URL, False
.Send
End With

response = HttpReq.responseText
HttpReq.WaitForResponse
StrToTXTFile fn, response
Shell "notepad " & fn, vbNormalFocus
End Sub

Sub StrToTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(GetFolderName(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub

Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetParentFolderName(filespec)
Set fso = Nothing
GetFolderName = s
End Function

CodeNinja
08-10-2012, 06:10 AM
alex878,

I am not sure exactly what you are trying to capture, but another route you could try if this is not working for you at all is using inner and outer html in htmlObjectLibrary...

The down side is that this is no where near as efficient (very slow) as you have to open the web page to access this data...

sample code below:

Sub test()
Dim sURL As String
Dim IE As InternetExplorer
Dim doc As HTMLDocument

'requires Microsoft Internet Controls and Microsoft HTML Object Library References
'http://www.vbaexpress.com/forum/showthread.php?t=43237

sURL = "http" & "://www" & ".bom.gov.au/vic/observations/melbourne.shtml"

IE.navigate sURL
IE.Visible = True

Set doc = IE.document

MsgBox (doc.body.innerHTML)

End Sub

Kenneth Hobs
08-10-2012, 11:26 AM
Using CodeNinja's method, you can sacrifice speed for the other features of the IE object.

e.g.
' requires Microsoft Internet Controls and Microsoft HTML Object Library
Sub Test_IE()
Dim sURL As String
Dim IE As InternetExplorer
Dim doc As HTMLDocument

'sURL = "http" & "://www" & ".bom.gov.au/vic/observations/melbourne.shtml"
sURL = "https://invest.etrade.com.au/QuotesAndResearch/Shares/Quote.aspx?symbol=ARI&tab=Quotes"

Set IE = New InternetExplorer
With IE
.Visible = True
'.Visible = False
.Navigate sURL
Do Until .ReadyState = 4: DoEvents: Loop
End With
Set doc = IE.Document
'Do Until Not doc Is Nothing: Loop
With doc
Debug.Print .body.innerHTML, vbLf, vbLf
.all.Item("Login1_txtUserName").Value = "ken"
.all.Item("Login1_txtPassword").Value = "pw"
.forms(0).submit
Debug.Print .body.innerHTML
End With
End Sub

alex878
08-12-2012, 09:19 PM
Thanks Kenneth.

I have spent the last couple of days doing some research into accessing password https sites and while there are a few threads out there I can’t seem to find one that has been able to solve the issue (I have listed a couple) below. The SetCredential method seems to come up a lot, and I have tried to implement this into my code below adding my username and password for the site but with still no luck.

http://www.vbaexpress.com/forum/archive/index.php/t-5978.html

http://www.automateexcel.com/2005/02/11/excel_vba_winhttprequest_with_login_and/

With HttpReq
.Open "GET", URL, False
.SetCredentials "USERNAME", "PASSWORD", 0
.Send
End With

response = HttpReq.responseText
HttpReq.WaitForResponse
MsgBox response

End Sub

Is there a way of getting this to work from your experience with the SetCredentials method?

Kenneth Hobs
08-13-2012, 05:35 AM
Do the login at the the base url and then try a 2nd url with the stock name.

If that does not work, try the msie solution. There again, a two url solution will probably meet your needs.