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 11 to 20 of 40Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Need Help in same case
Rate this:
(3/5 from 1 vote)
I am facing the same problem with the website
https://my.secure.home1.ul.com/portal/page/portal/usa/Tools/MyProjects/Open%20Projects

Have no clue what I need to do . I need to import data from this website, but the first step is how can I log in with excel.

Please help.
 Posted by on
Confused
still no luck...
Rate this:
(3/5 from 1 vote)
from [http://www.bentekenergy.com/Bentek/index.aspx]

 I get "a" and "b" for variables for username and pw respectively.
 from [http://www.bentekenergy.com/Benport/Login.aspx?ReturnUrl=/Benport/DisplayReportNews.aspx%3fLOC%3d1%26ID%3d0%26doc%3dBENTEK_EMF_NukeOutages.pdf&LOC=1&ID=0&doc=BENTEK_EMF_NukeOutages.pdf]
 I get "Login1$UserName" and "Login1$Password" for variables for username and pw respectively.
 Neither of those seem to work once. I also tried changing url from Login.aspx to BENportTech.aspx with no luck...... What else could I try to make this work...? I very much appreciate the help!

 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
Confused
Old Code
Rate this:
(3/5 from 1 vote)
I tried using the old code, but that does not work for me.
It shows me an error when I run it, it says: Websit "http:......... cannot be opened and data cannot be downloaded"

What do you think might be wrong?
 Thanks for your help
 Posted by on
Confused
so..
Rate this:
(3/5 from 1 vote)
I am going to need a bit of hand holding as I am a newb. How and what kind of if statement do I write? I am really not too familiar with vba and I really dont understand exactly what needs to be done...
 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
Confused
runtime 1004 error
Rate this:
(3/5 from 1 vote)
I am using the code you just posted - but i do get the run-time 1004 error, any ideas why..?
 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
Confused
debugging
Rate this:
(3/5 from 1 vote)
I think the error is in this line:
 tempStr = Mid(x, StartPos, Len(x) - StartPos)
 Posted by on
 Displaying page 2 of 5 

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

© 2020 Business Spreadsheets   Legal   Follow us: