MartinS
Active Member
- Joined
- Jun 17, 2003
- Messages
- 490
- Office Version
- 365
- Platform
- Windows
Hi
I've done lots of googling on this, and each time I think I've found a solution, it sets off a new issue, so thought I'd ask the experts and see what I'm doing wrong.
I'm trying to write a function to return the share price for a specific company to display in a workbook (only for reference), but whilst I have found several solutions online, and getting past the initial access denied issue, I'm now seeing an error saying "The date in the certificate is invalid or has expired". As you can see from the code, I did find what appeared to be a solution by telling the request to ignore errors, but still seeing the same error.
Function GetSharePrice(strSearchQuery As String) As Double
Dim strUrl As String
Dim strContent As String
Dim strSearchText As String
Dim strSharePrice As String
'strUrl = "https://finance.google.com/finance?q=" & strSearchQuery
strUrl = "https://www.google.com/search?q=" & strSearchQuery & "&tbm=fin"
Const SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS As Long = 13056
Const SXH_SERVER_CERT_IGNORE_CERT_DATE_INVALID As Long = 8192
With CreateObject("MSXML2.ServerXMLHTTP.6.0")
.Open "GET", strUrl, False
.setOption(2) = .getOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
.send
strContent = .responseText
End With
strSearchText = "itemprop=""price"""
strSharePrice = Mid(strContent, InStr(1, strContent, strSearchText) + Len(strSearchText) + 6)
GetSharePrice = Left(strSharePrice, InStr(strSharePrice, """") - 1)
End Function
I'm ignoring the last couple of lines at the moment as can't get the response to then determine what to search for, it's getting the response text that I need help with.
The only way this will kind of work is to change the line:
.setOption(2) = .getOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
to
.setOption(2) = SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
BUT this returns a simple request with Access Denied as the only content.
So the example function call would be GetSharePrice("aapl+shareprice"), which as a url (https://www.google.com/search?q=aapl+shareprice&tbm=fin) works without issue in the browser.
Is it possible to return a value from a webpage like this?
Many thanks
Martin
I've done lots of googling on this, and each time I think I've found a solution, it sets off a new issue, so thought I'd ask the experts and see what I'm doing wrong.
I'm trying to write a function to return the share price for a specific company to display in a workbook (only for reference), but whilst I have found several solutions online, and getting past the initial access denied issue, I'm now seeing an error saying "The date in the certificate is invalid or has expired". As you can see from the code, I did find what appeared to be a solution by telling the request to ignore errors, but still seeing the same error.
Function GetSharePrice(strSearchQuery As String) As Double
Dim strUrl As String
Dim strContent As String
Dim strSearchText As String
Dim strSharePrice As String
'strUrl = "https://finance.google.com/finance?q=" & strSearchQuery
strUrl = "https://www.google.com/search?q=" & strSearchQuery & "&tbm=fin"
Const SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS As Long = 13056
Const SXH_SERVER_CERT_IGNORE_CERT_DATE_INVALID As Long = 8192
With CreateObject("MSXML2.ServerXMLHTTP.6.0")
.Open "GET", strUrl, False
.setOption(2) = .getOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
.send
strContent = .responseText
End With
strSearchText = "itemprop=""price"""
strSharePrice = Mid(strContent, InStr(1, strContent, strSearchText) + Len(strSearchText) + 6)
GetSharePrice = Left(strSharePrice, InStr(strSharePrice, """") - 1)
End Function
I'm ignoring the last couple of lines at the moment as can't get the response to then determine what to search for, it's getting the response text that I need help with.
The only way this will kind of work is to change the line:
.setOption(2) = .getOption(2) - SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
to
.setOption(2) = SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
BUT this returns a simple request with Access Denied as the only content.
So the example function call would be GetSharePrice("aapl+shareprice"), which as a url (https://www.google.com/search?q=aapl+shareprice&tbm=fin) works without issue in the browser.
Is it possible to return a value from a webpage like this?
Many thanks
Martin
Last edited: