Home > Forum Home > Automating Data Analysis Tasks > Automating a web query login to download data Share

Automating a web query login to download data

Excel Help for Automating A Web Query Login To Download Data in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Automating A Web Query Login To Download Data

Rate this:
(2.8/5 from 16 votes)
Confused I have a funamental earnings and valuation model spreadsheet that downloads stock data from a number of different websites via certain add-ins.  One of the sites that it gets information from is investors.com, which requires a login before downloading stock information (not just price/volume/etc data, I get that easily from Yahoo).  Problem is that you have to manually login through a web query each time, and this is the current instructions:
  1. Go to "Data" on the Menu bar, then "Import External Data"
  2. Then go to "New Web Query" and a web browser will open
  3. Navigate to www.investors.com and login clicking remember me
  4. Then exit from the query, now you can use this tab
My question is how can I automate the login via either a macro or VBA code to login based upon a user name in one cell (C1), and a password in another cell (C2)?

Thanks in advance for any help you can provide! 
 Posted by on
 
Replies - Displaying 1 to 10 of 40Order Replies By: Most Recent | Chronological | Highest Rated
Oops
View the source of the login page
Rate this:
(3/5 from 1 vote)
Go to the page: http://www.bentekenergy.com/Benport/Login.aspx and "View Source" HTML of the page. 

The tags like <input type="..." name="XXX" id="..." value="..." /> will tell you what form variables are needed where XXX is the name of the variable.

 Excel Business Forums Administrator
 Posted by on
Surprised
Target page
Rate this:
(3/5 from 1 vote)
It could be due to the Javascript postback function on the login page.  Looking at the Javascript function, it seems that the target page for the login is in fact BENportHome.aspx and not Login.aspx.  Have you tried this?
 Excel Business Forums Administrator
 Posted by on
Oops
Two responses
Rate this:
(3/5 from 1 vote)
For Nick1234:

Try to use the VBA code at the beginning of this thread to login to the web site that you need the content for in Excel.

For vzny254:

On further investigation I think that Login.aspx is the correct target page and the other page is simply a redirect on the login event.

I think that the issue is that it is a .NET page which requires a valid session variable (you will see this in the HTML source). Therefore you need to first get the session variable from the login page and send it back when you log in.  It is possible to do this using the XML object in VBA.  This is a bit tricky as it will return the HTML source of the page and then you will then need to get the right attribute value to extract the session variable from that.  To use the XML object the code will resemble as follows:

VBA Code:
Sub Read_XML()
Dim xmlDom As MSXML2.DOMDocument
Dim i As Long, j As Long
' Add a reference to Microsoft XML, v6.0
Set xmlDom = New MSXML2.DOMDocument
xmlDom.Load "[ the URL here ]"

For i = 0 To xmlDom.DocumentElement.ChildNodes.Length - 1

Debug.Print "Name Pairs to be printed"
For j = 0 To xmlDom.DocumentElement.ChildNodes.Item(i).ChildNodes.Length
- 1
' Print the name of the column and then the value for the column
Debug.Print
xmlDom.DocumentElement.ChildNodes.Item(i).ChildNodes.Item(j).nodeName, " = ",
xmlDom.DocumentElement.ChildNodes.Item(i).ChildNodes.Item(j).nodeTypedValue

' You will need an If statement here to find the correct tag and then perform the string manipulation
' to get the value from the attribute
Next j

Next i

Set xmlDom = Nothing
End Sub
 Excel Business Forums Administrator
 Posted by on
Shocked
For Nick1234
Rate this:
(3/5 from 1 vote)
The reason that you cannot run the web query on the page in question is because it is a secure encrypted web page (https) that Excel cannot access via a web query.  See http://support.microsoft.com/kb/290347.
 Excel Business Forums Administrator
 Posted by on
Happy
Session ID for vzny254
Rate this:
(3/5 from 1 vote)
It took a while to work this out, but I developed some code that can get the session ID from the web page and then send it in the web query to authenticate the login. The XMLDom approach was not ideal as it is not a given that the web page is valid XML under the XHTML standard.  Instead, the following code uses Internet Explorer to recuperate the HTML of the page, extract the session ID from the HTML string and attach it to the web query call.

VBA Code:
Sub Login_WebQuery_SessionID()
Dim MyPost As String
Dim SessionIDName As String
Dim SessionIDValue As String
Dim StartPos As Single
Dim EndPos As Single
Dim tempStr As String

Const MyUrl As String = "http://www.bentekenergy.com/Benport/Login.aspx" 'change URL here
Const PostUser As String = "Login1$UserName=User Name" 'Change user name here
Const PostPassword As String = "&Login1$Password=password" 'Change password here
SessionIDName = "__VIEWSTATE" 'change session ID name here

'Get the HTML of the URL
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate MyUrl
While IE.Busy
DoEvents
Wend
x = IE.document.body.innerHTML
IE.Quit

'Narrow down the HTML to extract the Session ID value
StartPos = Application.WorksheetFunction.Find(SessionIDName, x)
tempStr = Mid(x, StartPos, Len(x) - StartPos)
StartPos = Application.WorksheetFunction.Find("value=", tempStr, 1)
tempStr = Mid(tempStr, StartPos, Len(tempStr) - StartPos)
EndPos = Application.WorksheetFunction.Find(" ", tempStr, 1)
tempStr = Mid(tempStr, 7, EndPos - 7)
tempStr = Replace(tempStr, """", "")
SessionIDValue = tempStr

'Attach the Login and Password
MyPost = PostUser & PostPassword

'Attach the required Session ID
MyPost = MyPost & "&" & SessionIDName & "=" & SessionIDValue

'Run the Web Query   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub

I have created a new post with this solution here as I think that it is useful on its own.
 Excel Business Forums Administrator
 Posted by on
Angry
StartPos value
Rate this:
(3/5 from 1 vote)
If you hover your mouse over StartPos when clicking Debug, what value does it give?

The code I posted works for me, except I do not have valid login details to send and I am not using the long URL with the query parameters in it.

If you could post your code (obviously without the login details), that might help.

 Excel Business Forums Administrator
 Posted by on
Sad
Debug code line
Rate this:
(3/5 from 1 vote)
When you click "Debug", what line of code is highlighted?
 Excel Business Forums Administrator
 Posted by on
Angry
Method will not work in all cases
Rate this:
(3/5 from 1 vote)
The .Post method for automating web query login will not work in all cases. In the last post, we can see from the page source that the form is posted to an encypted page:

action='https://wwwa2.settrade.com/LoginRepOnRole.jsp'

This should be the target of the web query; however it will not work as the page is secured (https).

Another setback we have seen is the presence of a session variable required by the target page to authenticate the user.  We have provided a solution for this whereby the session variavble value is first obtained then sent back with the login credentials to automate the login.  This is not fully stable and some more thought is required to come up with a robust solution to this issue.

 Excel Business Forums Administrator
 Posted by on
Surprised
Focus on Application
Rate this:
(3/5 from 1 vote)
Before you use the SendKeys command you need to set the system to focus on the launched application.
VBA Code:
Shell ("C:\???\appilication_name.exe"), vbMaximizedFocus

 Excel Business Forums Administrator
 Posted by on
Oops
Which version of Excel?
Rate this:
(3/5 from 1 vote)
The original code works for us.  Perhaps you are using a version of Excel that we have not tested.  Which version of Excel are you using?
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 5 

Find relevant Excel templates and add-ins for Automating a web query login to download data in the

© Business Spreadsheets   Legal   Follow us: